Reference no: EM13333021

In this assignment you will build a small database to support the needs of a company called Concertina that organises concerts in the UK. To do this you will need to create tables to support the following scenario.

Concertina run repeated scheduled concerts involving a single principal artist (or group) in large venues within the UK. These concerts have a name, a duration of 2,3,4,or 5 hours, a type of either classical, rock, or pop and a cost that varies between £30 and £500. The concerts are repeatedly run as events in a variety of venues. So, for example, the ABC concert could run a number of times in different venues. Each of these runs is called an event for that particular concert. The date of each event and the venue are recorded. Customers book onto the events and Concertina need to record the customer name, gender, telephone number, and address. Each time a customer attends an event, their time of arrival is recorded, and if they have parked their car in the official car park, the registration number of the vehicle is also recorded. Customers will also give an evaluation each time they attend an event (the evaluation is a number from 0 to 5). Concertina need to record the evaluation from each customer each time they attend an event. It is not a requirement that all events have customers registered on them, and it is also not required that every customer has to have attended a concert. Each event is held at an approved venue and each venue has a name, a maximum capacity (between 1,000 and 60,000 people) and a postcode.

1. Construct a simple entity model based on the above scenario.

Concertina have produced a first draft model of the above scenario as follows

Database Server Management Assessment (continued)

You should assume this model is a reasonable, but incomplete, draft and use it to develop a final improved entity model.

Using your improved and complete entity model, construct a set of tables with suitably defined columns to support this scenario.

 

2. Populate the tables with appropriate test data, bearing in mind the following :

a. There may be some concerts for which events have yet to be arranged.
b. There may be some customers who have yet to book on an event.
c. All events are uniquely identified by a single key column called event_id
d. Records of customers attending concerts (known as a booking) are identified by a combination of foreign keys (not by a single column surrogate key)
e. There may be some venues that are yet to be allocated to an event.

Hint: In order to keep the amount of test data to a minimum, we will assume that the company is unlikely to run with more than six different concerts and that the number of customers about which they have details is less than 11.

Take a look at the query specifications in question 3. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.

The creation of a good, representative set of test data is very important and is worth 15 marks

3. Create appropriate primary and foreign key constraints on the tables.

 

4. Develop a set of queries as follows, showing all output that they produce :

a. Find details of all concerts being held in York

b. Find the concert that runs the most number of times.

c. Find the total number of customers attending each type of concert.