1 / 31

Chapter 7: Relational Database Design

CS157B. Lecture 8 MVD. Chapter 7: Relational Database Design. Example R = ( student_id, student_name, course_id, course_name ) F = { student_id  student_name, course_id  course_name } { student_id, course_id } is a candidate key. F c = F R 1 = ( student_id, student_name )

hamal
Download Presentation

Chapter 7: Relational Database Design

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. CS157B Lecture 8 MVD Chapter 7: Relational Database Design

  2. Example R = ( student_id, student_name, course_id, course_name ) F = { student_id  student_name, course_id  course_name } { student_id, course_id } is a candidate key. Fc = F R1 = ( student_id, student_name ) R2 = ( course_id, course_name ) R3 = ( student_id, course_id)

  3. Example 2 R = ( A, B, C ) F = { A  BC, B  C } R is not in 3NF Fc = { A  B, B  C } Decomposition into: R1 = ( A, B ), R2 = ( B, C ) R1 and R2 are in 3NF

  4. BCNF VS 3NF • always possible to decompose a relation into relations in 3NF and • the decomposition is lossless • dependencies are preserved • always possible to decompose a relation into relations in BCNF and • the decomposition is lossless • may not be possible to preserve dependencies

  5. More Examples Candidate keys are (sid, part_id) and (sname, part_id). { sid, part_id }  qty { sname, part_id }  qty sid  sname sname  sid The relation is in 3NF: For sid  sname, …sname is in a candidate key. For sname  sid, …sid is in a candidate key. However, this leads to redundancy and loss of information sname part_id sid qty SSP

  6. sname part_id sid qty SSP If we decompose the schema into R1 = ( sid, sname ), R2 = ( sid, part_id, qty ) These are in BCNF. The decomposition is dependency preserving. { sname, part_id }  qty can be deduced from • sname  sid (given) • { sname, part_id }  { sid, part_id } (augmentation on (1)) • { sid, part_id }  qty (given) and finally transitivity on (2) and (3).

  7. part_id city sid SUPPLY city part_id sid SUPPLY More Examples At a city, for a certain part, the supplier is unique: citypart_id  sid. Also, sid  city The relation is not in BCNF: sid  city is not trivial, and …sid is not a superkey It is in 3NF: sid  city…city is in the candidate key of { city, part_id }. If we decompose into ( sid, city ) and ( sid, part_id ) we have BCNF, however { city, part_id }  sid will not be preserved.

  8. Design Goals • Goal for a relational database design is: • BCNF • lossless join • Dependency preservation • If we cannot achieve this, we accept: • 3NF • lossless join • Dependency preservation

  9. Multivalued Dependencies • There are database schemas in BCNF that do not seem to be sufficiently normalized • Consider a database classes(course, teacher, book)such that (c,t,b)  classes means that t is qualified to teach c, and b is a required textbook for c • The database is supposed to list for each course the set of teachers any one of which can be the course’s instructor, and the set of books, all of which are required for the course (no matter who teaches it).

  10. Multivalued Dependencies (Cont.) course teacher book • There are no non-trivial functional dependencies and therefore the relation is in BCNF • Insertion anomalies – i.e., if Sara is a new teacher that can teach database, two tuples need to be inserted (database, Sara, DB Concepts) (database, Sara, Ullman) database database database database database database operating systems operating systems operating systems operating systems Avi Avi Hank Hank Sudarshan Sudarshan Avi Avi Jim Jim DB Concepts Ullman DB Concepts Ullman DB Concepts Ullman OS Concepts Shaw OS Concepts Shaw classes

  11. Multivalued Dependencies (Cont.) • Therefore, it is better to decompose classes into: course teacher database database database operating systems operating systems Avi Hank Sudarshan Avi Jim teaches course book database database operating systems operating systems DB Concepts Ullman OS Concepts Shaw text We shall see that these two relations are in Fourth Normal Form (4NF)

  12. Multivalued Dependencies (MVDs) • Let R be a relation schema and let   R and  R. The multivalued dependency  holds on R if in any legal relation r(R), for all pairs for tuples t1 and t2 in r such that t1[] = t2 [], there exist tuples t3 and t4 in r such that: t1[] = t2 [] = t3 [] = t4[] t3[] = t1 [] t3[R – ] = t2[R – ] t4 [] = t2[] t4[R – ] = t1[R – ]

  13. MVD (Cont.) • Tabular representation of 

  14. X ->> Y is trivial if • Y  X or • Y U X = R

More Related