Database Design and Development Project - SOF535 Application Design

This project showcases the design and development of a database system, beginning with the creation of an Extended Entity Relationship Diagram (EERD) based on specified assumptions. It includes the identification of entities such as Bands, Festivals, Events, Equipment, Organizers, Venues, Locations, and Staff, along with their respective attributes and identifiers. Validation questions are formulated to ensure the model's integrity. The project progresses through logical and composite model design, culminating in a final relational model and forward engineering using Apex, complete with DDL and DML code. Key changes to the original model are discussed, highlighting the generalization of staff types and simplification of venue-related entities. The report also includes screenshots of successful table creation and code execution, along with a bibliography and appendices containing the DDL and DML code used in the project. Desklib offers a wealth of similar solved assignments and past papers for students.

Use this Template for the DS Assignment
 Please note that you can modify this template to
suit your own presentation
 This is only a sample, you should make sure all
aspects of your work and assessment tasks are
clearly evidenced
 You should also check Assignment Deliverables
should in the Assessment guide and the marking
schema.
Title: Database Design
Student Name:
Student id:
1

1.EERM
1.1 Assumptions
The following assumptions are made before the construction of
the Extended ER Diagram:
 It is assumed that there are only two types of staffs, the Stage Staffs and the
Security Staffs. They can be easily generalized to an Entity named staffs.
 It is assumed that a single venue is hired for a single festival and single venue
is located at a single location.
 It is assumed that the Location is the weak entity for the strong entity type
Venue and Stage_Staffs and Security_Staffs are the weak entities for the
strong entity type Staffs.
 It is assumed that a single venue contains a single stage. And hence stage is a
weak entity for the strong entity venue.
1.2 Extended Entity Relationship Diagram (EER Diagram)
Entity Relationship Diagram
Bands
Festivals
Venue
Organizer
Staffs
Equipments
Security_Staffs
Stage_Staffs
Location
Event
performs has hired for
organizes
located at
works for
hired for
Stage
contains
Figure: EERD
Source: (created by author)
1.3 List your attributes and identifiers here:
Bands (BandId, Band_Name, Genre)
Festivals (FestID, FestName, VenueID, OrganizerID)
Event (EventID, EventName, FestID, BandId, Attendence, EquipmentID)
Equipments (EquipmentID, EquipmentName)
Organizers (OrganizerID, OrganizerName, StaffID)
Venue (VenueId, Name, LocationName, Country, Contact_Person)
Location (LocationName, Country)
Staffs (StaffId, Name)
2

Stage_Staffs (StaffId, StaffName, Wage)
Security_Staffs (StaffId, StaffName, HiringCost)
Stage (StageID, VenueId)
1.4 Include a list of the Validation Questions of your model here:
 Details for each Festival and the key organizer of the Festival
 Details of Venues and Locations where the festival is taking place
 Name of the Contact person for the Venue
 List of all staff working at the Festival
 List of Bands playing at Festival
 A list of bands on each location, times and dates
 Details of allocation of staff to a stage
 Equipment list for each stage
 Security staff allocated to a venue and each stage
1.5 A definition for each entity
 Bands: The information about all the bands performing in the festivals
are provided here.
 Festivals: The information about the different festivals are provided
here.
 Events: This field is specialized from the festival entity. The
information about the different type of events occurring in the different
festivals are provided here.
 Equipment: the information about the different equipment that are
required for the events are provided here.
 Organizers: the information about the different type of organizers are
provided here.
 Venue: the entity provides the details of the venue in which the
festivals are to be held
 Location: the location details of the venues are provided by this entity.
 Staffs: the details of all the staffs are provided in this entity.
 Stage_Staffs: The information about the staffs on stage are provided
by this entity.
 Security_Staffs: The information about the security staffs are provided
in this entity.
 Stage: The information about the stage is provided by this entity.
1.6 Occurrences for each entity
Bands
Band id band name Genre
B001 Eluvitie Folk
B002 Linkin Park Rock
Festivals
3

Fest id Fest name Venue ID Organizer ID Fest Date
F001 British Summer
Festival
V001 O001 21-09-2017
F002 Long Weekend V002 O002 30-12-2017
F003 Folk Music
Festival
V003 O001 02-01-2018
Event
Event id Event name Fest ID Band ID Attendance Equipment ID
E001 Folk Music F003 0113-111 111 40,000 E001
Equipments
Equipment ID Equipment
name
E001 Keyboard
Organizer
Organizer id Organizer name
O001 UMM
O002 Ultra Music
Venue
Venue id Venue
name
Location
name
Country Contact
Person
Venue
Size
Stage No
V001 Wembley,
London
London,
UK
UK Ben 1,000,000 S001
V002 Old
Trafford,
Manchest
er
Manchest
er, UK
UK Jason 75,000 S002
V003 Roundhay
Park,
Leeds
Leeds, UK UK Rodriguez 50,000 S003
Staffs
Staff Id Event name Organizer ID Wage/Salary
B001 Ben Chilwell O001 10,000
B002 PJWSTK O001 8,000
4

2. Logical Design
Entity Relationship Diagram
Bands
BandId:INTEGER
Band_name:VARCHAR
Genre:VARCHAR
Festivals
FestId:INTEGER
FestName:VARCHAR
VenueID:INTEGER
OrganizerID:INTEGER
FestDate:TIME
Venue
VenueId:INTEGER
Name:VARCHAR
LocationName:INTEGER
Country:VARCHAR
Contact_Person:VARCHAR
VenueSize:NUMERIC
StageNo:INTEGER
Organizer
OrganizerID:INTEGER
OrganizerName:VARCHAR
Staffs
StaffId:INTEGER
Name:VARCHAR
OrganizerID:INTEGER
Equipments
EquipmentID:INTEGER
EquipmentName:VARCHAR
Security_Staffs
Staff_Id:INTEGER
Staff_Name:VARCHAR
HiringCost:NUMERIC
Stage_Staffs
Staff_Id:INTEGER
Staff_Name:VARCHAR
Wage:NUMERIC
Location
LocationName:VARCHAR
Country:VARCHAR
Event
EventID:INTEGER
EventName:VARCHAR
FestID:INTEGER
BandId:INTEGER
Attendence:NUMERIC
EquipmentID:INTEGER
performs has
hired for
organizes
works for
hired for
Stage
StageNo:INTEGER
VenueId:INTEGER
Bands (BandId (primary key), Band_Name, Genre)
Festivals (FestID (primary key), FestName, VenueID (foreign key),
OrganizerID (foreign key))
Event (EventID (primary key), EventName, FestID (foreign key), BandId
(foreign key), Attendence, EquipmentID (foreign key))
Equipments (EquipmentID (primary key), EquipmentName)
Organizers (OrganizerID (primary key), OrganizerName, StaffID (foreign key))
Venue (VenueId (primary key), Name, LocationName, Country,
Contact_Person)
Location (LocationName, Country)
Staffs (StaffId (primary key), Name)
Stage_Staffs (StaffId (primary key), StaffName, Wage)
Security_Staffs (StaffId (foreign key), StaffName, HiringCost)
Stage (StageID (foreign key), VenueId (foreign key))
3. COMPOSITE Model – using QSEE.
3.1 Composite Model
Physical Design Considerations
The staffs have been divided into two types, the stage staffs and
the security staffs. They can be generalized into a single entity named staffs.
In addition to this the location and the stages have been specialized from the
venue table and hence, a single entity venue has been selected for the
database.
5

Entity Relationship Diagram
Bands
BandId:INTEGER
Band_name:VARCHAR
Genre:VARCHAR
Festivals
FestId:INTEGER
FestName:VARCHAR
VenueID:INTEGER
OrganizerID:INTEGER
FestDate:TIME
Venue
VenueId:INTEGER
Name:VARCHAR
LocationName:INTEGER
Country:VARCHAR
Contact_Person:VARCHAR
VenueSize:NUMERIC
StageNo:INTEGER
Organizer
OrganizerID:INTEGER
OrganizerName:VARCHAR
Staffs
StaffId:INTEGER
Name:VARCHAR
OrganizerID:INTEGER
Cost:NUMERIC
Equipments
EquipmentID:INTEGER
EquipmentName:VARCHAR
Event
EventID:INTEGER
EventName:VARCHAR
FestID:INTEGER
BandId:INTEGER
Attendence:NUMERIC
EquipmentID:INTEGER
performs
has
hired for
organizes
works for
hired for
6

Discuss key changes to the original model.
The major key changes made to the physical model are the
staff_security and the Staff_stage entities have been removed completely.
The attribute named cost has been added to the staffs table. In addition to
this, the location entity and the stage entity have been deleted from the
database for generalizing them.
3.2 Final Relational model
Bands (BandId (primary key), Band_Name, Genre)
Festivals (FestID (primary key), FestName, VenueID (foreign key),
OrganizerID (foreign key))
Event (EventID (primary key), EventName, FestID (foreign key), BandId
(foreign key), Attendence, EquipmentID (foreign key))
Equipments (EquipmentID (primary key), EquipmentName)
Organizers (OrganizerID (primary key), OrganizerName, StaffID (foreign key))
Venue (VenueId (primary key), Name, LocationName, Country,
Contact_Person)
Staffs (StaffId (primary key), Name, Cost)
4. Forward Engineering
Apex Screenshot that table have been successfully created.
View code and successful run of the code.
7

8

5. Bibliography
Colmsee, C., Beier, S., Himmelbach, A., Schmutzer, T., Stein, N., Scholz, U.
and Mascher, M., 2015. BARLEX–the barley draft genome explorer.
Molecular plant, 8(6), pp.964-966.
Virpura, D. and Swaminarayan, P., 2017. Performance Evaluation &
Comparative study of Development Platform Architecture with Open Source
Tools. Performance Evaluation.
9

6. Appendixes
DDL code
CREATE table "BANDS" (
"BANDID" VARCHAR2(10) NOT NULL,
"BANDNAME" VARCHAR2(30) NOT NULL,
"GENRE" VARCHAR2(20),
constraint "BANDS_PK" primary key ("BANDID")
)
/
CREATE table "VENUE" (
"VENUEID" VARCHAR2(10) NOT NULL,
"VENUENAME" VARCHAR2(30),
"LOCATIONNAME" VARCHAR2(30),
"COUNTRY" VARCHAR2(20),
"CONTRACTPERSON" VARCHAR2(30),
constraint "VENUE_PK" primary key ("VENUEID")
)
/
CREATE TABLE "EQUIPMENT"
( "EQUIPMENTID" VARCHAR2(10) NOT NULL ENABLE,
"EQUIPEMENTNAME" VARCHAR2(30),
CONSTRAINT "EQUIPMENT_PK" PRIMARY KEY
("EQUIPMENTID")
USING INDEX ENABLE
)
/
CREATE table "STAFFS" (
"STAFFID" VARCHAR2(10) NOT NULL,
"STAFFNAME" VARCHAR2(30),
"COST" VARCHAR2(10),
constraint "STAFFS_PK" primary key ("STAFFID")
10

)
/
CREATE table "ORGANIZER" (
"ORGANIZERID" VARCHAR2(10) NOT NULL,
"ORGANIZERNAME" VARCHAR2(10),
"STAFFID" VARCHAR2(10) NOT NULL,
constraint "ORGANIZER_PK" primary key ("ORGANIZERID")
)
/
ALTER TABLE "ORGANIZER" ADD CONSTRAINT "ORGANIZER_FK"
FOREIGN KEY ("STAFFID")
REFERENCES "STAFFS" ("STAFFID")
ON DELETE SET NULL
/
CREATE table "FESTIVALS" (
"FESTID" VARCHAR2(10),
"FESTNAME" VARCHAR2(30),
"VENUEID" VARCHAR2(10),
"BANDID" VARCHAR2(10),
"ATTENDANCE" NUMBER(10,10),
"EQUIPMENTID" VARCHAR2(10),
constraint "FESTIVALS_PK" primary key ("FESTID")
)
/
ALTER TABLE "FESTIVALS" ADD CONSTRAINT "FESTIVALS_FK"
FOREIGN KEY ("VENUEID")
REFERENCES "VENUE" ("VENUEID")
ON DELETE SET NULL
/
ALTER TABLE "FESTIVALS" ADD CONSTRAINT "FESTIVALS_FK1"
11

FOREIGN KEY ("BANDID")
REFERENCES "BANDS" ("BANDID")
ON DELETE SET NULL
/
ALTER TABLE "FESTIVALS" ADD CONSTRAINT "FESTIVALS_FK2"
FOREIGN KEY ("EQUIPMENTID")
REFERENCES "EQUIPMENT" ("EQUIPMENTID")
/
CREATE table "EVENT" (
"EVENTID" VARCHAR2(10),
"EVENTNAME" VARCHAR2(30),
"FESTID" VARCHAR2(10),
"BANDID" VARCHAR2(10),
"EQUIPMENTID" VARCHAR2(10),
"ATTENDANCE" NUMBER(10,10),
constraint "EVENT_PK" primary key ("EVENTID")
)
/
ALTER TABLE "EVENT" ADD CONSTRAINT "EVENT_FK"
FOREIGN KEY ("FESTID")
REFERENCES "FESTIVALS" ("FESTID")
ON DELETE SET NULL
/
ALTER TABLE "EVENT" ADD CONSTRAINT "EVENT_FK1"
FOREIGN KEY ("BANDID")
REFERENCES "BANDS" ("BANDID")
ON DELETE SET NULL
/
12