Understanding Relational Model & Database Normalization

slide1 n.w
1 / 36
Embed
Share

Explore the relational model, functional dependency, candidate keys, normalization process, and determinants in database design. Learn how to ensure data integrity and reduce anomalies through proper organization of data.

  • Database Design
  • Relational Model
  • Normalization
  • Functional Dependency
  • Candidate Keys
rayaanacharya
aroll Follow

Uploaded on | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

Download Presentation

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.

E N D

Presentation Transcript

  1. 1 IST210 THE RELATIONAL MODEL II IST 210: Organization of Data

  2. 2 IST210 How to design a well-formed model? Revisit assignment 1 Break list into tables How to split? Functional Dependency important criteria Normalization Process process to design a well-formed model

  3. 3 IST210 Functional Dependency A relationship between attributes: some attribute(s) determine the value of other attribute(s) in the same table These attributes we name them determinant The other attributes are functionally dependent on this determinant CookieCost = NumberOfBoxes * $5 NumberOfBoxes CookieCost Functionally dependent on NumberofBoxes determinant CookieCost = NumberOfBoxes * UnitPrice (UnitPrice, NumberOfBoxes) CookieCost The composite forms determinant

  4. 4 IST210 Determinant in Relations StudentI D 9123450 9123451 9123452 9123453 FirstName LastName DOB Determinant: StudentID John John Jane Josh Smith Adam Adam Cohen Jan. 1, 1989 Jun. 1, 1988 Aug. 1,1989 Aug. 1,1989 ClubID 12 13 15 ClubName Football Medical Dance President 9123450 9123453 9123452 Determinant: ClubID Determinant: CourseID Determinant = Candidate key?

  5. 5 IST210 Review: Candidate Key A candidate key is a special key Any subset of a candidate key is NOT a key (StudentID, FirstName) is not a candidate key, because StudentID is a key

  6. 6 IST210 Determinant = Candidate Key? Student Name Bob Lisa Sarah Jim Bob Jim Kate Student's Department IST IST IST CSE IST CSE IST Student ID 1 5 2 3 1 3 10 Email CourseID Instructor 210 210 210 210 220 220 230 CourseName Organization of Data Organization of Data Organization of Data Organization of Data Network Network Computer Language Location 110IST 110IST 110IST 110IST 208IST 208IST 206IST Jessie Jessie Jessie Jessie John John David bbb@psu.edu lll@psu.edu sss@psu.edu jjj@psu.edu bbb@psu.edu jjj@psu.edu kkk@psu.edu In a relation, a candidate key must be a determinant Candidate Key Determinants In a relation, a determinant may not be a candidate key StudentID CourseID (StudentID, CourseID) (StudentID, CourseID) If every determinant is a candidate key, it is a well-formed relation.

  7. 7 IST210 Normalization Normalization is a process of analyzing a relation to ensure that it is well formed No data redundancy among the nonkey attributes Data can be inserted, deleted, or modified without creating update anomalies

  8. 8 IST210 Normalization Principles Relational design principle for normalized relations: To be a well-formed relation, every determinant must be a candidate key Any relation that is not well formed should be broken into two or more well-formed relations

  9. 9 IST210 Normalization Process Identify all the candidate keys of the relation. Identify all the functional dependencies in the relation. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the functional dependency in a new relation of their own. b. Make the determinant of the functional dependency the primary key of the new relation. c. Leave a copy of the determinant as a foreign key in the original relation. d. Create a referential integrity constraint between the original relation and the new relation. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key. 1. 2. 3. 4.

  10. 10 IST210 Normalization Process: Example 1: Step 1 Step 1. Identify all the candidate keys of the relation. PrescriptionNumber

  11. 11 IST210 Normalization Process: Example 1: Step 2 Step 2. Identify all the functional dependencies in the relation. PrescriptionNumber (Date, Drug, Dosage, CustomerName, CustomerPhone, CustomerEmail) Drug Dosage? No CustomerEmail (CustomerName, CustomerPhone)? Yes, in this example This is a trivial dependency by the definition of candidate key

  12. 12 IST210 Normalization Process: Example 1: Step 3 Step 3. If any determinant is not a candidate key, the relation is not well formed. Determinant: PrescriptionNumber, CustomerEmail Candidate key: PrescriptionNumber CustomerEmail is NOT a candidate key, so the relation NOT well formed Then, we will normalize it (break it into multiple relations)

  13. 13 IST210 Normalization Process: Example 1: Step 3 Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the functional dependency in a new relation of their own. CUSTOMER (CustomerEmail, CustomerName, CustomerPhone) b. Make the determinant of the functional dependency the primary key of the new relation. CUSTOMER (CustomerEmail, CustomerName, CustomerPhone) c. Leave a copy of the determinant as a foreign key in the original relation. PRESCRIPTION(PrescriptionNumber, Date, Drug, Dosage, CustomerEmail) d. Create a referential integrity constraint between the original relation and the new relation. CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER

  14. 14 IST210 Normalization Process: Example 1: Step 4 Step 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key. CUSTOMER (CustomerEmail, CustomerName, CustomerPhone) PRESCRIPTION(PrescriptionNumber, Date, Drug, Dosage, CustomerEmail) CustomerEmail in PRESCRIPTION must exist in CustomerEmail in CUSTOMER Well-formed relational model design

  15. 15 IST210 Normalization Process: Example 2 Step 1. Candidate keys Step 2. Functional dependencies Step 3. Split the relation

  16. 16 IST210 Normalization Process: Example 2: Step 1 Step 1. Identify all the candidate keys of the relation. StudentNumber

  17. 17 IST210 Normalization Process: Example 2: Step 2 Step 2. Identify all the functional dependencies in the relation. Trivial dependency: StudentNumber (LastName, FirstName, DormName, DormCost) DormName DormCost

  18. 18 IST210 Normalization Process: Example 2: Step 3 Step 3. If any determinant is not a candidate key, the relation is not well formed. StudentNumber StudentNumber (LastName, FirstName, DormName, DormCost) DormName DormCost

  19. 19 IST210 Normalization Process: Example 2: Step 3 Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the functional dependency in a new relation of their own. DORM(DormName, DormCost) b. Make the determinant of the functional dependency the primary key of the new relation. DORM(DormName, DormCost) c. Leave a copy of the determinant as a foreign key in the original relation. STU_DORM(StudentNumber, LastName, FirstName, DormName) d. Create a referential integrity constraint between the original relation and the new relation. DormName in STU_DORM must exist in DormName in DORM

  20. 20 IST210 Normalization Process: Example 2: Step 4 Step 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key. STU_DORM(StudentNumber, LastName, FirstName, DormName) DORM(DormName, DormCost) DormName in STU_DORM must exist in DormName in DORM Well-formed relational model design

  21. 21 IST210 Normalization Process: Example 3 Step 1. Candidate keys Step 2. Functional dependencies Step 3. Split the relation

  22. 22 IST210 Normalization Process: Example 3: Step 1 Step 1. Identify all the candidate keys of the relation. (Attorney, ClientNumber, MeetingDate)

  23. 23 IST210 Normalization Process: Example 3: Step 2 Step 2. Identify all the functional dependencies in the relation. Trivial dependency: (Attorney, ClientNumber, MeetingDate) (ClientName, Duration) ClientNumber ClientName

  24. 24 IST210 Normalization Process: Example 3: Step 3 Step 3. If any determinant is not a candidate key, the relation is not well formed. (Attorney, ClientNumber, MeetingDate) (ClientName, Duration) (Attorney, ClientNumber, MeetingDate) ClientNumber ClientName

  25. 25 IST210 Normalization Process: Example 3: Step 3 Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the functional dependency in a new relation of their own. CLIENT(ClientNumber, ClientName) b. Make the determinant of the functional dependency the primary key of the new relation. CLIENT(ClientNumber, ClientName) c. Leave a copy of the determinant as a foreign key in the original relation. MEETING(Attorney, ClientNumber, MeetingDate, Duration) ClientNumber: A foreign key and also part of primary key d. Create a referential integrity constraint between the original relation and the new relation. ClientNumber in MEETING must exist in ClientNumber in CLIENT

  26. 26 IST210 Normalization Process: Example 3: Step 4 Step 4. Repeat step 3 as many times as necessary until every determinant of every relation is a candidate key. CLIENT(ClientNumber, ClientName) MEETING(Attorney, ClientNumber, MeetingDate, Duration) ClientNumber in MEETING must exist in ClientNumber in CLIENT Well-formed relational model design

  27. 27 IST210 Normalization Process: Example 4 Student Name Bob Lisa Sarah Jim Bob Jim Kate Student's Department IST IST IST CSE IST CSE IST Student ID 1 5 2 3 1 3 10 Email CourseID Instructor 210 210 210 210 220 220 230 CourseName Organization of Data Organization of Data Organization of Data Organization of Data Network Network Computer Language Location 110IST 110IST 110IST 110IST 208IST 208IST 206IST Jessie Jessie Jessie Jessie John John David bbb@psu.edu lll@psu.edu sss@psu.edu jjj@psu.edu bbb@psu.edu jjj@psu.edu kkk@psu.edu Step 1. Candidate keys Step 2. Functional dependencies Step 3. Split the relation

  28. 28 IST210 Normalization Process: Example 4 Student Name Bob Lisa Sarah Jim Bob Jim Kate Student's Department IST IST IST CSE IST CSE IST Student ID 1 5 2 3 1 3 10 Email CourseID Instructor 210 210 210 210 220 220 230 CourseName Organization of Data Organization of Data Organization of Data Organization of Data Network Network Computer Language Location 110IST 110IST 110IST 110IST 208IST 208IST 206IST Jessie Jessie Jessie Jessie John John David bbb@psu.edu lll@psu.edu sss@psu.edu jjj@psu.edu bbb@psu.edu jjj@psu.edu kkk@psu.edu Step 1. Candidate keys (StudentID, CourseID) Step 2. Functional dependencies (StudentID, CourseID) all other columns StudentID (StudentName, Student s Department, Email) CourseID (Instructor, CourseName, Location) Step 3. Split the relation STUDENT(StudentID, StudentName, Student sDepartment, Email) COURSE(CourseID, Instructor, CourseName, Location) REGISTRATION(StudentID, CourseID) StudentID in REGISTRATION must exist in StudentID in STUDENT COURSEID in REGISTRATION must exist in CourseID in COURSE

  29. 29 IST210 Normalization Process: Example 5 GRADE(ClassName, Section, Term, Grade, StudentNumber, StudentName, Professor, Department, ProfessorEmail)

  30. 30 IST210 Normalization Process: Example 5: Step 1 GRADE(ClassName, Section, Term, Grade, StudentNumber, StudentName, Professor, Department, ProfessorEmail) Step 1. Identify all the candidate keys of the relation. (ClassName, Section, Term, StudentNumber)

  31. 31 IST210 Normalization Process: Example 5: Step 2 GRADE(ClassName, Section, Term, Grade, StudentNumber, StudentName, Professor, Department, ProfessorEmail) Step 2. Identify all the functional dependencies in the relation. Trivial dependency: (ClassName, Section, Term, StudentNumber) (Grade, StudentName, Professor, Department, ProfessorEmail) StudentNumber StudentName Professor (Department, ProfessorEmail) (Classname, Section, Term) Professor

  32. 32 IST210 Normalization Process: Example 5: Step 3 GRADE(ClassName, Section, Term, Grade, StudentNumber, StudentName, Professor, Department, ProfessorEmail) Step 3. If any determinant is not a candidate key, the relation is not well formed. (ClassName, Section, Term, StudentNumber) all other columns StudentNumber StudentName Professor (Department, ProfessorEmail) (Classname, Section, Term) Professor (ClassName, Section, Term, StudentNumber)

  33. 33 IST210 Normalization Process: Example 5: Step 3 Step 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: STUDENT(StudentNumber, StudentName) PROFESSOR(Professor, Department, ProfessorEmail) CLASS_PROFESSOR(ClassName, Section, Term, Professor) GRADE_1(ClassName, Section, Term, Grade, StudentNumber) StudentNumber in GRADE_1 must exist in StudentNumber in STUDENT Proessor in CLASS_PROFESSOR must exist in Professor in PROFESSOR (ClassName, Section, Term) in GRADE_1 must exist in (ClassName, Section, Term) of CLASS_PROFESSOR StudentNumber StudentName Professor (Department, ProfessorEmail) (Classname, Section, Term) Professor (ClassName, Section, Term, StudentNumber) all other columns

  34. 40 IST210 Review Question A relation is well-formed if A. Every determinant is a candidate key. B. Every candidate key is a determinant.

  35. 41 IST210 Summary of Chapter 2 Learn the concept of the relational model Learn the meaning and importance of keys, candidate keys, primary keys, foreign keys, and related terminology Learn the meaning of functional dependencies Learn to apply a process for normalizing relations

  36. 42 IST210 Reminder Homework 2 is out due Sept 14th at 11:59PM No Class on Labor day

Related


No related presentations.

More Related Content