Introduction
代写关系型数据库的纯理论作业，涉及关系推导，集合推导以及范式推导。
Instructions
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.
Question 1
Suppose R is a relation with four attributes ABCD and the only keys are AB and BD. How many superkeys R has? Explain why.
Question 2
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}
- Find all the keys of R with respect to Σ. Justify your answer (i.e., include the steps used for finding the keys).
- 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.
- Does Σ contain any redundant functional dependencies? A functional dependency X → Y is redundant if we remove it from Σ, and we can still infer X → Y from the functional dependencies in Σ − {X → Y}. Justify your answer.
Question 3
Consider a relation schema R with five attributes ABCDE and the following functional dependencies on R:
Σ = {AB → C, BD → E, ACD → E, AC → B}
- Is the decomposition {ABC, BCDE} dependency-preserving? Justify your answer.
- 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.
Question 4
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.
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.
- 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.
- 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.
- Is the Job Application relation schema in 3NF? If not, identify a lossless and dependency 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.
- Should we consider 3NF instead of BCNF when redesigning Job Application? Why or why not?