This assignment has two steps. In step one, you will develop an entity-relationship diagram (ERD) for the attached case. In step two, you will create relational tables for the ERD that we will provide. For step one do the following. 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.

You need to design a database that stores information about students, courses, and instructors. Instructors are identified by an ID; we also store information about their name and salary. Students are also identified by an ID and have names and total credits taken. Courses are identified by Course_id; we need to record their titles, credits and prerequisites (which are other courses). Each course is offered by a single department. Students take courses by registering for a particular section of a course which is identified by Course_id, Section_id, semester, and year in which it is offered. After completing a section of a course, students receive grades. Similarly, instructors teach sections of courses taken by students. Suppose also that each student must have another instructor who serves as a department advisor (undergraduate or graduate).

Instructors and students belong to unique departments which have names, budgets, and are located in buildings. Each section takes place in a particular classroom and a particular time slot (which can cover more than one day, e.g. MW 10-11AM is one slot). Classrooms are located in buildings and have room numbers and capacity. Time slots are identified by slot_id, have day(s), start time, and end time as attributes.

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”. However, you must include constraints which are reasonable in real world (e.g. a student has only one advisor).

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

4. Use only one type of arrow - sharp - for this project.

5. 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: June 4 at 9:30PM (no extensions!)

Hand in a hardcopy of your project.

This should include the following.

• E/R Diagram
  Your full E/R diagram for the University database.
• Documentation (optional)
  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).

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

• Microsoft Visio,
• OmniGraffle (The Omni Group),
• The Dia Diagram Editor, and
• LibreOffice (its Draw facility) of The Document Foundation.

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.