Entity-Relationship ModelEntity-Relationship Model (chapter 6)Basic ConceptsConstraints, Keys, and Design IssuesRequirements SpecificationEntity-Relationship DiagramIdentify Entity SetsDetermine AttributesDetermine Attributes (continued)Slide 10Activity Description CodesAnalyze InteractionsExtended E-R FeaturesDesign of an E-R Database Schema for a Job Search Activity SystemOpen Design IssuesReduction of an E-R Schema to TablesUnified Modeling Language (UML)ReferencesEntity-Relationship ModelSE 157A, Fall Semester 2006Robert CorrellEntity-Relationship Model (chapter 6)Basic ConceptsConstraints, Keys, and Design IssuesEntity-Relationship DiagramExtended E-R FeaturesDesign of an E-R Database SchemaReduction of an E-R Schema to TablesUnified Modeling Language (UML)Basic ConceptsLogical structure of database systemRequires iterative and incremental processoRequirements, analysis, design, …o“Inception, elaboration, construction, …”1Overall objective:oMinimize redundancy (space and updates)oMaximize effectiveness (the right product)Constraints, Keys, and Design IssuesJob Search Activity System:oA thorough record of eventsoHelps in demonstrating competencyoActivity example: received automated replyoRequirements specificationRequirements SpecificationTrack activities associated with job search:oList companies or locations of a companyoList contacts per location or per joboList jobs per zip code or per contactoList activity per contact, company, or joboList follow-up dates per contactoList referrals (recursive relationship)Entity-Relationship DiagramIdentify entity setsDetermine attributesAnalyze interactions between entitiesDraw the E-R DiagramRemember iterative and incremental process.Identify Entity SetsOne job seekerActivityCompanyLocationJobContactPending follow-up dateDetermine Attributeslocation_id- - - - - - - -streetlocationcityzip_codestatecompany_id co_namecompanystock_symbolweb_sitelast_search_dateShows a derived attribute: last_search_date.Determine Attributes (continued)Shows composite attribute name, which includes the attributes prefix, first_name, and last_name.first_namecontact_id namecontactrapor_leveltelephonetitlelast_nameprefixemaillast_activity_datemy_job_id co_job_idjobposition_typesourcepositionjob_statusDetermine Attributes (continued)activity_date activity_timeactivitycommentactivity_codeactivity_code descriptionactivity_descriptionEach description represents a specific job search activity.contact_id follow-up_datefollow-upjob_seekerActivity Description Codes1 Conducted search but did not apply2 Applied on company website3 E-mailed resume 4 Sent resume via regular mail5 Received automated reply6 Received personalized e-mail7 Received voice mail message8 Received call and spoke over phone9 Left contact voice mail message10 Called contact and spoke over phone11 Had on-site interviewAnalyze Interactionscompany_id co_namecompanystock_symbolweb_sitelast_search_datelocation_id- - - - - - - -streetlocationcityzip_codestate company_locationWeak entity set with discriminatorExtended E-R FeaturesSpecialization: superclass-subclass (ISA)Generalization: entity sets share attributesAttribute inheritance: higher- and lower-levelAggregation: back-to-back relation setsDesign of an E-R Database Schema for aJob Search Activity System my_job_idco_job_idjobposition_typesourcepositionjob_statusactivity_code descriptionactivity_descriptioncontact_idfollow-up_datefollow-upjob_contacteventlookingactivity_dateactivity_timeactivitycommentactivity_codemy_job_idjob_seekercompany_id co_namecompanystock_symbolweb_sitelast_search_datelocation_id- - - - - - - -streetlocationcityzip_codestate company_locationreferred_byfirst_namecontact_idnamecontactrapor_level telephonetitlelast_nameprefixemaillast_actiity_dateOpen Design IssuesShould location_id values begin with number 1 for each company?Job_type: regular, internship, contract, unsolicitedJob_status: hot, warm, cold, filled, not qualifiedMore job entity attributes: job_spec, target_dateJob_contact entity: contact_id, my_job_idReduction of an E-R Schema to TablesCompany = (company_id, co_name, web_site, stock_symbol)company_id co_name web_site stock_symbol1 Cisco www.cisco.com CSCO2 Symantec www.symantec.com SYMC3 Intel www.intel.com INTCUnified Modeling Language (UML)Cardinality constraints are presented differently between ERD and UML:oERDoUMLE1 E2E1 E2R0..*0..*0..10..1RReferences1. 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, 20054. Silberschatz, A., Korth H., Sudarshan, S: Database System Concepts, Fourth Edition,
View Full Document