After completing a course in database management, you have been asked to develop a preliminary ERD for a symphony orchestra. You discover the following entity types that should be included:
- CONCERT SEASON The season during which a series of concerts will be performed. Identifier is Opening_Date, which includes Month, Day, and Year.
- CONCERT A given performance of one or more compositions. Identifier is of the following: Month, Day, Year, and Time. Each concert typically has more than one concert date.
- COMPOSITION Compositions to be preformed at each concert. Identifier is Composition_ID, which consists of the following: COMPOSER_Name and Composition_Name. Another attribute is Movement_ID, which consist of two parts: Movement_Number and Movement_Name. Many, but not all, compositions have multiple movements
- CONDUCTOR Person who will conduct the concert. Identifier is Conductor_ID. Another attribute in Conductor_Name.
- SOLOIST Solo artist who performs a given compostion on a particular concert. Identifier is Soloist_ID. Another attribute is Soloist_Name.
During further discussion you discover the following:
- A concert season schedules one or more concerts. The particular concert is scheduled for only one season.
- A concert includes the performance of one or more compositions. A composition may be performed at one or more concerts or may not be performed.
- For each concert there is one conductor. A conductor may conduct any number of concerts or may not conduct any concerts.
- Each composition may require one or more soloists or may not require a soloist. A soloist may perform one or more compositions at a given concert or may not perform any composition. The symphony orchestra wishes to record the date when a soloist last performed a given composition (Date_Last_Performed).
Draw a ERD to represent what you have discovered. Identify a business rule in this description and explain how this business rule is modeled on the E-R diagram. Also draw a data model for this situation using the Vision