CS386/586 Introduction to Databases
Fall 2011 Quarter

---

Assignment 4 ER Diagrams

Remember: The first midterm is Tuesday, 1 November, on material through week 5 of the class.
Due: Thursday, 27 October 2011 at the beginning of class

You may do this assignment individually or you may work with one partner.  That is, this assignment is to be completed by individuals or by teams of two students.  If you work with a partner, then you should turn in one assignment paper, with both of your names on the paper. You should only talk to the instructor, the TA and your partner about this assignment. You may also post questions to the course mailing list, cs386-list@cs.pdx.edu.

Please turn in your completed assignments on paper. Put your last name, first name, the assignment number in that order in the first line of your assignment.  List last name and first name for your partner, if you have one, on the second line of your assignment. (If you are working with a partner, turn in one assignment paper.)

Part I: Creating ER Diagrams

Question 1 (25 points): Consider the following scenario.

• There are televisions series, which have names, networks and production companies, and are identified by the name and network.
• A television series has one or more episodes, identified by episode number. Episodes also have a title and a length. No episode can exist without a corresponding television series.
• There are also movies. A movie is identified by its title and the year it was released. It also has a studio.
• An actor is identified by name and birth date, and also has a nationality.
• A writer is also identified by name and birth date, and also has a literary agency that represents him or her.
• An actor can appear as a ˇ°regularˇ± on a television series, a guest star on an episode, and a performer in a movie.
• An episode has a writer, and a movie has a writer.

Draw an ER diagram that represents this scenario. You can use conventional notation (as in the book) or UML notation. Be sure to mark the key attributes and include cardinality constraints on relationships.

For Questions 2-5 you should modify your ER diagram from Question 1 to handle the following changes in the scenario. Do each scenario as a separate change from the original diagram. You only need to show the parts of the diagram that change.

Question 2 (10 points): An episode can have two parts.

Question 3 (10 points): Television series run for one or more seasons and each episode is associated with a particular season.

Question 4 (10 points): A movie can be a sequel to another movie.

Question 5 (10 points): An actor plays a particular character in a television series, episode or movie.

Part II: Cardinality Constraints

Questions 6-8 concern the ER diagram below, representing medicines that contain active ingredients. For each different condition, show the ER diagram with the correct cardinality constraints. Show both maximum and minimum bounds.

Question 6 (5 points): Every medicine has exactly one active ingredient, and an ingredient is never used in more than one medicine.

Question 7 (5 points): A medicine can have any number of active ingredients, and every active ingredient is used in at least one medicine.

Question 8 (5 points): A medicine has up to five active ingredients (and may have none), and an ingredient can be used in any number of medicines.

Part III: Attributes versus Entities

Questions 9-12 all concern a club entity (with name, phone and office). Each club also has faculty advisor (with name and department) who is assigned up through a particular academic quarter (for example, Fall 2007).

Question 9 (5 points): Give an ER diagram that represents advisor information only using attributes.

Question 10 (5 points): Give an ER diagram that represents advisor information using a relationship and relationship attributes.

Question 11 (5 points): Give an ER diagram that represents advisor information using a 3-way relationship.

Question 12 (5 points): Suppose a club can have multiple advisors. Which of your ER diagrams in Questions 9-11 could represent this situation?