Event Management System Database Design

The provided SQL assignment content defines five tables: EVENT, EVENT_MEDICAL_LOCATIONS, VOLUNTEERS, PARTICIPANTS, and MEDALS_ TYPE. The EVENT table stores information about events such as event ID, type, start and end dates/times, locations, route information, expected cost, and actual cost. The EVENT_MEDICAL_LOCATIONS table stores the location of medical facilities for each event. The VOLUNTEERS table contains information about volunteers including their ID, gender, name, qualification status, and wages. The PARTICIPANTS table holds details about participants such as participant ID, name, date of birth, gender, contact numbers, paid amount, dietary requirements, and address. The MEDALS_ TYPE table defines the types of medals that can be won by participants.

[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
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

3
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)

4
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)

5
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

6
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) ,

7
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) ,

8
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) ,

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