Database question

ICS 324 – HW 3

Due November 27, 2021

Solve the following normalization questions.

  1. [4 pts] Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and functional dependencies
  • {A,B} à {C}
  • {A} à {D,E}
  • {B} à {F} 
  • {F} à {G,H}  
  • {D} à {I,J}

What is the key for R? Decompose R into 2NF and then 3NF relations.

  • [4 pts] Consider a relation R = {A, B, C, D, E} with the following dependencies.
  • {A, B} à {C}
  • {C, D} à {E}
  • {D, E} à {B}

Is AB a candidate key of R?  What about ABD? Explain your answer.

  • [4 pts] 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}

Try to determine which sets of attributes form keys of R. How would you normalize this relation?

  • [4 pts] 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 DISK_DRIVE 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, the tuple Disk_drive (‘1978619’, ‘WesternDigital’, ‘A2235X’, ‘765234’, 500, ‘CompUSA’) specifies that WesternDigital made a disk drive with serial number 1978619 and model number A2235X, released in batch 765234; it is 500GB and sold by CompUSA. Write each of the following dependencies as an FD:

  1. The manufacturer and serial number uniquely identifies the drive.
  2. A model number is registered by a manufacturer and therefore can’t be used by another manufacturer.
  3. All disk drives in a particular batch are the same model.
  4. All disk drives of a certain model of a particular manufacturer have exactly the same capacity.
  • [4 pts] Consider the following relation:

CAR_SALE (Car_id, Option_type, Option_listprice, Sale_date, Option_discountedprice)

This relation refers to options installed in cars (e.g., cruise control) that were sold at a dealership, and the list and discounted prices of the options. If

  • CarID → Sale_date
  • Option_type → Option_listprice
  • CarID, Option_type → Option_discountedprice

Argue using the generalized definition of the 3NF that this relation is not in 3NF. Then argue from your knowledge of 2NF, why it is not even in 2NF.

  • [4 pts] Consider relation R = {A, B, C, D, E} with the following dependencies.
  • {A} → {C}
  • {B, D} → {A}
  • {D} → {E}

Decompose R into BCNF. In each step, explain which functional dependency you used to
decompose and explain why further decomposition is needed. And also make sure you indicate the keys for each relation.

× How can I help you?