1 / 22

The Entity-Relationship Model

The Entity-Relationship Model. Part II. Database Design Stages. Application Requirements. Conceptual Design. Conceptual Schema. Logical Design. Logical Schema. Physical Design. Physical Schema. Relationship types in ER Model.

carpenterjames
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 II. CS3431

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

  3. Relationship types in ER Model 3-ary “supplies” relationship: Supplier supplies Products to Consumers CS3431

  4. 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

  5. ER Model so far • Structures • Entity Types • Relationship Types • Binary, ternary, n-ary • Recursive • Attributes • For entity types and relationship types • Simple, composite, multivalued • Roles CS3431

  6. ER Model: Key Constraints How : Underline the key attribute/attributes Key for Student is sNumber Key for Movie is <title, year> Note: We can represent key for entity type consisting of more than 1 attribute (e.g.: Movie) We cannot represent multiple keys for an entity type (e.g: key for Student can be either sNumber or sName) CS3431

  7. ER Model: Cardinality Constraints How : Expressed using (min, max) Student can take >= 2 and <= 3 Courses Course can have >= 0 and <= * (infinity) Students min and max are non-negative integers max > min CS3431

  8. Cardinality Constraints 1:1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept pNumber dNumber Manages Person Dept pName dName CS3431

  9. Cardinality Constraints 1:1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept CS3431

  10. pNumber dNumber Works Person Dept For pName dName Cardinality Constraints 1:many relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons CS3431

  11. Cardinality Constraints 1:many relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons CS3431

  12. pNumber dNumber (0, 1) (1, 1) Manages Person Dept pName dName pNumber dNumber Works Person Dept For pName dName (1, 1) (0, *) Cardinality Constraints 1:1 relationship type: A Dept has exactly one Manager, A Person can manage at most one Dept 1:many (1:n) relationship type: A Person works for exactly one Dept, A Dept can have any number of Persons CS3431

  13. Cardinality Constraints pNumber dNumber Works Person Dept For pName dName many:many (m:n) relationship type: A Person works for one or more Depts, A Dept can have any number of Persons CS3431

  14. Cardinality Constraints many:many (m:n) relationship type: A Person works for one or more Depts, A Dept can have any number of Persons CS3431

  15. Cardinality Constraints for n-ary relationships pNumber pName Product sName cName Supply Supplier Consumer sLoc cLoc price qty A Supplier supplies at least oneProduct to some Consumer CS3431

  16. Cardinality Constraints for n-ary relationships A Supplier supplies at least one Product to some Consumer CS3431

  17. Cardinality Constraints for n-ary relationships What about the following constraints : (1) A Consumer gets a Product from only one Supplier (2) Each Supplier supplies exactly 2 Products CS3431

  18. pNumber pName Part superPart subPart Contains quantity Cardinality Constraints for Recursive Relationships A Part can be subpart of one superPart A Part can have many subParts CS3431

  19. Cardinality Constraints for Recursive Relationships A Part can be subpart of one superPart A Part can have many subParts CS3431

  20. Cardinality Constraints for Recursive Relationships A Part can be subpart of one superPart A Part can have many subParts A Part can be subpart of many superParts A Part can have many subParts CS3431

  21. ER Model Constraints Summary • Key Constraints • Cardinality Constraints • Expressed using (min, max) • Binary relationship types are called 1:1, 1:many, many:many CS3431

  22. Summary of ER • Structures • Entity Types • Relationship types – binary, ternary, n-ary. recursive • Attributes • For entity types or relationship types • Simple, composite or multi-valued • Constraints – key, cardinality • Roles of entity types in a relationship type • ISA relationship types • Weak Entity Types – identifying relationship type, identifying entity type CS3431

More Related