{"id":1033,"date":"2025-01-12T21:07:17","date_gmt":"2025-01-13T02:07:17","guid":{"rendered":"https:\/\/pressbooks.bccampus.ca\/nelson\/chapter\/chapter-5-the-relational-data-model-and-the-entity-relationship-diagram-erd\/"},"modified":"2025-01-12T21:23:07","modified_gmt":"2025-01-13T02:23:07","slug":"chapter05thirdedition","status":"publish","type":"chapter","link":"https:\/\/pressbooks.bccampus.ca\/nelson\/chapter\/chapter05thirdedition\/","title":{"raw":"Chapter 5 The Relational Data Model and the Entity Relationship Diagram (ERD)","rendered":"Chapter 5 The Relational Data Model and the Entity Relationship Diagram (ERD)"},"content":{"raw":"<div class=\"chapter-5-the-relational-data-model-and-the-entity-relationship-diagram-(erd)\">\r\n<p class=\"import-Normal\">Original Chapter 7 Author: Adrienne Watt<\/p>\r\n<p class=\"import-Normal\">Original Chapter 8 Author: Adrienne Watt<\/p>\r\n<p class=\"import-Normal\">Rewrite: Fred Strickland<\/p>\r\n\r\n<h2>Learning Outcomes<\/h2>\r\n<table>\r\n<tbody>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>Computing Sub Discipline<\/strong><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>Document <\/strong><strong>C<\/strong><strong>ode, Reference Code, and Page Number<\/strong><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>Text<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\" style=\"height: 107.9pt\">\r\n<td class=\"TableGrid-C\" style=\"background-color: transparent;border: solid windowtext 0.5pt\" rowspan=\"3\">\r\n<p class=\"import-Normal\">Computer Science<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">CS2013<\/p>\r\n<p class=\"import-Normal\">IM\/Relational Databases (RD)<\/p>\r\n<p class=\"import-Normal\">(Pages 115-116)<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">1. Prepare a relational schema from a conceptual model developed using the entity- relationship model. [Usage]<\/p>\r\n<p class=\"import-Normal\">Repeated learning outcome.<\/p>\r\n<p class=\"import-Normal\">9. Determine whether a set of attributes form a superkey and\/or candidate key for a relation with given functional dependencies. [Assessment]<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\" style=\"height: 27.75pt\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">CS2023<\/p>\r\n<p class=\"import-Normal\">DM Modeling: Data Modeling<\/p>\r\n<p class=\"import-Normal\">(Pages 116-117)<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">CS Core 2. Relational data model.<\/p>\r\n<p class=\"import-Normal\">KA Core 3. Conceptual models (e.g., entity-relationship, UML diagrams)<\/p>\r\n<p class=\"import-Normal\">ILO CS Core 1. Describe the components of the relational data model.<\/p>\r\n<p class=\"import-Normal\">ILO CS Core 3. Describe the components of the E-R (or some other non-relational) data model.<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\" style=\"height: 27.75pt\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">CS2023<\/p>\r\n<p class=\"import-Normal\">DM Relational: Relational Databases<\/p>\r\n<p class=\"import-Normal\">(Pages 117-118)<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">CS Core 2. Relational database design.<\/p>\r\n<p class=\"import-Normal\">KA Core 3. Mapping conceptual schema to a relational schema<\/p>\r\n<p class=\"import-Normal\">ILO CS Core 1. Describe the defining characteristics behind the relational data model.<\/p>\r\n<p class=\"import-Normal\">ILO CS Core 2. Comment on the difference between a foreign key and a superkey.<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">Information Systems<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">IS2020<\/p>\r\n<p class=\"import-Normal\">A3.2 Data \/ Information Competency Realm<\/p>\r\n<p class=\"import-Normal\">(Page 101)<\/p>\r\n<p class=\"import-Normal\">A3.2.1 Competency Area \u2013 Data \/ Information Management<\/p>\r\n<p class=\"import-Normal\">(Page 101)<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">2. Design relational databases (Page 103)<\/p>\r\n<p class=\"import-Normal\">Repeated learning outcome<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\" style=\"height: 48.55pt\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">Information Technology<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">IT2017<\/p>\r\n<p class=\"import-Normal\">ITE-IMA-03 Data modeling<\/p>\r\n<p class=\"import-Normal\">(Page 92)<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">a. Design Entity Relationship diagrams based on appropriate organizational rules for a given scenario.<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<h2>Introduction to Chapter 5<\/h2>\r\n<p class=\"import-Normal\">In Chapter 4, we began to look at the three-model approach for creating a database. This was at a high level with some relational database management system (DBMS) examples. We did not cover everything in the logical data model. We will continue in this chapter.<\/p>\r\n<p class=\"import-Normal\">The second edition Chapter 7 was a short chapter that explored at a high level the relational data model. The second edition Chapter 8 explored the entity relationship data model. For the third edition, these two chapters have been combined.<\/p>\r\n\r\n<h2>Background on the Relational Data Model<\/h2>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image1-1.png\" alt=\"image\" width=\"442.974173228346px\" height=\"436.093018372703px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.1 Image of Edgar Frank \u201cTed\u201d Codd. Source of image: https:\/\/studymate.tutorialathome.in\/postimg\/e-f-codd.jpg<\/p>\r\n<p class=\"import-Normal\">The relational data model was introduced by Edgar Frank \u201cTed\u201d Codd in 1970. Currently, it is the most widely used data model.<\/p>\r\n<p class=\"import-Normal\">The relational model has provided the basis for:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">Research on the theory of data\/relationship\/constraint<\/li>\r\n \t<li class=\"import-Normal\">Numerous database design methodologies<\/li>\r\n \t<li class=\"import-Normal\">The standard database access language called s<em>tructured query language (SQL)<\/em><\/li>\r\n \t<li class=\"import-Normal\">Almost all modern commercial database management systems<\/li>\r\n<\/ul>\r\n<p class=\"import-Normal\">The relational data model describes the world as \u201ca collection of inter-related relations (or tables).\u201d<\/p>\r\n\r\n<h2>Fundamental Concepts in the Relational Data Model<\/h2>\r\n<h3>Relation<\/h3>\r\n<p class=\"import-Normal\">Formally, a <em>relation<\/em> is a subset of the Cartesian product of a list of domains characterized by a name. Informally, this is a relation between two entities. This is also known as a <em>table<\/em> or <em>file<\/em>. And within a table, each <em>row<\/em> represents a group of related data values. A <em>row<\/em>, or <em>record<\/em>, is also known as a <em>tuple<\/em>. A <em>column<\/em> is a field and is also referred to as an attribute. A <em>domain<\/em> is the set of allowable values for a column.<\/p>\r\n<p class=\"import-Normal\">You can also think of it this way: An attribute is used to define the record and a record contains a set of attributes. The permitted or legal values are listed in the domain.<\/p>\r\n<p class=\"import-Normal\">The above can be formally expressed as follows:<\/p>\r\n\r\n<ol>\r\n \t<li class=\"import-Normal\">Given <em>n<\/em> domains are denoted by D1, D2, \u2026 Dn<\/li>\r\n \t<li class=\"import-Normal\">And <em>r<\/em> is a relation defined on these domains.<\/li>\r\n \t<li class=\"import-Normal\">Then r \u2286 D1\u00d7D2\u00d7\u2026\u00d7Dn<\/li>\r\n<\/ol>\r\n<p class=\"import-Normal\">Formal notations are used as common language when users come from different backgrounds. You can think about languages that are used for diplomacy or for business such as Latin, French, and English. Now to examine each term in greater detail.<\/p>\r\n\r\n<h3>Table<\/h3>\r\n<p class=\"import-Normal\">A database is composed of multiple tables and each table holds the data. Figure 7.2 shows a database that contains three tables.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image2-1.jpeg\" alt=\"image\" width=\"300px\" height=\"221px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.2. Database with three tables. Source of image: Second Edition Figure 7.2<\/p>\r\n\r\n<h3>Column<\/h3>\r\n<p class=\"import-Normal\">The principal storage units are called <em>columns<\/em> or <em>field<\/em> or <em>attributes<\/em>. These house the basic components of data into which your content can be broken down.<\/p>\r\n<p class=\"import-Normal\">When deciding which fields to create, you need to think generically about your information. For example, drawing out the common components of the data that you will store in the database and avoiding the specifics that distinguish one item from another. Look at the example of an ID card in Figure 7.3 to see the relationship between a field and its data.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image3-1.jpeg\" alt=\"image\" width=\"300px\" height=\"177px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.3. Example of an ID card by A. Watt. Source of image: Second Edition Figure 7.3<\/p>\r\n\r\n<h3>Domain<\/h3>\r\n<p class=\"import-Normal\">A <em>domain<\/em> is the original sets of atomic values used to model data. By <em>atomic value<\/em>, we mean that each value in the domain is indivisible as far as the relational model is concerned. For example:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">The domain of Marital Status has a set of possibilities: Married, Single, Divorced.<\/li>\r\n \t<li class=\"import-Normal\">The domain of Workdays has the set of all possible days: {Mon, Tue, Wed\u2026}.<\/li>\r\n \t<li class=\"import-Normal\">The domain of Salary is the set of all floating-point numbers greater than 0 and less than 200,000.<\/li>\r\n \t<li class=\"import-Normal\">The domain of First Name is the set of character strings that represents names of people.<\/li>\r\n<\/ul>\r\n<p class=\"import-Normal\">In summary, a domain is a set of acceptable values that a column is allowed to contain. This is based on various properties and the data type for the column. We will discuss data types in another chapter.<\/p>\r\n\r\n<h3>Records<\/h3>\r\n<p class=\"import-Normal\">Just as the content of a document or item needs to be broken down into its constituent bits of data for storage in the fields, the link between these also needs to be available so that the individual record can be reconstituted into the original form. Working with records allow us to do this. <em>Records<\/em> contain fields that are related, such as a customer or an employee. As noted earlier, a tuple is another term used for record.<\/p>\r\n<p class=\"import-Normal\">Records and fields form the basis of all databases. A simple table gives us the clearest picture of how records and fields work together in a database.<\/p>\r\n\r\n<table>\r\n<tbody>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>RecordID<\/strong><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>PubDate<\/strong><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>Author<\/strong><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><strong>Title<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">1<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">26\/07\/1968<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">B. Pitt<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">Rights and Wrongs Online<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">2<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">3\/5\/2000<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">A. Jolie<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">Networking for Change<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">3<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">27\/02\/1971<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">J. Carter<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">The Myth of Cyber Crimes<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">4<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">15\/09\/1983<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">I. Wheaton<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\">Connecting the Disconnected<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<p class=\"import-Normal\">Figure 5.4. Example of a simple table by A. Watt. (Revised by F. Strickland)<\/p>\r\n<p class=\"import-Normal\">Figure 5.4 shows a table with fields for holding data. This one has:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">The attribute name is unique in a table. These are in the row with light blue.<\/li>\r\n \t<li class=\"import-Normal\">Below the attribute name are cells containing the values.<\/li>\r\n \t<li class=\"import-Normal\">A row contains cells that are related in some fashion. This is sometimes called a <em>tuple.<\/em><\/li>\r\n \t<li class=\"import-Normal\">The RecordID field contains whole numbers. The data type is an integer.<\/li>\r\n \t<li class=\"import-Normal\">The PubDate field contains dates that are expressed in day\/month\/year format. The data type is date.<\/li>\r\n \t<li class=\"import-Normal\">The Author field contains strings that are displayed in first initial and surname format. The data type is text.<\/li>\r\n \t<li class=\"import-Normal\">The Title field contains strings that are free text. The data type is text.<\/li>\r\n<\/ul>\r\n<h3>Degree<\/h3>\r\n<p class=\"import-Normal\">The <em>degree<\/em> is the number of attributes in a table. In Figure 5.4, the degree is 4.<\/p>\r\n\r\n<h2>Properties of a Table<\/h2>\r\n<p class=\"import-Normal\">A database table has some properties:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">A table has a name that is distinct from all other tables in the database.\r\n<ul>\r\n \t<li class=\"import-Normal\">It is possible to have two databases and each one has a table with the same name.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">There are no duplicated rows.\r\n<ul>\r\n \t<li class=\"import-Normal\">According to database design best practices, a database table should not contain duplicated rows. To ensure that duplicated rows do not happen, primary keys are used.<\/li>\r\n \t<li class=\"import-Normal\">Duplicate rows could happen in\r\n<ul>\r\n \t<li class=\"import-Normal\">A staging table.<\/li>\r\n \t<li class=\"import-Normal\">A table that does not have a primary key.<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">Entries in columns are <em>atomic<\/em> or cannot be broken down further.\r\n<ul>\r\n \t<li class=\"import-Normal\">The table does not contain repeating groups or multivalued attributes.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">Entries in a column are from the same domain based on their data type such as:\r\n<ul>\r\n \t<li class=\"import-Normal\">number (numeric, integer, float, smallint,\u2026)<\/li>\r\n \t<li class=\"import-Normal\">character (string)<\/li>\r\n \t<li class=\"import-Normal\">date<\/li>\r\n \t<li class=\"import-Normal\">logical (true or false)<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">Operations combining different data types are disallowed.\r\n<ul>\r\n \t<li class=\"import-Normal\">This is possible if a <em>cast <\/em><em>function<\/em> is used. This converts a value of one data type to another data type.\r\n<ul>\r\n \t<li class=\"import-Normal\">For example, you wish to convert the string value of \u201c2\u201d to an integer so you an add it to an integer. CAST (\u201c2\u201d AS int) + 2 would be 4 instead of \u201c22.\u201d<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">Each attribute has a distinct name or unique name in a database table.\r\n<ul>\r\n \t<li class=\"import-Normal\">It is possible to have two data tables and each one has a column with the same name.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">The sequence or ordering of the columns is insignificant.<\/li>\r\n \t<li class=\"import-Normal\">The sequence or ordering of the rows is insignificant.<\/li>\r\n<\/ul>\r\n<h2>Background on the Entity-Relationship Model<\/h2>\r\n<p class=\"import-Normal\">In 1976, Peter Chen introduced the <em>entity<\/em><em>-<\/em><em>relationship <\/em><em>(ER<\/em><sup class=\"import-FootnoteReference\"><em><a id=\"sdfootnote1anc\" href=\"#sdfootnote1sym\">1<\/a><\/em><\/sup><em>) <\/em><em>model<\/em>. In his paper, he wrote about the model being a tool for designing databases. His approach would support data integrity, information retrieval, and data manipulation. The model draws upon the network model, the relational model, and the entity set model. In the introduction to his paper, he noted the strengths and the weaknesses of these three models. In his own words, he wrote:<\/p>\r\n\r\n<ul>\r\n \t<li>The network model provides a more natural view of data by separating entities and relationships (to a certain extent), but its capability to achieve data independence has been challenged.<\/li>\r\n \t<li>The relational model is based on relational theory and can achieve a high degree of data independence, but it may lose some important semantic information about the real world.<\/li>\r\n \t<li>The entity set model, which is based on set theory, also achieves a high degree of data independence, but its viewing of values such as \u201c3\u201d or \u201cred\u201d may not be natural to some people.<\/li>\r\n<\/ul>\r\n<p class=\"import-Normal\">Chen\u2019s model has a more natural view of the real world\u2014a world of entities and relationships. His model has a high degree of data independence. It is based on set theory and relation theory. Chen introduced a special diagramming technique for working on database designs that he called the <em>entity-relationship diagram (ERD<\/em>). (See the references for two links to the complete Peter Chen paper.)<\/p>\r\n\r\n<h2>Entities, Relationships, and Attributes<\/h2>\r\n<p class=\"import-Normal\">Chen focused on three parts:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\"><em>Entities<\/em> as something that \u201cexists in our minds.\u201d An entity could be a <em>tangible type<\/em> or could be a <em>non-tangible type<\/em>. These can be classified into different <em>entity sets<\/em>.\r\n<ul>\r\n \t<li class=\"import-Normal\">Employee, Project, and Department<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote2anc\" href=\"#sdfootnote2sym\">2<\/a><\/sup><\/li>\r\n \t<li class=\"import-Normal\">Male_Person<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote3anc\" href=\"#sdfootnote3sym\">3<\/a><\/sup> is a subset of Person.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\"><em>Relationships<\/em> as an \u201cassociations among entities<em>.<\/em><em>\u201d<\/em>\r\n<ul>\r\n \t<li class=\"import-Normal\">A marriage is a relationship between two entities in the entity set Person.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\"><em>Attribute<\/em><em>s<\/em> as an \u201cinformation about an entity or a relationship\u2026<em>.<\/em><em>\u201d<\/em>\r\n<ul>\r\n \t<li class=\"import-Normal\">\u201c3\u201d, \u201cred\u201d, \u201cPeter\u201d, and \u201cJohnson\u201d are values.<\/li>\r\n \t<li class=\"import-Normal\">\u201cValues are classified into different value sets, such as FEET, COLOR, FIRST-NAME, and LAST-NAME.\u201d<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<h2>Illustrating the ER Model and the ERD<\/h2>\r\n<p class=\"import-Normal\">For the rest of this chapter, we will use a sample database (Company) to illustrate the ER model and the ERD. We will be using the following symbols:<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image4-2.png\" alt=\"image\" width=\"624px\" height=\"344.466666666667px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.5 Symbols used in an ER Diagram. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\r\n<p class=\"import-Normal\">The Company database contains data about employees, departments, and projects. The ERD symbol for these is a rectangle:<\/p>\r\n\r\n<table>\r\n<tbody>\r\n<tr class=\"TableGrid-R\">\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\" style=\"text-align: center\">Employee<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border-top: none windowtext 0pt;border-bottom: none windowtext 0pt;border-right: solid windowtext 0.5pt;border-left: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\" style=\"text-align: center\">Department<\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border-top: none windowtext 0pt;border-bottom: none windowtext 0pt;border-right: solid windowtext 0.5pt;border-left: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\"><\/p>\r\n<\/td>\r\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\r\n<p class=\"import-Normal\" style=\"text-align: center\">Project<\/p>\r\n<\/td>\r\n<\/tr>\r\n<tr>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<td><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<p class=\"import-Normal\">Figure 5.6 ERD for the three Company entities.<\/p>\r\n<p class=\"import-Normal\">We need to note the following important points about the Company database:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">There are several departments in the company. Each department has a unique identification, a name, an office location, and a particular employee who manages the department.<\/li>\r\n \t<li class=\"import-Normal\">A department controls a number of projects. Each project has a unique name, a unique number, and a budget.<\/li>\r\n \t<li class=\"import-Normal\">Each employee has a name, an identification number (ID), an address, a salary, and a birthdate.\r\n<ul>\r\n \t<li class=\"import-Normal\">An employee is assigned to one department.<\/li>\r\n \t<li class=\"import-Normal\">An employee reports to one direct supervisor.<\/li>\r\n \t<li class=\"import-Normal\">An employee could be a part of several projects.\r\n<ul>\r\n \t<li class=\"import-Normal\">We need to record the start date of the employee in each project.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">An employee may have dependents.<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li class=\"import-Normal\">Each dependent has a name, a birthdate, and a relationship to the employee.<\/li>\r\n<\/ul>\r\n<h3>Entity Existence Dependency<\/h3>\r\n<p class=\"import-Normal\">Entities can be classified based on strength. An entity is considered strong if it can exist apart from all related entities. An entity is considered weak if its existence depends upon a corresponding entity in another entity set. Chen used the phrase \u201cexistence dependency\u201d to describe this relationship. In Chen\u2019s own words: \u201c\u2026 [the] existence of an entity in the entity set D[ependent] depends on the corresponding entity in the entity set E[mployee]. That is, if an employee leaves the company, his dependents may no longer be of interest.\u201d Figure 5.7 illustrates a strong entity and a weak entity.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image5-2.png\" alt=\"image\" width=\"624px\" height=\"103.066666666667px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.7 Strong entity on the left-hand side and a weak entity on the right-hand side.<\/p>\r\n\r\n<h3>Relationship Type and Degree of a Relationship Set<\/h3>\r\n<p class=\"import-Normal\">A <em>relationship type<\/em> represents the association between entity types. For example, in Figure 5.8 \u201cEnrolled in\u201d is a relationship type that exists between entity type \u201cStudent\u201d and entity type \u201cCourse.\u201d In the ERD, the relationship type is represented by a diamond. Lines are used to connect the entity types and the relationship types. Figure 5.8 shows two strong entities. Figure 5.9 shows a strong entity with a weak entity.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image6-2.png\" alt=\"image\" width=\"624px\" height=\"126.466666666667px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.8 Relation type with two strong entities. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image7-2.png\" alt=\"image\" width=\"624px\" height=\"131px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.9 Strong entity and a weak entity.<\/p>\r\n<p class=\"import-Normal\">Geeks for Geeks explained relationship set as \u201ca set of relationships of the same type\u201d The website has an illustration, but it does not clearly explain the definition. The concept of a <em>degree of a relationship set<\/em> does clear up the confusion. This is the number of different entity sets participating in a relationship set.<\/p>\r\n\r\n<h3>Degree of a Relationship Set: Unary<\/h3>\r\n<p class=\"import-Normal\">When there is only one entity set participating in a relation, the relationship is called a <em>unary relationship<\/em>. For example, one person is married to only one person.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image8.png\" alt=\"image\" width=\"569.649343832021px\" height=\"202.417532808399px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.10 An example of a person being married to a person. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\r\n<p class=\"import-Normal\">A variation of Figure 5.10 would not be used in the Company database. The exception would be if the company had a business rule that it hires only husbands and wives as a team.<\/p>\r\n<p class=\"import-Normal\">There is a <em>recursive unary relationship<\/em>. This is when there is a relationship between members of the same entity set. For example, an employee is a supervisor of another employee. Figure 5.11 illustrates this relationship. In the actual Employee table, it might be necessary to add a column for the supervisor\u2019s ID.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image9.jpeg\" alt=\"image\" width=\"300px\" height=\"168px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.11 Example of a unary relationship. Source of image: Second Edition Figure 8.9<\/p>\r\n\r\n<h3>Degree of a Relationship Set: Binary<\/h3>\r\n<p class=\"import-Normal\">When there are two entities set participating in a relationship, the relationship is called a binary relationship. Figure 5.12 illustrates a binary relationship with the Employee table and the Spouse table. Note how this is different from Figure 5.10, which has both individuals appearing in the same table.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image10.png\" alt=\"image\" width=\"624px\" height=\"130.6px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.12 An employee is associated with a spouse.<\/p>\r\n<p class=\"import-Normal\" style=\"text-align: justify\">The Geeks for Geeks website used an example from education. See Figure 5.13 for a Student being enrolled in a Course.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image11.png\" alt=\"image\" width=\"624px\" height=\"115.933333333333px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.13 An example of a binary relationship. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\r\n\r\n<h4>Degree of a Relationship Set: Ternary<\/h4>\r\n<p class=\"import-Normal\">This is when more than two entity sets participate in a relation.<\/p>\r\n\r\n<h3>Attribute Symbol<\/h3>\r\n<p class=\"import-Normal\">For an entity type, we define the properties. These are represented in ovals.<\/p>\r\n\r\n<h3>Key Attribute<\/h3>\r\n<p class=\"import-Normal\">An attribute that uniquely identifies each entity is called a <em>key attribute<\/em>. This is represented with the word underlined in an oval.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image12.png\" alt=\"image\" width=\"198.417217847769px\" height=\"69.6059842519685px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.14 An example of a key attribute.<\/p>\r\n\r\n<h3>Composite Attributes<\/h3>\r\n<p class=\"import-Normal\"><em>Composite attributes<\/em> are those that consist of a hierarchy of attributes. Using the Employee table from the Company database, the Address may consist of a Number, a Street, and a Suburb. This is represented with two or more ovals coming out of another oval. See Figure 5.15. This could be written as<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 36pt\">\u2192 Address = {59 + \u2018Meek Street\u2019 + \u2018Kingsford\u2019}<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image13.jpeg\" alt=\"image\" width=\"300px\" height=\"133px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.15. An example of composite attributes. Source of image: Second Edition Figure 8.3<\/p>\r\n\r\n<h3>Multivalued Attributes<\/h3>\r\n<p class=\"import-Normal\"><em>Multivalued<\/em> <em>attributes<\/em> are attributes that have a set of values for each entity. An example of a multivalued attribute from the Company database is illustrated in Figure 7.16. An employee could have more than one academic degree such as BSc, MIT, and PhD. This is represented as a double oval.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image14.jpeg\" alt=\"image\" width=\"300px\" height=\"131px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.16. Example of a multivalued attribute. Source of image: Second Edition Figure 8.4<\/p>\r\n\r\n<h3>Derived Attributes<\/h3>\r\n<p class=\"import-Normal\"><em>Derived <\/em><em>attributes<\/em> are attributes that are derived from other attributes. This is represented as a dotted oval in Figure 5.17.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image15.png\" alt=\"image\" width=\"198.417217847769px\" height=\"129.611233595801px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.17 Example of a derived attribute. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\r\n\r\n<h2>The Need for Keys<\/h2>\r\n<p class=\"import-Normal\">In the paragraphs on \u201ckey attribute,\u201d we defined this as something that uniquely identifies an entity. We did not cover the various types of keys nor did we explain how the primary key is selected. We will address those questions in this section.<\/p>\r\n\r\n<h2>Types of Keys<\/h2>\r\n<p class=\"import-Normal\">There are several types of keys that can be used to locate a record. Geeks for Geeks illustrate all of the possible keys (Figure 5.18):<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image16.png\" alt=\"image\" width=\"624px\" height=\"595.2px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.18 Diagram of all possible keys in a DBMS. Source of image: https:\/\/www.geeksforgeeks.org\/candidate-key-in-dbms\/<\/p>\r\n<p class=\"import-Normal\">These are described below.<\/p>\r\n\r\n<h3>Super Key<\/h3>\r\n<p class=\"import-Normal\">A <em>super key<\/em> is a set of one or more attributes (columns), which can uniquely identify a row in a table.<\/p>\r\n<p class=\"import-Normal\">Using the Employee table from the Company database example, we might have the following attributes:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID<\/li>\r\n \t<li>FirstName<\/li>\r\n \t<li>LastName<\/li>\r\n \t<li>SSN<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote4anc\" href=\"#sdfootnote4sym\">4<\/a><\/sup><\/li>\r\n \t<li>Address<\/li>\r\n \t<li>Phone<\/li>\r\n \t<li>BirthDate<\/li>\r\n \t<li>Salary<\/li>\r\n \t<li>DepartmentID<\/li>\r\n<\/ul>\r\n<p class=\"import-Normal\">The possible super keys are:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID<\/li>\r\n \t<li>FirstName (Assuming that this is unique.)<\/li>\r\n \t<li>LastName (Assuming that this is unique.)<\/li>\r\n \t<li>SSN<\/li>\r\n \t<li>Address (Assuming that this is unique.)<\/li>\r\n \t<li>Phone (Assuming that this is unique.)<\/li>\r\n \t<li>BirthDate (Assuming that this is unique.)<\/li>\r\n \t<li>Salary (Assuming that this is unique.)<\/li>\r\n \t<li>DepartmentID (Assuming that this is unique.)<\/li>\r\n \t<li>EmployeeID, FirstName<\/li>\r\n \t<li>EmployeeID, LastName<\/li>\r\n \t<li>EmployeeID, SSN<\/li>\r\n \t<li>EmployeeID, Address<\/li>\r\n \t<li>EmployeeID, Phone<\/li>\r\n \t<li>EmployeeID, BirthDate<\/li>\r\n \t<li>EmployeeID, Salary<\/li>\r\n \t<li>EmployeeID, DepartmentID<\/li>\r\n \t<li>FirstName, LastName (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, SSN<\/li>\r\n \t<li>FirstName, Address (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, SSN<\/li>\r\n \t<li>LastName, Address (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>SSN, Address<\/li>\r\n \t<li>SSN, Phone<\/li>\r\n \t<li>SSN, BirthDate<\/li>\r\n \t<li>SSN, Salary<\/li>\r\n \t<li>SSN, DepartmentID<\/li>\r\n \t<li>Address, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>BirthDate, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>BirthDate, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>Salary, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n<\/ul>\r\n<h3>Candidate Key<\/h3>\r\n<p class=\"import-Normal\">A <em>candidate key<\/em> is a simple or composite key that is unique and minimal. Another way to view a candidate key is that the minimal super key has no redundant attributes.<\/p>\r\n<p class=\"import-Normal\">The possible candidate keys are:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID<\/li>\r\n \t<li>FirstName (Assuming that this is unique.)<\/li>\r\n \t<li>LastName (Assuming that this is unique.)<\/li>\r\n \t<li>SSN<\/li>\r\n \t<li>Address (Assuming that this is unique.)<\/li>\r\n \t<li>Phone (Assuming that this is unique.)<\/li>\r\n \t<li>BirthDate (Assuming that this is unique.)<\/li>\r\n \t<li>Salary (Assuming that this is unique.)<\/li>\r\n<\/ul>\r\n<h3>Primary Key<\/h3>\r\n<p class=\"import-Normal\">A <em>primary<\/em> <em>key<\/em> is selected from the set of candidate keys. This done by the database administrator or by the database designer. More than likely one of the following would be selected:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID<\/li>\r\n \t<li>SSN<\/li>\r\n<\/ul>\r\n<h3>Alternate Key (or Secondary Key)<\/h3>\r\n<p class=\"import-Normal\">After the primary key has been selected, then the other candidate keys are the <em>alternate keys<\/em>. If EmployeeID was selected as the primary key, then SSN would be the alternate key. Or if SSN was selected as the primary key, then the EmployeeID would be the alternate key:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID<\/li>\r\n \t<li>SSN<\/li>\r\n<\/ul>\r\n<h3>Composite Key (or Compound Key)<\/h3>\r\n<p class=\"import-Normal\">A <em>composite<\/em> <em>key<\/em> (or <em>compound key<\/em>) uses more than one attribute to locate a record.<\/p>\r\n<p class=\"import-Normal\">One way to think about a composite key is when you attempt to access an account and you do not have the account number. Other pieces are used to locate your account.<\/p>\r\n<p class=\"import-Normal\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image17.png\" alt=\"image\" width=\"624px\" height=\"322.6px\" \/><\/p>\r\n<p class=\"import-Normal\">Figure 5.19 Example of how to locate an account when the number is not known. Source of image: https:\/\/s3.amazonaws.com\/cdn.freshdesk.com\/data\/helpdesk\/attachments\/production\/47043169576\/original\/Htjf1hvCQ9afJXvY2vQ54XHtVlLPsuQTGw.png?1603288395<\/p>\r\n<p class=\"import-Normal\">The possible composite keys are:<\/p>\r\n\r\n<ul>\r\n \t<li>EmployeeID, FirstName<\/li>\r\n \t<li>EmployeeID, LastName<\/li>\r\n \t<li>EmployeeID, SSN<\/li>\r\n \t<li>EmployeeID, Address<\/li>\r\n \t<li>EmployeeID, Phone<\/li>\r\n \t<li>EmployeeID, BirthDate<\/li>\r\n \t<li>EmployeeID, Salary<\/li>\r\n \t<li>EmployeeID, DepartmentID<\/li>\r\n \t<li>FirstName, LastName (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, SSN<\/li>\r\n \t<li>FirstName, Address (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>FirstName, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, SSN<\/li>\r\n \t<li>LastName, Address (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>LastName, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>SSN, Address<\/li>\r\n \t<li>SSN, Phone<\/li>\r\n \t<li>SSN, BirthDate<\/li>\r\n \t<li>SSN, Salary<\/li>\r\n \t<li>SSN, DepartmentID<\/li>\r\n \t<li>Address, Phone (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>Address, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, BirthDate (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>Phone, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>BirthDate, Salary (Assuming that the combination is unique.)<\/li>\r\n \t<li>BirthDate, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n \t<li>Salary, DepartmentID (Assuming that the combination is unique.)<\/li>\r\n<\/ul>\r\n<h3>Foreign Key<\/h3>\r\n<p class=\"import-Normal\">A <em>foreign<\/em><em> key<\/em> is used to link one table to another table. Or this is an attribute in a table that references the primary key in another table.<\/p>\r\n<p class=\"import-Normal\">This could be null. Both foreign and primary keys must be of the same data type.<\/p>\r\n<p class=\"import-Normal\">In the Company database example, DepartmentID is the foreign key in the Employee table:<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 36pt\"><strong>Employee<\/strong>(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)<\/p>\r\n\r\n<h2>Key Terms<\/h2>\r\n<p class=\"import-Normal\"><strong>alternate key (or secondary key)<\/strong><strong>: <\/strong>These are the unselected candidate keys.<\/p>\r\n<p class=\"import-Normal\"><strong>atomic values<\/strong><strong>: <\/strong>This means that the value cannot be broken down into small pieces.<\/p>\r\n<p class=\"import-Normal\"><strong>attributes<\/strong><strong>: <\/strong>This is information about an entity or about a relationship.<\/p>\r\n<p class=\"import-Normal\"><strong>cast function<\/strong><strong>: <\/strong>This is a way of converting a value to another data type.<\/p>\r\n<p class=\"import-Normal\"><strong>candidate key<\/strong><strong>: <\/strong>This is a way of converting a value to another data type.<\/p>\r\n<p class=\"import-Normal\"><strong>column<\/strong><strong>: <\/strong>This is an attribute.<\/p>\r\n<p class=\"import-Normal\"><strong>composite attribute<\/strong><strong>: <\/strong>This is an attribute that is composed of two or more pieces.<\/p>\r\n<p class=\"import-Normal\"><strong>c<\/strong><strong>omposite <\/strong><strong>key<\/strong><strong>: <\/strong>This uses more than one attribute to locate a record.<\/p>\r\n<p class=\"import-Normal\"><strong>d<\/strong><strong>egree<\/strong><strong>: <\/strong>This is the number of columns in a table.<\/p>\r\n<p class=\"import-Normal\"><strong>degree of a relationship set<\/strong><strong>: <\/strong>This is the number of different entity sets participating in a relationship set.<\/p>\r\n<p class=\"import-Normal\"><strong>d<\/strong><strong>erived attribute<\/strong><strong>: <\/strong>This is derived from other attributes.<\/p>\r\n<p class=\"import-Normal\"><strong>domain<\/strong><strong>: <\/strong>This is the set of allowable values for a column.<\/p>\r\n<p class=\"import-Normal\"><strong>entity or <\/strong><strong>entities<\/strong><strong>: <\/strong>In the words of Peter Chen, these are things that \u201cexists in our minds.\u201d An entity is an object in the real world with an independent existence that\u00a0can be differentiated from other objects.<\/p>\r\n<p class=\"import-Normal\"><strong>Entity-Relationship <\/strong><strong>Diagram<\/strong><strong>:<\/strong> Peter Chen developed this tool in support of the entity-relationship model.<\/p>\r\n<p class=\"import-Normal\"><strong>Entity-Relationship Model<\/strong><strong>:<\/strong> Peter Chen developed this model in 1976. It draws upon the network model, the relational model, and entity set model. It is a more natural view of the real world. It is based on sets theory and relational theory.<\/p>\r\n<p class=\"import-Normal\"><strong>entity set<\/strong><strong>: <\/strong>This is a collection of entities. In the words of Peter Chen, an entity \u201chas the properties common to the other entities in the entity set\u2026.\u201d<\/p>\r\n<p class=\"import-Normal\"><strong>existence dependency<\/strong>: An entity that dependents upon the existence of another entity. This is a weak relationship. If the linked entity is removed, then these dependent entities would go away.<\/p>\r\n<p class=\"import-Normal\"><strong>fi<\/strong><strong>eld<\/strong><strong>: <\/strong>See column above.<\/p>\r\n<p class=\"import-Normal\"><strong>file: <\/strong>See relation below.<\/p>\r\n<p class=\"import-Normal\"><strong>F<\/strong><strong>oreign key<\/strong><strong>:<\/strong> This is used to link from one table to another table.<\/p>\r\n<p class=\"import-Normal\"><strong>key attribute<\/strong><strong>: <\/strong>This is an attribute that uniquely identifies an entity.<\/p>\r\n<p class=\"import-Normal\"><strong>multivalued<\/strong><strong> attribute<\/strong><strong>: <\/strong>This is an attribute that could contain more than one entity.<\/p>\r\n<p class=\"import-Normal\"><strong>non-tangible type<\/strong>: This is an entity that is not physical and cannot be touched.<\/p>\r\n<p class=\"import-Normal\"><strong>primary key<\/strong>: This is selected from the set of candidate keys. Recall from Chapter 3 that this was defined as a unique value for locating a row of data in a database table.<\/p>\r\n<p class=\"import-Normal\"><strong>relation<\/strong>: Formally, this is a subset of the Cartesian product of a list of domains characterized by a name. Informally, this is a relation between two entities.<\/p>\r\n<p class=\"import-Normal\"><strong>r<\/strong><strong>elation<\/strong><strong>ship<\/strong><strong> type<\/strong>: This represents the associations between entity types.<\/p>\r\n<p class=\"import-Normal\"><strong>relation<\/strong><strong>ships<\/strong>: In the words of Peter Chen, this an \u201cassociation among entities.\u201d<\/p>\r\n<p class=\"import-Normal\"><strong>r<\/strong><strong>ecord<\/strong><strong>: <\/strong>See row below.<\/p>\r\n<p class=\"import-Normal\"><strong>recursive relationship:<\/strong> See unary relationship.<\/p>\r\n<p class=\"import-Normal\"><strong>row: <\/strong>This represents a group of related data.<\/p>\r\n<p class=\"import-Normal\"><strong>staging table: <\/strong>This is a table that is receiving data from different sources.<\/p>\r\n<p class=\"import-Normal\"><strong>s<\/strong><strong>u<\/strong><strong>per key<\/strong><strong>: <\/strong>This is a set of one or more attributes (columns), which can uniquely identify a row in a table.<\/p>\r\n<p class=\"import-Normal\"><strong>table: <\/strong>See relation above.<\/p>\r\n<p class=\"import-Normal\"><strong>tangible type<\/strong>: This is an entity that is physical or could be touched.<\/p>\r\n<p class=\"import-Normal\"><strong>t<\/strong><strong>ernary<\/strong>: This is when more than two entity sets participate in a relation.<\/p>\r\n<p class=\"import-Normal\"><strong>t<\/strong><strong>uple<\/strong><strong>: <\/strong>See row above.<\/p>\r\n<p class=\"import-Normal\"><strong>unary relationship<\/strong><strong>:<\/strong> This is one in which a relationship exists between occurrences of the same entity set.<\/p>\r\n\r\n<h2>Exercises<\/h2>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use Table 5.1 to answer the first four questions.<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use Table 5.2 to answer questions 7 through 10.<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use both tables to answer question 11.<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image18.jpeg\" alt=\"image\" width=\"600px\" height=\"112px\" \/><\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Table 5.1 Table for exercise questions, by A. Watt<\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\"><img src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image19.jpeg\" alt=\"image\" width=\"624px\" height=\"445.733333333333px\" \/><\/p>\r\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Table 5.2 Director and Play tables by A. Watt<\/p>\r\n\r\n<ol>\r\n \t<li>Using correct terminology, identify and describe all the components in Table 5.1.<\/li>\r\n \t<li>What is the possible domain for field EmpJobCode?<\/li>\r\n \t<li>How many records are shown?<\/li>\r\n \t<li>How many attributes are shown?<\/li>\r\n \t<li>List the properties of a database table.<\/li>\r\n \t<li>What three concepts or parts of the ERD?<\/li>\r\n \t<li>Identify the super keys in Table 5.2.<\/li>\r\n \t<li>Identify the candidate keys in Table 5.2. (CS2013 IM\/RD 9)<\/li>\r\n \t<li>Identify the primary keys in Table 5.2.<\/li>\r\n \t<li>Identify the foreign key in Table 5.2.<\/li>\r\n \t<li>Use Table 5.1 and Table 5.2 and create an ERD. You may need to make some assumptions. (CS2013 IM\/RD 1, IS2020 A3.2.1.2, and IT2017 ITE-IMA-03a)<\/li>\r\n \t<li>Define the following:\r\n<ol>\r\n \t<li>Relation<\/li>\r\n \t<li>Column<\/li>\r\n \t<li>Domain<\/li>\r\n \t<li>Records<\/li>\r\n \t<li>Degree<\/li>\r\n \t<li>Entities<\/li>\r\n \t<li>The ERD rectangle<\/li>\r\n \t<li>The ERD oval or ellipse<\/li>\r\n \t<li>The ERD double oval.<\/li>\r\n \t<li>The ERD double rectangle<\/li>\r\n \t<li>Weak entities<\/li>\r\n \t<li>Strong entities<\/li>\r\n \t<li>Unary degree<\/li>\r\n \t<li>Binary degree<\/li>\r\n \t<li>Ternary degree<\/li>\r\n \t<li>Key attribute<\/li>\r\n \t<li>Composite attributes<\/li>\r\n \t<li>Multivalued attributes<\/li>\r\n \t<li>Derived attributes<\/li>\r\n \t<li>Super key<\/li>\r\n \t<li>Candidate key<\/li>\r\n \t<li>Primary key<\/li>\r\n \t<li>Alternate key<\/li>\r\n \t<li>Composite key<\/li>\r\n \t<li>Foreign key<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ol>\r\n<h2>A Running Project<\/h2>\r\n<p class=\"import-Normal\">A running project was introduced in Chapter 2. You were encouraged to collect insights about the needs. In Chapter 3, you were told to use a word processor to create tables with columns for the entity pieces. In Chapter 4, you were told to identified the data types.<\/p>\r\n<p class=\"import-Normal\">In this chapter, you are to begin to create the ERD. Refer to Figure 5.5 for the symbols.<\/p>\r\n<p class=\"import-Normal\">Create a list of the possible keys. Identify the primary keys.<\/p>\r\n<p class=\"import-Normal\">Again, review what you have collected. Have you overlooked anything?<\/p>\r\n<p class=\"import-Normal\"><strong>\u00a0<\/strong><\/p>\r\n\r\n<h2>Attribution<\/h2>\r\n<p class=\"import-Normal\">This chapter of\u00a0<em>Database Design<\/em> is a derivative copy of\u00a0<a class=\"rId32\" href=\"http:\/\/cnx.org\/contents\/b57b8760-6898-469d-a0f7-06e0537f6817@1\">Database System Concepts<\/a>\u00a0by\u00a0Nguyen Kim Anh\u00a0licensed under\u00a0<a class=\"rId33\" href=\"http:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons Attribution License 3.0 license<\/a><\/p>\r\n<p class=\"import-Normal\">The following material was written by Adrienne Watt for the second edition:<\/p>\r\n\r\n<ul>\r\n \t<li class=\"import-Normal\">Introduction<\/li>\r\n \t<li class=\"import-Normal\">Key Terms<\/li>\r\n \t<li class=\"import-Normal\">Exercises<\/li>\r\n<\/ul>\r\n<p class=\"import-Normal\"><a id=\"_Hlk165870885\"><\/a>This chapter drew from chapter 7 and from chapter 8. The information was completely revised by Fred Strickland for the third edition.<\/p>\r\n\r\n<h2>References<\/h2>\r\n<p class=\"import-Normal\">Peter Chen. \u201cThe Entity-Relationship Model\u2014Toward a Unified View of Data<em>,\u201d ACM Transactions on Database Systems,<\/em> Volume 1, Number 1, March 1976. The abstract and the first draft page can be found at <a class=\"rId34\" href=\"https:\/\/dl.acm.org\/doi\/10.1145\/1095277.1095279\"><span class=\"import-Hyperlink\">https:\/\/dl.acm.org\/doi\/10.1145\/1095277.1095279<\/span><\/a> An unofficial copy of the paper is available at <a class=\"rId35\" href=\"https:\/\/dspace.mit.edu\/bitstream\/handle\/1721.1\/47432\/entityrelationshx00chen.pdf\"><span class=\"import-Hyperlink\">https:\/\/dspace.mit.edu\/bitstream\/handle\/1721.1\/47432\/entityrelationshx00chen.pdf<\/span><\/a> and at <a class=\"rId36\" href=\"https:\/\/www.comp.nus.edu.sg\/~lingtw\/papers\/tods76.chen.pdf\"><span class=\"import-Hyperlink\">https:\/\/www.comp.nus.edu.sg\/~lingtw\/papers\/tods76.chen.pdf<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">This was a groundbreaking paper, but it had some errors. For example, Figure 2 had the title of \u201cAttributes defined on the entity set PERSON,\u201d but in the drawing the entity set was named \u201cEMPLOYEE.\u201d<\/p>\r\n<p class=\"import-Normal\">Sergey Gigoyan. \u201cFind and Remove Duplicate Rows from a SQL Server Table,\u201d MSSQLTips, July 20, 2021. <a class=\"rId37\" href=\"https:\/\/www.mssqltips.com\/sqlservertip\/4486\/find-and-remove-duplicate-rows-from-a-sql-server-table\/\"><span class=\"import-Hyperlink\">https:\/\/www.mssqltips.com\/sqlservertip\/4486\/find-and-remove-duplicate-rows-from-a-sql-server-table\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">Chaitanya Singh. \u201cAlternate key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId38\" href=\"https:\/\/beginnersbook.com\/2015\/04\/alternate-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/alternate-key-in-dbms\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">Chaitanya Singh. \u201cComposite key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId39\" href=\"https:\/\/beginnersbook.com\/2015\/04\/composite-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/composite-key-in-dbms\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">Chaitanya Singh. \u201cForeign key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId40\" href=\"https:\/\/beginnersbook.com\/2015\/04\/foreign-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/foreign-key-in-dbms\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">Chaitanya Singh. \u201cSuper key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId41\" href=\"https:\/\/beginnersbook.com\/2015\/04\/super-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/super-key-in-dbms\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">\u201cIntroduction of ER Model,\u201d Geeks for Geeks, May 23, 2024. <a class=\"rId42\" href=\"https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/\"><span class=\"import-Hyperlink\">https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/span><\/a><\/p>\r\n<p class=\"import-Normal\">\u201cSQL Server CAST() Function,\u201d W3 Schools, n.d. <a class=\"rId43\" href=\"https:\/\/www.w3schools.com\/sql\/func_sqlserver_cast.asp\"><span class=\"import-Hyperlink\">https:\/\/www.w3schools.com\/sql\/func_sqlserver_cast.asp<\/span><\/a><\/p>\r\n&nbsp;\r\n<div id=\"sdfootnote1sym\"><a href=\"#sdfootnote1anc\">1<\/a> Peter Chen never abbreviated the phrases \u201centity relationship model.\u201d He always used the long form.<\/div>\r\n<div id=\"sdfootnote2sym\"><a href=\"#sdfootnote2anc\">2<\/a> In Peter Chen\u2019s paper, these three entities were written as \u201cEMPLOYEE, PROJECT, and DEPARTMENT.\u201d Most best practices websites do not use all capital letters in table names. Many websites use singular noun naming customs. The third edition has corrected the second edition examples to follow this naming custom.<\/div>\r\n<div id=\"sdfootnote3sym\"><a href=\"#sdfootnote3anc\">3<\/a> In Peter Chen\u2019s paper, this was written as MALE-PERSON. Some websites recommend using an underscore for separating words (the Snake Case). No website uses the hyphen. There are many opinions on the topic as in MalePeson (Pascal Case), malePerson (Camel Case), and Male_Person (Snake Case)<\/div>\r\n<div id=\"sdfootnote4sym\"><a href=\"#sdfootnote4anc\">4<\/a> The United States uses \u201cSSN\u201d and Canada uses \u201cSIN.\u201d<\/div>\r\n<\/div>","rendered":"<div class=\"chapter-5-the-relational-data-model-and-the-entity-relationship-diagram-(erd)\">\n<p class=\"import-Normal\">Original Chapter 7 Author: Adrienne Watt<\/p>\n<p class=\"import-Normal\">Original Chapter 8 Author: Adrienne Watt<\/p>\n<p class=\"import-Normal\">Rewrite: Fred Strickland<\/p>\n<h2>Learning Outcomes<\/h2>\n<table>\n<tbody>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>Computing Sub Discipline<\/strong><\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>Document <\/strong><strong>C<\/strong><strong>ode, Reference Code, and Page Number<\/strong><\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>Text<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\" style=\"height: 107.9pt\">\n<td class=\"TableGrid-C\" style=\"background-color: transparent;border: solid windowtext 0.5pt\" rowspan=\"3\">\n<p class=\"import-Normal\">Computer Science<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">CS2013<\/p>\n<p class=\"import-Normal\">IM\/Relational Databases (RD)<\/p>\n<p class=\"import-Normal\">(Pages 115-116)<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">1. Prepare a relational schema from a conceptual model developed using the entity- relationship model. [Usage]<\/p>\n<p class=\"import-Normal\">Repeated learning outcome.<\/p>\n<p class=\"import-Normal\">9. Determine whether a set of attributes form a superkey and\/or candidate key for a relation with given functional dependencies. [Assessment]<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\" style=\"height: 27.75pt\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">CS2023<\/p>\n<p class=\"import-Normal\">DM Modeling: Data Modeling<\/p>\n<p class=\"import-Normal\">(Pages 116-117)<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">CS Core 2. Relational data model.<\/p>\n<p class=\"import-Normal\">KA Core 3. Conceptual models (e.g., entity-relationship, UML diagrams)<\/p>\n<p class=\"import-Normal\">ILO CS Core 1. Describe the components of the relational data model.<\/p>\n<p class=\"import-Normal\">ILO CS Core 3. Describe the components of the E-R (or some other non-relational) data model.<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\" style=\"height: 27.75pt\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">CS2023<\/p>\n<p class=\"import-Normal\">DM Relational: Relational Databases<\/p>\n<p class=\"import-Normal\">(Pages 117-118)<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">CS Core 2. Relational database design.<\/p>\n<p class=\"import-Normal\">KA Core 3. Mapping conceptual schema to a relational schema<\/p>\n<p class=\"import-Normal\">ILO CS Core 1. Describe the defining characteristics behind the relational data model.<\/p>\n<p class=\"import-Normal\">ILO CS Core 2. Comment on the difference between a foreign key and a superkey.<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">Information Systems<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">IS2020<\/p>\n<p class=\"import-Normal\">A3.2 Data \/ Information Competency Realm<\/p>\n<p class=\"import-Normal\">(Page 101)<\/p>\n<p class=\"import-Normal\">A3.2.1 Competency Area \u2013 Data \/ Information Management<\/p>\n<p class=\"import-Normal\">(Page 101)<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">2. Design relational databases (Page 103)<\/p>\n<p class=\"import-Normal\">Repeated learning outcome<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\" style=\"height: 48.55pt\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">Information Technology<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">IT2017<\/p>\n<p class=\"import-Normal\">ITE-IMA-03 Data modeling<\/p>\n<p class=\"import-Normal\">(Page 92)<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">a. Design Entity Relationship diagrams based on appropriate organizational rules for a given scenario.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Introduction to Chapter 5<\/h2>\n<p class=\"import-Normal\">In Chapter 4, we began to look at the three-model approach for creating a database. This was at a high level with some relational database management system (DBMS) examples. We did not cover everything in the logical data model. We will continue in this chapter.<\/p>\n<p class=\"import-Normal\">The second edition Chapter 7 was a short chapter that explored at a high level the relational data model. The second edition Chapter 8 explored the entity relationship data model. For the third edition, these two chapters have been combined.<\/p>\n<h2>Background on the Relational Data Model<\/h2>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image1-1.png\" alt=\"image\" width=\"442.974173228346px\" height=\"436.093018372703px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.1 Image of Edgar Frank \u201cTed\u201d Codd. Source of image: https:\/\/studymate.tutorialathome.in\/postimg\/e-f-codd.jpg<\/p>\n<p class=\"import-Normal\">The relational data model was introduced by Edgar Frank \u201cTed\u201d Codd in 1970. Currently, it is the most widely used data model.<\/p>\n<p class=\"import-Normal\">The relational model has provided the basis for:<\/p>\n<ul>\n<li class=\"import-Normal\">Research on the theory of data\/relationship\/constraint<\/li>\n<li class=\"import-Normal\">Numerous database design methodologies<\/li>\n<li class=\"import-Normal\">The standard database access language called s<em>tructured query language (SQL)<\/em><\/li>\n<li class=\"import-Normal\">Almost all modern commercial database management systems<\/li>\n<\/ul>\n<p class=\"import-Normal\">The relational data model describes the world as \u201ca collection of inter-related relations (or tables).\u201d<\/p>\n<h2>Fundamental Concepts in the Relational Data Model<\/h2>\n<h3>Relation<\/h3>\n<p class=\"import-Normal\">Formally, a <em>relation<\/em> is a subset of the Cartesian product of a list of domains characterized by a name. Informally, this is a relation between two entities. This is also known as a <em>table<\/em> or <em>file<\/em>. And within a table, each <em>row<\/em> represents a group of related data values. A <em>row<\/em>, or <em>record<\/em>, is also known as a <em>tuple<\/em>. A <em>column<\/em> is a field and is also referred to as an attribute. A <em>domain<\/em> is the set of allowable values for a column.<\/p>\n<p class=\"import-Normal\">You can also think of it this way: An attribute is used to define the record and a record contains a set of attributes. The permitted or legal values are listed in the domain.<\/p>\n<p class=\"import-Normal\">The above can be formally expressed as follows:<\/p>\n<ol>\n<li class=\"import-Normal\">Given <em>n<\/em> domains are denoted by D1, D2, \u2026 Dn<\/li>\n<li class=\"import-Normal\">And <em>r<\/em> is a relation defined on these domains.<\/li>\n<li class=\"import-Normal\">Then r \u2286 D1\u00d7D2\u00d7\u2026\u00d7Dn<\/li>\n<\/ol>\n<p class=\"import-Normal\">Formal notations are used as common language when users come from different backgrounds. You can think about languages that are used for diplomacy or for business such as Latin, French, and English. Now to examine each term in greater detail.<\/p>\n<h3>Table<\/h3>\n<p class=\"import-Normal\">A database is composed of multiple tables and each table holds the data. Figure 7.2 shows a database that contains three tables.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image2-1.jpeg\" alt=\"image\" width=\"300px\" height=\"221px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.2. Database with three tables. Source of image: Second Edition Figure 7.2<\/p>\n<h3>Column<\/h3>\n<p class=\"import-Normal\">The principal storage units are called <em>columns<\/em> or <em>field<\/em> or <em>attributes<\/em>. These house the basic components of data into which your content can be broken down.<\/p>\n<p class=\"import-Normal\">When deciding which fields to create, you need to think generically about your information. For example, drawing out the common components of the data that you will store in the database and avoiding the specifics that distinguish one item from another. Look at the example of an ID card in Figure 7.3 to see the relationship between a field and its data.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image3-1.jpeg\" alt=\"image\" width=\"300px\" height=\"177px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.3. Example of an ID card by A. Watt. Source of image: Second Edition Figure 7.3<\/p>\n<h3>Domain<\/h3>\n<p class=\"import-Normal\">A <em>domain<\/em> is the original sets of atomic values used to model data. By <em>atomic value<\/em>, we mean that each value in the domain is indivisible as far as the relational model is concerned. For example:<\/p>\n<ul>\n<li class=\"import-Normal\">The domain of Marital Status has a set of possibilities: Married, Single, Divorced.<\/li>\n<li class=\"import-Normal\">The domain of Workdays has the set of all possible days: {Mon, Tue, Wed\u2026}.<\/li>\n<li class=\"import-Normal\">The domain of Salary is the set of all floating-point numbers greater than 0 and less than 200,000.<\/li>\n<li class=\"import-Normal\">The domain of First Name is the set of character strings that represents names of people.<\/li>\n<\/ul>\n<p class=\"import-Normal\">In summary, a domain is a set of acceptable values that a column is allowed to contain. This is based on various properties and the data type for the column. We will discuss data types in another chapter.<\/p>\n<h3>Records<\/h3>\n<p class=\"import-Normal\">Just as the content of a document or item needs to be broken down into its constituent bits of data for storage in the fields, the link between these also needs to be available so that the individual record can be reconstituted into the original form. Working with records allow us to do this. <em>Records<\/em> contain fields that are related, such as a customer or an employee. As noted earlier, a tuple is another term used for record.<\/p>\n<p class=\"import-Normal\">Records and fields form the basis of all databases. A simple table gives us the clearest picture of how records and fields work together in a database.<\/p>\n<table>\n<tbody>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>RecordID<\/strong><\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>PubDate<\/strong><\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>Author<\/strong><\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"background-color: #d9e2f3;border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\"><strong>Title<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">1<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">26\/07\/1968<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">B. Pitt<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">Rights and Wrongs Online<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">2<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">3\/5\/2000<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">A. Jolie<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">Networking for Change<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">3<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">27\/02\/1971<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">J. Carter<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">The Myth of Cyber Crimes<\/p>\n<\/td>\n<\/tr>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">4<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">15\/09\/1983<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">I. Wheaton<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">Connecting the Disconnected<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"import-Normal\">Figure 5.4. Example of a simple table by A. Watt. (Revised by F. Strickland)<\/p>\n<p class=\"import-Normal\">Figure 5.4 shows a table with fields for holding data. This one has:<\/p>\n<ul>\n<li class=\"import-Normal\">The attribute name is unique in a table. These are in the row with light blue.<\/li>\n<li class=\"import-Normal\">Below the attribute name are cells containing the values.<\/li>\n<li class=\"import-Normal\">A row contains cells that are related in some fashion. This is sometimes called a <em>tuple.<\/em><\/li>\n<li class=\"import-Normal\">The RecordID field contains whole numbers. The data type is an integer.<\/li>\n<li class=\"import-Normal\">The PubDate field contains dates that are expressed in day\/month\/year format. The data type is date.<\/li>\n<li class=\"import-Normal\">The Author field contains strings that are displayed in first initial and surname format. The data type is text.<\/li>\n<li class=\"import-Normal\">The Title field contains strings that are free text. The data type is text.<\/li>\n<\/ul>\n<h3>Degree<\/h3>\n<p class=\"import-Normal\">The <em>degree<\/em> is the number of attributes in a table. In Figure 5.4, the degree is 4.<\/p>\n<h2>Properties of a Table<\/h2>\n<p class=\"import-Normal\">A database table has some properties:<\/p>\n<ul>\n<li class=\"import-Normal\">A table has a name that is distinct from all other tables in the database.\n<ul>\n<li class=\"import-Normal\">It is possible to have two databases and each one has a table with the same name.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">There are no duplicated rows.\n<ul>\n<li class=\"import-Normal\">According to database design best practices, a database table should not contain duplicated rows. To ensure that duplicated rows do not happen, primary keys are used.<\/li>\n<li class=\"import-Normal\">Duplicate rows could happen in\n<ul>\n<li class=\"import-Normal\">A staging table.<\/li>\n<li class=\"import-Normal\">A table that does not have a primary key.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">Entries in columns are <em>atomic<\/em> or cannot be broken down further.\n<ul>\n<li class=\"import-Normal\">The table does not contain repeating groups or multivalued attributes.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">Entries in a column are from the same domain based on their data type such as:\n<ul>\n<li class=\"import-Normal\">number (numeric, integer, float, smallint,\u2026)<\/li>\n<li class=\"import-Normal\">character (string)<\/li>\n<li class=\"import-Normal\">date<\/li>\n<li class=\"import-Normal\">logical (true or false)<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">Operations combining different data types are disallowed.\n<ul>\n<li class=\"import-Normal\">This is possible if a <em>cast <\/em><em>function<\/em> is used. This converts a value of one data type to another data type.\n<ul>\n<li class=\"import-Normal\">For example, you wish to convert the string value of \u201c2\u201d to an integer so you an add it to an integer. CAST (\u201c2\u201d AS int) + 2 would be 4 instead of \u201c22.\u201d<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">Each attribute has a distinct name or unique name in a database table.\n<ul>\n<li class=\"import-Normal\">It is possible to have two data tables and each one has a column with the same name.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">The sequence or ordering of the columns is insignificant.<\/li>\n<li class=\"import-Normal\">The sequence or ordering of the rows is insignificant.<\/li>\n<\/ul>\n<h2>Background on the Entity-Relationship Model<\/h2>\n<p class=\"import-Normal\">In 1976, Peter Chen introduced the <em>entity<\/em><em>&#8211;<\/em><em>relationship <\/em><em>(ER<\/em><sup class=\"import-FootnoteReference\"><em><a id=\"sdfootnote1anc\" href=\"#sdfootnote1sym\">1<\/a><\/em><\/sup><em>) <\/em><em>model<\/em>. In his paper, he wrote about the model being a tool for designing databases. His approach would support data integrity, information retrieval, and data manipulation. The model draws upon the network model, the relational model, and the entity set model. In the introduction to his paper, he noted the strengths and the weaknesses of these three models. In his own words, he wrote:<\/p>\n<ul>\n<li>The network model provides a more natural view of data by separating entities and relationships (to a certain extent), but its capability to achieve data independence has been challenged.<\/li>\n<li>The relational model is based on relational theory and can achieve a high degree of data independence, but it may lose some important semantic information about the real world.<\/li>\n<li>The entity set model, which is based on set theory, also achieves a high degree of data independence, but its viewing of values such as \u201c3\u201d or \u201cred\u201d may not be natural to some people.<\/li>\n<\/ul>\n<p class=\"import-Normal\">Chen\u2019s model has a more natural view of the real world\u2014a world of entities and relationships. His model has a high degree of data independence. It is based on set theory and relation theory. Chen introduced a special diagramming technique for working on database designs that he called the <em>entity-relationship diagram (ERD<\/em>). (See the references for two links to the complete Peter Chen paper.)<\/p>\n<h2>Entities, Relationships, and Attributes<\/h2>\n<p class=\"import-Normal\">Chen focused on three parts:<\/p>\n<ul>\n<li class=\"import-Normal\"><em>Entities<\/em> as something that \u201cexists in our minds.\u201d An entity could be a <em>tangible type<\/em> or could be a <em>non-tangible type<\/em>. These can be classified into different <em>entity sets<\/em>.\n<ul>\n<li class=\"import-Normal\">Employee, Project, and Department<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote2anc\" href=\"#sdfootnote2sym\">2<\/a><\/sup><\/li>\n<li class=\"import-Normal\">Male_Person<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote3anc\" href=\"#sdfootnote3sym\">3<\/a><\/sup> is a subset of Person.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\"><em>Relationships<\/em> as an \u201cassociations among entities<em>.<\/em><em>\u201d<\/em>\n<ul>\n<li class=\"import-Normal\">A marriage is a relationship between two entities in the entity set Person.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\"><em>Attribute<\/em><em>s<\/em> as an \u201cinformation about an entity or a relationship\u2026<em>.<\/em><em>\u201d<\/em>\n<ul>\n<li class=\"import-Normal\">\u201c3\u201d, \u201cred\u201d, \u201cPeter\u201d, and \u201cJohnson\u201d are values.<\/li>\n<li class=\"import-Normal\">\u201cValues are classified into different value sets, such as FEET, COLOR, FIRST-NAME, and LAST-NAME.\u201d<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Illustrating the ER Model and the ERD<\/h2>\n<p class=\"import-Normal\">For the rest of this chapter, we will use a sample database (Company) to illustrate the ER model and the ERD. We will be using the following symbols:<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image4-2.png\" alt=\"image\" width=\"624px\" height=\"344.466666666667px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.5 Symbols used in an ER Diagram. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\n<p class=\"import-Normal\">The Company database contains data about employees, departments, and projects. The ERD symbol for these is a rectangle:<\/p>\n<table>\n<tbody>\n<tr class=\"TableGrid-R\">\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\" style=\"text-align: center\">Employee<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border-top: none windowtext 0pt;border-bottom: none windowtext 0pt;border-right: solid windowtext 0.5pt;border-left: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\" style=\"text-align: center\">Department<\/p>\n<\/td>\n<td class=\"TableGrid-C\" style=\"border-top: none windowtext 0pt;border-bottom: none windowtext 0pt;border-right: solid windowtext 0.5pt;border-left: solid windowtext 0.5pt\">\n<p class=\"import-Normal\">\n<\/td>\n<td class=\"TableGrid-C\" style=\"border: solid windowtext 0.5pt\">\n<p class=\"import-Normal\" style=\"text-align: center\">Project<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"import-Normal\">Figure 5.6 ERD for the three Company entities.<\/p>\n<p class=\"import-Normal\">We need to note the following important points about the Company database:<\/p>\n<ul>\n<li class=\"import-Normal\">There are several departments in the company. Each department has a unique identification, a name, an office location, and a particular employee who manages the department.<\/li>\n<li class=\"import-Normal\">A department controls a number of projects. Each project has a unique name, a unique number, and a budget.<\/li>\n<li class=\"import-Normal\">Each employee has a name, an identification number (ID), an address, a salary, and a birthdate.\n<ul>\n<li class=\"import-Normal\">An employee is assigned to one department.<\/li>\n<li class=\"import-Normal\">An employee reports to one direct supervisor.<\/li>\n<li class=\"import-Normal\">An employee could be a part of several projects.\n<ul>\n<li class=\"import-Normal\">We need to record the start date of the employee in each project.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">An employee may have dependents.<\/li>\n<\/ul>\n<\/li>\n<li class=\"import-Normal\">Each dependent has a name, a birthdate, and a relationship to the employee.<\/li>\n<\/ul>\n<h3>Entity Existence Dependency<\/h3>\n<p class=\"import-Normal\">Entities can be classified based on strength. An entity is considered strong if it can exist apart from all related entities. An entity is considered weak if its existence depends upon a corresponding entity in another entity set. Chen used the phrase \u201cexistence dependency\u201d to describe this relationship. In Chen\u2019s own words: \u201c\u2026 [the] existence of an entity in the entity set D[ependent] depends on the corresponding entity in the entity set E[mployee]. That is, if an employee leaves the company, his dependents may no longer be of interest.\u201d Figure 5.7 illustrates a strong entity and a weak entity.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image5-2.png\" alt=\"image\" width=\"624px\" height=\"103.066666666667px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.7 Strong entity on the left-hand side and a weak entity on the right-hand side.<\/p>\n<h3>Relationship Type and Degree of a Relationship Set<\/h3>\n<p class=\"import-Normal\">A <em>relationship type<\/em> represents the association between entity types. For example, in Figure 5.8 \u201cEnrolled in\u201d is a relationship type that exists between entity type \u201cStudent\u201d and entity type \u201cCourse.\u201d In the ERD, the relationship type is represented by a diamond. Lines are used to connect the entity types and the relationship types. Figure 5.8 shows two strong entities. Figure 5.9 shows a strong entity with a weak entity.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image6-2.png\" alt=\"image\" width=\"624px\" height=\"126.466666666667px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.8 Relation type with two strong entities. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image7-2.png\" alt=\"image\" width=\"624px\" height=\"131px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.9 Strong entity and a weak entity.<\/p>\n<p class=\"import-Normal\">Geeks for Geeks explained relationship set as \u201ca set of relationships of the same type\u201d The website has an illustration, but it does not clearly explain the definition. The concept of a <em>degree of a relationship set<\/em> does clear up the confusion. This is the number of different entity sets participating in a relationship set.<\/p>\n<h3>Degree of a Relationship Set: Unary<\/h3>\n<p class=\"import-Normal\">When there is only one entity set participating in a relation, the relationship is called a <em>unary relationship<\/em>. For example, one person is married to only one person.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image8.png\" alt=\"image\" width=\"569.649343832021px\" height=\"202.417532808399px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.10 An example of a person being married to a person. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\n<p class=\"import-Normal\">A variation of Figure 5.10 would not be used in the Company database. The exception would be if the company had a business rule that it hires only husbands and wives as a team.<\/p>\n<p class=\"import-Normal\">There is a <em>recursive unary relationship<\/em>. This is when there is a relationship between members of the same entity set. For example, an employee is a supervisor of another employee. Figure 5.11 illustrates this relationship. In the actual Employee table, it might be necessary to add a column for the supervisor\u2019s ID.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image9.jpeg\" alt=\"image\" width=\"300px\" height=\"168px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.11 Example of a unary relationship. Source of image: Second Edition Figure 8.9<\/p>\n<h3>Degree of a Relationship Set: Binary<\/h3>\n<p class=\"import-Normal\">When there are two entities set participating in a relationship, the relationship is called a binary relationship. Figure 5.12 illustrates a binary relationship with the Employee table and the Spouse table. Note how this is different from Figure 5.10, which has both individuals appearing in the same table.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image10.png\" alt=\"image\" width=\"624px\" height=\"130.6px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.12 An employee is associated with a spouse.<\/p>\n<p class=\"import-Normal\" style=\"text-align: justify\">The Geeks for Geeks website used an example from education. See Figure 5.13 for a Student being enrolled in a Course.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image11.png\" alt=\"image\" width=\"624px\" height=\"115.933333333333px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.13 An example of a binary relationship. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\n<h4>Degree of a Relationship Set: Ternary<\/h4>\n<p class=\"import-Normal\">This is when more than two entity sets participate in a relation.<\/p>\n<h3>Attribute Symbol<\/h3>\n<p class=\"import-Normal\">For an entity type, we define the properties. These are represented in ovals.<\/p>\n<h3>Key Attribute<\/h3>\n<p class=\"import-Normal\">An attribute that uniquely identifies each entity is called a <em>key attribute<\/em>. This is represented with the word underlined in an oval.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image12.png\" alt=\"image\" width=\"198.417217847769px\" height=\"69.6059842519685px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.14 An example of a key attribute.<\/p>\n<h3>Composite Attributes<\/h3>\n<p class=\"import-Normal\"><em>Composite attributes<\/em> are those that consist of a hierarchy of attributes. Using the Employee table from the Company database, the Address may consist of a Number, a Street, and a Suburb. This is represented with two or more ovals coming out of another oval. See Figure 5.15. This could be written as<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 36pt\">\u2192 Address = {59 + \u2018Meek Street\u2019 + \u2018Kingsford\u2019}<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image13.jpeg\" alt=\"image\" width=\"300px\" height=\"133px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.15. An example of composite attributes. Source of image: Second Edition Figure 8.3<\/p>\n<h3>Multivalued Attributes<\/h3>\n<p class=\"import-Normal\"><em>Multivalued<\/em> <em>attributes<\/em> are attributes that have a set of values for each entity. An example of a multivalued attribute from the Company database is illustrated in Figure 7.16. An employee could have more than one academic degree such as BSc, MIT, and PhD. This is represented as a double oval.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image14.jpeg\" alt=\"image\" width=\"300px\" height=\"131px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.16. Example of a multivalued attribute. Source of image: Second Edition Figure 8.4<\/p>\n<h3>Derived Attributes<\/h3>\n<p class=\"import-Normal\"><em>Derived <\/em><em>attributes<\/em> are attributes that are derived from other attributes. This is represented as a dotted oval in Figure 5.17.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image15.png\" alt=\"image\" width=\"198.417217847769px\" height=\"129.611233595801px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.17 Example of a derived attribute. Source of image: https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/p>\n<h2>The Need for Keys<\/h2>\n<p class=\"import-Normal\">In the paragraphs on \u201ckey attribute,\u201d we defined this as something that uniquely identifies an entity. We did not cover the various types of keys nor did we explain how the primary key is selected. We will address those questions in this section.<\/p>\n<h2>Types of Keys<\/h2>\n<p class=\"import-Normal\">There are several types of keys that can be used to locate a record. Geeks for Geeks illustrate all of the possible keys (Figure 5.18):<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image16.png\" alt=\"image\" width=\"624px\" height=\"595.2px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.18 Diagram of all possible keys in a DBMS. Source of image: https:\/\/www.geeksforgeeks.org\/candidate-key-in-dbms\/<\/p>\n<p class=\"import-Normal\">These are described below.<\/p>\n<h3>Super Key<\/h3>\n<p class=\"import-Normal\">A <em>super key<\/em> is a set of one or more attributes (columns), which can uniquely identify a row in a table.<\/p>\n<p class=\"import-Normal\">Using the Employee table from the Company database example, we might have the following attributes:<\/p>\n<ul>\n<li>EmployeeID<\/li>\n<li>FirstName<\/li>\n<li>LastName<\/li>\n<li>SSN<sup class=\"import-FootnoteReference\"><a id=\"sdfootnote4anc\" href=\"#sdfootnote4sym\">4<\/a><\/sup><\/li>\n<li>Address<\/li>\n<li>Phone<\/li>\n<li>BirthDate<\/li>\n<li>Salary<\/li>\n<li>DepartmentID<\/li>\n<\/ul>\n<p class=\"import-Normal\">The possible super keys are:<\/p>\n<ul>\n<li>EmployeeID<\/li>\n<li>FirstName (Assuming that this is unique.)<\/li>\n<li>LastName (Assuming that this is unique.)<\/li>\n<li>SSN<\/li>\n<li>Address (Assuming that this is unique.)<\/li>\n<li>Phone (Assuming that this is unique.)<\/li>\n<li>BirthDate (Assuming that this is unique.)<\/li>\n<li>Salary (Assuming that this is unique.)<\/li>\n<li>DepartmentID (Assuming that this is unique.)<\/li>\n<li>EmployeeID, FirstName<\/li>\n<li>EmployeeID, LastName<\/li>\n<li>EmployeeID, SSN<\/li>\n<li>EmployeeID, Address<\/li>\n<li>EmployeeID, Phone<\/li>\n<li>EmployeeID, BirthDate<\/li>\n<li>EmployeeID, Salary<\/li>\n<li>EmployeeID, DepartmentID<\/li>\n<li>FirstName, LastName (Assuming that the combination is unique.)<\/li>\n<li>FirstName, SSN<\/li>\n<li>FirstName, Address (Assuming that the combination is unique.)<\/li>\n<li>FirstName, Phone (Assuming that the combination is unique.)<\/li>\n<li>FirstName, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>FirstName, Salary (Assuming that the combination is unique.)<\/li>\n<li>FirstName, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>LastName, SSN<\/li>\n<li>LastName, Address (Assuming that the combination is unique.)<\/li>\n<li>LastName, Phone (Assuming that the combination is unique.)<\/li>\n<li>LastName, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>LastName, Salary (Assuming that the combination is unique.)<\/li>\n<li>LastName, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>SSN, Address<\/li>\n<li>SSN, Phone<\/li>\n<li>SSN, BirthDate<\/li>\n<li>SSN, Salary<\/li>\n<li>SSN, DepartmentID<\/li>\n<li>Address, Phone (Assuming that the combination is unique.)<\/li>\n<li>Address, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>Address, Salary (Assuming that the combination is unique.)<\/li>\n<li>Address, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>Phone, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>Phone, Salary (Assuming that the combination is unique.)<\/li>\n<li>Phone, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>BirthDate, Salary (Assuming that the combination is unique.)<\/li>\n<li>BirthDate, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>Salary, DepartmentID (Assuming that the combination is unique.)<\/li>\n<\/ul>\n<h3>Candidate Key<\/h3>\n<p class=\"import-Normal\">A <em>candidate key<\/em> is a simple or composite key that is unique and minimal. Another way to view a candidate key is that the minimal super key has no redundant attributes.<\/p>\n<p class=\"import-Normal\">The possible candidate keys are:<\/p>\n<ul>\n<li>EmployeeID<\/li>\n<li>FirstName (Assuming that this is unique.)<\/li>\n<li>LastName (Assuming that this is unique.)<\/li>\n<li>SSN<\/li>\n<li>Address (Assuming that this is unique.)<\/li>\n<li>Phone (Assuming that this is unique.)<\/li>\n<li>BirthDate (Assuming that this is unique.)<\/li>\n<li>Salary (Assuming that this is unique.)<\/li>\n<\/ul>\n<h3>Primary Key<\/h3>\n<p class=\"import-Normal\">A <em>primary<\/em> <em>key<\/em> is selected from the set of candidate keys. This done by the database administrator or by the database designer. More than likely one of the following would be selected:<\/p>\n<ul>\n<li>EmployeeID<\/li>\n<li>SSN<\/li>\n<\/ul>\n<h3>Alternate Key (or Secondary Key)<\/h3>\n<p class=\"import-Normal\">After the primary key has been selected, then the other candidate keys are the <em>alternate keys<\/em>. If EmployeeID was selected as the primary key, then SSN would be the alternate key. Or if SSN was selected as the primary key, then the EmployeeID would be the alternate key:<\/p>\n<ul>\n<li>EmployeeID<\/li>\n<li>SSN<\/li>\n<\/ul>\n<h3>Composite Key (or Compound Key)<\/h3>\n<p class=\"import-Normal\">A <em>composite<\/em> <em>key<\/em> (or <em>compound key<\/em>) uses more than one attribute to locate a record.<\/p>\n<p class=\"import-Normal\">One way to think about a composite key is when you attempt to access an account and you do not have the account number. Other pieces are used to locate your account.<\/p>\n<p class=\"import-Normal\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image17.png\" alt=\"image\" width=\"624px\" height=\"322.6px\" \/><\/p>\n<p class=\"import-Normal\">Figure 5.19 Example of how to locate an account when the number is not known. Source of image: https:\/\/s3.amazonaws.com\/cdn.freshdesk.com\/data\/helpdesk\/attachments\/production\/47043169576\/original\/Htjf1hvCQ9afJXvY2vQ54XHtVlLPsuQTGw.png?1603288395<\/p>\n<p class=\"import-Normal\">The possible composite keys are:<\/p>\n<ul>\n<li>EmployeeID, FirstName<\/li>\n<li>EmployeeID, LastName<\/li>\n<li>EmployeeID, SSN<\/li>\n<li>EmployeeID, Address<\/li>\n<li>EmployeeID, Phone<\/li>\n<li>EmployeeID, BirthDate<\/li>\n<li>EmployeeID, Salary<\/li>\n<li>EmployeeID, DepartmentID<\/li>\n<li>FirstName, LastName (Assuming that the combination is unique.)<\/li>\n<li>FirstName, SSN<\/li>\n<li>FirstName, Address (Assuming that the combination is unique.)<\/li>\n<li>FirstName, Phone (Assuming that the combination is unique.)<\/li>\n<li>FirstName, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>FirstName, Salary (Assuming that the combination is unique.)<\/li>\n<li>FirstName, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>LastName, SSN<\/li>\n<li>LastName, Address (Assuming that the combination is unique.)<\/li>\n<li>LastName, Phone (Assuming that the combination is unique.)<\/li>\n<li>LastName, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>LastName, Salary (Assuming that the combination is unique.)<\/li>\n<li>LastName, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>SSN, Address<\/li>\n<li>SSN, Phone<\/li>\n<li>SSN, BirthDate<\/li>\n<li>SSN, Salary<\/li>\n<li>SSN, DepartmentID<\/li>\n<li>Address, Phone (Assuming that the combination is unique.)<\/li>\n<li>Address, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>Address, Salary (Assuming that the combination is unique.)<\/li>\n<li>Address, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>Phone, BirthDate (Assuming that the combination is unique.)<\/li>\n<li>Phone, Salary (Assuming that the combination is unique.)<\/li>\n<li>Phone, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>BirthDate, Salary (Assuming that the combination is unique.)<\/li>\n<li>BirthDate, DepartmentID (Assuming that the combination is unique.)<\/li>\n<li>Salary, DepartmentID (Assuming that the combination is unique.)<\/li>\n<\/ul>\n<h3>Foreign Key<\/h3>\n<p class=\"import-Normal\">A <em>foreign<\/em><em> key<\/em> is used to link one table to another table. Or this is an attribute in a table that references the primary key in another table.<\/p>\n<p class=\"import-Normal\">This could be null. Both foreign and primary keys must be of the same data type.<\/p>\n<p class=\"import-Normal\">In the Company database example, DepartmentID is the foreign key in the Employee table:<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 36pt\"><strong>Employee<\/strong>(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)<\/p>\n<h2>Key Terms<\/h2>\n<p class=\"import-Normal\"><strong>alternate key (or secondary key)<\/strong><strong>: <\/strong>These are the unselected candidate keys.<\/p>\n<p class=\"import-Normal\"><strong>atomic values<\/strong><strong>: <\/strong>This means that the value cannot be broken down into small pieces.<\/p>\n<p class=\"import-Normal\"><strong>attributes<\/strong><strong>: <\/strong>This is information about an entity or about a relationship.<\/p>\n<p class=\"import-Normal\"><strong>cast function<\/strong><strong>: <\/strong>This is a way of converting a value to another data type.<\/p>\n<p class=\"import-Normal\"><strong>candidate key<\/strong><strong>: <\/strong>This is a way of converting a value to another data type.<\/p>\n<p class=\"import-Normal\"><strong>column<\/strong><strong>: <\/strong>This is an attribute.<\/p>\n<p class=\"import-Normal\"><strong>composite attribute<\/strong><strong>: <\/strong>This is an attribute that is composed of two or more pieces.<\/p>\n<p class=\"import-Normal\"><strong>c<\/strong><strong>omposite <\/strong><strong>key<\/strong><strong>: <\/strong>This uses more than one attribute to locate a record.<\/p>\n<p class=\"import-Normal\"><strong>d<\/strong><strong>egree<\/strong><strong>: <\/strong>This is the number of columns in a table.<\/p>\n<p class=\"import-Normal\"><strong>degree of a relationship set<\/strong><strong>: <\/strong>This is the number of different entity sets participating in a relationship set.<\/p>\n<p class=\"import-Normal\"><strong>d<\/strong><strong>erived attribute<\/strong><strong>: <\/strong>This is derived from other attributes.<\/p>\n<p class=\"import-Normal\"><strong>domain<\/strong><strong>: <\/strong>This is the set of allowable values for a column.<\/p>\n<p class=\"import-Normal\"><strong>entity or <\/strong><strong>entities<\/strong><strong>: <\/strong>In the words of Peter Chen, these are things that \u201cexists in our minds.\u201d An entity is an object in the real world with an independent existence that\u00a0can be differentiated from other objects.<\/p>\n<p class=\"import-Normal\"><strong>Entity-Relationship <\/strong><strong>Diagram<\/strong><strong>:<\/strong> Peter Chen developed this tool in support of the entity-relationship model.<\/p>\n<p class=\"import-Normal\"><strong>Entity-Relationship Model<\/strong><strong>:<\/strong> Peter Chen developed this model in 1976. It draws upon the network model, the relational model, and entity set model. It is a more natural view of the real world. It is based on sets theory and relational theory.<\/p>\n<p class=\"import-Normal\"><strong>entity set<\/strong><strong>: <\/strong>This is a collection of entities. In the words of Peter Chen, an entity \u201chas the properties common to the other entities in the entity set\u2026.\u201d<\/p>\n<p class=\"import-Normal\"><strong>existence dependency<\/strong>: An entity that dependents upon the existence of another entity. This is a weak relationship. If the linked entity is removed, then these dependent entities would go away.<\/p>\n<p class=\"import-Normal\"><strong>fi<\/strong><strong>eld<\/strong><strong>: <\/strong>See column above.<\/p>\n<p class=\"import-Normal\"><strong>file: <\/strong>See relation below.<\/p>\n<p class=\"import-Normal\"><strong>F<\/strong><strong>oreign key<\/strong><strong>:<\/strong> This is used to link from one table to another table.<\/p>\n<p class=\"import-Normal\"><strong>key attribute<\/strong><strong>: <\/strong>This is an attribute that uniquely identifies an entity.<\/p>\n<p class=\"import-Normal\"><strong>multivalued<\/strong><strong> attribute<\/strong><strong>: <\/strong>This is an attribute that could contain more than one entity.<\/p>\n<p class=\"import-Normal\"><strong>non-tangible type<\/strong>: This is an entity that is not physical and cannot be touched.<\/p>\n<p class=\"import-Normal\"><strong>primary key<\/strong>: This is selected from the set of candidate keys. Recall from Chapter 3 that this was defined as a unique value for locating a row of data in a database table.<\/p>\n<p class=\"import-Normal\"><strong>relation<\/strong>: Formally, this is a subset of the Cartesian product of a list of domains characterized by a name. Informally, this is a relation between two entities.<\/p>\n<p class=\"import-Normal\"><strong>r<\/strong><strong>elation<\/strong><strong>ship<\/strong><strong> type<\/strong>: This represents the associations between entity types.<\/p>\n<p class=\"import-Normal\"><strong>relation<\/strong><strong>ships<\/strong>: In the words of Peter Chen, this an \u201cassociation among entities.\u201d<\/p>\n<p class=\"import-Normal\"><strong>r<\/strong><strong>ecord<\/strong><strong>: <\/strong>See row below.<\/p>\n<p class=\"import-Normal\"><strong>recursive relationship:<\/strong> See unary relationship.<\/p>\n<p class=\"import-Normal\"><strong>row: <\/strong>This represents a group of related data.<\/p>\n<p class=\"import-Normal\"><strong>staging table: <\/strong>This is a table that is receiving data from different sources.<\/p>\n<p class=\"import-Normal\"><strong>s<\/strong><strong>u<\/strong><strong>per key<\/strong><strong>: <\/strong>This is a set of one or more attributes (columns), which can uniquely identify a row in a table.<\/p>\n<p class=\"import-Normal\"><strong>table: <\/strong>See relation above.<\/p>\n<p class=\"import-Normal\"><strong>tangible type<\/strong>: This is an entity that is physical or could be touched.<\/p>\n<p class=\"import-Normal\"><strong>t<\/strong><strong>ernary<\/strong>: This is when more than two entity sets participate in a relation.<\/p>\n<p class=\"import-Normal\"><strong>t<\/strong><strong>uple<\/strong><strong>: <\/strong>See row above.<\/p>\n<p class=\"import-Normal\"><strong>unary relationship<\/strong><strong>:<\/strong> This is one in which a relationship exists between occurrences of the same entity set.<\/p>\n<h2>Exercises<\/h2>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use Table 5.1 to answer the first four questions.<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use Table 5.2 to answer questions 7 through 10.<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Use both tables to answer question 11.<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image18.jpeg\" alt=\"image\" width=\"600px\" height=\"112px\" \/><\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Table 5.1 Table for exercise questions, by A. Watt<\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\"><img decoding=\"async\" src=\"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-content\/uploads\/sites\/2211\/2025\/01\/image19.jpeg\" alt=\"image\" width=\"624px\" height=\"445.733333333333px\" \/><\/p>\n<p class=\"import-Normal\" style=\"margin-left: 18pt\">Table 5.2 Director and Play tables by A. Watt<\/p>\n<ol>\n<li>Using correct terminology, identify and describe all the components in Table 5.1.<\/li>\n<li>What is the possible domain for field EmpJobCode?<\/li>\n<li>How many records are shown?<\/li>\n<li>How many attributes are shown?<\/li>\n<li>List the properties of a database table.<\/li>\n<li>What three concepts or parts of the ERD?<\/li>\n<li>Identify the super keys in Table 5.2.<\/li>\n<li>Identify the candidate keys in Table 5.2. (CS2013 IM\/RD 9)<\/li>\n<li>Identify the primary keys in Table 5.2.<\/li>\n<li>Identify the foreign key in Table 5.2.<\/li>\n<li>Use Table 5.1 and Table 5.2 and create an ERD. You may need to make some assumptions. (CS2013 IM\/RD 1, IS2020 A3.2.1.2, and IT2017 ITE-IMA-03a)<\/li>\n<li>Define the following:\n<ol>\n<li>Relation<\/li>\n<li>Column<\/li>\n<li>Domain<\/li>\n<li>Records<\/li>\n<li>Degree<\/li>\n<li>Entities<\/li>\n<li>The ERD rectangle<\/li>\n<li>The ERD oval or ellipse<\/li>\n<li>The ERD double oval.<\/li>\n<li>The ERD double rectangle<\/li>\n<li>Weak entities<\/li>\n<li>Strong entities<\/li>\n<li>Unary degree<\/li>\n<li>Binary degree<\/li>\n<li>Ternary degree<\/li>\n<li>Key attribute<\/li>\n<li>Composite attributes<\/li>\n<li>Multivalued attributes<\/li>\n<li>Derived attributes<\/li>\n<li>Super key<\/li>\n<li>Candidate key<\/li>\n<li>Primary key<\/li>\n<li>Alternate key<\/li>\n<li>Composite key<\/li>\n<li>Foreign key<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<h2>A Running Project<\/h2>\n<p class=\"import-Normal\">A running project was introduced in Chapter 2. You were encouraged to collect insights about the needs. In Chapter 3, you were told to use a word processor to create tables with columns for the entity pieces. In Chapter 4, you were told to identified the data types.<\/p>\n<p class=\"import-Normal\">In this chapter, you are to begin to create the ERD. Refer to Figure 5.5 for the symbols.<\/p>\n<p class=\"import-Normal\">Create a list of the possible keys. Identify the primary keys.<\/p>\n<p class=\"import-Normal\">Again, review what you have collected. Have you overlooked anything?<\/p>\n<p class=\"import-Normal\"><strong>\u00a0<\/strong><\/p>\n<h2>Attribution<\/h2>\n<p class=\"import-Normal\">This chapter of\u00a0<em>Database Design<\/em> is a derivative copy of\u00a0<a class=\"rId32\" href=\"http:\/\/cnx.org\/contents\/b57b8760-6898-469d-a0f7-06e0537f6817@1\">Database System Concepts<\/a>\u00a0by\u00a0Nguyen Kim Anh\u00a0licensed under\u00a0<a class=\"rId33\" href=\"http:\/\/creativecommons.org\/licenses\/by\/3.0\/\">Creative Commons Attribution License 3.0 license<\/a><\/p>\n<p class=\"import-Normal\">The following material was written by Adrienne Watt for the second edition:<\/p>\n<ul>\n<li class=\"import-Normal\">Introduction<\/li>\n<li class=\"import-Normal\">Key Terms<\/li>\n<li class=\"import-Normal\">Exercises<\/li>\n<\/ul>\n<p class=\"import-Normal\"><a id=\"_Hlk165870885\"><\/a>This chapter drew from chapter 7 and from chapter 8. The information was completely revised by Fred Strickland for the third edition.<\/p>\n<h2>References<\/h2>\n<p class=\"import-Normal\">Peter Chen. \u201cThe Entity-Relationship Model\u2014Toward a Unified View of Data<em>,\u201d ACM Transactions on Database Systems,<\/em> Volume 1, Number 1, March 1976. The abstract and the first draft page can be found at <a class=\"rId34\" href=\"https:\/\/dl.acm.org\/doi\/10.1145\/1095277.1095279\"><span class=\"import-Hyperlink\">https:\/\/dl.acm.org\/doi\/10.1145\/1095277.1095279<\/span><\/a> An unofficial copy of the paper is available at <a class=\"rId35\" href=\"https:\/\/dspace.mit.edu\/bitstream\/handle\/1721.1\/47432\/entityrelationshx00chen.pdf\"><span class=\"import-Hyperlink\">https:\/\/dspace.mit.edu\/bitstream\/handle\/1721.1\/47432\/entityrelationshx00chen.pdf<\/span><\/a> and at <a class=\"rId36\" href=\"https:\/\/www.comp.nus.edu.sg\/~lingtw\/papers\/tods76.chen.pdf\"><span class=\"import-Hyperlink\">https:\/\/www.comp.nus.edu.sg\/~lingtw\/papers\/tods76.chen.pdf<\/span><\/a><\/p>\n<p class=\"import-Normal\">This was a groundbreaking paper, but it had some errors. For example, Figure 2 had the title of \u201cAttributes defined on the entity set PERSON,\u201d but in the drawing the entity set was named \u201cEMPLOYEE.\u201d<\/p>\n<p class=\"import-Normal\">Sergey Gigoyan. \u201cFind and Remove Duplicate Rows from a SQL Server Table,\u201d MSSQLTips, July 20, 2021. <a class=\"rId37\" href=\"https:\/\/www.mssqltips.com\/sqlservertip\/4486\/find-and-remove-duplicate-rows-from-a-sql-server-table\/\"><span class=\"import-Hyperlink\">https:\/\/www.mssqltips.com\/sqlservertip\/4486\/find-and-remove-duplicate-rows-from-a-sql-server-table\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">Chaitanya Singh. \u201cAlternate key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId38\" href=\"https:\/\/beginnersbook.com\/2015\/04\/alternate-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/alternate-key-in-dbms\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">Chaitanya Singh. \u201cComposite key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId39\" href=\"https:\/\/beginnersbook.com\/2015\/04\/composite-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/composite-key-in-dbms\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">Chaitanya Singh. \u201cForeign key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId40\" href=\"https:\/\/beginnersbook.com\/2015\/04\/foreign-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/foreign-key-in-dbms\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">Chaitanya Singh. \u201cSuper key in DBMS,\u201d BeginnersBook, December 11, 2018. <a class=\"rId41\" href=\"https:\/\/beginnersbook.com\/2015\/04\/super-key-in-dbms\/\"><span class=\"import-Hyperlink\">https:\/\/beginnersbook.com\/2015\/04\/super-key-in-dbms\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">\u201cIntroduction of ER Model,\u201d Geeks for Geeks, May 23, 2024. <a class=\"rId42\" href=\"https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/\"><span class=\"import-Hyperlink\">https:\/\/www.geeksforgeeks.org\/introduction-of-er-model\/<\/span><\/a><\/p>\n<p class=\"import-Normal\">\u201cSQL Server CAST() Function,\u201d W3 Schools, n.d. <a class=\"rId43\" href=\"https:\/\/www.w3schools.com\/sql\/func_sqlserver_cast.asp\"><span class=\"import-Hyperlink\">https:\/\/www.w3schools.com\/sql\/func_sqlserver_cast.asp<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<div id=\"sdfootnote1sym\"><a href=\"#sdfootnote1anc\">1<\/a> Peter Chen never abbreviated the phrases \u201centity relationship model.\u201d He always used the long form.<\/div>\n<div id=\"sdfootnote2sym\"><a href=\"#sdfootnote2anc\">2<\/a> In Peter Chen\u2019s paper, these three entities were written as \u201cEMPLOYEE, PROJECT, and DEPARTMENT.\u201d Most best practices websites do not use all capital letters in table names. Many websites use singular noun naming customs. The third edition has corrected the second edition examples to follow this naming custom.<\/div>\n<div id=\"sdfootnote3sym\"><a href=\"#sdfootnote3anc\">3<\/a> In Peter Chen\u2019s paper, this was written as MALE-PERSON. Some websites recommend using an underscore for separating words (the Snake Case). No website uses the hyphen. There are many opinions on the topic as in MalePeson (Pascal Case), malePerson (Camel Case), and Male_Person (Snake Case)<\/div>\n<div id=\"sdfootnote4sym\"><a href=\"#sdfootnote4anc\">4<\/a> The United States uses \u201cSSN\u201d and Canada uses \u201cSIN.\u201d<\/div>\n<\/div>\n","protected":false},"author":2276,"menu_order":5,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["fredstrickland"],"pb_section_license":""},"chapter-type":[],"contributor":[66],"license":[],"class_list":["post-1033","chapter","type-chapter","status-publish","hentry","contributor-fredstrickland"],"part":3,"_links":{"self":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapters\/1033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/wp\/v2\/users\/2276"}],"version-history":[{"count":4,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapters\/1033\/revisions"}],"predecessor-version":[{"id":1037,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapters\/1033\/revisions\/1037"}],"part":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapters\/1033\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/wp\/v2\/media?parent=1033"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/pressbooks\/v2\/chapter-type?post=1033"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/wp\/v2\/contributor?post=1033"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/nelson\/wp-json\/wp\/v2\/license?post=1033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}