Database Design and Development Project: Edge Entertainment Project

This project focuses on designing and developing a database for Edge Entertainment, a company that organizes and facilitates festivals. The solution includes an entity-relationship diagram, normalization up to 3NF, and a data dictionary. SQL is used to create tables, insert data for festivals, vendors, equipment, stages, slots, and artists. Several SQL queries are provided to retrieve specific information, such as artists performing at a particular festival, dates and time slots for jazz artists, and equipment used on specific stages. The database is also modified to include ticket prices and track revenue data. The project concludes with a discussion of how the database meets the business requirements of Edge Entertainment.

COVER PAGE

Contents
Task 1 – Design............................................................................................................................................3
a) Entity relationship diagram..............................................................................................................3
b) Normalization up to 3NF..................................................................................................................3
c) Data dictionary................................................................................................................................4
Task 2 Data and Queries..............................................................................................................................5
a) Tables using SQL..............................................................................................................................5
b) Enter data for festivals and the days they are running....................................................................7
c) data for vendors..............................................................................................................................8
d) Data on equipment and equipment types.......................................................................................9
e) Data for stages, slots, artists at all festivals...................................................................................10
f) Festivals with their dates...............................................................................................................15
g) Artists performing at Leyton Fest..................................................................................................15
h) Write a query that selects all the dates and time slots for Jazz artists at all festival.....................16
i) Write a query that shows all the rigging equipment used on CF-Stage 1 at the Carson festiva.....16
j) Write a query that counts number of vendors at the Wonderland Rock Festival..........................16
k) Write a query to show all Food and Drink vendors allocated to festivals in July 2018..................17
I) Update the Carson Festival so that the rock band ‘Alonso’ replace ‘Dark Sound’. They should have
the same slot, date and stage as ‘Dark Sound’......................................................................................17
m) Alter the database so that it keeps a record of the ticket price for each festival.......................17
Task 3 Derived Data...................................................................................................................................17
Task 4........................................................................................................................................................18

Task 1 – Design
a) Entity relationship diagram
b) Normalization up to 3NF
Normalization up to 3NF involves performing three steps on the relations described in the entity relation
diagram. The following are the steps followed in order to achieve relations in 3NF.
 Converting relations to 1NF- converting relations to 1NF involves elimination of all the repeating
groups in every relation described in the entity relationship diagram. After the repeating groups
are removed, a primary key is identified for every relation.
 Converting relations achieved in 1NF to 2NF- After completing converting the relations to 1NF
the next step is to normalize the relations to 2NF. Normalization to 2NF involves eliminating
partial dependencies that exist in any relation. Partial dependencies are dependencies that
occur when a relation has a combination of attributes that can act as the candidate key for the
relation despite the relation already having a primary key which was identified in 1NF. All there
partial dependencies to transform the relations to 2NF.
 Converting relations achieved in 2NF to 3NF- After conversion of all relations to 2NF, the next
and last step is to normalize the relations to 3NF. Conversion to 3NF involves eliminating
transitive dependencies that may exist in any of the relations achieved in 2NF. Transitive occur
in relation when that relation has an attribute that can determine another attribute apart from
the key attribute which determines all the other attributes in the relation. Converting relations
to 3NF is basically eliminating all the transitive dependencies thus decomposing the tables into
normalized relations which are in 3NF.

For the festival database, the relations described were normalized up to 3nF by following the steps
described above to achieve the following relations that are in 3NF. At this level, the relations can be
converted into tables to form the complete festival database.
 Festival (festivalID, name , date)
 festivalDays (daySerialNO, day, festivalID)
 vendor (vendorID, name, type)
 festivalVendors (festivalID, vendorID)
 stage (stageID, name,festivalID)
 equipment (equipmentID, name, type)
 stage_equipments (stageID,equipmentID)
 artist (artistID, name,typeOfMusic)
 slots (slotID, daySerialNO, stageID, startTime, artistID)
c) Data dictionary
Entity Attribute Data Type Constraints
Festival festivalID Integer PK (festivalID)
name VARCHAR2(50)
festivalcredate Date
FestivalDays daySerialNO Integer PK (daySerialNO)
day Integer
festivalID Integer FK (festivalID) References festival (festivalID)
vendor vendorID Integer PK (vendorID)
name VARCHAR2(50)
Type VARCHAR2(50)
festivalVendors festivalID Integer PK (festivalID, vendorID)
FK (festivalID) References festival (festivalID)
vendorID Integer PK (festivalID, vendorID)
FK (vendorID) References vendor (vendorID)
stage stageID Integer PK (stageID)
Name VARCHAR2(50)
festivalID Integer FK (festivalID) References festival (festivalID)
equipment EquipmentID Integer PK (EquipmentID)
Name VARCHAR2(50)
Type VARCHAR2(50)
Stage_equipments stageID Integer PK (stageID,equipmentID)
FK (stageID) References Stage (stageID)
equipmentID Integer PK (stageID,equipmentID)
FK (equipmentID) References equipment (equipmentID)
artist artisID Integer PK (artistID)
name VARCHAR2(50)
typeOfMusic VARCHAR2(50)
Slots slotID Integer PK (slotID,daySerialNO)
daySerialNO Integer PK (slotID,daySerialNO)
FK (daySerialNO) references festivalDays (daySerialNO)
stageID Integer FK (stageID) References Stage (stageID)

startTime VARCHAR (15)
artistID Integer FK (artistID) References artist (artistID)
Task 2 Data and Queries
a) Tables using SQL
create table festival (
festivalID integer NOT NULL PRIMARY KEY,
name varchar2(50) NOT NULL,
festivaldate Date NOT NULL
);
create table festivaldays (
dayserialNO integer NOT NULL PRIMARY KEY,
day integer NOT NULL,
festivalID int NOT NULL,
constraint festivaldays_fk foreign key (festivalID) references
festival (festivalID)
);
create table vendor (
vendorid integer primary key,
name varchar2(50) not null,
type varchar2(50) not null
);
create table festivalvendors (
festivalID integer,
vendorID integer,
constraint festivalVendors_pk primary key (festivalID,vendorID),
constraint festivalvendorsfk_1 foreign key (festivalID)
references festival (festivalID),

constraint festivalvendorsfk_2 foreign key (vendorID) references
vendor (vendorID)
);
create table stage (
stageID int primary key,
name varchar2(50) not null,
festivalID int not null,
constraint stage_fk1 foreign key (festivalID) references festival
(festivalID)
);
create table equipment (
equipmentID int primary key,
name varchar2(50) not null,
type varchar2(50) not null
);
create table stageequipments (
equipmentID int not null,
stageID int not null,
constraint stageequiments_pk primary key (equipmentID,stageID),
constraint stageequipments_fk1 foreign key (equipmentID)
references equipment(equipmentID),
constraint stageequipments_fk2 foreign key (stageID) references
stage(stageID)
);
create table artist (
artistID integer primary key,
name varchar2(50) not null,
typeofmusic varchar2(50) not null
);

create table slots (
slotID integer not null,
dayserialno integer not null,
stageID integer not null,
startTime varchar2(25) not null,
artistID integer not null,
constraint slots_pk primary key (slotID,dayserialno),
constraint slots_fk1 foreign key (dayserialno) references
festivaldays (dayserialno),
constraint slots_fk2 foreign key (stageID) references stage
(stageID),
constraint slots_fk3 foreign key (artistID) references artist
(artistID)
);
b) Enter data for festivals and the days they are running
 Festival table
OUTPUT
 festivalDays table
OUTPUT

c) data for vendors
 Vendor table
Output
d) Data on equipment and equipment types
 Equipment table
Insert statements
Output
e) Data for stages, slots, artists at all festivals
 Table stage
Output

 StageEquipments table
Output
 Artists table
output

Table Slots
Output

f) Festivals with their dates
select name,festivalDate from festival;
g) Artists performing at Leyton Fest
select a.name from artist a
inner join slots s on s.artistID=a.artistID
inner join festivaldays fd on fd.dayserialno=s.dayserialno
inner join festival f on f.festivalid=fd.festivalid
where f.festivalid=1;

h) Write a query that selects all the dates and time slots for Jazz
artists at all festival
select a.name,f.festivaldate,s.starttime from artist a
inner join slots s on s.artistID=a.artistID
inner join festivaldays fd on fd.dayserialno=s.dayserialno
inner join festival f on f.festivalid=fd.festivalid
where a.typeofmusic='Jazz';
i) Write a query that shows all the rigging equipment used on CF-
Stage 1 at the Carson festiva
select e.name from equipment e
inner join stageequipments se on se.equipmentid=e.equipmentID
inner join stage s on s.stageid=se.stageid
inner join festival f on f.festivalid=s.festivalid
where f.name='Carson Festival' and s.name='CF-Stage 2';
j) Write a query that counts number of vendors at the Wonderland
Rock Festival
select f.name,count(v.vendorID) FROM festivalvendors v
inner join festival f on f.festivalid=v.festivalID
where f.name='Wonderland Rock festival'
group by f.name;

k) Write a query to show all Food and Drink vendors allocated to
festivals in July 2018
select ve.name FROM festivalvendors v
inner join festival f on f.festivalid=v.festivalID
inner join vendor ve on ve.vendorID=v.vendorID
where f.festivaldate between '01-July-2018' AND '31-July-2018'
AND ve.Type='Food and Drink';
I) Update the Carson Festival so that the rock band ‘Alonso’
replace ‘Dark Sound’. They should have the same slot, date
and stage as ‘Dark Sound’
INSERT INTO "ARTIST" (ARTISTID, NAME, TYPEOFMUSIC) VALUES ('17',
'Alonso', 'Rock');
update slots set artistID=17 where artistID=10;
m)Alter the database so that it keeps a record of the ticket price for
each festival
alter table festival add ticketPrice Number DEFAULT 0 NOT NULL ;
Task 3 Derived Data
To generate revenue data the database would have to be extended to keep records of ticket sales and
payment by vendors. For the ticket sales it means that the database would have to capture the details of
the customer who bought the ticket. Thus expanding the database would mean creating a table for the
customers buying the tickets and then another table for vendor payments. For the expenditures the
database would have to be expanded to capture all expenditures where by an expenditure is associated
to a certain festival.

Using the new added tables, it’s possible to produce a report showing the income and the expenditure
of the business.
Task 4
Edge entertainment needed a database to manage their business activities. Edge organizes and
facilitates festivals. For every festival there is important information crucial to the business that is
generated. For example the details of the festival have to be captured. Facilitation involves making sure
that the festival has the required stages. Details about the stage should be captured in the database.
Each stage is assigned to one or more equipment’s and this record should be saved in a table to show
which stage has which equipment. For every festival there are vendors who open up tents and the
business wants to keep a record of the vendors and which festival they attended. A festival is performed
by different artists who are allocated a time slot. This information should be recorded in the database.
To make sure that the database captured all the requirements of the business an thorough analysis was
done on the case study and all entities were identified. The next step was coming with an entity
relationship diagram to map out the entities. From the ERD relations were derived and normalized up to
3NF.
Implementation of the relations in 3NF was done Oracle SQL. All the relations were created and the
relationship between them created to ensure the data integrity for all the data stored in the database.