Question

Designing and refining an Entity-Relationship Model
A company wants a simple database to record information about ticket sale for
theatre performances. They describe the key elements of their requirements in the
following points:
• Customers have a name, phone number, a credit card no, and a unique customer
number.
• Customers can attend many performances, and each performance can have many
customers attending.
• Each performance of a show is on at a specific date and time, at a venue.
• Each performance has many actors and the actors in each performance can vary.
• Actors have a staff id, first name, last name, and a date of birth.
• A show has a title, year and duration in minutes. While two shows could have the same
title, no two shows in the same year have the same title.
• Shows can have many producers, each with a staff id, first name, last name, date of
birth.
Based on the information you gathered, model the activities in your client's business and present
your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you
make. In your ER diagram, you must properly denote all applicable concepts, including weak or
strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and
participation constraints.
After presenting your ER model to the management, they pointed out that your model lacks the
ability to capture all requirements of the operation. In particular, they pointed out the following
shortcomings:
1. Whenever customers want to attend a performance they must purchase a ticket, which
records the purchase date. They can use different credit cards for different purchases.
The customer account must be created prior to purchasing a ticket, and tickets are not
transferable.
2. Tickets are for a specific performance of a show and identify the seat number, and a
status (to indicate if the ticket has been redeemed).
3. There may be cases where performances of a show run concurrently.
4. Actors have a specific role that they play in each performance of the show which must be
recorded in the system.
5. Actors must have one understudy, who will perform their role in cases where the primary
actor is unavailable (eg due to illness). An understudy can study under many primary
actors.
6. Producers may have a single production company which has a unique name and has an
address. Each production company belongs to a single producer.
Draw a modified ER diagram to accommodate these additional requirements.
Note: Your answer to this question should include TWO complete ER diagrams

Answer 1

First ER diagram before makiing changes is as follow:

After making given changes from management team:

here ER Diagram for all required attrributes given by management team are added.