Question

Case Study]

Showman House is a large event management company of New York. The company organizes various types of events throughout the year. The events types include fashion shows, celebrity shows, chat shows, musical extravaganza, exhibitions, fairs, and charity shows.

Showman House organizes events of different types. The details of the various event types are stored in the Event Types file. This file contains data about the event type codes and the corresponding event types. Any attendee who wishes to organize an event needs to provide details about the nature of the event he wants to organize. Along with providing event details, he also makes the payment for the event. The payment for an event is made in instalments, according to the fee plan, which is stored in the Fee Schedules file. The Fee Schedules file maintains details such as fee schedule id, event id, fee description, and fee amount. An attendee needs to pay all the instalments of the payment, on or before the start date of the event.

All details related to an event such as event code, event name, event type code, location of the event, start date, end date, event description, number of people, and the staffing required for the event are stored in the Events file. The payment details that include payment amount, payment date, payment method id, and payment method description are also stored in the Events file. The details of an attendee such as attendee id, attendee name, and address are stored in the Attendees file.

Any event at Showman House is managed by an employee. The employee details such as employee id, first name, last name, title, and phone are stored in the Employees file.

The management of Showman House realize that it is difficult to maintain so much data manually. Therefore, they have decided to computerize the entire system of event management.

Blue Moon Computers have been assigned the task to computerize the system. The project team needs to perform the following tasks:

a. Identify the attributes of the various entities involved. The attributes should completely define the

entities.

b. Identify the primary and foreign keys in the tables.

c. Draw a diagram to show the relationships between various tables.

Answer 1

Please find below the steps to obtain the tables and relationship between the tables:

a. List tables in the Showman House database and assign attributes

Entities: The system will have below entities:

· EventType: To store the types of event

· Event: To store the actual event details

· Payment: To store the payment details

· Attendee: To store the attendee who organises the event

· Employee: Showman House employee to manage the event

Attributes:

· EventType(type_code, name, description)

· Event(event_code, event_name,event_type, location, start_date, end_date, description, total_ people, required_staffing, attendee_id, employee_id)

· Payment( payment_id, amount, date, payment_method_id, method_description, event_code)

· Attendee(attendee_id, attendee_name, address)

· Employee(employee_id, first_name, last_name, title, phone)

**********************************************************

b. Identifying primary and foreign keys

Primary keys are bold and underlined. Foreign key has * before the attribute name

· EventType(type_code)

Foreign key: NA

· Event(event_code)

Foreign key: *event_type, *attendee_id, *employee_id references from EventType, Attendee and Employee respectively

· Payment( payment_id)

Foreign key: event_code from Event entity

· Attendee(attendee_id)

Foreign key: NA

· Employee(employee_id)

Foreign key: NA

**********************************************************

c. Diagram to show relationship:

Relationships:

Identified entities have below relationships:

1. An Event should have one EventType whereas there can be many Event to same EventType. It is a Many to One relationship.

2. An Attendee can have atleast one to many Events, but an event will have only one attendee. It is a One to Many relationship.

3. An Event should be managed by one Employee whereas there can be many Event managed by one Employee. It is a Many to One relationship.

4. An Event can have many Payments, whereas a payment must belong to one Event. It is a One to Many relationship.

Relationship diagram is shown as below: