Self-paced Learning and On-line Teaching of Entity-Relationship Modeling

1. Self-paced Learning and On-line Teaching ofEntity-Relationship Modeling Peter Y. Wu Jeanne M. Baugh Valerie J. Harvey (c) Robert Morris University 2006

2. A plan to teach ER Modeling • Part of an undergraduate Database course • IS 2002 Model Curriculum requirements • 6 weeks out of 15 – for a 3 credits course • Designed for flexibility to cater to students • minimize need for face-to-face contact • facilitate for self-study and on-line learning • For self-paced learning and on-line teaching • Rely more on the students’ self-discipline

3. The (15 weeks) Schedule

4. A plan to teach ER Modeling • For 6 weeks … (out of the 15-weeks course) • The plan covers: • Fundamentals of Entity-Relationship Modeling • Application to the design of Relational Schema • Extended ER Modeling with Specialization • Optional alternative notations: • Bachman • UML • (automated tools) … to be added

5. 19 Modules for ER Modeling

6. M0: Introduction to ER Modeling M1: Entity and Attributes M2: Types of Attributes M3: Key and Key Attributes M4:Tables for Entity Sets M5: Index Card Analogy M6: Relationship and Relationship Instances M7: Participation Constraint M8: Cardinality Constraint M9: Design Rules M16: (min,max) Notation M10:Tables for Relationship Sets M11:Weak Entities M17: Bachman Notation optional M12: Tables for Weak Entity Sets M18: ER Diagram in UML optional M13: Creating Entity Sets M14: Specialization / Generalization M15: Tables for Extended ER Model ER Modeling: 19 Modules Dependency Graph

7. 19 Modules for ER Modeling

8. Module 0: Intro to ER modeling • Tool for database system analysis and design. • Simple case study as an example. • ER model captures the way business works with data. • ER diagram expresses the model, i.e., the schema.

9. Entity-Relationship Model Brieflyintroduced… • Entity – thing • Relationship – nature of association between things (entities) • Entity-Relationship Modeling – a way to model the real world, as things, and how the things are associated together. • Entity-Relationship Diagram – we present our model graphically.

10. Entity-Relationship Model • Entity – a specific thing, physical or conceptual. Example: the sun, the number one, the car model Mustang, the man Adam. • Attributes and Values – to model an entity by its properties (e.g. color: red) • Relationship – the way specific things are associated in the nature of a relationship. Example: Adam and Eve as husband and wife. The association of related entities together constitute a relationship instance.

11. instructor course teaches name phone number title Entity-Relationship Diagram Example… (Info System for the university registrar) • There are these entities: courses and instructors. • Each instructor has a name and a phone number. • Each course has course number and title. • Instructors teach courses…

12. name: P Laverty phone: x9429 number: 4240 title: DB Mgt Sys name: P. Wu phone: x9420 number: 4244 title: Doc Proc name: … phone: xxxxx number: 4249 title: E-Business instructors courses Illustration: the data… • Instructor P. Wu, phone x9420, teaches the course IS4240, Database Management System. • Instructor P. Laverty, phone x9429, teaches the course IS4244, Document Processing. • . . . teaches

13. instructor course teaches name phone number title Schema: conceptual to relational

14. Module 1: Entities and Attributes • Modeling things as entities and their attributes. • Attribute names and attribute values. • Entity as something specific, physical or conceptual. • Entity, entity type, and entity set.

15. Module 2: Types of Attributes • Simple and composite attributes • Stored and derived attributes • Single and multiple valued attributes • Optional attributes

16. Module 3: Key and Key Attributes • The uniqueness constraint in ER modeling. • Key as a collection of attributes • Key, super key, and candidate key • Different candidate keys for the same entity set.

17. Module 4: Tables for Entity Sets • Relational table for an entity set. • Primary key and secondary keys. • Optional attribute and column allowing null. • Extra table for multi-valued attribute.

18. Module 5: The Index Card Analogy • Entity set as a deck of index cards. • Entity set name and attribute names are printed on each card. • A card with attribute values filled in represents the entity.

19. Example…Entity Set in ER Model Question: are we talking about a book (published) or a copy (printed) ? BOOK Database System by G. Riccardi

20. course course course title:E-Business number:4249 title:Doc Proc number:4244 title:DBMS number:4240 Example…Entity Set with Attributes … think of each entity set as a deck of cards. Each card is labeled with entity type, and the sameattributes, being of the same type. Each card has its own specificvalues for each of the attributes, representing a specific entity.

21. student student student name:Pat Wong major:Comp Sci name:John Doe major:Info Sci name:Lily Smith major:Psychology Name Major Student Relational TablefromEntity Set Note that the ER Model conveys the conceptual structure of information, not the data values.

22. Module 6: Relationship and Relationship Instances • Binary relationship in ER modeling. • Relationship set, relationship instance. • Relationship attributes. • Relationship has NO key attribute. Why?

23. course course teacher teacher title:E-Business number:4249 title:DB Mgt Sys number:4240 name:P.Wu phone:x 9427 name:P. Laverty phone:x 9420 teaches teaches (P. Laverty, 4249) (P.Wu, 4240) Relationship Instances… Relationship Set

24. Module 7: Participation Constraint • Total participation. • Partial participation.

25. Module 8: Cardinality Constraint • 1-to-1 cardinality ratio. • 1-to-many and many-to-1 cardinality ratios. • Many-to-many cardinality ratio. • Cardinality ratios working with participation constraints. • Properly interpreting cardinality constraints.

26. Module 9: Design Rules and Tips • Two entity sets can connect only thru a relationship. • Attribute value should never imply a relationship between entities; relationship should be explicit in the model. • Relationship should never connect with another relationship; at least one of them can be treated as an entity set.

27. employee office Entity Sets • Do NOT connect one Entity Set to another. X

28. name employee office room# phone e-mail occupant office Example: Explicit Relationship! We should model relationship explicitly and avoid using attributes to imply a relationship. occupies X X

29. name employee office room# phone e-mail Example: Explicit Relationship. So that we may also keep track of changes in the near future within our database… occupies effective date

30. pairs up with Man Woman pair number Example: relationship or entity? Men and Women members of a skating club are going to pair up in a competition….

31. Judge Pair score Example: relationship or entity? Each pair will compete and get rated by three judges, each giving a score… rates

32. pairs up with Judge Man Woman pair number score rates Example: relationship to relationship? • A relationship is the association of entities, should not be that of other relationships! ?

33. Judge Man Woman Pair score rates Example: relationship to entities! pair number

34. Module 10: Tables for Relationship Sets • Additional table is needed for M-to-M relationship. • M-to-1 relationship: table at the “1” side may be extended to carry information for the relationship. • 1-to-1 relationship: may extend either side, or both sides. • Use of foreign keys in these tables. • Allowing null values for partial participation.

35. Module 11: Weak Entities and Dependency Relationships • Entities dependent on its relationships for identity. • Symbol for entity set and dependency relationships. • When dependency relationship is 1-to-1… • The need for partial key when dependency relationship is M-to-1.

36. title Book author year Call No Example: Weak Entity Set • Consider modeling the information in a library. We have the entity set Book for books…

37. Member Book loan due date Example: Weak Entity Set • Members of the library may borrow books: we have a relationship between Book and Member… PROBLEM! • Can we have two different members borrowing the same book? (More accurately stated: two different copies of the same book?)

38. title Book Copy author year Example: Weak Entity Set • The Book entity set: does each entity represent a book? OR a copy of a book? Need copy number! Call No + Copy No

39. title title Book Copy Book author author call no call no copy no Example: Weak Entity Set • We need to deal with two different concepts in the library: book, and copy of a book:

40. reserves borrows Book Copy Book Member expected date due date Example: Weak Entity Set • With both Book and Book Copy, we can properly model the relationships with Member. (0,*) (0,*) (0,4) (0,1)

41. title copies title Book Copy 1 N Book author author call no call no copy no Example: Weak Entity Set • We duplicate a good deal of information in the entity sets: Book and Book Copy – the two should be related! (implied relationship)

42. title 1 copies N Book Copy Book author call no copy no Example: Weak Entity Set • We will allow a weak entity set for Book Copy, with partial keycopy no (underlined by dash), and a dependency relationshipcopies to a strong entity set.

43. Module 12: Tables for Weak Entity Sets • Weak entity set translates into a table in the same way (as strong entity set). • Key for the weak entity set table. • The need to combine with partial key. • Extending the table to take care of attributes of the dependency relationship.

44. Module 13: Creating Entity Sets in Design • Entity versus attribute: an attributive entity. • Entity versus relationship: an associative weak entity. • Weak entity with multiple dependency relationships.

45. Module 14: Specialization and Generalization: Extended ER Modeling • Referring to a subset of an entity set: specialization in Extended ER modeling. • Super class and sub-class, with inheritance. • Total and partial specialization. • Disjoint and overlap specialization. • Generalization in Extended ER modeling: the reverse of specialization.

46. Module 15: Tables for the Extended ER Model • Table for superclass – same as an entity set. • Table for subclass: keys inherited. • Foreign key in subclass tables. • Special cases when specialization is total and disjoint (superclass table unnecessary).

47. Module 16: (min,max) Structural Constraint • Definition of the (min,max) notation for structural constraint. • Interpreting the (min,max) notation into participation and cardinality constraints.

48. Module 17: Bachman Notation • Entity sets – no more ovals…attributes listed inside the box (or use pop-up window in a computerized tool). • No more diamonds; a line connecting two boxes indicate relationship between two entity sets. • Adornment for structural constraints. • Notes on associative and attributive entities.

49. Employee Office Bachman’s Notation • Every employee is assigned to exactly one office and every office is assigned one employee. • One-to-One relationship with total participation at both ends. is assigned to

50. Bachman’s Notation • Every cargo aircraft serves one or more distribution centers and every center is served exactly one aircraft. • One-to-Many relationship with total participation at both ends. Cargo Aircraft serves Distribution Center