➢ Assignment Title: Database Normalization
Name: Muhammad Hafeez
Roll No: 10
Class: ADS IT
➢ Student Enrollment System Database
Normalization.
Task Overview:
The system initially contains a single unnormalized table, StudentCourses,
that stores student and course information. This table must be normalized
to the Second Normal Form (2NF) to eliminate redundancy and optimize
data storage.
1. Create the Initial Unnormalized Table:
The following SQL statement creates a single table, StudentCourses, to
store the initial data.
Sample Data Insertion:
2. Normalization to 2NF:
The StudentCourses table has partial dependencies that violate 2NF. The goal
is to decompose this table into three separate tables: Students, Courses, and
Enrollments to eliminate redundancy.
Students Table:
Stores student information.
Courses Table:
Stores course information.
Enrollments Table:
Tracks which students are enrolled in which courses.
3. Insert Data into Normalized Tables
After creating the tables, sample data can be inserted into each table:
SQL Code for Courses Table:
SQL Code for Enrollments Table:
4. Perform SQL Operations
SELECT Queries
1. Retrieve all students and the courses they are
enrolled in:
Retrieve all courses and their respective instructors:
UPDATE Queries
Update a student’s name based on their StudentID:
Change the instructor for a course:
DELETE Queries
Delete a student based on their StudentID:
Remove a course from the database:
Conclusion:
In this task, we successfully normalized the StudentCourses table into 2NF by
creating separate Students, Courses, and Enrollments tables. We also performed
SQL queries to insert, update, and delete data, ensuring that the system is optimized
and free from redundancy.
Thanks, Horse Rider.
