CS338 Study Guide - Winter 2019, Comprehensive Midterm Notes - Tuple, Relational Model, Sql

296 views20 pages
CS338
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 20 pages and 3 million more documents.

Already have an account? Log in
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
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 20 pages and 3 million more documents.

Already have an account? Log in
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
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 20 pages and 3 million more documents.

Already have an account? Log in
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
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 20 pages and 3 million more documents.

Already have an account? Log in

Get OneClass Grade+

Unlimited access to all notes and study guides.

Grade+All Inclusive
$10 USD/m
You will be charged $120 USD upfront and auto renewed at the end of each cycle. You may cancel anytime under Payment Settings. For more information, see our Terms and Privacy.
Payments are encrypted using 256-bit SSL. Powered by Stripe.