|
CSE-3421M
Introduction to Database Systems
York University
Winter 2013
|
Project #2
Voilà (Creating a Database):
The Seats Database
|
|
|
|
In
this project, you are to do the following.
- Design a relational schema based upon a provided E-R diagram.
- Write a creation script of SQL statements that will create your
relational schema as a database under the
class's IBM DB2 system.
- Populate your database with some provided data.
- 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.
Our patrons (customers) are:
- Cleverley, S., North York
- Lizzard, L., Willowdale
- Kyzar, V., Toronto
- Holdrich, A., Thornhill
- Brauch, N., Markham
- Horsnayle, U., Toronto
- Linane, H., Toronto
|
The theatres and their seat sections (and number of seats per section) are:
- Ottawa, 232 Arboretum Lane
- orchestra, 96
- main, 144
- balcony, 72
- side, 80
- Hamilton, 2133 Pond Road
- orchestra, 40
- main, 60
- side, 16
|
The time slots are:
- 05/28/2011 (Saturday 28 May 2011)
- 05/29/2011 (Sunday 29 May 2011)
|
The films are
<title, director, category,
length (in minutes), rating>:
- Once There were Glaciers, Silvia Pritchard, documentary, 103, G
- Which Way Falls Niagra Falls, Atom Ego, drama, 130, R
- The Unbearable Sadness of Being, Banana Yoshimoto, comedy, 110, PG
|
Scheduled is:
- Ottawa
- 05/28/2011
- 8:00pm, Which Way Falls Niagra Falls, gala
- 05/29/2011
- 10:00am, Once There were Glaciers, matinée
- 8:00pm, The Unbearable Sadness of Being, gala
- Hamilton
- 05/28/2011
- 3:00pm, Once There were Glaciers, matinée
- 8:00pm, The Unbearable Sadness of Being, evening
- 05/29/2011
- 3:00pm, Once There were Glaciers, matinée
- 8:00pm, Which Way Falls Niagra Falls, evening
|
Prices are:
- gala
- orchestra, 25.00
- main, 20.00
- balcony, 15.00
- side, 10.00
- evening
- orchestra, 20.00
- main, 15.00
- balcony, 10.00
- side, 7.00
- matinée
- orchestra, 15.00
- main, 10.00
- balcony, 7.00
- side, 5.00
|
Bookings are
<surname, initial; date, time,
tname, #seats, section, title>:
- Cleverley, S.
- 05/28/2011, 8:00pm, Ottawa, 3, orchestra,
Which Way Falls Niagra Falls
- 05/29/2011, 3:00pm, Hamilton, 3, orchestra,
Once There were Glaciers
- Lizzard, L.
- 05/28/2011, 3:00pm, Hamilton, 1, main,
Once There were Glaciers
- 05/29/2011, 8:00pm, Ottawa, 2, main,
The Unbearable Sadness of Being
- Kyzar, V.
- 05/28/2011, 8:00pm, Ottawa, 5, balcony,
Which Way Falls Niagra Falls
- Holdrich, A.
- 05/29/2011, 8:00pm, Hamilton, 2, side,
Which Way Falls Niagra Falls
- Brauch, N.
- 05/29/2011, 10:00am, Ottawa, 3, balcony,
Once There were Glaciers
- Horsnayle, U.
- 05/29/2011, 10:00am, Ottawa, 2, main,
Once There were Glaciers
- 05/29/2011, 3:00pm, Hamilton, 2, orchestra,
Once There were Glaciers
- 05/29/2011, 8:00pm, Hamilton, 2, main,
Which Way Falls Niagra Falls
- Linane, H.
- 05/28/2011, 8:00pm, Ottawa, 2, balcony,
Which Way Falls Niagra Falls
- 05/29/2011, 8:00pm, Ottawa, 2, orchestra,
The Unbearable Sadness of Being
|
|
|
|
|
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/29/2011 10:00 AM Ottawa Once There were Glaciers 3 21.00
1 Cleverley 05/28/2011 08:00 PM Ottawa Which Way Falls Niagra Falls 3 75.00
1 Cleverley 05/29/2011 03:00 PM Hamilton Once There were Glaciers 3 45.00
4 Holdrich 05/29/2011 08:00 PM Hamilton Which Way Falls Niagra Falls 2 14.00
6 Horsnayle 05/29/2011 10:00 AM Ottawa Once There were Glaciers 2 20.00
6 Horsnayle 05/29/2011 03:00 PM Hamilton Once There were Glaciers 2 30.00
6 Horsnayle 05/29/2011 08:00 PM Hamilton Which Way Falls Niagra Falls 2 30.00
3 Kyzar 05/28/2011 08:00 PM Ottawa Which Way Falls Niagra Falls 5 75.00
7 Linane 05/28/2011 08:00 PM Ottawa Which Way Falls Niagra Falls 2 30.00
7 Linane 05/29/2011 08:00 PM Ottawa The Unbearable Sadness of Being 2 50.00
2 Lizzard 05/28/2011 03:00 PM Hamilton Once There were Glaciers 1 10.00
2 Lizzard 05/29/2011 08:00 PM Ottawa The Unbearable Sadness of Being 2 40.00
12 record(s) selected.
|
Answer Table
|
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 attr1, ..., attrk;
This does a nested sort on the return table.
First, the tuples are sorted on attr1.
Then any group of tuples matching on attr1
(so now appearing adjacent),
these are sorted on attr2.
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 attr1 [ASC | DESC],
...,
attrk [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 Friday 4 March 2011.
You
should submit the following four files.
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.
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.)
query:
The SQL query that you wrote for the query above.
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.
|
|
To submit
You are to submit your project both in hardcopy
(to the dropbox)
and
in electronic form via the submit script on PRISM.
- 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-3421M / winter 2011
|
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
|
|
|
Designed by Parke Godfrey.
Edited by Dominic To.
|
|