13/10/20171 Lecture 3 Data Modelling with Entity-Relationship Model 13/10/20172 Part 4 – Objectives How to use Entity–Relationship (ER) modelingin database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ERmodel using Unified Modeling Language (UML). How to build an ER model from a requirementsspecification. 4A data model is a plan, or blueprint, for … Continue reading “Data Modelling with Entity-Relationship Model | My Assignment Tutor”
13/10/20171 Lecture 3 Data Modelling with Entity-Relationship Model 13/10/20172 Part 4 – Objectives How to use Entity–Relationship (ER) modelingin database design. Basic concepts associated with ER model. Diagrammatic technique for displaying ERmodel using Unified Modeling Language (UML). How to build an ER model from a requirementsspecification. 4A data model is a plan, or blueprint, for adatabase design.A data model is more generalized andabstract than a database design.It is easier to change a data model than itis to change a database design, so it is theappropriate place to work throughconceptual database problems.The Data Model 13/10/20173 Concepts of the ER Model Entity types Relationship types Attributes Entity-Relationship model is a set of concepts and graphicalsymbols that can be used to create conceptual schemas. Versions– Original E-R model — Peter Chen (1976).– Extended E-R model — Extensions to the Chen model.– Information Engineering (IE) — James Martin (1990); it uses “crow’sfoot” notation, is easier to understand and we will use it.– Unified Modeling Language (UML) — The Object Management Group; itsupports object-oriented methodologyE-R Model 13/10/20174 Something that can be identified and the users want totrack. It can be an object, a concept, or person thatusers are interested in recording information about. Examples:-Person, Account, Product, etc.– Entity type/class — a collection of entities of a given type orGroup of objects with same properties, identified byenterprise as having an independent existence.– Entity instance — the occurrence of a particularentity/Uniquely identifiable object of an entity type. There are usually many instances of an entity in anentity class.Entities Examples of Entity Types 13/10/20175 CUSTOMER- The Entity Class and Two EntityInstances 10 Attributes describe an entity’s characteristics. Examples of attributes on Customer entity class are :-Cust_no, cust_name, Cust_Dob, etc. All entity instances of a given entity class have thesame attributes, but vary in the values of thoseattributes. Originally shown in data models as ellipses. Data modeling products today commonly showattributes in rectangular form.Attributes 13/10/20176 11EMPLOYEE: Attributes in Ellipses 12EMPLOYEE: Attributes in Entity Rectangle 13/10/20177 13 Identifiers are attributes that name, or identify, entity instances. The identifier of an entity instance consists of one or more of theentity’s attributes. Composite identifiers: Identifiers that consist of two or moreattributes Identifiers in data models become keys in database designs:– Entities have identifiers.– Tables (or relations) have keys.Identifiers 14Entity Attribute Display in Data Models 13/10/20178 15 Entities can be associated with one another in relationships:– Relationship classes: associations among entity classes– Relationship instances: associations among entity instances In the original E-R model, relationships could have attributes buttoday this is no longer done. A relationship class can involve two or more entity classes.Relationships Relationship Types Relationship type– Set of meaningful associations among entitytypes. Relationship occurrence– Uniquely identifiable association, whichincludes one occurrence from eachparticipating entity type. 13/10/20179 17 Entity: is modeled as asquare Relationship: is modeledas a link between the twoentities. Attribute: is modeled as aoval.Entity Relationship Notation 18ID NameAddressEntity Relationship: Example Student CourseCourse13/10/201710 19•Define the relationship:•has to be a verb•Recommended to be one word•has to be clearThis reads like this: A student enrolls in a course. A coursehas students.Entity Relationship: Student CourseEnrollshas 20 The degree of a relationship is the number of entity classesparticipating in the relationship:– One entity class has a unary or recursive relationship of degreeone.– Two entity classes have a binary relationship of degree two.– Three entities have a ternary relationship of degree three.Degree of a Relationship 13/10/201711 A recursive relationship occurs when an entity has arelationship to itself Relationship type where same entity typeparticipates more than once in different rolesMarriesManagesRecursive Relationships PersonEmployee 22Binary Relationship 13/10/201712 23Ternary Relationship Quaternary relationship called Arranges 13/10/201713 The principle difference between an entity anda table (relation in a relational database) is thatyou can express a relationship between entitieswithout using foreign keys.This makes it easier to work with entities in theearly design process where the very existenceof entities and the relationships between them isuncertain.Entities and Tables Attributes Attribute– Property of an entity or a relationship type. Attribute Domain– Set of allowable values for one or more attributes. Simple Attribute– Attribute composed of a single component with anindependent existence. Composite Attribute– Attribute composed of multiple components, eachwith an independent existence. 13/10/201714 Attributes Single-valued Attribute– Attribute that holds a single value for each occurrenceof an entity type. Multi-valued Attribute– Attribute that holds multiple values for each occurrenceof an entity type. Derived Attribute– Attribute that represents a value that is derivable fromvalue of a related attribute, or set of attributes, notnecessarily in the same entity type. Keys Candidate Key– Minimal set of attributes that uniquelyidentifies each occurrence of an entity type. Primary Key– Candidate key selected to uniquely identifyeach occurrence of an entity type. Composite Key– A candidate key that consists of two or moreattributes. 13/10/201715 Structural ConstraintsMultiplicity is made up of two types of restrictionson relationships: cardinality and participation.Cardinality– Describes maximum number of possiblerelationship occurrences for an entityparticipating in a given relationship type.Participation– Determines whether all or only some entityoccurrences participate in a relationship. Multiplicity as cardinality and participationconstraints 13/10/201716 31Cardinality means “count,” and is expressed asa number.Maximum cardinality (Cardinality) is thehighest number of entity instances that canparticipate in a relationship.Minimum cardinality (Optionality) is thesmallest number of entity instances that mustparticipate in a relationship.Cardinality & Optionality 32 Maximum cardinality is the highest number of entity instances thatcan participate in a relationship. There are three types of maximum cardinality:– One-to-One [1:1]– One-to-Many [1:N]– Many-to-Many [N:M]Maximum Cardinality 13/10/201717 33The Three Types ofMaximum Cardinality 34 In a one-to-many relationship:– The entity on the one side of the relationship is called the parent entityor just the parent.– The entity on the many side of the relationship is called the child entityor just the child. In the figure below, EMPLOYEE is the parent and COMPUTER is thechild:Parent and Child Entities 13/10/201718 35The relationships we have been discussing areknown as HAS-A relationships:– Each entity instance has a relationship withanother entity instance:»An EMPLOYEE has one or moreCOMPUTERs.»A COMPUTER has an assignedEMPLOYEE.HAS-A Relationships 36 Minimum cardinality is the minimum number ofentity instances that must participate in arelationship. Minimums are generally stated as either zero or one:– IF zero [0] THEN participation in the relationship by theentity is optional, and no entity instance must participate inthe relationship.– IF one [1] THEN participation in the relationship by theentity is mandatory, and at least one entity instance mustparticipate in the relationship.Minimum Cardinality(Optionality) 13/10/201719 37 As shown in the examples in a following slide:– Minimum cardinality of zero [0] indicating optionalparticipation is indicated by placing an oval next to the optionalentity.– Minimum cardinality of one [1] indicating mandatory(required) participation is indicated by placing a vertical hashmark next to the required entity.Indicating Minimum Cardinality 38 Look toward the entity in question:– IF you see an oval THEN that entity is optional (minimumcardinality of zero [0]).– IF you see a vertical hash mark THEN that entity is mandatory(required) (minimum cardinality of one [ 1]).Reading Minimum Cardinality 13/10/201720 39The Three Types ofMinimum Cardinality 40Data Modeling Notation 13/10/201721 41Data Modeling Notation:ERwin 42 A Student can enroll in one Course at a time. A Course can have one to many Students at atime. A Lecturer can teach in zero to many Courses ata time. A Course would have one or more Lecturesteaching in that course at anytime.Lets look at some examples: EntityConstraint13/10/201722 43 one one to many zero to many(Min One, Max One)(Min One, Max Many)(Min Zero, Max Many)Constraints: AAA 44Optionality specifies the minimum number ofinstances of the related entity.Cardinality specifies the maximum number ofinstances of the related entity.E-R: Optionality and Cardinality A BCardinatlityOptionality13/10/201723 Lets see how it is implemented: A Student can enroll in one Course at a time only. A Course can have one to many Students at a time. A Lecturer can teach in zero to many Courses at a time. A Course would have one or more Lectures teaching at it atanytime. Student CourseLecturer 46 Note that: (1) ERwin cannot indicate trueminimum cardinalities on N:Mrelationships (2) Visio introduces theintersection table instead ofusing a true N:M modelData Modeling Notation:N:M and O-M 13/10/201724 47 An ID-dependent entity is an entity (child) whose identifierincludes the identifier of another entity (parent). The ID-dependent entity is a logical extension or sub-unit of theparent:– BUILDING : APARTMENT– PAINTING : PRINT The minimum cardinality from the ID-dependent entity to the parentis always one.ID-Dependent Entities A solid lineindicates anidentifyingrelationshipID-Dependent Entities 13/10/201725 Entity Type Strong Entity Type– Entity type that is not existence-dependenton some other entity type. Weak Entity Type– Entity type that is existence-dependent onsome other entity type. Strong entity type called Client and weak entity typecalled Preference 13/10/201726 51 A weak entity is an entity whose exisitence depends upon anotherentity. All ID-Dependent entities are considered weak. But there are also non-ID-dependent weak entities.– The identifier of the parent does not appear in the identifier of theweak child entity.Weak Entities Weak entitiesmust beindicated by anaccompanyingtext box inErwin – There isno specificnotation for anonidentifyingbut weak entityrelationshipA dashed lineindicates anonidentifyingrelationshipWeak Entities (Continued) 13/10/201727 53ID-Dependent and Weak Entities A subtype entity is a special case of asupertype entity:– STUDENT :UNDERGRADUATE orGRADUATEThe supertype contains all common attributes,while the subtypes contain specific attributes.The supertype may have a discriminatorattribute that indicates the subtype.Subtype Entities 13/10/201728 Subtypes with a Discriminator 56 If subtypes are exclusive, one supertype relates to at most onesubtype. If subtypes are inclusive, one supertype can relate to one or moresubtypes. Other database texts refer to exclusive subtypes as being disjoint. Inclusive subtypes are also known as overlaps.Subtypes: Exclusive (Disjoint)or Inclusive (Overlap) 13/10/201729 57Subtypes: Exclusive orInclusive (Continued) Relationships connecting supertypes and subtypes are called IS-Arelationships, because a subtype IS A supertype. The identifer of the supertype and all of its subtypes must beidentical, i.e., the identifier of the supertype becomes the identifierof the related subtype(s). Subtypes are used to avoid value-inappropriate nulls.Subtypes: IS-A relationships
