Cinema Q1

A company operates a cinema which has three different screens. Each screen has a capacity determined by the number of seats the screen has.

Each seat is identified by a unique seat number for the screen it is in, but two seats in different screens can have the same number. A specific seat is classified as being one of two types: standard or deluxe.

On a particular day, there will be several showings of films in each of the screens. A screen might show the same film multiple times or it might show different films at different times of day.

Customers make bookings to go to the cinema. Each booking is for one specific showing of a film. A booking is for one or more seats, and the customer can select the individual seats that they want to book when they make the booking.

When a booking is made, if the customer has not previously made a booking, the customer’s first name, last name and telephone number area recorded. If the booking is for more than one seat then only the details of the person who made the booking are recorded - the system does not store the details of who will be sitting in each seat. If a customer has made a booking previously then the details that were stored about them when the previous booking was made are re-used.

Schema

Screen(ScreenNumber, Capacity)

Seat(SeatNumber, ScreenNumber, SeatType)

Film(FilmID, FilmName, DurationMinutes, FilmCertificate)

Showing(ShowingID, ScreenNumber, FilmID, ShowTime, ShowDate)

Question

Write several CREATE queries to create the above schema in the database.

Each field should have an appropriate datatype. (e.g. ShowDate should be stored as date, and ShowTime should be stored as datetime)

Primary keys should be used, as well as foreign keys where appropriate.

You can assume that SeatType is always valid. You can also assume that data inserted will never be null.