1 / 15

Further Normalization

Further Normalization . Modeling Reality An interesting problem BC Normal Form Another interesting problem 4NF,5NF ….. Denormalization. An interesting problem . Let us assume the following reality For each subject, each student is taught by one instructor

locke
Download Presentation

Further Normalization

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. Further Normalization Modeling Reality An interesting problem BC Normal Form Another interesting problem 4NF,5NF ….. Denormalization

  2. An interesting problem • Let us assume the following reality • For each subject, each student is taught by one instructor • Each instructor teaches only one subject • Each subject is taught by several instructors

  3. An interesting relation From the course point of view => School: (Course,Student,Instructor) From the student point of view => Learning: (Student, Instructor, Course ) From the instructor point of view => Teaching: (Instructor, Course) Warning. If we wish to delete a student We may loose the professor information Course Instructor Student

  4. The Solution From the student point of view => Learning: (Student, Instructor) ) From the instructor point of view => Teaching: (Instructor, Course) Course They are said to be in Boyce Codd Normal Form Instructor Student

  5. Boyce/Codd Normal Form • BCNF refers to decompositions involving Relations with more than one candidate key, where the candidate keys are composite and overlapping • A relation is in BCNF if and only if every nontrivial, irreducible FD has a candidate key as its determinant • That is, a relation is in BCNF if and only if every determinant is a candidate key Source Date C.J. Introduction to Data Bases, 8th ed

  6. Multi-Valued Dependencies • A multi-valued dependency occurs when a determinant determines more than one dependent, and the dependents are independent of each other • Example course implies teacher; course implies text, where teacher and text are independent • A relation with course, instructor and text is all key, and exhibits redundancy, but is in 3NF • Updates can exhibit anomalies Source Date C.J. Introduction to Data Bases, 8th ed

  7. Another interesting problem • Let us assume the following reality • Each subject is taught by many Instructors • The same books are used in many subjects • Each Instructor uses a different book

  8. The Problem From the Instructor point of view => Instructors: (Course, Instructor, Text ) From the Textbook point of view => Textbools: (Course, Tex, Instructor ) There are Multi-Valued Dependencies Text Course Instructor

  9. The Solution From the student point of view => Instructors: (Course, Instructor) From the instructor point of view => Textbools: (Course, Text) They are said to be in Fourth Normal Form Text Course Instructor

  10. Fourth Normal Form • Relation R is in 4 NF if and only if, whenever there exist subsets A and B of the attributes of R such that the nontrivial multi-valued dependency A multi-determines B is satisfied, then all attributes of R are also functionally dependent on A • In the previous example, decompose course,instructor, text into two relation: course, instructor, and course, text Source Date C.J. Introduction to Data Bases, 8th ed

  11. The Practitioner’s solution 3NF is good enough as long as you add an extra domain Section Number

  12. Courses (Course, Section Number, Instructor) Student Load (Student, Course, Section Number) Section Number Course Instructor Student

  13. Courses (Course, Section Number, Text) Instructors: (Course, Instructor, Text ) Text Section Number Course Instructor

  14. Fifth Normal Form and so on • A relation R is in 5NF – also called projection-join normal form, if and only if every nontrivial join dependency that is satisfied by R is implied by the candidate key(s) of R • Theoretical discussions in DB have generated interesting case that go beyond the 5NF • As we have discussed a 3NF is good approach from a practitioner designer

  15. Denormalization • Denormalization is said to be necessary to improve performance • Technically normalization is a model concept, not related to stored files • Most people confuse the two • In practice, denormalization will speed up some queries, and drag down others • Proceed with caution Source Date C.J. Introduction to Data Bases, 8th ed

More Related