University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 1Lecture 12:Entity Relationship Modelling The Entity-Relationship Model Entities Relationships Attributes Constraining the instances Cardinalities Identifiers GeneralizationUniversity of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 2The 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.University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 3ExamplesAdapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 4Example Instances for ExamExamAdapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 5MeetsCourseRoomCourse instancesCourse instancesRoom instancesRoom instancesMeets instancesMeets instancesWhat Does An ER Diagram Really Mean? 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 roomAdapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 6Recursive Relationships An entity can haverelationships with itself… If the relationship is notsymmetric… …need to indicate the two roles thatthe entity plays in the relationship.Adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 7Ternary RelationshipsAdapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 8““Each OrderEach Ordereither contains aeither contains apart or requestspart or requestsa service, but nota service, but notbothboth””““For any given order,For any given order,whenever there is atwhenever there is atleast one invoiceleast one invoicethere is also at leastthere is also at leastone shipmentone shipmentand vice versaand vice versa””AND/XOR RelationshipsUniversity of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 9Attributes associates with each instance of an entity (or relationship) avalue belonging to a set (the domain of the attribute). The domain determines the admissible values for the attribute.Adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 10Composite Attributes These group attributes of the same entity or relationship thathave closely connected meanings or uses.Adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 11Schema with AttributesAdapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 12Cardinalities Cardinalities constrain participation in relationships maximum and minimum number of relationship instances in which an entityinstance 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 singleinstance of the relationship If b=“N” then each instance of the entity is associated with an arbitrarynumber of instances of the relationship.Adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999University of TorontoDepartment of Computer Science© 2004-5 Steve Easterbrook. This presentation is available free for non-commercial use with attribution under a creative commons license. 13MeetsCourseRoom(2,2)Day(0,40)(0,N)““A courseA coursemeets twicemeets twicea weeka week””““A room canA room canhave up tohave up
View Full Document
Unlocking...