|
|
|
Larry Snyder & Mel Oyler, Instructors |
|
Ariel Kemp, Isaac Kunen, Gerome Miklau &
Sean Squires, Teaching Assistants |
|
University of Washington, Autumn 1999 |
|
|
|
|
Need to store information |
|
Files can store data |
|
Problems with flat files |
|
Relational databases |
|
Benefits of relational databases |
|
|
|
|
|
|
|
|
|
|
|
|
|
Your address book |
|
Name, phone number, address |
|
Books available in UW bookstore |
|
Title, author, availability |
|
Business |
|
Employee information, product information |
|
Accounting and financial data |
|
Hospital |
|
Medical records and insurance information |
|
University of Washington |
|
Student grades, tuition payments |
|
|
|
|
|
|
Early days of computing 1950’s to 1980 |
|
File based data storage |
|
Flat files |
|
Programs were written to: |
|
read data from file on disk |
|
process data (change employee salary, enter
course grade) |
|
Write data back to file |
|
|
|
But there are problems with file based data
storage |
|
Where is my wallet ? |
|
|
|
|
|
|
|
Need to write a program to get at the data |
|
Too hard to get to the data |
|
Time consuming and expensive, even for simple
reports |
|
Too easy to get to the data |
|
It was difficult to control access to the files |
|
People could change data if they could write a
program |
|
Salary data, grade data, bank account balances |
|
Data dependency |
|
Changing data format means you need to change
all programs that read the data |
|
Zip code changed from 5 to 9 digits |
|
Don’t know which programs are using the data |
|
|
|
|
|
|
Name Course Room # Instructor |
|
Paul Go FIT 100 EE1 Mel |
|
Mary Hi FIT 100 EE1 Mel |
|
Peter Yi IS 480 B209 Mel |
|
Nancy Yo IS 320 B309 Russ |
|
Paul Go IS 320 B309 Russ |
|
Peter Yi FIT 100 EE1 Mel |
|
|
|
Redundant data leads to data anomalies |
|
Bad decisions |
|
|
|
|
|
|
|
|
Data is stored in tables |
|
One table for each type of object or “entity” |
|
Each of the following would have a table |
|
students table lists all students - one row for
each student |
|
course table lists all courses - one row for
each course |
|
instructor table lists all instructors - one row
for each instructor |
|
Each row has a unique identifier |
|
Primary key (PK) |
|
Primary key is never null and cannot be a
duplicate |
|
What is the primary key for a student in UW
database? |
|
|
|
|
SID Name Instr.ID Name |
|
1 Mary Hi
1 Mel |
|
2 Peter Yi
2 Russ |
|
3 Paul Go |
|
4 Nancy Yo |
|
Course ID Name Room |
|
1 FIT 100
EE1 |
|
2 IS 320
B309 |
|
3 IS 480
B209 |
|
|
|
|
|
|
Controls data redundancy |
|
Information about one entity per table |
|
Relationships are stored using keys |
|
Database Management System (DBMS) |
|
Application that restricts access to data tables |
|
Maintains information about data types
(metadata) |
|
Solves data dependency problem |
|
Provides query facilities |
|
Wednesday |
|