CSCI235 - Lab2 - Solved

Tasks 
Task 1 (0.4 mark)
 

Read and analyze the relational schemas listed below. For each one of the relational schemas find all nontrivial functional dependencies valid in the schemas.  Then, for each schema, explain which functional dependencies cause the redundancies (if any).

 

STUDENT(snumber, first-name, last-name, ccode)
A relational table STUDENT contains information about the students and the courses enrolled by the students. A course (ccode) is enrolled by more than one students (snumber) and each student enrols several course. Student number (snumber) uniquely identifies each students and course code (ccode) uniquely identifies each course. The first (first-name) and the last (last-name) names describe the students.

 

HOTEL(name, city, capacity, enumber, salary)
A relational table HOTEL contains information about the hotels and employees working in the hotels. A hotel is identified by a pair of attributes (name, city) and it is also described by the total number of rooms available (capacity). Each employee is identified by employee number (enumber) and it is described by a salary (salary).  

 

WAREHOUSE(wname, address, part, quantity)
A relational table WAREHOUSE contains information about the names of warehouses (wname) located at the given addresses (address). Each warehouse is located at one address and there is only one warehouse at each address. Parts (part) are stored in a warehouse. A quantity of each part is determined by a value of attribute quantity. 

 

LIBRARY(cnumber, title, price, isbn)
A relational table LIBRARY contains information about the books available from a library. Each copy of a book is uniquely identified by call number (cnumber). A book has one title (title) and one price (price). International system book number (isbn) is commonly used to uniquely identify a book.

 


                                                                                                                                                             

Task 2 (0.6 mark)
 

Perform the following steps and save the outcomes in a file solution2.pdf.

 

(1) Consider a relational schema R(A, B, C, D, E) and the following set of functional  dependencies valid in the schema,

 

{A ® B, C  ® A} 
 

List all derivations of functional dependencies that lead to the identification of minimal keys. List all minimal keys valid in a relational schema. Note, that a schema can have more than one minimal key.  

 

(2) Consider a relational schema R(A, B, C, D, E) and the following set of functional  dependencies valid in the schema,

 

{A ® E, E ® C, CD ® A} 
 

List all derivations of functional dependencies that lead to the identification of minimal keys. List all minimal keys valid in a relational schema. Note, that a schema can have more than one minimal key.  

 

 (3) Consider a relational schema R(A, B, C, D, E) and the following set of functional  dependencies valid in the schema,

 

{D ® A, DA ® B, DE ® ABC} 
 

List all derivations of functional dependencies that lead to the identification of minimal keys. List all minimal keys valid in a relational schema. Note, that a schema can have more than one minimal key.