CO887 Assessment 1

Here is a conceptual model and relational model for a theatre booking system

Relational Schema

Production(Title,BasicTicketPrice)
Performance(PerfDate,PerfTime,Title)
Zone(Name,PriceMultiplier)
Seat(RowNumber,Zone)
Booking(Email,PerfDate,PerfTime,RowNumber)


Constraints

Not Null:

• Production: BasicTicketPrice
• Booking: Email
• Zone: PriceMultiplier

Data

We have provided table creation and insert statements for Zone and Seat. Use these to create and populate the two tables.
Zone
Seat

You will need to define your own table creation and insert commands for the following data:

What you have to do

1. Write mySQL CREATE TABLE commands for Production, Performance and Booking, ensuring that they contain the correct columns. The commands must include primary key definitions. Pick sensible data types for the columns. You must include foreign keys and ensure that other constraints are enforced. These constraints include those explicitly listed as well as those implied by the models.
2. Write mySQL insertion commands to populate the tables with the data given above. Only the given data should be inserted.
3. Create the following mySQL queries.
   1. All performances of Cats in the order they occur, with the earliest first. Output all columns of the Performance table.
   2. Booking and performance details where the person booking has a email with domain "@email.com". For each such booking, output the Email, PerfDate, PerfTime and Title.
   3. The prices paid for of all booked seats. Output the RowNumber, Email of person booking and calculated price.
   4. All the free seats for the performance at 2017-11-01, 19:00:00. Output the RowNumber only.
   5. Count of all the seats in each zone for zones where the PriceMultiplier is greater than 2.3. Output the zone Name, PriceMultiplier and the count.

How it will be marked

• 50% for table and data creation.
• 50% for the queries. 10% will be given for each query.

The mySQL commands must work on the dragon mySql system or they will get 0 marks.

Submit, via moodle, a single .txt file containing all your create table, data insertion and queries. The .txt file should contain only working SQL.

The deadline is given on moodle. Late submissions, without concessions, will be given 0 marks.

Peter Rodgers
p.j.rodgers@kent.ac.uk