{"id":80,"date":"2013-12-12T00:09:55","date_gmt":"2013-12-12T00:09:55","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=80"},"modified":"2019-06-11T16:40:28","modified_gmt":"2019-06-11T16:40:28","slug":"chapter-10-er-modelling","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/chapter-10-er-modelling\/","title":{"raw":"Chapter 10 ER Modelling","rendered":"Chapter 10 ER Modelling"},"content":{"raw":"One important theory developed for the<span> entity<\/span> relational <span>(ER)<\/span> model involves the notion of functional dependency (FD). \u00a0The aim of studying this is to improve your understanding of relationships among data and to gain enough formalism to assist with practical database design.\r\n\r\nLike constraints, FDs\u00a0are drawn from the semantics of the application domain. Essentially, \u00a0<em>functional dependencies<\/em> describe how individual attributes are related. FDs are a kind of constraint among attributes within a relation and <span>contribute to a good relational schema design. In this chapter, we will look at:<\/span>\r\n<ul>\r\n \t<li>The basic theory and definition of functional dependency<\/li>\r\n \t<li>The methodology for improving schema <span>designs, also called normalization<\/span><\/li>\r\n<\/ul>\r\n<h2>Relational Design and Redundancy<\/h2>\r\nGenerally, a good relational database design must capture all of the necessary attributes<span> and<\/span> associations. The design should do this with a minimal amount of stored information <span>and<\/span> no redundant data.\r\n\r\nIn database design, redundancy is generally undesirable because it causes problems maintaining consistency after updates. However, <span>redundancy<\/span> can sometimes lead to performance improvemen<span>ts;\u00a0for example,\u00a0when redundancy can be used in place of\u00a0a <em>join<\/em> to connect data. A<em> join<\/em> is used when you need to obtain information based on two related tables.<\/span>\r\n\r\nConsider Figure 10.1: \u00a0customer 1313131 is displayed twice, once for account no. A-101 and again for account A-102. <span><span>In this case, the customer number is not redundant, although there are deletion anomalies with the table. Having a separate customer table would solve this problem. However, if a branch address were to change, it would have to be updated in multiple places. If the customer number was left in the table as is, then you wouldn't need a branch table and no join would be required, and performance is improved .\u00a0<span style=\"color: #ff0000;\"><\/span><\/span><\/span>\r\n\r\n[caption id=\"attachment_72\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197.jpg\" alt=\"Bank-Accounts-1-300x197\" width=\"300\" height=\"197\" class=\"wp-image-72 size-full\" \/><\/a> Figure 10.1. An example of redundancy used with bank accounts and branches.[\/caption]\r\n<h2>Insertion Anomaly<\/h2>\r\n<span>An<em> insertion anomaly<\/em> occurs when you are inserting inconsistent information into a table.\u00a0<\/span>When we insert a new record, <span>such as account no. A-306 in Figure 10.2<\/span>, we need to check that the branch data is consistent with existing rows.\r\n\r\n[caption id=\"attachment_73\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222.jpg\"><img class=\"wp-image-73 size-full\" alt=\"Insertion-Anomaly-Banking-Accounts-300x222\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222.jpg\" height=\"222\" width=\"300\" \/><\/a> Figure 10.2. Example of an insertion anomaly.[\/caption]\r\n<h2>Update Anomaly<\/h2>\r\nIf a branch changes address, such as the Round Hill branch in Figure 10.3, we need to update all rows referring to that branch. Changing existing informat<span>ion incorrectly\u00a0is called an <em>update anomaly<\/em>.<\/span>\r\n\r\n[caption id=\"attachment_74\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198.jpg\"><img class=\"wp-image-74 size-full\" alt=\"Update-Anomaly-Bank-Accounts-300x198\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198.jpg\" height=\"198\" width=\"300\" \/><\/a> Figure 10.3. Example of an update anomaly.[\/caption]\r\n<h2>Deletion Anomaly<\/h2>\r\n<span style=\"color: #333333;\">A <em>deletion anomaly<\/em>\u00a0occurs when you delete a record that may contain attributes that shouldn't be deleted.\u00a0<\/span>For instance, if we remove information about the last account at a branch, such as account A-101 at the Downtown branch in Figure 10.4, all of the branch information disappears.\r\n\r\n[caption id=\"attachment_75\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195.jpg\"><img class=\"wp-image-75 size-full\" alt=\"Deletion-anomaly-Bank-Account-300x195\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195.jpg\" height=\"195\" width=\"300\" \/><\/a> Figure 10.4. Example of a deletion anomaly.[\/caption]\r\n\r\nThe problem with deleting the A-101 row is we don\u2019t know where the Downtown branch is located and we lose all information regarding customer 1313131. To avoid these kinds of update or deletion problems, we need to decompose the original table into several smaller tables where each table has minimal overlap with other tables.\r\n\r\nEach bank account table must\u00a0contain information about one entity only, such as the \u00a0Branch or Customer, as displayed in Figure 10.5.\r\n\r\n[caption id=\"attachment_76\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117.jpg\"><img class=\"wp-image-76 size-full\" alt=\"Ch-10-Branch-to-Customer-ERD-300x117\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117.jpg\" height=\"117\" width=\"300\" \/><\/a> Figure 10.5. Examples of bank account tables that contain one entity each, by A. Watt.[\/caption]\r\n\r\nFollowing this practice will ensure that when branch information is added or updated it will only affect one record. So, when customer information is added or deleted, the branch information will not be accidentally modified or incorrectly recorded.\r\n<h3>Example: employee project table and anomalies<\/h3>\r\nFigure 10.6 shows an example of an employee project table. <span>From th<span style=\"color: #000000;\">is table, we can assume that<\/span><\/span><span style=\"color: #000000;\">:<\/span>\r\n<ol>\r\n \t<li><span>EmpID and ProjectID are a composite PK.<\/span><\/li>\r\n \t<li><span>Project ID determines Budget\u00a0(i.e., Project P1 has a budget of 32 hours).<\/span><\/li>\r\n<\/ol>\r\n[caption id=\"attachment_77\" align=\"aligncenter\" width=\"291\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table.jpg\"><img class=\"wp-image-77 size-full\" alt=\"Ch-10-ProjectEmp-table\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table.jpg\" height=\"152\" width=\"291\" \/><\/a> Figure 10.6. Example of an employee project table, by A. Watt.[\/caption]\r\n\r\n<span style=\"color: #000000;\">Next, let\u2019s look at some possi<\/span>ble anomalies <span>that might occur with this table during the following steps<\/span>.\r\n<ol>\r\n \t<li><span>Action:<\/span> Add row {S85,35,P1,9}<\/li>\r\n \t<li>Problem: There are two tuples with conflicting budgets<\/li>\r\n \t<li><span>Action:<\/span> Delete tuple {S79, 27, P3, 1}<\/li>\r\n \t<li><span>Problem<\/span>: <span>Step #3<\/span> deletes the budget for project P3<\/li>\r\n \t<li><span>Action<\/span>: Update tuple {S75, 32, P1, 7} to {S75, 35, P1, 7}<\/li>\r\n \t<li>Problem: <span>Step #5 creates<\/span> two tuples with different values for project P1\u2019s budget<\/li>\r\n \t<li><span>Solution: Create a separate table, each, for Projects and Employees, as shown in Figure 10.7.<\/span><\/li>\r\n<\/ol>\r\n[caption id=\"attachment_78\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114.jpg\"><img class=\"wp-image-78 size-full\" alt=\"Ch-10-Project-to-Emp-ERD-300x114\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114.jpg\" height=\"114\" width=\"300\" \/><\/a> Figure 10.7. Solution: separate tables for Project and Employee, by A. Watt.[\/caption]\r\n<h2><span style=\"color: #000000;\">How to Avoid Anomalies<\/span><\/h2>\r\nThe best approach to creating tables without anomalies is to ensure that the tables are normalized, and that\u2019s accomplished by understanding functional dependencies.\u00a0FD ensures that all attributes in a table belong to that table. In other words, it will eliminate redundancies and anomalies.\r\n<h3>Example: separate Project and Employee tables<\/h3>\r\n[caption id=\"attachment_79\" align=\"aligncenter\" width=\"400\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89.jpg\"><img class=\"wp-image-79\" alt=\"Ch-10-Project-and-Emp-tables-300x89\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89.jpg\" height=\"119\" width=\"400\" \/><\/a> Figure 10.8. Separate Project and Employee tables with data, by A. Watt.[\/caption]\r\n\r\n<span>By keeping data separate using individual Project and Employee tables:<\/span>\r\n<ol>\r\n \t<li>No anomalies will be created if a budget is changed.<\/li>\r\n \t<li>No dummy values are needed for projects that have no employees assigned.<\/li>\r\n \t<li>If an employee\u2019s contribution is deleted, no important data is lost.<\/li>\r\n \t<li>No anomalies are created if an employee\u2019s contribution is added.<\/li>\r\n<\/ol>\r\n<div class=\"textbox textbox--key-takeaways\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Key Terms<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n\r\n<strong>deletion anomaly<\/strong>:\u00a0occurs when you delete a record that may contain attributes that shouldn't be deleted\r\n\r\n<strong>functional dependency (FD)<\/strong>:\u00a0describes how individual attributes are related\r\n\r\n<strong>insertion anomaly<\/strong>:\u00a0occurs when you are inserting inconsistent information into a table\r\n\r\n<span><strong>join<\/strong>:\u00a0<span style=\"color: #000000;\">used when you need to obtain information based on two related tables<\/span><\/span>\r\n\r\n<strong>update anomaly<\/strong>: changing existing information incorrectly\r\n\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<ol>\r\n \t<li>Normalize Figure 10.9.\r\n\r\n[caption id=\"attachment_256\" align=\"aligncenter\" width=\"600\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977.jpg\"><img class=\"wp-image-256\" alt=\"Ch10-Exercises -Fig10-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977.jpg\" height=\"141\" width=\"600\" \/><\/a> Figure 10.9. Table for question 1, by A. Watt.[\/caption]<\/li>\r\n \t<li>Create a logical ERD for an online movie rental service (no many to many relationships). Use the following description of operations on which your business rules must be based:The <span>online movie rental service<\/span>\u00a0classifies movie titles according to their type: comedy, western, classical, science fiction, cartoon, action, musical, and new release. Each type contains many possible titles, and most titles within a type are available in multiple copies. For example, note the following summary:TYPE TITLE\r\nMusical My Fair Lady (Copy 1)\r\n<span>My Fair Lady (Copy 2)\r\n<\/span><span>Oklahoma (Copy 1)\r\n<\/span><span>Oklahoma (Copy 2)\r\n<\/span><span>Oklahoma (Copy 3)\r\n<\/span><span>etc.\u00a0<\/span><\/li>\r\n \t<li>What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?<\/li>\r\n<\/ol>\r\n<p style=\"color: #1f1f1d;\"><strong>Also see<\/strong>\u00a0\u00a0<em>Appendix B: Sample ERD Exercises<\/em><\/p>\r\n\r\n<\/div>\r\n<\/div>\r\n<h2>Attribution<\/h2>\r\n<span style=\"color: #1f1f1d;\">This chapter of\u00a0<\/span><em style=\"color: #1f1f1d;\">Database Design<\/em><span style=\"color: #1f1f1d;\"> (including images, except as otherwise noted) is a derivative copy of\u00a0<a href=\"http:\/\/cnx.org\/contents\/e5ac0441-0e54-4895-9112-fb3a4ee9bce1@1\" target=\"_blank\" rel=\"noopener\">Relational Design Theory<\/a><\/span><span style=\"color: #1f1f1d;\">\u00a0by<\/span><span style=\"color: #1f1f1d;\">\u00a0Nguyen Kim Anh<\/span><span style=\"color: #1f1f1d;\">\u00a0licensed under\u00a0<\/span><a style=\"color: #870d0d;\" href=\"http:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons Attribution License 3.0 license<\/a>\r\n\r\nThe following material was written by Adrienne Watt:\r\n<ol>\r\n \t<li>Example: employee project table and anomalies<\/li>\r\n \t<li>How to Avoid Anomalies<\/li>\r\n \t<li>Key Terms<\/li>\r\n \t<li>Exercises<\/li>\r\n<\/ol>","rendered":"<p>One important theory developed for the<span> entity<\/span> relational <span>(ER)<\/span> model involves the notion of functional dependency (FD). \u00a0The aim of studying this is to improve your understanding of relationships among data and to gain enough formalism to assist with practical database design.<\/p>\n<p>Like constraints, FDs\u00a0are drawn from the semantics of the application domain. Essentially, \u00a0<em>functional dependencies<\/em> describe how individual attributes are related. FDs are a kind of constraint among attributes within a relation and <span>contribute to a good relational schema design. In this chapter, we will look at:<\/span><\/p>\n<ul>\n<li>The basic theory and definition of functional dependency<\/li>\n<li>The methodology for improving schema <span>designs, also called normalization<\/span><\/li>\n<\/ul>\n<h2>Relational Design and Redundancy<\/h2>\n<p>Generally, a good relational database design must capture all of the necessary attributes<span> and<\/span> associations. The design should do this with a minimal amount of stored information <span>and<\/span> no redundant data.<\/p>\n<p>In database design, redundancy is generally undesirable because it causes problems maintaining consistency after updates. However, <span>redundancy<\/span> can sometimes lead to performance improvemen<span>ts;\u00a0for example,\u00a0when redundancy can be used in place of\u00a0a <em>join<\/em> to connect data. A<em> join<\/em> is used when you need to obtain information based on two related tables.<\/span><\/p>\n<p>Consider Figure 10.1: \u00a0customer 1313131 is displayed twice, once for account no. A-101 and again for account A-102. <span><span>In this case, the customer number is not redundant, although there are deletion anomalies with the table. Having a separate customer table would solve this problem. However, if a branch address were to change, it would have to be updated in multiple places. If the customer number was left in the table as is, then you wouldn&#8217;t need a branch table and no join would be required, and performance is improved .\u00a0<span style=\"color: #ff0000;\"><\/span><\/span><\/span><\/p>\n<figure id=\"attachment_72\" aria-describedby=\"caption-attachment-72\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197.jpg\" alt=\"Bank-Accounts-1-300x197\" width=\"300\" height=\"197\" class=\"wp-image-72 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Bank-Accounts-1-300x197-225x147.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-72\" class=\"wp-caption-text\">Figure 10.1. An example of redundancy used with bank accounts and branches.<\/figcaption><\/figure>\n<h2>Insertion Anomaly<\/h2>\n<p><span>An<em> insertion anomaly<\/em> occurs when you are inserting inconsistent information into a table.\u00a0<\/span>When we insert a new record, <span>such as account no. A-306 in Figure 10.2<\/span>, we need to check that the branch data is consistent with existing rows.<\/p>\n<figure id=\"attachment_73\" aria-describedby=\"caption-attachment-73\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-73 size-full\" alt=\"Insertion-Anomaly-Banking-Accounts-300x222\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222.jpg\" height=\"222\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222-65x48.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Insertion-Anomaly-Banking-Accounts-300x222-225x166.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-73\" class=\"wp-caption-text\">Figure 10.2. Example of an insertion anomaly.<\/figcaption><\/figure>\n<h2>Update Anomaly<\/h2>\n<p>If a branch changes address, such as the Round Hill branch in Figure 10.3, we need to update all rows referring to that branch. Changing existing informat<span>ion incorrectly\u00a0is called an <em>update anomaly<\/em>.<\/span><\/p>\n<figure id=\"attachment_74\" aria-describedby=\"caption-attachment-74\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-74 size-full\" alt=\"Update-Anomaly-Bank-Accounts-300x198\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198.jpg\" height=\"198\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Update-Anomaly-Bank-Accounts-300x198-225x148.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-74\" class=\"wp-caption-text\">Figure 10.3. Example of an update anomaly.<\/figcaption><\/figure>\n<h2>Deletion Anomaly<\/h2>\n<p><span style=\"color: #333333;\">A <em>deletion anomaly<\/em>\u00a0occurs when you delete a record that may contain attributes that shouldn&#8217;t be deleted.\u00a0<\/span>For instance, if we remove information about the last account at a branch, such as account A-101 at the Downtown branch in Figure 10.4, all of the branch information disappears.<\/p>\n<figure id=\"attachment_75\" aria-describedby=\"caption-attachment-75\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-75 size-full\" alt=\"Deletion-anomaly-Bank-Account-300x195\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195.jpg\" height=\"195\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Deletion-anomaly-Bank-Account-300x195-225x146.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-75\" class=\"wp-caption-text\">Figure 10.4. Example of a deletion anomaly.<\/figcaption><\/figure>\n<p>The problem with deleting the A-101 row is we don\u2019t know where the Downtown branch is located and we lose all information regarding customer 1313131. To avoid these kinds of update or deletion problems, we need to decompose the original table into several smaller tables where each table has minimal overlap with other tables.<\/p>\n<p>Each bank account table must\u00a0contain information about one entity only, such as the \u00a0Branch or Customer, as displayed in Figure 10.5.<\/p>\n<figure id=\"attachment_76\" aria-describedby=\"caption-attachment-76\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-76 size-full\" alt=\"Ch-10-Branch-to-Customer-ERD-300x117\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117.jpg\" height=\"117\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117-65x25.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Branch-to-Customer-ERD-300x117-225x87.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-76\" class=\"wp-caption-text\">Figure 10.5. Examples of bank account tables that contain one entity each, by A. Watt.<\/figcaption><\/figure>\n<p>Following this practice will ensure that when branch information is added or updated it will only affect one record. So, when customer information is added or deleted, the branch information will not be accidentally modified or incorrectly recorded.<\/p>\n<h3>Example: employee project table and anomalies<\/h3>\n<p>Figure 10.6 shows an example of an employee project table. <span>From th<span style=\"color: #000000;\">is table, we can assume that<\/span><\/span><span style=\"color: #000000;\">:<\/span><\/p>\n<ol>\n<li><span>EmpID and ProjectID are a composite PK.<\/span><\/li>\n<li><span>Project ID determines Budget\u00a0(i.e., Project P1 has a budget of 32 hours).<\/span><\/li>\n<\/ol>\n<figure id=\"attachment_77\" aria-describedby=\"caption-attachment-77\" style=\"width: 291px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-77 size-full\" alt=\"Ch-10-ProjectEmp-table\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table.jpg\" height=\"152\" width=\"291\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table.jpg 291w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table-65x33.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-ProjectEmp-table-225x117.jpg 225w\" sizes=\"auto, (max-width: 291px) 100vw, 291px\" \/><\/a><figcaption id=\"caption-attachment-77\" class=\"wp-caption-text\">Figure 10.6. Example of an employee project table, by A. Watt.<\/figcaption><\/figure>\n<p><span style=\"color: #000000;\">Next, let\u2019s look at some possi<\/span>ble anomalies <span>that might occur with this table during the following steps<\/span>.<\/p>\n<ol>\n<li><span>Action:<\/span> Add row {S85,35,P1,9}<\/li>\n<li>Problem: There are two tuples with conflicting budgets<\/li>\n<li><span>Action:<\/span> Delete tuple {S79, 27, P3, 1}<\/li>\n<li><span>Problem<\/span>: <span>Step #3<\/span> deletes the budget for project P3<\/li>\n<li><span>Action<\/span>: Update tuple {S75, 32, P1, 7} to {S75, 35, P1, 7}<\/li>\n<li>Problem: <span>Step #5 creates<\/span> two tuples with different values for project P1\u2019s budget<\/li>\n<li><span>Solution: Create a separate table, each, for Projects and Employees, as shown in Figure 10.7.<\/span><\/li>\n<\/ol>\n<figure id=\"attachment_78\" aria-describedby=\"caption-attachment-78\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-78 size-full\" alt=\"Ch-10-Project-to-Emp-ERD-300x114\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114.jpg\" height=\"114\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114-65x24.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-to-Emp-ERD-300x114-225x85.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-78\" class=\"wp-caption-text\">Figure 10.7. Solution: separate tables for Project and Employee, by A. Watt.<\/figcaption><\/figure>\n<h2><span style=\"color: #000000;\">How to Avoid Anomalies<\/span><\/h2>\n<p>The best approach to creating tables without anomalies is to ensure that the tables are normalized, and that\u2019s accomplished by understanding functional dependencies.\u00a0FD ensures that all attributes in a table belong to that table. In other words, it will eliminate redundancies and anomalies.<\/p>\n<h3>Example: separate Project and Employee tables<\/h3>\n<figure id=\"attachment_79\" aria-describedby=\"caption-attachment-79\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-79\" alt=\"Ch-10-Project-and-Emp-tables-300x89\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89.jpg\" height=\"119\" width=\"400\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89-65x19.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-10-Project-and-Emp-tables-300x89-225x66.jpg 225w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-79\" class=\"wp-caption-text\">Figure 10.8. Separate Project and Employee tables with data, by A. Watt.<\/figcaption><\/figure>\n<p><span>By keeping data separate using individual Project and Employee tables:<\/span><\/p>\n<ol>\n<li>No anomalies will be created if a budget is changed.<\/li>\n<li>No dummy values are needed for projects that have no employees assigned.<\/li>\n<li>If an employee\u2019s contribution is deleted, no important data is lost.<\/li>\n<li>No anomalies are created if an employee\u2019s contribution is added.<\/li>\n<\/ol>\n<div class=\"textbox textbox--key-takeaways\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Key Terms<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<p><strong>deletion anomaly<\/strong>:\u00a0occurs when you delete a record that may contain attributes that shouldn&#8217;t be deleted<\/p>\n<p><strong>functional dependency (FD)<\/strong>:\u00a0describes how individual attributes are related<\/p>\n<p><strong>insertion anomaly<\/strong>:\u00a0occurs when you are inserting inconsistent information into a table<\/p>\n<p><span><strong>join<\/strong>:\u00a0<span style=\"color: #000000;\">used when you need to obtain information based on two related tables<\/span><\/span><\/p>\n<p><strong>update anomaly<\/strong>: changing existing information incorrectly<\/p>\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<ol>\n<li>Normalize Figure 10.9.<br \/>\n<figure id=\"attachment_256\" aria-describedby=\"caption-attachment-256\" style=\"width: 600px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-256\" alt=\"Ch10-Exercises -Fig10-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977.jpg\" height=\"141\" width=\"600\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977.jpg 1222w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977-300x70.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977-1024x240.jpg 1024w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977-65x15.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977-225x52.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch10-Exercises-Fig10-1-e1409190793977-350x82.jpg 350w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><\/a><figcaption id=\"caption-attachment-256\" class=\"wp-caption-text\">Figure 10.9. Table for question 1, by A. Watt.<\/figcaption><\/figure>\n<\/li>\n<li>Create a logical ERD for an online movie rental service (no many to many relationships). Use the following description of operations on which your business rules must be based:The <span>online movie rental service<\/span>\u00a0classifies movie titles according to their type: comedy, western, classical, science fiction, cartoon, action, musical, and new release. Each type contains many possible titles, and most titles within a type are available in multiple copies. For example, note the following summary:TYPE TITLE<br \/>\nMusical My Fair Lady (Copy 1)<br \/>\n<span>My Fair Lady (Copy 2)<br \/>\n<\/span><span>Oklahoma (Copy 1)<br \/>\n<\/span><span>Oklahoma (Copy 2)<br \/>\n<\/span><span>Oklahoma (Copy 3)<br \/>\n<\/span><span>etc.\u00a0<\/span><\/li>\n<li>What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?<\/li>\n<\/ol>\n<p style=\"color: #1f1f1d;\"><strong>Also see<\/strong>\u00a0\u00a0<em>Appendix B: Sample ERD Exercises<\/em><\/p>\n<\/div>\n<\/div>\n<h2>Attribution<\/h2>\n<p><span style=\"color: #1f1f1d;\">This chapter of\u00a0<\/span><em style=\"color: #1f1f1d;\">Database Design<\/em><span style=\"color: #1f1f1d;\"> (including images, except as otherwise noted) is a derivative copy of\u00a0<a href=\"http:\/\/cnx.org\/contents\/e5ac0441-0e54-4895-9112-fb3a4ee9bce1@1\" target=\"_blank\" rel=\"noopener\">Relational Design Theory<\/a><\/span><span style=\"color: #1f1f1d;\">\u00a0by<\/span><span style=\"color: #1f1f1d;\">\u00a0Nguyen Kim Anh<\/span><span style=\"color: #1f1f1d;\">\u00a0licensed under\u00a0<\/span><a style=\"color: #870d0d;\" href=\"http:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons Attribution License 3.0 license<\/a><\/p>\n<p>The following material was written by Adrienne Watt:<\/p>\n<ol>\n<li>Example: employee project table and anomalies<\/li>\n<li>How to Avoid Anomalies<\/li>\n<li>Key Terms<\/li>\n<li>Exercises<\/li>\n<\/ol>\n","protected":false},"author":1,"menu_order":10,"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-80","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/80","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":27,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/80\/revisions"}],"predecessor-version":[{"id":1148,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/80\/revisions\/1148"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/80\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=80"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=80"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=80"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=80"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}