1 / 30

Entity-Relationship Model

Entity-Relationship Model. Chapter 2 B.Ramamurthy. Introduction. E-R model facilitates database design by allowing the specification of an “enterprise schema” which represents the overall logical structure of a database.

ostinmannual
Download Presentation

Entity-Relationship Model

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. Entity-Relationship Model Chapter 2 B.Ramamurthy B.Ramamurthy

  2. Introduction • E-R model facilitates database design by allowing the specification of an “enterprise schema” which represents the overall logical structure of a database. • The E-R model is extremely useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. B.Ramamurthy

  3. Topics for Discussion • Entity Sets • Relationship Sets • Design Issues • Mapping Constraints • Keys • E-R Diagram • Extended E-R Features • Design of an E-R Database Schema • Reduction of an E-R Scheme to Tables B.Ramamurthy

  4. Entity • A database can be modeled as : • a collection of entities • relationships among entities. • An entity is an object that exists and is distinguishable from other objects. • Example: specific person, company, event, plant • More Specific example: Consider Federal Government as a large enterprise. • Entities : President, IRS, amendment5, senate B.Ramamurthy

  5. Entity Sets • An entity set is a set of entities of the same type that share the same properties: • Examples: set of all persons, companies, trees, holidays • Considering Federal government : senator, office, cherry-blossom, fed-holiday B.Ramamurthy

  6. Attributes • An entity is represented by a set of attributes. • Attributes are properties possessed by all members of an entity set. • Example: Customer = (name, SSN, street, city) account = (number, balance) Consider federal government : senator = (name, state, party) amendment = (number, definition) B.Ramamurthy

  7. Attributes (contd.) • Domain : the set of permitted values for each attribute • Example: domain of attribute “state” comprises the 50 states • Cardinality of a domain : 50 is the cardinality of state attribute • Attribute types: • simple and composite • single valued and multi-valued • null • derived B.Ramamurthy

  8. Attribute Types : Examples • Single/composite : zip code is a single integer whereas street is composite attribute • Single/multi-valued: loan number is unique single values whereas dependent could be many. • Null : no values, starting point, indication of empty • Derived : Number of accounts held by a person can be derived (counted) and need not be physically represented wasting storage. B.Ramamurthy

  9. Hayes A-102 Account entity Customer entity Relationship • A relationship is an association among entities. Depositor Relationship B.Ramamurthy

  10. Relationship Sets • A relationship set is a mathematical relation among n >= 2 entities, each taken from entity sets {(e1,e2, ….en) | e1 Є E1, e2 Є E2, …en Є En} where (e1,e2,..en) is a relationship, E1, E2, .. En are entity sets (Hayes, A-102) Є Depositor B.Ramamurthy

  11. access-date street SSN number balance city name customer depositor account entity attribute relationship Attribute of a Relationship Set B.Ramamurthy

  12. Refers to the number of entity sets that participate in a relationship set. Relationship sets that involve two entity sets are binary. Generally most relationship sets in a database are binary. Relationship sets may involve more than two entity sets: the entity customer, loan and branch may be linked by ternary relationship set. N-nary relationship are also possible. Degree of Relationship Set B.Ramamurthy

  13. Entity set of a relationship need not be distinct. For example Roles phone name city SSN manager employee works-for worker B.Ramamurthy

  14. Roles (contd.) • The labels “manager” and “worker” are called “roles” • They specify how employee entities interact via the “works-for” relationship set • Roles are indicated in ER diagrams by labeling the lines that connect diamonds to rectangles. • Roles are optional. • They clarify semantics of a relationship. B.Ramamurthy

  15. Design Issue 1 • Entity sets vs. Attributes • depends on enterprise being modeled and the semantics of the data • Example 1: An auto is a dealership could be attribute whereas in a manufacturing plant it is an entity set. • Example 2: The type of a number to an application program is an attribute but in a mathematical system it is en entity-set. B.Ramamurthy

  16. Design Issue 2 • Entity sets vs Relationship sets • Relationship set is to describe an action that occurs among entities. • Example 1: Author for a book company could be an entity-set whereas it is relationship set between entity-sets Person and Books • One of the ways to distinguish between entity-sets and relationships is to use connectives such “for” , “of” : • Example : “Author of” instead “Author” explicitly puts that as a relationship set than an entity set. • Example 2 : “Works for” instead of “worker” B.Ramamurthy

  17. Design Issue 3 • Binary vs n-ary relationship sets • Example: Lawyer, Business, Bank entity sets related by a relationship set Business-Transaction • Even though it is possible to replace a n-ary relationship set by a number of binary relationship sets, n-ary is more expressive. • Often n-ary to binary is done for ease of analysis. B.Ramamurthy

  18. Exercises • Hwk1 : 2.3, 2.4, 2.5 (for 2.3 and 2.4 only) , 2.6 Due : 9/8/2000 • Ex 2.2 page 59 B.Ramamurthy

  19. Mapping Cardinalities • The number of entities to which another entity can be associated via a relationship set. • One of the following types: • one to one (Ex: wife entity set, marriage relationship set, husband entity set) • one to many (Ex: teacher entity set, course relationship set, student entity set) • many to one (Ex: client entity set, law suit relationship set, company entity set) • many to many (Ex: people entity set, travel relationship entity, cities entity set) B.Ramamurthy

  20. Existence Dependencies • If the existence of entity x depends on entity y, x is said to be dependent on y. (Ex: payment and loan) • Y is a dominant entity • X is a subordinate entity • In the example, if loan is deleted the payment should also go. B.Ramamurthy

  21. Total and Partial Participation • If every entity in a entity set has at least one relation in a relationship set then it is known as “total” participation. • Ex: Employee entity set , salary-paid relationship set, salary-code entity set • If only some of the entities have relations in a relationship set then it is known as “partial” participation. • Ex: Employee entity set, on-leave relationship set, dates entity set B.Ramamurthy

  22. Keys • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity. • A candidate key of an entity set is a minimal super key. Ex: SSN, account number • One of the candidate keys is selected to be the primary key. • Combination of primary keys of participating entity sets forms candidate key of a relationship set • Ex: (SSN, acctNo) is the primary key of depositor relationship set. B.Ramamurthy

  23. E-R Diagram Components • Rectangles represent entity sets • Ellipses represent attributes • Diamonds represent relationship sets • Lines link attributes to entity sets, entity sets to relationship sets (also represent roles) • Double ellipses multi-valued attributes • Dashed ellipses denote derived value attributes • Primary key attributes are underlined. B.Ramamurthy

  24. Weak Entity Sets • An entity set that does not have a primary key is referred to as a weak entity set. • The existence of a weak entity set is dependent on the existence of a strong entity set. • Discriminator or partial key of a weak entity set is the set of all attributes that distinguishes among all the entities of a weak entity set. • Primary key of strong entity set + discriminator is the primary key of a weak entity set. B.Ramamurthy

  25. Weak Entity Set (contd.) • Use double rectangle to represent weak entity set. • Underline the discriminator of a weak entity set with dashed line. B.Ramamurthy

  26. Weak Entity Set : Example date amount number amount number loan loan-payment payment Weak entity set is non-existent is the corresponding strong entity set is removed. B.Ramamurthy

  27. Strong entity sets to tables : Reduces to a table with same attributes Weak entity sets to tables : table that includes a column for primary key of identifying strong entity set. Relationship sets to tables : has columns corresponding to primary keys of the entity sets and the attributes of the relationship set. No need for table corresponding to weak relationship set. An algorithm for conversion from ER to tables is discussed in the recitation. E-R Model to Table B.Ramamurthy

  28. Multi-values Attributes • Multi-valued attributes result in a table. • But often a combination of tables can be reduced into fewer tables. • Ex: Account-balance. • Aggregation and Generalization relationships are also represented by a set of tables. B.Ramamurthy

  29. E-R Diagram for the Banking Enterprise • Lets go through a complete example in Fig.2.18 and examine how to convert it to relational tables. B.Ramamurthy

  30. Summary • E-R Model provides a means for analyzing and representing the database design for an enterprise. • E-R diagram can be systematically converted into a tables defining the logical structure of the database. B.Ramamurthy

More Related