Database Design Assignment: Event Management System, SQL Commands

This assignment details the database design for an event management system. It includes the creation of various SQL tables, such as EVENT, VOLUNTEERS, STAFF, CLIENT_DETAILS, and PARTICIPANTS. The solution provides the SQL commands to create these tables, along with their respective fields, data types, and primary/foreign key relationships. It encompasses the design of an Entity-Relationship Diagram (ERD) to represent the database structure. The assignment covers the implementation of database schema, including the design of the tables and relationships between them to manage event details, participants, volunteers, staff, and other related information. The solution also includes the creation of tables for the management of medals and event winners. The solution also includes the creation of tables for the management of medals and event winners.

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