Database systems

MITS4003 Database Systems

Take Home Exercise - 3 of Lesson - 3

June 2018 Task 1: Normalization 3.1 Normalize up to third normal form

MITS4003 Exercise 3

Copyright © 2015-2018 VIT, All Rights Reserved. 2

HEALTH HISTORY REPORT PET ID PET NAME PET TYPE PET AGE OWNER VISIT DATE PROCEDURE 246 ROVER DOG 12 SAM COOK JAN 13/2002 01 - RABIES VACCINATION MAR 27/2002 10 - EXAMINE and TREAT WOUND APR 02/2002 05 - HEART WORM TEST 298 SPOT DOG 2 TERRY KIM JAN 21/2002 08 - TETANUS VACCINATION MAR 10/2002 05 - HEART WORM TEST 341 MORRIS CAT 4 SAM COOK JAN 23/2001 01 - RABIES VACCINATION JAN 13/2002 01 - RABIES VACCINATION 519 TWEEDY BIRD 2 TERRY KIM APR 30/2002 20 - ANNUAL CHECK UP APR 30/2002 12 - EYE WASH

3.2 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.

Normalize up to third normal form.

Gallery Customer History Form

Customer Name Jackson, Elizabeth Phone (206) 284-6783

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

MITS4003 Exercise 3

Copyright © 2015-2018 VIT, All Rights Reserved. 3

3.3 Consider the following relation and functional dependencies to normalize up to BCNF Shipping (ShipN ame, ShipT ype, T ripId, Cargo, P ort, Date) ShipName → ShipT ype TripId → ShipName, Cargo ShipName, Date → TripId, Port and, we can infer TripId, Date → Port

Task 2: Problems 3.4 What conditions must the multivalued dependencies (if any) satisfy for a relation to be in 4NF?

3.5 Is a decomposition to 4NF always dependency preserving and/or lossless?