The Entity-Relationship Model ER Model - Part 1: Basics By Michael - - PowerPoint PPT Presentation

▶
the entity relationship model er model part 1 basics
SMART_READER_LITE
LIVE PREVIEW

The Entity-Relationship Model ER Model - Part 1: Basics By Michael - - PowerPoint PPT Presentation

Jun 22, 2023 526 likes •885 views

The Entity-Relationship Model ER Model - Part 1: Basics By Michael Hahsler Based on slides for CS145 Introductjon to Databases (Stanford) Introduction to Database Design 2 Database Design Database design: Why do we need it? Agree on

slide-1
SLIDE 1

The Entity-Relationship Model ER Model - Part 1: Basics

By Michael Hahsler Based on slides for CS145 Introductjon to Databases (Stanford)

slide-2
SLIDE 2

Introduction to Database Design

2

slide-3
SLIDE 3

3

Database Design

  • Database design: Why do we need it?
  • Agree on structure of the database before deciding on a partjcular

implementatjon

  • Consider issues such as:
  • What entjtjes to model
  • How entjtjes are related
  • What constraints exist in the domain
  • How to achieve good designs
  • Several formalisms exist
  • We discuss one fmavor of ER diagrams
slide-4
SLIDE 4

Database Design Process

  • 1. Requirements analysis
  • What is going to be stored?
  • How is it going to be used?
  • What are we going to do with the data?
  • Who should access the data?

4

  • 1. Requirements Analysis
  • 1. Requirements Analysis
  • 2. Conceptual Design
  • 2. Conceptual Design
  • 3. Logical, Physical, Security, etc.
  • 3. Logical, Physical, Security, etc.
slide-5
SLIDE 5
  • 2. Conceptual Design
  • A high-level descriptjon of the database
  • Suffjciently precise that technical people can understand it
  • But, not so precise that non-technical people cannot

partjcipate

5

Database Design Process

  • 1. Requirements Analysis
  • 1. Requirements Analysis
  • 2. Conceptual Design
  • 2. Conceptual Design
  • 3. Logical, Physical, Security, etc.
  • 3. Logical, Physical, Security, etc.
slide-6
SLIDE 6

6

Database Design Process

  • 1. Requirements Analysis
  • 1. Requirements Analysis
  • 2. Conceptual Design
  • 2. Conceptual Design
  • 3. Logical, Physical, Security, etc.
  • 3. Logical, Physical, Security, etc.
  • 3. Implementatjon:
  • Logical Database Design
  • Physical Database Design
  • Security Design
slide-7
SLIDE 7

7

ER is a visual syntax for DB design which is precise enough for technical points, but abstracted enough for non-technical people. ER is a visual syntax for DB design which is precise enough for technical points, but abstracted enough for non-technical people.

Database Design Process

  • 1. Requirements Analysis
  • 1. Requirements Analysis
  • 2. Conceptual Design
  • 2. Conceptual Design
  • 3. Logical, Physical, Security, etc.
  • 3. Logical, Physical, Security, etc.

Makes Product name category price Company name

ER Model & Diagrams

slide-8
SLIDE 8

Impact of the ER model

  • The ER model is one of the most cited artjcles in Computer

Science

  • “The Entjty-Relatjonship model – toward a unifjed view of data”

Peter Chen, 1976

  • Used by companies big and small

8

slide-9
SLIDE 9
  • 1. ER Basics:

Entities & Relations

9

slide-10
SLIDE 10

Entities and Entity Sets

  • Entjtjes & entjty types are the primitjve

units of the ER model

  • Entjtjes are the individual objects (instances),

which are members of entjty types

  • Entjty type are the classes or types of objects

in our model

  • Example: Person is an entjty type while

Michael is an entjty.

  • We use entjty types in ER models

10

Product Person

slide-11
SLIDE 11

Entities and Entity T ypes

  • An entjty type has atuributes

represented by ovals atuached to an entjty type

11

Product name category price Shapes are

  • important. Colors

used here are not. Shapes are

  • important. Colors

used here are not.

slide-12
SLIDE 12

Entities vs. Entity Sets

Example:

12

Product name category price Entity type

Product

Name: Xbox Category: Gamming Console Price: $250 Name: My Little Pony Doll Category: T

  • y

Price: $25

Entity Entity Attribute Entities are not explicitly represented in ER diagrams!

slide-13
SLIDE 13

Keys

A key is a minimal set of atuributes that uniquely identjfjes an entjty.

13

Product name category price

Denote elements of the primary key by underlining. Denote elements of the primary key by underlining.

Here, {name, category} is not a key (it is not minimal). If it were, what would it mean?

The ER model forces us to designate a single primary key, though there may be multiple candidate keys. Often, we introduce an artifjcial key attribute (also called a synthetic or surrogate key). The ER model forces us to designate a single primary key, though there may be multiple candidate keys. Often, we introduce an artifjcial key attribute (also called a synthetic or surrogate key).

slide-14
SLIDE 14

Entity T ypes Defjne Relations

14

Product name category price

name category price Gizmo Electronics $9.99 GizmoLite Electronics $7.50 Gadget Toys $5.50

Product

slide-15
SLIDE 15

The R in ER: Relationships

  • A relatjonship type is between two entjty types

15

Product name category price Company name Makes

How to read a relatjonship in both directjons:

  • 1. A product is made by a company
  • 2. A company makes a product
slide-16
SLIDE 16

16

makes buys Product name category price Person address name ssn Company stockprice name

slide-17
SLIDE 17

17

What is a Relationship?

  • A mathematjcal defjnitjon (called “Relatjon” in Math):
  • Let A, B be sets
  • A={1,2,3}, B={a,b,c,d}

1 2 3 a b c d A= B=

slide-18
SLIDE 18

18

What is a Relationship?

  • A mathematjcal defjnitjon:
  • Let A, B be sets
  • A={1,2,3}, B={a,b,c,d}
  • A x B (the cross-product) is the set of all pairs

(a,b)

  • A  B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c),

(2,d), (3,a), (3,b), (3,c), (3,d)}

1 2 3 a b c d A= B=

slide-19
SLIDE 19

19

What is a Relationship?

  • A mathematjcal defjnitjon:
  • Let A, B be sets
  • A={1,2,3}, B={a,b,c,d}
  • A x B (the cross-product) is the set of all pairs

(a,b)

  • A  B = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c),

(2,d), (3,a), (3,b), (3,c), (3,d)}

1 2 3 a b c d A= B=

  • We defjne a relatjonship (relatjon) to be a subset of A x B

R = {(1,a), (2,c), (2,d), (3,b)}

slide-20
SLIDE 20

20

What is a Relationship?

  • A mathematjcal defjnitjon:
  • Let A, B be sets
  • A x B (the cross-product) is the set of all pairs
  • A relatjonship (relatjon) is a subset of A x B
  • Example: Makes is a relatjonship. It is a

subset of Product  Company:

makes Company Product

1 2 3 a b c d A= B=

slide-21
SLIDE 21

What is a Relationship?

21

name category price Gizmo Electronics $9.99 GizmoLite Electronics $7.50 Gadget Toys $5.50 name GizmoWorks GadgetCorp

Product Company Makes Product name category price Company name

A relationship between entity sets P and C is a subset

  • f all possible pairs of entities in P and C, with tuples

uniquely identifjed by P and C’s keys A relationship between entity sets P and C is a subset

  • f all possible pairs of entities in P and C, with tuples

uniquely identifjed by P and C’s keys

slide-22
SLIDE 22

What is a Relationship?

22

name category price Gizmo Electronics $9.99 GizmoLite Electronics $7.50 Gadget Toys $5.50 name GizmoWorks GadgetCorp

Product Company

C.name P.name P.category P.price GizmoWorks Gizmo Electronics $9.99 GizmoWorks GizmoLite Electronics $7.50 GizmoWorks Gadget Toys $5.50 GadgetCorp Gizmo Electronics $9.99 GadgetCorp GizmoLite Electronics $7.50 GadgetCorp Gadget Toys $5.50

Company C Product P Makes Product name category price Company name

A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C, with tuples uniquely identifjed by P and C’s keys A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C, with tuples uniquely identifjed by P and C’s keys

slide-23
SLIDE 23

What is a Relationship?

23

name category price Gizmo Electronics $9.99 GizmoLite Electronics $7.50 Gadget Toys $5.50 name GizmoWorks GadgetCorp

Product Company

C.name P.name P.category P.price GizmoWorks Gizmo Electronics $9.99 GizmoWorks GizmoLite Electronics $7.50 GizmoWorks Gadget Toys $5.50 GadgetCorp Gizmo Electronics $9.99 GadgetCorp GizmoLite Electronics $7.50 GadgetCorp Gadget Toys $5.50

Company C Product P

C.name P.name GizmoWorks Gizmo GizmoWorks GizmoLite GadgetCorp Gadget

Makes Makes Product name category price Company name

A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C, with tuples uniquely identifjed by P and C’s keys A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C, with tuples uniquely identifjed by P and C’s keys

slide-24
SLIDE 24

What is a Relationship?

  • There can only be one relatjonship for every

unique combinatjon of entjtjes

  • This also means that the relatjonship is

uniquely determined by the keys of its entjtjes

  • Example: the key for Makes (to right) is

{Product.name, Company.name}

24

This follows from our mathematical defjnition

  • f a relationship (it is a

set) This follows from our mathematical defjnition

  • f a relationship (it is a

set)

Makes Product name category price Company name

KeyMakes = KeyProduct KeyCompany

slide-25
SLIDE 25

25

Product name category price Company name Makes since Relatjonships may have atuributes as well. For example: “since” records when company started making a product For example: “since” records when company started making a product

Note: For each product/company pair there is automatically only a single since value since there can only be one unique product/ company pair in makes. Note: For each product/company pair there is automatically only a single since value since there can only be one unique product/ company pair in makes.

Relationships and Attributes

slide-26
SLIDE 26

Decision: Relationship vs. Entity?

Q: What does this say? A: A person can only buy a specifjc product once per day (date)

26

Purchased Product name category price Person name date

Modeling something as a relationship makes it

  • unique. What if this is not appropriate?

Modeling something as a relationship makes it

  • unique. What if this is not appropriate?
slide-27
SLIDE 27

Decision: Relationship vs. Entity?

What about this way? Now we can have multjple purchases per product, person pair!

27

Product name category price Person name date Purchase quantjty PID# ProductOf BuyerOf

We can always use a new entity instead of a relationship. For example, to permit multiple instances of each entity combination! We can always use a new entity instead of a relationship. For example, to permit multiple instances of each entity combination!

slide-28
SLIDE 28

Note on Relationships vs. Relation

28

name category price Gizmo Electronics $9.99 GizmoLite Electronics $7.50 Gadget Toys $5.50

Product Makes Product name category price Company name

Relatjonship/Relatjonship type Relatjon

ER Model: How do Entjty types relate to each other Math: A Relatjon (= a subset of the cross product) Relatjonal Algebra: A table with data (a set)

Relations are used to implement entity types and certain relationship types! Relations are used to implement entity types and certain relationship types!

slide-29
SLIDE 29

How to Create an ER Diagram

“Rules of thumb" for mapping natural language descriptions into ER diagrams:

29

English grammar structure ER structure Common noun Entjty type Proper noun Entjty Verb Relatjonship type Adjectjve Aturibute for entjty Adverb Aturibute for relatjonship

slide-30
SLIDE 30

Example: How to Create an ER Diagram

Here is what the person in charge said: “Our company is called PowerSeller and we sell health products on Ebay. Our products are made by difgerent

  • manufacturers. Products belong to difgerent product

categories (e.g., supplements, cosmetics, etc.) and each product is sold at a fjxed price. We use customer IDs for our customers, and we know for all of them the shipping address and name, and for most we also know a phone number.”

30

slide-31
SLIDE 31

Example: How to Create an ER Diagram

Here is what the person in charge said: “Our company is called PowerSeller and we sell health products on Ebay. Our products are made by difgerent

  • manufacturers. Products belong to difgerent product

categories (e.g., supplements, cosmetics, etc.) and each product is sold at a fjxed price. We use customer IDs for our customers, and we know for all

  • f them the shipping address and name, and for most

we also know a phone number.”

31

Noun Verb

slide-32
SLIDE 32

Example: How to Create an ER Diagram

Identjfy entjty types and atuributes (nouns):

  • company, PowerSeller
  • health product, product, product category, price, manufacturer
  • customerID , customer, shipping address, name, phone number

Identjfy relatjonship types (verbs):

  • know
  • belongs to
  • sell
  • make

32

slide-33
SLIDE 33

Example: How to Create an ER Diagram

Identjfy Entjty types (bold) and atuributes:

  • company, PowerSeller
  • health product, product, product category, price, manufacturer
  • customerID , customer, shipping address, name, phone number

Identjfy relatjonship types:

  • know
  • belongs to
  • sell, buy
  • make

33

Make its own entjty type

slide-34
SLIDE 34

34

make buy Product name category price Customer address name ID Manufacturer name phone

slide-35
SLIDE 35

Exercise: Draw an ER diagram for football

35

T eams play each other in

  • Games. Each

pair of teams can play each

  • ther multiple

times Players belong to T eams (assume no trades / changes) A Play will contain either a Pass from one player to another, or a Run by one player A Game is made up of Plays that result in a yardage gain/loss, and potentially a touchdown