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

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


  • 144 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 of entity relationship modeling l.jpg

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

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

The (15 weeks) Schedule


A plan to teach er modeling4 l.jpg

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

19 Modules for ER Modeling


Slide6 l.jpg

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 modeling7 l.jpg

19 Modules for ER Modeling


Module 0 intro to er modeling l.jpg

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

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 model10 l.jpg

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.


Entity relationship diagram l.jpg

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…


Illustration the data l.jpg

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


Schema conceptual to relational l.jpg

instructor

course

teaches

name

phone

number

title

Schema: conceptual to relational


Module 1 entities and attributes l.jpg

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

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

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

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

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

Example…Entity Set in ER Model

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

BOOK

Database System

by G. Riccardi


Example entity set with attributes l.jpg

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.


Relational table from entity set l.jpg

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

Module 6: Relationship and Relationship Instances

  • Binary relationship in ER modeling.

  • Relationship set, relationship instance.

  • Relationship attributes.

  • Relationship has NO key attribute. Why?


Relationship instances l.jpg

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

Module 7: Participation Constraint

  • Total participation.

  • Partial participation.


Module 8 cardinality constraint l.jpg

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

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.


Entity sets l.jpg

employee

office

Entity Sets

  • Do NOT connect one Entity Set to another.

X


Example explicit relationship l.jpg

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


Example explicit relationship29 l.jpg

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


Example relationship or entity l.jpg

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


Example relationship or entity31 l.jpg

Judge

Pair

score

Example: relationship or entity?

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

rates


Example relationship to relationship l.jpg

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!

?


Example relationship to entities l.jpg

Judge

Man

Woman

Pair

score

rates

Example: relationship to entities!

pair number


Module 10 tables for relationship sets l.jpg

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

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.


Example weak entity set l.jpg

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…


Example weak entity set37 l.jpg

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


Example weak entity set38 l.jpg

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


Example weak entity set39 l.jpg

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:


Example weak entity set40 l.jpg

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)


Example weak entity set41 l.jpg

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)


Example weak entity set42 l.jpg

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

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

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

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

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

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

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.


Bachman s notation l.jpg

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 notation50 l.jpg

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 notation51 l.jpg

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 notation52 l.jpg

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 notation53 l.jpg

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 notation54 l.jpg

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 notation55 l.jpg

Bachman’s Notation


Module 18 er diagram in uml l.jpg

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

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

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