COMP2400/COMP6240 – Relational Databases Assignment 2

COMP2400/COMP6240 – Relational Databases

Assignment 2
Due date: 10am, 27 September 2016

This assignment will be marked out of 10. It will count for 10% of the final grade. Below you will find 4 questions to reach this score. Marks are assigned for the process of finding a solution, not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution.

Instructions:

• This assignment should be done individually or by a pair of students.
• Your submission must include Assignment Cover Sheet, which is available from the

  course website on Wattle.

• Hand written submissions will not be marked.
• You need to submit your assignment as a hard copy to the assignment box in the student foyer (i.e., on the ground floor of the CSIT building). You are also required to submit an electronic copy on Wattle.
• Late submission, unless authorized by the lecturers, will attract the penalties 5% per day. If submitted after one week of its deadline, the assignment will not be marked.
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. Good luck and enjoy the time you will spend on this assignment.

  Question 1 1 Mark

  Suppose R is a relation with four attributes ABCD and the only keys are AB and BD. How many superkeys R has? Explain why.

1

2 Assignment 2

Question 2 2 Marks

Consider a relation schema R with five attributes ABCDE and the following functional dependencies on R:

Σ = {A → C,C → E,EB → A,A → E,AD → B}

1. 2.1  Find all the keys of R with respect to Σ. Justify your answer (i.e., include the steps

   used for finding the keys).

   (1 Mark)

2. 2.2  Does Σ |= CD → B hold? If it holds, explain why it holds. If it does not hold, give a relation that contains only two tuples, as a counterexample, to show it does not hold.

   (0.5 Mark)

3. 2.3  Does Σ contain any redundant functional dependencies? A functional dependency X→Y isredundantifweremoveitfromΣ,andwecanstillinferX→Y fromthe functional dependencies in Σ − {X → Y }. Justify your answer.

   (0.5 Mark)

Question 3 2 Marks

Consider a relation schema R with five attributes ABCDE and the following functional dependencies on R:

Σ = {AB → C,BD → E,ACD → E,AC → B}

1. (1)  Is the decomposition {ABC,BCDE} dependency-preserving? Justify your answer.

   (1 Mark)

2. (2)  Is the decomposition {ABC,BCDE} lossless? Justify your answer. If your answer is negative, show how a relation over ABCDE is not lossless after being decomposed into two relations over ABC or BCDE.

   (1 Mark)

COMP2400/COMP6240 – Relational Databases 3

Question 4 5 Marks

An information system handling candidates, employers and job applications was requested by a job recruitment agency. The IT department created a database schema containing the following relation schema Job Application:

– Candidate Name
– Candidate DoB
– Candidate Email
– Candidate EducationLevel – Job No

– Job Position
– Job Type
– Job Salary
– Job Location
– Closing Date
– Employer Name
– Employer Address
– Number of Employees – Application Date

– Application Documents

The IT department identified the data requirements from the job recruitment agency using the following functional dependencies:

1. (FD1)  {Candidate Name, Candidate DoB} → {Candidate Email, Candidate EducationLevel};
2. (FD2)  {Candidate Email} → {Candidate Name, Candidate DoB, Candidate EducationLevel};
3. (FD3)  {Job No} → {Job Position, Employer Name,Job Type,Job Salary,Closing Date};
4. (FD4)  {Employer Name} → {Employer Address, Number of Employees};
5. (FD5)  {Employer Address} → {Job Location};
6. (FD6)  {Candidate Name, Candidate DoB, Candidate Email, Job No} →

   {Application Date, Application Documents}.

To better understand the above data requirements over the Job Application relation schema, the IT department also provided the following sample relation:

COMP2400/COMP6240 – Relational Databases 5

1. 4.1  Is the Job Application relation schema a good solution? Explain your answer. If you think it is not a good design, discuss at least two potential problems.

   (0.5 Mark)

2. 4.2  We want to redesign the Job Application relation schema. Can you identify a lossless BCNF decomposition for Job Application, which only preserves FD1, FD2, FD3, FD4, and FD6, i.e., FD5 cannot be preserved? You need to include the main steps used for identifying your BCNF decomposition and explain why your decomposition can preserve all functional dependencies except for FD5.

   (2 Marks)

3. 4.3  Is the Job Application relation schema in 3NF? If not, identify a lossless and de- pendency preserving 3NF decomposition for the current relation schema. You need to explain why the relation schema Job Application is or is not in 3NF, and include the steps used for identifying your 3NF decomposition.

   (2 Marks)

4. 4.4  Should we consider 3NF instead of BCNF when redesigning Job Application? Why or why not?

+++++

(0.5 Mark)