Schema Analysis and Normalization

Question 1

Explain 2NF violation and show the new normalized tables

Answer 1

2NF Violation Explanation: In the MARKS table, the moduleName is dependent on the ModuleId, which is not part of the primary key.
Normalized Tables: Separate MARKS into two tables: MARKS(StudentId, ModuleId, mark) and MODULE(ModuleId, moduleName).

Question 2

Explain 3NF violation and show the new normalized tables

Answer 2

3NF Violation Explanation: In the STUDENT table, age is transitively dependent on StudentId (via DateOfBirth).
Normalized Tables: Create a new table STUDENT_INFO(StudentId, DateOfBirth, age) and reference it from the STUDENT table.