Database : Stella Arts Event Booking System

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.


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.

Loyalty Scheme Levels
Customer Details

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


loyaltyidInteger Not nullPrimary keypk_loyaltyid


manager_idinteger Not nullPrimary keypk_mid  
manager_l_name  Varchar15   


post_collect_idinteger Not nullPrimary keypk_ptctid  
post_collectVarchar  10   


paymentid  Integer Not nullPrimary keypk_paymentid  
paymentmethod  varchar15   


venue_id  Integer Not nullPrimary keypk_event_id  
venue_name  Varchar50   
full_price  Float    
event_time  Time    
venue_id  int  Foreign keyfk_vid  


event_id  Integer Not nullPrimary keypk_event_id  
event_name  Varchar50   
full_price  Float    
event_time  Time    
venue_id  int  Foreign keyfk_vid  


customer_refInteger Not nullPrimary keypk_customer_ref
loyaltyidInteger  Foreign keyfk_cr_lid


ticketnointeger Not nullPrimary keypk_tno
post_collect_idinteger  Foreign keyfk_tno_pcid
customer_refinteger  Foreign keyfk_tno_cr


booking_refInteger Not nullPrimary keypk_bref
customer_refInteger  Foreign keyfk_cusref
payment_idInteger  Foreign keyfk_bref_eid
event_idinteger  Foreign keyfk_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

loyalty values

Inserting the given queries and executing data into Customer table


3.    Enter venue data

Inserting the given queries and executing data into Venue table

Venue Values

Inserting the given queries and executing data into manager table

Manager Values

4.    Enter booking and event data

Inserting the given queries and executing data into booking table

Booking Values

Inserting the given queries and executing data into event table

Event Values

Inserting the given queries and executing data into payment table

Payment Values

5.    Enter ticket data

Inserting the given queries and executing data into ticket table

Ticket Values

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

Post/Collect Values

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.


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.


SmartDraw. (2018, 10 15). Retrieved from

Leave a Comment