This preview shows page 1. Sign up to view the full content.
Unformatted text preview: .99 192 3.99 638 3.29 Birthdate 5/23/1980 5/23/1980 7/9/1983 4/5/1972 6/20/1960 2/3/1959 Access Query: Employee ID First Name 1245 Tom 1285 Erin 1538 Paula 2465 Christie 2768 Jamie 5345 Michaela Customer Number 100 101 102 103 104 105 106 Last Name Morgan Franklin Younger Oldham Smith Jones Customer Name Jacob Johnson Linea Marks Smith Wiggum Jonas Region Texas Oklahoma Oklahoma Texas Texas Louisiana New Mexico Query results:
Last Name Jones Morgan Oldham First Name Michaela Tom Christie Customer Number 103 103 103 Customer Name Marks Marks Marks Relational Databases A DBMS is characterized by the type of data DBMS model upon which it is based model A data model is Most databases are called relational databases
• They use the relational data model • Developed by E.F. Codd (1970) Relational Data Model A relational data model represents everything relational in the form of ______ in Tables are called ________ Each row is called a ______ The data are not actually stored in tables Stored according to the ___________________ RDM only describes how the data appear in the • ________________ schema ________________ schema • ________________ schema ________________ schema Types of Attributes A __________ uniquely identifies a row (_____) Can be a single ________ Can be a combination of attributes A foreign key is an attribute that _______________________________ _______________________________ Non-key attributes ___________________________ RDM Requirements RDM
A well structured (normalized) database: well Every column in a row must be single valued Primary keys cannot be null (_________________) Foreign keys must be either null or have a value that Foreign corresponds to the value of a primary key in another relation (____________________) relation Non-key attributes should describe a characteristic Non-key about the object identified by the primary key about Integrity Rules
Texas Drivers License Table TDL 100 200 300 400 500 700 800 900 Last Name First Name Street City Student Table Peoplesoft ID 11 12 13 14 15 16 17 18 TDL 300 700 200 500 800 400 100 Last Name First Name Street City Problems with Storing All Data in One Table Problems Creates redundancy: Creates Update anomaly Insert anomaly Delete anomaly Delete Update Anomaly Update Changes to some redundant data values are Changes overlooked if all data are in one table overlooked Example: a new in vendor web address
• Vendor data would be repeated for every purchase • Changes would have to be made wherever the Changes number appeared rather than just once number • Some might be missed Inefficient communication Lost on-line B-to-B orders Insert Anomaly Insert Inability to add new entities until a transaction Inability occurs occurs Example: if P.O. number is primary key
• A new vendor could not be e...
View Full Document
- Fall '10