A fictitious friend of mine runs quite a number of Playhouses around Central Illinois. He schedules
famous plays at various playhouses and hires actors to act in these plays. He currently is trying to manage
his schedule with the following spreadsheets:
Theatre (TName, TAddress, TCity, Play Name, Play Writer, Date Wrote, Act Number, Act Name, Start
Date, End Date)
Actor (Actor ID, Actor Name, Actor Contact Info, In Play Name, Assigned to Role [in the Play], Prefers
Role [yes/no])
Some of the problems are what you would expect. First, there is too much redundancy here: theatre
information is repeated for each play that is scheduled there, play information is repeated for each act in
the play, etc. Also, there are a couple of things these spreadsheets do not handle well at all: 1) the
"Prefers Role" column in the Actor spreadsheet only works if the actor has been assigned the role; my
friend would like an actor to be able to list a preferred set of roles without actually having to been
assigned them; 2) currently, the actors can only be assigned to a play they need to be able to be assigned
to a particular scheduling of a play; 3) the actor may be assigned to a role in a play, but there is no
guaranatee that role actually exists in the play this has led to a large number of input errors. Lastly, be
aware these are some of the problems, but not all.
Your task is to build a database for this enterprise using the Entity Relationship Model. Specifically,
upon completion of this design, I want you to turn in the following:
1. A list of entities (with their attributes) and a list of relationships. You will need to write a short
paragraph describing each entity (be sure to be specific about what attributes are unique). A brief
description of each relationship is required as well.
2. An E-R Diagram for your solution, complete with the mapping labels.
3. A set of tables derived from your E-R Diagram. Give the name of each table along with its
columns. Make SURE the set of attributes of your tables is a separate listing from your list of
entity attributes. Also, make sure you describe how the set of tables was derived from the E-R
diagram, if it is not part of the normal algorithm. Highlight the primary key and any foreign
keys.
