1 / 24

The Relational Model

Learn how to normalize a relation using the relational model and candidate keys. Identify functional dependencies and determine if the relation has normalization problems. Split functional dependencies into separate relations and create referential integrity constraints.

frankrodriguez
Download Presentation

The Relational 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 Model Chapter Two Normalization

  2. Normalization Process • Identify all candidate keys of the relation. • Identify all functional dependencies in the relation. • Exampine the determinants of the functional dependencies. If any determinant is NOT a candidate key, the relation has normalization problems. In this case…

  3. Normalization Process 3a. Place the columns of the functional dependency in a new relation of their own. 3b. Make the determinant the functional dependency of the primary key of the new relation. 3c. Leave a copy of the determinant as a foreign key in the original relation. 3d. Create a referential integrity constraint between the original relation and the new relation.

  4. Normalization Process 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key.

  5. Normalization Process Consider: PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, Customer Name, CustomerPhone, CustomerEmail)

  6. Normalization Process Step 1: Identify all candidate keys of the relation. PrescriptionNumber – determines all other elements Date? Dosage? Drug? – nope. i.e. many prescriptions can be written on any particular date Customer info? – nope. A customer may have more than one prescription. Candidate Keys: PrescriptionNumber

  7. Normalization Process Step 2: Identify all function dependencies in the relation. PrescriptionNumber determines all other attributes Drug  Dosage? Nope – a drug can have more than one dosage. Same for Dosage  Drug CustomerEmail  (CustomerName, CustomerPhone) Functional dependencies: PrescriptionNumber, CustomerEmail

  8. Normalization Process Step 3: Examine determinants. If any is not a candidate key, the relation has normalization problems. Split the functional dependency into relation of its own, make the determinant of the functional dependency the primary key: CUSTOMER (CustomerName, CustomerPhone, CustomerEmail)

  9. Normalization Process Still Step 3: Leave a copy of CustomerEmail in original relation as a foreign key: PRESCRIPTION (PrescriptionNumber, Date, Drug, Dosage, CustomerEmail)

  10. Normalization Process More Step 3: Create referential integrity constraint: CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER Repeating step 3 determines that these two relations are now normalized!

  11. Normalization Example 1 Table STU-DORM

  12. Normalization Example 1 STU-DORM (StudentNum, StudentName, DormName) DORM (DormName, DormCost) Constraint: DormName in STU-DORM must exist in DormName in DORM

  13. Normalization Example 1

  14. Normalization Example 2 Table EMPLOYEE

  15. Normalization Example 2 EMPLOYEE (EmployeeNum, LastName, Email, Department) DEPARTMENT (Department, DeptPhone) Constraint: Department in Employee must exist in Department in DEPARTMENT

  16. Normalization Example 2

  17. Normalization Example 3 Table MEETING

  18. Normalization Example 3 MEETING (Attorney, ClientNumber, MeetingDate, Duration) CLIENT (ClientNumber, ClientName) Constraint: ClientNumber in MEETING must exist in ClientNumber in CLIENT

  19. Normalization Example 3

  20. Normalization Example 4 Consider the following relation: GRADE (ClassName, Section, Term, Grade, StudentNumber, StudentName, Professor, Department, DepartmentEmail) Candidate keys? (Classname, Section, Term, StudentNumber) So… (Classname, Section, Term, StudentNumber) (Grade, StudentName, Professor, Department, ProfessorEmail)

  21. Normalization Example 4 Other functional dependencies: StudentNumber  StudentName Professor  (Department, ProfessorEmail) (Classname, Section, Term)  Professor Break these into own tables, leaving behind foreign keys…

  22. Normalization Example 4 STUDENT (StudentNumber, StudentName) PROFESSOR (Professor, Department, ProfessorEmail) CLASS_PROFESSOR( ClassName, Section, Term, Professor) GRADE (ClassName, Section, Term, Grade, StudentNumber) With proper constraints on each.

  23. The Null Value • A Null value means that no data was entered • This is different from a zero, space character, or tab character

  24. The Problem of Null Values • A Null is often ambiguous. It could mean… • The column value is not appropriate for the specific row • The column value is not decided • The column value is unknown • Each may have entirely different implications

More Related