Assignment 4: Database Normalization - Course: Databases

This assignment solution focuses on database normalization, a critical aspect of database design. The solution addresses several scenarios, including normalizing tables to 3NF. The first part of the assignment involves normalizing a table containing product information, addressing issues related to redundancy and ensuring data integrity. The second part involves normalizing a table with movie data, addressing issues like repeating groups. The third part tackles the normalization of supplier and part information, ensuring that data is organized efficiently. The final part involves normalizing project, employee and department information, ensuring data consistency and avoiding anomalies. The solution demonstrates how to apply the principles of normalization to achieve a well-structured and efficient database design, providing a practical understanding of database concepts.

Assignment 4 (10 points)
Your Name: Srikar Chintala
Your ZID: Z1788560
(1) State the normal form and then normalize. Primary key is ProductID
ProductID ProductName StateCode Price StateTaxPercent
1 Television IL 500 8.5
2 Camera IN 200 7
3 Camera IL 300 8.5
4 Phone IN 650 7
5 Dish Washer WA 450 0
6 Dryer WA 450 0
7 Television IN 480 7
(2) State the normal form and then normalize. Primary Key is (title, year)
(3) State the normal form and then normalize. Primary key is SupplierID
SupplierID SupplierName Location PartSupplied Price
1 ABC Inc Illinois Valve 85
2 ABC Inc Indiana Valve 85
3 Cummins Inc Indiana Pump 185
4 Cummins Inc Washington Valve 85
5 ABC Inc Illinois Pump 185
6 Europump Inc Oklahoma Pump 185
title year length filmType studioName starName
Star Wars 1977 124 color Fox Fisher
Star Wars 1977 124 color Fox Hamill
Star Wars 1977 124 color Fox Ford
Mighty Ducks 1991 104 color Disney Esteves
Wayne’s World 1992 95 color Paramount Carvey
Wayne’s World 1992 95 color Paramount Meyers

(4) Cutting Edge Incorporated is a company engaged in the development of computer-aided design
(CAD) software packages. The management of Cutting Edge wants to develop a project tracking system
to accumulate and report data on current projects, employees and departments. They have given you
the following attributes they store on an excel spreadsheet. Make any necessary assumptions and
normalize to 3NF.
(ProjectNumber, ProjectName, StartDate, ProjectStatus, (EmployeeNumber, EmployeeName,
DepartmentNumber, DepartmentName, JobTitle, ProjectHours))