ENTITYRELATIONSHIP ER MODEL

1
ENTITY-RELATIONSHIP (E/R) MODEL
2
Reading and Exercises

• Database Systems- The Complete Book Chapter 2
• Self testing exercises
• 2.1.3 and 2.1.4
• 2.2.4
• 2.3.1.b
• Any of the remaining exercises in the textbook
• Following lecture slides are modified from Jeff
  Ullmans slides for Fall 2002 -- Stanford

3
Purpose

• Informal Database Design
• Entity-relationship diagram
• Easy to convert to implementations (e.g.,
  relational databases)

4
Entity Sets

• Database collection of entities and relationship
  among entities
• Entity object that exists and distinguishable
  from other objects
• Entity set collection of similar objects
• Attribute property of an entity set
• Each entity in the set has the same properties

5
Attributes

• Domain set of permitted values for each
  attributes
• Attribute types
• Simple vs. composite
• Single-valued v.s. multi-valued
• Derived

6
E/R Diagram

• Entity sets diagrams
• Attributes oval
• Relationship sets between entity sets diamond

7
Example E/R Diagram
Breed
Name
Name
License
Dog
Kennel
Boards
Age
Address
Phone
Owns
Weight
Pays
Owner
Phone
Name
8
Relationship Set

• Current value of an entity set is the set of
  entities that belong to it
• Value of a relationship is a set of lists of
  currently related entities (one from each entity
  sets)

9
Example

• Example of the Owns relation

10
Degree of Relationship Sets

• Number of entity sets participating in a
  relationship set
• Binary relationship set two entity sets (most
  common)
• Multiway relationship set connects more than two
  entity sets
• E.g., An owner frequents certain kennels for
  certain dogs
• Binary relationship cant represent these
  requirements
• Need 3-way relationship

11
Example 3-Way Diagram
Breed
Name
Name
License
Dog
Kennel
Age
Address
Must Board
Phone
Weight
Owner
Phone
Name
12
Example 3-Way Relation

• Example of the Must-Board relation

13
Mapping Cardinality

• Number of entities to which another entity can be
  associated via a relationship set
• One-one
• Many-one (One-many)
• Many-many

14
One-One Relationship

• Each entity of either entity set is related to at
  most one entity of the other set
• E.g., assume each owner is allowed to have only 1
  dog, and each dog must belong to 1 owner. The
  Owns relation between Dog and Owner is one-one

15
Many-One Relationship

• Each entity of the first set is connected to at
  most one entity in the second set
• Entities in the second set can connect to 0, 1,
  2, or more entities of the first set.
• E.g., assume, each dog has at most 1 owner, but
  an owner may have any number of dogs. TheOwns
  relation from Dog to Owner is is many-one.

16
Many-Many Relationship

• Entity of either set can be connected to many
  entities of the other set
• E.g., assume that a dog may belong to several
  persons, and a person may have any number of
  dogs. The Own relation between Dog and Owner is
  many-many

17
Cardinality of Relationships
one-one many-one many-many
18
Representing Cardinality

• One-one arrow in each side of the relationship
• Many-one arrow on the one side
• Exactly one each entity of the one set must be
  related to exactly on entity of the other set.
  Rounded arrow on the one side

19
Relationship Attribute

• Relationships may have attributes
• Property of the relationship
• E.g., amount the owners pays to the kennel for
  boarding their dog

Amount
20
Relationship Attribute

• Other representation Create an entity set to
  represent relationship property
• Create 3-way relationship among all entities

21
Relationship Attributes
Pays
Owner
Kennel
Cost
Amount
22
Roles

• Entity set may appear more then once in a
  relationship
• Label the edges between the relationships and the
  entity set with names called roles.
• E.g., relationships among the dogs

Related to
ancestor
Dog
descendant
23
Subclasses in E/R

• Special case, fewer entities, more properties
• E.g., show dog is a dog, but not all dogs are
  show dogs. It also have properties, type of
  competition, rank, etc.
• Assume subclasses form a tree (no multiple
  inheritance)
• ISA relationship

24
Example Subclass
Name
Age
Breed
Weight
Dog
ISA
Type
Show-Dog
Rank
25
Keys

• Set of attributes for one entity set such that no
  two entities in the set has the same value for
  all the attributes of the key
• Each entity set must have a key

26
Keys

• Super key set of one or more attributes whose
  value iniquely determine each entity
• Candidate key minimal super key
• Primary key a selected candidate key

27
Keys in E/R Diagram

• Underline the key attributes
• In ISA hierarchy only the root entity set has a
  key, it is also a key for all subclasses

28
Example Key for Owner
Owner
Phone
Name
29
Example Multi-Attribute Key for Owner
There are more than One person with the Same
name, but they Live at different Places.
Owner
Name
Address
Phone
30
Weak Entity Sets

• Entities of an entity set need help to identify
  them uniquely
• Entity set E is said to be weak if in order to
  identify entities of E uniquely, we need to
  follow one of more many-one relationships from E
  and include the key of the related entities from
  the connected entity sets.

31
Example Weak Entity Set
Breed
Name and breed do not uniquely Identify a
dog. However, name and breed and The name of the
owner will Uniquely identify a
dog. Representation double line Around
relationship diamond.
Name
Dog
Owns
Owner
Many-one relationship
Phone
Name
32
Keys for Relationship Sets

• Combination of primary keys of participating
  entity sets
• Must consider mapping cardinalities to choose the
  candidate keys
• Need semantics of relationship set for selecting
  primary key

33
Design Techniques

• Avoid redundancy
• Limit use of weak entity sets
• Dont use entity set when can use attribute
• Binary v.s. multiway relationship sets

34
Avoid Redundancy

• Repeat the same thing in two or more different
  ways
• Bad because
• Wastes space
• Encourages inconsistency

35
Good Design Avoid Redundancy
Breed
Name
Name of the owner Listed only once For each dog
Dog
Age
Owns
Weight
Owner
Phone
Name
36
Bad Design Avoid Redundancy
Breed
Name
Name of the owner Listed twice, once for each
dog and once For the relationship Owns
Owner
Dog
Age
Owns
Weight
Owner
Phone
Name
37
Entity Set v.s. Attribute
Good
Bad
38
Weak Entity Set

• Try to create unique ids for entity sets
• Use weak entity sets only if impossible to create
  unique ids
• E.g., can not require all dog owners to give
  unique names for their pets

39
Student Oracle Accounts

• Some helpful web links
• https//www.cse.sc.edu - My CSE Account -
  Oracle account
• http//www.cse.sc.edu/okeefe/tutorials/oracle_sql
  plus/601.html