{"id":90,"date":"2013-12-12T00:09:59","date_gmt":"2013-12-12T00:09:59","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=90"},"modified":"2019-06-11T16:44:45","modified_gmt":"2019-06-11T16:44:45","slug":"chapter-12-normalization","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/chapter-12-normalization\/","title":{"raw":"Chapter 12 Normalization","rendered":"Chapter 12 Normalization"},"content":{"raw":"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.\r\n\r\nUse an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization\u2019s data requirements and operations. This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships.\r\n\r\nNormalization procedure focuses on characteristics of specific entities and represents the micro view of entities within the ERD.\r\n<h2>What Is Normalization?<\/h2>\r\n<em>Normalization<\/em> 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:\r\n<ul>\r\n \t<li>Be able to characterize the level of redundancy in a relational schema<\/li>\r\n \t<li>Provide mechanisms for transforming schemas in order to remove redundancy<\/li>\r\n<\/ul>\r\nNormalization 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\u00a0gives 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.\r\n<h2>Normal Forms<\/h2>\r\nAll the tables in any database can be in one of the normal forms we will discuss next.\u00a0 Ideally we only want minimal redundancy for PK to FK. Everything else should be derived from other tables.\u00a0 There are six normal forms, but we will only look at the first four, which are:\r\n<ul>\r\n \t<li>First\u00a0normal form (1NF)<\/li>\r\n \t<li>Second\u00a0normal form (2NF)<\/li>\r\n \t<li>Third normal form (3NF)<\/li>\r\n \t<li>Boyce-Codd normal form (BCNF)<\/li>\r\n<\/ul>\r\nBCNF is rarely used.\r\n<h2>First Normal Form (1NF)<\/h2>\r\nIn the <em>first normal form<\/em>, only single values are permitted at the intersection of each row and column; hence, there are no repeating groups.\r\n\r\nTo normalize a relation that contains a repeating group, remove the repeating group and form two new relations.\r\n\r\nThe PK\u00a0of the new relation is a combination of the PK\u00a0of the original relation plus an attribute from the newly created relation for unique identification.\r\n<h3>Process for 1NF<\/h3>\r\nWe will use the <strong>Student_Grade_Repor<span><span>t<\/span>\u00a0<\/span><\/strong><span>table below<\/span>,\u00a0from a School database, as our example to explain the process for 1NF.\r\n<div class=\"textbox\"><strong>Student_Grade_Report\u00a0<\/strong>(StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/div>\r\n<ul>\r\n \t<li>In the Student Grade Report table, the repeating group is the course information. A student can take many courses.<\/li>\r\n \t<li>Remove the repeating group. In this case, it\u2019s the course information for each student.<\/li>\r\n \t<li>Identify the PK for your new table.<\/li>\r\n \t<li>The PK must uniquely identify the attribute value (StudentNo and CourseNo).<\/li>\r\n \t<li>After removing all the attributes related to the course and student, you are left with the student course table (<strong>StudentCourse<\/strong>).<\/li>\r\n \t<li>The Student table (<strong>Student<\/strong>) is <span>now<\/span> in first normal form with the repeating group removed.<\/li>\r\n \t<li>The two new tables are shown below.<\/li>\r\n<\/ul>\r\n<div class=\"textbox\"><strong>Student<\/strong> (<span>StudentNo<\/span>, StudentName, Major)<\/div>\r\n<div class=\"textbox\"><strong>StudentCourse<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>,\u00a0CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/div>\r\n<h3>How to update 1NF anomalies<\/h3>\r\n<strong>StudentCourse<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)\r\n<ul>\r\n \t<li>To add a new course, we need a student.<\/li>\r\n \t<li>When course information needs to be updated, we may have inconsistencies.<\/li>\r\n \t<li>To delete a <em>student,<\/em> we might also delete critical information about a course.<\/li>\r\n<\/ul>\r\n<h2>Second Normal Form (2NF)<\/h2>\r\nFor the <em>second normal form<\/em>, the relation must first be in 1NF. The relation is automatically in 2NF if, and only if, the PK comprises a single attribute.\r\n\r\nIf 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 <span>or<\/span> augmentation).\r\n<h3>Process for 2NF<\/h3>\r\nTo move to 2NF, a table must first be in 1NF.\r\n<ul>\r\n \t<li>The Student table is already in 2NF because it has a single-column PK.<\/li>\r\n \t<li>When examining the Student Course table, we see that not all the attributes are fully dependent on the PK; specifically, all course information. The only attribute that is fully dependent is grade.<\/li>\r\n \t<li>Identify the new table that contains the course information.<\/li>\r\n \t<li>Identify the PK for the new table.<\/li>\r\n \t<li>The three new tables are shown below.<\/li>\r\n<\/ul>\r\n<div class=\"textbox\"><strong>Student<\/strong> (<span>StudentNo<\/span>, StudentName, Major)<\/div>\r\n<div class=\"textbox\"><strong>CourseGrade<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, Grade)<\/div>\r\n<div class=\"textbox\"><strong>CourseInstructor <\/strong>(<span>CourseNo<\/span>, CourseName, InstructorNo, InstructorName, InstructorLocation)<\/div>\r\n<h3>How to update 2NF anomalies<\/h3>\r\n<ul>\r\n \t<li>When adding a new instructor, we need a course.<\/li>\r\n \t<li>Updating course information could lead to inconsistencies for instructor information.<\/li>\r\n \t<li>Deleting a course may also delete instructor information.<\/li>\r\n<\/ul>\r\n<h2>Third Normal Form (3NF)<\/h2>\r\nTo be in <em>third normal form<\/em>, 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.\r\n<h3>Process for 3NF<\/h3>\r\n<ul>\r\n \t<li>Eliminate all dependent attributes in transitive relationship(s) from each of the tables that have a transitive relationship.<\/li>\r\n \t<li>Create new table(s) with removed dependency.<\/li>\r\n \t<li>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.<\/li>\r\n \t<li>See the four new tables below.<\/li>\r\n<\/ul>\r\n<div class=\"textbox\"><strong>Student<\/strong> (StudentNo, StudentName, Major)<\/div>\r\n<div class=\"textbox\"><strong>CourseGrade<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, Grade)<\/div>\r\n<div class=\"textbox\"><strong>Course<\/strong> (<span style=\"text-decoration: underline;\">CourseNo<\/span>, CourseName, InstructorNo)<\/div>\r\n<div class=\"textbox\"><strong>Instructor<\/strong> (InstructorNo, InstructorName, InstructorLocation)<\/div>\r\nAt this stage, there should be no anomalies in third normal <span style=\"color: #333333;\">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.<\/span>\r\n\r\n<strong>Student<\/strong> (StudentNo, StudentName, Major)\r\n\r\n<strong>StudentCourse<\/strong> (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)\r\n\r\nTo recap the normalization process for the School database, review the dependencies shown in Figure 12.1.\r\n\r\n[caption id=\"attachment_89\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89.jpg\" alt=\"Ch-11-Dependency-Diagram-School-300x89\" class=\"wp-image-89 size-full\" height=\"89\" width=\"300\" \/><\/a> Figure 12.1 Dependency diagram, by A. Watt.[\/caption]\r\n\r\nThe abbreviations used in Figure 12.1 are as follows:\r\n<ul>\r\n \t<li>PD: partial dependency<\/li>\r\n \t<li>TD: \u00a0transitive dependency<\/li>\r\n \t<li><span style=\"color: #ff0000;\"><span style=\"color: #333333;\">FD: \u00a0full dependency (Note: FD typically stands for <strong>functional<\/strong> dependency. Using FD as an abbreviation for full dependency is only used in Figure 12.1.)<\/span>\r\n<\/span><\/li>\r\n<\/ul>\r\n<h2>Boyce-Codd Normal Form (BCNF)<\/h2>\r\nWhen a table has more than one candidate key, anomalies may result even though the relation is in 3NF. <em>Boyce-Codd normal form<\/em> is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key.\r\n<h3>BCNF Example\u00a01<\/h3>\r\nConsider the following table (<strong>St_Maj_Adv<\/strong>).\r\n<table style=\"height: 126px;\" border=\"1\" width=\"202\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>Student_id<\/strong><\/td>\r\n<td valign=\"top\"><strong>Major<\/strong><\/td>\r\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">111<\/td>\r\n<td valign=\"top\">Physics<\/td>\r\n<td valign=\"top\">Smith<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">111<\/td>\r\n<td style=\"text-align: left;\" valign=\"top\">Music<\/td>\r\n<td valign=\"top\">Chan<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">320<\/td>\r\n<td valign=\"top\">Math<\/td>\r\n<td valign=\"top\">Dobbs<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">671<\/td>\r\n<td valign=\"top\">Physics<\/td>\r\n<td valign=\"top\">White<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">803<\/td>\r\n<td valign=\"top\">Physics<\/td>\r\n<td valign=\"top\">Smith<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nT<span style=\"color: #333333;\">he <em>semantic rules<\/em> (busines<\/span>s rules applied to the database) for this table are:\r\n<ol>\r\n \t<li>Each Student may major in several subjects.<\/li>\r\n \t<li>For each Major, a given Student has only one Advisor.<\/li>\r\n \t<li>Each Major has several Advisors.<\/li>\r\n \t<li>Each Advisor <span style=\"color: #000000;\">advises<\/span> only one Major.<\/li>\r\n \t<li>Each Advisor<span style=\"color: #000000;\"> advises <\/span>several Students in one Major.<\/li>\r\n<\/ol>\r\nThe functional dependencies for this table are listed below. The first one is a candidate key; the second is not.\r\n<ol>\r\n \t<li>Student_id, Major \u2014\u2014&gt;\u00a0 Advisor<\/li>\r\n \t<li>Advisor\u00a0 \u2014\u2014&gt;\u00a0 Major<\/li>\r\n<\/ol>\r\nAnomalies for this table include:\r\n<ol>\r\n \t<li>Delete\u00a0\u2013\u00a0student deletes advisor info<\/li>\r\n \t<li>Insert\u00a0\u2013 a new advisor needs a student<\/li>\r\n \t<li>Update \u2013 inconsistencies<\/li>\r\n<\/ol>\r\n<strong>Note<\/strong>: No single attribute is a candidate key.\r\n\r\nPK can be <span style=\"text-decoration: underline;\">Student_id, Major<\/span> or <span style=\"text-decoration: underline;\">Student_id, Advisor<\/span><strong>.<\/strong>\r\n\r\nTo reduce the <strong>St_Maj_Adv<\/strong> relation to BCNF, you create two new tables:\r\n<ol>\r\n \t<li><strong>St_Adv<\/strong> (<span style=\"text-decoration: underline;\">Student_id, Advisor<\/span>)<\/li>\r\n \t<li><strong>Adv_Maj<\/strong> (<span style=\"text-decoration: underline;\">Advisor<\/span>, Major)<\/li>\r\n<\/ol>\r\n<strong>St_Adv <\/strong>table \u00a0 \u00a0<strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0<\/strong>\r\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>Student_id<\/strong><\/td>\r\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">111<\/td>\r\n<td valign=\"top\">Smith<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">111<\/td>\r\n<td valign=\"top\">Chan<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">320<\/td>\r\n<td valign=\"top\">Dobbs<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">671<\/td>\r\n<td valign=\"top\">White<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">803<\/td>\r\n<td valign=\"top\">Smith<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<strong>Adv_Maj <\/strong>table<strong>\r\n<\/strong>\r\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\r\n<td valign=\"top\"><strong>Major<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">Smith<\/td>\r\n<td valign=\"top\">Physics<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">Chan<\/td>\r\n<td valign=\"top\">Music<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">Dobbs<\/td>\r\n<td valign=\"top\">Math<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">White<\/td>\r\n<td valign=\"top\">Physics<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<h3>BCNF Example 2<\/h3>\r\nConsider the following table (<strong>Client_Interview)<\/strong>.\r\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>ClientNo<\/strong><\/td>\r\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\r\n<td valign=\"top\"><strong>InterviewTime<\/strong><\/td>\r\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\r\n<td valign=\"top\"><strong>RoomNo<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR76<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">10.30<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<td valign=\"top\">G101<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR56<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">12.00<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<td valign=\"top\">G101<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR74<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">12.00<\/td>\r\n<td valign=\"top\">SG37<\/td>\r\n<td valign=\"top\">G102<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR56<\/td>\r\n<td valign=\"top\">1-July-02<\/td>\r\n<td valign=\"top\">10.30<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<td valign=\"top\">G102<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<span>FD1<\/span> \u2013 ClientNo, InterviewDate \u2013&gt; InterviewTime, StaffNo, RoomNo \u00a0(PK)\r\n\r\nFD2 \u2013 staffNo, interviewDate, interviewTime\u00a0\u2013&gt; clientNO \u00a0 \u00a0 \u00a0(candidate key: CK)\r\n\r\nFD3 \u2013 roomNo, interviewDate, interviewTime\u00a0\u2013&gt; staffNo, clientNo\u00a0\u00a0\u00a0 (CK)\r\n\r\nFD4 \u2013 staffNo, interviewDate\u00a0\u2013&gt; roomNo\r\n\r\nA 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 (<strong>Client_Interview2\u00a0<\/strong>table) and another table (<strong>StaffRoom<\/strong>\u00a0table) for the fourth FD.\r\n\r\n<strong>Client_Interview2\u00a0<\/strong>table\r\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>ClientNo<\/strong><\/td>\r\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\r\n<td valign=\"top\"><strong>InterViewTime<\/strong><\/td>\r\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR76<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">10.30<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR56<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">12.00<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR74<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">12.00<\/td>\r\n<td valign=\"top\">SG37<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">CR56<\/td>\r\n<td valign=\"top\">1-July-02<\/td>\r\n<td valign=\"top\">10.30<\/td>\r\n<td valign=\"top\">SG5<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<strong>StaffRoom<\/strong> table\r\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\r\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\r\n<td valign=\"top\"><strong>RoomNo<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">SG5<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">G101<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">SG37<\/td>\r\n<td valign=\"top\">13-May-02<\/td>\r\n<td valign=\"top\">G102<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\">SG5<\/td>\r\n<td valign=\"top\">1-July-02<\/td>\r\n<td valign=\"top\">G102<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<h2>Normalization and Database Design<\/h2>\r\n<span style=\"color: #444444;\">During 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.<\/span>\r\n<div class=\"textbox textbox--key-takeaways\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Key Terms and Abbrevations<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n<strong>Boyce-Codd normal form (BCNF)<\/strong>:\u00a0 a special case of 3rd NF\r\n\r\n<strong>first normal form (1NF):\u00a0<\/strong>only single values are permitted at the intersection of each row and column so there are no repeating groups\r\n\r\n<strong>normalization<\/strong>: the process of determining how much redundancy exists in a table\r\n\r\n<strong>second normal form (2NF)<\/strong>: the relation must be in 1NF and the PK comprises a single attribute\r\n\r\n<strong>semantic rules<\/strong>: business rules applied to the database\r\n\r\n<strong>third normal form (3NF)<\/strong>: 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\r\n<\/div>\r\n<\/div>\r\n<div class=\"textbox textbox--exercises\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Exercises<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n\r\n<strong><span style=\"color: #000000;\">Complete chapters 11 and 12 before doing these exercises.<\/span><\/strong>\r\n<ol>\r\n \t<li>What is normalization?<\/li>\r\n \t<li>When is a table in 1NF?<\/li>\r\n \t<li>When is a table in 2NF?<\/li>\r\n \t<li>When is a table in 3NF?<\/li>\r\n \t<li>\u00a0Identify and discuss each of the indicated dependencies in\u00a0the dependency diagram shown in Figure 12.2.\r\n\r\n[caption id=\"attachment_257\" align=\"aligncenter\" width=\"400\"]<a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943.jpg\"><img class=\"wp-image-257\" alt=\"Ch11-Exercises -Fig11-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943.jpg\" height=\"121\" width=\"400\" \/><\/a> Figure 12.2 For question 5, by A. Watt.[\/caption]<\/li>\r\n \t<li>To keep track of students and courses, a new college uses the table structure in Figure 12.3.\r\nDraw the dependency diagram for this table.\r\n\r\n[caption id=\"attachment_258\" align=\"aligncenter\" width=\"500\"]<a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289.jpg\"><img class=\"wp-image-258\" alt=\"Ch11-Exercises -Fig11-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289.jpg\" height=\"116\" width=\"500\" \/><\/a> Figure 12.3 For question\u00a06, by A. Watt.[\/caption]<\/li>\r\n \t<li>Using the dependency diagram you just drew, show the tables (in their third normal form) you would create to fix the problems you encountered. Draw the dependency diagram for the fixed table.<\/li>\r\n \t<li>An agency called Instant Cover supplies part-time\/temporary staff to hotels in Scotland. Figure 12.4 lists the time spent by agency staff working at various hotels. The national insurance number (NIN) is unique for every member of staff. Use Figure 12.4 to answer questions (a) and\u00a0(b).\r\n\r\n[caption id=\"attachment_259\" align=\"aligncenter\" width=\"400\"]<a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456.jpg\"><img class=\"wp-image-259\" alt=\"Ch12-Exercises -Fig12-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456.jpg\" height=\"94\" width=\"400\" \/><\/a> Figure 12.4 For question 8, by A. Watt.[\/caption]\r\n<ol>\r\n \t<li>This table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies.<\/li>\r\n \t<li>Normalize this table to third normal form. State any assumptions.<\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>Fill in the blanks:\r\n<ol>\r\n \t<li>____________________ produces a lower normal form.<\/li>\r\n \t<li>Any attribute whose value determines other values within a row is called a(n) ____________________.<\/li>\r\n \t<li>An attribute that cannot be further divided is said to display ____________________.<\/li>\r\n \t<li>____________________ refers to the level of detail represented by the values stored in a table\u2019s row.<\/li>\r\n \t<li>A relational table must not contain ____________________ groups.<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ol>\r\n<strong>Also see<\/strong> <em>Appendix B: Sample ERD Exercises<\/em>\r\n\r\n<\/div><\/div>\r\n<h2>Bibliography<\/h2>\r\n<span><span class=\"name\">Nguyen Kim Anh<\/span>, <\/span> <span><em>Relational Design Theory<\/em>. OpenStax CNX. <\/span> <span>8 Jul 2009 Retrieved July 2014 from <a class=\"external-link\" href=\"http:\/\/cnx.org\/contents\/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1\" rel=\"nofollow\">http:\/\/cnx.org\/contents\/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1<\/a><\/span>\r\n\r\nRussell, Gordon. Chapter 4 - Normalisation. <em>Database eLearning<\/em>. N.d. Retrived July 2014 from <a href=\"db.grussell.org\/ch4.html\">db.grussell.org\/ch4.html<\/a>","rendered":"<p>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.<\/p>\n<p>Use an entity relation diagram (ERD) to provide the big picture, or macro view, of an organization\u2019s data requirements and operations. This is created through an iterative process that involves identifying relevant entities, their attributes and their relationships.<\/p>\n<p>Normalization procedure focuses on characteristics of specific entities and represents the micro view of entities within the ERD.<\/p>\n<h2>What Is Normalization?<\/h2>\n<p><em>Normalization<\/em> 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:<\/p>\n<ul>\n<li>Be able to characterize the level of redundancy in a relational schema<\/li>\n<li>Provide mechanisms for transforming schemas in order to remove redundancy<\/li>\n<\/ul>\n<p>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\u00a0gives 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.<\/p>\n<h2>Normal Forms<\/h2>\n<p>All the tables in any database can be in one of the normal forms we will discuss next.\u00a0 Ideally we only want minimal redundancy for PK to FK. Everything else should be derived from other tables.\u00a0 There are six normal forms, but we will only look at the first four, which are:<\/p>\n<ul>\n<li>First\u00a0normal form (1NF)<\/li>\n<li>Second\u00a0normal form (2NF)<\/li>\n<li>Third normal form (3NF)<\/li>\n<li>Boyce-Codd normal form (BCNF)<\/li>\n<\/ul>\n<p>BCNF is rarely used.<\/p>\n<h2>First Normal Form (1NF)<\/h2>\n<p>In the <em>first normal form<\/em>, only single values are permitted at the intersection of each row and column; hence, there are no repeating groups.<\/p>\n<p>To normalize a relation that contains a repeating group, remove the repeating group and form two new relations.<\/p>\n<p>The PK\u00a0of the new relation is a combination of the PK\u00a0of the original relation plus an attribute from the newly created relation for unique identification.<\/p>\n<h3>Process for 1NF<\/h3>\n<p>We will use the <strong>Student_Grade_Repor<span><span>t<\/span>\u00a0<\/span><\/strong><span>table below<\/span>,\u00a0from a School database, as our example to explain the process for 1NF.<\/p>\n<div class=\"textbox\"><strong>Student_Grade_Report\u00a0<\/strong>(StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/div>\n<ul>\n<li>In the Student Grade Report table, the repeating group is the course information. A student can take many courses.<\/li>\n<li>Remove the repeating group. In this case, it\u2019s the course information for each student.<\/li>\n<li>Identify the PK for your new table.<\/li>\n<li>The PK must uniquely identify the attribute value (StudentNo and CourseNo).<\/li>\n<li>After removing all the attributes related to the course and student, you are left with the student course table (<strong>StudentCourse<\/strong>).<\/li>\n<li>The Student table (<strong>Student<\/strong>) is <span>now<\/span> in first normal form with the repeating group removed.<\/li>\n<li>The two new tables are shown below.<\/li>\n<\/ul>\n<div class=\"textbox\"><strong>Student<\/strong> (<span>StudentNo<\/span>, StudentName, Major)<\/div>\n<div class=\"textbox\"><strong>StudentCourse<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>,\u00a0CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/div>\n<h3>How to update 1NF anomalies<\/h3>\n<p><strong>StudentCourse<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/p>\n<ul>\n<li>To add a new course, we need a student.<\/li>\n<li>When course information needs to be updated, we may have inconsistencies.<\/li>\n<li>To delete a <em>student,<\/em> we might also delete critical information about a course.<\/li>\n<\/ul>\n<h2>Second Normal Form (2NF)<\/h2>\n<p>For the <em>second normal form<\/em>, the relation must first be in 1NF. The relation is automatically in 2NF if, and only if, the PK comprises a single attribute.<\/p>\n<p>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 <span>or<\/span> augmentation).<\/p>\n<h3>Process for 2NF<\/h3>\n<p>To move to 2NF, a table must first be in 1NF.<\/p>\n<ul>\n<li>The Student table is already in 2NF because it has a single-column PK.<\/li>\n<li>When examining the Student Course table, we see that not all the attributes are fully dependent on the PK; specifically, all course information. The only attribute that is fully dependent is grade.<\/li>\n<li>Identify the new table that contains the course information.<\/li>\n<li>Identify the PK for the new table.<\/li>\n<li>The three new tables are shown below.<\/li>\n<\/ul>\n<div class=\"textbox\"><strong>Student<\/strong> (<span>StudentNo<\/span>, StudentName, Major)<\/div>\n<div class=\"textbox\"><strong>CourseGrade<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, Grade)<\/div>\n<div class=\"textbox\"><strong>CourseInstructor <\/strong>(<span>CourseNo<\/span>, CourseName, InstructorNo, InstructorName, InstructorLocation)<\/div>\n<h3>How to update 2NF anomalies<\/h3>\n<ul>\n<li>When adding a new instructor, we need a course.<\/li>\n<li>Updating course information could lead to inconsistencies for instructor information.<\/li>\n<li>Deleting a course may also delete instructor information.<\/li>\n<\/ul>\n<h2>Third Normal Form (3NF)<\/h2>\n<p>To be in <em>third normal form<\/em>, 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.<\/p>\n<h3>Process for 3NF<\/h3>\n<ul>\n<li>Eliminate all dependent attributes in transitive relationship(s) from each of the tables that have a transitive relationship.<\/li>\n<li>Create new table(s) with removed dependency.<\/li>\n<li>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.<\/li>\n<li>See the four new tables below.<\/li>\n<\/ul>\n<div class=\"textbox\"><strong>Student<\/strong> (StudentNo, StudentName, Major)<\/div>\n<div class=\"textbox\"><strong>CourseGrade<\/strong> (<span style=\"text-decoration: underline;\">StudentNo, CourseNo<\/span>, Grade)<\/div>\n<div class=\"textbox\"><strong>Course<\/strong> (<span style=\"text-decoration: underline;\">CourseNo<\/span>, CourseName, InstructorNo)<\/div>\n<div class=\"textbox\"><strong>Instructor<\/strong> (InstructorNo, InstructorName, InstructorLocation)<\/div>\n<p>At this stage, there should be no anomalies in third normal <span style=\"color: #333333;\">form. Let&#8217;s look at the dependency diagram (Figure 12.1) for this example. The first step is to remove repeating groups, as discussed above.<\/span><\/p>\n<p><strong>Student<\/strong> (StudentNo, StudentName, Major)<\/p>\n<p><strong>StudentCourse<\/strong> (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)<\/p>\n<p>To recap the normalization process for the School database, review the dependencies shown in Figure 12.1.<\/p>\n<figure id=\"attachment_89\" aria-describedby=\"caption-attachment-89\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89.jpg\" alt=\"Ch-11-Dependency-Diagram-School-300x89\" class=\"wp-image-89 size-full\" height=\"89\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89-65x19.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-School-300x89-225x66.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-89\" class=\"wp-caption-text\">Figure 12.1 Dependency diagram, by A. Watt.<\/figcaption><\/figure>\n<p>The abbreviations used in Figure 12.1 are as follows:<\/p>\n<ul>\n<li>PD: partial dependency<\/li>\n<li>TD: \u00a0transitive dependency<\/li>\n<li><span style=\"color: #ff0000;\"><span style=\"color: #333333;\">FD: \u00a0full dependency (Note: FD typically stands for <strong>functional<\/strong> dependency. Using FD as an abbreviation for full dependency is only used in Figure 12.1.)<\/span><br \/>\n<\/span><\/li>\n<\/ul>\n<h2>Boyce-Codd Normal Form (BCNF)<\/h2>\n<p>When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. <em>Boyce-Codd normal form<\/em> is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key.<\/p>\n<h3>BCNF Example\u00a01<\/h3>\n<p>Consider the following table (<strong>St_Maj_Adv<\/strong>).<\/p>\n<table style=\"height: 126px; width: 202px; border-spacing: 0px;\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>Student_id<\/strong><\/td>\n<td valign=\"top\"><strong>Major<\/strong><\/td>\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">111<\/td>\n<td valign=\"top\">Physics<\/td>\n<td valign=\"top\">Smith<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">111<\/td>\n<td style=\"text-align: left;\" valign=\"top\">Music<\/td>\n<td valign=\"top\">Chan<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">320<\/td>\n<td valign=\"top\">Math<\/td>\n<td valign=\"top\">Dobbs<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">671<\/td>\n<td valign=\"top\">Physics<\/td>\n<td valign=\"top\">White<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">803<\/td>\n<td valign=\"top\">Physics<\/td>\n<td valign=\"top\">Smith<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>T<span style=\"color: #333333;\">he <em>semantic rules<\/em> (busines<\/span>s rules applied to the database) for this table are:<\/p>\n<ol>\n<li>Each Student may major in several subjects.<\/li>\n<li>For each Major, a given Student has only one Advisor.<\/li>\n<li>Each Major has several Advisors.<\/li>\n<li>Each Advisor <span style=\"color: #000000;\">advises<\/span> only one Major.<\/li>\n<li>Each Advisor<span style=\"color: #000000;\"> advises <\/span>several Students in one Major.<\/li>\n<\/ol>\n<p>The functional dependencies for this table are listed below. The first one is a candidate key; the second is not.<\/p>\n<ol>\n<li>Student_id, Major \u2014\u2014&gt;\u00a0 Advisor<\/li>\n<li>Advisor\u00a0 \u2014\u2014&gt;\u00a0 Major<\/li>\n<\/ol>\n<p>Anomalies for this table include:<\/p>\n<ol>\n<li>Delete\u00a0\u2013\u00a0student deletes advisor info<\/li>\n<li>Insert\u00a0\u2013 a new advisor needs a student<\/li>\n<li>Update \u2013 inconsistencies<\/li>\n<\/ol>\n<p><strong>Note<\/strong>: No single attribute is a candidate key.<\/p>\n<p>PK can be <span style=\"text-decoration: underline;\">Student_id, Major<\/span> or <span style=\"text-decoration: underline;\">Student_id, Advisor<\/span><strong>.<\/strong><\/p>\n<p>To reduce the <strong>St_Maj_Adv<\/strong> relation to BCNF, you create two new tables:<\/p>\n<ol>\n<li><strong>St_Adv<\/strong> (<span style=\"text-decoration: underline;\">Student_id, Advisor<\/span>)<\/li>\n<li><strong>Adv_Maj<\/strong> (<span style=\"text-decoration: underline;\">Advisor<\/span>, Major)<\/li>\n<\/ol>\n<p><strong>St_Adv <\/strong>table \u00a0 \u00a0<strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0<\/strong><\/p>\n<table cellpadding=\"0\" style=\"border-spacing: 0px;\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>Student_id<\/strong><\/td>\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">111<\/td>\n<td valign=\"top\">Smith<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">111<\/td>\n<td valign=\"top\">Chan<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">320<\/td>\n<td valign=\"top\">Dobbs<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">671<\/td>\n<td valign=\"top\">White<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">803<\/td>\n<td valign=\"top\">Smith<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Adv_Maj <\/strong>table<strong><br \/>\n<\/strong><\/p>\n<table cellpadding=\"0\" style=\"border-spacing: 0px;\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>Advisor<\/strong><\/td>\n<td valign=\"top\"><strong>Major<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">Smith<\/td>\n<td valign=\"top\">Physics<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">Chan<\/td>\n<td valign=\"top\">Music<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">Dobbs<\/td>\n<td valign=\"top\">Math<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">White<\/td>\n<td valign=\"top\">Physics<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>BCNF Example 2<\/h3>\n<p>Consider the following table (<strong>Client_Interview)<\/strong>.<\/p>\n<table cellpadding=\"0\" style=\"border-spacing: 0px;\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>ClientNo<\/strong><\/td>\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\n<td valign=\"top\"><strong>InterviewTime<\/strong><\/td>\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\n<td valign=\"top\"><strong>RoomNo<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR76<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">10.30<\/td>\n<td valign=\"top\">SG5<\/td>\n<td valign=\"top\">G101<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR56<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">12.00<\/td>\n<td valign=\"top\">SG5<\/td>\n<td valign=\"top\">G101<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR74<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">12.00<\/td>\n<td valign=\"top\">SG37<\/td>\n<td valign=\"top\">G102<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR56<\/td>\n<td valign=\"top\">1-July-02<\/td>\n<td valign=\"top\">10.30<\/td>\n<td valign=\"top\">SG5<\/td>\n<td valign=\"top\">G102<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span>FD1<\/span> \u2013 ClientNo, InterviewDate \u2013&gt; InterviewTime, StaffNo, RoomNo \u00a0(PK)<\/p>\n<p>FD2 \u2013 staffNo, interviewDate, interviewTime\u00a0\u2013&gt; clientNO \u00a0 \u00a0 \u00a0(candidate key: CK)<\/p>\n<p>FD3 \u2013 roomNo, interviewDate, interviewTime\u00a0\u2013&gt; staffNo, clientNo\u00a0\u00a0\u00a0 (CK)<\/p>\n<p>FD4 \u2013 staffNo, interviewDate\u00a0\u2013&gt; roomNo<\/p>\n<p>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 (<strong>Client_Interview2\u00a0<\/strong>table) and another table (<strong>StaffRoom<\/strong>\u00a0table) for the fourth FD.<\/p>\n<p><strong>Client_Interview2\u00a0<\/strong>table<\/p>\n<table cellpadding=\"0\" style=\"border-spacing: 0px;\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>ClientNo<\/strong><\/td>\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\n<td valign=\"top\"><strong>InterViewTime<\/strong><\/td>\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR76<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">10.30<\/td>\n<td valign=\"top\">SG5<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR56<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">12.00<\/td>\n<td valign=\"top\">SG5<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR74<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">12.00<\/td>\n<td valign=\"top\">SG37<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">CR56<\/td>\n<td valign=\"top\">1-July-02<\/td>\n<td valign=\"top\">10.30<\/td>\n<td valign=\"top\">SG5<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>StaffRoom<\/strong> table<\/p>\n<table cellpadding=\"0\" style=\"border-spacing: 0px;\">\n<tbody>\n<tr>\n<td valign=\"top\"><strong>StaffNo<\/strong><\/td>\n<td valign=\"top\"><strong>InterviewDate<\/strong><\/td>\n<td valign=\"top\"><strong>RoomNo<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">SG5<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">G101<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">SG37<\/td>\n<td valign=\"top\">13-May-02<\/td>\n<td valign=\"top\">G102<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">SG5<\/td>\n<td valign=\"top\">1-July-02<\/td>\n<td valign=\"top\">G102<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Normalization and Database Design<\/h2>\n<p><span style=\"color: #444444;\">During 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.<\/span><\/p>\n<div class=\"textbox textbox--key-takeaways\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Key Terms and Abbrevations<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<strong>Boyce-Codd normal form (BCNF)<\/strong>:\u00a0 a special case of 3rd NF<\/p>\n<p><strong>first normal form (1NF):\u00a0<\/strong>only single values are permitted at the intersection of each row and column so there are no repeating groups<\/p>\n<p><strong>normalization<\/strong>: the process of determining how much redundancy exists in a table<\/p>\n<p><strong>second normal form (2NF)<\/strong>: the relation must be in 1NF and the PK comprises a single attribute<\/p>\n<p><strong>semantic rules<\/strong>: business rules applied to the database<\/p>\n<p><strong>third normal form (3NF)<\/strong>: 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\n<\/div>\n<\/div>\n<div class=\"textbox textbox--exercises\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Exercises<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<p><strong><span style=\"color: #000000;\">Complete chapters 11 and 12 before doing these exercises.<\/span><\/strong><\/p>\n<ol>\n<li>What is normalization?<\/li>\n<li>When is a table in 1NF?<\/li>\n<li>When is a table in 2NF?<\/li>\n<li>When is a table in 3NF?<\/li>\n<li>\u00a0Identify and discuss each of the indicated dependencies in\u00a0the dependency diagram shown in Figure 12.2.<br \/>\n<figure id=\"attachment_257\" aria-describedby=\"caption-attachment-257\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-257\" alt=\"Ch11-Exercises -Fig11-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943.jpg\" height=\"121\" width=\"400\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943.jpg 857w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943-300x90.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943-65x19.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943-225x67.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-1-e1409835870943-350x105.jpg 350w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-257\" class=\"wp-caption-text\">Figure 12.2 For question 5, by A. Watt.<\/figcaption><\/figure>\n<\/li>\n<li>To keep track of students and courses, a new college uses the table structure in Figure 12.3.<br \/>\nDraw the dependency diagram for this table.<\/p>\n<figure id=\"attachment_258\" aria-describedby=\"caption-attachment-258\" style=\"width: 500px\" class=\"wp-caption aligncenter\"><a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-258\" alt=\"Ch11-Exercises -Fig11-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289.jpg\" height=\"116\" width=\"500\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289.jpg 1229w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289-300x69.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289-1024x238.jpg 1024w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289-65x15.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289-225x52.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch11-Exercises-Fig11-2-e1409836100289-350x81.jpg 350w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/a><figcaption id=\"caption-attachment-258\" class=\"wp-caption-text\">Figure 12.3 For question\u00a06, by A. Watt.<\/figcaption><\/figure>\n<\/li>\n<li>Using the dependency diagram you just drew, show the tables (in their third normal form) you would create to fix the problems you encountered. Draw the dependency diagram for the fixed table.<\/li>\n<li>An agency called Instant Cover supplies part-time\/temporary staff to hotels in Scotland. Figure 12.4 lists the time spent by agency staff working at various hotels. The national insurance number (NIN) is unique for every member of staff. Use Figure 12.4 to answer questions (a) and\u00a0(b).<br \/>\n<figure id=\"attachment_259\" aria-describedby=\"caption-attachment-259\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-259\" alt=\"Ch12-Exercises -Fig12-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456.jpg\" height=\"94\" width=\"400\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456.jpg 927w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456-300x70.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456-65x15.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456-225x52.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch12-Exercises-Fig12-1-e1409836269456-350x81.jpg 350w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-259\" class=\"wp-caption-text\">Figure 12.4 For question 8, by A. Watt.<\/figcaption><\/figure>\n<ol>\n<li>This table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies.<\/li>\n<li>Normalize this table to third normal form. State any assumptions.<\/li>\n<\/ol>\n<\/li>\n<li>Fill in the blanks:\n<ol>\n<li>____________________ produces a lower normal form.<\/li>\n<li>Any attribute whose value determines other values within a row is called a(n) ____________________.<\/li>\n<li>An attribute that cannot be further divided is said to display ____________________.<\/li>\n<li>____________________ refers to the level of detail represented by the values stored in a table\u2019s row.<\/li>\n<li>A relational table must not contain ____________________ groups.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><strong>Also see<\/strong> <em>Appendix B: Sample ERD Exercises<\/em><\/p>\n<\/div>\n<\/div>\n<h2>Bibliography<\/h2>\n<p><span><span class=\"name\">Nguyen Kim Anh<\/span>, <\/span> <span><em>Relational Design Theory<\/em>. OpenStax CNX. <\/span> <span>8 Jul 2009 Retrieved July 2014 from <a class=\"external-link\" href=\"http:\/\/cnx.org\/contents\/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1\" rel=\"nofollow\">http:\/\/cnx.org\/contents\/606cc532-0b1d-419d-a0ec-ac4e2e2d533b@1@1<\/a><\/span><\/p>\n<p>Russell, Gordon. Chapter 4 &#8211; Normalisation. <em>Database eLearning<\/em>. N.d. Retrived July 2014 from <a href=\"db.grussell.org\/ch4.html\">db.grussell.org\/ch4.html<\/a><\/p>\n","protected":false},"author":1,"menu_order":12,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["adrienne-watt"],"pb_section_license":""},"chapter-type":[],"contributor":[47],"license":[],"class_list":["post-90","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/90","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/users\/1"}],"version-history":[{"count":26,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/90\/revisions"}],"predecessor-version":[{"id":1151,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/90\/revisions\/1151"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/90\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=90"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=90"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=90"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=90"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}