Introduction to Database Normalization 4.pptx

hgjguyfugfftky

1. Detailed Example of Database Normalization Let’s walk through the process of normalizing a database, starting from an unnormalized table and progressing through the first, second, and third normal forms (1NF, 2NF, and 3NF). Step 1: Unnormalized Table (UNF) Suppose we have a table that stores information about students, the courses they are enrolled in, and their instructors:

2. Issues: The table contains multiple values in a single record, violating atomicity. Data redundancy is present because the same instructor is listed multiple times. The table has potential update, insertion, and deletion anomalies. Student I Student Name Course 1 Instructor 1 Course 2 Instructor 2 1 Alice Math Dr. Smith Science Dr. Brown 2 Bob Math Dr. Smith History Dr. White 3 Charlie English Dr. Black

3. Step 2: First Normal Form (1NF) To bring this table into 1NF, we must eliminate repeating groups and ensure that each field contains atomic values: Student ID Student Name Course Instructor 1 Alice Math Dr. Smith 1 Alice Science Dr. Brown 2 Bob Math Dr. Smith 2 Bob History Dr. White 3 Charlie English Dr. Black Improvements: Each column now contains atomic values. Each row represents a unique combination of Student ID and Course . Remaining Issues: Still some redundancy ( e.g., Dr. Smith is repeated for Math ).

4. Student ID Student Name Course 1 Alice Math 1 Alice Science 2 Bob Math 2 Bob History 3 Charlie English Step 3: Second Normal Form (2NF) To achieve 2NF, we need to remove partial dependencies. This means that all non-key attributes should depend on the entire primary key, not just part of it. Here, the primary key is a combination of Student ID and Course . We should move the Instructor column to a new table because it depends only on the Course , not the combination of Student ID and Course . Student-Course Table:

5. Course Instructor Math Dr. Smith Science Dr. Brown History Dr. White English Dr. Black Improvements: The Instructor information is now stored separately, reducing redundancy. The Student Course table now only contains data that is fully dependent on its primary key ( Student ID , Course ). Remaining Issues: StudentName in the Student-Course table is still dependent on StudentID alone, not the entire primary key. Course-Instructor Table:

6. StudentID StudentName 1 Alice 2 Bob 3 Charlie Student ID Course 1 Math 1 Science 2 Math 2 History 3 English Step 4: Third Normal Form (3NF) To achieve 3NF, we remove transitive dependencies, ensuring that non-key attributes depend only on the primary key and not on other non-key attributes. We will create a separate table for students: Student Table: Student-Course Table :

7. Course Instructor Math Dr. Smith Science Dr. Brown History Dr. White English Dr. Black Final Structure: The Student table holds unique student data. The Student-Course table links students to their courses. The Course-Instructor table links courses to their respective instructors. Improvements: The database is now in 3NF. There is no redundant data, and all non-key attributes are fully dependent on the primary key. The structure eliminates update, insertion, and deletion anomalies. Course-Instructor Table:

8. Summary of Normalization Process 1NF : Eliminated repeating groups by ensuring atomic values in each column. 2NF : Removed partial dependencies by separating out attributes dependent only on part of the composite key. 3NF : Eliminated transitive dependencies by ensuring non-key attributes depend only on the primary key. By following this normalization process, the database design is now more efficient, scalable, and less prone to anomalies..

9. For Your Attention Thank You Presentation - 2024 Dr . Sameera Abu Ghalyoun