1University of TorontoDepartment of Computer Science© Easterbrook 20041This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Lecture 14:Entity Relationship Modelling The Entity-Relationship Model Entities Relationships Attributes Constraining the instances Cardinalities Identifiers GeneralizationUniversity of TorontoDepartment of Computer Science© Easterbrook 20042This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999The 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© Easterbrook 20043This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999ExamplesUniversity of TorontoDepartment of Computer Science© Easterbrook 20044This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Example Instances for ExamExam2University of TorontoDepartment of Computer Science© Easterbrook 20045This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999MeetsCourseRoomCourse instances Room instancesMeets instancesWhat Does An E-R Diagram ReallyMean? Course and Room are entities. Their instances are particular courses (eg CSC340F) and rooms (eg MB128) Meets is a relationship. Its instances describe particular meetings. Each meeting has exactly one associated course and roomUniversity of TorontoDepartment of Computer Science© Easterbrook 20046This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Recursive Relationships an entity can haverelationships with itself… If the relationship is notsymmetric… …need to indicate the two roles thatthe entity plays in the relationship.University of TorontoDepartment of Computer Science© Easterbrook 20047This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Ternary RelationshipsUniversity of TorontoDepartment of Computer Science© Easterbrook 20048This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999ContainsOrderPartRequestsServiceXORFilledByOrderShipmentGeneratesInvoiceAND“Each Ordereither contains apart or requestsa service, but notboth”“For any given order,whenever there is atleast one invoicethere is also at leastone shipmentand vice versa”AND/XOR Relationships3University of TorontoDepartment of Computer Science© Easterbrook 20049This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Attributes 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.University of TorontoDepartment of Computer Science© Easterbrook 200410This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Composite Attributes These group attributes of the same entity or relationship thathave closely connected meanings or uses.University of TorontoDepartment of Computer Science© Easterbrook 200411This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Schema with AttributesUniversity of TorontoDepartment of Computer Science© Easterbrook 200412This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Cardinalities 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.4University of TorontoDepartment of Computer Science© Easterbrook 200413This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999MeetsCourseRoom(2,2)Day(0,40)(0,N)“A coursemeets twicea week”“A room canhave up to40 meetingsper week”“A day canhave anunlimitednumber ofmeetings”Cardinality ExampleUniversity of TorontoDepartment of Computer Science© Easterbrook 200414This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999MeetsCourseRoom(2,2)(0,40)Instantiating ER diagrams An ER diagram specifies what states are possible inthe world being modeledUniversity of TorontoDepartment of Computer Science© Easterbrook 200415This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999MeetsCourseRoom(2,2)(0,40)Illegal InstantiationsUniversity of TorontoDepartment of Computer Science© Easterbrook 200416This lecture adapted from chapter 5 of Atzeni et al, “Database Systems” McGraw Hill, 1999Cardinalities of Attributes Attributes can also havecardinalities To describe the minimum andmaximum number of values of theattribute associated with eachinstance of an entity or arelationship. The default is (1,1) Optional attributes have cardinality(0,1) Multi-valued attributecardinalities are problematic Usually better modelled with additionalentities linked by one-to-many (or many-to-many) relationshipsPersonOwnsCarSurnameLicense#Registration#(0,N)(1,1)5University of
View Full Document
Unlocking...