26fentity_relationship_model(bob).ppt

7/22/2019 26FEntity_Relationship_Model(Bob).ppt

1/18

Entity-Relationship Model

SE 157A, Fall Semester 2006

Robert Correll


2/18

Entity-Relationship Model (chapter 6)

Basic Concepts

Constraints, Keys, and Design Issues

Entity-Relationship Diagram

Extended E-R Features

Design of an E-R Database Schema

Reduction of an E-R Schema to Tables Unified Modeling Language (UML)


3/18

Basic Concepts

Logical structure of database system

Requires iterative and incremental process

o Requirements, analysis, design,

o Inception, elaboration, construction, 1

Overall objective:

o

Minimize redundancy (space and updates)o Maximize effectiveness (the right product)


4/18

Constraints, Keys, and Design Issues

Job Search Activity System:

o A thorough record of events

o Helps in demonstrating competency

o Activity example: received automated reply

o Requirements specification


5/18

Requirements Specification

Track activities associated with job search:

o List companies or locations of a company

o List contacts per location or per job

o List jobs per zip code or per contact

o List activity per contact, company, or job

o

List follow-up dates per contacto List referrals (recursive relationship)


6/18

Entity-Relationship Diagram

Identify entity sets

Determine attributes

Analyze interactions between entities

Draw the E-R Diagram

Remember iterative and incremental process.


7/18

Identify Entity Sets

One job seeker

Activity

Company

Location

Job

Contact Pending follow-up date


8/18

Determine Attributes

location_id

- - - - - - - - street

location

city

zip_codestate

company_id co_name

company

stock_symbol

web_site last_search_date

Shows a derived attribute: last_search_date.


9/18

Determine Attributes (continued)

Shows composite attribute name, which includes the

attributes prefix, first_name, and last_name.

first_name

contact_id name

contact

rapor_leveltelephonetitle

last_nameprefix

emaillast_activity_date

my_job_id co_job_id

job

position_type sourceposition

job_status


10/18

Determine Attributes (continued)

activity_date activity_time

activity

commentactivity_code

activity_code description

activity_description

Each description represents a specific job search activity.

contact_id follow-up_date

follow-upjob_seeker


11/18

Activity Description Codes

1 Conducted search but did not apply

2 Applied on company website

3 E-mailed resume

4 Sent resume via regular mail

5 Received automated reply

6 Received personalized e-mail

7 Received voice mail message

8 Received call and spoke over phone

9 Left contact voice mail message

10 Called contact and spoke over phone

11 Had on-site interview


12/18

Analyze Interactions

company_id co_name

company

stock_symbol


location_id

- - - - - - - -street

location

city

zip_codestate

company_location

Weak entity set with discriminator


13/18

Extended E-R Features

Specialization: superclass-subclass (ISA)

Generalization: entity sets share attributes

Attribute inheritance: higher- and lower-level

Aggregation: back-to-back relation sets


14/18

Design of an E-R Database Schema for a

Job Search Activity System

my_job_id

co_job_id

job

position_type

source

position

job_status

activity_code description

activity_description

contact_id

follow-up_date

follow-up

job_contact

eventlooking

activity_date

activity_time

activity

comment

activity_code

my_job_id

job_seeker

company_id co_name

company

stock_symbol


location_id

- - - - - - - -

street

location

city

zip_code

state

company_location

referred_by

first_name

contact_id

name

contact

rapor_level telephonetitle

last_nameprefix

email

last_actiity_date


15/18

Open Design Issues

Should location_id values begin with number1 for each company?

Job_type: regular, internship, contract,

unsolicited Job_status: hot, warm, cold, filled, not

qualified

More job entity attributes: job_spec,target_date

Job_contact entity: contact_id, my_job_id


16/18

Reduction of an E-R Schema to Tables

Company = (company_id, co_name, web_site, stock_symbol)

company_id co_name web_site stock_symbol

1 Cisco www.cisco.com CSCO

2 Symantec www.symantec.com SYMC

3 Intel www.intel.com INTC
http://www.cisco.com/http://www.symantec.com/http://www.intel.com/http://www.intel.com/http://www.symantec.com/http://www.cisco.com/


17/18

Unified Modeling Language (UML)

Cardinality constraints are presented

differently between ERD and UML:

o ERD

o UML

E1 E2

E1 E2

R0..*

0..*0..1

0..1

R


18/18

References

1. Arlow, J., UML 2 and the Unified Process,Second Edition, 2005.

2. Kroenke D., Database Concepts, Second

Edition, 2005.3. Silberschatz, A., Korth H., Sudarshan, S.

Database System Concepts, Fifth Edition,2005

4. Silberschatz, A., Korth H., Sudarshan, S:Database System Concepts, Fourth Edition,2002

26fentity_relationship_model(bob).ppt

Documents