Database Modeling 1

Instructions

1. Write two sentences for each relationship line in the following diagram, one for each direction, using the technique taught in class. 10 points.

   

2. Construct an E-R model for orchestras that conforms to the specifications below. Start with the entities shown below, and to facilitate my grading, put the entities in your diagram in the relative positions shown below. Add more entities if needed. Create a primary key for each entity (I recommend ConductorID, etc.). You may omit minimum cardinality indicators (but be sure to show cardinality maximums). Use meaningful verbs for relationship names (avoid “has”). Feel free to use this draw.io diagram as a starting point (see this video tutorial about draw.io). 30 points.

   

   An orchestra has a name and is directed by one or more conductors. A conductor has a name and salary. A conductor may conduct more than one orchestra. A conductor has a position for each orchestra he/she conducts. For example, a conductor may hold the position of associate conductor in one orchestra, and the position of assistant conductor in another.

   Each orchestra schedules one or more seasons of programs each year. A season is related to exactly one orchestra. It has a start and end date, and consists of several programs. Each program is presented in one season, and has a theme and a list of works performed on the program. A work has a title and a composer name and can be presented on multiple programs. A program can be presented in multiple performances. A performance of a program has a date, time, an assigned conductor, and venue. A venue has a name and a seating capacity.

3. Convert the following ERD to a set of tables. Give the answers using the format shown in the sample solution below. Indicate nullable foreign keys with a trailing question mark. 10 points.

   

4. Convert the following ERD to a set of tables. Follow the instructions for the previous question.

   

Submission

Create a neatly formatted report with your diagrams and associated design comments. Use a drawing tool to do the diagrams. I suggest draw.io, but you can use any tool you want. Your diagram must be black on a white background (not on a black background) to facilitate my grading. Diagrams that do not meet that requirement will be rejected and required to be redone in order to be graded.

Turn in a printed copy of your report.

Sample Solution to Conversion Problem

Home(Home, AgentID, SSN, Street, City, State, Zip, …)

FOREIGN KEY(AgentID) REFERENCES agent
FOREIGN KEY(SSN) REFERENCES owner

Agent(AgentID, OfficeID, Name, Phone)

FOREIGN KEY(OfficeID) REFERENCES office

Owner(SSN, Name, SpouseName, Profession, SpouseProfession)

Office(OfficeID, MgrName, Phone, Address)