{"id":55,"date":"2013-12-12T00:09:54","date_gmt":"2013-12-12T00:09:54","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=55"},"modified":"2019-06-11T16:37:30","modified_gmt":"2019-06-11T16:37:30","slug":"chapter-8-entity-relationship-model","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/chapter-8-entity-relationship-model\/","title":{"raw":"Chapter 8 The Entity Relationship Data Model","rendered":"Chapter 8 The Entity Relationship Data Model"},"content":{"raw":"The <em>entity relationship\u00a0(ER) data model<\/em> has existed for over 35 years. It\u00a0is well suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations.\u00a0ER models, <span>also called an ER schema<\/span>, are represented by ER diagrams.\r\n\r\nER modelling is based on two concepts:\r\n<ul>\r\n \t<li>Entities<em>,<\/em>\u00a0defined as tables that hold specific information (data)<\/li>\r\n \t<li><em>Relationships,<\/em>\u00a0defined as the associations or interactions between entities<\/li>\r\n<\/ul>\r\n<span>Here is an example of how these two concepts might be combined in an ER data model:\u00a0<\/span><span style=\"color: #0000ff;\">Prof. Ba (entity)<\/span><span style=\"color: #339966;\"> teaches (relationship)<\/span><span style=\"color: #0000ff;\"> the Database Systems\u00a0course (entity)<\/span>.\r\n\r\nFor the rest of this chapter, we will use a sample database called the COMPANY database to illustrate the concepts of the ER\u00a0model. This database contains information about employees, departments and projects. Important points to note include:\r\n<ul>\r\n \t<li>There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department.<\/li>\r\n \t<li>A department controls a number of projects, each of which has a unique name, a unique number and a\u00a0budget.<\/li>\r\n \t<li>Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee.<\/li>\r\n \t<li>We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.<\/li>\r\n<\/ul>\r\n<h2>Entity, Entity Set and Entity Type<\/h2>\r\nAn <em>entity<\/em> is an object in the real world with an independent existence that\u00a0can be differentiated from other objects. An entity might be\r\n<ul>\r\n \t<li>An object with physical existence\u00a0(e.g., a lecturer, a student, a car)<\/li>\r\n \t<li>An object with conceptual existence\u00a0(e.g., a course, a job, a position)<\/li>\r\n<\/ul>\r\nEntities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.\r\n<ul>\r\n \t<li><span>That is, it cannot exist without a relationship with another entity<\/span><\/li>\r\n \t<li><span>Its primary key is derived from the primary key of the parent entity<\/span>\r\n<ul>\r\n \t<li><span>The Spouse table, in the COMPANY database, is a weak entity because its primary key is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.<\/span><\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<span>An entity is considered strong if it can exist apart from all of its related entities.<\/span>\r\n<ul>\r\n \t<li><span>Kernels are strong entities.<\/span><\/li>\r\n \t<li><span>A table without a foreign key or a table that contains a foreign key that\u00a0can contain nulls\u00a0is a strong entity<\/span><\/li>\r\n<\/ul>\r\nAnother term to know is <em>entity type<\/em> which defines a collection of similar entities.\r\n\r\nAn <em>entity set<\/em> is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure 8.1, the entity type is EMPLOYEE.\r\n\r\n[caption id=\"attachment_46\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Attributes-300x106.jpg\"><img width=\"300\" height=\"106\" class=\"wp-image-46\" alt=\"A yellow rectangle with e1, e2 and en inside. There is an arrow from the yellow box to a blue rectangle with the work EMPLOYEE in capitals. Over the arrow are the words Represent in ER diagram. To the far left it says Entity set.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/EntitySet-300x86.jpg\" \/><\/a> Figure 8.1. ERD with entity type EMPLOYEE.[\/caption]\r\n<h3>Existence dependency<\/h3>\r\nAn entity\u2019s existence is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign key\u00a0(i.e.,\u00a0a foreign key attribute that cannot be null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee\u00a0entity.\r\n<h2>Kinds of Entities<\/h2>\r\nYou should also be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.\r\n<h3>Independent entities<\/h3>\r\n<em>Independent entities<\/em>, also referred to as kernels, are the backbone of the database. They are\u00a0what other tables are based on. <em>Kernels<\/em> have the following characteristics:\r\n<ul>\r\n \t<li>They are the building blocks of a database.<\/li>\r\n \t<li>The primary key may be simple or composite.<\/li>\r\n \t<li>The primary key is not a foreign key.<\/li>\r\n \t<li>They do not depend on another entity for their existence.<\/li>\r\n<\/ul>\r\n<span>If we refer back to our COMPANY database, examples of an independent entity include the Customer table, Employee table or Product table.<\/span>\r\n<h3>Dependent entities<\/h3>\r\n<em>Dependent entities<\/em>, also referred to as <em>derived entities<\/em>, depend on other tables for their meaning.\u00a0These entities have the following characteristics:\r\n<ul>\r\n \t<li>Dependent entities are used to connect two kernels together.<\/li>\r\n \t<li>They are said to be existence dependent on two or more tables.<\/li>\r\n \t<li>Many to many relationships become associative tables with at least two foreign keys.<\/li>\r\n \t<li>They may contain other attributes.<\/li>\r\n \t<li>The foreign key identifies each associated table.<\/li>\r\n \t<li>There are three options for the primary key:\r\n<ol>\r\n \t<li>Use a composite of foreign keys of associated tables if unique<\/li>\r\n \t<li>Use a composite of foreign keys and a qualifying column<\/li>\r\n \t<li>Create a new simple primary key<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ul>\r\n<h3>Characteristic entities<\/h3>\r\n<em>Characteristic entities<\/em> provide more information about another table. These entities have the following characteristics:\r\n<ul>\r\n \t<li>They represent multivalued attributes.<\/li>\r\n \t<li>They describe other entities.<\/li>\r\n \t<li>They typically have a one to many relationship.<\/li>\r\n \t<li>The foreign key is used to further identify the characterized table.<\/li>\r\n \t<li>Options for primary key are as follows:\r\n<ol>\r\n \t<li>Use a composite of foreign key plus a qualifying column<\/li>\r\n \t<li>Create a new simple primary key.<span> In the COMPANY database, these might include:<\/span>\r\n<ul>\r\n \t<li>Employee (<span style=\"text-decoration: underline;\">EID,<\/span> Name, Address, Age, Salary) - EID is the simple primary key.<\/li>\r\n \t<li>EmployeePhone (<span style=\"text-decoration: underline;\">EID, Phone<\/span>) - EID is part of a composite primary key. Here, EID is also a foreign key.<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ul>\r\n<h2>Attributes<\/h2>\r\nEach entity is described by a set of attributes\u00a0(e.g., Employee = (Name, Address,<span> Birthdate (Age)<\/span>, Salary).\r\n\r\n<span>Each attribute has a name, and is associated with an entity and a domain of legal values.<\/span> However, the information about attribute domain is not presented on the ERD.\r\n\r\nIn the entity relationship diagram, shown in Figure 8.2, each attribute is represented by an oval with a name inside.\r\n\r\n[caption id=\"attachment_38\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Distributed-Systems-300x217.jpg\"><img width=\"300\" height=\"106\" class=\"wp-image-38\" alt=\"One blue rectangle with the word EMPLOYEE. This is connected with a line to four separate yellow ovals. Each has a different word inside it: Name, Address, Birthdate, Salary.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Attributes-300x106.jpg\" \/><\/a> Figure 8.2. How attributes are represented in an ERD.[\/caption]\r\n<h2>Types of Attributes<\/h2>\r\nThere are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model. This first section will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model.\r\n<h3>Simple attributes<\/h3>\r\n<em>Simple attributes<\/em> are those drawn from the atomic value domains; they are also called <em>single-valued attributes<\/em>. In the COMPANY database, an example of this would be:\u00a0Name = {John} ; Age = {23}\r\n<h3>Composite attributes<\/h3>\r\n<em>Composite attributes<\/em> are those that consist of a hierarchy of attributes. <span style=\"color: #ff0000;\"><span style=\"color: #333333;\">Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written as \u2192 Address = {59 + \u2018Meek Street\u2019 + \u2018Kingsford\u2019}<\/span><\/span>\r\n\r\n[caption id=\"attachment_32\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Network-data-model-300x244.jpg\"><img width=\"300\" height=\"133\" class=\"wp-image-32\" alt=\"Blue rectangle with the word EMPLOYEE. Under this are four yellow ovals with the words Name, Address, Birthdate, Salary. There are lines between the rectangle and yellow ovals. Under the Address oval are three white ovals with the words Number, Street, Suburb. \" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Simple-and-Composite-Attributes-300x133.jpg\" \/><\/a> Figure 8.3. An example of composite attributes.[\/caption]\r\n<h3>Multivalued attributes<\/h3>\r\n<em>Multivalued\u00a0attributes<\/em> are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database,\u00a0as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.\r\n\r\n[caption id=\"attachment_52\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Many-to-Many-Relationships-300x167.jpg\"><img width=\"300\" height=\"131\" class=\"wp-image-52\" alt=\"Blue rectangle with the word EMPLOYEE. A line connects this to each of five yellow ovals with these words inside the ovals: Degrees, Name, Address, Birthdate, Salary\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Multivalued-Attribute-300x131.jpg\" \/><\/a> Figure 8.4. Example of a multivalued attribute.[\/caption]\r\n<h3>Derived attributes<\/h3>\r\n<em>Derived attributes<\/em>\u00a0are attributes that contain values calculated from other attributes. An example of this can be seen in Figure 8.5.\u00a0 Age can be derived from the attribute <span>Birthdate.<\/span> In this situation,<span>\u00a0Birthdate is <\/span>called a <em>stored attribute,\u00a0<\/em>which is physically saved to the database.\r\n\r\n[caption id=\"attachment_49\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154.jpg\" alt=\"Blue rectangle with the word EMPLOYEE, and connected by a line to five different yellow ovals with the words: Age, Name, Address, Birthdate, Salary.\" width=\"300\" height=\"154\" class=\"wp-image-49 size-full\" \/><\/a> Figure 8.5. Example of a derived attribute.[\/caption]\r\n<h2>Keys<\/h2>\r\nAn important constraint on an entity\u00a0is the key. The <em>key<\/em> is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.\r\n<h2>Types of Keys<\/h2>\r\nThere are several types of keys. These are described below.\r\n<h3>Candidate key<\/h3>\r\nA <em>candidate key<\/em> is a simple or composite key that is unique and minimal.\u00a0 It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness.\r\n\r\nFrom our COMPANY database example,<span> if the entity is<\/span> <strong>Employee<\/strong>(EID, First Name, Last Name, <em>SIN<\/em>, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:\r\n<ul>\r\n \t<li>EID, SIN<\/li>\r\n \t<li>First Name and Last Name \u2013 assuming there is no one else in the company with the same name<\/li>\r\n \t<li>Last Name and DepartmentID \u2013 assuming two people with the same last name don\u2019t work in the same department<\/li>\r\n<\/ul>\r\n<h3>Composite key<\/h3>\r\nA <em>composite key<\/em> is composed of two or more attributes, but it must be minimal.\r\n\r\nUsing the example from the candidate key section, possible composite\u00a0keys are:\r\n<ul>\r\n \t<li>First Name and Last Name \u2013 assuming there is no one else in the company with the same name<\/li>\r\n \t<li>Last Name and Department ID \u2013 assuming two people with the same last name don\u2019t work in the same department<\/li>\r\n<\/ul>\r\n<h3>Primary key<\/h3>\r\n<span style=\"color: #333333;\">The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute.<\/span>\r\n<ul>\r\n \t<li><span style=\"color: #333333;\">A candidate key is selected by the design<\/span>er to uniquely identify tuples in a table. It must not be null.<\/li>\r\n \t<li>A key is chosen by the database designer to be used as an identifying mechanism for the whole entity set.\u00a0 This is referred to as the primary key. This key is indicated by underlining the attribute in the ER model.<\/li>\r\n<\/ul>\r\nIn the following example, EID is the primary key:\r\n\r\n<strong>Employee<\/strong>(<span style=\"text-decoration: underline; color: #333333;\"><span style=\"text-decoration: underline;\">EID<\/span><\/span>, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)\r\n<h3>Secondary key<\/h3>\r\nA <em>secondary key<\/em> is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name.\r\n<h3>Alternate key<\/h3>\r\n<em>Alternate keys<\/em>\u00a0are all candidate keys not chosen as the primary key.\r\n<h3>Foreign key<\/h3>\r\nA<em> foreign key (FK)<\/em> is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type.\r\n\r\nIn the COMPANY database example below, DepartmentID is the foreign key:\r\n\r\n<strong>Employee<\/strong>(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)\r\n<h2>Nulls<\/h2>\r\nA<em> null<\/em> is a special symbol, independent of data type, which means either unknown or inapplicable. It does not mean zero or blank. Features of null include:\r\n<ul>\r\n \t<li>No data entry<\/li>\r\n \t<li>Not permitted in the primary key<\/li>\r\n \t<li>Should be avoided in other attributes<\/li>\r\n \t<li>Can represent\r\n<ul>\r\n \t<li>An unknown attribute value<\/li>\r\n \t<li>A known, but missing, attribute value<\/li>\r\n \t<li>A \u201cnot applicable\u201d condition<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li>Can create problems when functions such as COUNT, AVERAGE and SUM are used<\/li>\r\n \t<li>Can create logical problems when relational tables are linked<\/li>\r\n<\/ul>\r\nNOTE: The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions that\u00a0ignore nulls).\r\n<h3>Example of how null can be used<\/h3>\r\nUse the Salary table (Salary_tbl) in Figure 8.6 to follow an example of how null can be used.\r\n\r\n[caption id=\"attachment_50\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132.jpg\" alt=\"SalaryTable-300x132\" width=\"300\" height=\"132\" class=\"wp-image-50 size-full\" \/><\/a> Figure 8.6. Salary table for null example, by A. Watt.[\/caption]\r\n\r\nTo begin, find all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000.\r\n<ul>\r\n \t<li>SELECT emp# FROM Salary_tbl<\/li>\r\n \t<li>WHERE \u00a0jobName = Sales AND<\/li>\r\n \t<li>(commission + salary) &gt; 30,000 \u00a0\u2013&gt; E10 and E12<\/li>\r\n<\/ul>\r\nThis result does not include E13 because of the null value in \u00a0the commission column. To ensure that the row with the null value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below.\r\n<ul>\r\n \t<li>SELECT emp# FROM Salary_tbl<\/li>\r\n \t<li>WHERE jobName = Sales AND<\/li>\r\n \t<li>(commission &gt; 30000 OR<\/li>\r\n \t<li>salary &gt; 30000 OR<\/li>\r\n \t<li>(commission + salary) &gt; 30,000 \u00a0\u2013&gt;E10 and E12 and E13<\/li>\r\n<\/ul>\r\n<h2>Relationships<\/h2>\r\n<em>Relationships<\/em> are the glue that holds the tables together. They are used to connect related information<span> between <\/span>tables.\r\n\r\n<em>Relationship strength<\/em> is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. Company database examples include:\r\n<ul>\r\n \t<li><span>Customer(<strong><span style=\"text-decoration: underline;\">CustID<\/span><\/strong>, CustName)<\/span><\/li>\r\n \t<li><span>Order(<strong><span style=\"text-decoration: underline;\">OrderID<\/span><\/strong>, CustID, Date)<\/span><\/li>\r\n<\/ul>\r\n<span>A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include:<\/span>\r\n<ul>\r\n \t<li><span>Course(<strong><span style=\"text-decoration: underline;\">CrsCode<\/span><\/strong>, DeptCode, Description)<\/span><\/li>\r\n \t<li><span>Class(<strong><span style=\"text-decoration: underline;\">CrsCode, Section<\/span><\/strong>, ClassTime\u2026)<\/span><\/li>\r\n<\/ul>\r\n<h2>Types of Relationships<\/h2>\r\nBelow are descriptions of the various types of relationships.\r\n<h3><span>One to many <\/span>(1:M) relationship<\/h3>\r\n<span style=\"color: #ff0000;\"><span style=\"color: #333333;\">A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure 8.7 shows the relationship of one of these employees to the department.<\/span><\/span>\r\n\r\n[caption id=\"attachment_26\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/FileBased-300x170.jpg\"><img width=\"300\" height=\"137\" class=\"wp-image-26\" alt=\"A light blue diamond in the middle connected on either side to a blue rectangle. The rectangle on the left says EMPLOYEE and is connected with a line to five yellow ovals with the words Birthdate, Name, Address, Salary, EID. The diamond is also connected to a blue rectangle on its right with the word DEPARTMENT and that is connected with lines to three yellow ovals with the words Name, Office, DeptID.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/One-to-Many-Relationships-300x137.jpg\" \/><\/a> Figure 8.7. Example of a one to many relationship.[\/caption]\r\n<h3><span>One to one<\/span> (1:1) relationship<\/h3>\r\nA one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table.\r\n\r\nAn example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee.\r\n<h3><span>Many to many\u00a0<\/span>(M:N) relationships<\/h3>\r\n<span>For a many to many relationship, consider the following points:<\/span>\r\n<ul>\r\n \t<li>It cannot be implemented as such in the relational model.<\/li>\r\n \t<li>It can be changed into two 1:M relationships.<\/li>\r\n \t<li>It can be implemented by breaking up to produce a set of 1:M relationships.<\/li>\r\n \t<li><span>It involves the<\/span> implementation of a composite entity.<\/li>\r\n \t<li>Creates two or more 1:M relationships.<\/li>\r\n \t<li>The composite entity table must contain at least the primary keys of the original tables.<\/li>\r\n \t<li>The linking table contains multiple occurrences of the foreign key values.<\/li>\r\n \t<li>Additional attributes may be assigned as needed.<\/li>\r\n \t<li>It can avoid problems inherent in an\u00a0M:N relationship by creating a composite entity or bridge entity. For example, an employee can work on many projects OR a project can have many employees working on it, depending on the business rules. Or,\u00a0a student can have many classes and a class can hold many students.<\/li>\r\n<\/ul>\r\nFigure 8.8 shows another another aspect of the M:N relationship where an employee has different start dates for different projects<span style=\"color: #000000;\">. Therefore, we need a JOIN table that contains the EID, Code and StartDate.<\/span>\r\n\r\n[caption id=\"attachment_51\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/One-to-Many-Relationships-300x137.jpg\"><img width=\"300\" height=\"167\" class=\"wp-image-51\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Many-to-Many-Relationships-300x167.jpg\" \/><\/a> Figure 8.8. Example where employee has different start dates for different projects.[\/caption]\r\n\r\n<span style=\"color: #333333;\"><strong>Example of mapping\u00a0an M:N binary relationship type<\/strong><\/span>\r\n<ul>\r\n \t<li>For each M:N binary relationship, identify two relations.<\/li>\r\n \t<li>A and B represent two entity types participating in R.<\/li>\r\n \t<li>Create a new relation S to represent R.<\/li>\r\n \t<li><span style=\"color: #333333;\">S needs to contain the PKs of A and B. These together can be the PK in the S table OR these together with another simple attribute in the new table R can be the PK.\u00a0<\/span><\/li>\r\n \t<li>The combination of the primary keys (A and B) will make the primary key of S.<\/li>\r\n<\/ul>\r\n<h3>Unary relationship (recursive)<\/h3>\r\nA <em>unary relationship, <\/em>also called <em>recursive, <\/em> is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with\u00a0different roles. See Figure 8.9 for an example.\r\n\r\n<span style=\"color: #333333;\">For some entities in a unary relationship<\/span>, a separate column can be created that refers to the primary key of the same entity set.\r\n\r\n[caption id=\"attachment_35\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Data-Abstraction-300x226.jpg\"><img width=\"300\" height=\"168\" class=\"wp-image-35\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Unary-Relationships-300x168.jpg\" \/><\/a> Figure 8.9. Example of a unary relationship.[\/caption]\r\n<h3>Ternary Relationships<\/h3>\r\n<span style=\"color: #333333;\">A <em>ternary relationship<\/em> is a relationship type that involves many to many relationships between three tables.\u00a0<\/span>\r\n\r\nRefer to Figure 8.10 for an example of mapping a ternary relationship type. Note\u00a0<em>n-ary<\/em> means multiple tables in a relationship. (Remember, N = many.)\r\n<ul>\r\n \t<li>For each n-ary (&gt; 2) relationship, create a new relation to represent the relationship.<\/li>\r\n \t<li>The primary key of the new relation is a combination of the primary keys of the participating entities that hold the N (many) side.<\/li>\r\n \t<li>In most cases of an n-ary relationship, all the participating entities hold a <strong>many<\/strong> side.<\/li>\r\n<\/ul>\r\n[caption id=\"attachment_54\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197.jpg\" alt=\"Ternary-Mapping-Relationships-300x197\" width=\"300\" height=\"197\" class=\"wp-image-54 size-full\" \/><\/a> Figure 8.10. Example of a ternary relationship.[\/caption]\r\n\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\"><strong>alternate key<\/strong>: all candidate keys not chosen as the primary key<strong>candidate key<\/strong>: a simple or composite key that is unique (no two rows in a table may have the same value) and minimal (every column is necessary)\r\n\r\n<strong>characteristic entities<\/strong>: entities that provide more information about another table\r\n\r\n<strong>composite attributes<\/strong>: attributes that consist of a hierarchy of attributes\r\n\r\n<strong>composite key<\/strong>: composed of two or more attributes, but it must be minimal\r\n\r\n<strong>dependent entities<\/strong>: these entities depend on other tables for their meaning\r\n\r\n<strong>derived attributes<\/strong>: attributes that contain values calculated from other attributes\r\n\r\n<strong>derived entities<\/strong>: see<em> dependent entities<\/em>\r\n\r\n<strong>EID<\/strong>: <span style=\"color: #333333;\">employee identification (ID)<\/span>\r\n\r\n<strong>entity<\/strong>: a thing or\u00a0object in the real world with an independent existence that\u00a0can be differentiated from other objects\r\n\r\n<strong>entity relationship (ER) data model<\/strong>:\u00a0<span>also called an ER schema<\/span><span>, are represented by ER diagrams. These are\u00a0well suited to data modelling for use with databases.<\/span>\r\n\r\n<strong>entity relationship schema<\/strong><span style=\"color: #000000;\">: see<em> entity relationship data model<\/em><\/span>\r\n\r\n<strong>entity set<\/strong>:a collection of entities of an entity type at a point of time\r\n\r\n<strong>entity type<\/strong>:\u00a0a collection of similar entities\r\n\r\n<strong>foreign key (FK)<\/strong>: an attribute in a table that references the primary key in another table <span>OR it can be null<\/span>\r\n\r\n<strong>independent entity<\/strong>: as the building blocks of a database, these entities are what other tables are based on\r\n\r\n<strong>kernel<\/strong>: see<em> independent entity<\/em>\r\n\r\n<strong>key<\/strong>: an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set\r\n\r\n<strong>multivalued\u00a0attributes<\/strong>: attributes that have a set of values for each entity\r\n\r\n<strong>n-ary<\/strong>:\u00a0multiple tables in a relationship\r\n\r\n<strong>null<\/strong>: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank\r\n\r\n<strong>recursive relationship<\/strong>: see<em> unary relationship<\/em>\r\n\r\n<strong>relationships<\/strong>:\u00a0the associations or interactions between entities; used to connect related information between tables\r\n\r\n<strong>relationship strength<\/strong>:\u00a0 based on how the primary key of a related entity is defined\r\n\r\n<strong>secondary key<\/strong><span>\u00a0an attribute used strictly for retrieval purposes\u00a0<\/span>\r\n\r\n<strong>simple attributes<\/strong>: drawn from the atomic value domains\r\n\r\n<strong>SIN<\/strong>: social insurance number\r\n\r\n<strong>single-valued attributes<\/strong>: see<em> simple attributes<\/em>\r\n\r\n<strong>stored attribute<\/strong>: saved physically to the database\r\n\r\n<strong>ternary relationship<\/strong>:\u00a0a relationship type that involves many to many relationships between three tables.\r\n\r\n<strong>unary relationship<\/strong>: one in which a relationship exists between occurrences of the same entity set.\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\r\n<ol>\r\n \t<li>What two concepts are ER modelling based on?<\/li>\r\n \t<li>The database in Figure 8.11 is composed of two tables. Use this figure to answer questions 2.1 to 2.5.\r\n\r\n[caption id=\"attachment_251\" align=\"aligncenter\" width=\"400\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109.jpg\"><img width=\"400\" height=\"286\" class=\"wp-image-251\" alt=\"Ch8-Exercises -Fig8-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109.jpg\" \/><\/a> Figure 8.11. Director and Play tables\u00a0for question 2, by A. Watt.[\/caption]\r\n<ol>\r\n \t<li>Identify the primary key for each table.<\/li>\r\n \t<li>Identify the foreign key in the PLAY table.<\/li>\r\n \t<li>Identify the candidate keys in both tables.<\/li>\r\n \t<li>Draw the ER model.<\/li>\r\n \t<li>Does the PLAY table exhibit referential integrity? Why or why not?<\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>Define the following terms (you may need to use the Internet for some of these):\r\nschema\r\nhost language\r\ndata sublanguage\r\ndata definition language\r\nunary relation\r\nforeign key\r\nvirtual relation\r\nconnectivity\r\ncomposite key\r\nlinking table<\/li>\r\n \t<li>The RRE Trucking Company database includes the three tables \u00a0in Figure 8.12. Use Figure 8.12 to answer questions 4.1 to 4.5.\r\n\r\n[caption id=\"attachment_252\" align=\"aligncenter\" width=\"450\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961.jpg\"><img width=\"450\" height=\"230\" class=\"wp-image-252\" alt=\"Ch8-Exercises -Fig8-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961.jpg\" \/><\/a> Figure 8.12. Truck, Base and Type tables for question 4, by A. Watt.[\/caption]\r\n<ol>\r\n \t<li>Identify the primary and foreign key(s) for each table.<\/li>\r\n \t<li>Does the TRUCK table exhibit entity and referential integrity? Why or why not? Explain your answer.<\/li>\r\n \t<li>What kind of relationship exists between the TRUCK and BASE tables?<\/li>\r\n \t<li>How many entities does the TRUCK table contain ?<\/li>\r\n \t<li>Identify the TRUCK table candidate key(s).\r\n\r\n[caption id=\"attachment_253\" align=\"aligncenter\" width=\"400\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204.jpg\"><img width=\"400\" height=\"259\" class=\"wp-image-253\" alt=\"Ch8-Exercises -Fig8-3\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204.jpg\" \/><\/a> Figure 8.13. Customer and BookOrders tables for question 5, by A. Watt.[\/caption]<\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>Suppose you are using the database\u00a0in Figure 8.13, composed of the two tables.\u00a0Use Figure 8.13 to answer questions 5.1 to 5.6.\r\n<ol>\r\n \t<li>Identify the primary key in each table.<\/li>\r\n \t<li>Identify the foreign key in the BookOrders table.<\/li>\r\n \t<li>Are there any candidate keys in either table?<\/li>\r\n \t<li>Draw the ER model.<\/li>\r\n \t<li>Does the BookOrders table exhibit referential integrity? Why or why not?<\/li>\r\n \t<li>Do the tables contain redundant data? If so which table(s) and what is the redundant data?<\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li>Looking at the student table in Figure 8.14, list all the possible candidate keys. Why did you select these?\r\n\r\n[caption id=\"attachment_250\" align=\"aligncenter\" width=\"150\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student.jpg\"><img width=\"150\" height=\"255\" class=\"wp-image-250\" alt=\"Ch8-Exercises - No6 Student\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student.jpg\" \/><\/a> Figure 8.14. Student table for question 6, by A. Watt.[\/caption]\r\n\r\n[caption id=\"attachment_254\" align=\"aligncenter\" width=\"400\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database.jpg\"><img width=\"400\" height=\"250\" class=\"wp-image-254\" alt=\"Ch8-Exercises -No7 School Database\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-1024x640.jpg\" \/><\/a> Figure 8.15. ERD of school database for questions 7-10, by A. Watt.[\/caption]\r\n\r\nUse the ERD\u00a0of a school database in Figure 8.15 to answer questions 7 to 10.<\/li>\r\n \t<li>Identity all the kernels and dependent and characteristic entities in the ERD.<\/li>\r\n \t<li>Which of the tables contribute to weak relationships? Strong relationships?<\/li>\r\n \t<li>Looking at each of the tables in the school database in Figure 8.15, which attribute could have a NULL value?\u00a0 Why?<\/li>\r\n \t<li>Which of the tables were created as a result of many to many relationships?<\/li>\r\n<\/ol>\r\n<p style=\"color: #1f1f1d;\"><strong>Also see<\/strong> <em>Appendix B: Sample ERD Exercises<\/em><\/p>\r\n\r\n<\/div><\/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 otherwisse noted) is a derivative copy of\u00a0<a href=\"http:\/\/cnx.org\/contents\/68cd9551-e453-420e-a906-d8e7e71260bb@1\">Data Modeling Using Entity-Relationship Model<\/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>Nulls section and example<\/li>\r\n \t<li>Key Terms<\/li>\r\n \t<li>Exercises<\/li>\r\n<\/ol>","rendered":"<p>The <em>entity relationship\u00a0(ER) data model<\/em> has existed for over 35 years. It\u00a0is well suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations.\u00a0ER models, <span>also called an ER schema<\/span>, are represented by ER diagrams.<\/p>\n<p>ER modelling is based on two concepts:<\/p>\n<ul>\n<li>Entities<em>,<\/em>\u00a0defined as tables that hold specific information (data)<\/li>\n<li><em>Relationships,<\/em>\u00a0defined as the associations or interactions between entities<\/li>\n<\/ul>\n<p><span>Here is an example of how these two concepts might be combined in an ER data model:\u00a0<\/span><span style=\"color: #0000ff;\">Prof. Ba (entity)<\/span><span style=\"color: #339966;\"> teaches (relationship)<\/span><span style=\"color: #0000ff;\"> the Database Systems\u00a0course (entity)<\/span>.<\/p>\n<p>For the rest of this chapter, we will use a sample database called the COMPANY database to illustrate the concepts of the ER\u00a0model. This database contains information about employees, departments and projects. Important points to note include:<\/p>\n<ul>\n<li>There are several departments in the company. Each department has a unique identification, a name, location of the office and a particular employee who manages the department.<\/li>\n<li>A department controls a number of projects, each of which has a unique name, a unique number and a\u00a0budget.<\/li>\n<li>Each employee has a name, identification number, address, salary and birthdate. An employee is assigned to one department but can join in several projects. We need to record the start date of the employee in each project. We also need to know the direct supervisor of each employee.<\/li>\n<li>We want to keep track of the dependents for each employee. Each dependent has a name, birthdate and relationship with the employee.<\/li>\n<\/ul>\n<h2>Entity, Entity Set and Entity Type<\/h2>\n<p>An <em>entity<\/em> is an object in the real world with an independent existence that\u00a0can be differentiated from other objects. An entity might be<\/p>\n<ul>\n<li>An object with physical existence\u00a0(e.g., a lecturer, a student, a car)<\/li>\n<li>An object with conceptual existence\u00a0(e.g., a course, a job, a position)<\/li>\n<\/ul>\n<p>Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.<\/p>\n<ul>\n<li><span>That is, it cannot exist without a relationship with another entity<\/span><\/li>\n<li><span>Its primary key is derived from the primary key of the parent entity<\/span>\n<ul>\n<li><span>The Spouse table, in the COMPANY database, is a weak entity because its primary key is dependent on the Employee table. Without a corresponding employee record, the spouse record would not exist.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><span>An entity is considered strong if it can exist apart from all of its related entities.<\/span><\/p>\n<ul>\n<li><span>Kernels are strong entities.<\/span><\/li>\n<li><span>A table without a foreign key or a table that contains a foreign key that\u00a0can contain nulls\u00a0is a strong entity<\/span><\/li>\n<\/ul>\n<p>Another term to know is <em>entity type<\/em> which defines a collection of similar entities.<\/p>\n<p>An <em>entity set<\/em> is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure 8.1, the entity type is EMPLOYEE.<\/p>\n<figure id=\"attachment_46\" aria-describedby=\"caption-attachment-46\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Attributes-300x106.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"106\" class=\"wp-image-46\" alt=\"A yellow rectangle with e1, e2 and en inside. There is an arrow from the yellow box to a blue rectangle with the work EMPLOYEE in capitals. Over the arrow are the words Represent in ER diagram. To the far left it says Entity set.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/EntitySet-300x86.jpg\" \/><\/a><figcaption id=\"caption-attachment-46\" class=\"wp-caption-text\">Figure 8.1. ERD with entity type EMPLOYEE.<\/figcaption><\/figure>\n<h3>Existence dependency<\/h3>\n<p>An entity\u2019s existence is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign key\u00a0(i.e.,\u00a0a foreign key attribute that cannot be null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee\u00a0entity.<\/p>\n<h2>Kinds of Entities<\/h2>\n<p>You should also be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below.<\/p>\n<h3>Independent entities<\/h3>\n<p><em>Independent entities<\/em>, also referred to as kernels, are the backbone of the database. They are\u00a0what other tables are based on. <em>Kernels<\/em> have the following characteristics:<\/p>\n<ul>\n<li>They are the building blocks of a database.<\/li>\n<li>The primary key may be simple or composite.<\/li>\n<li>The primary key is not a foreign key.<\/li>\n<li>They do not depend on another entity for their existence.<\/li>\n<\/ul>\n<p><span>If we refer back to our COMPANY database, examples of an independent entity include the Customer table, Employee table or Product table.<\/span><\/p>\n<h3>Dependent entities<\/h3>\n<p><em>Dependent entities<\/em>, also referred to as <em>derived entities<\/em>, depend on other tables for their meaning.\u00a0These entities have the following characteristics:<\/p>\n<ul>\n<li>Dependent entities are used to connect two kernels together.<\/li>\n<li>They are said to be existence dependent on two or more tables.<\/li>\n<li>Many to many relationships become associative tables with at least two foreign keys.<\/li>\n<li>They may contain other attributes.<\/li>\n<li>The foreign key identifies each associated table.<\/li>\n<li>There are three options for the primary key:\n<ol>\n<li>Use a composite of foreign keys of associated tables if unique<\/li>\n<li>Use a composite of foreign keys and a qualifying column<\/li>\n<li>Create a new simple primary key<\/li>\n<\/ol>\n<\/li>\n<\/ul>\n<h3>Characteristic entities<\/h3>\n<p><em>Characteristic entities<\/em> provide more information about another table. These entities have the following characteristics:<\/p>\n<ul>\n<li>They represent multivalued attributes.<\/li>\n<li>They describe other entities.<\/li>\n<li>They typically have a one to many relationship.<\/li>\n<li>The foreign key is used to further identify the characterized table.<\/li>\n<li>Options for primary key are as follows:\n<ol>\n<li>Use a composite of foreign key plus a qualifying column<\/li>\n<li>Create a new simple primary key.<span> In the COMPANY database, these might include:<\/span>\n<ul>\n<li>Employee (<span style=\"text-decoration: underline;\">EID,<\/span> Name, Address, Age, Salary) &#8211; EID is the simple primary key.<\/li>\n<li>EmployeePhone (<span style=\"text-decoration: underline;\">EID, Phone<\/span>) &#8211; EID is part of a composite primary key. Here, EID is also a foreign key.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<\/li>\n<\/ul>\n<h2>Attributes<\/h2>\n<p>Each entity is described by a set of attributes\u00a0(e.g., Employee = (Name, Address,<span> Birthdate (Age)<\/span>, Salary).<\/p>\n<p><span>Each attribute has a name, and is associated with an entity and a domain of legal values.<\/span> However, the information about attribute domain is not presented on the ERD.<\/p>\n<p>In the entity relationship diagram, shown in Figure 8.2, each attribute is represented by an oval with a name inside.<\/p>\n<figure id=\"attachment_38\" aria-describedby=\"caption-attachment-38\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Distributed-Systems-300x217.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"106\" class=\"wp-image-38\" alt=\"One blue rectangle with the word EMPLOYEE. This is connected with a line to four separate yellow ovals. Each has a different word inside it: Name, Address, Birthdate, Salary.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Attributes-300x106.jpg\" \/><\/a><figcaption id=\"caption-attachment-38\" class=\"wp-caption-text\">Figure 8.2. How attributes are represented in an ERD.<\/figcaption><\/figure>\n<h2>Types of Attributes<\/h2>\n<p>There are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model. This first section will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model.<\/p>\n<h3>Simple attributes<\/h3>\n<p><em>Simple attributes<\/em> are those drawn from the atomic value domains; they are also called <em>single-valued attributes<\/em>. In the COMPANY database, an example of this would be:\u00a0Name = {John} ; Age = {23}<\/p>\n<h3>Composite attributes<\/h3>\n<p><em>Composite attributes<\/em> are those that consist of a hierarchy of attributes. <span style=\"color: #ff0000;\"><span style=\"color: #333333;\">Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written as \u2192 Address = {59 + \u2018Meek Street\u2019 + \u2018Kingsford\u2019}<\/span><\/span><\/p>\n<figure id=\"attachment_32\" aria-describedby=\"caption-attachment-32\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Network-data-model-300x244.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"133\" class=\"wp-image-32\" alt=\"Blue rectangle with the word EMPLOYEE. Under this are four yellow ovals with the words Name, Address, Birthdate, Salary. There are lines between the rectangle and yellow ovals. Under the Address oval are three white ovals with the words Number, Street, Suburb.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Simple-and-Composite-Attributes-300x133.jpg\" \/><\/a><figcaption id=\"caption-attachment-32\" class=\"wp-caption-text\">Figure 8.3. An example of composite attributes.<\/figcaption><\/figure>\n<h3>Multivalued attributes<\/h3>\n<p><em>Multivalued\u00a0attributes<\/em> are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database,\u00a0as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.<\/p>\n<figure id=\"attachment_52\" aria-describedby=\"caption-attachment-52\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Many-to-Many-Relationships-300x167.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"131\" class=\"wp-image-52\" alt=\"Blue rectangle with the word EMPLOYEE. A line connects this to each of five yellow ovals with these words inside the ovals: Degrees, Name, Address, Birthdate, Salary\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Multivalued-Attribute-300x131.jpg\" \/><\/a><figcaption id=\"caption-attachment-52\" class=\"wp-caption-text\">Figure 8.4. Example of a multivalued attribute.<\/figcaption><\/figure>\n<h3>Derived attributes<\/h3>\n<p><em>Derived attributes<\/em>\u00a0are attributes that contain values calculated from other attributes. An example of this can be seen in Figure 8.5.\u00a0 Age can be derived from the attribute <span>Birthdate.<\/span> In this situation,<span>\u00a0Birthdate is <\/span>called a <em>stored attribute,\u00a0<\/em>which is physically saved to the database.<\/p>\n<figure id=\"attachment_49\" aria-describedby=\"caption-attachment-49\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154.jpg\" alt=\"Blue rectangle with the word EMPLOYEE, and connected by a line to five different yellow ovals with the words: Age, Name, Address, Birthdate, Salary.\" width=\"300\" height=\"154\" class=\"wp-image-49 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154-65x33.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Derived-Attribute-300x154-225x115.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-49\" class=\"wp-caption-text\">Figure 8.5. Example of a derived attribute.<\/figcaption><\/figure>\n<h2>Keys<\/h2>\n<p>An important constraint on an entity\u00a0is the key. The <em>key<\/em> is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set.<\/p>\n<h2>Types of Keys<\/h2>\n<p>There are several types of keys. These are described below.<\/p>\n<h3>Candidate key<\/h3>\n<p>A <em>candidate key<\/em> is a simple or composite key that is unique and minimal.\u00a0 It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness.<\/p>\n<p>From our COMPANY database example,<span> if the entity is<\/span> <strong>Employee<\/strong>(EID, First Name, Last Name, <em>SIN<\/em>, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:<\/p>\n<ul>\n<li>EID, SIN<\/li>\n<li>First Name and Last Name \u2013 assuming there is no one else in the company with the same name<\/li>\n<li>Last Name and DepartmentID \u2013 assuming two people with the same last name don\u2019t work in the same department<\/li>\n<\/ul>\n<h3>Composite key<\/h3>\n<p>A <em>composite key<\/em> is composed of two or more attributes, but it must be minimal.<\/p>\n<p>Using the example from the candidate key section, possible composite\u00a0keys are:<\/p>\n<ul>\n<li>First Name and Last Name \u2013 assuming there is no one else in the company with the same name<\/li>\n<li>Last Name and Department ID \u2013 assuming two people with the same last name don\u2019t work in the same department<\/li>\n<\/ul>\n<h3>Primary key<\/h3>\n<p><span style=\"color: #333333;\">The primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute.<\/span><\/p>\n<ul>\n<li><span style=\"color: #333333;\">A candidate key is selected by the design<\/span>er to uniquely identify tuples in a table. It must not be null.<\/li>\n<li>A key is chosen by the database designer to be used as an identifying mechanism for the whole entity set.\u00a0 This is referred to as the primary key. This key is indicated by underlining the attribute in the ER model.<\/li>\n<\/ul>\n<p>In the following example, EID is the primary key:<\/p>\n<p><strong>Employee<\/strong>(<span style=\"text-decoration: underline; color: #333333;\"><span style=\"text-decoration: underline;\">EID<\/span><\/span>, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)<\/p>\n<h3>Secondary key<\/h3>\n<p>A <em>secondary key<\/em> is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name.<\/p>\n<h3>Alternate key<\/h3>\n<p><em>Alternate keys<\/em>\u00a0are all candidate keys not chosen as the primary key.<\/p>\n<h3>Foreign key<\/h3>\n<p>A<em> foreign key (FK)<\/em> is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type.<\/p>\n<p>In the COMPANY database example below, DepartmentID is the foreign key:<\/p>\n<p><strong>Employee<\/strong>(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)<\/p>\n<h2>Nulls<\/h2>\n<p>A<em> null<\/em> is a special symbol, independent of data type, which means either unknown or inapplicable. It does not mean zero or blank. Features of null include:<\/p>\n<ul>\n<li>No data entry<\/li>\n<li>Not permitted in the primary key<\/li>\n<li>Should be avoided in other attributes<\/li>\n<li>Can represent\n<ul>\n<li>An unknown attribute value<\/li>\n<li>A known, but missing, attribute value<\/li>\n<li>A \u201cnot applicable\u201d condition<\/li>\n<\/ul>\n<\/li>\n<li>Can create problems when functions such as COUNT, AVERAGE and SUM are used<\/li>\n<li>Can create logical problems when relational tables are linked<\/li>\n<\/ul>\n<p>NOTE: The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions that\u00a0ignore nulls).<\/p>\n<h3>Example of how null can be used<\/h3>\n<p>Use the Salary table (Salary_tbl) in Figure 8.6 to follow an example of how null can be used.<\/p>\n<figure id=\"attachment_50\" aria-describedby=\"caption-attachment-50\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132.jpg\" alt=\"SalaryTable-300x132\" width=\"300\" height=\"132\" class=\"wp-image-50 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132-65x28.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/SalaryTable-300x132-225x99.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-50\" class=\"wp-caption-text\">Figure 8.6. Salary table for null example, by A. Watt.<\/figcaption><\/figure>\n<p>To begin, find all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000.<\/p>\n<ul>\n<li>SELECT emp# FROM Salary_tbl<\/li>\n<li>WHERE \u00a0jobName = Sales AND<\/li>\n<li>(commission + salary) &gt; 30,000 \u00a0\u2013&gt; E10 and E12<\/li>\n<\/ul>\n<p>This result does not include E13 because of the null value in \u00a0the commission column. To ensure that the row with the null value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below.<\/p>\n<ul>\n<li>SELECT emp# FROM Salary_tbl<\/li>\n<li>WHERE jobName = Sales AND<\/li>\n<li>(commission &gt; 30000 OR<\/li>\n<li>salary &gt; 30000 OR<\/li>\n<li>(commission + salary) &gt; 30,000 \u00a0\u2013&gt;E10 and E12 and E13<\/li>\n<\/ul>\n<h2>Relationships<\/h2>\n<p><em>Relationships<\/em> are the glue that holds the tables together. They are used to connect related information<span> between <\/span>tables.<\/p>\n<p><em>Relationship strength<\/em> is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. Company database examples include:<\/p>\n<ul>\n<li><span>Customer(<strong><span style=\"text-decoration: underline;\">CustID<\/span><\/strong>, CustName)<\/span><\/li>\n<li><span>Order(<strong><span style=\"text-decoration: underline;\">OrderID<\/span><\/strong>, CustID, Date)<\/span><\/li>\n<\/ul>\n<p><span>A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include:<\/span><\/p>\n<ul>\n<li><span>Course(<strong><span style=\"text-decoration: underline;\">CrsCode<\/span><\/strong>, DeptCode, Description)<\/span><\/li>\n<li><span>Class(<strong><span style=\"text-decoration: underline;\">CrsCode, Section<\/span><\/strong>, ClassTime\u2026)<\/span><\/li>\n<\/ul>\n<h2>Types of Relationships<\/h2>\n<p>Below are descriptions of the various types of relationships.<\/p>\n<h3><span>One to many <\/span>(1:M) relationship<\/h3>\n<p><span style=\"color: #ff0000;\"><span style=\"color: #333333;\">A one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure 8.7 shows the relationship of one of these employees to the department.<\/span><\/span><\/p>\n<figure id=\"attachment_26\" aria-describedby=\"caption-attachment-26\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/FileBased-300x170.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"137\" class=\"wp-image-26\" alt=\"A light blue diamond in the middle connected on either side to a blue rectangle. The rectangle on the left says EMPLOYEE and is connected with a line to five yellow ovals with the words Birthdate, Name, Address, Salary, EID. The diamond is also connected to a blue rectangle on its right with the word DEPARTMENT and that is connected with lines to three yellow ovals with the words Name, Office, DeptID.\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/One-to-Many-Relationships-300x137.jpg\" \/><\/a><figcaption id=\"caption-attachment-26\" class=\"wp-caption-text\">Figure 8.7. Example of a one to many relationship.<\/figcaption><\/figure>\n<h3><span>One to one<\/span> (1:1) relationship<\/h3>\n<p>A one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table.<\/p>\n<p>An example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee.<\/p>\n<h3><span>Many to many\u00a0<\/span>(M:N) relationships<\/h3>\n<p><span>For a many to many relationship, consider the following points:<\/span><\/p>\n<ul>\n<li>It cannot be implemented as such in the relational model.<\/li>\n<li>It can be changed into two 1:M relationships.<\/li>\n<li>It can be implemented by breaking up to produce a set of 1:M relationships.<\/li>\n<li><span>It involves the<\/span> implementation of a composite entity.<\/li>\n<li>Creates two or more 1:M relationships.<\/li>\n<li>The composite entity table must contain at least the primary keys of the original tables.<\/li>\n<li>The linking table contains multiple occurrences of the foreign key values.<\/li>\n<li>Additional attributes may be assigned as needed.<\/li>\n<li>It can avoid problems inherent in an\u00a0M:N relationship by creating a composite entity or bridge entity. For example, an employee can work on many projects OR a project can have many employees working on it, depending on the business rules. Or,\u00a0a student can have many classes and a class can hold many students.<\/li>\n<\/ul>\n<p>Figure 8.8 shows another another aspect of the M:N relationship where an employee has different start dates for different projects<span style=\"color: #000000;\">. Therefore, we need a JOIN table that contains the EID, Code and StartDate.<\/span><\/p>\n<figure id=\"attachment_51\" aria-describedby=\"caption-attachment-51\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/One-to-Many-Relationships-300x137.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"167\" class=\"wp-image-51\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Many-to-Many-Relationships-300x167.jpg\" alt=\"image\" \/><\/a><figcaption id=\"caption-attachment-51\" class=\"wp-caption-text\">Figure 8.8. Example where employee has different start dates for different projects.<\/figcaption><\/figure>\n<p><span style=\"color: #333333;\"><strong>Example of mapping\u00a0an M:N binary relationship type<\/strong><\/span><\/p>\n<ul>\n<li>For each M:N binary relationship, identify two relations.<\/li>\n<li>A and B represent two entity types participating in R.<\/li>\n<li>Create a new relation S to represent R.<\/li>\n<li><span style=\"color: #333333;\">S needs to contain the PKs of A and B. These together can be the PK in the S table OR these together with another simple attribute in the new table R can be the PK.\u00a0<\/span><\/li>\n<li>The combination of the primary keys (A and B) will make the primary key of S.<\/li>\n<\/ul>\n<h3>Unary relationship (recursive)<\/h3>\n<p>A <em>unary relationship, <\/em>also called <em>recursive, <\/em> is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with\u00a0different roles. See Figure 8.9 for an example.<\/p>\n<p><span style=\"color: #333333;\">For some entities in a unary relationship<\/span>, a separate column can be created that refers to the primary key of the same entity set.<\/p>\n<figure id=\"attachment_35\" aria-describedby=\"caption-attachment-35\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Data-Abstraction-300x226.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"168\" class=\"wp-image-35\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Unary-Relationships-300x168.jpg\" alt=\"image\" \/><\/a><figcaption id=\"caption-attachment-35\" class=\"wp-caption-text\">Figure 8.9. Example of a unary relationship.<\/figcaption><\/figure>\n<h3>Ternary Relationships<\/h3>\n<p><span style=\"color: #333333;\">A <em>ternary relationship<\/em> is a relationship type that involves many to many relationships between three tables.\u00a0<\/span><\/p>\n<p>Refer to Figure 8.10 for an example of mapping a ternary relationship type. Note\u00a0<em>n-ary<\/em> means multiple tables in a relationship. (Remember, N = many.)<\/p>\n<ul>\n<li>For each n-ary (&gt; 2) relationship, create a new relation to represent the relationship.<\/li>\n<li>The primary key of the new relation is a combination of the primary keys of the participating entities that hold the N (many) side.<\/li>\n<li>In most cases of an n-ary relationship, all the participating entities hold a <strong>many<\/strong> side.<\/li>\n<\/ul>\n<figure id=\"attachment_54\" aria-describedby=\"caption-attachment-54\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197.jpg\" alt=\"Ternary-Mapping-Relationships-300x197\" width=\"300\" height=\"197\" class=\"wp-image-54 size-full\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ternary-Mapping-Relationships-300x197-225x147.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-54\" class=\"wp-caption-text\">Figure 8.10. Example of a ternary relationship.<\/figcaption><\/figure>\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\"><strong>alternate key<\/strong>: all candidate keys not chosen as the primary key<strong>candidate key<\/strong>: a simple or composite key that is unique (no two rows in a table may have the same value) and minimal (every column is necessary)<\/p>\n<p><strong>characteristic entities<\/strong>: entities that provide more information about another table<\/p>\n<p><strong>composite attributes<\/strong>: attributes that consist of a hierarchy of attributes<\/p>\n<p><strong>composite key<\/strong>: composed of two or more attributes, but it must be minimal<\/p>\n<p><strong>dependent entities<\/strong>: these entities depend on other tables for their meaning<\/p>\n<p><strong>derived attributes<\/strong>: attributes that contain values calculated from other attributes<\/p>\n<p><strong>derived entities<\/strong>: see<em> dependent entities<\/em><\/p>\n<p><strong>EID<\/strong>: <span style=\"color: #333333;\">employee identification (ID)<\/span><\/p>\n<p><strong>entity<\/strong>: a thing or\u00a0object in the real world with an independent existence that\u00a0can be differentiated from other objects<\/p>\n<p><strong>entity relationship (ER) data model<\/strong>:\u00a0<span>also called an ER schema<\/span><span>, are represented by ER diagrams. These are\u00a0well suited to data modelling for use with databases.<\/span><\/p>\n<p><strong>entity relationship schema<\/strong><span style=\"color: #000000;\">: see<em> entity relationship data model<\/em><\/span><\/p>\n<p><strong>entity set<\/strong>:a collection of entities of an entity type at a point of time<\/p>\n<p><strong>entity type<\/strong>:\u00a0a collection of similar entities<\/p>\n<p><strong>foreign key (FK)<\/strong>: an attribute in a table that references the primary key in another table <span>OR it can be null<\/span><\/p>\n<p><strong>independent entity<\/strong>: as the building blocks of a database, these entities are what other tables are based on<\/p>\n<p><strong>kernel<\/strong>: see<em> independent entity<\/em><\/p>\n<p><strong>key<\/strong>: an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set<\/p>\n<p><strong>multivalued\u00a0attributes<\/strong>: attributes that have a set of values for each entity<\/p>\n<p><strong>n-ary<\/strong>:\u00a0multiple tables in a relationship<\/p>\n<p><strong>null<\/strong>: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank<\/p>\n<p><strong>recursive relationship<\/strong>: see<em> unary relationship<\/em><\/p>\n<p><strong>relationships<\/strong>:\u00a0the associations or interactions between entities; used to connect related information between tables<\/p>\n<p><strong>relationship strength<\/strong>:\u00a0 based on how the primary key of a related entity is defined<\/p>\n<p><strong>secondary key<\/strong><span>\u00a0an attribute used strictly for retrieval purposes\u00a0<\/span><\/p>\n<p><strong>simple attributes<\/strong>: drawn from the atomic value domains<\/p>\n<p><strong>SIN<\/strong>: social insurance number<\/p>\n<p><strong>single-valued attributes<\/strong>: see<em> simple attributes<\/em><\/p>\n<p><strong>stored attribute<\/strong>: saved physically to the database<\/p>\n<p><strong>ternary relationship<\/strong>:\u00a0a relationship type that involves many to many relationships between three tables.<\/p>\n<p><strong>unary relationship<\/strong>: one in which a relationship exists between occurrences of the same entity set.<\/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>What two concepts are ER modelling based on?<\/li>\n<li>The database in Figure 8.11 is composed of two tables. Use this figure to answer questions 2.1 to 2.5.<br \/>\n<figure id=\"attachment_251\" aria-describedby=\"caption-attachment-251\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"286\" class=\"wp-image-251\" alt=\"Ch8-Exercises -Fig8-1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109.jpg\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109.jpg 861w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109-300x214.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109-65x46.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109-225x160.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-1-e1409188616109-350x250.jpg 350w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-251\" class=\"wp-caption-text\">Figure 8.11. Director and Play tables\u00a0for question 2, by A. Watt.<\/figcaption><\/figure>\n<ol>\n<li>Identify the primary key for each table.<\/li>\n<li>Identify the foreign key in the PLAY table.<\/li>\n<li>Identify the candidate keys in both tables.<\/li>\n<li>Draw the ER model.<\/li>\n<li>Does the PLAY table exhibit referential integrity? Why or why not?<\/li>\n<\/ol>\n<\/li>\n<li>Define the following terms (you may need to use the Internet for some of these):<br \/>\nschema<br \/>\nhost language<br \/>\ndata sublanguage<br \/>\ndata definition language<br \/>\nunary relation<br \/>\nforeign key<br \/>\nvirtual relation<br \/>\nconnectivity<br \/>\ncomposite key<br \/>\nlinking table<\/li>\n<li>The RRE Trucking Company database includes the three tables \u00a0in Figure 8.12. Use Figure 8.12 to answer questions 4.1 to 4.5.<br \/>\n<figure id=\"attachment_252\" aria-describedby=\"caption-attachment-252\" style=\"width: 450px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"230\" class=\"wp-image-252\" alt=\"Ch8-Exercises -Fig8-2\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961.jpg\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961.jpg 1207w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961-300x153.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961-1024x524.jpg 1024w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961-65x33.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961-225x115.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-2-e1409189722961-350x179.jpg 350w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/a><figcaption id=\"caption-attachment-252\" class=\"wp-caption-text\">Figure 8.12. Truck, Base and Type tables for question 4, by A. Watt.<\/figcaption><\/figure>\n<ol>\n<li>Identify the primary and foreign key(s) for each table.<\/li>\n<li>Does the TRUCK table exhibit entity and referential integrity? Why or why not? Explain your answer.<\/li>\n<li>What kind of relationship exists between the TRUCK and BASE tables?<\/li>\n<li>How many entities does the TRUCK table contain ?<\/li>\n<li>Identify the TRUCK table candidate key(s).<br \/>\n<figure id=\"attachment_253\" aria-describedby=\"caption-attachment-253\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"259\" class=\"wp-image-253\" alt=\"Ch8-Exercises -Fig8-3\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204.jpg\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204.jpg 895w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204-300x194.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204-65x42.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204-225x145.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-Fig8-3-e1409189789204-350x226.jpg 350w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-253\" class=\"wp-caption-text\">Figure 8.13. Customer and BookOrders tables for question 5, by A. Watt.<\/figcaption><\/figure>\n<\/li>\n<\/ol>\n<\/li>\n<li>Suppose you are using the database\u00a0in Figure 8.13, composed of the two tables.\u00a0Use Figure 8.13 to answer questions 5.1 to 5.6.\n<ol>\n<li>Identify the primary key in each table.<\/li>\n<li>Identify the foreign key in the BookOrders table.<\/li>\n<li>Are there any candidate keys in either table?<\/li>\n<li>Draw the ER model.<\/li>\n<li>Does the BookOrders table exhibit referential integrity? Why or why not?<\/li>\n<li>Do the tables contain redundant data? If so which table(s) and what is the redundant data?<\/li>\n<\/ol>\n<\/li>\n<li>Looking at the student table in Figure 8.14, list all the possible candidate keys. Why did you select these?<br \/>\n<figure id=\"attachment_250\" aria-describedby=\"caption-attachment-250\" style=\"width: 150px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"150\" height=\"255\" class=\"wp-image-250\" alt=\"Ch8-Exercises - No6 Student\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student.jpg\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student.jpg 337w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student-176x300.jpg 176w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student-65x110.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No6-Student-225x382.jpg 225w\" sizes=\"auto, (max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-250\" class=\"wp-caption-text\">Figure 8.14. Student table for question 6, by A. Watt.<\/figcaption><\/figure>\n<figure id=\"attachment_254\" aria-describedby=\"caption-attachment-254\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"250\" class=\"wp-image-254\" alt=\"Ch8-Exercises -No7 School Database\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-1024x640.jpg\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-1024x640.jpg 1024w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-300x187.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-65x40.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-225x140.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database-350x219.jpg 350w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch8-Exercises-No7-School-Database.jpg 1294w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-254\" class=\"wp-caption-text\">Figure 8.15. ERD of school database for questions 7-10, by A. Watt.<\/figcaption><\/figure>\n<p>Use the ERD\u00a0of a school database in Figure 8.15 to answer questions 7 to 10.<\/li>\n<li>Identity all the kernels and dependent and characteristic entities in the ERD.<\/li>\n<li>Which of the tables contribute to weak relationships? Strong relationships?<\/li>\n<li>Looking at each of the tables in the school database in Figure 8.15, which attribute could have a NULL value?\u00a0 Why?<\/li>\n<li>Which of the tables were created as a result of many to many relationships?<\/li>\n<\/ol>\n<p style=\"color: #1f1f1d;\"><strong>Also see<\/strong> <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 otherwisse noted) is a derivative copy of\u00a0<a href=\"http:\/\/cnx.org\/contents\/68cd9551-e453-420e-a906-d8e7e71260bb@1\">Data Modeling Using Entity-Relationship Model<\/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>Nulls section and example<\/li>\n<li>Key Terms<\/li>\n<li>Exercises<\/li>\n<\/ol>\n","protected":false},"author":1,"menu_order":8,"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-55","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/55","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\/55\/revisions"}],"predecessor-version":[{"id":1145,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/55\/revisions\/1145"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/55\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=55"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=55"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=55"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}