02 entity-relationship_model.pdf - CMPE 138/180B Database...

This preview shows page 1 - 11 out of 56 pages.

CMPE 138/180BDatabase System IEntity-Relationship (ER) ModelInstructor: Kong Li
Data modeling stepsEntity-Relationship DiagramEntity: type, set (instance)Attribute: composite, multi-valued, derived, keyRelationship: type, set (instance), attribute, rolesParticipationCardinalityWeak Entity: partial key, identifying relationshipRefining the ER Design for the COMPANY DatabaseER Diagrams, Naming Conventions, and Design IssuesOther Notations: (min, max) and UML Class DiagramsRelationship Types of Degree Higher than Two2Outline
Steps for Database Design3data flow diagrams, sequence diagrams, scenarios, etc.Software engineeringOut of scope
Steps for Database Design (cont’d)Requirements collection and AnalysisInput: Talk to the right people to collect/analyze requirementsOutput: data and functional requirementsConceptual designInput: data requirementsMap data requirements to Entity Relationship Diagram (ERD)Output: conceptual schema - ERDsLogical designInput: conceptual schema - ERDsMap ERDs to DB tables and constraints (DDL)Output: logical schema (DBMS specific)Physical designInternal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files specified4
Why is Data Modeling ImportantLeverageSmall change to data model may have big impact on the systemWell-designed data model significant savings in total costPoor data modeling expensive to fixConcisenessImplicitly define a whole set of how to retrieve/update/delete data, what can be done, and what cannot be doneData qualityCapture “valid” data, based on business requirementsData format, e.g., mm/dd/yyyy or dd/mm/yyyyData type, e.g., integer, string, etc.Integrity constraintsQ: how to define US zip code?54-digit year good enough?
Sample database: COMPANYMiniworld:Purpose: whyActors: whoroles: whatinteractions with other actors: how, where, when, whomOperations: data requirements, functional requirementsData requirements for the company miniworldCompany: multiple departments, employees, projectsDepartmentUnique name, unique number One head of the department (employee), w/ start dateSeveral locationsMusthave one or more employeesMaycontrol certain projects6
Sample database: COMPANYProjectUnique name, unique numberMustbe controlled by a single departmentSingle locationMusthave one or more workers (employees)EmployeeName, ssn (unique), address, salary, gender, b-dayMustwork for a single departmentMustwork on one or more projects (controlled by own or other departments)Number of hours/week/projectMayreport to a supervisor (another employee)Maysupervise a few employeesMayhave dependentsEach dependent: first name (unique for a given employee only), gender, b-day, relationshipMaybe manager (manage up to one department)7
ER Diagram of COMPANY8
Notation of ER Diagram Peter Chen 9
ER ModelER model describes data asEntitiesObjectsAttributesProperties of objectsRelationshipsData association among objects10

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture