PowerPoint PresentationSlide 2Data Modeling Using the Entity-Relationship (ER) ModelUsing High-Level Conceptual Data Models for Database DesignUsing High-Level Conceptual Data Models (cont’d.)Slide 6A Sample Database ApplicationSlide 8Entity Types, Entity Sets, Attributes, and KeysEntities and AttributesEntities and Attributes (cont’d.)Entity Types, Entity Sets, Keys, and Value SetsEntity Types, Entity Sets, Keys, and Value Sets (cont’d.)Initial Conceptual Design of the COMPANY DatabaseRelationship Types, Relationship Sets, Roles, and Structural ConstraintsRelationship DegreeSlide 17Slide 18Constraints on Binary Relationship TypesWeak Entity TypesRefining the ER Design for the COMPANY DatabaseER Diagrams, Naming Conventions, and Design IssuesProper Naming of Schema ConstructsSlide 24Alternative Notations for ER DiagramsSlide 26Example of Other Notation: UML Class DiagramsSlide 28Example of Other Notation: UML Class Diagrams (cont’d.)Slide 30SummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 7Data Modeling Using the Entity-Relationship (ER) ModelCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheData Modeling Using theEntity-Relationship (ER) ModelEntity-Relationship (ER) modelPopular high-level conceptual data modelER diagramsDiagrammatic notation associated with the ER modelUnified Modeling Language (UML)Copyright © 2011 Ramez Elmasri and Shamkant NavatheUsing High-Level Conceptual Data Models for Database DesignRequirements collection and analysisDatabase designers interview prospective database users to understand and document data requirementsResult: data requirementsFunctional requirements of the applicationCopyright © 2011 Ramez Elmasri and Shamkant NavatheUsing High-Level Conceptual Data Models (cont’d.)Conceptual schema Conceptual designDescription of data requirementsIncludes detailed descriptions of the entity types, relationships, and constraintsTransformed from high-level data model into implementation data modelCopyright © 2011 Ramez Elmasri and Shamkant NavatheUsing High-Level Conceptual Data Models (cont’d.)Logical design or data model mappingResult is a database schema in implementation data model of DBMSPhysical design phaseInternal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files specifiedCopyright © 2011 Ramez Elmasri and Shamkant NavatheA Sample Database ApplicationCOMPANYEmployees, departments, and projectsCompany is organized into departmentsDepartment controls a number of projectsEmployee: store each employee’s name, Social Security number, address, salary, gender, and birth dateKeep track of the dependents of each employeeCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheEntity Types, Entity Sets, Attributes, and KeysER model describes data as: EntitiesRelationshipsAttributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheEntities and AttributesEntityThing in real world with independent existenceAttributesParticular properties that describe entityTypes of attributes:•Composite versus simple (atomic) attributes•Single-valued versus multivalued attributes•Stored versus derived attributes•Complex attributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheEntities and Attributes (cont’d.)Copyright © 2011 Ramez Elmasri and Shamkant NavatheEntity Types, Entity Sets, Keys, and Value SetsEntity type Collection (or set) of entities that have the same attributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheEntity Types, Entity Sets, Keys, and Value Sets (cont’d.)Key or uniqueness constraint Attributes whose values are distinct for each individual entity in entity setKey attribute•Uniqueness property must hold for every entity set of the entity typeCopyright © 2011 Ramez Elmasri and Shamkant NavatheInitial Conceptual Design of the COMPANY DatabaseCopyright © 2011 Ramez Elmasri and Shamkant NavatheRelationship Types, Relationship Sets, Roles, and Structural ConstraintsRelationshipWhen an attribute of one entity type refers to another entity typeRepresent references as relationships not attributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheRelationship DegreeDegree of a relationship type Number of participating entity typesBinary, ternaryCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheConstraints on Binary Relationship TypesCardinality ratio for a binary relationship Specifies maximum number of relationship instances that entity can participate inParticipation constraint Specifies whether existence of entity depends on its being related to another entityTypes: total and partialCopyright © 2011 Ramez Elmasri and Shamkant NavatheWeak Entity TypesDo not have key attributes of their ownIdentified by being related to specific entities from another entity typeIdentifying relationshipRelates a weak entity type to its owner Always has a total participation constraintCopyright © 2011 Ramez Elmasri and Shamkant NavatheRefining the ER Design for the COMPANY DatabaseChange attributes that represent relationships into relationship typesDetermine cardinality ratio and participation constraint of each relationship typeCopyright © 2011 Ramez Elmasri and Shamkant NavatheER Diagrams, Naming Conventions, and Design IssuesCopyright © 2011 Ramez Elmasri and Shamkant NavatheProper Naming of Schema ConstructsChoose names that convey meanings attached to different constructs in schemaNouns give rise to entity type namesVerbs indicate names of relationship typesChoose binary relationship names to make ER diagram readable from left to right and from top to bottomCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheAlternative Notations for ER DiagramsSpecify structural constraints on relationshipsReplaces cardinality ratio (1:1, 1:N, M:N) and single/double line notation for participation constraintsAssociate a pair of integer numbers (min, max) with each participation of an entity type E in a relationship type R, where 0 ≤ min ≤ max and max ≥ 1Copyright © 2011 Ramez
View Full Document
Unlocking...