CSE3330 Homework #3
Due on Thursday November 1 before midnight
Worth 5% of the final grade


Directions:

In your Entity-Relationship diagrams you need to identify:

Do not draw the participation or the structural constraints. Avoid the following common mistakes: to use two different entities for the same object; to represent a complex entity as a (multivalued) attribute of another entity (when the former entity is related to other entities); to have N:M identifying relationships for weak entities.

Problems:

  1. (30 points) Design the Entity-Relationship diagram of a movie database, which contains information about movies and actors/actresses. Each movie is identified by title and year of release. A movie has a length in minutes, a genre (drama, comedy, etc), a running time, and one director. A movie is made at a movie studio (such as MGM) and may have multiple producers (people in charge of the movie production). A movie studio has a unique name, one address, and may make one or more movies. Producers are identified by name and date of birth and produce one or more movies. Directors are also identified by name and date of birth and direct one or more movies. Assume that directors don't act or produce movies. Actors are identified by name and date of birth, and may appear in one or more movies. An actor who appears in a movie, has a role (eg, Sylvester Stallone had the role of ``Rocky Balboa'' in the movie Rocky). There may be one or more actors that appear in the movie and some of them may be in the lead role. Assume that there are no actors who are directors and/or producers.

  2. (35 points) Construct an E-R diagram for a bookstore database, which maintains information about books, professional journals, their authors, and publishers. Books and journals are collectively referred to as publications even though publications will not appear in the E-R diagram. All publications have a unique ISBN, a title, and a year of publication. No publication is published by more than one publisher. Books have one or more authors, whereas journals have a volume and a number. Journals contain articles, each of which is authored by one or more authors. Articles have a title and a subject category and no article can appear in more than one journal. Journal article authors are distinct from book authors. A book author may be employed by only one publisher. All authors have a unique SSN, a name, and a date of birth, whereas a publisher has a unique name, and an address.

  3. (35 points) Construct an Entity-Relationship diagram for a horse racing database. A horse has a name, a breed (eg, Mustang, Arabian, etc), a gender, and a price. A horse may also have one sire and one dam (the parent horses), may be owned by only one owner, and may be trained by only one trainer. An owner has a unique owner ID, a name, and a state of residence. An owner may own many horses. A horse trainer has a unique trainer ID, a name, and a state of residence. A trainer may train many horses. A jockey has a unique jockey ID, a name, a weight, a number of years of experience, and a state of residence. A horse race takes place on a certain date, has a type (eg, endurance, quarter horse, etc), and includes a number of race entries. Each race entry is associated with a certain jockey who rides a certain horse. A race entry has a gate number, the finish position (1st, 2nd, etc), and the amount won. Note that jockeys and horses may participate in multiple race entries.


How to Submit the Homework

You need to submit your homework solution online using the following web form. We do not accept email or hardcopy submissions. Acceptable document formats are: Microsoft word/powerpoint document, PDF, and postscript. You may draw your figures on paper, scan them with a scanner, and submit the PDF files of the scans. You may submit your files as many times as you like, but only the most recently submitted files will be retained and evaluated.

Submit Homework #3:

Last modified: 10/23/12 by Leonidas Fegaras