{"id":71,"date":"2013-12-12T00:09:55","date_gmt":"2013-12-12T00:09:55","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=71"},"modified":"2019-06-11T16:38:58","modified_gmt":"2019-06-11T16:38:58","slug":"chapter-9-integrity-rules-and-constraints","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/chapter-9-integrity-rules-and-constraints\/","title":{"raw":"Chapter 9 Integrity Rules and Constraints","rendered":"Chapter 9 Integrity Rules and Constraints"},"content":{"raw":"Constraints are a very important feature in a relational model. In fact, the relational model supports the well-defined theory of constraints on attributes or tables. Constraints are useful because they allow a designer to specify the semantics of data in the database. <em>Constraints<\/em> are the rules that force DBMSs to check that data satisfies the semantics.\r\n<h2>Domain Integrity<\/h2>\r\nDomain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-world semantics for data that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are not allowed and which format is suitable for an attribute. For example,<span style=\"color: #333333;\"> the Employee ID (EID) must<\/span> be unique or the employee Birthdate is in the range [Jan 1, 1950, Jan 1, 2000]. Such information is provided in logical statements called <em>integrity constraints<\/em>.\r\n\r\nThere are several kinds of integrity constraints, described below.\r\n<h3>Entity integrity<\/h3>\r\nTo ensure <em>entity integrity<\/em>, it is required that every table have a primary key. Neither the PK nor any part of it can contain null\u00a0values. This is because null\u00a0values for the primary key mean we cannot identify some rows. For example, in the EMPLOYEE\u00a0table, Phone cannot be a primary key since some people may not have a telephone.\r\n<h3>Referential integrity<\/h3>\r\n<em>Referential integrity<\/em> requires that a foreign key must have a matching primary key or it must be null.\u00a0This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables.\u00a0 It means the reference from a row in one table to another table must be valid.\r\n\r\nExamples of referential integrity constraint in the Customer\/Order database of the Company<span style=\"color: #ff0000;\"><\/span>:\r\n<ul>\r\n \t<li>Customer(<strong><span style=\"text-decoration: underline;\">CustID<\/span><\/strong>, CustName)<\/li>\r\n \t<li>Order(<strong><span style=\"text-decoration: underline;\">OrderID<\/span><\/strong>, CustID, OrderDate)<\/li>\r\n<\/ul>\r\nTo ensure that there are no orphan records, we need to enforce referential integrity.\u00a0An <em>orphan record<\/em> is one whose foreign key FK\u00a0value is not found in the corresponding entity \u2013 the entity where the\u00a0PK\u00a0is located. Recall that a typical join is between a PK and FK.\r\n\r\nThe referential integrity constraint states that the customer ID (CustID) in the Order table must match a valid CustID in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.\r\n\r\nHere is another example from a Course\/Class database:\r\n<ul>\r\n \t<li>Course(<strong><span style=\"text-decoration: underline;\">CrsCode<\/span><\/strong>, DeptCode, Description)<\/li>\r\n \t<li>Class(<strong><span style=\"text-decoration: underline;\">CrsCode, Section<\/span><\/strong>, ClassTime)<\/li>\r\n<\/ul>\r\nThe referential integrity constraint states that CrsCode in the Class table must match a valid CrsCode in the Course table. In this situation, it\u2019s not enough that the CrsCode and Section in the Class table make up the<span style=\"color: #333333;\"> PK<\/span>, we must also enforce referential integrity.\r\n\r\nWhen setting up referential integrity it is important that the<span style=\"color: #333333;\"> PK and FK <\/span>have the same data types and come from the same domain, otherwise the<span style=\"color: #ff0000;\">\u00a0<span style=\"color: #333333;\">relational database management system (RDBMS)<\/span><\/span> will not allow the join. RDBMS is a popular database system that is based on the relational model introduced by E. F. Codd of IBM's San Jose Research Laboratory. Relational database systems are easier to use and understand than other database systems.\r\n<h3>Referential integrity in Microsoft Access<\/h3>\r\nIn Microsoft (MS) Access, referential integrity is set up by joi<span>ning the PK in the<\/span> Customer table to the CustID in the Order table. See Figure 9.1 for a view of how this is done on the Edit Relationships screen in MS Access.\r\n\r\n[caption id=\"attachment_56\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212.jpg\" alt=\"A screenshot of the Edit Relationships commend in MS Access.\" width=\"300\" height=\"212\" class=\"wp-image-56 size-full\" \/><\/a> Figure 9.1. Referential access in MS Access, by A. Watt.[\/caption]\r\n<h3>Referential integrity using Transact-SQL (MS SQL Server)<\/h3>\r\nWhen using Transact-SQL, the referential integrity is set when creating the Order t<span style=\"color: #333333;\">able with the FK<\/span>. Listed below are the statements showing the FK in the Order table referencing the PK in the Customer table.\r\n<div class=\"textbox\">CREATE TABLE Customer\r\n( CustID INTEGER PRIMARY KEY,\r\nCustName CHAR(35) )<\/div>\r\n<div class=\"textbox\">CREATE TABLE Orders\r\n( OrderID INTEGER PRIMARY KEY,\r\nCustID INTEGER REFERENCES Customer(CustID),\r\nOrderDate DATETIME )<\/div>\r\n<h3>Foreign key rules<\/h3>\r\n<span>Additional foreign key rules may be added when setting referential integrity, such as what to do with the child rows (in the Orders table) when the record with the PK, part of\u00a0the parent (Customer), is deleted or changed (updated). For example, the Edit Relationships window in MS Access (see Figure 9.1) shows two additional options for FK\u00a0rules: Cascade Update and Cascade Delete. If these are not selected, the system will prevent the deletion or update of PK values in the parent table (Customer table) if a child record exists. The child record is any record with a matching PK.<\/span>\r\n\r\n<span>In some databases, an additional option exists when selecting the Delete option called Set to Null. In this is chosen, the PK row is deleted, but the FK in the child table is set to NULL.<\/span> Though this creates an orphan row, it is acceptable.\r\n<h2>Enterprise Constraints<\/h2>\r\nEnterprise constraints \u2013 sometimes referred to as semantic constraints\u00a0\u2013\u00a0are additional rules specified by users or database administrators<span> and can be based on multiple tables.<\/span>\r\n\r\nHere are some examples.\r\n<ul>\r\n \t<li>A\u00a0class can have a maximum of 30 students.<\/li>\r\n \t<li>A teacher can teach a maximum of four\u00a0classes per semester.<\/li>\r\n \t<li>An employee cannot take part in more than five\u00a0projects.<\/li>\r\n \t<li>The salary of an employee cannot exceed the salary of the employee\u2019s manager.<\/li>\r\n<\/ul>\r\n<h2>Business Rules<\/h2>\r\n<em>Business rules<\/em> are obtained from users when gathering requirements. The requirements-gathering process is very important, and its results should be verified by the user before the database design is built. If the business rules are incorrect, the design will be incorrect, and ultimately the application built will not function as expected by the users.\r\n\r\nSome examples of business rules are:\r\n<ul>\r\n \t<li>A teacher can teach many students.<\/li>\r\n \t<li>A class can have a maximum of 35 students.<\/li>\r\n \t<li>A course can be taught many times, but by only one instructor.<\/li>\r\n \t<li>Not all teachers teach classes.<\/li>\r\n<\/ul>\r\n<h3>Cardinality and connectivity<\/h3>\r\nBusiness rules are used to determine cardinality <span>and connectivity<\/span>.<em>\u00a0Cardinality<\/em>\u00a0<span>describes the relationship between two data tables<\/span> by expressing the minimum and maximum number of entity occurrences associated with one occurrence of a related entity. <span>In Figure 9.2, you can see that cardinality is represented by the innermost markings on the relationship symbol. In this figure, the cardinality is 0 (zero) on the right and 1 (one) on the left.<\/span>\r\n\r\n[caption id=\"attachment_57\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202.jpg\" alt=\"Connectivity-and-Cardinality-300x202\" width=\"300\" height=\"202\" class=\"wp-image-57 size-full\" \/><\/a> Figure 9.2. Position of connectivity and cardinality on a relationship symbol, by A. Watt.[\/caption]\r\n\r\nThe\u00a0outermost\u00a0symbol <span>of the relationship symbol<\/span>, on the other hand, represents the connectivity <span style=\"color: #333333;\">between the two tables.<\/span><span><span><em>\u00a0<\/em><span><em>Connectivity<\/em> is the relationship between two tables, e.g., one to one or one to many<\/span>. The only time it is zero is when the FK can be null. When it comes to participation, there are <span>three\u00a0options<\/span>\u00a0to the relationship\u00a0<span>between these entities<\/span>:\u00a0either 0 (zero), 1 (one) or many.\u00a0<\/span>In Figure 9.2, for example, the connectivity is 1 (one) on the outer, left-hand side of this line and many on the outer, right-hand side.<\/span>\r\n\r\n<span>Figure 9.3. shows the symbol that represents a one to many relationship.<\/span>\r\n\r\n[caption id=\"attachment_58\" align=\"aligncenter\" width=\"171\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1.jpg\"><img class=\"wp-image-58 size-full\" alt=\"Ch-9-oneToMany-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1.jpg\" height=\"34\" width=\"171\" \/><\/a> Figure 9.3.[\/caption]\r\n\r\nIn Figure 9.4, both inner (representing cardinality) and outer (representing connectivity) markers are shown. The left side of this symbol\u00a0 is\u00a0read as minimum 1\u00a0and maximum 1. On the right side, it is read as: minimum 1\u00a0and maximum many.\r\n\r\n[caption id=\"attachment_59\" align=\"aligncenter\" width=\"169\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2.jpg\"><img class=\"wp-image-59 size-full\" alt=\"Ch-9-oneToMany-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2.jpg\" height=\"37\" width=\"169\" \/><\/a> Figure 9.4.[\/caption]\r\n<h2>Relationship Types<\/h2>\r\nThe line that connects two tables, in an ERD, indicates the <em>relationship type <\/em>between the tables<em>:<\/em> either identifying or non-identifying. An <em>identifying relationship<\/em> will have a solid line (where the PK contains the FK). A <em>non-identifying relationshi<span style=\"color: #333333;\">p<\/span><\/em><span style=\"color: #333333;\"> is indicated by a broken line <\/span>and does not contain the FK in the PK. See the section in Chapter 8 that discusses weak and strong relationships for more explanation.\r\n\r\n[caption id=\"attachment_70\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298.jpg\"><img class=\"wp-image-70 size-full\" alt=\"Figure 9.5. Identifying and non-identifying relationship.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298.jpg\" height=\"298\" width=\"300\" \/><\/a> Figure 9.5. Identifying and non-identifying relationship, by A. Watt.[\/caption]\r\n<h3><span>Optional relationships<\/span><\/h3>\r\n<span style=\"color: #ff0000;\"><\/span><span style=\"color: #333333;\">In an <em>optional relationship<\/em>,\u00a0the FK can be null or the parent table does not need to have a corresponding child table\u00a0<\/span>occurrence.<span style=\"color: #333333;\"> The symbol, shown in Figure 9.6, illustrates one type with a<span style=\"background-color: #ffffff;\"> zero and three prongs (indicating many) which is interpreted as zero OR many.<\/span><\/span><span style=\"background-color: #ffffff; color: #ff0000;\">\r\n<\/span>\r\n\r\n[caption id=\"attachment_60\" align=\"aligncenter\" width=\"67\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\"><img class=\"wp-image-60\" alt=\"Ch-9-Zero-or-Many-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\" height=\"45\" width=\"67\" \/><\/a> Figure 9.6.[\/caption]\r\n\r\n<span style=\"color: #ff0000;\"><span style=\"color: #333333;\">For example, if you look at the Order table on\u00a0 the right-hand side of Figure 9.7, you'll notice that a customer doesn't need to place an order to be a customer. In other words, the <strong>many side<\/strong> is optional.<\/span>\r\n<\/span>\r\n\r\n[caption id=\"attachment_61\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189.jpg\" alt=\"Cardinality-Optional-0-or-more-300x189\" width=\"300\" height=\"189\" class=\"wp-image-61 size-full\" \/><\/a> Figure 9.7. Example usage of a zero to many optional relationship symbol, by A. Watt.[\/caption]\r\n\r\n<span style=\"color: #333333;\">The relationship symbol in Figure 9.7 can also be read as follows:<\/span>\r\n<ul>\r\n \t<li>Left side:\u00a0The order entity must contain a minimum of one related entity in the Customer table and a maximum of one\u00a0related entity.<\/li>\r\n \t<li>Right side: A customer can<span>\u00a0place<\/span>\u00a0a minimum of zero orders or a maximum of many orders.<\/li>\r\n<\/ul>\r\n<span style=\"color: #333333;\">Figure 9.8\u00a0 shows another type of optional relationship symbol with a zero and one, meaning zero OR one.\u00a0<\/span> The <strong>one\u00a0side<\/strong> is optional.\r\n\r\n[caption id=\"attachment_62\" align=\"aligncenter\" width=\"66\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Optional-Arrow-2.jpg\"><img class=\"wp-image-62\" alt=\"Ch-9-Optional-Arrow-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Optional-Arrow-2.jpg\" height=\"47\" width=\"66\" \/><\/a> Figure 9.8.[\/caption]\r\n\r\n<span>Figure 9.9 gives an example of how a zero to one symbol might be used.<\/span>\r\n\r\n[caption id=\"attachment_63\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188.jpg\" alt=\"Cardinality-Optional-0-or-1-300x188\" width=\"300\" height=\"188\" class=\"wp-image-63 size-full\" \/><\/a> Figure 9.9. Example usage of a zero to one optional relationship symbol, by A. Watt.[\/caption]\r\n\r\n&nbsp;\r\n<h3>Mandatory<span> relationships<\/span><\/h3>\r\n<span>In a <em>mandatory relationship<\/em>, o<\/span>ne entity occurrence requires a corresponding entity occurrence. The symbol for this relationship shows <em>one and only one <\/em>as shown in Figure 9.10.\u00a0The one side is mandatory.\r\n\r\n[caption id=\"attachment_64\" align=\"aligncenter\" width=\"40\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToOne-1.jpg\"><img class=\"wp-image-64\" alt=\"Ch-9-oneToOne-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToOne-1.jpg\" height=\"46\" width=\"40\" \/><\/a> Figure 9.10[\/caption]\r\n\r\n<span>See Figure 9.11 for an example of how the one and only one mandatory symbol is used.<\/span>\r\n\r\n[caption id=\"attachment_65\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188.jpg\" alt=\"Ch-9-Mandatory-Cardinality-one-to-one-300x188\" width=\"300\" height=\"188\" class=\"wp-image-65 size-full\" \/><\/a> Figure 9.11. Example of a one and only one mandatory relationship symbol, by A. Watt.[\/caption]\r\n\r\nFigure 9.12 illustrates what a one to many relationship symbol looks like where the <strong>many side<\/strong> is mandatory.\r\n\r\n[caption id=\"attachment_66\" align=\"aligncenter\" width=\"40\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-3.jpg\"><img class=\"wp-image-66\" alt=\"Ch-9-oneToMany-3\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-3.jpg\" height=\"45\" width=\"40\" \/><\/a> Figure 9.12.[\/caption]\r\n\r\n<span>Refer to Figure 9.13 for an example of how the one to many symbol may be used.<\/span>\r\n\r\n[caption id=\"attachment_67\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188.jpg\" alt=\"Ch-9-Mandatory-Cardinality-one-to-Many-300x188\" width=\"300\" height=\"188\" class=\"wp-image-67 size-full\" \/><\/a> Figure 9.13. Example of a one to many mandatory relationship symbol, by A. Watt.[\/caption]\r\n\r\nSo far we have seen that the innermost side <span>of a relationship symbol<\/span>\u00a0(on the left-side of the symbol in Figure 9.14) can have<a><\/a> a 0 (zero) cardinality and a connectivity of many (shown on the right-side of the symbol in Figure 9.14), o<span>r one (not shown).<\/span>\r\n\r\n[caption id=\"attachment_60\" align=\"aligncenter\" width=\"65\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\"><img class=\"wp-image-60\" alt=\"Ch-9-Zero-or-Many-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\" height=\"44\" width=\"65\" \/><\/a> Figure 9.14[\/caption]\r\n\r\n<span>However, \u00a0it cannot have a connectivity of 0 (zero), as displayed in Figure 9.15. The connectivity can only be 1.<\/span>\r\n\r\n[caption id=\"attachment_68\" align=\"aligncenter\" width=\"60\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-2.jpg\"><img class=\"wp-image-68\" alt=\"Ch-9-Zero-or-Many-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-2.jpg\" height=\"43\" width=\"60\" \/><\/a> Figure 9.15.[\/caption]\r\n\r\nThe connectivity symbols show maximums. So if you\u00a0think about it logically, if the connectivity symbol on the left side shows 0 (zero), then there would be no connection between the tables.\r\n\r\nThe way to read <span>a relationship symbol, such as the one in Figure 9.16,<\/span> is as follows.\r\n<ul>\r\n \t<li>The CustID in the Order table must <span>also<\/span> be found in the Customer table a minimum of 0 and a maximum of 1\u00a0times.<\/li>\r\n \t<li>The 0 means that the CustID in the Order table may be null.<\/li>\r\n \t<li>The left-most 1 (right before the 0 representing connectivity) says that if there is a CustID in the Order table, it can only be in the Customer table once.<\/li>\r\n \t<li>When you see the 0 symbol for cardinality, you can assume two things: T\r\n<ol>\r\n \t<li>the FK in the Order table allows nulls, and<\/li>\r\n \t<li>the FK is not part of the PK since PKs must not contain null values.<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ul>\r\n[caption id=\"attachment_69\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168.jpg\" alt=\"Ch-9-Cust-to-Order-ERD-300x168\" width=\"300\" height=\"168\" class=\"wp-image-69 size-full\" \/><\/a> Figure 9.16. The relationship between a Customer table and an Order table, by A. Watt.[\/caption]\r\n\r\n&nbsp;\r\n<h2><strong>\u00a0<\/strong><\/h2>\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<strong>business rules<\/strong>: obtained from users when gathering requirements and are used to determine cardinality\r\n\r\n<strong>cardinality<\/strong>: expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity\r\n\r\n<strong>connectivity<\/strong>:\u00a0the relationship between two tables, e.g., one to one or one to many\r\n\r\n<strong>constraints<\/strong>: the rules that force DBMSs to check that data satisfies the semantics\r\n\r\n<strong>entity integrity<\/strong>: <span style=\"color: #000000;\">requires that every table have a primary key;\u00a0n<\/span>either the primary key, nor any part of it, can contain null\u00a0values\r\n\r\n<strong>identifying relationship<\/strong>: where the primary key contains the foreign key; indicated in an ERD by a solid line\r\n\r\n<strong>integrity constraints<\/strong>: logical statements that state what data values are or are not allowed and which format is suitable for an attribute\r\n\r\n<strong><span style=\"color: #000000;\">mandatory relationship<\/span><\/strong><span style=\"color: #000000;\">:<\/span>one entity occurrence requires a corresponding entity occurrence.\r\n\r\n<strong>non-identifying relationship<\/strong>: does not contain the foreign key in the primary key; indicated in an ERD by a dotted line\r\n\r\n<span style=\"color: #000000;\"><strong>optional relationship<\/strong>:<\/span>\u00a0t<span>he FK can be null or the parent table does not need to have a corresponding child table\u00a0<\/span>occurrence\r\n\r\n<strong>orphan record<\/strong>: a record whose foreign key value is not found in the corresponding entity -\u00a0 the entity where the primary key is located\r\n\r\n<strong>referential integrity<\/strong>: requires that a foreign key must have a matching primary key or it must be null\r\n\r\n<strong>relational database management system (RDBMS)<\/strong>: a popular database system based on the relational model introduced by E. F. Codd of IBM's San Jose Research Laboratory\r\n\r\n<strong>relationship type<\/strong>: the type of relationship between two<span>\u00a0tables in an ERD (either identifying or non-identifying); this relationship is indicated by a line drawn between the two tables.\u00a0<\/span>\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\r\n<p style=\"color: #1f1f1d;\"><em>Read the following description and then answer questions 1-5 at the end.<\/em><\/p>\r\n<p style=\"color: #1f1f1d;\">The\u00a0swim club database in Figure 9.17 has been designed to hold information about students who are enrolled in swim classes. The following information is stored: students, enrollment, swim classes, pools where classes are held, instructors for the classes, and various levels of swim classes. Use Figure 9.17 to answer questions 1 to 5.<\/p>\r\n\r\n<div class=\"wp-nocaption alignnone size-full wp-image-284\" style=\"color: #1f1f1d;\">\r\n\r\n[caption id=\"attachment_284\" align=\"aligncenter\" width=\"600\"]<a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase.jpg\"><img class=\"wp-image-284\" alt=\"SwimClubDatabase\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase.jpg\" height=\"295\" width=\"600\" \/><\/a> Figure 9.17. ERD for questions 1-5. (Diagram by A. Watt.)[\/caption]\r\n<\/div>\r\n<\/div>\r\n<p style=\"color: #1f1f1d;\">The primary keys are identified below. The following data types are defined in the SQL Server.<\/p>\r\n<p style=\"color: #1f1f1d;\"><strong>tblLevels\u00a0<\/strong>\r\nLevel \u2013 Identity PK\r\nClassName \u2013 text 20 \u2013 nulls are not allowed<\/p>\r\n<p style=\"color: #1f1f1d;\"><strong>tblPool<\/strong>\r\nPool \u2013 Identity PK\r\nPoolName \u2013 text 20 \u2013 nulls are not allowed\r\nLocation \u2013 text 30<\/p>\r\n<p style=\"color: #1f1f1d;\"><strong>tblStaff<\/strong>\r\nStaffID \u2013 Identity PK\r\nFirstName \u2013 text 20\r\nMiddleInitial \u2013 text 3\r\nLastName \u2013 text 30\r\nSuffix \u2013 text 3\r\nSalaried \u2013 Bit\r\nPayAmount \u2013 money<\/p>\r\n<strong>tblClasses<\/strong>\r\nLessonIndex \u2013 Identity PK\r\nLevel \u2013 Integer FK\r\nSectionID - Integer\r\nSemester \u2013 TinyInt\r\nDays \u2013 text 20\r\nTime \u2013 datetime (formatted for time)\r\nPool \u2013 Integer FK\r\nInstructor \u2013 Integer FK\r\nLimit \u2013 TinyInt\r\nEnrolled \u2013 TinyInt\r\nPrice \u2013 money\r\n<p style=\"color: #1f1f1d;\"><strong>tblEnrollment<\/strong>\r\nLessonIndex \u2013 Integer FK\r\nSID \u2013 Integer FK (LessonIndex and SID) Primary Key\r\nStatus \u2013 text 30\r\nCharged \u2013 bit\r\nAmountPaid \u2013 money\r\nDateEnrolled \u2013 datetime<\/p>\r\n<p style=\"color: #1f1f1d;\"><strong>tblStudents<\/strong>\r\nSID \u2013 Identity PK\r\nFirstName \u2013 text 20\r\nMiddleInitial \u2013 text 3\r\nLastName \u2013 text 30\r\nSuffix \u2013 text 3\r\nBirthday \u2013 datetime\r\nLocalStreet \u2013 text 30\r\nLocalCity \u2013 text 20\r\nLocalPostalCode \u2013 text 6\r\nLocalPhone \u2013 text 10<\/p>\r\n<p style=\"color: #1f1f1d;\">Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database.<\/p>\r\n\r\n<ol>\r\n \t<li>Explain the relationship rules for each relationship (e.g.,\u00a0tblEnrollment and tblStudents: A student can enroll in many classes).<\/li>\r\n \t<li>Identify cardinality for each relationship,\u00a0assuming the following rules:\r\n<ul>\r\n \t<li>A pool may or may not ever have a class.<\/li>\r\n \t<li>The levels table must always be associated with at least one class.<\/li>\r\n \t<li>The staff table may not have ever taught a class.<\/li>\r\n \t<li>All students must be enrolled in at least one class.<\/li>\r\n \t<li>The class must have students enrolled in it.<\/li>\r\n \t<li>The class must have a valid pool.<\/li>\r\n \t<li>The class may not have an instructor assigned.<\/li>\r\n \t<li>The class must always be associated with an existing level.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li>Which tables are weak and which tables are strong (covered in an earlier chapter)?<\/li>\r\n \t<li>Which of the tables are non-identifying and which are identifying?<\/li>\r\n<\/ol>\r\n<\/div>\r\n<h2>Image Attributions<\/h2>\r\nFigures 9.3, 9.4, 9.6, 9.8, 9.10, 9.12, 9.14 and 9.15 by A. Watt.","rendered":"<p>Constraints are a very important feature in a relational model. In fact, the relational model supports the well-defined theory of constraints on attributes or tables. Constraints are useful because they allow a designer to specify the semantics of data in the database. <em>Constraints<\/em> are the rules that force DBMSs to check that data satisfies the semantics.<\/p>\n<h2>Domain Integrity<\/h2>\n<p>Domain restricts the values of attributes in the relation and is a constraint of the relational model. However, there are real-world semantics for data that cannot be specified if used only with domain constraints. We need more specific ways to state what data values are or are not allowed and which format is suitable for an attribute. For example,<span style=\"color: #333333;\"> the Employee ID (EID) must<\/span> be unique or the employee Birthdate is in the range [Jan 1, 1950, Jan 1, 2000]. Such information is provided in logical statements called <em>integrity constraints<\/em>.<\/p>\n<p>There are several kinds of integrity constraints, described below.<\/p>\n<h3>Entity integrity<\/h3>\n<p>To ensure <em>entity integrity<\/em>, it is required that every table have a primary key. Neither the PK nor any part of it can contain null\u00a0values. This is because null\u00a0values for the primary key mean we cannot identify some rows. For example, in the EMPLOYEE\u00a0table, Phone cannot be a primary key since some people may not have a telephone.<\/p>\n<h3>Referential integrity<\/h3>\n<p><em>Referential integrity<\/em> requires that a foreign key must have a matching primary key or it must be null.\u00a0This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables.\u00a0 It means the reference from a row in one table to another table must be valid.<\/p>\n<p>Examples of referential integrity constraint in the Customer\/Order database of the Company<span style=\"color: #ff0000;\"><\/span>:<\/p>\n<ul>\n<li>Customer(<strong><span style=\"text-decoration: underline;\">CustID<\/span><\/strong>, CustName)<\/li>\n<li>Order(<strong><span style=\"text-decoration: underline;\">OrderID<\/span><\/strong>, CustID, OrderDate)<\/li>\n<\/ul>\n<p>To ensure that there are no orphan records, we need to enforce referential integrity.\u00a0An <em>orphan record<\/em> is one whose foreign key FK\u00a0value is not found in the corresponding entity \u2013 the entity where the\u00a0PK\u00a0is located. Recall that a typical join is between a PK and FK.<\/p>\n<p>The referential integrity constraint states that the customer ID (CustID) in the Order table must match a valid CustID in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.<\/p>\n<p>Here is another example from a Course\/Class database:<\/p>\n<ul>\n<li>Course(<strong><span style=\"text-decoration: underline;\">CrsCode<\/span><\/strong>, DeptCode, Description)<\/li>\n<li>Class(<strong><span style=\"text-decoration: underline;\">CrsCode, Section<\/span><\/strong>, ClassTime)<\/li>\n<\/ul>\n<p>The referential integrity constraint states that CrsCode in the Class table must match a valid CrsCode in the Course table. In this situation, it\u2019s not enough that the CrsCode and Section in the Class table make up the<span style=\"color: #333333;\"> PK<\/span>, we must also enforce referential integrity.<\/p>\n<p>When setting up referential integrity it is important that the<span style=\"color: #333333;\"> PK and FK <\/span>have the same data types and come from the same domain, otherwise the<span style=\"color: #ff0000;\">\u00a0<span style=\"color: #333333;\">relational database management system (RDBMS)<\/span><\/span> will not allow the join. RDBMS is a popular database system that is based on the relational model introduced by E. F. Codd of IBM&#8217;s San Jose Research Laboratory. Relational database systems are easier to use and understand than other database systems.<\/p>\n<h3>Referential integrity in Microsoft Access<\/h3>\n<p>In Microsoft (MS) Access, referential integrity is set up by joi<span>ning the PK in the<\/span> Customer table to the CustID in the Order table. See Figure 9.1 for a view of how this is done on the Edit Relationships screen in MS Access.<\/p>\n<figure id=\"attachment_56\" aria-describedby=\"caption-attachment-56\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212.jpg\" alt=\"A screenshot of the Edit Relationships commend in MS Access.\" width=\"300\" height=\"212\" class=\"wp-image-56 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212-65x45.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Access-Referential-Integrity-Window-300x212-225x159.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-56\" class=\"wp-caption-text\">Figure 9.1. Referential access in MS Access, by A. Watt.<\/figcaption><\/figure>\n<h3>Referential integrity using Transact-SQL (MS SQL Server)<\/h3>\n<p>When using Transact-SQL, the referential integrity is set when creating the Order t<span style=\"color: #333333;\">able with the FK<\/span>. Listed below are the statements showing the FK in the Order table referencing the PK in the Customer table.<\/p>\n<div class=\"textbox\">CREATE TABLE Customer<br \/>\n( CustID INTEGER PRIMARY KEY,<br \/>\nCustName CHAR(35) )<\/div>\n<div class=\"textbox\">CREATE TABLE Orders<br \/>\n( OrderID INTEGER PRIMARY KEY,<br \/>\nCustID INTEGER REFERENCES Customer(CustID),<br \/>\nOrderDate DATETIME )<\/div>\n<h3>Foreign key rules<\/h3>\n<p><span>Additional foreign key rules may be added when setting referential integrity, such as what to do with the child rows (in the Orders table) when the record with the PK, part of\u00a0the parent (Customer), is deleted or changed (updated). For example, the Edit Relationships window in MS Access (see Figure 9.1) shows two additional options for FK\u00a0rules: Cascade Update and Cascade Delete. If these are not selected, the system will prevent the deletion or update of PK values in the parent table (Customer table) if a child record exists. The child record is any record with a matching PK.<\/span><\/p>\n<p><span>In some databases, an additional option exists when selecting the Delete option called Set to Null. In this is chosen, the PK row is deleted, but the FK in the child table is set to NULL.<\/span> Though this creates an orphan row, it is acceptable.<\/p>\n<h2>Enterprise Constraints<\/h2>\n<p>Enterprise constraints \u2013 sometimes referred to as semantic constraints\u00a0\u2013\u00a0are additional rules specified by users or database administrators<span> and can be based on multiple tables.<\/span><\/p>\n<p>Here are some examples.<\/p>\n<ul>\n<li>A\u00a0class can have a maximum of 30 students.<\/li>\n<li>A teacher can teach a maximum of four\u00a0classes per semester.<\/li>\n<li>An employee cannot take part in more than five\u00a0projects.<\/li>\n<li>The salary of an employee cannot exceed the salary of the employee\u2019s manager.<\/li>\n<\/ul>\n<h2>Business Rules<\/h2>\n<p><em>Business rules<\/em> are obtained from users when gathering requirements. The requirements-gathering process is very important, and its results should be verified by the user before the database design is built. If the business rules are incorrect, the design will be incorrect, and ultimately the application built will not function as expected by the users.<\/p>\n<p>Some examples of business rules are:<\/p>\n<ul>\n<li>A teacher can teach many students.<\/li>\n<li>A class can have a maximum of 35 students.<\/li>\n<li>A course can be taught many times, but by only one instructor.<\/li>\n<li>Not all teachers teach classes.<\/li>\n<\/ul>\n<h3>Cardinality and connectivity<\/h3>\n<p>Business rules are used to determine cardinality <span>and connectivity<\/span>.<em>\u00a0Cardinality<\/em>\u00a0<span>describes the relationship between two data tables<\/span> by expressing the minimum and maximum number of entity occurrences associated with one occurrence of a related entity. <span>In Figure 9.2, you can see that cardinality is represented by the innermost markings on the relationship symbol. In this figure, the cardinality is 0 (zero) on the right and 1 (one) on the left.<\/span><\/p>\n<figure id=\"attachment_57\" aria-describedby=\"caption-attachment-57\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202.jpg\" alt=\"Connectivity-and-Cardinality-300x202\" width=\"300\" height=\"202\" class=\"wp-image-57 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202-65x43.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Connectivity-and-Cardinality-300x202-225x151.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-57\" class=\"wp-caption-text\">Figure 9.2. Position of connectivity and cardinality on a relationship symbol, by A. Watt.<\/figcaption><\/figure>\n<p>The\u00a0outermost\u00a0symbol <span>of the relationship symbol<\/span>, on the other hand, represents the connectivity <span style=\"color: #333333;\">between the two tables.<\/span><span><span><em>\u00a0<\/em><span><em>Connectivity<\/em> is the relationship between two tables, e.g., one to one or one to many<\/span>. The only time it is zero is when the FK can be null. When it comes to participation, there are <span>three\u00a0options<\/span>\u00a0to the relationship\u00a0<span>between these entities<\/span>:\u00a0either 0 (zero), 1 (one) or many.\u00a0<\/span>In Figure 9.2, for example, the connectivity is 1 (one) on the outer, left-hand side of this line and many on the outer, right-hand side.<\/span><\/p>\n<p><span>Figure 9.3. shows the symbol that represents a one to many relationship.<\/span><\/p>\n<figure id=\"attachment_58\" aria-describedby=\"caption-attachment-58\" style=\"width: 171px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-58 size-full\" alt=\"Ch-9-oneToMany-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1.jpg\" height=\"34\" width=\"171\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1.jpg 171w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-1-65x12.jpg 65w\" sizes=\"auto, (max-width: 171px) 100vw, 171px\" \/><\/a><figcaption id=\"caption-attachment-58\" class=\"wp-caption-text\">Figure 9.3.<\/figcaption><\/figure>\n<p>In Figure 9.4, both inner (representing cardinality) and outer (representing connectivity) markers are shown. The left side of this symbol\u00a0 is\u00a0read as minimum 1\u00a0and maximum 1. On the right side, it is read as: minimum 1\u00a0and maximum many.<\/p>\n<figure id=\"attachment_59\" aria-describedby=\"caption-attachment-59\" style=\"width: 169px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-59 size-full\" alt=\"Ch-9-oneToMany-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2.jpg\" height=\"37\" width=\"169\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2.jpg 169w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-2-65x14.jpg 65w\" sizes=\"auto, (max-width: 169px) 100vw, 169px\" \/><\/a><figcaption id=\"caption-attachment-59\" class=\"wp-caption-text\">Figure 9.4.<\/figcaption><\/figure>\n<h2>Relationship Types<\/h2>\n<p>The line that connects two tables, in an ERD, indicates the <em>relationship type <\/em>between the tables<em>:<\/em> either identifying or non-identifying. An <em>identifying relationship<\/em> will have a solid line (where the PK contains the FK). A <em>non-identifying relationshi<span style=\"color: #333333;\">p<\/span><\/em><span style=\"color: #333333;\"> is indicated by a broken line <\/span>and does not contain the FK in the PK. See the section in Chapter 8 that discusses weak and strong relationships for more explanation.<\/p>\n<figure id=\"attachment_70\" aria-describedby=\"caption-attachment-70\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-70 size-full\" alt=\"Figure 9.5. Identifying and non-identifying relationship.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298.jpg\" height=\"298\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298-150x150.jpg 150w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298-65x64.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Identifying-and-Non-Identifying-relationship-300x298-225x223.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-70\" class=\"wp-caption-text\">Figure 9.5. Identifying and non-identifying relationship, by A. Watt.<\/figcaption><\/figure>\n<h3><span>Optional relationships<\/span><\/h3>\n<p><span style=\"color: #ff0000;\"><\/span><span style=\"color: #333333;\">In an <em>optional relationship<\/em>,\u00a0the FK can be null or the parent table does not need to have a corresponding child table\u00a0<\/span>occurrence.<span style=\"color: #333333;\"> The symbol, shown in Figure 9.6, illustrates one type with a<span style=\"background-color: #ffffff;\"> zero and three prongs (indicating many) which is interpreted as zero OR many.<\/span><\/span><span style=\"background-color: #ffffff; color: #ff0000;\"><br \/>\n<\/span><\/p>\n<figure id=\"attachment_60\" aria-describedby=\"caption-attachment-60\" style=\"width: 67px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-60\" alt=\"Ch-9-Zero-or-Many-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\" height=\"45\" width=\"67\" \/><\/a><figcaption id=\"caption-attachment-60\" class=\"wp-caption-text\">Figure 9.6.<\/figcaption><\/figure>\n<p><span style=\"color: #ff0000;\"><span style=\"color: #333333;\">For example, if you look at the Order table on\u00a0 the right-hand side of Figure 9.7, you&#8217;ll notice that a customer doesn&#8217;t need to place an order to be a customer. In other words, the <strong>many side<\/strong> is optional.<\/span><br \/>\n<\/span><\/p>\n<figure id=\"attachment_61\" aria-describedby=\"caption-attachment-61\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189.jpg\" alt=\"Cardinality-Optional-0-or-more-300x189\" width=\"300\" height=\"189\" class=\"wp-image-61 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189-65x40.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-more-300x189-225x141.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-61\" class=\"wp-caption-text\">Figure 9.7. Example usage of a zero to many optional relationship symbol, by A. Watt.<\/figcaption><\/figure>\n<p><span style=\"color: #333333;\">The relationship symbol in Figure 9.7 can also be read as follows:<\/span><\/p>\n<ul>\n<li>Left side:\u00a0The order entity must contain a minimum of one related entity in the Customer table and a maximum of one\u00a0related entity.<\/li>\n<li>Right side: A customer can<span>\u00a0place<\/span>\u00a0a minimum of zero orders or a maximum of many orders.<\/li>\n<\/ul>\n<p><span style=\"color: #333333;\">Figure 9.8\u00a0 shows another type of optional relationship symbol with a zero and one, meaning zero OR one.\u00a0<\/span> The <strong>one\u00a0side<\/strong> is optional.<\/p>\n<figure id=\"attachment_62\" aria-describedby=\"caption-attachment-62\" style=\"width: 66px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Optional-Arrow-2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-62\" alt=\"Ch-9-Optional-Arrow-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Optional-Arrow-2.jpg\" height=\"47\" width=\"66\" \/><\/a><figcaption id=\"caption-attachment-62\" class=\"wp-caption-text\">Figure 9.8.<\/figcaption><\/figure>\n<p><span>Figure 9.9 gives an example of how a zero to one symbol might be used.<\/span><\/p>\n<figure id=\"attachment_63\" aria-describedby=\"caption-attachment-63\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188.jpg\" alt=\"Cardinality-Optional-0-or-1-300x188\" width=\"300\" height=\"188\" class=\"wp-image-63 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188-65x40.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Cardinality-Optional-0-or-1-300x188-225x141.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-63\" class=\"wp-caption-text\">Figure 9.9. Example usage of a zero to one optional relationship symbol, by A. Watt.<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<h3>Mandatory<span> relationships<\/span><\/h3>\n<p><span>In a <em>mandatory relationship<\/em>, o<\/span>ne entity occurrence requires a corresponding entity occurrence. The symbol for this relationship shows <em>one and only one <\/em>as shown in Figure 9.10.\u00a0The one side is mandatory.<\/p>\n<figure id=\"attachment_64\" aria-describedby=\"caption-attachment-64\" style=\"width: 40px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToOne-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-64\" alt=\"Ch-9-oneToOne-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToOne-1.jpg\" height=\"46\" width=\"40\" \/><\/a><figcaption id=\"caption-attachment-64\" class=\"wp-caption-text\">Figure 9.10<\/figcaption><\/figure>\n<p><span>See Figure 9.11 for an example of how the one and only one mandatory symbol is used.<\/span><\/p>\n<figure id=\"attachment_65\" aria-describedby=\"caption-attachment-65\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188.jpg\" alt=\"Ch-9-Mandatory-Cardinality-one-to-one-300x188\" width=\"300\" height=\"188\" class=\"wp-image-65 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188-65x40.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-one-300x188-225x141.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-65\" class=\"wp-caption-text\">Figure 9.11. Example of a one and only one mandatory relationship symbol, by A. Watt.<\/figcaption><\/figure>\n<p>Figure 9.12 illustrates what a one to many relationship symbol looks like where the <strong>many side<\/strong> is mandatory.<\/p>\n<figure id=\"attachment_66\" aria-describedby=\"caption-attachment-66\" style=\"width: 40px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-3.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-66\" alt=\"Ch-9-oneToMany-3\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-oneToMany-3.jpg\" height=\"45\" width=\"40\" \/><\/a><figcaption id=\"caption-attachment-66\" class=\"wp-caption-text\">Figure 9.12.<\/figcaption><\/figure>\n<p><span>Refer to Figure 9.13 for an example of how the one to many symbol may be used.<\/span><\/p>\n<figure id=\"attachment_67\" aria-describedby=\"caption-attachment-67\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188.jpg\" alt=\"Ch-9-Mandatory-Cardinality-one-to-Many-300x188\" width=\"300\" height=\"188\" class=\"wp-image-67 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188-65x40.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Mandatory-Cardinality-one-to-Many-300x188-225x141.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-67\" class=\"wp-caption-text\">Figure 9.13. Example of a one to many mandatory relationship symbol, by A. Watt.<\/figcaption><\/figure>\n<p>So far we have seen that the innermost side <span>of a relationship symbol<\/span>\u00a0(on the left-side of the symbol in Figure 9.14) can have<a><\/a> a 0 (zero) cardinality and a connectivity of many (shown on the right-side of the symbol in Figure 9.14), o<span>r one (not shown).<\/span><\/p>\n<figure id=\"attachment_60\" aria-describedby=\"caption-attachment-60\" style=\"width: 65px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-60\" alt=\"Ch-9-Zero-or-Many-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-1.jpg\" height=\"44\" width=\"65\" \/><\/a><figcaption id=\"caption-attachment-60\" class=\"wp-caption-text\">Figure 9.14<\/figcaption><\/figure>\n<p><span>However, \u00a0it cannot have a connectivity of 0 (zero), as displayed in Figure 9.15. The connectivity can only be 1.<\/span><\/p>\n<figure id=\"attachment_68\" aria-describedby=\"caption-attachment-68\" style=\"width: 60px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-68\" alt=\"Ch-9-Zero-or-Many-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Zero-or-Many-2.jpg\" height=\"43\" width=\"60\" \/><\/a><figcaption id=\"caption-attachment-68\" class=\"wp-caption-text\">Figure 9.15.<\/figcaption><\/figure>\n<p>The connectivity symbols show maximums. So if you\u00a0think about it logically, if the connectivity symbol on the left side shows 0 (zero), then there would be no connection between the tables.<\/p>\n<p>The way to read <span>a relationship symbol, such as the one in Figure 9.16,<\/span> is as follows.<\/p>\n<ul>\n<li>The CustID in the Order table must <span>also<\/span> be found in the Customer table a minimum of 0 and a maximum of 1\u00a0times.<\/li>\n<li>The 0 means that the CustID in the Order table may be null.<\/li>\n<li>The left-most 1 (right before the 0 representing connectivity) says that if there is a CustID in the Order table, it can only be in the Customer table once.<\/li>\n<li>When you see the 0 symbol for cardinality, you can assume two things: T\n<ol>\n<li>the FK in the Order table allows nulls, and<\/li>\n<li>the FK is not part of the PK since PKs must not contain null values.<\/li>\n<\/ol>\n<\/li>\n<\/ul>\n<figure id=\"attachment_69\" aria-describedby=\"caption-attachment-69\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168.jpg\" alt=\"Ch-9-Cust-to-Order-ERD-300x168\" width=\"300\" height=\"168\" class=\"wp-image-69 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168-65x36.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-9-Cust-to-Order-ERD-300x168-225x126.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-69\" class=\"wp-caption-text\">Figure 9.16. The relationship between a Customer table and an Order table, by A. Watt.<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<h2><strong>\u00a0<\/strong><\/h2>\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<strong>business rules<\/strong>: obtained from users when gathering requirements and are used to determine cardinality<\/p>\n<p><strong>cardinality<\/strong>: expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity<\/p>\n<p><strong>connectivity<\/strong>:\u00a0the relationship between two tables, e.g., one to one or one to many<\/p>\n<p><strong>constraints<\/strong>: the rules that force DBMSs to check that data satisfies the semantics<\/p>\n<p><strong>entity integrity<\/strong>: <span style=\"color: #000000;\">requires that every table have a primary key;\u00a0n<\/span>either the primary key, nor any part of it, can contain null\u00a0values<\/p>\n<p><strong>identifying relationship<\/strong>: where the primary key contains the foreign key; indicated in an ERD by a solid line<\/p>\n<p><strong>integrity constraints<\/strong>: logical statements that state what data values are or are not allowed and which format is suitable for an attribute<\/p>\n<p><strong><span style=\"color: #000000;\">mandatory relationship<\/span><\/strong><span style=\"color: #000000;\">:<\/span>one entity occurrence requires a corresponding entity occurrence.<\/p>\n<p><strong>non-identifying relationship<\/strong>: does not contain the foreign key in the primary key; indicated in an ERD by a dotted line<\/p>\n<p><span style=\"color: #000000;\"><strong>optional relationship<\/strong>:<\/span>\u00a0t<span>he FK can be null or the parent table does not need to have a corresponding child table\u00a0<\/span>occurrence<\/p>\n<p><strong>orphan record<\/strong>: a record whose foreign key value is not found in the corresponding entity &#8211;\u00a0 the entity where the primary key is located<\/p>\n<p><strong>referential integrity<\/strong>: requires that a foreign key must have a matching primary key or it must be null<\/p>\n<p><strong>relational database management system (RDBMS)<\/strong>: a popular database system based on the relational model introduced by E. F. Codd of IBM&#8217;s San Jose Research Laboratory<\/p>\n<p><strong>relationship type<\/strong>: the type of relationship between two<span>\u00a0tables in an ERD (either identifying or non-identifying); this relationship is indicated by a line drawn between the two tables.\u00a0<\/span>\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 style=\"color: #1f1f1d;\"><em>Read the following description and then answer questions 1-5 at the end.<\/em><\/p>\n<p style=\"color: #1f1f1d;\">The\u00a0swim club database in Figure 9.17 has been designed to hold information about students who are enrolled in swim classes. The following information is stored: students, enrollment, swim classes, pools where classes are held, instructors for the classes, and various levels of swim classes. Use Figure 9.17 to answer questions 1 to 5.<\/p>\n<div class=\"wp-nocaption alignnone size-full wp-image-284\" style=\"color: #1f1f1d;\">\n<figure id=\"attachment_284\" aria-describedby=\"caption-attachment-284\" style=\"width: 600px\" class=\"wp-caption aligncenter\"><a style=\"color: #870d0d;\" href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-284\" alt=\"SwimClubDatabase\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase.jpg\" height=\"295\" width=\"600\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase.jpg 966w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase-300x147.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase-65x31.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase-225x110.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/SwimClubDatabase-350x172.jpg 350w\" sizes=\"auto, (max-width: 600px) 100vw, 600px\" \/><\/a><figcaption id=\"caption-attachment-284\" class=\"wp-caption-text\">Figure 9.17. ERD for questions 1-5. (Diagram by A. Watt.)<\/figcaption><\/figure>\n<\/div>\n<\/div>\n<p style=\"color: #1f1f1d;\">The primary keys are identified below. The following data types are defined in the SQL Server.<\/p>\n<p style=\"color: #1f1f1d;\"><strong>tblLevels\u00a0<\/strong><br \/>\nLevel \u2013 Identity PK<br \/>\nClassName \u2013 text 20 \u2013 nulls are not allowed<\/p>\n<p style=\"color: #1f1f1d;\"><strong>tblPool<\/strong><br \/>\nPool \u2013 Identity PK<br \/>\nPoolName \u2013 text 20 \u2013 nulls are not allowed<br \/>\nLocation \u2013 text 30<\/p>\n<p style=\"color: #1f1f1d;\"><strong>tblStaff<\/strong><br \/>\nStaffID \u2013 Identity PK<br \/>\nFirstName \u2013 text 20<br \/>\nMiddleInitial \u2013 text 3<br \/>\nLastName \u2013 text 30<br \/>\nSuffix \u2013 text 3<br \/>\nSalaried \u2013 Bit<br \/>\nPayAmount \u2013 money<\/p>\n<p><strong>tblClasses<\/strong><br \/>\nLessonIndex \u2013 Identity PK<br \/>\nLevel \u2013 Integer FK<br \/>\nSectionID &#8211; Integer<br \/>\nSemester \u2013 TinyInt<br \/>\nDays \u2013 text 20<br \/>\nTime \u2013 datetime (formatted for time)<br \/>\nPool \u2013 Integer FK<br \/>\nInstructor \u2013 Integer FK<br \/>\nLimit \u2013 TinyInt<br \/>\nEnrolled \u2013 TinyInt<br \/>\nPrice \u2013 money<\/p>\n<p style=\"color: #1f1f1d;\"><strong>tblEnrollment<\/strong><br \/>\nLessonIndex \u2013 Integer FK<br \/>\nSID \u2013 Integer FK (LessonIndex and SID) Primary Key<br \/>\nStatus \u2013 text 30<br \/>\nCharged \u2013 bit<br \/>\nAmountPaid \u2013 money<br \/>\nDateEnrolled \u2013 datetime<\/p>\n<p style=\"color: #1f1f1d;\"><strong>tblStudents<\/strong><br \/>\nSID \u2013 Identity PK<br \/>\nFirstName \u2013 text 20<br \/>\nMiddleInitial \u2013 text 3<br \/>\nLastName \u2013 text 30<br \/>\nSuffix \u2013 text 3<br \/>\nBirthday \u2013 datetime<br \/>\nLocalStreet \u2013 text 30<br \/>\nLocalCity \u2013 text 20<br \/>\nLocalPostalCode \u2013 text 6<br \/>\nLocalPhone \u2013 text 10<\/p>\n<p style=\"color: #1f1f1d;\">Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database.<\/p>\n<ol>\n<li>Explain the relationship rules for each relationship (e.g.,\u00a0tblEnrollment and tblStudents: A student can enroll in many classes).<\/li>\n<li>Identify cardinality for each relationship,\u00a0assuming the following rules:\n<ul>\n<li>A pool may or may not ever have a class.<\/li>\n<li>The levels table must always be associated with at least one class.<\/li>\n<li>The staff table may not have ever taught a class.<\/li>\n<li>All students must be enrolled in at least one class.<\/li>\n<li>The class must have students enrolled in it.<\/li>\n<li>The class must have a valid pool.<\/li>\n<li>The class may not have an instructor assigned.<\/li>\n<li>The class must always be associated with an existing level.<\/li>\n<\/ul>\n<\/li>\n<li>Which tables are weak and which tables are strong (covered in an earlier chapter)?<\/li>\n<li>Which of the tables are non-identifying and which are identifying?<\/li>\n<\/ol>\n<\/div>\n<h2>Image Attributions<\/h2>\n<p>Figures 9.3, 9.4, 9.6, 9.8, 9.10, 9.12, 9.14 and 9.15 by A. Watt.<\/p>\n","protected":false},"author":1,"menu_order":9,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["adrienne-watt-nelson-eng"],"pb_section_license":""},"chapter-type":[],"contributor":[48],"license":[],"class_list":["post-71","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt-nelson-eng"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/71","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\/71\/revisions"}],"predecessor-version":[{"id":1146,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/71\/revisions\/1146"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/71\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=71"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=71"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}