CS 377 - Database Systems

Hw5: Relational Database Design and Normalization

Out: Thursday, October 20

Due: Thursday, October 27, 11:59pm

Extended: Monday, October 31, 11:59pm

1.
Consider
the following relation:

CAR_SALE(Car#,
Date_sold, Salesman#, Commision%,
Discount_amt)

Assume that a car may be sold by
multiple salesmen and hence {CAR#, SALESMAN#} is the one and only key.
Additional dependencies are:

Date_sold ->Discount_amt

Salesman# ->commission%

a)
Based
on the given key and functional dependencies, is the relation in 1NF, 2NF, or
3NF? For each normal form, show why or why not, and normalize it if not.

2.
Consider
the following relation that holds schedules of courses and sections at Emory
university:

Schedule(CourseNo, SecNo,
OfferingDept, CreditHours, CourseLevel, Instructor, Semester, Year, Days_Hours, RoomNo, NoOfStudents)

Suppose
that the following functional dependencies hold on the above relation:

{CourseNo} -> {OfferingDept, CreditHours, CourseLevel}

{CourseNo, SecNo, Semester, Year}
->{Days_Hours, RoomNo, NoOfStudents, Instructor}

{RoomNo, Days_Hours, Semester,
Year} -> {Instructor, CourseNo, SecNo}

a)
Find
two keys for the relation based on the functional dependencies. Show the
intermediate steps or why they are keys.

b)
Given
the two keys you find, and the given functional dependencies, normalize the
relation to achieve 2NF, 3NF, and BCNF.
For each normal form, show the violating functional dependencies.

3.
Consider
the following relation DiskDrive where each tuple contains information about a disk drive with a serialNumber, made by a manufacturer, with a particular
model, released in a certain batch, having a certain storage capacity, and is
sold by a certain retailer.

DiskDrive(serialNumber, manufacturer, model, batch, capacity,
retailer)

Consider the following assumptions:

1)
The
manufacturer and serial number uniquely identifies the drive

2)
A
model number is registered by a manufacturer and hence can’t be used by
another manufacturer.

3)
All
disk drives released in a particular batch by a particular manufacturer are the
same model.

4)
All
disk drives of a particular model have exactly the same capacity

a)
Convert
the assumptions into formal functional dependencies.

b)
Find
two keys for the relation based on the functional dependencies. Show the
intermediate steps or why they are keys.

c)
Given
the two keys you find and the functional dependencies,
show why or why not the relation is in 2NF, 3NF, and BCNF and normalize it if
necessary to achieve the normal forms.
For BCNF, use the algorithm 16.5 directly on the original relation and
compare it with the result you get from normalizing the intermediate relation
that achieves 3NF.

·
Please
place your solution in your cs377 directory ~/cs377/hw5/hw5.pdf