7

When designing a database, it is common practice for a database designer to develop an Entity Relationship model and to represent that model in a drawing, the entity relationship diagram (ERD). In this chapter, we discuss the concepts required to develop an ERD and the Peter Chen notation. Peter Chen introduced entity relationship modeling in his paper The Entity-Relationship Model–Toward a Unified View of Data (ACM Transactions on Database Systems, Vol. 1, No. 1, 1976). This paper can be found at http://csc.lsu.edu/news/erd.pdf; it is one of the most cited papers in the computer field, and has been considered one of the most influential papers in computer science. Another later paper published in Software Pioneers: Contributions to Software Engineering (2002) is Entity-Relationship Modeling: Historical Events, Future Trends, and Lessons Learned and can be found at http://bit.csc.lsu.edu/~chen/pdf/Chen_Pioneers.pdf.

Entity Relationship modeling is a process used to help us understand and document the informational requirements of a system as a logical or conceptual data model. When the model is complete, we then create a physical model in some database management system (DBMS); typically a relational DBMS, or relational database management system (RDBMS).

7.1 Introduction

In the entity relationship approach to modeling, we analyze system requirements and classify our knowledge in terms of entities, relationships, and attributes.

Entities

Entities are the things we decide to keep track of. For example, if one considers a system to support an educational environment, one is likely to decide that we need to keep track of students, instructors, courses, etc. Typically, entities are the people, places, things, and events that we need to remember something about.

Suppose we know of four student entities and two course entities. For example, consider four students (say John, Amelia, Lee, and April) and two courses (Introduction to Art and Introduction to History). We can illustrate these in a number of ways:

As tables of information:

Figure 7.1 Entities shown as rows in table

Set of student entities

Figure 7.2 Entities shown as sets

Relationships

Entities can be related to one another and so we use relationships to describe how entities relate to one another. Continuing with our educational example we know that students enroll in courses, and so this is one of the relationships we should know about. Suppose we have the two courses and four students listed previously. Suppose also that

• • John and Amelia are enrolled in Introduction to Art
  • John and Lee are enrolled in Introduction to History
  • April is not enrolled in any course.

Below, we depict four instances of the enroll-in relationship by drawing a line from a student to a course. Each relationship pairs one student with one course.

Figure 7.3 Relationships shown as lines connecting entities

Attributes

Entities and relationships have characteristics that describe them. For instance, the students in our example are described by the values for their name, id number, and phone number. As we look back, we can see there is a student named John whose id number is 184 and his phone number is 283-4984.

Courses are shown with a course title, a course number, and belong to a department. There is a course numbered 661 that is offered by the Art department and it is titled Introduction to Art.

If we consider the enroll-in relationship, we know there is a date when the student enrolled in the course and a final grade that was awarded to the student when the course was completed. For instance, we could have John enrolled in Introduction to Art on July 1, 2010 and was awarded an A+ on completion of that course.

These characteristics that serve to describe entities and relationships are called attributes. We will be examining attributes in some detail. As we will see some attributes, such as student number, serve to distinguish one instance from another – each student has a student number distinct from any other student. Other attributes we consider to be purely descriptive, such as the name of a student – many students could have the same name.

Notation

There are many notations in use today that illustrate database designs. In this text, as is done in many database textbooks, the Peter Chen notation is used; other popular notations include IDEF1X, IE and UML. There are many similarities, and so once you master the Peter Chen notation it is not difficult to adapt to a different notation.

The following is an example of an ERD drawn using the Peter Chen notation. Note the following:

• • Entity types are represented using rectangular shapes.
  • Relationship types are shown with a diamond shape. Lines connect the relationship type to its related entity types with cardinality symbols (m and n).
  • Attributes are shown as ovals with a line connecting it to the pertinent entity type or relationship type.

Figure 7.4 An ERD in Chen notation

The various symbols we use with the Peter Chen notation:

Figure 7.5 Symbols used in the Chen notation

7.2 Entities

Entities are the people, places, things, or events that are of interest for a system that we are planning to build. In the previous section, we considered there were several entities: four students and two courses.

In general, we find examples of entities when we think of people, places, things, or events in our area of interest:

People: student, customer, employee

Places: resort, city, country

Things: restaurant, product, invoice, movie, painting, book, building, contract

Events: registration, election, presentation, earthquake, hurricane

Entity sets are named collections of related entities. From our example, we have two entity sets:

• • • The Student entity set comprises at least the 4 student entities: John, Amelia, Lee, and April.
    • The Course entity set comprises at least the 2 course entities: Introduction to Art and Introduction to History.

Entity sets are the collections of entities of one type. We consider an Entity Type to be the definition of the entities in such a set. A common convention is to name entity types as singular nouns and that, at least, the first letter is capitalized.

In an ERD entity, types are shown as named rectangular shapes. For example:

The Student and Department entity types shown above are drawn with a simple single-line border. This means that they are regular (or strong) entity types that are not existence-dependent on other entity types (see the next section).

7.2 Exercises

1. Consider your educational institution. Your educational institution needs to keep track of its students. How many student entities does the institution have? You have provided the institution with information about you. In your opinion, what attributes describe these entities?
2. Consider your place of work. The Human Resources department in your company needs to manage information about its employees. How many employee entities are there? What attributes describe these entities?
3. Consider your educational institution or place of work.
   1. What are some of the entity types that would be useful?
   2. What relationships exist that relate entity types to one another?
   3. What attributes would be useful to describe entities and relationships?
   4. Draw an ERD.

7.2.1 Weak Entities

Sometimes we know certain entities only exist in relationship to others. For example, a typical educational institution comprises a number of departments that offer courses. So we could have a History department, an Art department and so on. These departments would design and deliver courses that students would register for. In this framework, the courses exist in the context of a department, and the identifier for a course is typically a department code and course number combination. So the history course, Introduction to History, belongs to the History department and it would be known by the identifier HIST-765. HIST is a code representing the History department and 765 is a number assigned to the course; other departments could have a course with that same number, 765.

In these situations where the existence of an entity depends on the existence of another entity, we say the entity is a weak entity, and the corresponding entity type is a weak entity type. Weak entities often have identifiers that comprise multiple parts (such as department code and course number). Later on, we will see other aspects of an ERD that relate to weak entity types. At this time, we should be aware that weak entity types are illustrated in an ERD with a double-lined rectangle:

Figure 7.6 Course as a weak entity type

Often, when we purchase things the vendor provides, an invoice giving details of each item that is purchased (see the sample invoice below). Appearing on the invoice are detail lines specifying the product, the quantity and price. Invoice lines are things that exist only in the context of an invoice and so each invoice line is a weak entity; the invoice lines are existence-dependent on an invoice:

Figure 7.7 Sample Invoice

The following includes a few attributes to show how Invoice and Invoice Line could appear in an ERD.

Figure 7.8 Invoices (regular entity type) and Invoice Lines (weak entity type)

7.3 Attributes

Attributes are the characteristics that describe entities and relationships. For example, a Student entity may be described by attributes including:

student number name

first name

last name

address

date of birth

gender

An Invoice entity may be described by attributes including:

invoice number

invoice date

invoice total

A common convention for naming attributes is to use singular nouns. Further, a naming convention may require one of:

All characters are in upper case.

All characters are in lower case.

Only the first character is in upper case.

All characters are lower case, but each subsequent part of a multi-part name has the first character capitalized

Using the last convention mentioned, some examples of attribute names:

lastName for last name

empLastName for employee last name

deptCode for department code

prodCode for product code

invNum for invoice number

In practice, a naming convention is important, and you should expect the organization you are working for to have a standard approach for naming things appearing in a model. A substantial data model will have tens, if not into the hundreds, of entity types, many more attributes and relationships. It becomes important to easily understand the concept underlying a specific name; a naming convention can be helpful.

There are many ways we can look at attributes including whether they are atomic, composite, single-valued, etc. We consider these next.

7.3.1 Atomic Attributes

A simple, or atomic, attribute is one that cannot be decomposed into meaningful components. For example, consider an attribute for gender – such an attribute will assume values such as Male or Female. Gender cannot be meaningfully decomposed into other smaller components.

As another example, consider an attribute for product price. A sample value for product price is $21.03. Of course, one could decompose this into two attributes where one attribute represents the dollar component (21), and the other attribute represents the cents component (03), but our assumption here is that such decompositions are not meaningful to the intended application or system. So, we would consider product price to be atomic because it cannot be usefully decomposed into meaningful components.

Similarly, an attribute for the employee’s last name cannot be decomposed, because you cannot subdivide last name into a finer set of meaningful attributes.

7.3.1 Exercises

1. Consider that a Human Resources system must keep track of employees. If we are only including atomic attributes, what attributes would you include for the employee’s name. Some possibilities are first name, last name, middle name, full name.
2. In some large organizations where there are several buildings and floors, we see room numbers that encode information about the building, floor, and room number. For example, the room 3C13 stands for room 13 on the third floor of the Centennial building. Suppose we need to include Room in an ERD. How would you represent the room number given that you must include atomic attributes only?

7.3.2 Composite Attributes

Consider an attribute containing an employee name which is to represent an employee’s complete name. For example, suppose an employee’s name is John McKenzie; the first name is John and the last name is McKenzie. It is easy to appreciate that one user may only need employee last names, and another user may need to display the first name followed by the last name, and yet another user may display the last name, a comma, and then the first name. If it’s reasonable for one to refer to the complete concept of employee name and to its component parts, first name and last name, then we can use a composite attribute. An attribute is composite if it comprises other attributes. To show that an attribute is composite and contains other attributes, we show the components as attribute ovals connected to the composite as in:

Figure 7.9 Composite attribute

Attributes can be composite and some of its component attributes may be composite as well (see Exercise #3).

7.3.2 Exercises

1. How would you use a composite attribute to model a phone number?
2. Consider the previous exercise set. Show how we can include room number as a composite attribute that has multiple components.
3. Consider an address attribute. Show that this can be modeled as a multi-level composite attribute where the component attributes include street, city, province, country and where street includes apartment number, street number, street name.

7.3.3 Single-Valued Attributes

We characterize an attribute as being single-valued if there is only one value at a given time for the attribute.

Consider the Employee entity type for a typical business application where we need to include a gender attribute. Each employee is either male or female, and so there is only one value to store per employee. In this case, we have an attribute that is single-valued for each employee. Single- valued attributes are shown with a simple oval as in all diagrams up to this point. In all of our examples so far, we have assumed that each attribute was single-valued.

7.3.3 Exercises

1. A college or university will keep track of several addresses for a student, but each of these can be named differently: for example, consider that a student has a mailing address and a home address. Create an ERD for a student entity type with two composite attributes for student addresses where each comprises several single-valued attributes.
2. Consider a marriage entity type and attributes marriage date, marriage location, husband, wife. Each marriage will only have one value for each of these attributes. Illustrate the marriage entity and its single-valued attributes in an ERD .

7.3.4 Multi-Valued Attributes

Now, suppose someone proposes to track each employee’s university degrees with an attribute named empDegree. Certainly many employees could have several degrees and so there are multiple values to be stored at one time. Consider the following sample data for three employees: each employee has a single employee number and phone number, but they have varying numbers of degrees.

Figure 7.10 Employees – number, phone, degrees

For a given employee and point in time, empDegree could have multiple values as is the case for the last two employees listed above. In this case, we say the attribute is multi-valued.

Multi-valued attributes are illustrated in an ERD with a double-lined oval.

Figure 7.11 Employee degrees shown as multi-valued

We can use multi-valued attributes to (at least) document a requirement, and at a later time, refine the model replacing the multi-valued attribute with a more detailed representation. The presence of a multi-valued attribute indicates an area that may require more analysis; multi-valued attributes are discussed again in Chapter 10.

7.3.4 Exercises

1. Consider the employee entity type.
   1. Suppose the company needs to track the names of dependents for each employee. Show the dependent name as a multi-valued attribute.
   2. Modify your ERD to show empDependentName as a composite multi-valued attribute comprising first and last names and middle initials.
2. Create an ERD that avoids the multi-valued attribute empDegrees in the previous example. Hint: Consider including another entity type and a relationship for keeping track of degrees.

7.3.5 Derived Attributes

If an attribute’s value can be derived from the values of other attributes, then the attribute is derivable, and is said to be a derived attribute. For example, if we have an attribute for birth date then age is derivable. Derived attributes are shown with a dotted lined oval.

Figure 7.12 Age is a derived attribute

Sometimes an attribute of one entity type is derived from attributes from other entity types. Consider the attribute for the total of an Invoice. A value of InvTotal is derivable; it can be computed from invoice lines. Someone who implements a database and applications that access the database would need to decide whether the value of a derivable attribute should be computed when the entity is stored or updated versus computing the value (on-the-fly) when it is needed.

7.3.5 Exercises

1. Consider an educational environment where the institution tracks the performance of each student. Often this is called the students overall average, or overall grade point average. Is such an attribute a derived attribute? How is its value determined?
2. Consider a library application that needs to keep track of books that have been borrowed. Suppose there is an entity type Loan that has attributes bookID, memberID, dateBorrowed and dateDue. Suppose the due date is always 2 weeks after the borrowed date. Show Loan and its attributes in an ERD.

7.3.6 Key Attributes

Some attributes, or combinations of attributes, serve to identify individual entities. For instance, suppose an educational institution assigns each student a student number that is different from all other student numbers. We say the student number attribute is a key attribute; student numbers are unique and distinguish students.

In an ERD, keys are shown underlined:

Figure 7.13 Key attribute is underlined

We define a key to be a minimal set of attributes that uniquely identify entities in an entity type. By minimal we mean that all of the attributes are required – none can be omitted. For instance, a typical key for an invoice line entity type would be the combination of invoice number and invoice line number. Both attributes are required to identify a particular invoice line.

It is not unusual for an entity type to have several keys. For instance, suppose an educational institution has many departments such as Mathematics, Physics, and Computer Science. Each department is given a unique name and as well the institution assigns each one a unique code: MATH, PHYS, and CS. Both attributes would be underlined to show this in the ERD:

Figure 7.14 Multiple key attributes

7.3.6 Exercises

1. Suppose a company that sells products has a product entity type with the following attributes: prodNum, prodDesc, prodPrice. Suppose all three attributes are single-valued and that prodNum is a key attribute – each product has a different product number. Illustrate this information in an ERD.
2. Consider a banking application where each account is identified first by an account number and then by its type (Savings, Checking, and Loan). This scheme allows the customer to remember just one number instead of three, and then to pick a specific account by its type. Other attributes to be considered are the date the account was opened and the account’s current balance. Draw an ERD for the entity type Account with the attributes account number, account type, date opened, current balance. What is the key of the entity type? Is there an attribute that is likely a derived attribute? Show these attributes appropriately in the ERD.

7.3.7 Partial Key

Sometimes we have attributes that distinguish entities of an entity type from other entities of the same type, but only relative to some other related entity. This situation arises naturally when we model things like invoices and invoice lines. If invoice lines are assigned line numbers (1, 2, 3, etc.), these line numbers distinguish lines on a single invoice from other lines of the same invoice. However, for any given line number value, there could be many invoice lines (from separate invoices) with that same line number.

A partial key (also called a discriminator) is an attribute that distinguishes instances of a weak entity type relative to a strong entity. Invoice line number is a partial key for invoice lines; each line on one invoice will have different line numbers. Using the Peter Chen notation the discriminator attribute is underlined with a dashed line:

Figure 7.15 Line number distinguishes lines on the same invoice

Later when relationships are covered, it will be clearer that attributes for relationships can be discriminators too. Consider that a library has books that members will borrow. Any book could be borrowed many times and even by the same member. However when a member borrows the same book more than once the date/time will distinguish those events. Consider the following ERD for this case:

Figure 7.16 Relationship attribute as a discriminator

7.3.7 Exercises

1. Consider an educational institution that has departments and where each department offers courses. Suppose departments are assigned unique identifiers and so deptCode is a key for the department entity type. Courses are identified within a department by a course number; course numbers are unique within a department but not across departments. So, History may have a course numbered 215, and English could have a course numbered 215 too. In order to identify a particular course we need to know the department and we need to know the course number. Illustrate an ERD including department and course entity types. Include attributes for the Department (department code and department name), and for Course (course number, title, and description).
2. Consider a company that owns and operates parking lots. Develop an ERD with two entity types Parking Lot and Space and where:
   • The address of a parking lot serves to identify the lot.
   • Each space within a lot is rented at the same monthly rental charge.
   • Each parking space is known by its number within the lot (within a lot these always start at 1).
   • Each parking space is rented out to at most one vehicle. The vehicle’s identifier must be recorded. The identifier comprises a province code and license plate number.

7.3.8 Surrogate Key

When a key specified for an entity is meaningless to the entity and to end-users (it doesn’t describe any characteristic of an entity), the key is referred to as a surrogate key. A key that is not a surrogate key is often referred to as a natural key. Often a surrogate key is just a simple integer value assigned by the database system.

When database designs are implemented surrogate keys can be useful to simplify references from one table to another (referential integrity) and the associated joins when tables are referenced in queries.

7.3.8 Exercise

1. Assuming you have experience with some database system, what data type would you use for surrogate keys?

7.3.9 Non-Key Attributes

Non-key attributes are attributes that are not part of any key. Generally, most attributes are simply descriptive, and fall into this category. Determining key and non-key attributes is an important modeling exercise, one that requires careful consideration. Consider an Employee entity type that has attributes for first name, last name, birth date; these attributes would serve to describe an employee but would not serve to uniquely identify employees.

People may join an organization and their name is not likely unique for the organization; we expect many people in a large organization to have the same first name, same last name, and even the same combination of first and last name. Names cannot usually be used as a key.

However, names chosen for entities such as departments in an organization could be keys because of the way the company would choose department names – they wouldn’t give two different departments the same name.

7.3.9 Exercises

1. Consider an employee entity type and its attributes, and decide which attributes are key attributes and which ones are non-key attributes. Illustrate with an ERD.
2. A birthdate attribute would appear for many entity types – for example students, employees, children. What is a birthdate likely to be: key or non-key?
3. Consider a library and the fact that books are loaned out to library members. Dates could be used heavily for the date a book was borrowed, the date the book was returned, and the due date for a book. Consider an entity type Loan that has attributes book identifier, member identifier, date borrowed, date due, date returned. What combination of attributes would be a key? Which attributes are key attributes? Which attributes are non-key attributes?

7.3.10 Nulls

When a database design is implemented, one of the important things to know for each attribute of an entity type is whether or not that attribute must have a value. For example, when a book is borrowed from a library, the date the book is borrowed is known, but the returned date is not known. Sometimes you will not know the value of an attribute until a certain event occurs.

Consider an educational environment and when a student registers for a course. The date the student registers would be known, but the grade is yet to be determined.

When an entity is created but some attribute does not have a value we say it is null. Null represents the absence of a value; null is different from zero or from blank.

7.3.11 Domains

To complete the analysis for a database design, it is necessary to determine what constitutes a valid value for an attribute. A domain for an attribute is its set of valid values which includes a choice of datatype, but a full specification of domain is typically more than that.

For instance, analysis for student identifiers may lead one to state that a student identifier is a positive whole number of exactly 7 digits with no leading zeros. The analysis of requirements for person names may lead one to state that the values stored in a database for a first name, last name, or middle name will not be more than 50 characters in length, and that names will not have any spaces at the beginning or end.

For each attribute, one must determine its domain. More than one attribute can share the same domain. Knowing the underlying domains in your model is important. They help to complete your analysis, they are indispensable for coding programs, and they are useful for defining meaningful error messages.

Attribute domains are not usually shown in an ERD. Rather, domains are included in accompanying documentation which can be referred to when the database is being implemented.

7.4 Relationships

Up to this point, we have made several references to the concept of relationship. Now, we will make our understanding of this concept more complete. A relationship is an association amongst entities. Relationships will have justification in business rules, in the way an enterprise manages its business.

There are several ways of classifying relationships, according to degree, participation, cardinality, whether recursion is involved, and whether or not a relationship is identifying.

7.4.1 Degree

We consider the degree as the number of entities that participate in the relationship. When we speak of a student enrolling in a course, we are considering a relationship (say, the enroll in relationship) where two entity types (Student and Course) are involved. This relationship is of degree 2 because each instance of the relationship will always involve one student entity and one course entity.

Figure 7.17 Binary relationship involves two entity types

With binary relationships, there must be two defining statements we can express, one from the perspective of each entity type. In this case, our statements are:

• A student may enroll in any number of courses.
• A course may have any number of students enrolled.

Many database modeling tools only support binary relationships. However, there are situations where relationships of higher degree are useful. A relationship involving 3 entity types is called ternary; more generally, we refer to relationships with n entity types as n-ary. Our primary focus in this text is on binary relationships.

7.4.2 Participation

Suppose, we are designing a database for a company that has several departments and employees. Each employee must also be assigned to work in one department. We can define a works in relationship involving Department and Employee. Employees must participate in the relationship and we show this using a double line joining the diamond symbol to the Employee entity type.

Figure 7.18 Employee must work in a department

The double line stands for total or mandatory participation which means that instances of the adjacent entity type must participate in the relationship – in the case above, all instances of Employee must be assigned to some department. Any time we show a single line we are stating participation is optional; for the above we are saying that a department will have zero or more employees who work there.

Cardinality

Cardinality is a constraint on a relationship specifying the number of entity instances that a specific entity may be related to via the relationship. Suppose we have the following rules for departments and employees:

• A department can have several employees that work in the department
• An employee is assigned to work in one department. From these rules, we know the cardinalities for the works in relationship and we express them with the cardinality symbols 1 and n below.

Figure 7.19 One-to-many relationships are most common

The n represents an arbitrary number of instances, and the 1 represents at most one instance. For the above works in relationship. we have

• a specific employee works in at most only one department, and
• a specific department may have many (zero or more) employees who work there.

n, m, N, and M are common symbols used in ER diagrams for representing an arbitrary number of occurrences; however, any alphabetic character will suffice.

Based on cardinality, there are three types of binary relationships: one-to-one, one-to-many, and many-to-many.

One-to-One

One-to-one relationships have 1 specified for both cardinalities. Suppose, we have two entity types: Driver and Vehicle. Assume that we are only concerned with the current driver of a vehicle, and that we are only concerned with the current vehicle that a driver is operating. Our two rules associate an instance of one entity type with at most one instance of the other entity type:

a driver operates at most one vehicle, and

a vehicle is operated by at most one driver.

and so the relationship is one-to-one.

Figure 7.20 One-to-one relationship

One-to-Many

One-to-many relationships are the most common ones in database designs. Suppose, we have customer entities and invoice entities and:

• an invoice is for exactly one customer, and
• a customer could have any number (zero or more) of invoices at any point in time.Because one instance of an Invoice can only be associated with a single instance of Customer, and because one instance of Customer can be associated with any number of Invoice instances, this is a one-to-many relationship:

Figure 7.21 One-to-many relationship

Many-to-Many

Suppose we are interested in courses and students and the fact that students register for courses. Our two rule statements are:

• any student may enroll in several courses,
• a course may be taken by several students.

This situation is represented as a many-to-many relationship between Course and Student:

Figure 7.22 Many-to-many relationship

As will be discussed again later, a many-to-many relationship is implemented in a relational database in a separate relation. In a relational database for the above, there would be three relations: one for Student, one for Course, and one for the many-to-many. (Sometimes this 3rd relation is called an intersection table, a composite table, a bridge table.)

Partly because of the need for a separate structure when the database is implemented, many modelers will ‘resolve’ a many-to-many relationship into two one-to-many relationships as they are modeling. We can restructure the above many-to-many as two one-to-many relationships where we have ‘invented’ a new entity type called Enrollment:

A student can have many enrollments, and each course may have many enrollments. An enrollment entity is related to one student entity and to one course entity.

Figure 7.23 Many-to-many becomes two one-to-many relationships

7.4.4 Recursive Relationships

A relationship is recursive if the same entity type appears more than once. A typical business example is a rule such as “an employee supervises other employees”. The supervises relationship is recursive; each instance of supervises will specify two employees, one of which is considered a supervisor and the other the supervised. 

In the following diagram, the relationship symbol joins to the Employee entity type twice by two separate lines. Note the relationship is one-to-many: an employee may supervise many employees, and, an employee may be supervised by one other employee.

Figure 7.24 Recursive relationship involving Employee twice

With recursive relationships, it is appropriate to name the roles each entity type plays. Suppose we have an instance of the relationship:

John supervises Terry

Then with respect to this instance, John is the supervisor employee and Terry is the supervised employee. We can show these two roles that entity types play in a relationship by placing labels on the relationship line:

Figure 7.25 Recursive relationship with role names

This one-to-many supervises relationship can be visualized as a hierarchy. In the following, we show five instances of the relationship: John supervises Lee, John supervises Peter, Peter supervises Don, Peter supervises Mary, and John supervises Noel.

Figure 7.26 The supervising hierarchy

In the above example, note the participation constraint at both ends of supervises is optional. This has to be the case because some employee will not be supervised, and, for some employees there are no employees they supervise.

Generally, recursive relationships are difficult to master. Some other situations where recursive relationships can be used:

• A person marries another person
• A person is the parent of a person
• A team plays against another team
• An organizational units report to another organizational unit
• A part is composed of other parts.

7.4.5 Identifying Relationships

When entity types were first introduced, we discussed an example where a department offers courses and that a course must exist in the context of a department. In that case, the Course entity type is considered a weak entity type as it is existence-dependent on Department. It is typical in such situations that the key of the strong entity type is used in the identification scheme for the weak entity type. For example, courses could be identified as MATH-123 or PHYS-329, or as Mathematics-123 or Physics-329. In order to convey the composite identification scheme for a weak entity type, we specify the relationship as an identifying relationship which is visualized using a double-lined diamond symbol:
Additionally, in situations where we have an identifying relationship, we usually have

• a weak entity type with a partial key
• a weak entity type that must participate in the relationship (total participation) and so the ERD for our hypothetical educational institution could be:

Figure 7.27 An identifying relationship

Note the keys for the strong entity type appear only at the strong entity type. The identifying relationship tells one that a department key will be needed to complete the identification of a course.

Exercises

1. Consider a company that owns and operates parking lots. Draw an ERD to include the following specifications. Each parking lot has a unique address (use the typical fields for addresses) and each parking lot has a certain number, say n, of parking spaces. Each space in a lot has a number between 1 and n. The cost of renting a parking space is the same for all spaces in a lot. The company rents individual spaces out to its customers. Each customer is identified by a driver’s license id, has a first and last name. Each customer will identify possibly several cars that they will park in the space rented to them. For each car the company needs to know the year, make, model, color and its license plate number.
2. Modify your model from the previous question to allow for scrambled parking. By this we mean that a customer is rented a space in a lot, but the customer may park in any available space.
3. Draw an ERD involving employees and their dependents where each employee has a unique id number and where dependents of the same employee are numbered starting at 1. It may be rare, but we will allow for dependents of the same employee to have the same name and birthdates. Include typical attributes for an employee, and for a dependent include the birthdate, first and last names.
4. Draw an ERD for marriages between two people. For persons include birthdate, first name, last name, and a unique person id. Consider marriage to be a relationship between two people and suppose we want our model to allow for people to have more than one marriage. Use the date of the marriage as a discriminator.
5. Consider marriages again but now let marriage be an entity type. Suppose when people marry there is a marriage certificate that is granted by a government authority. Include attributes applicable to a marriage.
6. Suppose we are modeling marriage as a relationship between two people. When, or under what circumstances, can we model this as a one-to-one relationship?
7. Draw an ERD that allows for marriages between possibly more than two people.
8. Consider the one-to-one operates relationship in this chapter. Modify the example so that drivers have attributes: driver license, name (which comprises first name and last name), and vehicles have attributes: license plate number, VIN, year, colour, make and model. Note thatVIN stands for vehicle identification number and this is unique for each vehicle. Assume that each driver must be assigned to a vehicle.
9. Consider the enroll in relationship used in this chapter. Suppose we must allow for a student to repeat a course to improve their grade. Develop an ERD and include typical attributes for student, course, etc. We need to keep a complete history of all course attempts by students.
10. What problems arise if one makes the supervises relationship mandatory for either the supervising employee or the employee who is supervised?
11. Consider requirements for teams, players and games, and develop a suitable ERD. Each team would have a unique name, have a non-player who is the coach, and have several players. Each player has a first and last name and is identified by a number (1, 2, 3, etc.). One player is designated the captain of the team. Assume a game occurs on some date and time, and is played by two teams where one team is called the home team and the other team is called the visiting team. At the end of the game the score must be recorded.
12. Modify your ERD for the above to accommodate a specific sport such as curling, baseball, etc.
13. Consider an ERD for modelling customers, phones, and phone calls. Each customer owns one phone and so the phone number identifies the customer. Include other attributes such as credit card number, first name, and last name for a customer. We must record information for each phone call that is made: for each call there is a start time, end time, and of course the phone number/customers involved.
14. Create an ERD suitable for a database that will keep genealogy data. Suppose there is one entity type Person and you must model the two relationships: marries and child of.
15. Develop an ERD to support home real estate sales. Consider there are several sales employees who list and sell properties. For each employee we need to know their name (first and last), the date they started working for this company, and the number of years they have been with the company. Each property has owners (one or more people), and may have certain features such as number of baths, number of levels, number of bedrooms. For each owner we must keep track of their names (first and last). Each property has an address; each address has the usual attributes: street (comprising apartment number, street number, street name), city, province, and postal code. A home is listed at a certain price and sold at possibly a different price. Of course, we need to track the names of the buyers, the date of a listing and the date of a sale.
16. Develop an ERD to keep track of information for an educational institution. Assume each course is taught by one instructor, and an instructor could teach several courses. For each instructor suppose we have a unique identifier, a first name, a last name, and a gender. Each course belongs to exactly one department. Within a department courses are identified by a course number. Departments are identified by a department code.
17. Develop an ERD to allow us to keep information on a survey. Suppose a survey will have several questions that can be answered true or false. Over a period of time the survey is conducted and there will be several responses.
18. Modify the ERD above to allow for surveys that have multiple choice answers.
19. Develop an ERD to support the management of credit cards. Each credit card has a unique number and has a customer associated with it. A customer may have several credit cards. The customer has a first name, last name, and an address. Each time a customer uses a credit card we must record the time, the date, the vendor, and the amount of money involved.
20. Modify the ERD for the above to accommodate the monthly billing of customers. Each month a customer receives a statement detailing the activity that month.
21. Develop an ERD to be used by a company to manage the orders it receives from its customers. Each customer is identified uniquely by a customer id; include the first name, last name, and address for each customer. The company has several products that it stocks and for which customers place orders. Each product has a unique id, unique name, unit price, and a quantity on hand. At any time a customer may place an order which will involve possibly many products. For each product ordered the database must know the quantity ordered and the unit price at that point in time. If the customer does this through a phone call then an employee is involved in the call and will be responsible for the order from the company side. Some orders are placed via the internet. For each order an order number is generated. For each order the database must keep track of the order number, the date the order was placed and the date by which the customer needs to receive the goods.