New version page

# UTD CS 6360 - Normalization

Documents in this Course

7 pages

24 pages

13 pages

4 pages

2 pages

## This preview shows page 1-2 out of 7 pages.

View Full Document
Do you want full access? Go Premium and unlock all 7 pages.
Do you want full access? Go Premium and unlock all 7 pages.

Unformatted text preview:

Chapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis2NF, 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.GraphicTextR = {A, B, C, D, E, F}F = {{A,B}→{C,D}, {B}→{F}, {D}→{E}}Elmasri, Chapter 15 Study ExercisesA B C D E FFD1FD2FD3RChapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin DavisConsider 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)2) Textbook Exercise 15.24 (p.538)Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JFD1FD2FD3FD4FD5RFD6A B C D E F G H I JRFD1FD2FD3FD4FD5Chapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis3) Textbook Exercise 15.25 (p.538)4)Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JRFD1FD2FD3FD4FD5A B C D E F G H I JRKFD1 FD2FD3 FD4FD5 FD6FD7 FD8Chapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis1)Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JFD1FD2FD3FD4FD5RA B CFD1A D E FFD2FD3B G H I JFD4FD5R1 R2 R3A D D E FR2a R2bB G H G H I JFD4 FD5FD3FD2R3a R3b2NF3NFR1no 3NFneededFD6FD6FD6H I JFD6G I JBCNFR3b1 R3b2R2a R2b R3aR1Chapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis2) Textbook Exercise 15.24 (p.538)Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JRA D E B F G HA B C I J2NFA B CR1 R2 R3R1A D E I JDR2a R2bB F F G H3NF1NFFD1FD2FD3FD4FD5FD1 FD2FD5FD3FD4FD1 FD2 FD5 FD3 FD4Chapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis3) Textbook Exercise 15.25 (p.538)Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JR1NFFD1FD2FD3FD4FD5A BR1D A B CR2B D E FFD1 FD2A D G HFD3A IJFD5FD4R3 R4 R52NF3NFA BR1D A B CR2FD1B D E FFD2R3A IFD4R5A D G HFD3FD5R4aH JR4bChapter 15 – Normalization ExamplesUTD CS-6360 Dr. Chris Irwin Davis4) Elmasri, Chapter 15 Study ExercisesA B C D E F G H I JRKA B C D EE F G H I JF KFD1 FD2FD3 FD4FD5 FD6FD7 FD8FD2FD4FD6FD1FD3FD5FD7FD8R1 R2 R3E F G H I JG H IR1a R1b R1cFD2 FD4 FD6A EFD7A B CFD1B C DFD3FD5R2a R2b R2cF KFD8R3C DFD5B D1NF2NF3NFBCNFR2c1

View Full Document