[1] Consider the following table: Shipment. Shipment(ShipmentId, ProductId, Quan

Question

[1] Consider the following table: Shipment. Shipment(ShipmentId, ProductId, Quantity, SourceCity, DestinationCity, Distance)

ShipmentId is an unique identifier for a shipment of a quantity of a product (identified by ProductId) from a SourceCity to a DestinationCity. The two cities determine the Distance between them. (For example, the distance between Houston and Dallas is 239.1 miles.

(a) Identify the functional dependencies (FD) of the relation.

(b) What are the candidate keys? (c) What are the non-prime attributes?

(d) What is the highest normal form of the relation?

(e) If it is not in BCNF, convert the relation to relations of BCNF or 3NF.

(2) Consider the following table. Employee(EmployeeId, SSN, FirstName, LastName, Phone, EMail, DepartmentId, ProjectId)

It stores some information about employees. EmployeeId is a unique id of an employee. The social security number, SSN, of an employee is stored for tax purpose. It should also be unique. One name and phone of every employee is recorded but an employee may have multiple email addresses and all should be recorded. An employee works for one department (identified by DepartmentId) but an employee may work on multiple projects (identified by ProjectId).

(a) Identify the functional dependencies (FD) of the relation.

(b) What are the candidate keys?

(c) What are the non-prime attributes?

(d) What is the highest normal form of the relation?

(e) If it is not in BCNF, convert the relation to relations of BCNF or 3NF.

(3) Consider the relation R(A,B,C,D) with two candidate keys: A and BC. List all superkeys. How do you consider what is a superkey?

Explanation / Answer

For question [1] :

(a) below are the dependencies :

Basically functional dependencies are the relation between two or more attributes where one attribute of table can uniquely identify other attribute. So the attribute which can uniquely identify is placed on left while attributes which can be identified are placed on right of FD.

  ShipmentId -> ProductId Quantity SourceCity DestinationCity Distance. (as shipment id is unique for every shipment request hence it can uniquely identify all the attributes present in table)

SourceCity  DestinationCity -> Distance (here distance for the same pair of source and destination city will always be same hence sourcecity and destination city can uniquely identify the distance while reverse is not possible as there can be more than 1 pair of source and destination city with same distance)

other than that about the attribute productid and quantity they are independent of any of these fields values so no dependencies between them.

(b) A Candidate key is minimal super key, i.e. it is the attribute or group of attributes which together can identify the whole tuple of the table or we can say whose value can uniquely fetch tuple from table. here in our case only shipementId is such attribute. Based on shipment id we can fetch all the different values uniquely. By uniquely, i mean that only one tuple will be fetched as result. Hence ShipmentId is the candidate key.

(c) Basically, the attributes which are the parts of key of db table like part of candidate key/super key are prime attributes. We can also identify these attributes as the attributes in the left of the FD of the table. Now the attributes other than prime attributes are non-prime attributes. Here in this table distance, productId,quantity are non prime attributes.

(d) Highest Normal form is 2NF. As here a transitive FD exists. This transitive FD is : SourceCity  DestinationCity -> Distance. For easy understanding we can think of it like if the attributes at left side of a FD are unable to identify all of the attributes of table or unable to fetch unique tuple from table then that FD is transitive dependency and hence highgest normal form is 2NF.

(e) For converting relation in BCNF or 3NF we need to break table in two parts as below :

(ShipmentId, productId, quantity) (shipmentId, sourceCity, destinationcity, distance)

For question [2]:

(a)Thinking in the same manner as first question, FD's are :

EmployeeId -> SSN FirstName LastName Phone EMail DepartmentId ProjectId

SSN -> EmployeeId FirstName LastName Phone EMail DepartmentId ProjectId

SSN -> EmployeeId

EmployeeId -> SSN

EmployeeId -->> EMail.

SSN -> EMail

EmployeeId DepartmentId -->> ProjectId. (it's a multivalued dependency as for single value of employeeid and departmentid there are multiple values of projectid).

(b) Candidate keys are : EmployeeId, SSN

(c) Non prime attributes are : FirstName LastName Phone EMail ProjectId

(d) Highest normal form is 2NF as again there are many transitive dependencies.

(e)For converting table in BCNF or 3NF we need to ensure that all the transitive dependencies are removed and the attributes at the left should be super key. So below decomposed form is in BCNF or 3NF :

(EmployeeId SSN FirstName LastName Phone DepartmentId) (EmployeeId EMail) (EmployeeId DepartmentId ProjectId)

For question [3] :

Superkeys are the super set of candidatekeys. In other words all the possible combination of keys which can identify the tuples or row of table uniquely is superkey. Following are the superkeys for the table : EmployeeId, SSN, EmployeeId DepartmentID, SSN DepartmentID.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.