|
EECS-3421A
Introduction to Database Systems
York University
Fall 2016
|
Project #3:
SQL Jeopardy:
Querying the Raccoon-Rhapsody Database
|
|
|
|
|
|
In
this project,
you will work with an existing database,
the Raccoon Rhapsody Database (RR-DB).
This is a continuation of the schema that you all have
worked with in the first two projects.
The Quest marketing team did it again;
Raccoon Rhapsody is the name of our game
for market.
Each will install his or her own private copy of the database
on which to work.
For SQL Jeopardy,
you are to implement a number of SQL queries over the RR-DB.
The project is 10% of the total grade.
There are ten queries that you are to write.
Each is worth one point for a total of ten points for Project #3.
I am the manager and I provide you
for each one
with English specifications
in the form of a statement,
which you will turn into a question
—
an SQL query against the RR-DB, actually
—
like in the game show Jeopardy.
Each query is given a name for bookkeeping.
Also, the answer table for each is provided.
This is so you can check whether
you have likely implemented the SQL query correctly
by seeing whether you get the same result
when run in DB2 on the RR-DB.
(Note that, however,
this is not proof that your query is entirely correct.
A logically incorrect query with respect to the requirements
would give the wrong answers on some instance of the database.
And one might have cheated by making a simple query on purpose
that manages to print the same results on this instance of the database!)
Grading per query is all-or-nothing.
Either your query (always) produces the right results,
or it does not.
(I likely will test the queries for grading against a different
instance of the databases.)
The queries below are arranged roughly in order of increasing difficulty.
The ones at the beginning are straightforward,
while the ones towards the end are difficult.
Do not get too discouraged if you are unable to do the final few.
Honesty:
For projects, you are permitted to confer with others,
seek advice, and (to a reasonable extent) help.
However, remember that copying someone else's queries
and claiming them as your own work is plagiarism.
You must do your own work.
|
|
|
|
|
|
The Raccoon-Rhapsody Database
|
|
|
Two
scripts are provided:
The script rrdb-create will create the RR-DB schema for you.
and
will also populate the tables with the mock data.
The script rrdb-drop is provided for convenience.
It will drop your copy of RR-DB from your DB2 schema space.
If you mess things up,
you can always drop RR-DB and then re-create it easily.
To create the RR-DB in your DB2 schema space:
Read the schema definition in rrdb-create for RR-DB
to understand the design and what the RR-DB is about.
You will notice that it is an extension of the Quest
database schema that you worked with in the first two projects,
with minor modifications.
Players can earn loot
for the quests that they participate in that are successfully
completed.
Each piece of loot is a type of treasure.
Each type of treasure has a value associated with it
in the game's in-world currency
—
commonly called “scrip” in gaming parlance.
Our in-world currency officially is sql
for Standard Quest Loonie.
(Players can turn in pieces of loot they have received
for the sql,
or keep the loot.
The pieces have uses in the game.
This is not modelled so far in the database.)
|
|
|
|
|
|
|
1. avies
Players by login and name
along with their avatars by name as avatar
and race.
schema:
login, name, avatar, race
order by login, name, avatar
answer table for avies
|
|
|
2. golden
Each quest by realm, day,
and theme which offered a prize (treasure)
with “Gold” in the name which was rewarded to some player.
schema:
realm, day, theme
order by day, realm, theme
answer table for golden
|
|
|
3. twotimer
Each player by login and name
who managed to participate
in more than one quest on the same day along with those quests
by day, realm, and theme.
schema:
login, name, day, realm, theme
order by login, name, day, realm, theme
answer table for avies
|
|
|
4. sole
Each realm (by realm) with each treasure
(by treasure) along with
that treasure's value in scrip (as sql)
that only that realm has ever offered
as loot via some quest in that realm.
schema:
realm, treasure, sql
order by realm asc, sql desc, treasure asc
answer table for sole
|
|
|
5. seconds
Each player by login, name, and
address with the total number of seconds
that he or she has played in quests (seconds).
-
Assume that all quests start at 6pm (18:00) on their
day and continue to their completion time
(
succeeded)
or end at midnight if unsuccessful
(succeeded is null).
Assume all times here and in the database are in the
Toronto timezone.
-
Assume a player in a quest had to sign up before 6pm on
the day of the quest, and is assumed to have participated
from 18:00 to the end of the quest.
-
Only count players who have participated in some quest.
schema:
login, name, address, seconds
order by login, name, address
answer table for seconds
(fixed)
|
|
|
6. swap
Each player by login, name, and
gender who entirely gender swapped
with their avatars, along with the count of how many avatars
that he or she has (#avatar).
-
Gender swapped is when the player and avatar are
opposite genders. Entirely gender swapped is when all
the person's avatars are the opposite gender to him-
or herself.
E.g., a woman with all male (‘M’) avatars.
-
Report only for players who have some avatars.
schema:
login, name, gender, #avatar
order by login, name, gender
answer table for swap
|
|
|
7. toppers
Each realm and gender (of avatar)
with the race whose avatars of that gender
earned the most scrip (sql) collectively from
loot rewarded in quests in that realm, along with the
what that race and gender collectively earned in quests
in the realm (total).
-
In case of ties for most in a region,
list all that tied.
schema:
realm, gender, race, total
order by realm, gender, race
answer table for toppers
(fixed)
|
|
|
8. potential
For each avatar by login, avatar's name,
and race,
the scrip (sql)
that the avatar would have earned (earned)
if the avatar had been rewarded
the prize (loot) of highest value
(and just that prize, one piece of loot)
for each quest that the avatar participated in
that was successfully completed,
and how many successful quests the avatar has
participated in (#quest).
-
In case of an avatar that did not participate
in any successful quest,
list zero for both
earned and #quest.
-
In case of ties for prize of highest value in a successful quest,
consider that the avatar would only receive one.
schema:
login, name, race, earned, #quest
order by login, name
answer table for potential
|
|
|
9. themestat
Each realm's theme by realm and theme
with how many quests of that theme
in the realm have occurred (#quest),
how many distinct players have participated (Actor)
as #player,
the number of distinct avatars
that have particpated as #avatar,
how many “actors” have participated in total over quests
of that theme in that region as #actor
(that is, the sum of participant counts over the quests),
the total scrip (sql)
offered as offered,
and the total scrip (sql) rewarded to players
as #rewarded.
-
Note that quests are grouped in recurring themes within a realm.
For example, the theme of "Airport Chaos" was used for quests in the
realm ‘Buffalo’ on days ‘08/06/2016’, ‘08/14/2016’, ‘08/21/2016’,
‘08/27/2016’, ‘09/04/2016’, ‘09/11/2016’, ‘10/16/2016’,
and ‘10/23/2016’.
schema:
realm, theme, #quest, #player, #avatar, #actor, offered, rewarded
order by realm, theme
answer table for themestat
|
|
|
10. follows
Each avatar by login as follower
and avatar's name as fname
whose particpation in quests within a given realm
(as realm)
has always been together with a second avatar
by login as leader and name as lname
who has participated in the same quests,
such that the follower (and hence, leader, too)
has been on at least two quests in the realm.
-
Note that the leader may have participated
in more quests in the realm than the follower.
schema:
follower, fname, realm, leader, lname
order by follower, realm, leader
answer table for follows
|
|
|
|
|
|
|
Due by 11:59pm Friday 2 December 2016.
For
each query,
write your query in a file with the corresponding name as above
(avies,
golden,
twotimer,
sole,
seconds,
swap,
toppers,
potential,
themestat, and
follows).
Do not include the answers generated by each of your queries.
We will be testing your queries ourselves for grading your project.
When you are finished,
use the submit command to turn in your work.
- % submit 3421 rrdb
avie golden twotimer sole seconds
swap toppers potential themestat follows
Should you not do some of the queries,
just leave those files out.
Note to get credit for each query,
it must produce the same number of columns
with the same names as in the description,
and the correct answer tuples.
You do not have to turn in a hardcopy of this project.
|
|
|
|
|
|
|
with clause
One
may have more tables in the with clause, if need be:
with
first (...) as
(...),
second (...) as
(...),
third (...) as
(...)
select ...
...;
|
Note that you can use the (temporary) view first
inside the definition for second,
and the views first and second
inside the definition for third,
and so forth.
DB2 is picky about nested SELECTS always being named
via AS.
For example,
select ...
from (select ...) as A,
(select ...) as B,
...
...;
|
Even if you do not need the alias names A or B
later on, give the sub-queries (nested SELECTS) names anyway.
Otherwise, DB2 will complain.
|
|
|
|
|
|
|
|