1 / 16

The Entity-Relationship Model

The Entity-Relationship Model. Part I. Database Design Stages. Application Requirements. Conceptual Design. Conceptual Schema. Logical Design. Logical Schema. Physical Design. Physical Schema. Conceptual Design. What is Conceptual Design?

minya
Download Presentation

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. The Entity-Relationship Model Part I. CS3431

  2. Database Design Stages Application Requirements Conceptual Design Conceptual Schema Logical Design Logical Schema Physical Design Physical Schema CS3431

  3. Conceptual Design • What is Conceptual Design? • Concise representation of our DB application requirements • Why Conceptual Design ? • It helps us to understand application requirements better • It helps us to communicate our understanding of application • It helps us to come up with a ‘good’ design CS3431

  4. Conceptual Design • Conceptual Models • ER (Entity Relationship) Model, • UML (Unified Modeling Language), • ORM (Object Role Modeling), etc • ER Model • Structures: entities and relationships • Constraints • An ER schema is represented as • an ER diagram. CS3431

  5. ER: Entity Types and Attributes • Entity: “Object” • Entity Type: “Class” • Attribute: property of an entity, has a domain • In ER diagrams • Entity Type  rectangle • Attribute  Oval. Entity Type Student with attributes (sNumber, sName, sAge) CS3431

  6. ER Example • Consider DB instance with 3 students : (1, Joe, 21), (2, Mary, 20), (3, Emily, 20) CS3431

  7. ER Model: Complex Attributes Composite Attribute: address Multivalued Attribute: major Student entity type with all its attributes CS3431

  8. ER Model: Relationship Types • Relationship: Association between entities • Relationship Type: Class of relationships • Representation: Use a diamond shape Relationship type HasTaken to represent Courses taken by Students CS3431

  9. ER Model: Relationship Types with Attributes Relationship HasTaken has an attribute project which is the project the Student did for the Course CS3431

  10. Example: Relationship Instances • students {Hong, Song}, • courses {DB1, DB2}, and • relationships {(Hong, DB1 : 98), (Song, DB1 : 99), (Hong, DB2 : 97)} CS3431

  11. Example : Relationship types Example : Suppliers have a name and an address. Products have a type and a default price. Consumers have a name and a telephone number. Some Suppliers have established contracts to supply a certain Product to a particular Consumer for specially negotiated price at a given quantity. How would you model this ? CS3431

  12. More relationship types Model the relationship Supplier supplies Products to Consumers Could we make two binary (or three binary) relationships instead? CS3431

  13. Binary vs. Ternary Relationships • What about following binary relationships : • S “can-supply” P, • C “needs” P, and • C “deals-with” S • No combination of binary relationships is an adequate substitute: • Together 3 binary relationships don’t imply that C has agreed to buy P from S. • Also, how could we record qty and price? CS3431

  14. Recursive Relationship Types Model: All parts have a part number and name. Some parts contain other parts as subcomponents, with a certain quantity. For instance: Both bikes and wheels are parts that we sell. Yet, a bike contains two wheels. CS3431

  15. Recursive Relationship Types and Roles Recursive relationship type :Part-Subpart Roles: There are Parts that play the role of superPart There are Parts that play the role of subPart CS3431

  16. ER Model so far • Structures • Entity Types • Relationship Types • Binary, ternary, n-ary • Recursive (roles) • Attributes • For entity types and relationship types • Simple, composite, multi-valued • Roles CS3431

More Related