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

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

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

The (15 weeks) Schedule Week(s) Topic 1 to 6 Relational Data Model and Use of SQL 7 and 8 Normal Forms and Normalization (for design evaluation) 9 to 14 Entity-Relationship Modeling and database design 15 Database Systems: administration and management Nov 2, 2006

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

19 Modules for ER Modeling 0 1 2 Introduction to ER modeling 3 4 5 6 Key and Key Attributes Entities and Attributes Types of Attributes Tables for Entity Sets The Index Card Analogy Relationship and Relationship Instances 7 Participation Constraint 8 Cardinality Constraint 9 Nov. Design Rules and Tips 2, 2006 10 11 Tables for Relationship Sets 12 13 14 15 16 17 18 Tables for Weak Entity Sets Weak Entities and Dependency Relationship Creating Entity Sets in Design Specialization and Generalization Tables for Extended ER Model (min, max) Structural Constraint Bachman Notation ER Diagram using UML

M 0: Introduction to ER Modeling: 19 Modules M 1: Entity and Attributes M 2: Types of Attributes M 3: Key and Key Attributes Dependency Graph M 4: Tables for Entity Sets M 5: Index Card Analogy M 6: Relationship and Relationship Instances M 7: Participation Constraint M 8: Cardinality Constraint M 9: Design Rules M 16: (min, max) Notation M 10: Tables for Relationship Sets M 11: Weak Entities M 17: Bachman Notation optional M 12: Tables for Weak Entity Sets M 18: ER Diagram in UML optional M 13: Creating Entity Sets M 14: Specialization / Generalization Nov 2, 2006 M 15: Tables for Extended ER Model

19 Modules for ER Modeling 0 1 2 Introduction to ER modeling 3 4 5 6 Key and Key Attributes Entities and Attributes Types of Attributes Tables for Entity Sets The Index Card Analogy Relationship and Relationship Instances 7 Participation Constraint 8 Cardinality Constraint 9 Nov. Design Rules and Tips 2, 2006 10 11 Tables for Relationship Sets 12 13 14 15 16 17 18 Tables for Weak Entity Sets Weak Entities and Dependency Relationship Creating Entity Sets in Design Specialization and Generalization Tables for Extended ER Model (min, max) Structural Constraint Bachman Notation ER Diagram using UML

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

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

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

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

Illustration: the data … n Instructor P. Wu, phone x 9420, teaches the course IS 4240, Database Management System. n Instructor P. Laverty, phone x 9429, teaches the course IS 4244, Document Processing. n. . . name: P Laverty phone: x 9429 name: P. Wu phone: x 9420 number: 4240 title: DB Mgt Sys number: 4244 title: Doc Proc name: … phone: xxxxx number: 4249 title: E-Business instructors Nov 2, 2006 teaches courses

Schema: conceptual to relational instructor teaches course name number title phone INSTRUCTOR Name Nov 2, 2006 Phone TEACHES Name Number COURSE Number Title

Module 1: Entities and Attributes 1. Modeling things as entities and their attributes. 2. Attribute names and attribute values. 3. Entity as something specific, physical or conceptual. 4. Entity, entity type, and entity set. Nov 2, 2006

Module 2: Types of Attributes 1. Simple and composite attributes 2. Stored and derived attributes 3. Single and multiple valued attributes 4. Optional attributes Nov 2, 2006

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

Module 4: Tables for Entity Sets 1. Relational table for an entity set. 2. Primary key and secondary keys. 3. Optional attribute and column allowing null. 4. Extra table for multi-valued attribute. Nov 2, 2006

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

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

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

Relational Table from Entity Set Name Student Major student name: John Doe major: Info Sci student name: Pat Wong name: Lily Smithmajor: Comp Sci major: Psychology Student Name Note that the ER Model conveys the conceptual structure of information, not the data values. Nov 2, 2006 Major John Doe Info Sci Lily Smith Psychology Pat Wong Comp Sci . . .

Module 6: Relationship and Relationship Instances 1. Binary relationship in ER modeling. 2. Relationship set, relationship instance. 3. Relationship attributes. 4. Relationship has NO key attribute. Why? Nov 2, 2006

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

Module 7: Participation Constraint 1. Total participation. 2. Partial participation. Nov 2, 2006

Module 8: Cardinality Constraint 1. 1 -to-1 cardinality ratio. 2. 1 -to-many and many-to-1 cardinality ratios. 3. Many-to-many cardinality ratio. 4. Cardinality ratios working with participation constraints. 5. Properly interpreting cardinality constraints. Nov 2, 2006

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

Entity Sets n Do NOT connect one Entity Set to another. employee Nov 2, 2006 X office

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

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

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

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

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

Example: relationship to entities! Man Woman Pair pair number Judge rates score Nov 2, 2006

Module 10: Tables for Relationship Sets 1. Additional table is needed for M-to-M 2. 3. 4. 5. 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. Nov 2, 2006

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

Example: Weak Entity Set n Consider modeling the information in a library. We have the entity set Book for books… title Book author year Call No Nov 2, 2006

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

Example: Weak Entity Set n The Book entity set: does each entity represent a book? OR a copy of a book? Need copy number! title Book Copy author year Call No + Copy No Nov 2, 2006

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

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

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

Example: Weak Entity Set n We will allow a weak entity set for Book Copy, with partial key copy no (underlined by dash), and a dependency relationship copies to a strong entity set. 1 Book copies N Book Copy title author Nov 2, 2006 copy no call no

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

Module 13: Creating Entity Sets in Design 1. Entity versus attribute: an attributive entity. 2. Entity versus relationship: an associative weak entity. 3. Weak entity with multiple dependency relationships. Nov 2, 2006

Module 14: Specialization and Generalization: Extended ER Modeling 1. Referring to a subset of an entity set: 2. 3. 4. 5. 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. Nov 2, 2006

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

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

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

Bachman’s Notation Employee is assigned to Office n Every employee is assigned to exactly one office and every office is assigned one employee. n One-to-One relationship with total participation at both ends. Nov 2, 2006

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

Bachman’s Notation Systems Analyst is in charge of Project n An analyst may be in charge of many projects (or may not be in charge of any) and every project has exactly one systems analyst in charge. n One-to-Many relationship with total participation at one end (Project) and partial at the other (Analyst). Nov 2, 2006

Bachman’s Notation Machine is undergoing Scheduled Maintenance n A machine may or may not be undergoing scheduled maintenance and every scheduled maintenance always refers to exactly one machine. n One-to-One relationship with total participation at one end (Scheduled Maintenance) and partial at the other (Machine). Nov 2, 2006

Bachman’s Notation Salesperson is assigned to call on Customer n Every salesperson is assigned to call on one or more customers, and every customer has one or more salespersons assigned to call. n Many-to-Many relationship with total participation at both ends. Nov 2, 2006

Bachman’s Notation Home Office is permitted to Employee n Every home office is permitted to one or more employees, and an employee may or may not be permitted to a home office. n One-to-Many relationship with total participation at one end (Home Office) and partial at the other end (Employee). Nov 2, 2006

Bachman’s Notation Partner participation is optional, with at most one unit per partner: one-to-? … (0, 1). Partner participation is mandatory, with at most one unit per partner: one-to-? … (1, 1). Partner participation is mandatory, with possibly many units per partner: many-to-? … (1, *). Partner participation is optional, with possibly many units per partner: many-to-? … (0, *). Nov 2, 2006

Module 18: ER Diagram in UML 1. Basic UML: classes and objects. 2. Public, private, and protected attributes. 3. Connection and multiplicities. 4. Broader software construction aspects of UML. Nov 2, 2006

19 Modules to teach ER modeling n Small modules: complete and compact n Specific learning objectives in each module n Motivate self-study: perceiving progress… n Minimize need for face-to-face contact hours n Sequence: each builds on previous ones n Exercise for each module re-enforces the learning objectives thru practice. n Suitable to delivery on-line. Nov 2, 2006

Further Work … n Compare student performance and attitude to evaluate the effectiveness of the plan. n The modules may constitute a stand-alone tutorial on ER modeling. n Try the experimental use of Intelligent Tutoring System. n Gather sufficient exercises and tests to deliver the teaching on-line, for self-paced studying. Nov 2, 2006