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

The textbook (and our examples in the lectures) uses the Chen style of E-R modelling. There are many, many “dialects” of E-R. However, for consistency, stay with the textbook's (Chen's) 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.

Congratulations! You just received a letter from the Canadian Quiddich Association. Alas, it is not that they have discovered that you have latent wizardly talents and are inviting you to play for the League; rather, they have discovered your mastery of database design, and they are commissioning you to design a conceptual model (by ER diagram) to track the League's matches.

During the requirements gathering meeting, Chief Vincent Crabbe of the Canadian Quiddich Association explains what they want.

We are automating the recording of Quidditch tournaments. A tournament has a (unique) name and a start date. A tournament consists of a series of matches (games). A match usually consists of two teams playing against each other. However, by the Canadian Association's rules — and unlike the European Association's rules — a single match may consist of one, two, or more teams playing against one another! (Historically, there has never been a match of just one team; how silly that would be! But the rules allow for it. The match with the most teams ever had five! But more than five is allowed by the rules. Of course, the most common number of teams in a match is, by far, two.)

When the match ends, the team in the match with the most points is the winner. The other team(s) is/are the loser(s). It is magically ensured just one team will have the most points; there are no ties. Two teams will meet at most once in a match in a given tournament. By the end of the tournament, the one remaining team that has never lost a match in the tournament is declared the winner of the tournament.

A tournament match has a day and time that it starts. No two matches in the tournament start on the same day at the same time. (So day and time uniquely identifies the match in the tournament.) A match will take place at a given stadium, which we want to record. A stadium has a unique name, and its capacity — how many spectators it seats — should be noted.

A team has a team name that is unique to it. A player belongs to exactly one team. Each player is a assigned a League player number (player#) that is unique on his or her team. A team consists of at least seven players, but may have up to fifteen. (Just seven of the players of a given team play in a match.) We want to store a player's name, birthdate, gender, and house, as well.

A player for a team who plays in a match will be assigned exactly one position — e.g., chaser, beater, keeper, and seeker — that he or she plays during the match. (This could be called the team's “roster” for the match.) Note that a player for a given team need not play in every match that the team is in.

The database is also meant to record a match's “play”; that is, the important events of a match. Any event in a match is one of a list of actions we wish to record; this includes scoring events, fouls, and the like. An action has an associated points — e.g., 150 points for the chaser seeker catching the golden snitch — and a unique action name. (The action's points is set at zero for a non-scoring action such as a foul.) For each event that occurs during a match, we record the time (minutes and seconds, “m:s”) from the start of the match that the event occurred, and who the player is who performed the action. The player plus the time will be unique for an event in a match. We also need to record what type of action the event is.

The League will be charging spectators for the first time. (Quite unfortunately, the Canadian Quiddich Association is broke and we need to earn money.) So we will sell tickets to matches. Each match will have a ticket price. (All “seats” for a given match are the same price.) We shall assign each spectator — anyone who ever buys a ticket to one of our matches — a unique spectator#. We want to also record the person's name, address, and telephone. A spectator can only purchase a ticket for a match by a credit card. A spectator can have more than one credit card on file with us, but a given credit card can only be associated with one spectator. We need to record the ccard#, unique for the card, its expiry date, and its type (e.g., Mastercard, Visa, or Wizardbank). We need to record which card the spectator used to buy a given ticket.

You meet with your design firm's lead E/R person Dr. Mark Dogfurry to discuss CQA' design.

The requirement that a match can consist of one or more teams seems odd. I would usually have the team entity connnected to a, say, relationship “match” twice. But what now?

Well, they did not say anything about a match having a key. All the same, we might consider to make match an entity. We would have to introduce a surrogate key for it, say, mid. Then team and match can be related; a match to any number of teams, and a team to any number of matches.

The more I think about it, that is the only way we will be able to handle that one, two, or more teams can participate in one match. This does mean we will not have any easy way in our design to enforce that no two teams meet in more than one match within a tournmament. We would have to note that we do not enforce that, if not. (And that it should be accommodated later in the relational design, if possible.)

How do I record the scores of each team in a match and who the overall winner of the match is? And the winner of the tournament?

Careful! You are thinking process model, not data model. And an E/R diagram is a data model. Is the winner of the match derivable from the information that is recorded in your diagram? If so, then you need not add anything more to represent explicitly the winner. In fact, to do so would be a logical redundancy; the stated winner might be different than what computing the scores for the match would indicate.

One thing our diagram ought to capture, it seems, is that any player recorded to play for a team in a given match (the “roster”) ought to be actually a player who belongs to that team. CQA did not mention that explicitly... But this is not possible to capture in E/R, no, so this requirement is safe to ignore, is it not?

Hardly! It is certainly possible to represent, and you will want to. Consider how you can work with weak entities and with aggregation perhaps to accomplish this.

...

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

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 tournament 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. For example, if there is/are ISA situation(s), indicate the overlapping and coverage constraints next to the ISA symbol(s).

   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 just do not accommodate them.

Due: Before class Tuesday 4 February.

Hand in a hardcopy of your project. This should include:

• ERD
  Your full E-R diagram for the prescriptions database.
• Documentation
  Paragraphs explaining details about the design.
  • Any clarifications about your ERM 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 ERM.
  The documentation may be minimal, or even absent, if your model has no such needed clarifications.

Hand-written is fine, but, if so, use pen, not pencil. Do not use binders, folders, paper clips etc. Use standard size paper (8.5" x 11"), and have the sheets stapled together.

Have a cover page for submitting your work, filling out your student number, etc., as follows:

Student Number
Sur (Family) Name
Given Name

Please drop off your assignment in the CSE-3421 drop-off box in the Lassonde (Computer Science & Engineering) Building (attached to Lassonde #1003). Or you may hand in to instructor at the start of class.

Your ERD would be much prettier typeset rather than handwritten. And it would likely be much, much easier to produce, considering multiple iterations are involved. There are a number of professional tools companies use for E/R, but many are rather expensive and specialized.

Many general drawing packages that include diagram / semantic support work nicely, however. They take a litle bit of time to learn, unless you know one already. But likely worth the investment.

• Visio: a Microsoft product that is not free, but many do own it.
• OmniGraffle: an Apple product that is not free, but many do own it.
• Dia: a free, open-source product available for Linux, Mac OSX, and MS Windows. It is quite adequate for the job. (Use its general templates, though, not its specialized ER templates; those are for a quite different dialect.)
• XFig: a free, open-source product available for Unix / Linux — and for Mac OSX for those who run XCode. Is on the PRISM machines. An ancient diagramming package that is hard to use, and is painstaking. But does the job.

Some of the diagramming packages may be missing one or two things the Chen ER dialect demands. For example, there seems to no easy way to underline text in Dia, but key attributes should be underlined (and partial key attributes in weak entities underrlined with a dotted underline). In this case, you could use bold for key attributes (and partial key attributes in week entities) instead. Just make note with your ER design document any such notational changes you make.

The best way to learn E/R modelling well is lots of practise! Look through the examples in the textbook Look through the examples in the exercises linked on the class homepage. And here is the corresponding E/R project from my class last year:

• E/R Project from winter 2012
• a solution