Designing A Database For Edge Entertainment’s Festival Management

The Edge Entertainment Scenario

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)

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)

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)

);

• Festival table

OUTPUT

 

• festivalDays table

OUTPUT

 

• Vendor table

Output

 

• Equipment table

Insert statements

Output

 

• Table stage

Output

 

• StageEquipments table

Output

 

• Artists table

output

Table Slots

Output

 

select name,festivalDate from festival;

 

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;

 

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

Designing the Database

inner join festival f on f.festivalid=fd.festivalid

where a.typeofmusic=’Jazz’;

 

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′;

 

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;

 

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’;

 

INSERT INTO “ARTIST” (ARTISTID, NAME, TYPEOFMUSIC) VALUES (’17’, ‘Alonso’, ‘Rock’);

update slots set artistID=17 where artistID=10;

 

alter table festival add ticketPrice Number DEFAULT 0 NOT NULL ;

 

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.

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 datab