University Database Design Assignment: Event Management System

This assignment presents a comprehensive database design for an event management system. It includes an Entity-Relationship Diagram (EERD) illustrating the entities and their relationships, a rationale outlining design choices and assumptions, and a detailed data dictionary defining each table's structure, including column names, data types, and constraints. Furthermore, the assignment provides SQL create table commands to implement the database schema, covering tables for events, medical locations, volunteers, departments, staff, client details, participants, and medal types, along with tables to manage relationships between these entities such as event-client, event-participants, and event-winner. The database design is intended to efficiently store and manage event-related information, participant details, and associated data.

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