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.  

Submission

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