Database Design and Implementation for Event Management System

This assignment presents a comprehensive database design and implementation for an event management system. The solution begins with a revised schema, incorporating new tables like 'Guests' and 'Helpers,' and modifying existing relations to optimize data handling. Detailed table definitions are provided, outlining column names, data types, constraints, and integrity rules. The solution then includes the SQL code to create the database tables, insert sample data, and create various database views. The views are designed to retrieve specific data subsets, such as service details, member information, and event-related data. The assignment demonstrates practical SQL skills in creating, inserting, and querying data, providing a complete solution for students studying database design and implementation. The assignment encompasses different aspects of database management, from initial schema design to data population and query creation, providing a holistic understanding of database systems.

PART 1 :
Since all the shortlisting and swapping is to be handled internally so the relations for them has been
dropped as they are no longer required.
A new relation Guests is added to the database for those who are not a member and are eager to
attend the event.
A helper table is added for the volunteers for the event.
Some attributes that are added to the EVENT table includes OrganiserID, LeaderID, Category,
Postcode and Instructions.
SCHEMA:
SERVICES (serviceID, Category, Task Name)
MEMBERS (WorkerID, Name, Address, Suburb, Email, Contact, Biography, ServiceID, Duration)
GUESTS (GuestID, Name, Suburb, Email, Contact)
EVENT (EventID, OrganiserID, LeaderID, Title, Description, Category, Date, Location, Postcode,
Instruction, Number of People)
HELPER (HelperID, EventID)
ATTENDENTS (AttendentID, EventID)

PART 2:
SERVICES
Column
Name
Descriptio
n
Data
type
Domain Defaul
t value
Require
d
Uniqu
e
Constrain
t
Integrit
y Rule
ServiceID Uniquely
defines a
service
NUMBER Three
digit
number
- Y Y PRIMARY
KEY
-
Category Type of
Service
VARCHA
R
Characte
r string
length
15
- N N - -
TaskNam
e
Specific
service
VARCHA
R
Characte
r string
length
30
- N N - -
MEMBERS
Column
Name
Descriptio
n
Data
type
Domain Defaul
t value
Require
d
Uniqu
e
Constrain
t
Integrit
y Rule
WorkerI
D
Uniquely
defines a
Member
NUMBER Three
digit
number
- Y Y PRIMARY
KEY
-
Name Name of
the
Member
VARCHA
R
Characte
r string
length
15
- N N - -
Address Member’s
address
VARCHA
R
Characte
r string
length
30
- N N - -
Suburb suburb VARCHA
R
Characte
r string
length
15
- N N - -
Email Member’s
email id
VARCHA
R
Characte
r string
length
30
- N N - -
Contact Member’s
contact
NUMBER 10 digit
number
- N N - -
Biograph
y
A brief
about
Member
VARCHA
R
Characte
r string
length
50
- N N - -
ServiceID The
service he
provides
NUMBER Three
digit
number
- N N FOREIGN
KEY
On
delete
Duration Duration VARCHA Characte - N N - -

when the
member is
free
R r string
length
25
GUESTS
Column
Name
Descriptio
n
Data
type
Domain Defaul
t value
Require
d
Uniqu
e
Constrain
t
Integrit
y Rule
GuestI
D
Uniquely
defines a
Guest
NUMBER Three
digit
number
- Y Y PRIMARY
KEY
-
Name Name of
the
Member
VARCHA
R
Characte
r string
length 15
- N N - -
Suburb suburb VARCHA
R
Characte
r string
length 15
- N N - -
Email Member’s
email id
VARCHA
R
Characte
r string
length 30
- N N - -
Contact Member’s
contact
NUMBER 10 digit
number
- N N - -
EVENT
Column Name Descriptio
n
Data
type
Domain Defau
lt
value
Require
d
Uniqu
e
Constrai
nt
Integrit
y Rule
EventID Uniquely
defines a
Guest
NUMBE
R
Three
digit
number
- Y Y PRIMAR
Y KEY
-
OrganiserID MemberI
D of the
organiser
NUMBE
R
Three
digit
number
- N N FOREIGN
KEY
On
delete
LeaderID MemberI
D of the
Event
leader
NUMBE
R
Three
digit
number
- N N FOREIGN
KEY
On
delete
Title Title of
the Event
VARCHA
R
Charact
er string
length
15
- N N - -
Description Descriptio
n of the
event
VARCHA
R
Charact
er string
length
50
- N N - -
Category Category
the event
falls in
VARCHA
R
Charact
er string
length
20
- N N - -

Dates Date on
which the
event will
get
organised
DATE Date - N N - -
Location Venue VARCHA
R
Charact
er string
length
50
- N N - -
Postcode A 6 digit
postal
code
NUMBE
R
six digit
number
- N N - -
Instruction Instructio
ns to the
attendent
s
VARCHA
R
Charact
er string
length
50
- N N - -
NumberofPeo
ple
Max
number
of people
allowed
to attend
the event
NUMBE
R
2 digit
number
- N N - -
Contact Member’s
contact
NUMBE
R
10 digit
number
- N N - -
HELPER
Column
Name
Description Data
type
Domai
n
Defaul
t
value
Require
d
Uniqu
e
Constraint Integrit
y Rule
HelperI
D
Uniquely
defines a
Helper/volunte
er
NUMBE
R
Three
digit
numbe
r
- Y Y PRIMARY
KEY/FOREIG
N KEY
On
update
EventID EventID NUMBE
R
Three
digit
numbe
r
- N N FOREIGN
KEY
On
delete
ATTENDENTS
Column
Name
Descriptio
n
Data
type
Domai
n
Defaul
t
value
Require
d
Uniqu
e
Constraint Integrit
y Rule
AttendentI
D
Uniquely
defines a
attendee
NUMBE
R
Three
digit
numbe
r
- Y Y PRIMARY
KEY/FOREIG
N KEY
On
update
EventID EventID NUMBE
R
Three
digit
- N N FOREIGN
KEY
On
delete

numbe
r

PART 3:
CREATE TABLE SERVICES(ServiceID NUMBER(3) NOT NULL, Category VARCHAR(15), TaskName
VARCHAR(30), CONSTRAINT services_pk PRIMARY KEY(ServiceID));
CREATE TABLE MEMBERS(WorkerID NUMBER(3) NOT NULL, Name VARCHAR(15), Address
VARCHAR(30), Suburb VARCHAR(15), Email VARCHAR(30), Contact NUMBER(10), Biography
VARCHAR(50), ServiceID NUMBER(3), Duration VARCHAR(25), CONSTRAINT workers_pk PRIMARY
KEY(WorkerID), CONSTRAINT fk_services FOREIGN KEY(ServiceID) REFERENCES SERVICES(ServiceID));
CREATE TABLE GUESTS(GuestID NUMBER(3) NOT NULL, Name VARCHAR(15), Suburb VARCHAR(15),
Email VARCHAR(30), Contact NUMBER(10), CONSTRAINT guests_pk PRIMARY KEY(GuestID));
CREATE TABLE EVENT(EventID NUMBER(3) NOT NULL, OrganiserID NUMBER(3), LeaderID
NUMBER(3), Title VARCHAR(15), Description VARCHAR(50), Category VARCHAR(20), Dates DATE,
Location VARCHAR(20), Postcode NUMBER(6), Instruction VARCHAR(25), NumberofPeople
NUMBER(3), CONSTRAINT event_pk PRIMARY KEY(EventID), CONSTRAINT fk_workerso FOREIGN
KEY(OrganiserID) REFERENCES MEMBERS(WorkerID), CONSTRAINT fk_workersl FOREIGN
KEY(LeaderID) REFERENCES MEMBERS(WorkerID));
CREATE TABLE HELPER(HelperID NUMBER(3) NOT NULL, EventID NUMBER(3), CONSTRAINT
helper_pk PRIMARY KEY(HelperID), CONSTRAINT fk_workerhelper FOREIGN KEY(HelperID)
REFERENCES MEMBERS(WorkerID), CONSTRAINT fk_eventhelper FOREIGN KEY(EventID) REFERENCES
EVENT(EventID));
CREATE TABLE ATTENDENTS(AttendentID NUMBER(3) NOT NULL, EventID NUMBER(3), CONSTRAINT
attendent_pk PRIMARY KEY(AttendentID), CONSTRAINT fk_workerattendents FOREIGN KEY(GuestID)
REFERENCES MEMBERS(WorkerID), CONSTRAINT fk_guestattendents FOREIGN KEY(GuestID)
REFERENCES GUESTS(GuestID), CONSTRAINT fk_eventattendent FOREIGN KEY(EventID) REFERENCES
EVENT(EventID));
INSERT INTO SERVICES VALUES(101,'Maintainence','Weeding');
INSERT INTO SERVICES VALUES(102,'Equipment Loan','Mulchers');
INSERT INTO SERVICES VALUES(103,'Landscaping','Paving');
INSERT INTO SERVICES VALUES(104,'Pickup&Delivery','Taking Rubbish');
INSERT INTO SERVICES VALUES(105,'Maintainence','Watering');
INSERT INTO MEMBERS VALUES(201,'Michael','221 Baker
Street','Downtown','[e-mail]','9876543210','My name is Michael.','103','any Sunday');
INSERT INTO MEMBERS VALUES(202,'Mishra','43 Palm
Road','Uptown','[e-mail]','7896543210','My name is Mishra.','101','by arrangement');
INSERT INTO MEMBERS VALUES(203,'Rehman','65 Eastern
Street','Tomberstone','[e-mail]','9786543210','My name is Rehman.','105','Aug-Oct');

INSERT INTO MEMBERS VALUES(204,'Abhinav','225 Baker
Street','Downtown','[e-mail]','8976543210','My name is Abhinav.','102','any Sunday');
INSERT INTO MEMBERS VALUES(205,'Kaley','02 Pam
Road','Uptown','[e-mail]','8796543210','My name is Kaley.','104','any Saturday');
INSERT INTO GUESTS VALUES(301,'Rohan','Uptown','[e-mail]','7892565232');
INSERT INTO GUESTS VALUES(302,'Tom','Tomberstone','[e-mail]','9872565232');
INSERT INTO GUESTS VALUES(303,'Harward','Uptown','[e-mail]','8972565232');
INSERT INTO GUESTS VALUES(304,'Robin','Tomberstone','[e-mail]','7982565232');
INSERT INTO GUESTS VALUES(305,'Rehana','Downtown','[e-mail]','7652565232');
INSERT INTO EVENT VALUES(401,205,205,'Maintainence','Breif overview of Maintainence','Open
Gardens',TO_DATE('2019/07/02','yyyy/mm/dd'),'Event Garden 2',523124,'don’t forget your hat and
sunscreen',20);
INSERT INTO EVENT VALUES(402,203,201,'Loans','Breif overview of Loans','Skills and
Techniques',TO_DATE('2019/08/02','yyyy/mm/dd'),'Event Garden 1',592589,'don’t forget your hat
and sunscreen',20);
INSERT INTO EVENT VALUES(403,201,204,'Landscaping','Breif overview of Landscaping','Know Your
Plants',TO_DATE('2019/07/15','yyyy/mm/dd'),'Event Garden 3',487898,'don’t forget your hat and
sunscreen',20);
INSERT INTO EVENT VALUES(404,202,203,'PickupsDelivery','Breif overview of Pickup and
Delivery','Open Gardens',TO_DATE('2019/07/22','yyyy/mm/dd'),'Event Garden 5',489898,'don’t
forget your hat and sunscreen',20);
INSERT INTO HELPER VALUES(201,401);
INSERT INTO HELPER VALUES(205,404);
INSERT INTO HELPER VALUES(203,403);
INSERT INTO ATTENDENTS VALUES(301,403);
INSERT INTO ATTENDENTS VALUES(304,402);
INSERT INTO ATTENDENTS VALUES(201,404);

PART 4:
1. CREATE VIEW viewA AS
SELECT *
FROM MEMBERS
ORDER BY Name;
2. CREATE VIEW viewB as
SELECT s.Category, s.TaskName, m.Name, m.Contact
FROM SERVICES s INNER JOIN MEMBERS m
WHERE s.ServiceID=m.ServiceID;
3. CREATE VIEW viewC as
SELECT *
FROM ATTENDENTS a INNER JOIN MEMBERS m ON a.AttendentID=m.WorkerID
INNER JOIN GUESTS g ON a.AttendentID=g.GuestID;
4. CREATE VIEW viewD as
SELECT e.Title, e.Dates, MAX(COUNT(e.NumberofPeople))
FROM EVENT e RIGHT JOIN ATTENDENTS a
ON e.EventID=a.EventID;
5. CREATE VIEW viewE as
SELECT e.Title, e.Dates, e.Location
FROM EVENT e
WHERE e.Postcode = 487898 OR TO_DATE(SYSDATE,e.Dates)<=1;
6. CREATE VIEW viewF AS
SELECT *
FROM EVENT e
WHERE DESCRIPTION LIKE ‘roses’;
7. CREATE VIEW viewG AS
SELECT m.*
FROM MEMBERS m INNER JOIN HELPER h ON m.WorkerID=h.HelperID
INNER JOIN EVENT e ON m.WorkerID=e.OrganiserID AND m.WorkerID=e.LeaderID
8. CREATE VIEW viewH AS
SELECT e.Title, e.Dates, m.Name
FROM EVENT e INNER JOIN MEMBERS m
ON e.LeaderID = m.WorderID ;
9. CREATE VIEW viewI AS
SELECT g.*
FROM GUESTS g INNER JOIN EVENT e ON e.EventID=g.EventID
WHERE g.Suburb <> e.Suburb;
10. CREATE VIEW viewj AS
SELECT *
FROM SERVICES
WHERE Category IS NULL;
CREATE VIEW viewA AS
SELECT EVENT.*
FROM EVENT LEFT JOIN ATTENDENTS

ON EVENT.EventID = ATTENDENTS.EventID;
CREATE VIEW viewB AS
SELECT *
FROM MEMBERS
WHERE WorkerID = (SELECT HelperID FROM HELPER);