Welcome to Lesson 3 of Module 11 on Normalization Concepts and Practice. I'm gonna start with an important trivia question about the relational data model and normalization theory development. Who is considered the father of the relational data model? Lesson 3 demonstrates usage of functional dependencies that you learned about in Lesson 2. You will learn general concepts of normal forms and details of the most important normal form known as Boyce-Codd Normal Form or BCNF for short. The objectives in this lesson involve the nature of normal forms and the details of BCNF. You should be able to define BCNF and apply it to a list of functional dependencies as well as explain the nature of normal forms. Normalization is the process of removing unwanted redundancy in a table design. The input to normalization is a list of functional dependencies in a table design including a designation of unique columns. Applying a normal form involves detecting violations of the allowable pattern of functional dependencies and splitting the original table into smaller tables if a violation is found. The tables in the revised design should conform to the allowable pattern of functional dependencies specified by the normal form. Here are some other important points about the normalization process. Normalization essentially splits a table into smaller tables if violations of the normal form are found. The functional dependency should be complete and minimal for normalization to work correctly. Obtaining a complete and minimal list of functional dependencies is a highly detailed and specialized topic not usually important in practice. Thus determination of a complete and minimal list of functional dependencies is not covered in this course. The unique columns for table can be derived from the functional dependencies, but this derivation is rather detailed and not usually important in practice. Thus, derivation of unique columns from functional dependencies is not covered in this course. A number of normal forms have been developed to remove redundancies. As previously indicated, a normal form is a rule about allowable dependencies. Each normal form removes certain kinds of redundancies. As shown in this diagram, first normal form is the starting point. Second normal form is stronger than first normal form. Only a subset of first normal form tables are in second normal form. Each successive normal form refines the previous normal form to remove additional kinds of redundancies. Because Boyce-Codd Normal Form, or BCNF for short, is revised and stronger definition for third normal form, third normal form and BCNF are shown in the same part of this diagram. BCNF is the most important rule in practice because higher normals forms involve other kinds of constraints that are less common and more difficult to understand. Therefore, this course only covers BCNF. Boyce-Codd Normal Form, or BCNF for short, was originally conceived as a simpler definition in a third normal form and a correction of an omission in a third normal form. The correction is not important in practice. The BCNF definition is simple as it does not refer to second normal form. BCNF requires that every determinant must be unique in a table. An FD violates BCNF if the determinate in the functional dependency is not unique in a table. This definition is easy to check in practice as will be demonstrated in the remainder of this lesson. After demonstrating violations of BCNF, a simple procedure will be presented to refine a table design so that it conforms to BCNF. The example to demonstrate BCNF violations uses the Big University Database Table shown in Lessons 1 and 2. This sample table adds a student email column to the sample table shown in Lessons 1 and 2. The primary key is a combination of student number and offer number. The combination of student email and offer number is also unique in this table. The functional dependencies for the Big University Database Table contain many violations of BCNF. The unique columns in the table are a combination of student number and offer number, and student email and offer number. A functional dependency violates BCNF if a determinant in the functional dependency is not unique in the table. The first four functional dependency groups violate BCNF as a left-hand side in each functional dependency group is not a determinant. In the first functional dependency, student number is a subset of a determinant, student number and offer number, but not a determinant by itself. Only the last FD group satisfies BCNF. In the last FD group, the determinant, the combination of student number and offer number, is unique in the table. All other functional dependencies violate BCNF. The remainder of this lesson presents an essential but simplified procedure to perform normalization for BCNF. This procedure can be used to generate tables satisfying BCNF starting with a list of functional dependencies. The first step organizes functional dependencies by determinant. The functional dependency list contains a list of functional dependency groups in which each functional dependency group is the same left-hand side or determinant. The second step defines tables, one for each functional dependency group. The determinant in a functional dependency group is the primary key of the table. Foreign keys for each table are also added. The third step merges tables to prevent excessive splitting. Two tables are merged if one table contains a subset of the columns of the other table. Let's apply the BCNF procedure to the functional dependencies for the Big University Database Table. Each step will be briefly reviewed. In the first step, the functional dependencies organized into five FD groups. This grouping was previously shown to clarify violations of BCNF. In the second step, each functional dependancy the group becomes a table with the determinate as the primary key. Table names must be made for each FD group. In the third step, the student and student email tables are merged because the columns in the student table contain the columns of the student email table. Foreign keys should be added for each table defined in step 2. A foreign key is added for each column of the table. It appears as a primary key in another table. For example, Offering.CourseNo is a foreign key in the offering table because CourseNo is the primary key of the course table. I want to slightly elaborate on the details of step 3. Step 2, they define too many tables when two columns determine each other. In this example, student number determines student email, and student email determines student number. When two columns determine each other, step 2 creates two tables with one table containing each other. For example, the student table contains a student email table. These tables should be merged. The merged table satisfies BCNF, as a determinant in each FD is unique in the merged table. Multiple unique columns in the table do not violate BCNF. Let's wrap up Lesson 3 about normal forms. Lesson 3 has covered the general idea of normal forms and the details of the most important normal form known as Boyce-Codd Normal Form or BCNF for short. The definition of BCNF and the procedure to refine the table design so that a satisfies BCNF were presented. The Big University Database Table, presented in earlier lessons in Module 11, was used to depict the BCNF violations and the BCNF procedure. In answer to the opening question, Dr. Ted Codd is regarded as the father of relational databases for his pioneering work while a scientist at IBM. Dr Codd wrote a famous paper in 1970 defining the basic elements of the relational data model. Dr. Codd urged IBM executives to start a project to design a new generation of DB products based on the relational data model. After some reluctance, IBM agreed with Dr. Codd and started the System R project, the predecessor of IBM's commercial DBMS products based on the relational model. Dr. Codd also devised the Bocye-Codd Normal Form, the pillar of normalization theory and practice. Many important honors have been bestowed on Dr. Codd for his work on relational databases. After the details of normal forms, you are almost ready to work practice and graded problems. Lesson 4 presents some practical issues to give you a perspective on a normalization process. You should keep these practical issues in mind as you work practice and graded problems. Lesson 5 completes this Module by presenting some practice problems.