Entity-Relationship Model SE 157 A Fall Semester 2006

Entity-Relationship Model SE 157 A, Fall Semester 2006 Robert Correll

Entity-Relationship Model (chapter 6) n n n n 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)

Basic Concepts n n o o Logical structure of database system Requires iterative and incremental process Requirements, analysis, design, … “Inception, elaboration, construction, …” 1 Overall objective: Minimize redundancy (space and updates) Maximize effectiveness (the right product)

Constraints, Keys, and Design Issues n o o Job Search Activity System: A thorough record of events Helps in demonstrating competency Activity example: received automated reply Requirements specification

Requirements Specification n o o o Track activities associated with job search: List companies or locations of a company List contacts per location or per job List jobs per zip code or per contact List activity per contact, company, or job List follow-up dates per contact List referrals (recursive relationship)

Entity-Relationship Diagram n n n Identify entity sets Determine attributes Analyze interactions between entities Draw the E-R Diagram Remember iterative and incremental process.

Identify Entity Sets n n n n One job seeker Activity Company Location Job Contact Pending follow-up date

Determine Attributes company_id co_name location_id ---- stock_symbol city last_search_date web_site street state company zip_code location Shows a derived attribute: last_search_date.

Determine Attributes (continued) prefix my_job_id position co_job_id position_type first_name contact_id source title last_name rapor_level telephone job_status last_activity_date job email contact Shows composite attribute name, which includes the attributes prefix, first_name, and last_name.

Determine Attributes (continued) activity_date activity_code activity_time activity_code description comment activity_description activity job_seeker contact_id follow-up_date follow-up Each description represents a specific job search activity.

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

Analyze Interactions company_id co_name location_id ---- stock_symbol street city last_search_date web_site state company_location zip_code location Weak entity set with discriminator

Extended E-R Features n n Specialization: superclass-subclass (ISA) Generalization: entity sets share attributes Attribute inheritance: higher- and lower-level Aggregation: back-to-back relation sets

Design of an E-R Database Schema for a Job Search Activity System position_type company_id co_name stock_symbol my_job_id street company co_job_id state city last_search_date web_site source location_id ---- company_location job_status zip_code job_contact location job_seeker referred_by looking activity event contact email contact_id comment follow-up activity_description contact_id follow-up_date activity_time activity_date rapor_level telephone name prefix first_name last_actiity_date title my_job_id activity_code description last_name

Open Design Issues n n n Should location_id values begin with number 1 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

Reduction of an E-R Schema to Tables n 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

Unified Modeling Language (UML) n o Cardinality constraints are presented differently between ERD and UML: ERD E 1 o 0. . * R 0. . 1 E 2 UML E 1 0. . 1 R 0. . * E 2

References 1. 2. 3. 4. Arlow, J. , UML 2 and the Unified Process, Second Edition, 2005. Kroenke D. , Database Concepts, Second Edition, 2005. Silberschatz, A. , Korth H. , Sudarshan, S. Database System Concepts, Fifth Edition, 2005 Silberschatz, A. , Korth H. , Sudarshan, S: Database System Concepts, Fourth Edition, 2002