Database Modeling 1

Instructions

1. Construct an E-R model for orchestras that conforms to the specifications below. Start with the entities shown below, and to facilitate grading, put the entities in your diagram in the positions shown below (add space as needed). Add more entities if needed.

   Create a primary key for each entity (I recommend ConductorID, etc.) and underline it. Use meaningful verbs for relationship names (avoid “has”). Use this draw.io diagram as a starting point (see this video tutorial about draw.io). 20 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.

2. Convert the following E-R model to a set of tables. Give the answers using the format shown in the sample solution below. 10 points.

   

Collaboration Policy

The only help you may give or receive on this assignment is clarifying assignment requirements.

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 use black text on a white background (not white text). 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. Your report should have a cover sheet with the assignment name, the course number, your name, and the date. No signed academic integrity statement is needed since no “debugging” help is permitted.

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)