Activity 8: Normalize the Denormalize table
Step 1: Analyzing the Denormalized STUDENT Table
Let's begin by looking at an example of a denormalized STUDENT table. In a denormalized database, multiple entities are stored together, leading to redundancy. Here’s an example schema for the denormalized table:
| Column | Type |
| studentId | INT |
| studentName | VARCHAR(255) |
| gender | VARCHAR(255) |
| dateOfBirth | DATE |
| address, city | VARCHAR(255) |
| postalCode | INT |
| phoneNumber | VARCHAR(255) |
| VARCHAR(255) | |
| guardianName | VARCHAR(255) |
| guardianPhone | VARCHAR(255) |
| courseName | VARCHAR(255) |
| courseCredits | INT |
| departmentName | VARCHAR(255) |
| semester | INT |
| yearOfEnrollment | INT |
| gpa | FLOAT |
| projectTitle | VARCHAR(255) |
| projectSupervisor | VARCHAR(255) |
| scholarshipStatus | VARCHAR(255) |
| scholarshipType | VARCHAR(255) |
| extraCurricularName | VARCHAR(255) |
| advisorName | VARCHAR(255) |
| roomNumber | VARCHAR(255) |
| attendancePercentage | FLOAT |
In this table, we have multiple unrelated entities (such as Course, Department, Project, Scholarship, Extracurricular Activities, Advisor, and Attendance) stored alongside basic student information. As a result, student details (e.g., course name, advisor name) are repeated for each student. This redundancy makes the database inefficient and prone to errors.
Step 2: Identifying Distinct Entities
From the denormalized table above, we can identify several distinct entities that should be separated into their own tables:
Student: Contains basic student information, such as student ID, name, gender, and contact details.
Course: Includes course-specific data like course name, course credits, and possibly other attributes (such as course schedule or professor).
Department: Details about the academic department to which the student belongs.
Project: Information related to student projects, including project titles and project supervisors.
Scholarship: Scholarship details, such as scholarship status and type, associated with eligible students.
Extracurricular Activities: Details of the activities that the student participates in, such as sports or clubs.
Advisor: Information about the student's academic advisor.
Attendance: Attendance records, including attendance percentage and related data.
Step 3: Creating Normalized Tables
To normalize the data, we would break the original table into multiple smaller, related tables based on the identified entities. Here’s how each table would look:
Step 4: Establishing Relationships
In the normalized schema, relationships between the tables are managed using foreign keys (FK). These foreign keys link a record in one table to a related record in another table, ensuring referential integrity. For example:
The Student table is linked to the Project, Scholarship, Extracurricular Activities, Advisor, and Attendance tables via the studentId foreign key.
The Department table is linked to the Student table through a separate Department-Student Relationship table.
The Course table can be linked to students either directly or via an intermediary table (if students can enroll in multiple courses).
Conclusion
Normalization plays a critical role in organizing database structures for better efficiency, data integrity, and scalability. By breaking down a denormalized table (like the one for students) into smaller, logically organized tables, we reduce redundancy, ensure consistency, and improve query performance. Each entity is stored in its own table, and relationships between entities are clearly defined through the use of foreign keys. This approach minimizes data duplication and ensures that updates, deletions, and insertions are handled in a more consistent and reliable way.