Question

For the database for a symphony orchestra, develop an ER diagram to keep track of all business rules specified below:

Strong Entity Types:

Customer: The person who purchases season ticket(s). Attributes are Customer D (Identifier), Name, Address, Phone.

Concert Season: The season during which a series of concerts will be performed. The identifier is Opening Date, which includes Month, Day, and Year

Concert: A given performance of one or more compositions. Identifier is Concert Number. Another attribute is Concert Date, which consists of Month, Day, and Year, and Time. Each concert typically has more than one concert date.

Composition: Compositions to be performed at each concert. The identifier is Composition ID, which consists of Composer Name and Composition Name. Another Attribute is Movement ID, which consists of Movement Number and Movement Name. Many, but not all, compositions have multiple movements.

Conductor: Person who will conduct the concert. The identifier is Conductor ID. Another attribute is the Conductor Name.

Soloist: Solo artist who performs a given composition on a particular concert. The identifier is Soloist ID. Another attribute is the Soloist Name.

Promotion: Promotion program to be used to raise the sales of season tickets. The identifier is Promotion Code. Other attributes are Description and Expiration Date.

During the further discussions, you discover the following business rules:

 A concert season schedules one or more concerts. A particular concert is scheduled for only one concert season.

 A customer may or may not purchase a season ticket at a certain year using a specific promotion. A concert season may have many customers with season tickets or may not have any customers with season tickets. A certain promotion may be used by many customers in purchasing season tickets or may not be used by any customers.

 You need to keep track of the season ticket price paid by a customer as well as the seat class purchased by the customer using a certain promotion for a given concert 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 concert may have one or more soloists or may not have a soloist. However, a soloist must perform at least one composition at a concert.

 The symphony orchestra wishes to record the date when a soloist last performed a given composition at a particular concert (DATE_LAST_PERFORMED).

Answer 1

Solution:

ER diagram:

Explanation:

From the above ER diagram,

The entities are mentioned in rectangle box attributes are mentioned in oval shape.

The unique attributes are underlined

The relationship between each entity mentioned in diamond shape