EECS-3421A
Introduction to Database Systems

York University
Fall 2015
Project #2
Voilà (Creating a Database):
The CQA Tournament Database
  The Project

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 to keep track of tournament matches for the Canadian Quiddich Association (CQA).

 
  The Conceptual Design

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.

  • For a roster, no position is assigned twice.

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.)

  • Each of the seven official positions (the values of Position) should be assigned per “roster”.

 
  The Data

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:

  1. Toronto Turnips (TT), founded 29 December 1862;
  2. Winnipeg Wyrmlings (WW), founded 21 June 1327;
  3. Halifax Hexors (HH), founded 11 April 1794;
  4. Quebec City Quadricorns (QQ), founded 5 September 1671; and
  5. 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.)

  1. 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’.

  2. 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’.

  3. 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.

  1. 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’.

  2. 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’.

  3. 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’.

Roster

A file of the rosters: roster.txt.

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.

 
  The Query

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.

 
  Deliverables
Due Date

Due: by 11:59pm Wednesday 4 November 2015.

Materials

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. 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.)

  5. 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
    EECS-Account  
    Course EECS-3421A / fall 2015
    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