Self paced learning and on line teaching of entity relationship modeling l.jpg
Sponsored Links
This presentation is the property of its rightful owner.
1 / 58

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


  • 151 Views
  • Uploaded on
  • Presentation posted in: General

Self-paced Learning and On-line Teaching of Entity-Relationship Modeling. Peter Y. Wu Jeanne M. Baugh Valerie J. Harvey. 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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Self-paced Learning and On-line Teaching ofEntity-Relationship Modeling

Peter Y. Wu

Jeanne M. Baugh

Valerie J. Harvey

(c) Robert Morris University 2006


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


The (15 weeks) Schedule


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


19 Modules for ER Modeling


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


19 Modules for ER Modeling


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.


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.


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.


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…


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


instructor

course

teaches

name

phone

number

title

Schema: conceptual to relational


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.


Module 2: Types of Attributes

  • Simple and composite attributes

  • Stored and derived attributes

  • Single and multiple valued attributes

  • Optional attributes


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.


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.


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.


Example…Entity Set in ER Model

Question: are we talking about a book (published) or a copy (printed) ?

BOOK

Database System

by G. Riccardi


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.


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.


Module 6: Relationship and Relationship Instances

  • Binary relationship in ER modeling.

  • Relationship set, relationship instance.

  • Relationship attributes.

  • Relationship has NO key attribute. Why?


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


Module 7: Participation Constraint

  • Total participation.

  • Partial participation.


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.


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.


employee

office

Entity Sets

  • Do NOT connect one Entity Set to another.

X


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


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


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


Judge

Pair

score

Example: relationship or entity?

Each pair will compete and get rated by three judges, each giving a score…

rates


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!

?


Judge

Man

Woman

Pair

score

rates

Example: relationship to entities!

pair number


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.


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.


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…


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?)


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


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:


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)


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)


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.


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.


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.


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.


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


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.


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.


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


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


Bachman’s Notation

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

  • One-to-Many relationship with total participation at one end (Project) and partial at the other (Analyst).

Systems Analyst

is in charge of

Project


Bachman’s Notation

  • A machine may or may not be undergoing scheduled maintenance and every scheduled maintenance always refers to exactly one machine.

  • One-to-One relationship with total participation at one end (Scheduled Maintenance) and partial at the other (Machine).

is undergoing

Scheduled Maintenance

Machine


Bachman’s Notation

  • Every salesperson is assigned to call on one or more customers, and every customer has one or more salespersons assigned to call.

  • Many-to-Many relationship with total participation at both ends.

is assigned to call on

Salesperson

Customer


Bachman’s Notation

  • Every home office is permitted to one or more employees, and an employee may or may not be permitted to a home office.

  • One-to-Many relationship with total participation at one end (Home Office) and partial at the other end (Employee).

Home Office

is permitted to

Employee


Bachman’s Notation


Module 18: ER Diagram in UML

  • Basic UML: classes and objects.

  • Public, private, and protected attributes.

  • Connection and multiplicities.

  • Broader software construction aspects of UML.


19 Modules to teach ER modeling

  • Small modules: complete and compact

  • Specific learning objectives in each module

  • Motivate self-study: perceiving progress…

  • Minimize need for face-to-face contact hours

  • Sequence: each builds on previous ones

  • Exercise for each module re-enforces the learning objectives thru practice.

  • Suitable to delivery on-line.


Further Work …

  • Compare student performance and attitude to evaluate the effectiveness of the plan.

  • The modules may constitute a stand-alone tutorial on ER modeling.

  • Try the experimental use of Intelligent Tutoring System.

  • Gather sufficient exercises and tests to deliver the teaching on-line, for self-paced studying.


  • Login