1 Week 9 October 24 Modeling with ERD

1 Week 9 October 24 • Modeling with ERD R. Ching, Ph. D. • MIS • California State University, Sacramento

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 2

ERD Notation 3 Primary key Relationship type Relationship name Entity type Music_categories Attributes CDs Classify music_category_code {PK} stock_number {PK} music_category_title 1. . 1 0. . * CD_title artist music_category_code record_label_code Multiplicity (constraint) Degree of the Relationship: Binary R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternatively Entity type 4 Primary key (underscored) Relationship type Relationship name Music_categories Attributes Music_category_code Music_category_title Cardinality CDs Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) Minimum Maximum (inside) Many (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 5

Degree of Relationship Type 6 • The number of participating entity types in a relationship • Binary (two entity types) Employee Awards Receives 1. . 1 0. . 1 • Ternary (three entity types) Net pay Employee Attribute of a relationship type Emp_Benefits Receives Emp_Tax R. Ching, Ph. D. • MIS • California State University, Sacramento

Recursive Relationship 7 • A relationship type where the same entity participates more tan once in different roles Managers and staff are employees. Manages 1. . * Manager Staff are managed by managers 0. . * A manager is an employee Employee Staff Role name indicates the purpose an entity type plays in a relationship type R. Ching, Ph. D. • MIS • California State University, Sacramento

Attributes 8 Property of an entity or relationship type Customers Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num Customer_Accounts Cust_account Current_balance Credit_limit Active_date Expire_date • Attribute domain – Set of values that may be assigned to a single-valued attribute R. Ching, Ph. D. • MIS • California State University, Sacramento

Attributes of Attributes 9 • 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 R. Ching, Ph. D. • MIS • California State University, Sacramento

Attribute Domain 10 Customers Composite Cust_account Cust_name Cust_address Cust_phone Soc_Sec_Num Cust_first_name Cust_last_name John William Anita Homer Brown Tell Breake Simpson R. Ching, Ph. D. • MIS • California State University, Sacramento • On an ER model, should customer name be shown as a composite or simple attribute? • What is the attribute domain of Cus_name?

Derived Attributes 11 • Derived - value derived from a related attribute or set of attributes Derived attribute Student_ID Semester Course_ID Units Grade_point Student_ID Semester Course_ID Units Grade_point Units x Grade = Grade point R. Ching, Ph. D. • MIS • California State University, Sacramento

Attributes as Keys 12 Uniquely identifies an entity Candidate key Primary key • Keys cannot change their values (good for the life of the entity) • An efficient means for identifying an entity • Alternate key - candidate that can also be used to access an entity • Composite key - composed of multiple attributes (components) R. Ching, Ph. D. • MIS • California State University, Sacramento

Attribute Diagrammatic Representation 13 Customers Composite attribute Cust_account {PK} Cust_name First_name Middle_name Last_name Cust_address Street_number Zip_code (fk) Cust_phone Soc_sec_num R. Ching, Ph. D. • MIS • California State University, Sacramento Key Foreign key

Attribute Diagrammatic Representation Customer_Purchases Composite key Cust_account {PK} Transaction_number {PK} Date Store_number Payment_type Foreign key A customer may have more than one purchase R. Ching, Ph. D. • MIS • California State University, Sacramento 14

Strong vs. Weak Entity Types • Strong Entity Type – An entity type that is not existencedependent on some other entity type – Often referred to as parent, owner or dominant entities • Weak Entity Type – An entity type that is existencedependent on some other entity type. – Often referred to as child, dependent or subordinate entities R. Ching, Ph. D. • MIS • California State University, Sacramento 15

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 R. Ching, Ph. D. • MIS • California State University, Sacramento 16

Strong vs. Weak Entity Types Customer_accounts Account. Number {PK} First. Name Middle. Name Last. Name Address Zip. Code Membership. Date Customer_Purchases Account. Number {PK} Store. Number {PK} Purchase Transaction. Date {PK} 1. . 1 0. . * Transaction. Number {PK} Payment. Type Which is the strong and weak entity type? How can you tell? R. Ching, Ph. D. • MIS • California State University, Sacramento 17

Strong vs. Weak Entity Types Customer_accounts Account. Number {PK} First. Name Middle. Name Last. Name Address Zip. Code Membership. Date 18 Customer_Purchases Existent-dependence Also a FK Account. Number {PK} Store. Number {PK} Purchase Transaction. Date {PK} 1. . 1 0. . * Transaction. Number {PK} Payment. Type Which is the strong and weak entity type? How can you tell? R. Ching, Ph. D. • MIS • California State University, Sacramento

Attributes of Relationship Types • Attributes produced through relationship types (i. e. , not retained in the entity types) Customer. Accounts Account. Number … Customer. Flights Reserve 1. . 1 Account. Number {PK} 0. . * Transaction. Date {PK} Intinerary. Number {PK} … Number. Of. Itineraries R. Ching, Ph. D. • MIS • California State University, Sacramento Derived attribute 19

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 (1: 1) – One to many (1: *) Defined by business rules – 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 R. Ching, Ph. D. • MIS • California State University, Sacramento 20

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 – 1: 1 (1. . 1) minimum is one – mandatory participation – 0: 1 (0. . 1) minimum is zero – optional participation R. Ching, Ph. D. • MIS • California State University, Sacramento 21

Cardinality • 1: 1 (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. R. Ching, Ph. D. • MIS • California State University, Sacramento 22

Cardinality 23 1: 1 Relationships Strong entity type Weak entity type Customers Customer_ID {PK} Customer_name Customer_address Zip_code Accounts Own 1. . 1 Mandatory participation A customer owns a minimum and maximum of one account Account_number {PK} 1. . 1 Customer_ID {FK} Account_type Current_balance 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. R. Ching, Ph. D. • MIS • California State University, Sacramento

Cardinality 24 1: M Relationships Strong entity type Weak entity type Customers Customer_ID {PK} Customer_name Customer_address Zip_code Accounts Own 1. . 1 Mandatory participation A customer owns a minimum one and maximum of many accounts R. Ching, Ph. D. • MIS • California State University, Sacramento Account_number {PK} 1. . * Customer_ID {FK} Account_type Current_balance An account is own by a minimum and maximum of one customer

Cardinality 25 • *: * (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 Customer_ID {PK} Customer_name Customer_address Zip_code Accounts Own 1. . * A customer owns a minimum one and maximum of many accounts R. Ching, Ph. D. • MIS • California State University, Sacramento Account_number {PK} 1. . * Customer_ID {FK} Account_type Current_balance An account is own by a minimum of one customer and maximum of many customers

ERD Notation Entity type 26 Primary key Relationship type Relationship name Video_categories Attributes Category_code {PK} 1. . 1 Category_title Videos Classify 0. . * Stock_number {PK} Video_title … Category_code {FK} Distributor_code {FK} Minimum Zero Maximum Many For an occurrence of videos, For an occurrence of video there may exist one and only categories, there may exist one video category. zero to many videos. Cardinality R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Video_categories Category_code {PK} 1. . 1 Category_title 27 Videos Classify 0. . * Video categories classify videos For an occurrence of distributors, there are zero to many videos Stock_number {PK} Video_title … Category_code {FK} Distributor_code {FK} 0. . * Release 1. . 1 Distributors release videos R. Ching, Ph. D. • MIS • California State University, Sacramento Distributor_code {PK} Distributor_name

ERD Notation Video_categories Category_code {PK} 1. . 1 Category_title 28 Videos Classify Video categories classify videos 0. . * Stock_number {PK} Video_title … Category_code {FK} Distributor_code {FK} 0. . * Release For an occurrence of videos, there is one and only one video category Distributors release videos R. Ching, Ph. D. • MIS • California State University, Sacramento 1. . 1 Distributors Distributor_code {PK} Distributor_name

ERD Notation 29 Video_categories Category_code {PK} 1. . 1 Category_title Videos Classify 0. . * Stock_number {PK} Video_title … Category_code {FK} Distributor_code {FK} 0. . * Attribute of the relationship type Number_of_videos_ released Release 1. . 1 Distributors Distributor_code {PK} Distributor_name R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternate Notation Entity type 30 Primary key (underscored) Relationship type Relationship name Music_categories Attributes Music_category_code Music_category_title Cardinality CDs Classify Stock_number CD_title Artist Music_category_code Record_label_code Zero (circle) Minimum Maximum (inside) Many (outside) (crows feet) R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternate Notation Entity type 31 Minimum cardinality of one (a music category has to have at least one CD) Music_categories CDs Music_category_code Music_category_title Classify Weak entity type (all four corners) R. Ching, Ph. D. • MIS • California State University, Sacramento Stock_number CD_title Artist Music_category_code Record_label_code

ERD Notation Alternative Notation 32 Music_categories Music_category_code Music_category_title CDs Classify A record label is related to a minimum of zero and maximum of many CDs Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternative Notation 33 Music_categories Music_category_code Music_category_title CDs Classify A CD is related to a minimum and maximum of one record label Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

ERD Notation Alternative Notation 34 Music_categories Music_category_code Music_category_title CDs Classify Quantity_produced Attribute of a relationship Stock_number CD_title Artist Music_category_code Record_label_code Produce Record_labels Record_label_code Record_label R. Ching, Ph. D. • MIS • California State University, Sacramento

35 R. Ching, Ph. D. • MIS • California State University, Sacramento