Normalization should be part of the database design process. However, it is difficult to separate the normalization process from the ER modelling process so the two techniques should be used concurrently. Show
Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization’s data requirements and operations. This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships. Normalization procedure focuses on characteristics of specific entities and represents the micro view of entities within the ERD. What Is Normalization?Normalization is the branch of relational theory that provides design insights. It is the process of determining how much redundancy exists in a table. The goals of normalization are to:
Normalization theory draws heavily on the theory of functional dependencies. Normalization theory defines six normal forms (NF). Each normal form involves a set of dependency properties that a schema must satisfy and each normal form gives guarantees about the presence and/or absence of update anomalies. This means that higher normal forms have less redundancy, and as a result, fewer update problems. Normal FormsAll the tables in any database can be in one of the normal forms we will discuss next. Ideally we only want minimal redundancy for PK to FK. Everything else should be derived from other tables. There are six normal forms, but we will only look at the first four, which are:
BCNF is rarely used. First Normal Form (1NF)In the first normal form, only single values are permitted at the intersection of each row and column; hence, there are no repeating groups. To normalize a relation that contains a repeating group, remove the repeating group and form two new relations. The PK of the new relation is a combination of the PK of the original relation plus an attribute from the newly created relation for unique identification. Process for 1NFWe will use the Student_Grade_Report table below, from a School database, as our example to explain the process for 1NF. Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
Student (StudentNo, StudentName, Major) StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade) How to update 1NF anomaliesStudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
Second Normal Form (2NF)For the second normal form, the relation must first be in 1NF. The relation is automatically in 2NF if, and only if, the PK comprises a single attribute. If the relation has a composite PK, then each non-key attribute must be fully dependent on the entire PK and not on a subset of the PK (i.e., there must be no partial dependency or augmentation). Process for 2NFTo move to 2NF, a table must first be in 1NF.
Student (StudentNo, StudentName, Major) CourseGrade (StudentNo, CourseNo, Grade) CourseInstructor (CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation) How to update 2NF anomalies
Third Normal Form (3NF)To be in third normal form, the relation must be in second normal form. Also all transitive dependencies must be removed; a non-key attribute may not be functionally dependent on another non-key attribute. Process for 3NF
Student (StudentNo, StudentName, Major) CourseGrade (StudentNo, CourseNo, Grade) Course (CourseNo, CourseName, InstructorNo) Instructor (InstructorNo, InstructorName, InstructorLocation) At this stage, there should be no anomalies in third normal form. Let’s look at the dependency diagram (Figure 12.1) for this example. The first step is to remove repeating groups, as discussed above. Student (StudentNo, StudentName, Major) StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade) To recap the normalization process for the School database, review the dependencies shown in Figure 12.1. Figure 12.1 Dependency diagram, by A. Watt.The abbreviations used in Figure 12.1 are as follows:
Boyce-Codd Normal Form (BCNF)When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd normal form is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key. BCNF Example 1Consider the following table (St_Maj_Adv). Student_idMajorAdvisor111PhysicsSmith111MusicChan320MathDobbs671PhysicsWhite803PhysicsSmithThe semantic rules (business rules applied to the database) for this table are:
The functional dependencies for this table are listed below. The first one is a candidate key; the second is not.
Anomalies for this table include:
Note: No single attribute is a candidate key. PK can be Student_id, Major or Student_id, Advisor. To reduce the St_Maj_Adv relation to BCNF, you create two new tables:
St_Adv table Student_idAdvisor111Smith111Chan320Dobbs671White803SmithAdv_Maj table BCNF Example 2Consider the following table (Client_Interview). ClientNoInterviewDateInterviewTimeStaffNoRoomNoCR7613-May-0210.30SG5G101CR5613-May-0212.00SG5G101CR7413-May-0212.00SG37G102CR561-July-0210.30SG5G102FD1 – 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 create a table that incorporates the first three FDs (Client_Interview2 table) and another table (StaffRoom table) for the fourth FD. Client_Interview2 table ClientNoInterviewDateInterViewTimeStaffNoCR7613-May-0210.30SG5CR5613-May-0212.00SG5CR7413-May-0212.00SG37CR561-July-0210.30SG5StaffRoom table StaffNoInterviewDateRoomNoSG513-May-02G101SG3713-May-02G102SG51-July-02G102Normalization and Database DesignDuring the normalization process of database design, make sure that proposed entities meet required normal form before table structures are created. Many real-world databases have been improperly designed or burdened with anomalies if improperly modified during the course of time. You may be asked to redesign and modify existing databases. This can be a large undertaking if the tables are not properly normalized. Boyce-Codd normal form (BCNF): a special case of 3rd NF first normal form (1NF): only single values are permitted at the intersection of each row and column so there are no repeating groups normalization: the process of determining how much redundancy exists in a table second normal form (2NF): the relation must be in 1NF and the PK comprises a single attribute semantic rules: business rules applied to the database third normal form (3NF): the relation must be in 2NF and all transitive dependencies must be removed; a non-key attribute may not be functionally dependent on another non-key attribute Complete chapters 11 and 12 before doing these exercises.
Also see Appendix B: Sample ERD Exercises BibliographyNguyen Kim Anh, Relational Design Theory. OpenStax CNX. 8 Jul 2009 Retrieved July 2014 from http://cnx.org/contents/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1 Russell, Gordon. Chapter 4 – Normalisation. Database eLearning. N.d. Retrived July 2014 from db.grussell.org/ch4.html Is it true that every relation is 3NF is also BCNF?Every relation in BCNF is also in 3NF, but the reverse is not necessarily true. 3NF allows attributes to be part of a candidate key that is not the primary key; BCNF does not. This means that relations in 3NF are often in BCNF, but not always.
What is the relationship between BCNF and 3NF?BCNF is an extension of 3NF and it is has more strict rules than 3NF. Also, it is considered to be more stronger than 3NF. This relation is in BCNF as it is already in 3Nf (there is no prime attribute deriving no prime attribute) and on the left hand side of the functional dependency there is a candidate key.
Which of the following is true about 3NF and BCNF?A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R but this does not guarantee transitive dependency. Every relation is BCNF must be in 3NF. Any relation can be in both BCNF & 3NF if it satisfies the condition of BCNF. Hence, the correct answer is “option 3”.
Can 3NF be BCNF?A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied: R must be in 3rd Normal Form. and, for each functional dependency ( X → Y ), X should be a super Key.
|