Normalization have to be component of the database style process. However, that is challenging to separate the normalization procedure from the ER modelling process so the two techniques should be used concurrently.

You are watching: Normalization works through a series of stages called normal forms.

Use an reality relation diagram (ERD) to administer the large picture, or macro view, of an organization data requirements and operations. This is produced through an iterative procedure that involves identifying relevant entities, your attributes and their relationships.

Normalization procedure focuses on features of specific entities and also represents the micro check out of entities in ~ the ERD.

What Is Normalization?

Normalization is the branch that relational theory that provides style insights. The is the procedure of determining how much redundancy exists in a table. The objectives of normalization are to:

Be able to characterize the level of redundancy in a relational schemaProvide mechanisms for transforming schemas in order to eliminate redundancy

Normalization concept draws heavily on the theory of practical dependencies. Normalization theory specifies six normal forms (NF). Each normal form involves a set of exposed properties the a schema should satisfy and also each normal form gives guarantees around the existence and/or absence of update anomalies. This way that greater normal develops have much less redundancy, and also as a result, fewer update problems.

Normal Forms

All the tables in any type of database deserve to be in among the normal forms we will discuss next. Ideally we only want minimal redundancy because that PK to FK. Every little thing else need to be obtained from various other tables. There are six normal forms, yet we will just look at the first four, i m sorry are:

First normal form (1NF)Second normal form (2NF)Third normal type (3NF)Boyce-Codd normal kind (BCNF)

BCNF is hardly ever used.

First Normal kind (1NF)

In the first common form, only solitary values are permitted at the intersection of every row and also column; hence, there room no repeating groups.

To normalize a relationship that includes a repeating group, remove the repeating team and form two new relations.

The PK of the new relation is a mix of the PK of the original relation plus an attribute indigenous the newly created relation for distinctive identification.

Process because that 1NF

We will usage the Student_Grade_Report table below, from a institution database, as our instance to describe the procedure for 1NF.


Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
In the college student Grade Report table, the repeating group is the food information. A student have the right to take numerous courses.Remove the repeating group. In this case, that the course information for each student.Identify the PK because that your brand-new table.The PK have to uniquely determine the attribute value (StudentNo and also CourseNo).After removing all the features related come the course and also student, you space left with the college student course table (StudentCourse).The college student table (Student) is now in very first normal form with the repeating group removed.The two new tables are presented below.
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

How to update 1NF anomalies

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

To include a brand-new course, we need a student.When course information needs to it is in updated, we may have actually inconsistencies.To delete a student, us might also delete an essential information around a course.

Second Normal form (2NF)

For the second normal form, the relationship must an initial be in 1NF. The relationship is instantly in 2NF if, and also only if, the PK comprises a single attribute.

If the relation has actually a composite PK, then each non-key attribute need to be totally dependent top top the whole PK and also not on a subset of the PK (i.e., there need to be no partial exposed or augmentation).

Process for 2NF

To relocate to 2NF, a table must an initial be in 1NF.

The college student table is already in 2NF due to the fact that it has a single-column PK.When examining the college student Course table, we check out that not all the attributes are totally dependent ~ above the PK; specifically, every course information. The only attribute the is completely dependent is grade.Identify the brand-new table that contains the course information.Identify the PK because that the brand-new table.The three brand-new tables are shown below.

How to update 2NF anomalies

When adding a new instructor, we need a course.Updating food information might lead to inconsistencies for instructor information.Deleting a food may additionally delete instructor information.

Third Normal kind (3NF)

To be in third typical form, the relation need to be in 2nd normal form. Likewise all transitive dependencies need to be removed; a non-key attribute might not it is in functionally dependence on another non-key attribute.

Process because that 3NF

Eliminate every dependent qualities in transitive relationship(s) from every of the tables that have actually a leg relationship.Create new table(s) with eliminated dependency.Check brand-new table(s) as well as table(s) modified come make sure that each table has a determinant and also that no table consists of inappropriate dependencies.See the four brand-new tables below.

At this stage, there should be no anomalies in 3rd normal form. Let’s look in ~ the dependency chart (Figure 12.1) because that this example. The first step is to remove repeating groups, as questioned above.

Student (StudentNo, StudentName, Major)

StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

To recap the normalization process for the institution database, testimonial the dependencies presented in number 12.1.

\"*\"
Figure 12.1 exposed diagram, through A. Watt.

The abbreviations supplied in number 12.1 room as follows:

PD: partial dependencyTD: leg dependencyFD: full dependency (Note: FD commonly stands because that functional dependency. Utilizing FD together an abbreviation for full dependency is only supplied in number 12.1.)

Boyce-Codd Normal type (BCNF)

When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd typical form is a special case of 3NF. A relationship is in BCNF if, and only if, every determinant is a candidate key.

BCNF Example 1

Consider the following table (St_Maj_Adv).

Student_idMajorAdvisor
111PhysicsSmith
111MusicChan
320MathDobbs
671PhysicsWhite
803PhysicsSmith

The semantic rules (business rules used to the database) for this table are:

Each college student may major in number of subjects.For each Major, a given Student has actually only one Advisor.Each major has number of Advisors.Each torture advises only one Major.Each torture advises numerous Students in one Major.

The practical dependencies because that this table are detailed below. The an initial one is a candidate key; the 2nd is not.

Student_id, major ——> AdvisorAdvisor ——> Major

Anomalies for this table include:

Delete – student deletes torture infoInsert – a brand-new advisor demands a studentUpdate – inconsistencies

Note: No single attribute is a candidate key.

PK have the right to be Student_id, major or Student_id, Advisor.

To minimize the St_Maj_Adv relationship to BCNF, you create two brand-new tables:

St_Adv (Student_id, Advisor)Adv_Maj (Advisor, Major)

St_Adv table

Student_idAdvisor
111Smith
111Chan
320Dobbs
671White
803Smith

Adv_Maj table

AdvisorMajor
SmithPhysics
ChanMusic
DobbsMath
WhitePhysics

BCNF instance 2

Consider the adhering to table (Client_Interview).

ClientNoInterviewDateInterviewTimeStaffNoRoomNo
CR7613-May-0210.30SG5G101
CR5613-May-0212.00SG5G101
CR7413-May-0212.00SG37G102
CR561-July-0210.30SG5G102

FD1 – ClientNo, InterviewDate –> InterviewTime, StaffNo, RoomNo (PK)

FD2 – staffNo, interviewDate, interviewTime –> clientNO (candidate key: CK)

FD3 – roomNo, interviewDate, interviewTime –> staffNo, clientNo (CK)

FD4 – staffNo, interviewDate –> roomNo

A relation is in BCNF if, and only if, every determinant is a candidate key. We need to produce a table that incorporates the very first three FDs (Client_Interview2 table) and also another table (StaffRoom table) for the 4th FD.

Client_Interview2 table

ClientNoInterviewDateInterViewTimeStaffNo
CR7613-May-0210.30SG5
CR5613-May-0212.00SG5
CR7413-May-0212.00SG37
CR561-July-0210.30SG5

StaffRoom table

StaffNoInterviewDateRoomNo
SG513-May-02G101
SG3713-May-02G102
SG51-July-02G102

Normalization and also Database Design

During the normalization procedure of database design, make certain that proposed entities meet forced normal form before table structures space created. Numerous real-world databases have been improperly design or burdened v anomalies if improperly modified during the course of time. You may be asked come redesign and also modify existing databases. This can be a big undertaking if the tables room not correctly normalized.


Boyce-Codd normal kind (BCNF): a special situation of third NF

first normal type (1NF): only solitary values are allowed at the intersection of every row and also column so there are no repeating groups

normalization: the procedure of determining just how much redundancy exists in a table

second normal type (2NF): the relation should be in 1NF and also the PK comprises a solitary attribute

semantic rules: business rules applied to the database

third normal type (3NF): the relation have to be in 2NF and all leg dependencies must be removed; a non-key attribute might not it is in functionally dependency on another non-key attribute

Complete chapters 11 and 12 before doing this exercises.

See more: What Is So Delicate That Saying Its Name Breaks It S Name Breaks It?

What is normalization?When is a table in 1NF?When is a table in 2NF?When is a table in 3NF?Using the dependency diagram you simply drew, present the tables (in their 3rd normal form) girlfriend would create to resolve the troubles you encountered. Attract the dependency diagram for the addressed table.An firm called immediate Cover supplies part-time/temporary employee to hotels in Scotland. Figure 12.4 lists the time invested by agency staff working at various hotels. The national insurance number (NIN) is distinctive for every member the staff. Use number 12.4 come answer inquiries (a) and (b).Figure 12.4 For concern 8, by A. Watt.This table is susceptible to update anomalies. Administer examples the insertion, deletion and also update anomalies.Normalize this table to 3rd normal form. State any type of assumptions.Fill in the blanks:____________________ produces a lower normal form.Any attribute whose value determines various other values within a row is dubbed a(n) ____________________.An attribute that cannot be further divided is stated to display screen ____________________.____________________ describes the level of information represented through the values stored in a table’s row.A relational table should not save ____________________ groups.

Also see Appendix B: Sample ERD Exercises


Bibliography

Nguyen Kim Anh, Relational style Theory. OpenStax CNX. 8 Jul 2009 recall July 2014 from http://cnx.org/contents/606cc532-0b1d-419d-a0ec-ac4e2e2d533b
1

Russell, Gordon. Thing 4 – Normalisation. Database eLearning. N.d. Retrived July 2014 from db.grussell.org/ch4.html