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.
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.
|loyaltyid||Integer||Not null||Primary key||pk_loyaltyid|
|manager_id||integer||Not null||Primary key||pk_mid|
|post_collect_id||integer||Not null||Primary key||pk_ptctid|
|paymentid||Integer||Not null||Primary key||pk_paymentid|
|venue_id||Integer||Not null||Primary key||pk_event_id|
|event_id||Integer||Not null||Primary key||pk_event_id|
|customer_ref||Integer||Not null||Primary key||pk_customer_ref|
|ticketno||integer||Not null||Primary key||pk_tno|
|booking_ref||Integer||Not null||Primary key||pk_bref|
1. Create all the normalized tables in SQL. Show your SQL scripts and the finished tables.
Inserting the given queries and executing data into table
Inserting the given queries and executing data into Customer table
Inserting the given queries and executing data into Venue table
Inserting the given queries and executing data into manager table
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
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
9. Write a query that returns all event bookings where the quantity of tickets is greater than 1 ordered by description
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
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.
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.
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 https://www.smartdraw.com/entity-relationship-diagram/