| Printed Name: | |
| UFID: | |
| Email Address: |
Instructions: Please provide your answers to the questions of the following pages on separate sheets of paper. Mark to which question each answer belongs. Then scan your work into PDF by using either a scanner or a suitable scanner app on your smartphone. Note that only the PDF format is allowed and that your submission must be a single PDF file. Finally, upload your PDF file into Gradescope and follow the instructions there.
Question 1 (Normalization)
- [5 points] Consider the following table CollectionBooks and explain if it is in the first normal form. If not, normalize the table to the first normal form.
| CollectionBooks | ||||
| Book | Author | Price | Publisher | Year |
| Book A | Author X, Author Y | 20 | Publisher Z | 2020 |
| Book B | Author X, Author Z | 15 | Publisher Y | 2022 |
| Book C | Author Y, Author Z | 25 | Publisher Z | 2019 |
- [5 points] Consider the following table StudentCourses. Explain if it is in the second normal form. If not, explain which anomalies can occur and normalize the table to the second normal form. Primary keys are underlined.
| StudentCourses | |||
| StudentID | StudentName | CourseID | CourseName |
| 1 | Alice | C1 | Math |
| 1 | Alice | C2 | English |
| 2 | Bob | C1 | Math |
| 2 | Bob | C3 | Physics |
| 3 | Charlie | C2 | English |
| 3 | Charlie | C3 | Physics |
- [5 points] Give the reason why the following table is not in the third normal form and normalize it.
| OrderInfo | |||
| Order ID | Product Name | Product Category | Customer Name |
| 1 | T-shirt | Clothing | John Smith |
| 1 | Jeans | Clothing | John Smith |
| 2 | T-shirt | Clothing | Mary Johnson |
| 2 | Shoes | Footwear | Mary Johnson |
- [5 points] Explain if the tables you obtained for the previous question is in BCNF and, if not, normalize it to BCNF.
Question 2 (Normal Forms)
- [5 points] Let R(A,B,C,D,E) be a relation schema, and let S ={CD → E,B → C,AB → CDE} be a set of functional dependencies. Determine if R is in 2NF. Explain your answer. If R is not in 2NF, normalize it to 2NF.
- [5 points] Explain the conditions of 3NF. Are the relation schemas from the answer of the previous question in 3NF? Explain your answer. If not in 3NF, normalize it.
- [5 points] Let R(A,B,C,D,E) be a relation schema, and let F ={AB → CDE,CD → ABE,E → D} be a set of functional dependencies. Determine the highest normal form of R. Check from 2NF to BCNF.
- [5 points] Normalize the relation schema R of the previous question to the next higher normal form.
Question 3 (Lossless Join Decomposition & Dependency Preservation)
- [10 points] Let R(A,B,C,D,E,F) be a relation schema, and let S ={A → B,BC → D,E → C,D → AE,C → F} be a set of functional dependencies. Use the Chase test to determine if the following decomposition is lossless.
Decomposed relations: R1(AC),R2(ABD),R3(DEF),R4(CF)
- [5 points] Let R(A,B,C,D,E,F) be a relation schema, and let S ={A → B,BC → D,E → C,D → A,B → D} be a set of functional dependencies. Use the Nonadditive Join Test for Binary Decomposition to determine if the following decomposition is lossless.
Decomposed relations: R1(ACDE),R2(BDF)
- [5 points] Let R(A,B,C,D,E,F) be a relation schema, and let S ={A → B,BC → D,E → C,D → A,B → D} be a set of functional dependencies. Use one of the algorithms introduced in the lecture to determine if the following decomposition is dependency preserving.
Decomposed relations: R1(ABCD),R2(ABDF)
Question 4 (Normalization) [25 points]
- [13 points] Let R(A,B,C,D,E) be a relation schema, and let F = {A → BC,CD → AE,ABD → CD,CE → AD} be a set of functional dependencies. Decompose R into 3NF by using the 3NF synthesis algorithm. Show all steps and argue precisely. Is this decomposition also in BCNF? If so, why? If not, explain the reasons and decompose into BCNF.
- [12 points] Let R(A,B,C,D,E) be a relation schema, and let S ={AB → CD,D → E,A → C,B → D} be a set of functional dependencies. Decompose R into BCNF by using the BCNF decomposition algorithm introduced in the lecture. Show all steps and argue precisely.
Question 5 (Integrity Constraints) [15 points]
Consider the following tables:
- Employee (eID, name, email, dID, salary)
- Department (dID, name, managerID)
- Project (pID, name, startDate, endDate, dID)
- Employee Project (eID, pID)
- [5 points] Write an assertion check for the following condition: the total number of employees working on a project is less than or equal to 10.
You want to keep track of the number of employees who have worked on a project in a new table named Employee Projects Count. The table has two attributes that are pID and numOfEmployees.
- [5 points] Write a SQL statement to create the table Employee Projects Count. You should add all constraints.
- [5 points] Write a trigger that automatically inserts an entry into the Employee Projects Count table and updates the count whenever a new project is added to the Employee Project
