Exercise ER-1 What is the cardinality and existence of each of the following relationships in just the direction given? State any assumptions you have to make. 1. 2. 3. 4. 5.
Husband to wife Student to degree Child to parent Player to team Student to course
Answer to Exercise ER-1 The cardinality and existence are shown in the ER diagrams below. You did not have to draw these diagrams. You could have just used words to describe the cardinality and existence. 1
Only current marriages in system.
2
Students are entered into the database before they earn a degree. When a new degree is created, it is entered into the database before any students have earned it. Cardinality: 1-M. Existence: optional.
3
Optionality on the left might be mandatory if only parents parents are in the parent table. If parents-to-be are included, then the figure shown below is correct. Cardinality: 1-M. Remember, this is for child
to parent. If I had asked what's the cardinality between child and parent, you would say M-M. Existence: mandatory.
4
Mandatory on the left assumes that a team can join the league without any players. Mandatory on the right assumes that a player is not included until assigned to a team. All teams that a players has played for are recorded. Cardinality: 1-M. Existence: mandatory.
5
Students are included before they have even enrolled in a class. Courses are included before enrollment period has begun. Cardinality: 1-M. Existence: optional.
Exercise ER-2 For each of the following pairs of rules, identify two entity types and one relationship. State the cardinality and existence of the relationship in each case. If you don't think enough information is available to define either of these, then state an assumption that makes it clear. Draw the ER diagram. 1. 2. 3. 4. 5. 6. 7. 8.
A department employs many persons. A person is employed by, at most, one department. A manager manages, at most, one department. A department is managed by, at most, one manager. An author may write many books. A book may be written by many authors. A team consists of many players. A player plays for only one team. A lecturer teaches, at most, one course. A course is taught by exactly one lecturer. A flight-leg connects two airports. An airport is used by many flight-legs. A purchase order may be for many products. A product may appear on many purchase orders. A customer may submit many orders. An order is for exactly one customer.
Answer to Exercise ER-2 In each of the below I drew the ER diagram and assume that you can read the cardinality and existence off of the diagram. 1
The optional on the left could be left out. It is not stated in the problem. Assumption: A person does not have to be employed by a department.
2
The optional on both sides could be left out. It is not stated in the problem. Assumptions: A department does not have to have a manager. A manager does not have to manage a department.
3
The optional on both sides could be left out. It is not stated in the problem. Assumptions: An author does not have to write a book. A book does not have to have an author.
4
The mandatory on the right could be left out. It is not stated in the problem. Assumption: A team must have at least one player. FYI: If you see this type of ER diagram, you can know that it is not implemented this way. Ask yourself: If there aren't any teams or players in a database, and I need to enter my first team, then I can't because I don't have any players for it yet. Similarly, if I need to enter my first player, then I can't because I don't have any teams for him/her yet. So, generally, you'll see an optional on the many side of the relationship.
5
The optional on the right could be left out. It is not stated in the problem. Assumption: A lecturer does not have to teach any courses.
6
The straight-forward way of describing this situation is the following.
But, you know, I don't really like the above. It doesn't capture what's really going on with a flight leg. A flight leg takes off from exactly one airport and lands at exactly one airport — why not make it so that the ER diagram represents the situation and emphasizes these facts?
To me, the above is a more natural representation of the situation, though the original description of the problem leads you to answer the problem similar to the first presentation. 7
The existence information on each side is not stated in the problem. Assumptions: A product does not have to be on any purchase orders. A purchase order must have at least one product.
8
Everything you need is in the problem statement.
Exercise ER-3 Draw an ER diagram for the following. Be sure to indicate the existence and cardinality for each relationship. 1.
A college runs many classes. Each class may be taught by several teachers, and a teacher may teach several classes. A particular class always uses the sa me room. Because classes may meet at different times or on different evenings, it is possible for different classes to use the same room.
2.
Each employee in an engineering company has at most one recognized skill, but a given skill may be possessed by several employees. An employee is able to operate a given machine-type (e.g., lathe, grinder) if he has one of several skills, but each skill is associated with the operation of only one machine type. Possession of a given skill (e.g., mechanic, electrician) allows an employee to maintain several machine-types, although maintenance of any given machine-type requires a specific skill (e.g., a lathe must be maintained by a mechanic).
Answer to Exercise ER-3 1
2
Exercise ER-4 Draw an ER diagram for each of the following situations. On the diagram be sure to identify the cardinality, existence, and optionality (for subtypes) of each relationship. 1.
2. 3. 4.
5.
A company has a number of employees. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned, and may have several employees assigned. A university has a large number of courses in its catalog. Each course may have one or more other courses as prerequisistes, or may have no prerequisites. A college course may have one or more scheduled sections, or may not have a scheduled section. A hospital patient has a patient history. Each patient has one or more history records (we assume that the initial patient visit is always recorded as an instance of the history). Each patient history record belongs to exactly one patient. A video store may stock more than one copy of a given movie. It is also true that the store may not have a single copy of a paticular movie.
Answer to Exercise ER-4 1
2
3
4
5
Exercise ER-5 Draw an ER diagram to represent the following entity types and the natural relationships among them: Vehicle, Land-vehicle, Air-vehicle, Water-vehicle, Ocean-vessel, River-raft, Helicopter, Railvehicle, Road-vehicle, Car, Airplane, Bicycle. Answer to Exercise ER-5
Exercise ER-6 Draw an ER diagram that best represents the following situation. There are three types of accounts in a bank, with these attributes: 1. 2. 3.
Checking: Acct-no, Date-opened, Balance, Service-charge Savings: Acct-no, Date-opened, Balance, Interest-rate Loan: Acct-no, Date-opened, Balance, Acct-limit
Answer to Exercise ER-6
Exercise ER-7 Consider the following diagram:
1.
2.
What are the minimum and maximum number of Cs that can be associated with any one A? This is the same as asking “What are the existence and cardinality for the C to A relationship?” Substitute A:person and B:manage and C:project in the above ER diagram. Suppose that someone told you that the following three facts are true. Are these consistent with the diagram? Answer yes or no for each of the three facts. If your answer is no, then explain why it is inconsistent. 1. Mackenzie manages the White project. 2. Lindsey manages the Brown and Black projects.
3.
Joe doesn't manage any projects.
Answer to Exercise ER-7 1.
2.
The minimum number of C s that can be associated with one A is 1. I know this because the existence of the C to A relationship is mandatory. The maximum number of C s that can be associated with one A is unlimited (or many). I know this because the cardinality of the C to A relationship is 1-M. Answers 1. This is consistent with the diagram since Mackenzie is managing one project. A person must manage at least one project. 2. This is consistent with the diagram since Lindsey is managing two projects. A person must manage at least one project with no upper limit. 3. This is inconsistent with the diagram. The diagram states that a person must manage at least one project and Joe does not manage any.
Exercise ER-8 The following facts make up all of the leads relation between person and project :
Jennifer leads the Southpark project. Jennifer leads the Briarwood project. Don leads the Eastgate project.
You do not know whether or not there are any other people or any other projects. Which diagram(s) that is (are) consistent with this set of three facts. For example, you might answer “1, 4” if both 1 and 4 are consistent with the above facts.
1
2
3
4
Answer to Exercise ER-8
Diagrams 1, 2, 3, and 4 are consistent with the facts. Diagram 2 is consistent because, although Jennifer leads more than one project, the diagram is ambiguous as regards to cardinality — it merely points out that the existence is optional. Diagram 4 is also consistent with the facts because of its ambiguity regarding existence, this time in both directions.
Exercise ER-9 For each of the following sets of sentences, draw the corresponding ER diagram. 1.
2.
3. 4.
An account can be charged against many projects, t hough it may not be charged against any. A project must have at least one, though it may have many, accounts charged against it. Projects must be classified as either top secret or civilian (but not both). There is information specific to top secret projects and specific to civilian projects that we want to record. An employee must manage exactly one department. A department may or may not have one employee manage it. Men are only allowed t o supervise men. Women are only allowed to supervise women. We do not want to allow the database to hold data representing a man supervising a woman. An employee, regardless of sex, is assigned to exactly one office, with each office having exactly one employee in it. (Be sure to include the office entity in this diagram.)
Answer to Exercise ER-9 1
2
3
4
Exercise ER-10 The following descriptions all have to do with a holding company for food service companies. You should answer each one separately from the others. 1. 2.
3. 4. 5.
Each chain consists of 50 to 300 stores that are owned by ACTME, the holding company for several restaurant chains and two caterers. Menu items are wide ranging, and can be classified by the section of the menu (appetizer, dessert, etc.) in which each is presented and by ethnic group (Italian, Hungarian, etc.) to which it belongs. The menu of each restaurant changes every couple months; management likes to keep track of past menus to track which ones have been successful. Each menu item is made of several ingredients (eggs, bacon, etc.) that are used in a certain quantity. Ingredients can be acquired from several suppliers. Ingredients are acquired by sending orders for several goods using the vendor's item numbers. The item number for each of these ingredients varies across suppliers, so if you ar e going to order eggs from supplier # 1, then you might order item 52 while from supplier # 2 you might order item J216. The company keeps price and item numbers for all the ingredients for all its menu items — and even for some ingredients which it i s not currently using in any menu items. The quantity needed for each ingredient is also kept. The current price of these goods is maintained though the historical price is not. Because of special deals and volume discounts, the price at which a good is acquired is often different from its list price. Thus, the database must retain the price at which a good is actually acquired.
Answer to Exercise ER-10
