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

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 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 3

Database Design Process 1. Requirements Analysis 2. Conceptual Design 1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc. 3. Logical, Physical, Security, etc. 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

Database Design Process 1. Requirements Analysis 2. Conceptual Design 1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc. 3. Logical, Physical, Security, etc. 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 2. Conceptual Design 1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc. 3. Logical, Physical, Security, etc. 3. Implementatjon: • Logical Database Design • Physical Database Design • Security Design 6

Database Design Process 1. Requirements Analysis 2. Conceptual Design 1. Requirements Analysis 2. Conceptual Design 3. Logical, Physical, Security, etc. 3. Logical, Physical, Security, etc. ER Model & Diagrams name name category price Product Makes Company ER is a visual syntax for DB design which is precise ER is a visual syntax for DB design which is precise enough for technical points, but abstracted enough for enough for technical points, but abstracted enough for non-technical people. non-technical people. 7

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

1. ER Basics: Entities & Relations 9

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 Product • Entjty type are the classes or types of objects Person in our model • Example: Person is an entjty type while Michael is an entjty. • We use entjty types in ER models 10

Entities and Entity T ypes • An entjty type has atuributes represented by ovals atuached to an entjty type Shapes are Shapes are important. Colors important. Colors used here are used here are name category not. not. price Product 11

Entities vs. Entity Sets Example: Entity name category price Name : Xbox Entity Category : Name : My Little Pony Doll Gamming Console Product Attribute Category : T oy Price : $250 Price : $25 Product Entity type Entities are not explicitly represented in ER diagrams! 12

Keys A key is a minimal set of atuributes that uniquely identjfjes an entjty. Denote elements of Denote elements of Here, {name, category} is the primary key by the primary key by not a key (it is not minimal ). underlining. underlining. If it were, what would it name category mean? price Product The ER model forces us to designate a single primary key , though The ER model forces us to designate a single primary key , though there may be multiple candidate keys. Often, we introduce an there may be multiple candidate keys. Often, we introduce an artifjcial key attribute (also called a synthetic or surrogate key ). artifjcial key attribute (also called a synthetic or surrogate key ). 13

Entity T ypes Defjne Relations Product name name category price category Gizmo Electronics $9.99 price GizmoLite Electronics $7.50 Gadget Toys $5.50 Product 14

The R in ER: Relationships • A relatjonship type is between two entjty types name name category price Product Makes Company How to read a relatjonship in both directjons: 1. A product is made by a company 2. A company makes a product 15

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

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

What is a Relationship? • A mathematjcal defjnitjon: B= A= 1 a • Let A, B be sets • A={1,2,3}, B={a,b,c,d} b 2 c • A x B (the cross-product ) is the set of all pairs 3 (a,b) d • 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)} 18

What is a Relationship? • A mathematjcal defjnitjon: B= A= 1 a • Let A, B be sets • A={1,2,3}, B={a,b,c,d} b 2 c • A x B (the cross-product ) is the set of all pairs 3 (a,b) d • 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)} • We defjne a relatjonship (relatjon) to be a subset of A x B R = {(1,a), (2,c), (2,d), (3,b)} 19

What is a Relationship? • A mathematjcal defjnitjon: • Let A, B be sets B= A= 1 a • A x B (the cross-product ) is the set of all pairs • A relatjonship (relatjon) is a subset of A x B b 2 c 3 • Example: Makes is a relatjonship. It is a d subset of Product  Company : makes Company Product 20

What is a Relationship? Product Company name category price name Gizmo Electronics $9.99 GizmoWorks GizmoLite Electronics $7.50 GadgetCorp Gadget Toys $5.50 name name category price Product Makes Company A relationship between entity sets P and C is a subset A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C , with tuples of all possible pairs of entities in P and C , with tuples uniquely identifjed by P and C’s keys uniquely identifjed by P and C’s keys 21

What is a Relationship? Company C Product P Product Company C.name P.name P.category P.price name category price name GizmoWorks Gizmo Electronics $9.99 Gizmo Electronics $9.99 GizmoWorks GizmoWorks GizmoLite Electronics $7.50 GizmoLite Electronics $7.50 GadgetCorp GizmoWorks Gadget Toys $5.50 Gadget Toys $5.50 GadgetCorp Gizmo Electronics $9.99 GadgetCorp GizmoLite Electronics $7.50 GadgetCorp Gadget Toys $5.50 name name category price Product Makes Company A relationship between entity sets P and C is a A relationship between entity sets P and C is a subset of all possible pairs of entities in P and C , subset of all possible pairs of entities in P and C , with tuples uniquely identifjed by P and C’s keys with tuples uniquely identifjed by P and C’s keys 22

What is a Relationship? Company C Product P Product Company C.name P.name P.category P.price name category price name GizmoWorks Gizmo Electronics $9.99 Gizmo Electronics $9.99 GizmoWorks GizmoWorks GizmoLite Electronics $7.50 GizmoLite Electronics $7.50 GadgetCorp GizmoWorks Gadget Toys $5.50 Gadget Toys $5.50 GadgetCorp Gizmo Electronics $9.99 GadgetCorp GizmoLite Electronics $7.50 GadgetCorp Gadget Toys $5.50 name name category price Product Makes Company Makes C.name P.name A relationship between entity sets P and A relationship between entity sets P and GizmoWorks Gizmo C is a subset of all possible pairs of C is a subset of all possible pairs of GizmoWorks GizmoLite entities in P and C , with tuples uniquely entities in P and C , with tuples uniquely GadgetCorp Gadget identifjed by P and C’s keys identifjed by P and C’s keys 23

What is a Relationship? This follows from our This follows from our • There can only be one relatjonship for every mathematical defjnition mathematical defjnition unique combinatjon of entjtjes of a relationship (it is a of a relationship (it is a set) set) • This also means that the relatjonship is uniquely determined by the keys of its entjtjes Key Makes = Key Product Key Company • Example: the key for Makes (to right) is {Product.name, Company.name} name name category price Product Makes Company 24

Relationships and Attributes Relatjonships may have atuributes as well. since name name category price Product Makes Company Note: For each product/company For example: “since” Note: For each product/company For example: “since” pair there is automatically only a pair there is automatically only a records when company records when company single since value since there single since value since there started making a started making a can only be one unique product/ can only be one unique product/ product product company pair in makes. company pair in makes. 25

Decision: Relationship vs. Entity? Q: What does this say? date name name category price Product Purchased Person A: A person can only buy a specifjc product once per day (date) Modeling something as a relationship makes it Modeling something as a relationship makes it unique. What if this is not appropriate? unique. What if this is not appropriate? 26