|
EECS-3421A
Introduction to Database Systems
York University
Fall 2016
|
Project #1: Scheming
Loot!
|
|
|
|
|
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 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.
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.
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”.
You are not required to specify
the domains of the attributes
in this project.
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.
|
|
|
|
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
|
|
|
|
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.
- Assorted exercises covering
the relational model,
E/R design,
relational design,
relational algebra & calculus,
and
normalization:
-
E/R project from winter 2013:
-
E/R project from winter 2014:
-
E/R project from Fall 2015:
|
|
|
|