1 / 48

Chapter 3 The Entity-Relationship Model

Chapter 3 The Entity-Relationship Model. Conceptual Data Modeling. SDLC Revisited – Data Modeling is an Analysis Activity. Project Identification and Selection. Purpose –thorough analysis Deliverable – functional system specifications. Project Initiation and Planning. Analysis.

odelia
Download Presentation

Chapter 3 The Entity-Relationship Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 3The Entity-Relationship Model Conceptual Data Modeling

  2. SDLC Revisited – Data Modeling is an Analysis Activity Project Identification and Selection Purpose –thorough analysis Deliverable – functional system specifications Project Initiation and Planning Analysis Logical Design Physical Design Database activity – conceptual data modeling communication between DB designer & user Implementation Maintenance

  3. Business Rules • Statements that define or constrain some aspect of the business • Assert business structure • Control/influence business behavior • Expressed in terms familiar to end users • Automated through DBMS software

  4. A Good Business Rule is: • Declarative – what, not how • Precise – clear, agreed-upon meaning • Atomic – one statement • Consistent – internally and externally • Expressible – structured, natural language • Distinct – non-redundant • Business-oriented – understood by business people

  5. Sample E-R Diagram

  6. E-R Model Constructs • Entity • Person: customer, vendor, supplier • Place: location, city, region, territory • Object: vehicle, monitor, part • Event: order, registration, renewal • Concept: expense, flight, project • Entity Type versus Entity Instance • Entity Type versus System Input, Output, or User

  7. Basic E-R Notation A special entity that is also a relationship Entity symbols Attribute symbols Relationship symbols

  8. What Should an Entity Be? • SHOULD BE: • An object that will have many instances in the database • An object that will be composed of multiple attributes • An object that we are trying to model • SHOULD NOT BE: • A user of the database system • An output of the database system (e.g. a report)

  9. System output System user Appropriate entities Inappropriate entities

  10. Attributes • Attribute - property or characteristic of an entity type • Classifications of attributes: • Simple versus Composite Attribute • Single-Valued versus Multivalued Attribute • Derived Attributes • Identifier Attributes

  11. A composite attribute

  12. Multivalued: an employee can have more than one skill Derived from date employed and current date Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed)

  13. E-R Model Constructs • Identifier or Key • An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type. • Simple Key versus Composite Key • Candidate Key • an attribute that could be a key…satisfies the requirements for being a key

  14. The key is underlined Simple key attribute

  15. The key is composed of two subparts Composite key attribute

  16. Criteria for Selecting Identifiers (Keys) • Will not change in value. • Will not be null. • No intelligent identifiers • e.g. containing locations or people that might change • Substitute new, simple keys for long, composite keys.

  17. Strong vs. Weak Entities, andIdentifying Relationships • Strong entities • exist independently of other types of entities • has its own unique identifier • represented with single-line rectangle • Weak entity • dependent on a strong entity…cannot exist on its own • Does not have a unique identifier • represented with double-line rectangle • Identifying relationship • links strong entities to weak entities • represented with double line diamond

  18. Example of a weak entity

  19. Basic E-R Notation

  20. Relationships • Relationship Types vs. Relationship Instances • The relationship type is modeled as the diamond and lines between entity types • Relationships can have attributes • These describe features pertaining to the association between the entities in the relationship • Two entities can have more than one type of relationship between them (multiple relationships)

  21. Relationship type (Completes)

  22. Relationship instances

  23. Attribute on a relationship

  24. Associative Entity • Its an Entity – It’s a Relationship • When should a relationship be an Associative Entity? • All relationships involved are “many” • Associative entity has independent meaning • Has one or more non-key attributes • Independent relationships

  25. An associative entity (CERTIFICATE)

  26. Degree of Relationships • Degree of a Relationship - number of entity types that participate in it. • Unary (or Recursive) Relationship • Binary • Ternary

  27. One entity related to another of the same entity type Entities of two different types related to each other Entities of three different types related to each other Degree of relationships

  28. Binary relationships

  29. Unary relationships

  30. A unary relationship with an attribute This has a many-to-many relationship Represents a bill-of -materials structure

  31. An Associative EntityBill of Materials Structure

  32. Ternary relationships

  33. A ternary relationship as an associative entity

  34. Multivalued attribute vs. relationship. Alternative approaches

  35. Cardinality of Relationships • One – to – One • Each entity in the relationship will have exactly one related entity • One – to – Many • An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity • Many – to – Many • Entities on both sides of the relationship can have many related entities on the other side

  36. Cardinality Constraints • The number of instances of one entity that can or must be associated with each instance of another entity • Minimum Cardinality • If zero, then optional • If one or more, then mandatory • Maximum Cardinality • Mandatory One - when min & max both = 1

  37. Cardinality notation

  38. Cardinality constraints Mandatory cardinalities

  39. Cardinality constraints One optional, one mandatory cardinality

  40. Optional cardinalities with unary degree, one-to-one relationship

  41. Cardinality constraints in a ternary relationship

  42. Modeling Time-Dependent Data • Time Stamps

  43. Multiple Relationships • More than one relationship between the same entity types

  44. Multiple relationship with fixed cardinality constraint

  45. E-R Diagram for Pine Valley

  46. Pine Valley Furniture User View 1: Orders for customers

  47. Pine Valley FurnitureUser View 2: Orders for products

  48. Synonyms you should know… Entity = relation = table Attribute = column Instance = row columns table rows

More Related