Database Design and Development Project: ABC Event Organizer

Desklib provides solved assignments and past papers to help students succeed.

Assignment 1
ER Model & Relational Schema

Executive summary
Database design and development is most important technique to line up all operations,
process and strategies and efficiently manage whole organizational data using one single
system. Processing of task and handling of record using file or traditional way is outdated
terms. Now in this decade number of innovation has been done in field of database
management of the organization. Some world class database model design techniques are here
which allows for development of required system to handle organizational data. Development
of system is not a bigger concern. Here everything is dependent on selection of technology for
the development. The most convenient design model is relational database design model that
formulate data into row and column. (www.tutorialspoint.com, 2019). Here each row of the
table contains data or record and column contains attribute of information. A special query
language SQL is being used for the manipulation of the dataset. This query allows for faster
retrieval, insertion, extraction, modification and amendment of record. Since the relational
model is quite differ from other existing model in formatting of record and ability to perform
operation on them. So this paper has been developed as per analyzing requirement of given
organization. The given organization is from event organizing area and seeking for a database
system. So as per need here in this assessment relational database model and query tool SQL
have been selected to perform implementation and test of database to assist them with
managing their booking, customer and supplier information.

Table of Contents
Introduction.................................................................................................................................................4
Relational Schema for: [insert company name]...........................................................................................5
SQL Statements Report for: [insert company name]...................................................................................5
Complete ER diagram..................................................................................................................................5
3.1 Question................................................................................................................................................6
 A......................................................................................................................................................6
 B.......................................................................................................................................................6
 C.......................................................................................................................................................7
3.2 Question 2.............................................................................................................................................8
3.3 Question 3.............................................................................................................................................8
3.4 Question 4.............................................................................................................................................9
3.5 Question 5...........................................................................................................................................10
3.6 Question 6...........................................................................................................................................11
3.7 Question 7...........................................................................................................................................12
3.8 Question 8...........................................................................................................................................12
3.9 Question 9...........................................................................................................................................13
3.10 Question 10.......................................................................................................................................13
Conclusion.................................................................................................................................................14
Bibliography...............................................................................................................................................14

Introduction
Database system is a repository of information or collection of related data in shorted order o
the basis of facts. In earlier days, data was record in file format. After evolution of new model of
database it allows to overcome from deficiencies of file format system. A modern relational
database model allows performing extensive amount of activity the proper management of all
records for longer time. So as per requirement of ABC Event Organizer I have appointed to
design and develop a relational database system and perform large set of operation to test the
database for purpose of assistance to the Julie for management of bookings, customer,
supplier, staff, products and etc. After analysis it is found that majority of design work has been
completed but needed to complete the design with additional six tables. (Techopedia.com,
2019)
The requirement of Julie with the proposed system is as follows:
 She wanted to test the system by adding cost in some of product supplier record.
 All price should be set on time of creating event
 ABC organization wishes to have a table that contains supervision or certification wage
figures.
 A record to manage payout for the casual and regular staff member on the basis of their
work.
 System should able to keep record of staff started and ended their employment.
After providing this information they ask to make assumptions as per requirement on
development period and record the assumptions.
Relational Schema for: [insert company name]
Relational schema for the insertion of company name is as follows:
CustID(CustFirstName, CustLastName, CustEmail, CorporationName, Website)
CustFirstName(CorporationName)

(Medium, 2019)
SQL Statements Report for: [insert company name]
SELECT CorporationName FROM customer;
Complete ER diagram
On the basis of given incomplete entity relationship diagram and provided requirement detail a
complete entity relationship diagram has been developed. On the basis of new design all
specified operation and testing action would be done. (Visual-paradigm.com, 2019)
Figure 1 Complete ER diagram

3.1 Question
 A
select concat(concat(StaffFirstName," "), StaffLastName) as "Fullname", TFN FROM staff
order by StaffLastName
 B
SELECT Staff.StaffFirstName, Staff.StaffLastName, certification.Description from
certification, staff, staffcertifications where staff.StaffID = staffcertifications.StaffID and
staffcertifications.CertificationID = certification.CertificationID and `ExpiryDate` >=
CURDATE();

 C
select events.EventID, eventtype.Description, events.EventDate, events.EventTime,
events.NumberOfAttendees, events.Location from events, eventtype WHERE
events.EventID = eventtype.EventType and events.EventDate BETWEEN CURDATE() and
'2019-12-31';

3.2 Question 2
Query
update customer set website = 'partiesRUs.net.au' where CustID = 'c110'
Result
3.3 Question 3
UPDATE STAFF SET supervisior=’Pat’ WHERE StaffType=CasualStaff

Result
3.4 Question 4
Query
Update supplier product set price = (price + 1.00);
Result

3.5 Question 5
Alter table staff add COLUMN DateOfBirth date
Result

3.6 Question 6
Query
Result

3.7 Question 7
Query
ALTER TABLE `staff` ADD `HireDate` DATE NOT NULL AFTER `DateOfBirth`, ADD `EndDate` DATE
NULL AFTER `HireDate`;
Result
3.8 Question 8
Query