Welcome!
Everything is fine.

ER Diagrams

Plan

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

Announcements

  • H4 partners assigned.
  • Project ideas due next week

H4 Partners

  • 2 Nicole+Sana
  • 4 Ashley Cong+Fangzhong
  • 6 Bella+Sophie
  • 8 Vaishu+Winnie
  • 10 Anjali+Jasmine
  • 12 Ashley You+Sophia
  • 14 Hannah+Linh
  • 16 Ariel+Daisy
  • 18 Maya+Yuneydy
  • 20 Flora+Makana
  • 22 Indira+Mukhlisa
  • 24 Chloe+Maddie
  • 26 Ali+Diya
  • 28 Claire+Kathy

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:

The Wellesley Movie Database (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 new ER diagram for the WMDB:

The Wellesley Movie Database (WMDB) created using 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

version 1; no remarriage version 2; allows remarriage
The figures are nearly identical, but the one on the right included the wedding date as part of the key, so it allows remarriage between same two people.

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