CS338 Study Guide - Winter 2019, Comprehensive Midterm Notes - Tuple, Relational Model, Sql
296 views20 pages
14 Feb 2019
School
Department
Course
Professor
CS338
Thursday, January 31, 2019
5:44 PM
Module 1: Terminology
TYPES:
Traditional Databases:
• Store numeric short text information
• For managing enterprises
Text & Multimedia Databases:
• Stores documents, digital images, audio and video streams
Geographic Information Systems
• Store maps, weather data, satellite images
• For route finding, agriculture, natural resource management
Data warehouses and online analytical processing (OLAP) systems
• Store historical business information
• For business analytics & decision support
Real time and active database technology
• Store process models, constraints, and key performance indicators
• Control industrial and manufacturing processes
DEFINITIONS:
Database
• Collection of related data
• Known facts that can be recorded and have implicit meaning
• Represents some aspects of the real world (miniworld)
• Built for a specific purpose
• Eg. Amazon.com, Canadian Census, The Bay’s product inventory, data collection
underlying Quest
Database Management System (DBMS)
• Collection of programs
• Enables users to create and maintain a database
• Allows multiple users and programs to access and manipulate database simultaneously
• Ability to change the database and program as requirements change
• Eg. IBM’s DB2, Access & SQL Server, Oracle, MySQL
Creating a Database:
• Need to define data types, structures, and constraints of the data to be stored
• Uses a Data Definition Language
Meta-data
• Database definition or descriptive information
• Stored by the DBMS in a database catalog or data dictionary
Steps in creating a Database
1. Requirements specification and analysis
2. Conceptual design
o Use Entity- Relationship Model
3. Logical design
o Relational Model
4. Physical Design
Populating a Database - inserting data to reflect miniworld
Query:
• Interaction causing some data to be retrieved
• Uses a Query Language
find more resources at oneclass.com
find more resources at oneclass.com
Manipulating a Database:
• Querying and updating the database to understand/ reflect miniworld
• Generating reports
• Uses a Data Manipulation Language
Application Program
• Access database by sending queries and updates to DBMS
Transactions
• A group of queries and updates that must be executed together and cannot be split
• Eg. buying a product, transferring funds, switching co-op streams
Users-> Application Programs/ Queries -> software processes queries/ programs -> access
stored data -> stored database definition (metadata) OR stored database
Example:
• List the cast of characters for Lincoln:
SELECT persona
FROM Role
WHERE movie = Lincoln
• Who Directed a drama in 2012?
SELECT director
FROM Film
WHERE year= 2012 AND genre= drama
• Who directed a film in which he or she also played a role?
SELECT actor
FROM Film, Role
WHERE director = actor AND title = movie
• What awards were won by War Horse?
SELECT award
FROM Honours
WHERE movie = winner= War Horse?
Updates???
Reorganizing a database
o Changes metadata, not data
o More drastic than data updates
• May require massive changes to data
• May require changes to application programs
o Uses data definition language
o Eg. renaming a column, adding a column
o Move column from one to another
o Change format of data in a cell
o Splitting one column into two - names
Pre-DBMS Databases
• Used traditional file processing
• Define & implement files needed for a specific software
• As application grew: many shared files, many structures, need to exchange data amongst
applications
Databases Problems removed:
1. Redundancy removed - no duplicates
2. Inconsistency - independent updates
3. Inaccuracy - concurrent updates
4. Incompatibility - multiple formats
5. Insecurity - proliferation
6. Inaudibility - chain of responsibility
find more resources at oneclass.com
find more resources at oneclass.com
7. Inflexibility - easy to apply changes
Characteristics
• Abstraction: dont tell how or where data is stored, shared in multiple views
• Transaction: multiple user processing
• Self-defining data: complete definition of structure & constraints as meta-data
o DB Catalog used by DBMS, users who need it
Actors
• DBA (administrator)
o Authorizing access
o Coordinating and monitoring use
o Acquiring software & hardware
o Tuning DBMS for best performance
• Designers
o Identify data to be stored
o Choose appropriate structures to represent and store data
• End Users
o Naive/ parametric end users - canned queries & updates
o Casual - occasional, special purpose
o Sophisticated - deep knowledge of design & DBMS facilities
• Standalone Users
o Personal database users
• System analysts
o Determines requirements of end users
• Application programmers
o Implement complex specifications (business logic) as programs
Behind the Scene
• Designers & implementers
• Tool developers
• Operators & management personnel
Times not to Use
• Simple, fixed data
• Small amount/ variety of data
• Time requirements
• Single access to data
find more resources at oneclass.com
find more resources at oneclass.com