CPSC 343 Database Theory and Practice Fall 2017

# Practice Problems - ER Modeling

1. Consider the E/R diagram below. (Click for a larger version.) Extract from the diagram the data requirements and constraints represented. Be as precise as possible.

(solution)

2. Consider a database whose schema is described by the E/R diagram below. (Click for a larger version.) Now imagine that the database has populated by some set of entitities and answer "true", "false", or "maybe" for each statement - "true" means the statement is true (because it must hold for any set of entities populating the database), "false" means the statement cannot be true (because it can't hold for any set of entities populating the database), and "maybe" means the statement could be true but isn't guaranteed to be (because whether or not it is true depends on the particular set of entities populating the database). Explain your answers.

 There are no actors who haven't been in any movies. There are some actors who have acted in more than 10 movies. Some actors have had a lead role in more than one movie. Every actor has had at least one lead role. Every movie has at least one actor. Every movie has exactly two lead actors. There are movies with more than a dozen actors. Most movies have one director and one producer. Some movies have one director but several producers. No movie has a director that has also acted in that movie. No director can direct more than one movie. Every director has also acted in some movie. No director can also act in the same movie. No producer has ever acted in a movie. Some producers have also been directors.

(solution)

3. The following E/R diagrams (labelled (a)-(d); click for a larger image) show four attempts to model a bank database where a customer may own multiple accounts and an account may have multiple owners but must have a single owner designated as the primary owner. Compare and contrast the designs. Do all of them accurately capture these requirements? Do some impose additional constraints that others do not? What about the other design principles? Explain.

(solution)

4. Draw an ER diagram to model a university database as described below. Represent the situation as accurately as possible, including participation, cardinality, and key constraints as appropriate. Also identify any attributes which are required (i.e. should be NOT NULL). Note any constraints present that you did not capture in the diagram along with a reason why they were omitted. If necessary specifics are lacking in the description of the situation, make reasonable assumptions and state those assumptions.

• Student information includes each student's name (required), ID number (required), social security number, current address and phone, permanent address and phone (required), birth date, sex, and class (first year, sophomore, junior, senior). Some applications need to refer to the city, state, and zip code of the student's permanent address and to the student's last name. Both the social security number and student ID number are unique for each student. Class is determined by the number of credits earned.

• A student's major(s) and minor(s) are also recorded. A major and a minor or second major are required to graduate.

• Course information includes the course name, course description, subject prefix (e.g. CPSC), course number, and number of credits. The subject prefix and course number uniquely identify each course. Every course must be offered by a department, and one course cannot be offered by more than one department.

• Each section of a course has an instructor, the term in which it is offered (a code which includes semester and year), and a section number. The section number distinguishes sections of the a course that are taught in the same term; its values are 1, 2, 3, etc up to the number of sections of that course taught in that term.

• Students register for particular sections of courses. At the end of the term, a grade for the course is recorded.

(solution)

5. The following is an ER diagram for the HWS registrar DB example. Is the Transcript entity type necessary or could the notion of a transcript and the associated information be captured another way? What are the tradeoffs? Discuss.

(solution)

6. Compare different representations for the characters-played-by-actors-say-quotes-in-movies concept.

(solution)

7. Draw an EER diagram to capture the following:

• Movies and TV shows are identified by a title, release year, and country.
• Movies, TV shows, and actors may have won or been nominated for awards (with the name of the award, year, category, and result - win or loss - included).

In particular, is specialization appropriate here? A category type? Explain.

(solution)

8. Draw an ER/EER diagram to model the situation described below. Represent the situation as accurately as possible, including participation, cardinality, and key constraints as appropriate. Also identify any attributes which are required (i.e. should be NOT NULL). Note any constraints present that you did not capture in the diagram along with a reason why they were omitted. If necessary specifics are lacking in the description of the situation, make reasonable assumptions and state those assumptions.

A horse has a registration number (by which it is identified), name, type (quarter horse or thoroughbred), gender, and trainer. A horse is only trained by one person, but a given trainer may train any number of horses. The heritage of every horse must also be maintained, if the information is available. For example, the mother (dam) and father (sire) should be recorded. It is also necessary to identify the offspring of a given horse.

A stable is a company that breeds, trains, and sells/buys horses. A stable has a unique identifier, and a name, phone, and contact person.

Information about people involved in the horse racing business should be maintained. An identifier, name, address, and phone number should be maintained about every person. If a person is a horse trainer, the salary of the trainer should be indicated along with the horses trained by the trainer. A horse trainer works for a specific stable. (A stable may employ many trainers.) If a person is a jockey, the weight of the jockey must be recorded, along with the date of the last recorded weight. It is sometimes necessary to know whether a person is both a trainer and a jockey. Name and contact information can also be maintained about people other than trainers and jockeys.

A horse can have more than one owner. An owner can own more than one horse. Information is always recorded about the most recent date and purchase price of a horse by its current owner(s). Owners must also record their percentage of ownership of a horse (which should not add up to more than 100%). An owner can be either a stable or a person.

Every race track has a race schedule indicating the date of each race day and the list of races for each race day. A race day typically has 10 scheduled races, where each race has a race number (from 1 to 10) and a purse. The purse is the amount of money awarded to the winner of the race.

Every race has several entries. Each entry indicates the horse, jockey, and gate position of the horse at the start of the race. After the race, the entry records the finishing position of the horse (first, second, third, etc). Every horse and every jockey must be able to produce a history of the races in which they have participated.

(solution)

 last updated: --Fri Sep 8 17:39:22 EDT 2017-- page owned by: bridgeman@hws.edu