SYSTEM ANALYSIS
System Summary:
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.
The management of Showman House realizes that it is difficult to maintain so
much data manually. Therefore, they have decided to computerize the entire
system of event management.
1. Identify the various entities involved.
2. Identify the attributes of the various entities. The attributes should
completely define the entities.
3. Draw an E/R diagram to demonstrate the relationship between the various
entities.
4. Map the E/R diagram to tables.
5. Normalize the tables to 3 NF.
6. Identify the primary and foreign keys in the tables.
7. Draw a diagram to show the relationships between various tables.
Showman House Page 1 of 16
ENTITIES
Number of entities : 5
Name of entities :
• Attendees
• Employees
• Event
• Payment
• Event Types
Showman House Page 2 of 16
ATTRIBUTES
Attributes:
Attendees
• Attendees ID
• First Name
• Last Name
• Phone
• Address
• City
• State
• Zip
• Country
Employees
• Employee ID
• First Name
• Last Name
• Title
• Phone
Showman House Page 3 of 16
Event
• Event ID
• Event Name
• Event Type
• Location
• Start Date
• End Date
• Staffing Required
• Event Description
• Employee ID
• Attendee ID
• No Of People
1. Payment
• Payment ID
• Event ID
• Payment Amount
• Payment Date
• Credit Card Number
• Card Holder’s Name
• Credit Card Exp Date
• Payment Method ID
• Description
• Fee Schedule ID
2. Event Types
• Event Type ID
• Description
Showman House Page 4 of 16
E/R DIAGRAM
Showman House Page 5 of 16
Showman House Page 6 of 16
TABLES
Number of tables: 6
Events (entity) Employees (entity) Payments (entity)
Employee ID
First Name
Last Name
Title
Phone
Showman House Page 7 of 16
Attendees (entity) Event Types (entity) Fee Schedules (relationship)
Attendee ID
First Name
Last Name
Address
City
State
Zip
Country
Phone
Showman House Page 8 of 16
TABLES AFTER 1 NF
Tables are already in 1 NF.
Events (entity) Employees (entity) Payments (entity)
Employee ID
First Name
Last Name
Title
Phone
Showman House Page 9 of 16
Attendees (entity) Event Types (entity) Fee Schedules (relationship)
Attendee ID
First Name
Last Name
Address
City
State
Zip
Country
Phone
Showman House Page 10 of 16
TABLES AFTER 2 NF
The tables are already in 2 NF as the attributes in each table depend on the primary key.
Events (entity) Employees (entity) Payments (entity)
Employee ID
First Name
Last Name
Title
Phone
Showman House Page 11 of 16
Attendees (entity) Event Types (entity) Fee Schedules (relationship)
Attendee ID
First Name
Last Name
Address
City
State
Zip
Country
Phone
Showman House Page 12 of 16
TABLES AFTER 3 NF
In the Suppliers, Ship-Payment Method ID and Ship-Description are not depending on
Suppliers table, we need to create another table, Payment Methods.
Events (entity) Employees (entity) Payments (entity)
Employee ID
First Name
Last Name
Title
Phone
Payment Methods
Payment Method ID
Description
Showman House Page 13 of 16
Attendees (entity) Event Types (entity) Fee Schedules (relationship)
Attendee ID
First Name
Last Name
Address
City
State
Zip
Country
Phone
Showman House Page 14 of 16
Events
Primary key : Event ID
Foreign keys : Employee ID, Attendee ID, Event Type ID
Attendees
Primary key : Attendee ID
Employees
Primary key : Employee ID
Event Types
Primary key : Event Type ID
Fee Schedules
Primary key : Fee Schedule ID
Foreign Key : Event ID
Payments
Primary key : Payment ID
Foreign Key : Event ID, Payment Method ID, Fee Schedule ID
Payment Methods
Primary key : Payment Method ID
Showman House Page 15 of 16
RELATIONSHIPS BETWEEN FINAL
Showman House Page 16 of 16
