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

1. Temple University – CIS Dept.CIS616– 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 that all members of an entity set have • 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., multi-valued 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 what about different 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 (e.g., ‘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 (e.g., (ssn, address) ) • candidate key: a minimal super key (e.g., ssn; employee#) • primary key: a cand. key, chosen by DBA • multivalued or set-valued attributes (e.g., ‘dependents’ for EMPLOYEE) • derived attributes (e.g., 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. e.g., 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; we use the terms interchangeably • could have many levels of an IS-A hierarchy • attribute inheritance

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 Specialization/Generalization • The 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 • The 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. treats a relationship as an ‘abstract’ entity allows relationships between relationships rarely used Aggregation

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

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

39. E-R Design Decisions  • Decisions regarding: • 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 ones • Use of a strong or weak entity set • Use of specialization/generalization (that 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

40. E-R Diagram for a Banking Enterprise

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

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

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

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

45. 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)

46. 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!

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

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

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

50. Representing Generalization/Specialization no super-entity: [STUDENT(ssn, name, address)] PT-STUDENT( ssn, num-credits FT-STUDENT( ssn, major