Skip to content

Normalization Exercises

Question 1

Consider relation: R = (A, B, C, D, E)

with the set of functional dependencies: {CE → D, D → B, C → A}

  • Find all candidate keys.
  • Identify the best normal form that R satisfies (1NF, 2NF, 3NF).
  • If the relation is not in 3NF, decompose it until it becomes 3NF.
    • At each step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy.

Question 2

Consider relation: R = (A, B, C, D, E, F, G, H, I, J)

with the set of functional dependencies: {AB → C, A → DE, B → F, F → GH, D → IJ}

  • Find all candidate keys.
  • Identify the best normal form that R satisfies (1NF, 2NF, 3NF).
  • If the relation is not in 3NF, decompose it until it becomes 3NF.
    • At each step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy.

Question 3

Consider relation: R = {A, B, C, D, E, F, G, H, I, J}

and the set of functional dependencies: {AB → C, BD → EF, AD → GH, A → I, H → J}

  • Find all candidate keys.
  • Identify the best normal form that R satisfies (1NF, 2NF, 3NF).
  • If the relation is not in 3NF, decompose it until it becomes 3NF.
    • At each step, identify a new relation, decompose and re-compute the keys and the normal forms they satisfy.

Question 4

Given the sample order form and description below, create the un-normalized form of the data and transform through the steps needed to move into 3NF.

Gallery Customer History Form

Customer Name
    Jackson, Elizabeth        Phone  (555) 867-5309
    123 4th Avenue
    Fonthill, ON, L3J 4S4

Purchases Made
Artist                    Title                       Purchase Date     Sales Price
03 - Carol Channing       Laugh with Teeth            09/17/2000        7000.00
15 - Dennis Frings        South toward Emerald Sea    05/11/2000        1800.00
03 - Carol Channing       At the Movies               02/14/2002        5550.00
15 - Dennis Frings        South toward Emerald Sea    07/15/2003        2200.00

The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer.

Question 5

Given the sample forms and description below, create the un-normalized form of the data and transform through the steps needed to move into 3NF. Finally, merge the two views into a unified schema.

View 1

Good News Grocers — Price Update List

This report is used by the department managers to update the prices that are displayed in the grocery store for these products.

Department Product Code Aisle Price Units
Produce 4081 1 0.35 lb
Produce 4027 1 0.90 ea
Produce 4108 1 1.99 lb
Butcher 331100 5 1.50 lb
Butcher 331105 5 2.40 lb
Butcher 332110 5 5.00 lb
Freezer 411100 6 1.00 ea
Freezer 521101 6 1.00 ea
Freezer 866503 6 5.00 ea
Freezer 866504 6 5.00 ea

View 2

Good News Grocers — Product Cost Report

This report is used by the grocery store manager to determine the final selling price of their products.

Supplier Product Cost Markup Price Dept
21 – Very Veggie 4108 – tomatoes, plum 1.89 5% 1.99 PR
32 – Fab Fruits 4081 – bananas 0.20 75% 0.35 PR
32 – Fab Fruits 4027 – grapefruit 0.45 100% 0.90 PR
32 – Fab Fruits 4851 – celery 1.00 100% 2.00 PR
08 – Meats R Us 331100 – chicken wings 0.50 300% 1.50 BU
08 – Meats R Us 331105 – lean ground beef 0.60 400% 2.40 BU
08 – Meats R Us 332110 – boneless chicken breasts 2.50 100% 5.00 BU
10 – Jerry’s Juice 411100 – orange juice 0.25 400% 1.00 FR
10 – Jerry’s Juice 521101 – apple juice 0.25 400% 1.00 FR
45 – Icey Creams 866503 – vanilla ice cream 2.50 100% 5.00 FR
45 – Icey Creams 866504 – chocolate ice cream 2.50 100% 5.00 FR

Question 6

For the following tables, identify what normal form the table is in: 0NF, 1NF, 2NF or 3NF. If it’s not in 3NF, bring it up to 3NF.

Table 1

Ship(BoatId, NbrOfPools, YearMade, Tonnage, CompanyId, CompanyName)

The functional dependencies for this table are:

  • BoatId → NbrOfPools, YearMade, Tonnage, CompanyId, CompanyName
  • CompanyId → CompanyName
  • CompanyName → CompanyId

Table 2

Inventory(PartNbr, {Warehouse, Location}, QOH, Weight, PartColor)

The functional dependencies for this table are:

  • PartNbr → Weight, PartColor
  • PartNbr + Warehouse → QOH   QOH is Quantity On Hand
  • Warehouse → Location

Sample Data

PartNbr Warehouse Location QOH Weight PartColor
01 500 NW 135 11.75 Blue
01 600 SW 210 11.75 Blue
01 800 East 192 11.75 Blue
02 500 NW 75 2.50 Red
02 800 East 45 2.50 Red
03 500 NW 290 21.35 Green
03 600 SW 83 21.35 Green

This format makes the repeating groups easier to see:

PartNbr Weight PartColor Warehouse Location QOH
01 11.75 Blue 500 NW 135
600 SW 210
800 East 192
02 2.50 Red 500 NW 75
800 East 45
03 21.35 Green 500 NW 290
600 SW 83

Table 3

Hospital(Patient, Insurance, Doctor, {Test, Result})

The functional dependencies for this table are:

  • Patient → Insurance, Doctor
  • Patient + Test → Result

Sample Data

Patient Insurance Doctor Test Result
Tweety Red Cross Livingston Brain Scan Not Found
Tweety Red Cross Livingston Blood work Yes and red
Sylvester Red Shield Kildare Cat Scan Yes he is a Cat
Sylvester Red Shield Kildare X Rays No broken bones
Sylvester Red Shield Kildare Flea check None

Table 4

Cars(Vin, Make, Style, Price, Year, Color)

The functional dependencies for this table are:

  • Vin → Make, Style, Price, Year, Color

Sample Data

Vin Make Style Price Year Color
1FD56GH8901X Ford SUV 55,000 1965 Red
1HC21KQ0733G Chevy Sports 18,200 1998 Green
3AS88JV9203K Dodge Truck 13,200 1997 Silver
4PL72XD5431M Ford Truck 5,500 1996 Blue
8BE24RU7385Y Ford Sedan 5,500 1996 Blue
7JU69TD2485R Dodge SUV 18,200 1998 Black
0LF83SA6540Z Dodge Sedan 500 1990 Rust

Acknowledgements

http://www.gc.maricopa.edu/business/sylvester/cis164/norm-pr1.htm

http://www.cs.rpi.edu/~sibel/dbs/FALL2002/notes/normalizationqa.pdf

https://scs.senecac.on.ca/~dbs201/pages/Normalization_Practice.htm

Fundamentals of Database Systems, Elmasri & Navathe