Database Normalization in MS Access

Normalization divides data into appropriate tables and connects those tables using keys and relationships. Instead of storing the same information repeatedly, related information is stored in its proper table.

Example of an Unnormalized Table

Problems with this design include:

• Fixed columns limit the number of courses per student.
• Adding more courses may require changing the table structure.
• Course information may be repeated unnecessarily.
• Updating course details in multiple places can cause inconsistencies.

• Reduces unnecessary data duplication.
• Improves data consistency.
• Makes updating information easier.
• Helps prevent insertion, update, and deletion anomalies.
• Creates clearer relationships between tables.
• Makes a database easier to maintain as it grows.

Example

Suppose the course name ADCA is stored in hundreds of student records. If the course name changes, many records may need updating. If course details are stored once in a Courses table, the course name can be updated in one place.

1. Insertion Anomaly

An insertion anomaly occurs when it is difficult to add one piece of information without adding unrelated data.

Example: You want to add a new course, but your table requires a student record before you can store the course information.

2. Update Anomaly

An update anomaly occurs when the same information is stored in multiple places and all copies must be changed.

Example: The name of a course appears in many records. Updating only some records leaves inconsistent names.

3. Deletion Anomaly

A deletion anomaly occurs when deleting one record accidentally removes other information that should have been retained.

Example: If the only student enrolled in a course is deleted from a combined table, the course information may also be lost.

A table is in First Normal Form (1NF) when each field contains a single value and repeating groups are not stored as multiple columns or multiple values in one field.

Table Not in 1NF

The Courses field contains multiple course values in one field.

Table in 1NF

Each row now stores one student-course combination. In a real database, a suitable key or a separate enrollment table should identify each combination.

A table is in Second Normal Form (2NF) when it is in 1NF and every non-key attribute depends on the whole candidate key, not just part of a composite key.

Example

Suppose an Enrollment table uses the combination of StudentID and CourseID as its primary key.

Enrollment
--------------------------------
StudentID       Composite Key
CourseID        Composite Key
StudentName
CourseName
EnrollmentDate

StudentName depends only on StudentID, while CourseName depends only on CourseID. These are partial dependencies on parts of the composite key.

Improved Table Structure

Students Table

Students
-------------------------
StudentID       Primary Key
StudentName

Courses Table

Courses
-------------------------
CourseID        Primary Key
CourseName

Enrollment Table

Enrollment
-------------------------
StudentID       Foreign Key
CourseID        Foreign Key
EnrollmentDate

Primary Key: StudentID + CourseID

A table is in Third Normal Form (3NF) when it satisfies 2NF and non-key attributes do not depend transitively on a candidate key.

Example

Consider the following Students table:

Students
--------------------------------
StudentID
StudentName
DepartmentID
DepartmentName

StudentID determines DepartmentID, and DepartmentID determines DepartmentName. Therefore, DepartmentName depends on StudentID through DepartmentID.

Improved Structure

Students Table

Students
-------------------------
StudentID       Primary Key
StudentName
DepartmentID    Foreign Key

Departments Table

Departments
-------------------------
DepartmentID    Primary Key
DepartmentName

DepartmentName is now stored in the Departments table, where it depends directly on DepartmentID.

Each normal form builds on the requirements of the preceding normal form.

1. Identify the information your database must store.
2. Identify repeating groups and fields containing multiple values.
3. Separate different subjects into appropriate tables.
4. Choose a primary key for each table.
5. Use foreign keys to connect related tables.
6. Check for partial dependencies on composite keys.
7. Check for transitive dependencies.
8. Open Database Tools and select Relationships.
9. Create the required relationships between tables.
10. Test your tables, queries, forms, and reports.

Design a normalized student management database using three related tables.

Students Table

StudentID       AutoNumber, Primary Key
StudentName     Short Text
Mobile          Short Text
DepartmentID    Number, Foreign Key

Departments Table

DepartmentID    AutoNumber, Primary Key
DepartmentName  Short Text

Courses Table

CourseID        AutoNumber, Primary Key
CourseName      Short Text
CourseFee       Currency

If students can enroll in multiple courses and courses can have multiple students, create an additional Enrollment table:

Enrollment
-------------------------
StudentID       Foreign Key
CourseID        Foreign Key
EnrollmentDate  Date/Time

Primary Key: StudentID + CourseID

Create relationships from Students to Enrollment and from Courses to Enrollment. This represents a many-to-many relationship through a junction table.

Create a normalized database for a computer training institute.

Requirements

1. Create a Students table.
2. Create a Courses table.
3. Create an Enrollment table.
4. Give every table a suitable primary key.
5. Use foreign keys in the Enrollment table.
6. Connect the tables using Relationships.
7. Check that each field stores a single value.
8. Avoid storing course names repeatedly in student records.
9. Create a query showing student names and course names.
10. Explain how the design reduces duplication.

1. What is database normalization?
2. Why is normalization important?
3. What is an insertion anomaly?
4. What is an update anomaly?
5. What is a deletion anomaly?
6. What are the requirements of First Normal Form?
7. What is a partial dependency?
8. What is a transitive dependency?
9. How does a junction table help model a many-to-many relationship?
10. How can normalization improve a student management database?