ER Diagrams¶

Plan¶

• Overview of ER diagrams
• ER examples and exercises
• quiz questions
• breakout exercises

Announcements¶

• Grading status
• H4 partners assigned.
• Come to class/lab tomorrow. You can meet with your ER partner and continue working on the assignment
• Project ideas due next week. Let's talk about that.

H4 Partners¶

1. Jessica and Melissa
2. BB and Sophie
3. Joey and Ashley Yang (Olin)
4. Zoe and Emma
5. Nina and Kayla
6. Shelley and Naomi
7. Dana and Nessa
8. Sharon and Megan
9. Julie and Samiksha
10. Dora and Bessie
11. Ashley Yang (Wellesley) and Aayah
12. Ruth and Meg

ER Diagrams¶

• Pictoral, high-level design of entities and their relationships. Similar to UML diagrams
• Can be systematically converted into MySQL tables.
• Not all info can be captured.
• There are many variant drawing methods; we'll use a new one.

Here's an old ER diagram for the WMDB:

• There's an arrow from Movie to Directs to Person because of the N:1 directs relationship
• There's a line (no arrow) from Movie to Acts In (credit) to Person because of the N:N relationship
• There's an arrow from Movie to Adds to Staff because of the N:1 adds relationship (addedby)
• There's an arrow from Person to Adds to Staff because of another N:1 adds relationship (addedby)

Here's the ER diagram for the WMDB as created by DBDiagram.io:

• No diamonds; instead lines like 1:*
• The director relationship is just a line.
• There's still a credit table for the many-to-many relationship
• The staff table has two 1:* lines.

What are your questions?

Diagrams¶

• Entity Sets are boxes
• Attributes/columns are inside the box, along with their datatype
• Primary Keys are in bold
• Relationship Sets are lines
• result can be exported as MySQL code!

The diagram is created automatically from the DBML. You can then take a screenshot, particularly once you've arranged things to look nice. (It doesn't seem to remember my layouts.)

Quiz Questions¶

Most people felt pretty comfortable with this, which is great! But not everyone.

I'll answer your quiz questions.

Hobbies Exercise¶

Create an ER diagram for a hobbies relation. Just a sketch. Think about the SQL DDL.

Married Exercise¶

Create an ER diagram for a married relation. Just a sketch.

Write some DDL to implement it. (Just draft; no need to implement! This is not about syntax; it's about thinking about the representation.)

What is the key for that table?

If the key is a pair of IDs, then you can't have re-marriage by the same people!

Since you can't have duplicates of that pair, the same pair of people can't get re-married. E.g. Liz Taylor and Richard Burton were married in 1964, divorced in 1974, re-married in 1975 and re-divorced in 1976.

We could need to add a date or two to the relationship: married 1964-1975, married 1976-1977.

There are many choices for diagram and DDL; there's no one right answer. I'm happy to discuss yours in office hours.

married v1

married v2

create table married_to (
    husband char(9),  -- SSN
    wife char(9), -- SSN
    wedding date,
    foreign key (husband) references person(ssn) 
        on delete cascade
        on update cascade,
    foreign key (wife) references person(ssn) 
        on delete cascade
        on update cascade,
    primary key (husband, wife, wedding)
);

Together¶

We'll discuss these and I'll answer your questions.

Getting started on the Assignment¶

Let's get started on ER