Background and requirements

Stella Arts is an arts company which manages and produces a range of performances including ballet, plays and musicals at a range of venues across the country. They run a loyalty scheme which many customers join as they receive a discount on future bookings. Current discount levels are 5% for bronze customers, 7% for silver and 10% for gold customers (see figure 1).

The current system starts with customers booking events. The customer will phone Stella Arts and the receptionist will book the tickets. Each booking can have many tickets. The booking form will include the customer details (see figure 2), the quantity of tickets, the event name and other details such as the venue and price (see figure 3). Each event must be at different dates/times. Payment can be made either in cash on the door when they arrive at the event or by credit card or debit card. After the booking has been made the customer will receive email confirmation and tickets (figure 4) are either collected at the venue or sent in the post.

Venue details such as the name of the venue, address, capacity and contact details are stored in a notebook (see figure 5).

The client would like a database system so that they can keep track of bookings and to reduce the pressure on reception staff. Stella Arts would like an online booking system and one can also provide management information relating to bookings for business planning purposes.

Introduction

Scenario given above is about creating database for Stella art company. Stella art company manages performances that includes musicals play, ballet at a variety of Venues across the country. Book keeping system of the company have been recorded at notebook. Company also gives loyalty Scheme in which customer can get offer. Company has different loyalty level like gold bronze and silver for their customer. Booking can be completed by the form of customer detail, event, venue, ticket and payment details. payment is accepted in different method. Only one event and date can be booked at one time. Ticket can be taken at given address or also can be collected at venue.

Task 1 – Design

a)    Produce an entity relationship model for the proposed database system for Stella Arts. This should be a fully normalized model to 3rd normal form.

The relationships of entity sets stored in a database is An entity relationship diagram (ERD) shows. An entity in this context is an object, a component of data. … By defining the entities, their attributes, and showing the relationships between them, an ER diagram illustrates the logical structure of databases. (SmartDraw, 2018)

ER diagram was created

b)    Discuss how normalization of each of the samples of data in the scenario contributed to your finished ER diagram.

Normalization is the process of making many simple tables from complicated table. It helps to reduce data and make easier for user by removing duplication, partial dependency and in dependent dependency.

C)    Produce a data dictionary for the entity relationship model showing all attributes, with data types and identifying primary and foreign keys.

Roshan_Loyalty

name	Type	length	null	key	constraint
loyaltyid	Integer		Not null	Primary key	pk_loyaltyid
loyaltylevel	Varchar	20
noofbooking	Integer
discount	Float

Roshan_Manager

name	Type	length	null	key	constraint
manager_id	integer		Not null	Primary key	pk_mid
manager_f_name	Varchar	15
manager_l_name	Varchar	15
tel_number	Varchar	20

Roshan_post_Colect

name	Type	length	null	key	constraint
post_collect_id	integer		Not null	Primary key	pk_ptctid
post_collect	Varchar	10

Roshan_Payment

name	Type	length	null	key	constraint
paymentid	Integer		Not null	Primary key	pk_paymentid
paymentmethod	varchar	15

Roshan_Venue

name	Type	length	null	key	constraint
venue_id	Integer		Not null	Primary key	pk_event_id
venue_name	Varchar	50
full_price	Float
event_time	Time
venue_id	int			Foreign key	fk_vid

Roshan_event

name	Type	length	null	key	constraint
event_id	Integer		Not null	Primary key	pk_event_id
event_name	Varchar	50
full_price	Float
event_time	Time
venue_id	int			Foreign key	fk_vid

Roshan_Customer

name	Type	length	null	key	constraint
customer_ref	Integer		Not null	Primary key	pk_customer_ref
customerf_name	Varchar	50
customerl_name	Varchar	50
address	Varchar	100
contact_number	Varchar	20
email	Varchar	50
loyaltyid	Integer			Foreign key	fk_cr_lid

Roshan_Ticket

name	Type	length	null	key	constraint
ticketno	integer		Not null	Primary key	pk_tno
seatno	integer
row	Varchar	10
post_collect_id	integer			Foreign key	fk_tno_pcid
customer_ref	integer			Foreign key	fk_tno_cr

Roshan_Booking

name	Type	length	null	key	constraint
booking_ref	Integer		Not null	Primary key	pk_bref
booking_date	date
ticket_qty	integer
event_date	Date
customer_ref	Integer			Foreign key	fk_cusref
payment_id	Integer			Foreign key	fk_bref_eid
event_id	integer			Foreign key	fk_bref_pid

Task 2 – Data Entry and Data Manipulation

1.    Create all the normalized tables in SQL. Show your SQL scripts and the finished tables. 

2.    Enter all data on all customers and loyalty level

 Inserting the given queries and executing data into table

Inserting the given queries and executing data into Customer table

3.    Enter venue data

Inserting the given queries and executing data into Venue table

Inserting the given queries and executing data into manager table

4.    Enter booking and event data

Inserting the given queries and executing data into booking table

Inserting the given queries and executing data into event table

Inserting the given queries and executing data into payment table

5.    Enter ticket data

Inserting the given queries and executing data into ticket table

Inserting the given queries and executing data into post/Collect table

6.    Write a query that selects the first name and surname of customers in alphabetical order of surname

7. Write a query that selects all bookings by event date in ascending order

8. Write a query that counts all the bookings where the payment method is credit card

9. Write a query that returns all event bookings where the quantity of tickets is greater than 1 ordered by description

10. Write a query that selects all customer first names and surnames and loyalty level who pay by credit card grouped by their loyalty level

11. Change the contact name for ‘The Coliseum’ to Mark Hibbart

12. Add a new venue: ‘The Round Theatre, 12 Grayson Lane, Manchester, M1 2RT’ to the venue table. It has a capacity of 230. The contact name is ‘Sarah Jones’ and the contact number is 07667543444

13. Delete the venue ‘The Octagon’

Task 3 – Assessment

1.    Your understanding of the requirement explaining any assumptions made

Since every company needs managed and easy data and Stella also needs data related to booking, event, event date, ticket, venue and loyalty level. So, the company needs a organized data to keep records of customer. So table of booking, ticket, event, venue and loyalty level is required to create.

2.    Initial design to meet the requirements including your design decisions and justifications.

Every data on table was normalized. Broken into 1NF, 2NF and 3NF. After that dependency like partial dependency, non-key dependency and repeating group will separate. To design the database of Stella arts I have divided data into 9 tables.

3.     How the requirement has been met.

Company requirement is to have a reliable record keeping database system. It must be also easy for working staff to easily input the data. Staff should input data effortlessly. Data should look clearer. As shown in above data ER diagram the relation between table was established. After that it became easier to input the data, view the files, delete the record and edit the given records.

Conclusion

Stella Art Company record data management was designed. The data of Stella Art could be added, removed, view and update. Company requirement was met by the following development. Now it time for company to decide weather they likes or not and if it meets company standard.

References

SmartDraw. (2018, 10 15). Retrieved from https://www.smartdraw.com/entity-relationship-diagram/