Transcript Chapter11(ERD)

Lecture Notes
Data Modeling and the
Entity-Relationship Model
CS 475
Outline of Lecture
• Overview of Database Modeling
• Entity-Relationship Model
– Constructs
– Relationships
– Constraints
• Entity-Relationship Diagram
• Chapter 11, Sec. 11.1 - 11.6
CS 475 - Spring 2002
2
Database Modeling
• Analysis of what information the database must hold
• Relationships among components of that information
• Database schema is specified in one of several
languages or notations suitable for expressing designs
• Next, design is committed to a form which can be
input to a DBMS  DB takes on physical existence
• Design notation in this course is Entity -Relationship
(E/R) model
Object-Oriented
DBMS
ODL, UML, etc.
Ideas
Relations
E/R
CS 475 - Spring 2002
Relational
DBMS
3
Entity-Relationship Model
Principal building blocks:
– Entity: Real-world object, concept, or event
– Entity type/set: Collection of entities of the same type
(same properties); sets don’t have to be disjoint
– Attribute: Values describing some property of an entity
• simple vs. composite
• single-valued vs. multi-valued
• stored vs. derived
• Null
– Relationship: Association between two or more entity sets
– Relationship type/set: Collection of relationships of the
same type
CS 475 - Spring 2002
4
Examples - “Movie Database”
• Entity:
– Star
• Attributes:
–
–
–
–
Name: “Harrison Ford” (simple, single-valued, stored)
Address: “123 Main Str., LA, CA” (complex)
Birthdate: “1-1-50” (simple)
Age: 50 (could be derived from Birthdate and current date)
• Entity Type:
– Stars (Name, Address, Birthdate, Age)
• Entity Occurrence
– a uniquely identifiable object of an
entity type
CS 475 - Spring 2002
5
Entity Type in ERD
STAR
Represented by a rectangle with the entity name in it
CS 475 - Spring 2002
6
Attributes
•Attribute domain - set of allowable values
•Simple or Composite
surname simple
address composite (street,city,state)
•Single valued / multi-valued
course_number
contact_info - (phone,e-mail,cell,etc)
•derived - may be generated from a set of attribute values,
possibly from different entities.
CS 475 - Spring 2002
7
Relationships
• Define associations among entities
• Grouped together into relationships sets (types)
• Example:
– Relationship type: Stars-in between Movies and
Stars
• Can be visualized as a table
• Each entry is a relationship occurrence
Relationship Type
CS 475 - Spring 2002
Movies
Stars
Total Recall
A. Schwarzenegger
Total Recall
Sharon Stone
Basic Instinct
Sharon Stone
Relationships
8
ERD Relationship
Star
Stars
in
Movie
Represented by a diamond shape containing the relationship name
CS 475 - Spring 2002
9
Relationships Cont’d
• Degree of a relationship type is the number of
participating entity types
– binary, ternary, n-ary
• Example:
– Entities: Stars, Movies, Studios
– Ternary Relationship Set: Contracts
• Each entity type that participates in a relationship
type plays a role in the relationship
– Marked by (optional) name
– Entity type may play multiple roles in a relationship type
– Ex.: Relationship type Sequel-of between Movies
and itself (Movies)
CS 475 - Spring 2002
10
Relationships Cont’d
• Relationships can have attributes
• Example: Relationship type Contracts between
Stars and Studios for Movies
• Might want to record salary associated with each
contract
• Not necessary to place attributes on relationships;
instead, can invent new entity type whose entities
have the attributes ascribed to relationship
CS 475 - Spring 2002
11
Constraints on Relationships
• Limit the possible combinations of entities that
participate in the relationship type
– Derived from real-world scenario which is being modeled
• Multiplicity (Cardinality ratio) and participation
constraints
• Cardinality ratio specifies the number of
relationship occurrences an entity can participate in
– 1:1, 1:N, N:1, N:M
CS 475 - Spring 2002
12
Mapping Cardinalities
• Studio-Owns between Studio and Movies is 1:N
• Stars-in between Stars and Movies is M:N
• Teaches between Professors and Students is M:N
• Manages between staff and Branch is 1:1
CS 475 - Spring 2002
13
Relationship Constraints Cont’d
Existence Dependency
• Existence of some entity x from entity type X
depends on existence of some entity y from entity
type Y
– x is said to be existence dependent on y
– if y is deleted, so is x
• Example:
– Entity type Loans and Payments with relationship
Loan-payment (1:N from Loans to Payments)
– Payment entities are existence dependent on loan entities
CS 475 - Spring 2002
14
Strong and Weak Entities
• Strong Entity type
An entity type that is not existence-dependent
on some other entity type.
• Weak Entity type
an entity type that is existence-dependent on
some other entity type – two rectangles
CS 475 - Spring 2002
15
Relationship Constraints Cont’d
Participation constraint
• Participation of an entity type E in relationship type
R: total or partial
– Mandatory (Total) if every entity in E participates in at
least one relationship in R
– Optional (Partial) if only some entities in E participate in
R
– Represented by the min value in a min-max pair
• In previous example, participation of Payment
entities in the relationship type Loan-payments is
total
CS 475 - Spring 2002
16
ER Diagrams - Notation
...
Entity Set
Role
Name
Composite
Attribute
Relationship Set
Total participation
of E2 in R
1:n
Attribute
E1
R
E2
Multivalued
1
E1
N
R
1:N Relationship
between E1 and E2
in R
E2
Derived
CS 475 - Spring 2002
17