Description

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: R₁(AC),R₂(ABD),R₃(DEF),R₄(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: R₁(ACDE),R₂(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: R₁(ABCD),R₂(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