ims 4212: introduction to data modeling—relationships 1 dr. lawrence west, management dept.,...

IMS 4212: Introduction to Data Modeling—Relationships

1Dr. Lawrence West, Management Dept., University of Central [email protected]

Relationships—Topics

• Goal of the Relational Data Model

• Introducing Relationships

• Relationship Notation Schemes

• Binary Relationships

• Relationship Cardinality & Notation

• Policy and Cardinality

• Recursive (Unary) Relationships

• Ternary Relationships

• Relationships with Attributes

• The Road Ahead



The Goal of the Relational Data Model

• Recall that entities hold data about one type of object or event of interest to the organization

• A goal of the relational data model is to minimize the amount of stored data subject to:

– Necessity to record data needed by the organization

– Necessity to maintain relationships



The Goal of the Relational Data Model (cont).

• Data minimization is achieved by removing any redundant data

– Achieved through good entity and relationship design

– Normalization

• Our approach

– Design properly normalized (minimal redundancy) data structures

– Selectively denormalize (introduce redundancy) to improve performance



The Goal of the Relational Data Model (cont).

• DB design results in many tables, sometimes for simple organizational needs

– Identifying entities from information requirements analysis

– Creating new entities to fix attribute-level and relationship problems (covered soon)

• Relationships between tables enable us to reconnect data that is dispersed into many tables



Relationships

• "A meaningful association between (or among) entities"

• What in the world does this mean?

• Relationships indicate how entities interact from the organization's perspective

• Relationships will end up defining paths through the database along which data will be retrieved

– The paths usually mirror real world associations between entities



Relationships (cont.)

• While entities are nouns relationships are verbs

– Buys, teaches, sells, owns, …

– Is a

– Has

• Relationship verb describes how two entities interact with each other

• If two entities do not interact (from the organization’s official viewpoint) then there is no relationship between them

– Professor ?? Football_Play

• ‘Direction’ of verb is not very important

Important special cases



Introducing Relationships

• Relationships are defined in three ways

– In data modeling by conceptually identifying and documenting the fact that two entities do relate to each other

– In data modeling by identifying shared attributes between the two entities

– In the physical database by implementing common attributes and declaring the relationship



Introducing Relationships (cont.)

• Relationships are the glue that connects different stored data in a way that meets the organization’s needs

• File-based vs. Relational systems

– In file-based systems each transaction record had all necessary data stored with it, including redundant copies of data

– In relational systems only data of a particular type is stored in each entity (table)—little redundancy

• Relationships allow the system to reconstruct the logic of a transaction



Introducing Relationships (cont.)

• We deal with relationships in three ways

– Modeling relationships as part of a process of discovery of the organization’s structure, etc

– Adapting or correcting the relationships we find into the form required for database implementation

– Implementing the relationships in the physical database

STUDENT CLASSTakes



Two Notation Schemes (Chen LDM)

STUDENT Takes CLASS

SSNLast

NameFirst

NameName

Entities are indicated by a box with the entity nameinside

Attributes are listed in ovalsattached to entities

Relationships are indicatedby diamonds

Relationships are connectedto entities by notation toindicate the cardinality ofthe relationship



Two Notation Schemes (Alternative LDM)

SSNLastNameFirstName

STUDENT

DepartmentNumberName

CLASSTakes

Entities shown as boxes

Entity name

Attributes

Relationship shown withoutthe diamond



Binary Relationships

• The most commonly found relationship is between two entities (binary)

STUDENT Takes CLASS


STUDENT


CLASSTakes

Entities EntitiesRelationship

Cardinality Cardinality

Chen Diagram

My Approach



Cardinality

• Understanding “Cardinality” is one of the most fundamentally important concepts in DB design

• Cardinality indicates how many occurrences of an entity must or may be allowed in the relationship with any one occurrence in the other entity

• Cardinality goes in each direction

– One student may/must take ? Classes

– One class must/may be taken by ? Students


STUDENT


CLASSTakes



Relationship Cardinality (cont.)

• The measure of cardinality has two components at each end of the relationship:

– A maximum (usually either 1 or an unconstrained number greater than one, referred to as “many”)

– A minimum (usually either 0 or 1 but other values are possible, though rare)

• Relationship is mandatory if at least one matching record is required (minimum is 1)

• Relationship is optional if a matching record is not required (minimum is 0)




STUDENT


CLASSTakes

Cardinality Notation

• Mandatory One

– One professor must have exactly one phone number

• Mandatory Many

– A customer must have at least one purchase to be a customer but may have many

• Optional One

– One professor may have as few as zero reserved parking spaces but may have only one at most

• Optional Many

– One student may take as few as zero classes but may take more than one class



Cardinality Notation (cont.)

• Interpret these cardinalities

SalesRep SaleMakes

Sale CustomerHas

TrafficCitation DriverHas

Employee OfficeHas




• Relationship cardinality is governed by the number of related occurrences you could have

– If a student could have two majors then relationship is ‘Many’ on the Major side

– May a car or house have more than one owner?

– May an Employee be assigned to more than one job title at a time?

– Will you record a Supplier if you do not currently carry any of their products?

– Will you enter an Employee without assigning them to a position?




• Commonly used verbal shorthand ignores the minimum component of a relationship

• 1:M (one-to-many)

• 1:1 (one-to-one)

• M:M (or M:N) (many-to-many)

Employee OfficeHas

Sale CustomerHas

Student MajorHas




• The graphical layout of a relationship is purely arbitrary

SaleCustomer Has

Sale CustomerHas

Sale

Customer

Has



Organization Policy and Cardinality

• Business policies (or regulations) may affect cardinality

• Identify legitimate business policies that support each of the different cardinality combinations reflected here

Sale CustomerHas

Sale CustomerHas

Sale CustomerHas



StoreEmployeeSaleProductEmployeeShiftDepartment



Unary Relationships

• Unary relationships are relationships between an entity and itself

– One employee supervises many other employees; eachemployee is supervisedby, at most, one otheremployee

– One part is a component ofmany other parts;One part (assembly)contains manyother parts

Employee Supervises

Part Is ComposedOf



Ternary Relationships

• A ternary relationship is one between three entities

• This relationship isfor modeling and discoveryonly

– Model the relationship the way the user describes it

– Recognize that there are problems with implementing this relationship

• Relationship will be decomposed into multiple binary relationships for the final ERD

– (What is the solution?)

Sells P roductSa lesR ep

C ustom er



Attributes on Relationships

• The modeling processwill sometimes produceattributes of relationships

• These also will be eliminatedin the final ERD

– (What is the solution?)

• Look for the missing entityand implement it now

S ells P roductS a lesR ep

D ate Q ty



Multiple Relationships

• Sometimes there can be two relationships between the same two entities

Sells

ProductCompany

Services



Implementing Relationships

• Relationships are implemented by sharing attributes between entities

• When the Identifier Attribute of one entity appears as an attribute in another entity set a relationship is established (whether you intended it or not)

• These shared identifier attributes are called foreign keys (more next time)

SSNLNameFName

Student

NumberSSNTagNo

TicketGets

IdentifierAttribute

SharedIdentifierAttribute



Problem Relationships

• Ternary relationships, attributes on relationships, multiple relationships, and Many-to-Many relationships all have serious implementation problems

• What are they?

• What does the nature of the problem tell us about what we should do to fix it?



Next Time

• Parent and Child Relationships

• Strong & Weak Entities

• Time- and Space-Dependent Data

• Supertype/Subtype entity sets

ims 4212: introduction to data modeling—relationships 1 dr. lawrence west, management dept.,...

Documents