Design, Normalization and Data Dictionary for Festival Database

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)