Lab: Database Design

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

You may work either individually or with a partner.

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. Give a candidate key for each relation.
Decompose R into 3NF relations. Give a candidate key for each relation.

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. Give a candidate key for each relation.
Which normal form is your decomposed relational schema in?
Give a candidate key for R.

Submit

Submit your response on Gradescope. If you worked with a partner, only one of you should submit.

This lab will be graded as part of your assignments grade.