MySQL

The university course registration system is intended to manage and store information

about students, courses, instructors, and enrollments efficiently. The initial database

design consolidates relevant data into a single, unnormalized relation, potentially leading

to data redundancy and anomalies.

Initial Relation:

R$($StudentID$,$ StudentN ame, CourseID, CourseN ame, InstructorID, InstructorN ame,

Department, Grade$)$

Functional Dependencies:

$1.$ StudentID $->$ StudentN ame

$2.$ CourseID $->$ CourseN ame, Department, InstructorID

$3.$ InstructorID $->$ InstructorN ame, Department

$4.$ StudentID, CourseID $->$ Grade

$5.$ Department $->$ InstructorID

Assignment Tasks:

$1.$ Analyze the Initial Design: Identify and explain potential problems $($data redundancy, update anomalies, insertion anomalies, and deletion anomalies$)$ associated

with the initial design.

$2.$ Normalize to $3$NF: Decompose the given relation R into well$-$structured relations that are in Third Normal Form $(3$NF$).$ Justify each decomposition step with

reference to the functional dependencies and normalization rules.

$3.$ Normalize to BCNF: Further decompose your $3$NF relations into Boyce$-$Codd

Normal Form $($BCNF$),$ if necessary. Explain why each step is required and how it

addresses the limitations of $3$NF$.$

$4.$ Schema Design: For the final set of relations, identify primary keys and foreign

keys. Construct a schema diagram representing your normalized database.

$5.$ Critical Evaluation: Discuss the impact of normalization $($up to BCNF$)$ on the

database$$s efficiency and integrity. Consider scenarios where normalization might

introduce challenges or limitations