Question

Write a paper of 800 - 1,000 words in APA format about Improving Database Design including normalization. The improvements will provide for better information and enhance operational characteristics. How is this done and why is it important? How might it impact you personally or professionally?

Answer 1

Normalization is the branch of relational theory that provides design insights. It is the process eliminate the flaws in the database. A bad designed database will create an issue while performing updating, creating and deleting the data.

The goals of normalization are to:

• Be able to characterize the level of redundancy in a relational schema.
• Provide mechanisms for transforming schemas in order to remove redundancy.

Normalization theory has defined six normal forms (known as NF).

The following makes Database Normalization a important step in the process of designing the database:

1. Resolving the database anomalies

The forms of Normalization i.e. 1NF, 2NF, 3NF, BCF, 4NF and 5NF remove all the Insert, Update and Delete anomalies.

1. Insertion Anomaly: Occurs when we try to insert data in a record that does not exist.
2. Deletion Anomaly: Occurs when a data is to be deleted and due to the bad design of database, other record get deletes.

2. Removal of Data Redundancy

Data Redundancy is storing same data item multiple times. A normalized table do not have the issue of redundancy of data.

3. Data Dependency

The data gets stored in the correct table and ensures normalization.

4. Data Isolation

A good designed database states that the changes in one table or field will not affect other fields.

5. Data Consistency

While updating if a record is left, it can led to inconsistent data, Normalization resolves it and ensures Data Consistency.

Data Normalization Forms:

1. 1NF (First Normal Form) Rules

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

Process for 1NF

We will use the Student_Grade table below, from a School database, as our example to explain the process for 1NF.

StudentNo	StudentName	Major	CourseId	CourseName	TeacherIDNo	TeacherName	Grade	Location

In the Student Grade table, the repeating group is the course information. A student can take many courses.

• Remove the repeating group. In this case, it’s the course information for each student.
• Identify the PK for your new table.
• The PK must uniquely identify the attribute value (StudentNo and CourseId).
• After removing all the attributes related to the course and student, you are left with the student course table (StudentCourse).
• The Student table (Student) is now in first normal form with the repeating group removed.
• The two new tables are shown below.

Student [StudentNo, StudentName, Major]

StudentCourse [StudentNo, CourseNo, CourseName, TeacherIDNo, TeacherName, Grade, Location].

Updating the 1NF anomalies

StudentCourse [StudentNo, CourseNo, CourseName, TeacherIDNo, TeacherName, Grade, Location].

• To add a new course, we need a student.
• When course information needs to be updated, we may have inconsistencies.
• To delete a student, we might also delete critical information about a course.

2. Second Normal Form (2NF)

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

Process for 2NF

To move to 2NF, a table must first be in 1NF.

• The Student table is already in 2NF because it has a single-column PrimaryKey.
• When examining the Student Course table, we see that not all the attributes are fully dependent on the PrimaryKey; specifically, all course information. The only attribute that is fully dependent is grade.
• Identify the new table that contains the course information.
• Identify the PrimaryKey for the new table.

The three new tables are .

Student[StudentNo, StudentName, Major]

CourseGrade [StudentNo, CourseNo, Grade]

CourseTeacher [CourseNo, CourseName, TeacherIDNo, TeacherName, Location]

StudentCourse [StudentNo, CourseNo, CourseName, TeacherIDNo, TeacherName, Grade, Location].

Updating 2NF anomalies

• When adding a new teacher, we need a course.
• Updating course information could lead to inconsistencies for teacher information.
• Deleting a course may also delete teacher information.

3. Third Normal Form (3NF)

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

Process for 3NF

• Eliminate all dependent attributes in transitive relationship(s) from each of the tables that have a transitive relationship.
• Create new table(s) with removed dependency.
• Check new table(s) as well as table(s) modified to make sure that each table has a determinant and that no table contains inappropriate dependencies.

See the four new tables below.

Student [StudentNo, StudentName, Major]

CourseGrade [StudentNo, CourseNo, Grade]

Course [CourseNo, CourseName, TeacherIdNo]

Teacher [TeacherIdNo, TeacherName, Location]

4. Boyce-Codd Normal Form (BCNF)

Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key. BCNF is also referred as 3.5 Normal Form.

5. 4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.

6. 5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

7. 6NF (Sixth Normal Form) Proposed

6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6th Normal.