Activity #6: Normalizing Denormalized Data Using Excel

In this activity, you will take 5 denormalized datasets and normalize them using Excel. You will generate 5 tables, each containing at least 25 columns. The goal is to split the data into multiple tables to follow database normalization rules using Excel sheets. This helps remove redundancy and ensure better data organization.

Step 1: Create an Excel Workbook with 5 Sheets

1. employeeData (Denormalized)

Normalized:

Employee Table

Managers Table

Project Table

Departments table

employeeProject table

Which columns were redundant and how you removed those redundancies.

managerName, managerPhone, managerEmail - These were formerly duplicated for each employee, but are now only saved in the Managers table avoiding redundancy.

projectName, projectDeadline, projectStatus - These were duplicated for each employee and are now saved in the Projects table.

departmentName - This was repeated for each employee it is now stored in the Departments table.

2. studentData (Denormalized)

Normalized:

Students Table

Teacher Table

Classes Table

Courses table

studentGrade Table

Guardians table

Which columns were redundant and how you removed those redundancies.

className, teacherID, courseID, courseName - These were formerly repeated for each student, but are now saved in their relevant tables (Classes, Teachers, Courses) reducing duplication.

guardianName, guardianPhone, guardianEmail → These were repeated for each student, and they are currently saved in the Guardians database.

grade, attendanceRate → These were repeated for each student in each class and are now saved in the StudentGrades database which connects students to classes.

3. productData (Denormalized)

Normalized:

Products Table

Suppliers Table

Categories Table

Warehouses Table

Manufacturers Table

Which columns were redundant and how you removed those redundancies.

categoryName, supplierName, supplierPhone, supplierEmail, warehouseLocation, manufacturerName, manufacturerPhone, manufacturerEmail → These were initially repeated for each product they are now kept in their appropriate tables (Categories, Suppliers, Warehouses, and Manufacturers) which eliminates redundancy.

4. orderData (Denormalized)

Normalized:

Orders Table

Products Table

Customers Table

Sales Represetatives Table

Order Item Table

5. salesData (Denormalized)

Normalized:

Sales Table

Product Table

Customer Table

Region Table

Sales Represetatives Table

Invoice Table