Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 2NF 3NF and BCNF Normalization Normalization problems begin with a given relational schema and an associated set of Functional Dependencies FD For such problems you will be told to which level you are expected to normalize Normalization problems must proceed in hierarchical order each step must be completely normalized to that level before moving on to the next normal form 4NF and 5NF Normalization Problems normalizing 4NF and 5NF will also include a small ER diagram to disambiguate the schema semantics i e to determine whether 4NF normalization or 5NF normalization is warranted Problems normalizing 4NF and 5NF may also include a data set which must be mapped onto the normalized schema Format of Given Information Normalization problems may be presented in either one of two different representations One is a graphic representation of a relational schema and its associated FDs the other is a text based representation of a relational schema and its associated FDs The following two examples are two different ways to represent the exact same schema and FDs Exercises 15 24 and 15 25 in the textbook are given in text format Graphic R A B C D E F FD1 FD2 FD3 Text R A B C D E F F A B C D B F D E Elmasri Chapter 15 Study Exercises Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis Consider each of the universal relation R below numbered 1 4 and each associated set of function dependencies What is are the key s for each R Normalize decompose each R into 2NF then 3NF and finally BCNF relations Clearly indicate each step label all FDs and label the primary key PK for each new decomposed relation 1 R A B C D E F G H I J FD1 FD2 FD3 FD4 FD5 FD6 2 Textbook Exercise 15 24 p 538 R A B C D E F G H I FD1 FD2 FD3 FD4 FD5 Elmasri Chapter 15 Study Exercises J Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 3 Textbook Exercise 15 25 p 538 R A B C D E F G H I J A B C D E F G H I J K FD1 FD2 FD3 FD4 FD5 4 R FD1 FD2 FD3 FD4 FD5 FD6 FD7 FD8 Elmasri Chapter 15 Study Exercises Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 1 R A B C D E F G H I J FD1 FD2 FD3 FD4 FD5 FD6 2NF R1 A B C FD1 R2 A D E F FD2 FD4 FD3 FD5 3NF I J FD6 no 3NF needed R1 R3 B G H R2a A D FD2 R2b D E F FD3 R3a B G H FD4 R3b G H I J FD5 FD6 R1 R2a R2b R3a BCNF R3b1 G I J R3b2 H I FD6 Elmasri Chapter 15 Study Exercises J Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 2 Textbook Exercise 15 24 p 538 R 1NF A B C D E F G H I J FD1 FD2 FD3 FD4 FD5 2NF R1 A B C R2 A D E FD1 3NF R1 A B C FD1 R3 I J B F G H FD2 FD3 FD5 FD4 R2a A D E R2b D FD2 FD5 I J B F FD3 Elmasri Chapter 15 Study Exercises F G H FD4 Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 3 Textbook Exercise 15 25 p 538 R 1NF A B C D E F G H I J FD1 FD2 FD3 FD4 FD5 R1 2NF R2 A B D R3 A B C FD1 R4 B D E F R5 A D G H J FD2 FD3 A I R5 A I FD4 FD5 3NF R1 A B D R2 A B C FD1 R3 B D E F FD2 R4a A D G H R4b H J FD3 FD5 Elmasri Chapter 15 Study Exercises FD4 Chapter 15 Normalization Examples UTD CS 6360 Dr Chris Irwin Davis 4 R 1NF A B C D E F G H FD1 FD2 FD3 FD4 FD5 FD6 FD7 FD8 R1 2NF I J K R2 E F G H I J R3 A B C D E FD2 FD1 FD4 FD3 FD6 FD5 F K FD8 FD7 R1a 3NF R1b E F G H FD2 R1c G H FD4 I I FD6 R2a J R2b A E FD7 R2c A B C R3 B C D FD1 F K FD3 FD8 FD5 R2c1 BCNF R2c2 B D C D FD5 Elmasri Chapter 15 Study Exercises