Database Design Project

This assignment presents a comprehensive database design for an event management system. It includes an Entity-Relationship Diagram (ERD), a detailed data dictionary specifying data types and constraints for each table, and SQL create table commands to implement the design in a relational database. The rationale and assumptions behind the design choices are clearly explained, emphasizing the avoidance of data redundancy and the scalability of the design to accommodate future expansion. The database is designed to manage events (races and walks), participants, volunteers, staff, clients (sponsors), and medical locations. The design includes tables for events, participants, volunteers, staff, clients, medical locations, and relationships between them, such as event-client, event-participant, and event-volunteer relationships. The SQL commands provide a practical implementation of the design, allowing for the creation of the database and its tables.

[Type the document title]
Student Name:
Student ID:
Course Name:
Course ID:
Faculty Name:
University Name:

1
Contents
TaskT1: EERD.................................................................................................................................2
TaskT2: Rationale & Assumption...................................................................................................2
TaskT3: Data Dictionary.................................................................................................................3
TaskT4: SQL Tables Create Commands.........................................................................................6

2
TaskT1: EERD
TaskT2: Rationale & Assumption
I have designed database according to internal system. I have assumed that no duplicate data
entered in database. This is assumed that the staff will be medical staff who assists the
participants during events. If there are more than one departments in the event Management
Company then they can be added in database later, to do this a separate table department is
created. Here are only two types of events that company arranged race and walk. So a separate
table is not designed for this. There is an event table to store the total events with the event
related details. Client table is created to store the details for all sponsors who are associate with
the company. These sponsors invest money to start an event. There is a table which is created for
participants this table store the participants details like name, two emergencies contact, address
and dietary requirements and total paid amount for the event. A volunteers table is created to
store the volunteer’s details like name, contact, email address and their daily wedges.
Event_Medical_Locations table store all the medical centers which are available for this event.
Event_Winner table store the winners details in the event_winner table, this table store the
winner rank, winner won medal.

3
TaskT3: Data Dictionary
Table: EVENT
Column Name DataType Constraints
eventID INT PRIMARY KEY
eventType ENUM('RACE','WALK') NOT NULL
startDateTime TIMESTAMP NOT NULL
endDateTime TIMESTAMP NOT NULL
startLocation VARCHAR(60) NOT NULL
endLocation VARCHAR(60) NOT NULL
routeInformation VARCHAR(100) NOT NULL
expectedCost DOUBLE NOT NULL
actualCost DOUBLE NOT NULL

4
Table: EVENT_MEDICAL_LOCATIONS
Column Name DataType Constraints
eventID INT REFERENCES
EVENT(eventID)
location VARCHAR(20) UNIQUE
Table: VOLUNTEERS
Column Name DataType Constraints
volunteerID INT PRIMARY KEY
gender ENUM('M','F') NOT NULL
name VARCHAR(30) NOT NULL
qualificationStatus VARCHAR(20) NOT NULL
wadges DOUBLE NOT NULL
Table: DEPARTMENT
Column Name DataType Constraints
deptID INT PRIMARY KEY
depatName VARCHAR(50) NOT NULL
Table: STAFF
Column Name DataType Constraints
staffID INT PRIMARY KEY
staff_name VARCHAR(30) NOT NULL
gender ENUM('F','M') NOT NULL
dob DATE NOT NULL
doj DATE NOT NULL
salary DOUBLE NOT NULL
department_id INT REFERENCES
DEPARTMENT(deptID)

5
Table: CLIENT_DETAILS
Column Name DataType Constraints
clientID INT PRIMARY KEY
name VARCHAR(30) NOT NULL
gender ENUM('F','M') NOT NULL
emailID VARCHAR(80) NOT NULL
contact_1 VARCHAR(12) NOT NULL
contact_2 VARCHAR(12) NOT NULL
address VARCHAR(100) NOT NULL
Table: EVENT_CLIENT
Column Name DataType Constraints
eventID INT REFERENCES
EVENT(eventID)
clientID INT REFERENCES
CLIENT_DETAILS(clientID)
PRIMARY
KEY(eventID,clientID)
Table: PARTICIPANTS
Column Name DataType Constraints
participantID INT PRIMARY KEY
name VARCHAR(30) NOT NULL
dob DATE NOT NULL
gender ENUM('F','M') NOT NULL
contact_1 VARCHAR(12) NOT NULL
contact_2 VARCHAR(12) NOT NULL
paidAmount DOUBLE NOT NULL
dietaryRequirementDesc VARCHAR(100) NOT NULL
address VARCHAR(100)

6
Table: EVENT_PARTICIPANTS
Column Name DataType Constraints
eventID INT REFERENCES
EVENT(eventID)
participantID INT REFERENCES
PARTICIPANTS(participantID
)
raceTime DOUBLE DEFAULT NULL
positionn INT DEFAULT NULL
PRIMARY
KEY(eventID,participantID)
Table: MEDALS_TYPE
Column Name DataType Constraints
medalTypeID INT PRIMARY KEY
medalType VARCHAR(20) NOT NULL
Table: EVENT_WINNER
Column Name DataType Constraints
eventID INT REFERENCES
EVENT_PARTICIPANTS (eventID)
participantID INT REFERENCES
EVENT_PARTICIPANTS(participantID)
medalTypeID INT REFERENCES
MEDALS_TYPE(medalTypeID)
PRIMARY KEY
(eventID,participantID,medalTypeID)
TaskT4: SQL Tables Create Commands

7
CREATE DATABASE EVENT_MGMT_SYSTEM;
USE EVENT_MGMT_SYSTEM;
CREATE TABLE EVENT(
eventID INT PRIMARY KEY,
eventType ENUM('RACE','WALK') NOT NULL,
startDateTime TIMESTAMP NOT NULL,
endDateTime TIMESTAMP ,
startLocation VARCHAR(60) NOT NULL,
endLocation VARCHAR(60) NOT NULL,
routeInformation VARCHAR(100) NOT NULL,
expectedCost DOUBLE NOT NULL,
actualCost DOUBLE
);
CREATE TABLE EVENT_MEDICAL_LOCATIONS(
eventID INT REFERENCES EVENT(eventID) ,
location VARCHAR(20) UNIQUE
);
CREATE TABLE VOLUNTEERS(
volunteerID INT PRIMARY KEY,
gender ENUM('M','F') NOT NULL,
name VARCHAR(30) NOT NULL,
qualificationStatus VARCHAR(20) NOT NULL,
wadges DOUBLE NOT NULL
);
CREATE TABLE EVENT_VOLUNTEERS(
eventId INT REFERENCES EVENT(eventID) ,

8
volunteerID INT REFERENCES VOLUNTEERS(volunteerID) ,
primary key( eventID, volunteerID)
);
CREATE TABLE DEPARTMENT(
deptID INT PRIMARY KEY ,
depatName VARCHAR(50) NOT NULL
);
CREATE TABLE STAFF(
staffID INT PRIMARY KEY,
staff_name VARCHAR(30) NOT NULL,
gender ENUM('F','M') NOT NULL,
dob DATE NOT NULL,
doj DATE NOT NULL,
salary DOUBLE NOT NULL,
department_id INT REFERENCES DEPARTMENT(deptID)
);
CREATE TABLE CLIENT_DETAILS(
clientID INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
gender ENUM('F','M') NOT NULL,
emailID VARCHAR(80) NOT NULL,
contact_1 VARCHAR(12) NOT NULL,
contact_2 VARCHAR(12)NOT NULL,
address VARCHAR(100) NOT NULL
);
CREATE TABLE EVENT_CLIENT(
eventID INT REFERENCES EVENT(eventID) ,
clientID INT REFERENCES CLIENT_DETAILS(clientID) ,

9
PRIMARY KEY(eventID,clientID)
);
CREATE TABLE PARTICIPANTS(
participantID INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
dob DATE NOT NULL,
gender ENUM('F','M') NOT NULL,
contact_1 VARCHAR(12) NOT NULL,
contact_2 VARCHAR(12) NOT NULL,
paidAmount DOUBLE NOT NULL,
dietaryRequirementDesc VARCHAR(100) NOT NULL,
address VARCHAR(100)
);
CREATE TABLE EVENT_PARTICIPANTS(
eventID INT REFERENCES EVENT(eventID) ,
participantID INT REFERENCES PARTICIPANTS(participantID) ,
raceTime DOUBLE,
positionn INT,
PRIMARY KEY(eventID,participantID)
);
CREATE TABLE MEDALS_TYPE(
medalTypeID INT PRIMARY KEY,
medalType VARCHAR(20) NOT NULL
);
CREATE TABLE EVENT_WINNER(
eventID INT REFERENCES EVENT_PARTICIPANTS (eventID) ,
participantID INT REFERENCES EVENT_PARTICIPANTS(participantID) ,
medalTypeID INT REFERENCES MEDALS_TYPE(medalTypeID) ,

10
PRIMARY KEY (eventID,participantID,medalTypeID)
);