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 as described in the textbook and in class.

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.

You have had the misfortune fortune to be hired into the most recent start-up venture of the famous database researcher, Dr. Mark Dogfurry. The start up is called The Howling. They are making an on-line social messaging system. No, it is not the same as that silly bird one. The Howling will be much, much better. Of course. Trust us.

You have been hired as their lead database architect. Your first job is to design the basic schema that will be the heart of the social messaging service.

During the requirements gathering meeting, Mark Dogfurry explains what they want.

The Howling has a theme, as most of these things do. We are a social messaging service. People will post messages, which then are read by the people in the groups to which they are posted.

People who sign up for our service are called pups. These are our customers. A pup can “bark” a message, which we call a bark. The bark is posted to one or more groups, which we call a pack (for “pack of dogs,” of course), to which he or she belongs. Every member of the pack sees the stream (the howling) of the barks posted there. For a pup, we want to keep track of his or her name, address, and cc# (a valid credit card number we keep on file). We also have a petname, which is the “nickname” the pup goes by in the service. He or she can change this petname as wanted. To keep track of our pups, a dogtag provides a unique ID. This is just used internally. A pup should belong to at least one pack. (We will likely have at least one generic pack, say, called “general”, to which all new members can be added.)

Each pup belongs to one or more packs. A pack is a “discussion group” in The Howling. A pack has a founder (the person, pup, who created the pack), a leader (a pup who acts as a forum leader for the pack), and a packname which uniquely names the pack. Pups belong to packs; they can join and leave packs as they wish. (Someone might join, leave, and join a given pack again. That is possible.) We want to keep track of when a pup joins a pack (start), and when they leave it (stop). (The stop field will just not be filled in —null— if the person is still a member.) We want to keep record of old memberships, even afer a pup has left a pack.

A bark is posted by a pup (the barker) to one or more of the packs to which he or she, the barker, belongs. A bark is uniquely identified by its barker and the date and time of its posting. A bark consists of a text message of up to 80 unicode characters. (None of that long-winded stuff you find in that bird social messaging!) E.g., “Release the hounds! Let The Howling commence!”

In a bark message, there can be references to other pups. These are indicated by use of the character “🐶” in the message. (Hovering with the mouse over one in what will be our app will show the petname of the pup in question.) There also can be references to other barks. These are indicated by use of the character “💬” in the message. (Hovering with the mouse over one in what will be our app will show the bark in question.) E.g., “🐶's bark💬 was so funny!” To keep track of these, we will need “links” from the given bark to pups mentioned in the message and to other barks mentioned in the message. With each link, record a position for which “🐶” or “💬” in the message string it refers.

When pups sign in, they can read the howling of any of the packs to which they belong. A howling is essentially the stream of barks (and yips) that have been posted to the howling (stream) by member pups. To keep track of what barks the pup has seen in the stream, we should keep a date and time. We will assume that he or she has read all the barks up to (and including) that date and time; and that anything newer, he or she has not seen yet. Note that if the same bark is posted to several packs to which the pup is a member, he or she will see that same bark in each howling / pack's stream.

A pup may also “woof”. A woof is a bark that the pup is reposting (barking). The initial bark could have been posted initially by someone else. We must record a link with a woof to the original bark of which it is a reposting.

Lastly, a pup may “yip”. A yip is a one word response to a bark in a howling (a pack's stream). (A given yip is only within a given howling / stream, while a bark may have been posted to several howlings / streams.) E.g., “Fantastic!” All yips to a bark essentially get “attached” to the bark in that howling, and get shown to a reader of the stream at the time the pup reads the bark. For a yip, we record the pup who yipped it, the bark (and in which howling to which it was yipped), and the date and time it was yipped.

Dogs are pups who are allowed to be “leaders” of packs. (Not any pup may lead a pack; only a pup who is a dog may.) Top dogs are dogs (and so are also pups) who are allowed to found (create) new packs. (Not any pup or dog may found a new pack; only a dog who is a top dog may.)

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

Is it that a bark can only be posted to packs for which the pup posting the bark is a member?

Yes.

...

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 Wednesday 14 October 2015.

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 practice! Look through the examples in the textbook Look through the examples in the exercises linked on the class homepage.

• E/R project from winter 2013:
  • E/R Project: A Prescription DB
  • a solution
• E/R project from winter 2014:
  • E/R Project: The Canadian Quiddich Association Match DB
  • a solution