Week 1 - Create an ER diagram

Please use Canvas to return the assignments: https://ucsb.instructure.com/courses/19301/assignments/236835

Create a physical ER (entity-relationship) diagram for the Harry Potter tables shown in class. It will be helpful to refer back to the slides.

As discussed briefly in class, a logical or conceptual ER diagram focuses on high-level abstractions, and doesn’t address how entities and relationships actually get implemented. In particular, in a logical ER diagram a many-to-many relationship between two entities might be represented by a simple line, even though in implementation a many-to-many relationship requires a separate table to store the relationship tuples. By contrast, a physical ER diagram describes actual tables. You are being asked to create a physical ER diagram.

Requirements:

• Your diagram should include Student, House, Wand, Course, and Enrollment tables.
• Each table should list the name of the entity, any attributes, and which attribute(s) form the primary key, if there is one.
• A foreign key relationship from an attribute in one table to an attribute in another table should be indicated by a line between the two attributes. The ends of the lines should reflect the cardinalities at each end. See the example below.

Twist #1! The slides shown in class demonstrated a many-to-one relationship between wands and students, i.e., one student might own multiple wands, but any given wand has only one owner. However, for this exercise, you are being asked to model a many-to-many relationship between wands and students (it happened in the books that the same wand was used by different students, though at different times, of course). To create a many-to-many relationship, you will need to invent an intermediate table that represents the student-wand ownership relation, in the same way the Enrollment table intermediates between the Student and Course tables.

Twist #2! You must also store the date range (i.e., begin date and end date) of wand ownership. You will need to think where these date attributes belong. Are they attributes of a student? Of a wand? Of something else?

Various symbologies have been developed for ER diagrams. For this assignment, represent the “one” side of a many-to-one relationship by a single vertical bar, and represent the “many” side by a so-called crow’s foot. In the end, your diagram should visually resemble something like this:

You can use a tool like dbdiagram.io as was used to create the above diagram, or any other drawing tool. Or you can just draw it by hand and take a picture with your phone. Regardless of the method, be sure to indicate primary keys somehow (bold text, underlined text, add “PK” next to the attribute, etc., whatever works visually).