Question

Hey everyone

I'm working through my homework which involves creating a ER diagram based of certain business rules and I am stuck on this part:

Updated info

• 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.

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.

I can see that credit cards would be an attribute of customer entity but i'm not sure how this would relate to a ticket entity, an example diagram would really help me (UML must be used)

Thanks in advance!

Answer 1

Please find below the stops to created ERD in UML notation :

Customer credit card relates to ticket and will be stored in ticket entity instead of customer ntity as every ticket can have different credit card number.

Step 1: List normal entities:

1. Customer

2. Performance

3. Actor

4. Show

5. ProductionHouse

Step 2: List relationships between the entities:

1. Customer attends many Performance and a Performance attended by many Customers. It is M:N relationship. This will be stored in a new entity Ticket which will store additional details for ticket.

2. Performance has many Actors and an actor acts in many Performances, it is M:N relationship.

3. Show has many Producers and it can be assumed that a producer can produce many shows. It is a M:N relationship.

4. Understudy is a relation between actor to actor as below:

Understudy(mainActor_staff_id, understudying_actor_id, performance_no)

5. Some staff (which are producer) have one production house and a production house belongs to one producer, it is 1:1 relationship.

6. Show has many performances, but a performance belongs to one show, thus it is M:1 relationship.

Step 3: Add attributes to entities:

1. Customer( customer_no, name, phone_number)

2. Performance(performance_no, date, time, venue, show_title, show_year)

3. Actor(staff_id, first_name, last_name, DOB

4. Show(title, year , duration_in_mins)

Step 4: Additional Entities to store M:N relationships:

1. Ticket is a weak entity which will store customer, show and performance references for which the customer purchased the ticket.

Ticket(customer_no, show_title, show_year, performance_no, purchase_date, credit_card)

2. Actor has many roles in many performances which is stored as below:

PerformanceActor(performance_no, staff_id, role)

3. Understudy is a relation between actor to actor as below:

Understudy(mainActor_staff_id, understudying_actor_id, performance_no)

Step 4: Create ER Diagram: