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}
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 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)
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) Is the decomposition {ABC,BCDE} dependency-preserving? Justify your answer.
(1 Mark)
(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:
(FD1) {Candidate Name, Candidate DoB} → {Candidate Email, Candidate EducationLevel};
(FD2) {Candidate Email} → {Candidate Name, Candidate DoB, Candidate EducationLevel};
(FD3) {Job No} → {Job Position, Employer Name,Job Type,Job Salary,Closing Date};
(FD4) {Employer Name} → {Employer Address, Number of Employees};
(FD5) {Employer Address} → {Job Location};
(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:
Job Application
4 Assignment 2
Candidate Name
Candidate DoB
Candiate 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
Claire Gillespie Maria Rasberry DP Zimmer Claire Gillespie Claire Gillespie Michael G. Dow Michael G. Dow Dr. Dan Reed Dr. Dan Reed Joann J. Cantor Nora Alvardo John M. Conklin Xin Li
07/10/1979 claireg@gmail.com 26/03/1968 maria@hotmail.com 13/04/1961 zimmer61@aol.com 07/10/1979 claireg@gmail.com 07/10/1979 claireg@gmail.com 26/08/1970 michaeld@gmail.com 26/08/1970 michaeld@gmail.com 23/06/1963 dan.reed@cam.ac.uk 23/06/1963 dan.reed@cam.ac.uk 08/06/1992 jjcantor@cantor.com 11/10/1993 u6543210anu.edu.au 11/10/1987 johncs@gmail.com 26/08/1995 lixin1995@qq.com
Bachelor Bachelor Diploma Bachelor Bachelor Master Master PhD PhD Bachelor Bachelor PhD Bachelor
1001034 Admin Officer 1001034 Admin Officer 1001035 IT Support 1001036 Software Engineer 1001100 HR Manager 1009000 Software Engineer 1001036 Software Engineer 1008282 CECS Professor 987020 Senior Researcher 1003847 COMP1100 Tutor 1003848 COMP1110 Tutor 1003947 Postdoc (CS) 1000080 Business Intern
Full Time Full Time Part Time Full Time Full Time Full Time Full Time Full Time Full Time Casual Casual Full Time Full Time
$70,000 p.a. $70,000 p.a. $433 per day $135,000 p.a. $95,000 p.a. $60,000 p.a. $135,000 p.a. $140,000 p.a. $160,000 p.a. $40 per hour $40 per hour $85,000 p.a. $3,500 per mo.
Canberra Canberra Canberra Sydney Canberra Canberra Sydney Canberra Sydney Canberra Canberra Canberra Canberra
31/10/2016 ANU 31/10/2016 ANU 31/10/2016 ANU 09/12/2016 Google 31/10/2016 ANU 31/11/2016 IBM 09/12/2016 Google 30/06/2017 ANU 31/11/2016 Google 01/07/2016 ANU 01/07/2016 ANU 30/06/2017 ANU 31/10/2016 KPMG
ANU, Acton 4000 ANU, Acton 4000 ANU, Acton 4000 48 Pirrama Rd. 1200 ANU, Acton 4000 28 Sydney Ave. 150 48 Pirrama Rd. 1200 ANU, Acton 4000 48 Pirrama Rd. 1200 ANU, Acton 4000 ANU, Acton 4000 ANU, Acton 4000 20 Brid Circuit 500
01/08/2016 resume.pdf 03/08/2016 maria resume.pdf 30/08/2016 cover letter.pdf 01/08/2016 c.gillespie.pdf 01/08/2016 resume.pdf 01/08/2016 michael-ibm.pdf 13/07/2016 michael-google.pdf 01/05/2016 cv.pdf
06/05/2016 danreed.pdf 23/06/2016 NULL
30/06/2016 nora cv.doc 23/05/2016 cv.pdf
15/08/2016 lixin.doc
JOB APPLICATION
COMP2400/COMP6240 – Relational Databases 5
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)
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)
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 Should we consider 3NF instead of BCNF when redesigning Job Application? Why or why not?
+++++
(0.5 Mark)