Modeling RelationshipsDefinition of RelationshipRelationshipsRelationships Are Identified Through SentencesRelationships RevealedRelationships DocumentationOne-to-One RelationshipsSlide 8Slide 9One-to-Many RelationshipsSlide 11Slide 12Some Relationships Suggest New EntitiesDefinition of HierarchyExamples of HierarchiesGeneralization & SpecializationAn Example of Generalization & SpecializationHiearchy May Simplify Data StructuresRules for Identifying HierarchiesSlide 20ConfusionSteps for Identifying a HierarchyStep two: DiscriminatorExampleStep Three: Relations at Super Entity LevelAdvantages of HierarchiesAn exampleInsert a new entity in the hierarchyNew Data StructureCheck Sub EntitiesCheck Super EntityCheck DiscriminatorHierarchiesMany to Many RelationshipsSlide 35Patient Clinician AssociationAssociation Class SimplifiesMany-to-many relationshipsBack to HierarchiesSelf ReferralTake Home LessonsModeling Relationships Modeling Relationships Farrokh Alemi, Ph.D.Updated by Janusz Wojtusiak, Fall 2009Definition of RelationshipDefinition of RelationshipRelationship describes the link between two entitiesDifferent words for the same idea –At the conceptual level (e.g. entities and their relationships) –At the physical level (tables, data classes and linkages)RelationshipsRelationshipsA relationship is ALWAYS between two entities.–If you have three entities, you need at least two relationships to link them.Relationships Are Identified Relationships Are Identified Through SentencesThrough SentencesA sentence containing the names of the two entities and a verb phrase in between. –Verb phrases: "has," "contains," "visits," "prescribes," "travels with," etc. –The two entities "Patient" and "Clinician" can be made into a sentence such as "A patient visits a clinician."Relationships RevealedRelationships RevealedIs one of the entities a look up table used to provide menu items for an attribute of the other entity. Is one of the entities a subcategory to another. Do two entities share an attribute.Relationships DocumentationRelationships DocumentationThe name of first entityThe name of second entityThe verb phrase describing the relationshipThe cardinality of the relationship–one to one, one to many or many to manyBusiness justification of the relationship, particularly its cardinality.One-to-One RelationshipsOne-to-One RelationshipsThis type of relationship often suggest that entities have been incorrectly designedDatabase designer must identify a good reason for using this type of relationshipOne-to-one relationships are used in specific situations, such as:–To avoid missing data–To model hierarchies–For privacy/security reason–To break very large tables if some fields are rarely accesses at the same time.One-to-One RelationshipsOne-to-One RelationshipsThis type of relationship in realized by using a common identifiers in both entities EmployeeEmployeeIDFirstNameLastName…EmployeeSecurityEmployeeIDDateObtainedSecurityLevel…One-to-One RelationshipsOne-to-One RelationshipsMore examples?Justification?One-to-Many RelationshipsOne-to-Many RelationshipsThis is the most important type of relationship in relational databasesThey are used to represent all types of relationships in relational databasesRelational databases are about one-to-many relationshipsMajority of relationships in databases are of this type.One-to-Many RelationshipsOne-to-Many RelationshipsOne-to-Many relationships are represented by listing an identifier of “one” in “many” EmployeeEmployeeIDFirstNameLastName…EmployeeAddressAddressIDEmployeeIDDateObtainedLevel…One-to-Many RelationshipsOne-to-Many RelationshipsMore examples?Some Relationships Suggest Some Relationships Suggest New EntitiesNew EntitiesHierarchical Many to many relationshipsDefinition of HierarchyDefinition of HierarchyA collection of super and sub entities.– A super entity is the broadest definition of several sub entities. –A sub entity is an entity that inherits its relationship from another entity.Examples of HierarchiesExamples of HierarchiesVehiclesOrganismsDrugsParts of a complex deviceAny other examples?Generalization & SpecializationGeneralization & SpecializationThe process of abstracting from narrowly scoped terms into terms of broader scope is a generalization process Starting with a general term and narrowing its scope is a specialization process.An Example of Generalization & An Example of Generalization & SpecializationSpecializationHiearchy May Simplify Data Hiearchy May Simplify Data StructuresStructuresSpecializations of the more general data class PersonOftentimes, introducing a subtype hierarchy can simplify the information modelRules for Identifying HierarchiesRules for Identifying HierarchiesSuper-type is the broadest entity, should have all the attributes that are shared across the sub-entities. Each sub-entity should have an attribute that makes it different from other entities. –The attributes should be mutually exclusive.Rules for Identifying HierarchiesRules for Identifying HierarchiesConfusionConfusionIt may not be possible for sub types to unambiguously reside in one and only one of the subtypes specified in the hierarchy. –Refine further–If confusing, do not do itSteps for Identifying a HierarchySteps for Identifying a Hierarchy1. Shared attributes 2. Discriminator attribute3. Entity linkages at the super type levelStep two: DiscriminatorStep two: DiscriminatorExampleExampleStep Three: Relations at Super Step Three: Relations at Super Entity LevelEntity LevelReduces unnecessary relations–Simpler to read–Easier to implementAdvantages of HierarchiesAdvantages of HierarchiesStabilize the overall model with respect to new requirements.An exampleAn exampleNew Requirement: “Patient of record” becomes inactive but the system does not purge itInsert a new entity in the Insert a new entity in the hierarchyhierarchyInclude the new entity in the discriminator attributeCheck that all sub entities are appropriately namedNew Data StructureNew Data StructureCheck Sub EntitiesCheck Sub EntitiesThe first issue is whether the sub entity is distinct form other entitiesCheck Super EntityCheck Super EntityDo the attributes in the super entity apply equally well to the new sub entityCheck DiscriminatorCheck DiscriminatorThe third issue is