Lab 6: Database Design Practice

This lab is designed to help you practice designing relational database schema, using the concepts of functional dependencies and normalization.

You may work with your project team.

Instructions

Problem 1

Consider a relation: $R(A, B, C, D, E)$ with the following dependencies:

$AB \rightarrow C, CD \rightarrow E, DE \rightarrow B$

Is AB a candidate key of this relation? Explain.
Is ABD a candidate key? Explain.
Is ABCDE a candidate key? Explain.

Problem 2

This exercise asks you to convert business statements into dependencies. Consider the relation:

DISK_DRIVE (Serial_number, Manufacturer, Model, Batch, Capacity, Retailer)

Each tuple in the relation contains information about a disk drive with a unique serial number, made by a manufacturer, with a particular model number, released in a certain batch, which has a certain storage capacity and is sold by a certain retailer. For example, consider the tuple:

Disk_drive (‘1978619’, ‘WesternDigital’, ‘A2235X’, ‘765234’, 500, ‘CompUSA’)

This tuple specifies that WesternDigital made a disk drive with serial number 1978619 and model number A2235X, released in batch 765234, it is 500GB, and it is sold by CompUSA.

Write each of the following dependencies as a functional dependency:

The manufacturer and serial number uniquely identifies the drive.
A model number is registered by a manufacturer and therefore can’t be used by another manufacturer.
All disk drives in a particular batch are the same model.
All disk drives of a certain model of a particular manufacturer have exactly the same capacity.

Problem 3

Consider the universal relation:

$R = \{A, B, C, D, E, F, G, H, I, J\}$

And the set of functional dependencies:

$F = \{\{A, B\}\rightarrow\{C\}, \{A\}\rightarrow\{D, E\}, \{B\}\rightarrow\{F\}, \{F\}\rightarrow\{G, H\}, \{D\}\rightarrow\{I, J\}\}$

Give a candidate key for R
Decompose R into 2NF relations
Decompose R into 3NF relations

Problem 4

Consider the same relation schema:

$R = \{A, B, C, D, E, F, G, H, I, J\}$

with the same set of functional dependencies as in the previous problem:

$F = \{\{A, B\}\rightarrow\{C\}, \{A\}\rightarrow\{D, E\}, \{B\}\rightarrow\{F\}, \{F\}\rightarrow\{G, H\}, \{D\}\rightarrow\{I, J\}\}$

Determine:

Whether each decomposition has the dependency preservation property, with respect to F
Whether each decomposition has the lossless join property, with respect to F
Which normal form each relation scheme is in (1NF, 2NF, 3NF, BCNF, 4NF)

$D_1 = \{R_1,R_2,R_3,R_4,R_5\}$
$R_1 = \{A,B,C\}$
$R_2 = \{A,D,E\}$
$R_3 = \{B,F\}$
$R_4 = \{F, G, H\}$
$R_5 = \{D, I, J\}$
$D_2 = \{R_1, R_2, R_3\}$
$R_1 = \{A, B, C, D, E\}$
$R_2 = \{B, F, G, H\}$
$R_3 = \{D, I, J\}$
$D_3 = \{R_1,R_2,R_3,R_4,R_5\}$
$R_1 = \{A,B,C,D\}$
$R_2 = \{D,E\}$
$R_3 = \{B,F\}$
$R_4 = \{F, G, H\}$
$R_5 = \{D, I, J\}$

Problem 5

Consider the relation R, which has attributes that hold schedules of courses and sections at a university:

R = {Course_no,
     Sec_no,
     Offering_dept,
     Credit_hours,
     Course_level,
     Instructor_ssn,
     Semester,
     Year,
     Days_hours,
     Room_no,
     No_of_students}

Suppose that the following functional dependencies hold on R:

{Course_no} -> {Offering_dept, Credit_hours, Course_level}
{Course_no, Sec_no, Semester, Year} -> {Days_hours, Room_no, No_of_students, Instructor_ssn}
{Room_no, Days_hours, Semester, Year} -> {Instructor_ssn, Course_no, Sec_no}

Normalize R to the greatest extent possible, while preserving all functional dependencies.
Which normal form is your decomposed relational schema in?
Give a candidate key for R.

Submit

If you worked with your team, only one of you should upload the PDF to Moodle. You should submit a PDF containing:

The names of your teammates
Your answers

The assignment will be graded for accuracy.