Module 3 Case: Database Normalization, Employee table

Normalization
Name
Institutional Affiliation
Database Normalization
In the design and development of a database, it is essential to ensure that the structure provides efficiency and enhances the handling of data by the database system. Technically, the process of organizing the structure of the database while designing it by ensuring that the tables are in their minimum form to reduce redundancy is referred to as database normalization (Rouse & Vaughan, Database Normalization, 2016). The aim is to remove duplicates from the tables. In addition, the database normalization is critical in determining the dependencies in the database such that the data is logically integrated. A comprehensive database normalization process ensures that future incidences of data loss do not occur in the process of handling and updating the database (Harrington, 2009). Normalization takes the forms first normal form (1NF), second normal form (2NF), third normal form (3NF) and Boyce-Codd normal form (BCNF), among others.
In 1NF, each row should have unique attributes. In 2NF, no column in a table should be dependent on the primary key. Efforts should be made to split the dependent columns into two or more tables. Finally, 3NF adds on the aspects of 2NF, and notes that each attribute in a column should not be dependent on another attribute.
Employee table
Social Security NumberFirst NameSecond nameAddressCityStateZip CodeBirthdateStatus (full/part time)Salary (hourly rate)Date of hireCustomer table
Customer NumberFirst NameSecond NameEmailTelAddressCityStateZipCustomer since (date)
To normalize the tables according to 1NF, attributes such as cases of customers having two telephone numbers should be recorded in two rows.
A 2NF normalization of the tables would be:
Customer table (In case a customer has two telephone numbers)
Customer numberFirst NameSecond NameEmailAddressCityStatezip
Customer numberTel
On the other hand, a 3NF normalization of the employee table would be;
Social Security NumberFirst NameSecond NameZip CodeBirth dateStatus (full/part time)Salary (hourly rate)Date of hire
Zip codeAddressCityState
For the customer table;
Customer numberFirst NameSecond NameEmailTelZipCustomer since
Zip code AddressCityState


Figure  SEQ Figure \* ARABIC 1 ERD Diagram after 3NF normalization
ACID
ACID in databases is an acronym for Atomicity, Consistency, Isolation and Durability (Rouse, 2016). Atomicity is a database process that highlights the uniqueness of the inf...