1 / 79

The Relational Database Model

The Relational Database Model. CST272. Return. Creating the Unnormalized Table. List the fields Select a primary key The field (or concatenation of two or more fields) that uniquely identifies each record. Purchase Order Report. Unnormalized Table.

aleron
Download Presentation

The Relational Database Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Database Model CST272

  2. Return

  3. Creating the Unnormalized Table • List the fields • Select a primary key • The field (or concatenation of two or more fields) that uniquely identifies each record Purchase Order Report

  4. Unnormalized Table Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Extension, Subtotal, Tax, Shipping, Discount, PO total) Purchase Order Report

  5. The Unnormalized Purchase Order Table Purchase Order Report Return

  6. To Convert from Unnormalized to 1NF • Identify, list and remove repeating groups • Modify Primary key (concatenated fields) • Important: In 1NF there always will still be just one table

  7. Repeating Groups Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) The Unnormalized Purchase Order Table Purchase Order Report

  8. The Payables Table in 1NF Purchase Order Report Return

  9. 1NF (First Normal Form) Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) The Payables Table in 1NF Purchase Order Report

  10. To Convert from 1NF to 2NF • Identify the determinants • A listing of every possible combination of elements (fields) that make up the 1NF primary key • Identify the functional dependencies • Which non-key fields rely on which determinants to determine their value • For the new 2NF tables, the determinants are the primary keys

  11. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number Product number PO number, Product number

  12. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date Product number PO number, Product number

  13. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number Product number  PO number, Product number 

  14. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name Product number PO number, Product number

  15. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address Product number  PO number, Product number 

  16. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City Product number  PO number, Product number 

  17. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State Product number PO number, Product number

  18. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code Product number PO number, Product number

  19. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code Product number Product PO number, Product number

  20. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code Product number Product PO number, Product number Unit price

  21. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code Product number Product PO number, Product number Unit price, Quantity

  22. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal Product number Product PO number, Product number Unit price, Quantity

  23. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax Product number Product PO number, Product number Unit price, Quantity

  24. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping Product number Product PO number, Product number Unit price, Quantity

  25. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount Product number Product PO number, Product number Unit price, Quantity

  26. Functional Dependencies Payables (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Product number, Product, Unit price, Quantity, Subtotal, Tax, Shipping, Discount, PO total) Determinants PO number PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total Product number Product PO number, Product number Unit price, Quantity

  27. 2NF (Second Normal Form) Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Purchase Order Report

  28. To Convert from 2NF to 3NF • Within the existing 2NF tables, identify the non-key determinants • Any non-key fields that could be a primary key for other fields in one of the 2NF tables • Identify those functional dependencies • Create new table(s) from the determinant(s) and functional dependency(cies) • Do not remove the non-key determinant(s) from original table(s)

  29. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product)

  30. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number

  31. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number Vendor name

  32. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number Vendor name, Address

  33. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number Vendor name, Address, City

  34. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number Vendor name, Address, City, State

  35. Non-key Determinants Purchase Order (PO number, PO date, Vendor number, Vendor name, Address, City, State, ZIP code, Subtotal, Tax, Shipping, Discount, PO total) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product) Vendor number Vendor name, Address, City, State, ZIP code

  36. 3NF (Third Normal Form) Vendor (Vendor number, Vendor name, Address, City, State, ZIP code) Purchase Order (PO number, Vendor number, Subtotal, Tax, Shipping, PO total, PO date) Payables (PO number, Product number, Unit price, Quantity) Product (Product number, Product)

  37. Finalize the Tables in Database Design Language • Present the 3NF tables with all keys: • Primary keys—that field which uniquely identifies (differentiates) a record from all other records in the table • Alternate (secondary) keys—a field that could have been the primary key but is not; DBMS must enforce that values of field are unique for every record • Foreign keys—a field that links to field values in another table; a foreign key value must match the primary key of one of the records in table to which it joins, or be null

  38. DBDL (Database Design Language) Vendor (Vendor number, Vendor name, Address, City, State, ZIP code) Purchase Order (PO number, Vendor number, Subtotal, Tax, Shipping, PO total, PO date) FK Vendor number  Vendor Payables (PO number, Product number, Unit price, Quantity) FK PO number  Purchase Order FK Product number  Product Product (Product number, Product)

  39. Unnormalized—Contact Listing Vendor (Vendor number, Vendor name, Contact, Telephone)

  40. DBDL—Contact Listing Vendor (Vendor number, Vendor name, Contact, Telephone)

  41. Merge the Tables • If you have two tables with the identical primary key, they generally should be merged into a single table • The results of each new normalization should be merged into the existing tables from previous normalizations

  42. DBDL—The Merged Tables Vendor (Vendor number, Vendor name, Address, City, State, ZIP code, Contact, Telephone) Purchase Order (PO number, Vendor number, Subtotal, Tax, Shipping, PO total, PO date) FK Vendor number  Vendor Payables (PO number, Product number, Unit price, Quantity) FK PO number  Purchase Order FK Product number  Product Product (Product number, Product)

  43. DBDL—The Final Tables Vendor (Vendor number, Vendor name, Address, City, State, ZIP code, Contact, Telephone, Fax, Term days, Term percent, Date last order) Purchase Order (PO number, Vendor number, Subtotal, Tax, Shipping, PO total, PO date, Bill date, Discount date, Due date) FK Vendor number  Vendor Payables (PO number, Product number, Unit price, Quantity, Back ordered?) FK PO number  Purchase Order FK Product number  Product Product (Product number, Product)

  44. Unnormalized table Repeating groups (list) 1NF Determinants and functional dependencies 2NF Non-key determinants and functional dependencies 3NF in DBDL (with all keys) Mini Quiz • Normalize a “Student Transcript”using the model given from the “Payables” database Student Transcript Student ID: 345 Advisor ID: 56 Student Name: Sally Henson Advisor name: Ralph Pollard Credits completed: 32 Advisor office: H203 Class standing: Freshman Advisor phone: 851-6590 Course numberGrade CS33 A EG13 B+ Return

  45. The Unnormalized Table • Student(StudentID, StudentLast, StudentFirst, AdvisorID, AdvisorLast, AdvisorFirst, AdvisorOffice, AdvisorPhone, CreditsCompleted, ClassStanding, CourseNumber, Grade) • CreditsCompletedis the total credits currently completed by the student • ClassStanding is Freshman, Sophomore, etc. • CourseNumber is each course (of more than one) that the student has taken or is taking Student Transcript

  46. Convert from Unnormalized to 1NF • Identify, list and remove repeating groups • Modify the unnormalized table by including an additional field to create a concatenated primary key Student Transcript

  47. Repeating Groups • CourseNumber, Grade Student Transcript

  48. 1NF • Student Grade (StudentID, StudentLast, StudentFirst, AdvisorID, AdvisorLast, AdvisorFirst, AdvisorOffice, AdvisorPhone, CreditsCompleted, ClassStanding, CourseNumber, Grade) Student Transcript Return

  49. Convert from 1NF to 2NF • Identify the determinants • List the functional dependencies for each determinant • Create the new tables—one for each determinant and the fields functionally dependent upon it

More Related