Design Database for Event Management System

The assignment content describes the creation of a database for an event management system. The database includes several tables: EVENT, EVENT_MEDICAL_LOCATIONS, VOLUNTEERS, EVENT_VOLUNTEERS, DEPARTMENT, STAFF, CLIENT_DETAILS, EVENT_CLIENT, PARTICIPANTS, EVENT_PARTICIPANTS, MEDALS_TYPE, and EVENT_WINNER. Each table is designed to store specific data related to events, volunteers, staff, clients, participants, and medals.

[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.................................................................................................................2
TaskT4: SQL Tables........................................................................................................................2

2
TaskT1: EERD
TaskT2: Rationale & Assumption
TaskT3: Data Dictionary

3
TaskT4: SQL Tables Create Commands
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,

4
qualificationStatus VARCHAR(20) NOT NULL,
wadges DOUBLE NOT NULL
);
CREATE TABLE EVENT_VOLUNTEERS(
eventId INT REFERENCES EVENT(eventID) ,
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,

5
address VARCHAR(100) NOT NULL
);
CREATE TABLE EVENT_CLIENT(
eventID INT REFERENCES EVENT(eventID) ,
clientID INT REFERENCES CLIENT_DETAILS(clientID) ,
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

6
);
CREATE TABLE EVENT_WINNER(
eventID INT REFERENCES EVENT_PARTICIPANTS (eventID) ,
participantID INT REFERENCES EVENT_PARTICIPANTS(participantID) ,
medalTypeID INT REFERENCES MEDALS_TYPE(medalTypeID) ,
PRIMARY KEY (eventID,participantID,medalTypeID)
);