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 players (“accounts”) in the online game (MMORPG) Quest. (Marketing has rebranded Loot! as Quest.)

E/R Diagram for the Quest MMORPG 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.)

We want to track players (Account) and the avatars that they create. A player may visit at most one of the games realms on a given day using one of his or her avatars.

There can be any number of quests available in a realm on a given day. A quest is only for the day. Any player visiting a realm may play in one of the realm's quests of the day. A player who has joined a quest is assigned a role in the quest.

Take a restrictive interpretation for all keys from the E/R design. Also, in the E/R, Visit does not capture the constraint that a player on a given day may only be in one realm. Add this constraint in your schema design.

Make all attributes not null in your schema, except for succeeded in Quest. Succeeded is null for any quest that was not successfully completed.

Use varchar(15) for all the “string”-type columns, except for gender of Avatar. For gender, use char(1) to record ‘M’ or ‘F’ for each avie. For succeeded in Quest, use time. And for joined in Account, use timestamp (which encodes a date and time together).

While Day is an entity in the E/R design, do not make a table for it. The date column type will work fine instead for this for day in tables referencing Day.

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 Quest MMORPG database would be much larger, of course.)

The players' accounts are as follows. In bold is the account name. Under each are the avatars that they have created.

1. agnetha. Sarah at 81 Franklin St, account created on 08/01/2016 at 17:50:44.

   Flutter Shy     F      faerie         
   Flit            F      faerie         
   Whisp           F      faerie         
   

2. astral. Jessie at 191 Howth Ave, account created on 05/28/2016 at 21:21:21.

   Lightning       F      raccoon        
   Elphaba         F      wizard         
   

3. bobby. Robert at 7 Avenue Rd, account created on 07/19/2016 at 12:00:59.

   Steve           M      wizard         
   Wayne           M      footballer     
   Smiley          M      shark          
   

4. cait. Caitlin at 444 Sycamore Dr, account created on 08/05/2016 at 23:51:17.

   Cait            F      raccoon        
   

5. dazzle. Alice at 1313 Denton St, account created on 04/30/2016 at 09:47:13.

   Beckham         M      footballer     
   Landshark       F      shark          
   Tinks           F      faerie         
   

6. dougy. Douglas at 92 Horace Way, account created on 07/21/2016 at 15:18:29.

   Wayne           M      footballer     
   Zounds          M      wizard         
   

7. growley. Franck at 218 Jane St, account created on 02/07/2016 at 15:32:08.

   Rocky           M      raccoon        
   Gandalf         M      wizard         
   

8. hills. Hillary at 1600 Penn Ave, account created on 04/05/2016. at 11:45:30.

   Fluffs          F      raccoon        
   Razor           F      shark          
   Ged             M      wizard         
   

9. lurker. Mark at 232 Bedford Ave, account created on 05/29/13 at 13:07:59.

   Mousey          M      raccoon        
   

10. macho. Tony at 887 Dufferin, account created on 06/27/2016 at 19:27:03.

    Bash            M      wizard         
    Kawaii          F      faerie         
    Scream          M      shark          
    

11. ollie. Oliver at 18 Castaway, account created on 07/01/2016 at 20:30:18.

    Ralf            M      raccoon        
    Tooth           M      shark          
    

12. storm. Felicia at 15 Davis Way, account created on 08/02/2016 at 22:58:22.

    Beyonce         F      faerie         
    Nibbles         F      shark          
    Lazarus         M      wizard         
    

13. surfette. Zeripha at 92 Horace Way, account created on 07/22/2016 at 16:05:41.

    Zerf            F      faerie         
    Mia             F      footballer     
    

14. thesix. Drake at 12 King St, account created on 02/29/2016 at 11:05:47.

    Beckham         M      footballer     
    Sweetiepie      F      faerie         
    

15. zedster. Quenton at 7 Vermont Ave, account created on 07-16-2016 at 12:00:01.

    Horatio         M      raccoon        
    Marvin          M      wizard         
    

16. zoood. Daena at 221 Baker St, account created on 08-13-2016 at 20:29:30.

    Smiley          F      shark          
    Sherlock        F      shark   
    

For each avatar, listed is the avatar's name, gender, and look. Record gender as a char(1). Enter an account's joined date and time as a timestamp column type. For eaxmple, for agnetha, her joined value would be entered as '2016-08-01-17.50.44'.

There are five looks for avatars: faerie, footballer, raccoon, shark, and wizard.

There are five realms: Buffalo, Camelot, The Deep, Valhalla, and Walmart.

Data from the other tables will dictate the day values that appear in the database.

The gaming house team sets up quests for given days in given realms. These are the quests in “insert” format.

These are the visits by players (accounts) as avatars to specific realms on specific days in “insert” format.

Players (accounts) visiting a given region on a given day may play in a quest that is being offered in the region on that day. When a player participates in a quest, he or she is assigned a role. Here are the instances of such in “insert” format.

Query: For each player (account) and avatar, list by the player's login, name (as “player”), and avatar's name (as “avatar”) the number of quests (as “#quests”) that the player has been on as that avatar. (Only count as long as he or she has been on at least one quest as that avatar; that is, if he or she has never been on a quest with that avatar, you do not have to report ‘0’.)

Order the result rows by login, player, and avatar (all ascending). Your results should match this answer.

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]

Due: by 11:59pm Monday 14 November 2015.

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.

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.

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 2016
  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 create schema data query answer

Or, if you have note file,

% submit 3421 create schema data query answer note