In this project, you are to do the following.

1. Design a relational schema based upon a provided E-R diagram.
2. Write a creation script of SQL statements that will create your relational schema as a database under the class's IBM DB2 system.
3. Populate your database with some provided data.
4. Test your database with an SQL query. (The query expressed in English is provided.) The SQL query and its resulting table will serve to determine whether your schema is logically sound.

The enterprise for this project database is a ticket selling service (Seats) for a set of cinema houses.

E-R for the Seats Database

A difference from your typical movie theatre is that a ticket is for a given section of the theatre, so this is like assigned seating when one goes to a theatre to see a play. The price of a ticket is determined by the section of the theatre it is for and the type of show (e.g., gala, evening, or matinée). The type of show is indicated by ShowCat and its key level.

It is intended that the relationship cost effectively be a crossproduct of ShowCat and Section. For any category of show and theatre section, we should be able to look up the price.

For each patron, we keep a patron# as key, the patron's surname, the initial of the patron's given name (initial), and the patron's address (just the city for now).

A booking is for a given number of seats within a section of the theatre on a particular day and time (thus for whatever is showing that day and time at that theatre). Booking should enforce that the theatre indeed has the section indicated. Furthermore, the booking involves only one theatre, even though Theatre is involved via rel-ships in and for in the diagram, so implement it that way.

When designing your relational database based upon the E-R above, name your tables and attributes the same as the names in the E-R diagram. Implement the E-R as given. Do not take shortcuts or implement a different design.

Here is the sample data that you should load into your database.

Query: What are the bookings by patron number, surname, date, time, theatre name, film title, number of seats, and total price for the booking (number of seats times seat price)?

PATRON#     SURNAME              DATE       TIME     TNAME      TITLE                                    #SEATS PRICE    
----------- -------------------- ---------- -------- ---------- ---------------------------------------- ------ ---------
          5 Brauch               05/31/2009 10:00 AM Ottawa     Once There were Glaciers                      3     21.00
          1 Cleverley            05/30/2009 08:00 PM Ottawa     Which Way Falls Niagra Falls                  3     75.00
          1 Cleverley            05/31/2009 03:00 PM Hamilton   Once There were Glaciers                      3     45.00
          4 Holdrich             05/31/2009 08:00 PM Hamilton   Which Way Falls Niagra Falls                  2     14.00
          6 Horsnayle            05/31/2009 10:00 AM Ottawa     Once There were Glaciers                      2     20.00
          6 Horsnayle            05/31/2009 03:00 PM Hamilton   Once There were Glaciers                      2     30.00
          6 Horsnayle            05/31/2009 08:00 PM Hamilton   Which Way Falls Niagra Falls                  2     30.00
          3 Kyzar                05/30/2009 08:00 PM Ottawa     Which Way Falls Niagra Falls                  5     75.00
          7 Linane               05/30/2009 08:00 PM Ottawa     Which Way Falls Niagra Falls                  2     30.00
          7 Linane               05/31/2009 08:00 PM Ottawa     The Unbearable Sadness of Being               2     50.00
          2 Lizzard              05/30/2009 03:00 PM Hamilton   Once There were Glaciers                      1     10.00
          2 Lizzard              05/31/2009 08:00 PM Ottawa     The Unbearable Sadness of Being               2     40.00

  12 record(s) selected.

Order the results by surname, patron#, date, time, and tname.

In case you have not run across how to order tuples in the output in SQL, here is how SQL lets you do it. SQL provides a clause called ORDER BY.

SELECT ...

FROM ...

WHERE ...

ORDER BY attr₁, ..., attr_k;

This does a nested sort on the return table. First, the tuples are sorted on attr₁. Then any group of tuples matching on attr₁ (so now appearing adjacent), these are sorted on attr₂. And so forth.

The SQL standards (and DB2) allow one to specify whether the order should be ascending or descending with an optional keyword after each column name in the ORDER BY clause.

ORDER BY attr₁ [ASC | DESC], ..., attr_k [ASC | DESC]

The default is ascending. Strings are sorted in lexiographical (dictionary) order.

Note that you do not have to get it so your answer table looks exactly like the one above. However, you want the logical content to be the same.

For instance, since you may be handling time as varchar, you might have something different. In particular, if you are handling time as varchar, you can use '10:00 AM", '3:00 PM', and '8:00 PM' (so without leading '0's for the PM's) so that the lexiographic ordering of the times will be right (for this case).

For you diehards who insist on using the DATE and TIME types (domains) to do it "right", you will notice that the times will show as '10:00:00', '15:00:00', and '20:00:00' by default. This is using the 24-hour convention, and shows the seconds as well. It is acceptable if your times show up this way in your query's results.

You are a diehard and you insist that your times print out exactly as in the query above? Because date and time data is so important, database systems have many functions for dealing with date and time data. One can cast dates and times into many different formats for display. Investigate.

Due: by 11:59pm Tuesday 28 April.

You should submit the following four files.

1. schema: The SQL creation script that you wrote and used to create your database under DB2.

   This will be a sequence of SQL CREATE statements. For full credit, you must create all appropriate primary key and foreign key constraints, enforce participation constraints where possible, and give appropriate domain types to the attributes.

2. data: An SQL script that adds the requested data to your database under DB2.

   This will be a sequence of SQL INSERT statements. Do not use IMPORT or LOAD for this project. (These are commands for bulk loading.)

3. query: The SQL query that you wrote for the query above.

4. answers: The result table from the SQL query.

   For full credit, your results table should match the one above! (No, you don't have to get the spacing exactly the same. But the rows and columns of your answer table should be essentially the same.)

For the data type on price, I recommend one to use DB2's DECIMAL(5,2) type. (The 5 here says five significant digits, and the 2 says two digits right of the decimal point.) You may use VARCHAR(..) for the dates and times for this project. Yes, there is a DATE type and a TIME in the standard SQL, and in DB2, which you can use here (but do not have to use). (If we were going to be casting lots of queries on this database, having dates as type DATE and times as type TIME would be exceedingly convenient, and then we would want to do our database "right" using DATE and TIME.) This is because the system handles comparisions of DATE's and TIME's correctly and orders them correctly.

You are to submit your project both in hardcopy (to the dropbox) and in electronic form via the submit script on PRISM.

Due by 11:59pm Wednesday 29 April.

hardcopy

• Have a cover page for submitting your work, filling out your student number, CS-account, course, and name. E.g.,

  Student Number
  CS-account
  Course	CSE-3421 / winter 2009
  Family Name
  Given Name

• Attach printouts of the four files: schema, data, query, and answers.

online copy

To submit online:

% submit 3421 seats schema data query answers