University of Toronto Department of Computer Science Lecture 14 Entity Relationship Modelling The Entity Relationship Model Entities Relationships Attributes Constraining the instances Cardinalities Identifiers Generalization 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 1 University of Toronto Department of Computer Science The Entity Relationship Model Entity Relationship Schema Describes data requirements for a new information system Direct easy to understand graphical notation Translates readily to relational schema for database design But more abstract than relational schema E g can represent an entity without knowing its properties comparable to UML class diagrams Entities classes of objects with properties in common and an autonomous existence E g City Department Employee Purchase and Sale An instance of an entity is an object in the class represented by the entity E g Stockholm Helsinki are examples of instances of the entity City Relationships logical links between two or more entities E g Residence is a relationship that can exist between the City and Employee An instance of a relationship is an n tuple of instances of entities E g the pair Johanssen Stockholm is an instance in the relationship Residence 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 2 Department of Computer Science University of Toronto Examples Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 3 Department of Computer Science University of Toronto Example Instances for Exam Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 Exam 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 4 Department of Computer Science University of Toronto What Does An ER Diagram Really Mean Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 Course Meets Room Course and Room are entities Their instances are particular courses eg CSC340F and rooms eg MS2172 Meets is a relationship Its instances describe particular meetings Each meeting has exactly one associated course and room Course instances Meets instances Room instances 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 5 University of Toronto Department of Computer Science Recursive Relationships Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 An entity can have relationships with itself If the relationship is not symmetric need to indicate the two roles that the entity plays in the relationship 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 6 University of Toronto Department of Computer Science Ternary Relationships Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 7 University of Toronto Department of Computer Science AND XOR Relationships Each Order either contains a part or requests a service but not both For any given order whenever there is at least one invoice there is also at least one shipment and vice versa 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 8 Department of Computer Science University of Toronto Attributes Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 associates with each instance of an entity or relationship a value belonging to a set the domain of the attribute The domain determines the admissible values for the attribute 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 9 University of Toronto Department of Computer Science Composite Attributes Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 These group attributes of the same entity or relationship that have closely connected meanings or uses 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 10 University of Toronto Department of Computer Science Schema with Attributes Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 11 Department of Computer Science University of Toronto Cardinalities Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 Cardinalities constrain participation in relationships maximum and minimum number of relationship instances in which an entity instance can participate E g cardinality is any pair of non negative integers a b such that a b If a 0 then entity participation in a relationship is optional If a 1 then entity participation in a relationship is mandatory If b 1 each instance of the entity is associated at most with a single instance of the relationship If b N then each instance of the entity is associated with an arbitrary number of instances of the relationship 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 12 Department of Computer Science University of Toronto Cardinality Example Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 A course meets twice a week Course 2 2 A day can have an unlimited number of meetings Meets 0 N 0 40 Room A room can have up to 40 meetings per week Day 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative commons license 13 Department of Computer Science University of Toronto Instantiating ER diagrams Adapted from chapter 5 of Atzeni et al Database Systems McGraw Hill 1999 An ER diagram specifies what states are possible in the world being modeled Course 2 2 Meets 0 40 Room 2004 5 Steve Easterbrook This presentation is available free for non commercial use with attribution under a creative
View Full Document
Unlocking...