{"id":26,"date":"2023-01-23T16:34:46","date_gmt":"2023-01-23T16:34:46","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-4-chapter-notes\/"},"modified":"2023-03-24T14:17:52","modified_gmt":"2023-03-24T14:17:52","slug":"module-4-chapter-notes","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/chapter\/module-4-chapter-notes\/","title":{"raw":"Module 4 Chapter Notes","rendered":"Module 4 Chapter Notes"},"content":{"raw":"<h2>Chapter 6 Notes for Module 4<\/h2>\n<strong>What is Normalization?<\/strong>\n\nNormalization is the process for assigning attributes to entities. Properly executed, the normalization process eliminates uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies.\n\nNormalization does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.\n<ul>\n \t<li>Evaluating and correcting table structures to minimize data redundancies<\/li>\n \t<li>Reduces data anomalies<\/li>\n \t<li>Assigns attributes to tables based on determination<\/li>\n \t<li>Normal forms<\/li>\n \t<li>First normal form (1NF)<\/li>\n \t<li>Second normal form (2NF)<\/li>\n \t<li>Third normal form (3NF)<\/li>\n<\/ul>\n<strong>What is the Normalization Process?<\/strong>\n<ul>\n \t<li>Objective is to ensure that each table conforms to the concept of well-formed relations\n<ul>\n \t<li>Each table represents a single subject<\/li>\n \t<li>No data item will be unnecessarily stored in more than one table<\/li>\n \t<li>All nonprime attributes in a table are dependent on the primary key<\/li>\n \t<li>Each table is void of insertion, update, and deletion anomalies<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<strong>When is a table in 1NF?<\/strong>\n\nA table is in 1NF when all the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key. However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and\/or transitive dependencies that are based on a non-key attribute.\n\n<strong>When is a table in 2NF?<\/strong>\n\nA table is in 2NF when it is in 1NF and it includes no partial dependencies. However, a table in 2NF may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.\n\n<strong>When is a table in 3NF?<\/strong>\n\nA table is in 3NF when it is in 2NF and it contains no transitive dependencies.\n<p class=\"page-title\"><strong>When is a table in BCNF?<\/strong><\/p>\nA table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF and every determinant in the table is a candidate key. For example, if the table is in 3NF\u00a0<i>and it contains a nonprime attribute that determines a prime attribute<\/i>, the BCNF requirements are not met. (Reference the text's Figure 6.8 to support this discussion.)This description clearly yields the following conclusions:\n<ul>\n \t<li>If a table is in 3NF and it contains only one candidate key, 3NF and BCNF are equivalent.<\/li>\n \t<li>BCNF can be violated only if the table contains more than one candidate key. Putting it another way, there is no way that the BCNF requirement can be violated if there is only one candidate key.<\/li>\n<\/ul>\n<p class=\"page-title\"><strong>What are the types of functional dependencies?<\/strong><\/p>\n\n<ul>\n \t<li><em><strong>Partial dependency:<\/strong><\/em>\u00a0Functional dependence in which the determinant is only part of the primary key\n<ul>\n \t<li>Assumption - One candidate key<\/li>\n \t<li>Straight forward<\/li>\n \t<li>Easy to identify<\/li>\n<\/ul>\n<\/li>\n \t<li><em><strong>Transitive dependency<\/strong><\/em>: An attribute functionally depends on another nonkey attribute<\/li>\n<\/ul>","rendered":"<h2>Chapter 6 Notes for Module 4<\/h2>\n<p><strong>What is Normalization?<\/strong><\/p>\n<p>Normalization is the process for assigning attributes to entities. Properly executed, the normalization process eliminates uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies.<\/p>\n<p>Normalization does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.<\/p>\n<ul>\n<li>Evaluating and correcting table structures to minimize data redundancies<\/li>\n<li>Reduces data anomalies<\/li>\n<li>Assigns attributes to tables based on determination<\/li>\n<li>Normal forms<\/li>\n<li>First normal form (1NF)<\/li>\n<li>Second normal form (2NF)<\/li>\n<li>Third normal form (3NF)<\/li>\n<\/ul>\n<p><strong>What is the Normalization Process?<\/strong><\/p>\n<ul>\n<li>Objective is to ensure that each table conforms to the concept of well-formed relations\n<ul>\n<li>Each table represents a single subject<\/li>\n<li>No data item will be unnecessarily stored in more than one table<\/li>\n<li>All nonprime attributes in a table are dependent on the primary key<\/li>\n<li>Each table is void of insertion, update, and deletion anomalies<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><strong>When is a table in 1NF?<\/strong><\/p>\n<p>A table is in 1NF when all the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key. However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and\/or transitive dependencies that are based on a non-key attribute.<\/p>\n<p><strong>When is a table in 2NF?<\/strong><\/p>\n<p>A table is in 2NF when it is in 1NF and it includes no partial dependencies. However, a table in 2NF may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.<\/p>\n<p><strong>When is a table in 3NF?<\/strong><\/p>\n<p>A table is in 3NF when it is in 2NF and it contains no transitive dependencies.<\/p>\n<p class=\"page-title\"><strong>When is a table in BCNF?<\/strong><\/p>\n<p>A table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF and every determinant in the table is a candidate key. For example, if the table is in 3NF\u00a0<i>and it contains a nonprime attribute that determines a prime attribute<\/i>, the BCNF requirements are not met. (Reference the text&#8217;s Figure 6.8 to support this discussion.)This description clearly yields the following conclusions:<\/p>\n<ul>\n<li>If a table is in 3NF and it contains only one candidate key, 3NF and BCNF are equivalent.<\/li>\n<li>BCNF can be violated only if the table contains more than one candidate key. Putting it another way, there is no way that the BCNF requirement can be violated if there is only one candidate key.<\/li>\n<\/ul>\n<p class=\"page-title\"><strong>What are the types of functional dependencies?<\/strong><\/p>\n<ul>\n<li><em><strong>Partial dependency:<\/strong><\/em>\u00a0Functional dependence in which the determinant is only part of the primary key\n<ul>\n<li>Assumption &#8211; One candidate key<\/li>\n<li>Straight forward<\/li>\n<li>Easy to identify<\/li>\n<\/ul>\n<\/li>\n<li><em><strong>Transitive dependency<\/strong><\/em>: An attribute functionally depends on another nonkey attribute<\/li>\n<\/ul>\n","protected":false},"author":11,"menu_order":4,"template":"","meta":{"pb_show_title":"","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[49],"contributor":[],"license":[],"class_list":["post-26","chapter","type-chapter","status-publish","hentry","chapter-type-numberless"],"part":19,"_links":{"self":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/26","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/users\/11"}],"version-history":[{"count":1,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/26\/revisions"}],"predecessor-version":[{"id":27,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/26\/revisions\/27"}],"part":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/parts\/19"}],"metadata":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapters\/26\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/media?parent=26"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/pressbooks\/v2\/chapter-type?post=26"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/contributor?post=26"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.usnh.edu\/databasemanagementsystems\/wp-json\/wp\/v2\/license?post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}