CSE 344: Section 4

ER diagrams

Jan 29th, 2026

​

Announcements

• Homework 3
• Late deadline tonight at 10pm!

​

• Homework 1 Grades out
• Deadline for regrade requests: Wed, 2/04 @ 7 am
• Cannot submit regrade requests after deadline!

​

• Questions?

​

Database Design

E/R diagram Basics

Entity set

Attribute

Relationship

Subclass

is a

Weak Entity

Entity Set

Subclass

CREATE TABLE Product (

pname VARCHAR(30) PRIMARY KEY,

price INT)

​

CREATE TABLE Toy (

pname VARCHAR(30) PRIMARY KEY REFERENCES Product,

age INT)

​

CREATE TABLE Candy (

pname VARCHAR(30) PRIMARY KEY REFERENCES Product,

isChoc VARCHAR(5))

Relationships

• One-to-one
• Many-to-one
• Many-to-many

Integrity Constraints in ER Diagrams

Single Value Constraints (many-one) [at most one]

makes

Referential Integrity Constraints (many-one) [exactly one]

makes

Other Constraints

>2

makes

Examples of Relationships

• One to one: ssn to UW student id
• One to many: ssn to phone number
• Many to many: students to courses
• Is a (subclass): PC is a computer and Mac is a computer

​

Question: "at most one" vs. "exactly one"?

Keys and FKs

• Key should be underlined
• For composite keys, all attributes should be underlined

​

• Foreign keys implied by relationship
• Don’t need to explicitly include them as attributes

​

Product

price

name

category

owns

Company

name

E/R to SQL

• Use NOT NULL to enforce >0
• Also use for required attributes
• Only many-to-many relationships should have extra tables
• Ask yourself: for a many-to-X relationship, is it reasonable to add X extra FKs in an existing table?
• For many-to-many, X is potentially infinite!

​

CREATE TABLE 3PlayerGame (

gid VARCHAR(30) PRIMARY KEY,

player1 VARCHAR(30) NOT NULL REFERENCES Players,

player2 VARCHAR(30) NOT NULL REFERENCES Players,

player3 VARCHAR(30) NOT NULL REFERENCES Players,

)

​

CREATE TABLE Players (

pid VARCHAR(30) PRIMARY KEY,

name VARCHAR(30)

)

Weak Entity Set

Entity Relationships Example

Entity Relationships Example: Question

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

Entity Relationships Example: Step 1

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

​

Step 1: Describe the relationship between the entities in english

Entity Relationships Example: Step 1.1

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

​

Step 1: Describe the relationship between the entities in english

• Every student can check out multiple books
• Each book can be checked out by at most one student

Entity Relationships Example: Step 2

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

​

Step 1: Describe the relationship between the entities in english

• Every student can check out multiple books
• Each book can be checked out by at most one student

​

Step 2: Draw entities and relationship

​

Entity Relationships Example: Step 2.1

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

​

Step 1: Describe the relationship between the entities in english

• Every student can check out multiple books
• Each book can be checked out by at most one student

​

Step 2: Draw entities and relationship

​

Checks out

Entity Relationships Example: Step 3

Every student can check out multiple books

Each book can be checked out by at most one student

Step 3: For each entity, add arrow pointing to it if it is constrained to 1

​

​

​

​

​

​

​

​

​

Entity Relationships Example: Step 3.1

Every student can check out multiple books

Each book can be checked out by at most one student

Step 3: For each entity, add arrow pointing to it if it is constrained to 1

​

• If entity is constrained to one, arrow points to it
• if it is AT MOST one
• if it is EXACTLY one

​

​

​

​

​

​

​

​

​

​

Entity Relationships Example: Step 3.2

Every student can check out multiple books

Each book can be checked out by at most one student

Step 3: For each entity, add arrow pointing to it if it is constrained to 1

​

• If entity is constrained to one, arrow points to it
• if it is AT MOST one
• if it is EXACTLY one

​

​

​

• If entity is not constrained to one, no arrow points to it. But annotate if it has another constraint.

​

​

​

​

​

​

​

​

​

​

​

Entity Relationships Example: Final Solution

Every student can check out at most 5 books. Some books may not be checked out by anyone. Draw the ER diagram. What is the multiplicity?

​

Final Solution:

Many-to-One

Follow this same step-by-step formula!

Worksheet Exercise 1:

Entity Relationships & Multiplicity

Worksheet Exercise 1a: Multiplicity Overview

SCENARIO: Every author writes at least one book.

​

Draw the E/R Diagram and state the multiplicity.

​

Relevant domain information:

• Each author writes at least one book.
• Each book must have at least one author

​

Worksheet Exercise 1a: Multiplicity Solution

SCENARIO: Every author writes at least one book.

​

Draw the E/R Diagram and state the multiplicity.

​

Relevant domain information:

• Each author writes at least one book.
• Each book must have at least one author

​

Many-to-Many

Neither entity is constrained to one, so no arrows

Worksheet Exercise 1b: Multiplicity Overview

SCENARIO: What is the relationship between US State and capital?

​

Draw the E/R Diagram and state the multiplicity.

​

​

Worksheet Exercise 1b: Multiplicity Solution

SCENARIO: What is the relationship between US State and capital?

​

Draw the E/R Diagram and state the multiplicity.

​

​

Each state has exactly one capital.

Each capital belongs to exactly one state.

Both entities constrained to one, so arrows pointing to both

One-to-One

Worksheet Exercise 1c: Multiplicity Overview

SCENARIO: Every city has at most 5 parks. Assume parks are confined to one city (ie. they don’t span across multiple cities).

​

Draw the E/R Diagram and state the multiplicity.

​

​

Worksheet Exercise 1c: Multiplicity Solution

SCENARIO: Every city has at most 5 parks. Assume parks are confined to one city (ie. they don’t span across multiple cities).

​

Draw the E/R Diagram and state the multiplicity.

​

​

Each city can have multiple parks.

Each park is in exactly one city.

City is constrained to one, so arrow points to it.

Many-to-One

Worksheet Exercise 2a:

Entity Relationship Diagrams

Worksheet Exercise 2a: Entity Relationship Diagrams Overview

Odegaard Library is in need of a new database, and they have asked you to help design it! Here are some of the requirements for what information needs to be stored in this database:

​

• Each book has a unique ID, a title, an author, a genre (required), and a number of pages
• Readers who visit the library have a unique email address, a first name, a last name, and an age
• Readers can “check out” multiple books from the library at a time, and one book can be checked out multiple times. We should keep track of the day that each book was checked out
• To make it easier to recommend books to readers, we should assign a recommended age for each genre

1. Design an ER diagram for this new library database.

Worksheet Exercise 2a: Entity Relationship Diagrams Step 1

• Each book has a unique ID, a title, an author, a genre (required), and a number of pages

book

num_of_pages

id

title

author

Worksheet Exercise 2a: Entity Relationship Diagrams Step 2

book

num_of_pages

id

title

author

• Each book has a unique ID, a title, an author, a genre (required), and a number of pages
• To make it easier to recommend books to readers, we should assign a recommended age for each genre

Worksheet Exercise 2a: Entity Relationship Diagrams Step 3

book

num_of_pages

id

title

author

has_genre

genre

name

recommended_age

• Each book has a unique ID, a title, an author, a genre (required), and a number of pages
• To make it easier to recommend books to readers, we should assign a recommended age for each genre

Worksheet Exercise 2a: Entity Relationship Diagrams Step 4

reader

email

first_name

last_name

age

• Readers who visit the library have a unique email address, a first name, a last name, and an age
• Readers can “check out” multiple books from the library at a time, and one book can be checked out multiple times. We should keep track of the day that each book was checked out

Worksheet Exercise 2a: Entity Relationship Diagrams Step 5

• Readers who visit the library have a unique email address, a first name, a last name, and an age
• Readers can “check out” multiple books from the library at a time, and one book can be checked out multiple times. We should keep track of the day that each book was checked out

reader

email

first_name

last_name

age

book

num_of_pages

id

title

author

has_genre

genre

name

recommended_age

check_out

day_check_out

Worksheet Exercise 2a: Entity Relationship Diagrams Solution

reader

email

first_name

last_name

age

book

num_of_pages

id

title

author

has_genre

genre

name

recommended_age

check_out

day_check_out

Worksheet Exercise 2b

Worksheet Exercise 2b: Entity Relationship Diagrams Solution

CREATE TABLE Checkouts (

day_check_out VARCHAR(10),

book_id INT REFERENCES Books,

reader_email VARCHAR(50) REFERENCES Readers,

PRIMARY KEY (book_id, reader_email, day_check_out)

);

CREATE TABLE Readers (

email VARCHAR(50) PRIMARY KEY,

first_name VARCHAR(20),

last_name VARCHAR(20),

age INT

);

CREATE TABLE Books (

id INT PRIMARY KEY,

title VARCHAR(100),

author VARCHAR(50),

genre VARCHAR(20) NOT NULL REFERENCES Genres,

num_pages INT

);

CREATE TABLE Genres (

name VARCHAR(20) PRIMARY KEY,

recommended_age INT

);

Convert the ER diagram to a series of CREATE TABLE statements. Include primary key and foreign key statements.

Worksheet Exercise 3

Worksheet Exercise 3: Subclassing Overview

Read the following scenario and draw the E/R diagram representing the relationships between the entities:

​

• Each pet has the following attributes: name, age, color, breed
• A pet is uniquely identified by its name. No two pets have the same name
• A dog has the following attributes: name, age, color, breed, num_barks
• A cat has the following attributes: name, age, color, breed, num_purrs
• Note that since no two pets have the same name, if for example, a dog was named “Dobby”, no other dog nor cat would have that name.

​

​

Worksheet Exercise 3: Subclassing Step 1

Read the following scenario and draw the E/R diagram representing the relationships between the entities:

​

• Each pet has the following attributes: name, age, color, breed
• A pet is uniquely identified by its name. No two pets have the same name
• A dog has the following attributes: name, age, color, breed, num_barks
• A cat has the following attributes: name, age, color, breed, num_purrs
• Note that since no two pets have the same name, if for example, a dog was named “Dobby”, no other dog nor cat would have that name.

​

​

Pets

name

age

color

breed

Worksheet Exercise 3: Subclassing Step 2

Read the following scenario and draw the E/R diagram representing the relationships between the entities:

​

• Each pet has the following attributes: name, age, color, breed
• A pet is uniquely identified by its name. No two pets have the same name
• A dog has the following attributes: name, age, color, breed, num_barks
• A cat has the following attributes: name, age, color, breed, num_purrs
• Note that since no two pets have the same name, if for example, a dog was named “Dobby”, no other dog nor cat would have that name.

​

​

Pets

name

age

color

breed

Is a

Dogs

num_barks

Worksheet Exercise 3: Subclassing Step 3

Read the following scenario and draw the E/R diagram representing the relationships between the entities:

​

• Each pet has the following attributes: name, age, color, breed
• A pet is uniquely identified by its name. No two pets have the same name
• A dog has the following attributes: name, age, color, breed, num_barks
• A cat has the following attributes: name, age, color, breed, num_purrs
• Note that since no two pets have the same name, if for example, a dog was named “Dobby”, no other dog nor cat would have that name.

​

​

Pets

name

age

color

breed

Is a

Dogs

Cats

num_barks

num_purrs

Functional Dependencies

Review: Functional Dependency

A → B

Indicates that attribute A determines attribute B (B is dependent on A).

• Can also say "A yields B"

​

Some examples:

• student_id → graduation_year
• origin_city → origin_state, (population)
• flight_id → carrier_id, origin_city, dest_city, price, …

Review: Transitive Dependency

If A → B and B → C

then A → C

​

{A}+ = Closure of attribute A: Set of all attributes that can be determined by A.

{A}+ = {ABC}

Functional Dependency Example: Overview

Student(studentID, name, dateOfBirth, phoneNumber)

​

​

​

​

Functional Dependency Example: Questions

Do the following FDs hold for�this relation?

​

{studentID} → {name}

​

{studentID} → {name, dateOfBirth}

​

{studentID} → {phoneNumber}

​

​

​

Functional Dependency Example: FD 1

Do the following FDs hold for�this relation?

​

{studentID} → {name}

​

{studentID} → {name, dateOfBirth}

​

{studentID} → {phoneNumber}

​

​

​

Functional Dependency Example: FD 2

Do the following FDs hold for�this relation?

​

{studentID} → {name}

​

{studentID} → {name, dateOfBirth}

​

{studentID} → {phoneNumber}

​

​

​

Functional Dependency Example: FD 3

Do the following FDs hold for�this relation?

​

{studentID} → {name}

​

{studentID} → {name, dateOfBirth}

​

{studentID} → {phoneNumber}

​

There are two tuples that agree on studentID but don’t agree on phoneNumber!

​

​

​