|
CSE-3421M
Introduction to Database Systems
York University
Winter 2014
|
Project #2
Voilà (Creating a Database):
The CQA Tournament 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
to keep track of tournament matches
for the Canadian Quiddich Association (CQA).
|
|
|
|
|
E-R Diagram for the CQA Tournament Database
|
The
conceptual design is similar to the E-R you did for Project #1,
but it is not entirely the same.
(Less is being tracked here.)
A Match may consist of one, two, or more Teams
(but usually is two).
Each Match is part of a Tournament .
For Team ,
founded is the date the team was founded.
The call
of a team is a two-letter code that uniquely
identifies it within the CQA;
e.g., “TT” is the Toronto Turnips.
For Player ,
dob is a date and means
“date of birth”.
The attribute #points records the total score
for that Team in that Match .
A Team may have up to ten Players .
Each Association Player
plays for exactly one Team .
For a match,
seven of the team's players play,
one assigned to each position.
This is the roster .
No replacements or changes of position are permitted
for the duration of a match.
A Match 's
start and end
each is a date and time
(an SQL timestamp ).
No two matches in a tournament start the same time.
In addition to the E-R,
we intend the following contraints
to be captured by the database schema.
We also intend the following contraint is held,
but do not insist it is captured by the database schema.
(In fact,
there is no easy way to do this,
and you are not requested to.)
|
|
|
|
Here
is the sample data that you should load into your database.
(This is just sample data for purposes of testing your schema.
The working CQA Match database would be much larger,
of course.)
|
|
Team
The
Teams are as follows:
- Toronto Turnips (
TT ),
founded 29 December 1862;
- Winnipeg Wyrmlings (
WW ),
founded 21 June 1327;
- Halifax Hexors (
HH ),
founded 11 April 1794;
- Quebec City Quadricorns (
QQ ),
founded 5 September 1671;
and
- Edmonton Eaglets (
EE ),
founded 1 January 1949.
|
|
Player
A
file of the Players :
player.txt.
A player# is a two-letter sequence.
(It was meant to be a number,
but the CQA was not clear on that.
So various teams have been inventive!)
|
|
Position
The
values of
Position are
Left Chaser,
Centre Chaser,
Right Chaser,
Left Beater,
Right Beater,
Keeper,
and
Seeker.
|
|
Stadium
The
Stadiums are
the Frontenac,
the Champlain,
the Eagle Perch,
and
the Rocky Bludger.
|
|
Tournament
There
are two Tournaments :
the Quebec Classic
which started 9am Saturday June 1, 2013
(timestamp ‘2013-6-1-09.00.00’);
and
the Edmonton Event
which started 2pm Saturday November 16, 2013
(timestamp ‘2013-11-16-14.00.00’).
|
|
Match
There
were three matches at the Quebec Classic.
(Final points per team listed after the team name.)
9am Saturday June 1, 2013
(‘2013-6-1-09.00.00’)
at the Frontenac:
- Toronto Turnips (90)
- Halifax Hexors (210)
Ended ‘2013-6-8-23.42.19’.
11am Saturday June 15, 2013
(‘2013-6-15-11.00.00’):
at the Champlain
- Winnipeg Wyrmlings (0)
- Quebec City Quadricorns (150)
Ended ‘2013-6-15-11.03.17’.
12:01am Sunday June 23, 2013
(‘2013-6-23-00.01.00’)
at the Champlain:
- Quebec City Quadricorns (330)
- Halifax Hexors (340)
Ended ‘2013-6-23-16.27.03’.
There were three matches at the Edmonton Event.
2pm Saturday November 16, 2013
(‘2013-11-16-14.00.00’)
at the Eagle Perch:
- Winnipeg Wyrmlings (150)
- Halifax Hexors (110)
- Quebec City Quadricorns (30)
Ended ‘2013-11-17-04.18.11’.
12pm Saturday November 23, 2013
(‘2013-11-23-12.00.00’)
at the Rocky Bludger:
- Toronto Turnips (170)
- Edmonton Eaglets (320)
Ended ‘2013-11-23-21.33.59’.
12:01am Sunday December 1, 2013
(‘2013-12-01-00.01.00’)
at the Eagle Perch:
- Edmonton Eaglets (70)
- Winnipeg Wyrmlings (180)
Ended ‘2013-12-04-10.14.05’.
|
|
|
Fixes
The
initial roster.txt
file had a mistake in syntax in some
of the timestamp data.
And it had a mistake in the positions data:
it had “Right Chaser”
instead of “Right Beater”.
(This meant each “roster”
had two right chasers!)
These have been fixed.
Download the (fixed) roster.txt file again,
if you need it.
|
|
|
|
Query:
For each of the players who has played in a tournament,
list the player by
call,
name,
and
player#,
along with the total minutes
that he or she played across the tournaments.
Order the result rows by
call,
name,
and
player#
(all ascending).
Your results should match this:
answer.txt.
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]
To get the minutes that is the difference of timestamps
start and end in IBM DB2:
timestamp(4, end - start) .
The “4” is asking for minutes as the unit.
|
|
|
|
Due Date
Due: by 11:59pm Tuesday 11 March 2014.
|
|
Materials
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.
answer:
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.)
note (optional):
An ASCII text file with any clarifications
of the above,
if you feel it necessary.
All your files being submitted must be ASCII text
files.
Files in other formats
—
MS Word, PDF, etc.
—
will be ignored.
|
|
Example
As
an example of an SQL create script,
see yrb-create.txt,
the SQL-create script for the York River Bookseller database.
Note that
this file has both the schema and the data
in the same file;
you are asked to put these in separate files,
however.
The file
yrb-drop.txt
is a simple file of SQL drop commands that clears out
the YRB database.
You may find you want to make a
“drop” file too for your CQA Tournament database,
as you will likely find the project is quite iterative.
|
|
Submission
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 2013
|
Family Name
|
|
Given Name
|
|
Attach printouts of the four files:
schema,
data,
query,
and
answer
(and optionally note).
- online copy
To submit online:
- % submit 3421 cqa schema data query answer
Or, if you have note file,
- % submit 3421 cqa schema data query answer note
|
|
|
|
Parke Godfrey
|
|