EECS-3421A
Introduction to Database Systems

York University
Fall 2016
Project #1: Scheming
Loot!
 
  Assignment

Develop an entity-relationship diagram (ERD) for the attached case. Clearly identify the entity sets, relationship sets, multiplicity (i.e., many-one, many-many, etc.), attributes, keys, and constraints in your model, using the notations and diagramming rules as described in the textbook and in class.

The textbook — and our examples in the lectures — uses the “Stanford” style of E/R modelling. There are many, many “dialects” of E-R. However, for consistency, stay with the textbook's dialect.

Warning: A different textbook was used previously in this course which used a different dialect (the “Wisconsin” dialect) of E/R. There is lots of “3421” materials around with E/R examples written in the Wisconsin dialect. Feel free to use these for studying and for reference, but be aware of the differences. And do your work in the Stanford dialect.

Keep in mind that the case is designed to simulate a real life system-analysis situation; hence, the conversations are unstructured, and sometimes fuzzy. It is your responsibility — as an analyst / designer — to translate what you heard / read into an ERD.

The Task

Congratulations! You have just been hired as the lead database architect at Questeme, Toronto's newest start-up gaming house. Your first job is to design the basic schema for Questeme's pilot project for a new virtual reality MMORPG (Massively multiplayer online role-playing) game, Loot! This will change the face of on-line gaming! Trust us. It will be that good.

 
  Requirements
Requirements Meeting

During the requirements gathering meeting, Mark Dogfurry, the lead game architect for the Loot! pilot, explains what they want.

Loot! is a quest-based game. But it will be so much better than previous quest games. It will be VR, for one thing!

People who sign up for Loot! — call them players — will have accounts. For each player's account, we need to keep track of the player's name, address, the date that they joined, a cc# (a valid credit card number we keep on file), and a balance (how much money in Canadian dollars they presently have in their game account). Each player will also have a unique login, which is the account's “name”. It is assumed that a given player will have just one account.

A player may create over time any number of avatars. An avatar is an in-game persona. Thus, an avatar belongs to a player (an account). An avatar has a name, gender, look, and skill level. There is a small set of looks available, which may be extended over time; e.g., “wizard”, “faerie”, “footballer”, “shark”, and “raccoon”.

The game's virtual word is divided up into a hundred or so realms. On any given day, a player may log in (visit) as one of his or her avatars into one of the realms. (The player may not then switch avatars or realms for that day.) Of course, some days, a player might not log in at all.

Realms will be intricate, amazing virtual worlds unto themselves! But, for the purposes of this database, we just need to know the realm's unique name (realm).

The Questeme adventure-scripting staff will be in charge of creating daily quests. We will try to ensure that each realm on each day has several quests available. Thus, a given quest is associated with a day and a realm. Quests will be intricate, amazing challenges! But, for the purposes of this database, we just need to know the quest's name (quest). The quest's name is guaranteed to be unique on its day in its realm. (That is, there will not be two quests named the same thing on a given day within a given realm). And, for a quest, we need to keep the time it was completed, if it was completed. (This is explained more later.)

A player when he or she logs into a realm — as one of his or her avatars — on a given day may just hang out. (Believe me, our realms will be amazing! The most amazing realms you've ever hung out in!) Or their avatar can join one of realm's daily quests. We call this acting in a quest. When an avatar (the player as the avatar) joins a quest, they choose a role to play.

Associated with a quest is loot. Each piece of loot is a type of treasure. Each treasure type has a unique name, and a description (desc). A quest can have any number of pieces of loot. And, of course, each treasure type can be loot for any number of quests.

If the quest is successfully completed by its “team” — that is, by the avatars signed up for (acting in) the quest — then the quest's loot is given to those players. (The most famous treasure in the game is a golden lute. Lute. Loot. Get it? Snickers.)

Each piece of loot from the quest will be given to just one of the players who participated in the quest (as decided by the game engine). We need to record who has been given what. If a quest was successfully completed, we should record the finish time. (A quest is just for a given day, so we already know the day.)

And that is pretty much it for now! We would like the schema design to capture tightly the game logic as described above. We want the database to be able to protect against incorrect data updates to protect the integrity of the game.

Internal Discussion

Periodically, you meet with Dr. Mark Dogfurry to discuss the schema design.


Will I need weak entities in my design? This smells of weak entities. I don't like weak entities!

Wait, why did I hire you again? You're the designer!

But I can see where connecting entities could be very useful here.


...

Any other clarifications or important questions raised about the design requirements sent my way — and any hints deemed needed — may appear here as addressed by Dr. Dogfurry.

Considerations

Keep these things in mind for your design.

  1. In real life, many more data elements than described in the “requirements” above would be needed to build a useful quest database. We do not, however, want to turn this project into something huge. So keep in mind that this is a highly simplified case.

  2. As a general approach, if a particular constraint is not explicitly given, then assume the least restricted situation. For example, “many” in ERD is less restricted than “at most one”.

  3. You are not required to specify the domains of the attributes in this project.

  4. Make certain that you clearly indicate any constraints in your design in addition to those captured in the logic of your E/R diagram.

    State any constraints that are indicated by the requirements but that you know are not enforced by your design in brief documentation attached with your design. Explain whether the unenforced constraints are beyond the scope of E/R, they seem to be beyond the scope (but you do not know for certain), they would greatly complicate the design beyond value, or that you simply do not accommodate them.

 
  Deliverables

Due: By the end of Friday 7 October 2016.

Hand in a hardcopy and submit an electronic copy in PDF of your project. (I will mark the time of submission as when the electronic copy was submitted. If you are not on campus that Friday, you can drop off the hardcopy before next Wednesday's class. I just want the hardcopies for marking purposes.)

This should include the following.

  • E/R Diagram
    Your full E/R diagram for the Loot! database.
  • Documentation
    Paragraphs explaining details about the design.
    • Any clarifications about your E/R diagram that are not evident in the model itself.
    • Any assumptions you had to make with respect to the requirements. (In the real world, these would then be resolved in a second requirements meeting.)
    • Any constraints (business rules) apparent from the requirements that you are unable to model via your E/R diagram.
    The documentation may be minimal — or even absent — if your model has no such needed clarifications.

Your project must be typeset. Have a cover page for submitting your work, filling out your student number, etc., something as follows.

Student Number
Sur (Family) Name  
Given Name  

Drop off for the hardcopy of your assignment at the EECS-3421 drop-off box in the Lassonde Building (adjacent to Lassonde #1012).

Submit online your PDF as follows.

% submit 3421 scheming your_file.pdf

If you are submitting your project late (until the end of Sunday), use

% submit 3421 scheming-late your_file.pdf

 
  Resources
Diagramming Software

There are a number of professional tools companies use for E/R, but many are rather expensive and specialized. They often provide lots of additional functionality, such as verifiers and automated tools to help translate to relational.

Many generic drawing packages that include diagram / semantic support work nicely, however. Some suitable applications good for drawing E/R are

Visio and OmniGraffle are great, but somewhat expensive proprietary drawing and generic diagramming applications. Dia is not bad free and open source software under the GNU General Public License version 2.0 (GPLv2), which I used to use. I recommend LibreOffice, which is excellent free and open source software under the Mozilla Public License (MPLv2.0). It is what I am using for the E/R in the class slides. This is on PRISM machines, is in the OS image that EECS distributes, and is freely downloadable.

There are any number of other drawing applications that would work. There are also many quite expensive professional packages on the market for E/R.

Some of the diagramming packages may be missing one or two things kind of needed for E/R. For example, there seems to no easy way to underline text in Dia, but key attributes should be underlined. In this case, you could use bold for key attributes (and partial key attributes in week entities) instead. Just make note in your E/R design document any such notational changes you make due to drawing-application limitations. Pick some reasonable, obvious convention for your arrowheads, following the textbook's style, or the class's lecture style, or something obvious and similar.

Practice

The best way to learn E/R modelling well is lots of practice! Look through the examples in the textbook and the slides.

Look through the examples in the exercises linked on the class homepage (and here, below) and previous E/R assignments in this course.
Warning: The E/R in these are in the Wisconsin dialect, not the Stanford dialect.