1 / 58

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

A comprehensive plan for teaching ER modeling in a self-paced and online learning environment, covering fundamentals, relational schema design, specialization, and more.

naomir
Download Presentation

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

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

More Related