Question

The tbl_employee contains information about the employee and its department, and it contains the following fields (it is possible to also include other missing fields): employee_id, employee_gender ,employee_salary, employee_annual_sallary, department_name, department_location, department head.

Make use of the database normalization skills learned to normalize a database to the third normalization form. Organize the columns and tables to reduce data redundancy and arrange attributes based on the relationships.

• Provide a screen shot of the database design before the normalization. Create a heading titled “Database Before Normalization.”
• Provide a screen shot of the database design after the normalization. Create a heading titled “Database After Normalization.”
• In more than 400 words, describe the three normalization forms and the guidelines for transforming from lower normal forms to higher normal forms.

Answer 1

Database Before Normalization

employee_id

employee_gender

employee_salary

employee_annual_sallary

department_name

department_head

Database After normalization

Table1-

employee_id

employee_gender

employee_salary

employee_annual_sallary

Table2

department_name

department_location

department_head

NORMALIZATION is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

1NF (First Normal Form) Rules:-

• Each table cell should contain a single value.
• Each record needs to be unique.

2NF (Second Normal Form) Rules:

• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2nd Normalization form unless we partition the table.

3NF (Third Normal Form) Rules

• Rule 1- Be in 2NF
• Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide