|CPSC 343||Database Theory and Practice||Fall 2017|
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.
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.
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.
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.
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.
Compare different representations for the characters-played-by-actors-say-quotes-in-movies concept.
Draw an EER diagram to capture the following:
In particular, is specialization appropriate here? A category type? Explain.
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.