1 / 70

Temple University – CIS Dept. CIS331– Principles of Database Systems

This overview provides an introduction to E-R diagrams and mapping constraints in database systems, including concepts, entities, relationships, attributes, and more.

maryernest
Download Presentation

Temple University – CIS Dept. CIS331– Principles of Database Systems

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. Temple University – CIS Dept.CIS331– Principles of Database Systems V. Megalooikonomou E-R Diagrams (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

  2. Overview • Concepts • Entities • Relationships • Attributes • Mapping constraints: cardinalities • Keys • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables

  3. The Challenge: … a sample Database (from last lecture)

  4. The Tools Entities (‘entity sets’) N M Relationships (‘rel. sets’) and mapping constraints P attributes

  5. Entity Sets • 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 • Entities have attributes • Example: people have names and addresses • An entity set is a set of entities of the same type that share the same properties • Example: set of all persons, companies, holidays

  6. Attributes • An entity is represented by a set of attributes, that is, descriptive properties possessed by all members of an entity set. • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite (e.g., name (first,middle,last)) attributes • Single-valued and multi-valued attributes • E.g. multivalued attribute: phone-numbers, dependents, etc • Derived attributes • Can be computed from other attributes • E.g., age, given date of birth E.g., customer = (customer-id, customer-name, customer-street, customer-city) loan = (loan-number, amount)

  7. Composite Attributes

  8. Relationship Sets • A relationship is an association among several entities Example:HayesdepositorA-102customer entity relationship set account entity • 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 (entity sets E1,E2,…, En participate in the relationship) • Example: (Hayes, A-102)  depositor

  9. More examples … Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per course nouns -> entity sets verbs -> relationships

  10. ... name STUDENT ssn INSTRUCTOR issn

  11. ... name STUDENT c-id ssn c-name COURSE INSTRUCTOR issn but: sections of course (with different instructors)?

  12. ssn STUDENT c-id s-id SECTION COURSE but: s-id is not unique... issn INSTRUCTOR

  13. ssn STUDENT N c-id takes M s-id SECTION COURSE issn INSTRUCTOR

  14. STUDENT N c-id takes M s-id SECTION COURSE N teaches 1 INSTRUCTOR

  15. Mapping Constraints: Cardinalities • 1 to 1 (example?) • 1 to N • N to M

  16. Mapping Cardinalities One to one One to many Note: Some elements in A and B may not be mapped to any elements in the other set

  17. Mapping Cardinalities Many to one Many to many Note: Some elements in A and B may not be mapped to any elements in the other set

  18. STUDENT N c-id takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR Example

  19. E-R Diagrams • Rectangles represent entity sets • Diamonds represent relationship sets • Lines link attributes to entity sets and entity sets to relationship sets • Ellipses represent attributes • Double ellipses represent multivalued attributes • Dashed ellipses denote derived attributes • Underline indicates primary key attributes (will study later)

  20. More details • ‘weak’ entities: if they need to borrow a unique id from a ‘strong entity - DOUBLE box. • ‘c-id’ + ‘s-id’: unique id for SECTION • discriminator (eg., ‘s-id’) c-id 1 N s-id has SECTION COURSE

  21. More details • Entity sets of a relationship need not be distinct • self-relationships – example ?

  22. manages 1 EMPLOYEE N More details • Entity sets of a relationship need not be distinct • self-relationships – example ? • Usually different “roles” are indicated by labeling the lines that connect diamonds to rectangles

  23. Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • E.g.: One-to-one relationship: • A customer is associated with at most one loan via the relationship borrower • A loan is associated with at most one customer via borrower

  24. More details • Binary relationships • 3-way and k-way relationships? N M EMPLOYEE TOOL uses P PROJECT

  25. More details - attributes • superkey: a set of one or more attributes whose values uniquely determine each entity (eg., (ssn, address) ) • candidate key: a minimal super key (eg., ssn; employee#) • primary key: a cand. key, chosen by DBA • multivalued or set-valued attributes (eg., ‘dependents’ for EMPLOYEE) • derived attributes (eg., 15% tip)

  26. Participation of an Entity Set in a Relationship Set More details: • Totalparticipation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g. participation of loan in borrower is total • every loan must have a customer associated to it via borrower • Partial participation: some entities may not participate in any relationship in the relationship set • E.g. participation of customer in borrower is partial

  27. Alternative Notation for Cardinality Limits More details: • Cardinality limits (min and max) can also express participation constraints (min=1 implies total participation of an entity set to a relationship set, max=1 implies that an entity participates in at most one relationship, max=* implies no limit).

  28. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • turning E-R diagrams to tables

  29. eg., students: part time (#credit-hours) and full time (major) Extended ER features: Specialization name STUDENT ssn IS-A major FT-STUDENT PT-STUDENT #credits

  30. Specialization • Top-down design process; designate subgroupings within an entity set that are distinctive from other entities in the set • These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set • Depicted by a triangle component labeled ISA (E.g. customer “is a” person) • Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked

  31. Another specialization example

  32. Generalization • … opposite to specialization • A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set • Specialization and generalization are inversions of each other; they are represented in an E-R diagram in the same way • The terms specialization and generalization are used interchangeably

  33. Specialization and generalization (Cont.) • Can have multiple specializations of an entity set based on different features • E.g. permanent-employee vs. temporary-employee, in addition to officer vs. secretary vs. teller • Each particular employee would be • a member of one of permanent-employee or temporary-employee, • and also a member of one of officer, secretary, or teller • The ISA relationship also referred to as superclass - subclass relationship

  34. Design Constraints on a Specialization/Generalization • Constraint on which entities can be members of a given lower-level entity set • condition-defined • E.g. all customers over 65 years are members of senior-citizen entity set; senior-citizen ISA person • user-defined • Constraint on whether or not entities may belong to more than one lower-level entity sets within a single generalization • Disjoint • an entity can belong to only one lower-level entity set • Noted in E-R diagram by writing disjoint next to the ISA triangle • Overlapping • an entity can belong to more than one lower-level entity set

  35. Design Constraints on a Specialization/Generalization (Cont.) • Completenessconstraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization • total: an entity must belong to one of the lower-level entity sets • partial: an entity need not belong to one of the lower-level entity sets

  36. Observations • Generalization: exact reverse of ‘specialization’ • attribute inheritance • could have many levels of an IS-A hierarchy

  37. treat a relationship as an ‘abstract’ entity allows relationships between relationships rarely used Aggregation

  38. Binary vs. Non-Binary Relationships • Some relationships that appear to be non-binary may be better represented using binary relationships • E.g. A ternary relationship parents, relating a child to his/her father and mother, is best replaced by two binary relationships, father and mother • Using two binary relationships allows partial information (e.g. only mother being known) • But there are some relationships that are naturally non-binary • E.g. works-on

  39. Converting Non-Binary Relationships to Binary Form • In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set • Replace R between entity sets A, B and Cby an entity set E, and three relationship sets: 1. RA, relating E and A 2.RB, relating E and B 3. RC, relating E and C • Create a special identifying attribute for E • Add any attributes of R to E • For each relationship (ai , bi , ci) in R, create 1. a new entity eiin the entity set E 2. add (ei , ai ) to RA 3. add (ei , bi) to RB 4. add (ei , ci ) to RC

  40. E-R Design Decisions  • The use of an attribute or entity set to represent an object • Whether a real-world concept is best expressed by an entity set or a relationship set • Use of a ternary relationship versus a pair of binary relationships • Use of a strong or weak entity set • Use of specialization/generalization – contributes to modularity in the design • Use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure

  41. E-R Diagram for a Banking Enterprise

  42. Overview • concepts • Entities • Relationships • Attributes • Specialization/Generalization • Aggregation • Reduction of E-R diagrams to tables

  43. STUDENT N c-id grade takes M 1 N has s-id SECTION COURSE N teaches 1 INSTRUCTOR

  44. Representing strong entities just list the attributes, and underline the primary key, eg. STUDENT(ssn, name, address)

  45. Representing Multivalued attributes Eg., EMPLOYEE with many dependents: • introduce a new table, with (ssn, dependent-name)

  46. Representing relationships • get primary keys of all involved entities • primary key - depends on cardinality • 1 to 1: use either key, e.g., EMPLOYEE( ssn, empno, name, ...) • 1 to N: use the key of the ‘N’ part, e.g., TEACHES( issn, c-id, s-id) • N to M: use both keys, e.g., TAKES( ssn, c-id, s-id, grade)

  47. Representing relationships • 1 to N: no need for separate table - eg., SECTION( issn, room-num, c-id, s-id) instead of SECTION1(c-id, s-id, room-num) TEACHES(issn, c-id, s-id) • for rel. between strong and corresponding weak entity, no need for table, at all!

  48. Representing Generalization/Specialization Two solutions: - one table for each or - no table for super-entity (pros and cons?)

  49. Representing Generalization/Specialization E.g., STUDENT(ssn, name, address) PT-STUDENT( FT-STUDENT(

  50. Representing Generalization/Specialization E.g., STUDENT(ssn, name, address) PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major

More Related