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

You are watching: An unnormalized relation is a table that has more than one row.

Use an reality relation diagram (ERD) to carry out the large picture, or macro view, the an organization’s data requirements and operations. This is created through one iterative procedure that requires identifying appropriate entities, their attributes and also their relationships.

Normalization procedure focuses on features of particular entities and also represents the micro see of entities in ~ the ERD.

What Is Normalization?

Normalization is the branch that relational theory that provides design insights. It is the process of determining just how much redundancy exists in a table. The objectives of normalization space to:

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

Normalization theory draws heavily on the concept of functional dependencies. Normalization theory specifies six normal creates (NF). Every normal type involves a set of suspended properties the a schema need to satisfy and each normal form gives guarantees about the presence and/or lack of update anomalies. This method that higher normal develops have less redundancy, and as a result, fewer update problems.

Normal Forms

All the tables in any type of database have the right to be in one of the normal forms we will discuss next. Ideally we only want minimal redundancy because that PK to FK. Whatever else have to be derived from various other tables. There are 6 normal forms, yet we will just look at the first four, i beg your pardon are:

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

BCNF is hardly ever used.

First Normal type (1NF)

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

To normalize a relation that includes a repeating group, eliminate the repeating group and kind two new relations.

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

Process for 1NF

We will usage the Student_Grade_Report table below, from a college database, together our instance to define the process for 1NF.


Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
In the student Grade Report table, the repeating group is the course information. A student deserve to take countless courses.Remove the repeating group. In this case, it’s the course info for each student.Identify the PK for your new table.The PK have to uniquely determine the attribute worth (StudentNo and CourseNo).After removing every the features related to the course and also student, you space left v the student course table (StudentCourse).The college student table (Student) is now in an initial normal kind with the repeating group removed.The two new tables are shown below.
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)

How to upgrade 1NF anomalies

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

To include a brand-new course, we require a student.When course details needs to be updated, we may have actually inconsistencies.To delete a student, us might also delete vital information about a course.

Second Normal type (2NF)

For the second typical form, the relation 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, climate each non-key attribute have to be totally dependent on the whole PK and not top top a subset of the PK (i.e., there should be no partial dependency or augmentation).

Process because that 2NF

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

The college student table is currently in 2NF due to the fact that it has actually a single-column PK.When evaluating the student Course table, we watch that not all the characteristics are completely dependent on the PK; specifically, every course information. The just attribute that is totally dependent is grade.Identify the new table that includes the food information.Identify the PK for the new table.The three new tables are displayed below.

How to upgrade 2NF anomalies

When including a brand-new instructor, we need a course.Updating food information can lead come inconsistencies because that instructor information.Deleting a food may additionally delete instructor information.

Third Normal form (3NF)

To be in third regular form, the relation should be in 2nd normal form. Additionally all transitive dependencies need to be removed; a non-key attribute may not be functionally dependent 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 gotten rid of dependency.Check new table(s) as well as table(s) modified come make sure that every table has a determinant and also that no table includes inappropriate dependencies.See the four new tables below.

At this stage, there must be no anomalies in 3rd normal form. Let’s look in ~ the dependency diagram (Figure 12.1) for this example. The an initial step is to eliminate repeating groups, as debated above.

Student (StudentNo, StudentName, Major)

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

To recap the normalization process for the college database, evaluation the dependencies shown in figure 12.1.

*
Figure 12.1 dependency diagram, by A. Watt.

The abbreviations supplied in number 12.1 room as follows:

PD: partial dependencyTD: transitive dependencyFD: full dependency (Note: FD frequently stands for functional dependency. Making use of FD together an abbreviation for full dependency is only provided in figure 12.1.)

Boyce-Codd Normal kind (BCNF)

When a table has more than one candidate key, anomalies may an outcome even though the relationship is in 3NF. Boyce-Codd common form is a special case of 3NF. A relationship is in BCNF if, and also 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 applied to the database) for this table are:

Each student may significant in several subjects.For every Major, a given Student has actually only one Advisor.Each major has several Advisors.Each torture advises just one Major.Each torture advises numerous Students in one Major.

The functional dependencies because that this table are detailed below. The first one is a candidate key; the second is not.

Student_id, major ——> AdvisorAdvisor ——> Major

Anomalies for this table include:

Delete – student deletes torture infoInsert – a new advisor requirements a studentUpdate – inconsistencies

Note: No solitary attribute is a candidate key.

PK deserve to be Student_id, significant or Student_id, Advisor.

To reduce the St_Maj_Adv relationship to BCNF, you develop 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 first three FDs (Client_Interview2 table) and 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 sure that proposed entities meet required normal type before table structures space created. Countless real-world databases have actually been improperly draft or burdened v anomalies if improperly modified throughout the course of time. You might be asked to redesign and modify existing databases. This can be a big undertaking if the tables room not effectively normalized.


Boyce-Codd normal type (BCNF): a special instance of third NF

first normal form (1NF): only single values are allowed at the intersection of each row and also column for this reason there space no repeating groups

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

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

semantic rules: service rules applied to the database

third normal type (3NF): the relation have to be in 2NF and also all leg dependencies need to be removed; a non-key attribute may not it is in functionally dependent on an additional non-key attribute

Complete chapters 11 and also 12 prior to doing these exercises.

See more: Steps On How To Put In A Nose Hoop With Gem At The End, How Do I Put In A Nose Hoop With Gem

What is normalization?When is a table in 1NF?When is a table in 2NF?When is a table in 3NF?Using the dependency chart you just drew, show the tables (in their third normal form) girlfriend would produce to fix the difficulties you encountered. Draw the dependency diagram because that the resolved table.An agency called prompt Cover gives part-time/temporary staff to many hotels in Scotland. Figure 12.4 lists the time invested by agency staff functioning at assorted hotels. The nationwide insurance number (NIN) is unique for every member the staff. Use number 12.4 come answer concerns (a) and (b).Figure 12.4 For inquiry 8, by A. Watt.This table is prone to upgrade anomalies. Administer examples of insertion, deletion and update anomalies.Normalize this table to third normal form. State any type of assumptions.Fill in the blanks:____________________ produces a lower normal form.Any attribute whose value determines other values within a heat is called a(n) ____________________.An attribute that cannot be further split is said to display screen ____________________.____________________ describes the level of information represented through the worths stored in a table’s row.A relational table need to not contain ____________________ groups.

Also see Appendix B: Sample ERD Exercises


Bibliography

Nguyen Kim Anh, Relational architecture Theory. OpenStax CNX. 8 Jul 2009 re-cover July 2014 indigenous 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