1 / 35

Week 9 October 24

Week 9 October 24. Modeling with ERD. 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

hcockerham
Download Presentation

Week 9 October 24

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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_code 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 Attributes Classify 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_address 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 Payment_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} TransactionDate {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 (1:1) • 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 • 1:1 (1..1) minimum is one – mandatory participation • 0:1 (0..1) minimum is zero – optional participation

  22. 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.

  23. Cardinality 1:1 Relationships Strong entity type Weak entity type Customers Accounts Own Customer_ID {PK} Customer_name Customer_address Zip_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 1:M Relationships Strong entity type Weak entity type Customers Accounts Own Customer_ID {PK} Customer_name Customer_address Zip_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 Zip_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 Distributors release videos Distributor_code {PK} Distributor_name

  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 Distributors release videos Distributor_code {PK} Distributor_name

  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 Attributes Classify 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 NotationAlternative 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 NotationAlternative 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 Quantity_produced Produce Attribute of a relationship Record_labels Record_label_code Record_label

More Related