Week 9 October 24

1
Week 9October 24

• Modeling with ERD

2
Entity Relationship (ER) Model(applies to
relational data model)

• High-level conceptual model
• Describes the structure of the database, and the
  associated retrieval and update transactions on
  the database
• Composed of
• Entity types
• Relationship types
• Attributes

3
ERD Notation
Primary key
Entity type
Relationship type
Relationship name
Music_categories
CDs
Classify?
music_category_code PK music_category_title
Attributes
stock_number PK CD_title artist music_category_c
ode record_label_code
1..1
0..
Multiplicity (constraint)
Degree of the Relationship Binary
4
ERD NotationAlternatively
Primary key (underscored)
Entity type
Relationship type
Relationship name
Music_categories
CDs
Music_category_code Music_category_title
Classify
Attributes
Stock_number CD_title Artist Music_category_code
Record_label_code
Zero (circle)
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
5
ERD

• Entity type A group of objects with the same
  properties, which are identified by the
  enterprise as having an independent existence
• Relationship type A set of meaningful
  associations among entity types
• Attributes A property of an entity or a
  relationship type

6
Degree of Relationship Type

• The number of participating entity types in a
  relationship
• Binary (two entity types)
• Ternary (three entity types)

Employee
Employee Awards
Receives?
1..1
0..1
Attribute of a relationship type
Net pay
Employee
Emp_Benefits
Receives
Emp_Tax
7
Recursive Relationship

• A relationship type where the same entity
  participates more tan once in different roles

Managers and staff are employees.
?Manages
1..
A manager is an employee
Manager
Employee
0..
Staff are managed by managers
Staff
Role name indicates the purpose an entity type
plays in a relationship type
8
Attributes

• Property of an entity or relationship type
• Attribute domain
• Set of values that may be assigned to a
  single-valued attribute

Customers
Cust_account Cust_name Cust_address
Cust_phone Soc_Sec_Num
Customer_Accounts
Cust_account Current_balance Credit_limit
Active_date Expire_date
9
Attributes of Attributes

• Simple (atomic attributes) - composed of a single
  component
• Composite - composed of multiple components
• Single valued - one value for an entity
• Multi-valued - one or more values for an entity
• Derived - value derived from a related attribute
  or set of attributes

Student_ID FName MName LName
Single-valued
Multi-valued
Student_ID Semester Course_ID
More than one semester, more than one course_id
10
Attribute Domain
Customers
Composite
Cust_account Cust_name Cust_address
Cust_phone Soc_Sec_Num

• On an ER model, should customer name be shown as
  a composite or simple attribute?
• What is the attribute domain of Cus_name?

Cust_first_name
Cust_last_name
John William Anita Homer
Brown Tell Breake Simpson
11
Derived Attributes

• Derived - value derived from a related attribute
  or set of attributes

Derived attribute
Student_ID Semester Course_ID Units
Grade Grade_point
Student_ID Semester Course_ID Units
Grade Grade_point
Student_ID Semester Course_ID Units
Grade Grade_point
Units x Grade Grade point
12
Attributes as Keys
Uniquely identifies an entity
Candidate key

• Keys cannot change their values (good for the
  life of the entity)
• An efficient means for identifying an entity

Primary key

• Alternate key - candidate that can also be used
  to access an entity
• Composite key - composed of multiple attributes
  (components)

13
Attribute Diagrammatic Representation
Customers Cust_account PK Cust_name
First_name Middle_name Last_name Cust_addres
s Street_number Zip_code (fk) Cust_phone Soc
_sec_num
Key
Composite attribute
Composite attribute
Foreign key
14
Attribute Diagrammatic Representation
Customer_Purchases Cust_account
PK Transaction_number PK Date Store_number Pay
ment_type
Foreign key
Composite key
Foreign key
A customer may have more than one purchase
15
Strong vs. Weak Entity Types

• Strong Entity Type An entity type that is not
  existence-dependent on some other entity type
• Often referred to as parent, owner or dominant
  entities
• Weak Entity Type An entity type that is
  existence-dependent on some other entity type.
• Often referred to as child, dependent or
  subordinate entities

16
Definition of a Weak Entity Type

• An entity type that borrows all or part of its
  primary key. Identifying relationships indicate
  the entity types that supply components of the
  borrowed primary key.
• Mannino, 1999

17
Strong vs. Weak Entity Types
Customer_accounts AccountNumber
PK FirstName MiddleName LastName Address ZipCode
MembershipDate
Customer_Purchases AccountNumber
PK StoreNumber PK TransactionDate
PK TransactionNumber PK PaymentType
Purchase?
1..1
0..
Which is the strong and weak entity type? How can
you tell?
18
Strong vs. Weak Entity Types
Customer_accounts AccountNumber
PK FirstName MiddleName LastName Address ZipCode
MembershipDate
Customer_Purchases AccountNumber
PK StoreNumber PK TransactionDate
PK TransactionNumber PK PaymentType
Existent-dependence
Also a FK
Purchase?
1..1
0..
Which is the strong and weak entity type? How can
you tell?
19
Attributes of Relationship Types

• Attributes produced through relationship types
  (i.e., not retained in the entity types)

CustomerAccounts AccountNumber
CustomerFlights AccountNumber PK TransactionDat
e PK IntineraryNumber PK
Reserve?
1..1
0..
NumberOfItineraries
Derived attribute
20
Structural Constraints

• Multiplicity Number of possible occurences of
  an entity type that may relate to a single
  occurrence of an associated entity type through a
  particular relationship type
• One to one (11)
• One to many (1)
• Many to many ()
• Cardinality and participation constraints
• Cardinality Describes the maximum number of
  possible relationship occurrences for an entity
  participating in a given relationship type

Defined by business rules
21
Cardinality and Participation Constraints

• Cardinality Describes the maximum number of
  possible relationship occurrences for an entity
  participating in a given relationship type
• Participation Determines whether all or only
  some entity occurrences participate in a
  relationship
• 11 (1..1) minimum is one mandatory
  participation
• 01 (0..1) minimum is zero optional
  participation

22
Cardinality

• 11 (one to one)
• Each entity in X is associated with at most one
  entity in Y and conversely each entity in Y is
  associated with at most one entity in X
• 1 (one to many)
• Each entity in X can be associated with many
  entities in Y but each entity in Y is associated
  with at most one entity in X.
• (many to many)
• Each entity in X can be associated with many
  entities in Y and each entity in Y can be
  associated with many entities in X.

23
Cardinality
11 Relationships
Strong entity type
Weak entity type
Customers
Accounts
Own?
Customer_ID PK Customer_name Customer_address Zi
p_code
Account_number PK Customer_ID
FK Account_type Current_balance
1..1
1..1
Mandatory participation
A customer owns a minimum and maximum of one
account
An account is owned by a minimum and maximum of
one customer
Note. This would be avoided in the logical
design, but could be implemented in the physical.
24
Cardinality
1M Relationships
Strong entity type
Weak entity type
Customers
Accounts
Own?
Customer_ID PK Customer_name Customer_address Zi
p_code
Account_number PK Customer_ID
FK Account_type Current_balance
1..1
1..
Mandatory participation
A customer owns a minimum one and maximum of many
accounts
An account is own by a minimum and maximum of one
customer
25
Cardinality

• (many-to-many) relationship if a customer can
  own more than one account (e.g., revolving,
  long-term), and one account can have more than
  one owner (e.g., joint account).

Customers
Accounts
Own?
Customer_ID PK Customer_name Customer_address Zi
p_code
Account_number PK Customer_ID
FK Account_type Current_balance
1..
1..
A customer owns a minimum one and maximum of many
accounts
An account is own by a minimum of one customer
and maximum of many customers
26
ERD Notation
Primary key
Entity type
Relationship type
Relationship name
Video_categories
Videos
Classify
Category_code PK Category_title
Attributes
Stock_number PK Video_title Category_code
FK Distributor_code FK
1..1
0..
Minimum
Zero
Cardinality
Maximum
Many
For an occurrence of videos, there may exist one
and only one video category.
For an occurrence of video categories, there may
exist zero to many videos.
27
ERD Notation
Video_categories
Videos
Classify?
Category_code PK Category_title
Stock_number PK Video_title Category_code
FK Distributor_code FK
1..1
0..
Video categories classify videos
For an occurrence of distributors, there are zero
to many videos
0..
?Release
1..1
Distributors
Distributor_code PK Distributor_name
Distributors release videos
28
ERD Notation
Video_categories
Videos
Classify?
Category_code PK Category_title
Stock_number PK Video_title Category_code
FK Distributor_code FK
1..1
0..
Video categories classify videos
0..
?Release
For an occurrence of videos, there is one and
only one video category
1..1
Distributors
Distributor_code PK Distributor_name
Distributors release videos
29
ERD Notation
Video_categories
Videos
Classify?
Category_code PK Category_title
Stock_number PK Video_title Category_code
FK Distributor_code FK
1..1
0..
0..
Attribute of the relationship type
Number_of_videos_released
?Release
1..1
Distributors
Distributor_code PK Distributor_name
30
ERD NotationAlternate Notation
Primary key (underscored)
Entity type
Relationship type
Relationship name
Music_categories
CDs
Music_category_code Music_category_title
Classify
Attributes
Stock_number CD_title Artist Music_category_code
Record_label_code
Zero (circle)
Minimum (inside)
Cardinality
Maximum (outside)
Many (crows feet)
31
ERD NotationAlternate Notation
Minimum cardinality of one (a music category has
to have at least one CD)
Entity type
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Weak entity type (all four corners)
32
ERD Notation Alternative Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
A record label is related to a minimum of zero
and maximum of many CDs
Produce
Record_labels
Record_label_code Record_label
33
ERD Notation Alternative Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Produce
A CD is related to a minimum and maximum of one
record label
Record_labels
Record_label_code Record_label
34
ERD NotationAlternative Notation
Music_categories
CDs
Music_category_code Music_category_title
Classify
Stock_number CD_title Artist Music_category_code
Record_label_code
Produce
Quantity_produced
Attribute of a relationship
Record_labels
Record_label_code Record_label
35
(No Transcript)