Normalization : University Database System - Case Study 1

 

Case Study: University Database System

 

Step 1: Identify Entities and Attributes

 

Entities:

 Student

Course

Instructor

Department

Enrollment

 

Attributes:

Student:

StudentID (Primary Key)

FirstName

LastName

DateOfBirth

Email

DepartmentID (Foreign Key)

 

Course:

CourseID (Primary Key)

CourseName

Credits

InstructorID (Foreign Key)

 

Instructor:

InstructorID (Primary Key)

FirstName

LastName

Email

DepartmentID (Foreign Key)

 

Department:

DepartmentID (Primary Key)

DepartmentName

Location

 

Enrollment:

EnrollmentID (Primary Key)

StudentID (Foreign Key)

CourseID (Foreign Key)

EnrollmentDate

 

Step 2: 1st Normal Form (1NF)

 

Ensure that each attribute contains only atomic (indivisible) values and remove repeating groups.

 

Normalized Data (1NF):

Each entity's table is structured with no repeating groups:

Student Table (1NF):

StudentID       FirstName       LastName        DateOfBirth    Email   DepartmentID

1          John     Smith   1995-03-15      john@example.com    101

2          Alice    Johnson           1996-05-20      alice@example.com    102

Course Table (1NF):

CourseID        Course             Name               Credits            InstructorID

201                  Math                101                  3                      301

202                  History            101                 3                      302

 

Instructor Table (1NF):

InstructorID    FirstName       LastName        Email               DepartmentID

301                  David              White              david@example.com  101

302                  Emily               Brown             emily@example.com   102

 

Department Table (1NF):

DepartmentID             Department     Name               Location

101                              Math                Building          A

102                              History            Building          B

 

Enrollment Table (1NF):

EnrollmentID  StudentID       CourseID        EnrollmentDate

401                              201      20                    23-01-15

402                              2          202                  2023-02-10

 

The data is now in 1st Normal Form (1NF), with no repeating groups.

 

Step 3: 2nd Normal Form (2NF)

Ensure that there are no partial dependencies on the primary key.

 

Normalized Data (2NF):

 

All tables are already in 2nd Normal Form (2NF) because there is no partial dependency. All non-key attributes are fully dependent on the primary key.

 

Step 4: 3rd Normal Form (3NF)

Remove transitive dependencies.

 

Normalized Data (3NF):

All tables are now in 3rd Normal Form (3NF) because there are no transitive dependencies.