1

Relationships

• Relationships will have justification in business rules, in the way the enterprise manages its business. The following figure illustrates an enroll in relationship.

Our business rules state that students enrol in courses,and that courses have students enrolled in them.

Student enroll in Course

2

There are several ways of classifying relationships, according to the

degree, cardinality, participation constraint (mandatory or optional),whether recursion is involved, andwhether or not relationship is identifying.

We consider the degree as the number of entities that participate in therelationship. When we speak of a student enrolling in a course, we arediscussing a relationship, enroll in, where two entity sets (STUDENTand COURSE) are involved; the relationship is of degree 2 because eachinstance of enroll in will always involve one student entity and onecourse entity.

3

binary, ternary, or n-ary relationships Our primary focus is on binary relationships. However, inthe section on Dimensional Modeling we will see thatn-ary relationships are commonplace. Consider the enroll in relationship again, and considerquestions like:         Must a course have students enrolled in it?         Must a student enroll in a course? These questions focus on the participation requirementof an entity set in a relationship.

enroll in CourseStudent

4

Student enroll in Course

The single relationship line means that ‘enroll in’ is optionalto a Course. A course can exist and have zero studentsenrolled. The double line means that ‘enroll in’ is mandatoryfor Student: each Student must be enrolled in at least oneCourse.

5

Cardinality is a constraint on a relationship specifying thenumber of entity instances that a specific entity may berelated to via the relationship.

InvLine specify Product

An Invoice Line will specify exactly one Product.A Product may appear on any number, zero or more,Invoice Lines.

N 1

6

One-to-One RelationshipsOne-to-one relationships have “1” specified for bothcardinalities, and do not arise very often.

Diver operates Vehicle1 1

One-to-Many Relationships

Invoice CustomerN 1received

7

Many-to-Many RelationshipsMany-to-many relationships have “N” and “M” specified focardinalities, and are also very common.  However, should you examine a data model in some business,there is a good chance you will not see any many-to-manyrelationships on the diagram. In those cases, the data modelerhas resolved the many-to-many relationships into twoone-to-many relationships.

Student enroll in CourseN M

This many-to-many relationship implies there may be more than one Studententity for each Course entity, and that there may be more than one Courseentity for each Student entity.

8

As will be discussed again later in the text, a many-to-manyrelationship is implemented in a relational database using itsown relation.  Because of this, many modelers will resolve a many-to-manyinto two one-to-many relationships in their diagrams.  So, for this situation we could redraw the above many-to-manyas two one-to-many relationships, as shown in the figure below.

EnrollmentregisterN M

Has enrollments

Student Course

1 1

9

Recursive RelationshipsIf an entity has a relationship with another entity of the sameentity set, then we have a recursive relationship. Of the relationship types, these are the most difficult to master.  Some situations where recursive relationships can be used:• An employee supervises other employees• A person marries another person• A person is a child of a person• A course is a prerequisite for another course• A team plays against another team• Organizational units report to other organizational units • A bill-of-materials system, where a part is composed of

other parts.

10

Recursion in a data model is an especially difficult topic.We have a recursive relationship if the same entity setappears more than once in a relationship.

Employee supervisessuperviser

supervised1

N

Any instance of this relationship involves two employees,and so it is a recursive relationship. In this relationship,one employee is designated the ‘supervisor’ and the otheras the ‘supervised’. Employee fills two roles in this relationship.

11

Previously, we briefly examined the first case above:“An employee supervises another employee”.

An instance of the supervises relationship involves twoentities from the same entity set. See the following figure,where five examples of the supervise relationship aredepicted in a reporting hierarchy.

John

Lee Peter Noel

Don Mary

The reporting hierarchy informs us:John does not have supervisor John supervises LeeJohn supervises PeterJohn supervises NoelLee does not supervise anyoneNoel does not supervise anyonePeter supervises DonPeter supervises MaryDon does not supervise anyoneMary does not supervise anyone

12

Employee supervisessuperviser

supervised1

N

ERD for the supervises relationship. Optionalityspecifications allow for:

at least one employee does not have a supervisorsome employees do not supervise others

13

Now consider the second example listed above: “A personmarries another person”. You must be certain of the businessrules that are valid for the miniworld you are concerned with.Depending on that miniworld, you model marries as1:1, 1:m, or m:n. Is the relationship “A person marriesanother person”, 1:1, 1:m, or m:n?

What is the cardinality of this relationship?1:1 ?1:m ?m:n ?

Person marries1

1

14

If you say 1:1, then only the current marriage for people is of interest inyour miniworld. A recursive 1:1 relationship allows two entities of thesame entity set to be related, but a given entity can only be related toone other entity. Consider the following figure, where we illustrate 5persons, and the relationship set showing two marriages involving twopeople each. The fifth person is not related to another person throughthis relationship.

Does the model allow for a person marrying themselves? Yes, it does,but that would be against the ‘rules’. To ensure that no one accidentallyrecords such an instance in your database, you would need supportingcode in the database to ensure it doesn’t occur.

15

Person

personIdperName

perFirstName

perInitials

perLastName

perGender

A person can be represented using the following diagram.

16

Since marries is an association between two people (two instances of Person) we understand marries is a recursive relationship

Person

personIdperName

perFirstName

perInitials

perLastName

perGender

marries

Since we are only interested in someone’s current marriage partner, we understand that a person is associated with at most one other person via marries

11

17

John

April

LeeAlex

Amelia

Each person is involvedin no more than one‘marries’ relationship

Entity SetPerson

Relationship Setmarries

18

If you said m:n, then you are tracking of all marriages, past and present,for people in your miniworld; you are allowing for people to remarry andyou are keeping track of all their marriages. The following figure showsJohn and Amelia being involved in two marries relationships. A datumthat is recorded for marriages, is the date the ceremony occurred. Wherewould that be kept in the model? We will address that soon, in the sectionon attributes of relationships.

John

April

LeeAlex

Amelia

Two of these entities areinvolved in more than one‘marries’ relationship.

Entity SetPerson

Relationship Setmarries

19

Since marries is an association between two people (two instances of Person) we understand marries is a recursive relationship

Person

personIdperName

perFirstName

perInitials

perLastName

perGender

marries

Since we are only interested in someone’s current marriage partner, we understand that a person is associated with at most one other person via marries

MN

20

Identifying Relationships

If an entity is existence-dependent on another entity, then itis a weak entity. Examples where this may occur are:

- an invoice line is existence-dependent on an invoice- a dependent is existence-dependent on an employee- a section is existence-dependent on a course.

21

In these cases: we have a discriminator that differentiatesthe lines on the same invoice from one another, thedependents of the same employee from one another, andthe courses within the same department. In these cases, theprimary key of the weak entity needs two components: theprimary key of the strong entity and the discriminator of theweak entity. Because the primary key of the strong entity isrequired to uniquely identify a weak entity, we say therelationship is an identifying relationship, and we use adouble lined relationship symbol. Note that one entity set isa weak entity set, and its participation in the relationship ismandatory

22

Invoice Line InvoiceN 1

•An invoice line is partly identified by the Invoiceand its invoice number).•An invoice line must participate in the relationship.•The invoice line is a weak entity.•Consider the relationship between Course and Section

LineNum InvNum

belongs-to

23

Attributes associated with relationshipsAttributes describe entities, and sometimes attributesdescribe aspects of relationships too. Consider the m:nrelationship marries. Useful attributes for marries are thedate the ceremony occurs, the location of the ceremony,and the name of the person officiating the marriage.

Person marriesM

N

MarDate

MarLocn

MarOfficial

24

Exercise: Consider the figures on page 7 and 8 again.Suppose we have attributes for the date the student registeredfor a course, EnrDateRegistered, and for the grade, EnrGrade,the student receives. Redraw the ERD in the figure on page 7with attributes for the relationship. In the figure on page 8,the many-to-many was resolved into two one-to-manyrelationships. Redraw the ERD with these attribute properlyplaced.

25

Entity or RelationshipWhen your model includes a many-to-many relationship, you have achoice to show it as a relationship or to modify the diagram to haveanother entity set and a pair of relationships. For instance, the marriesrelationship above could be redrawn with a marriage entity set; considerthe following:

Person

Marries ashusband1

1

MarDate

MarLocn

MarOfficial

marries

Marries aswife

N

Name

Birthdate

M

26

A convention some modelling tools and designers use isshown below. In this case, they are using a composite entityset (the relationship symbol is enclosed in the entity setsymbol). Their intention is to show that marries is both a relationship and an entity set. We will not be usingthis notation.

Person marriesM

N

MarDate

MarLocn

MarOfficial

We will avoid using this convention.

27

Organizational hierarchies

• Organizational hierarchies are very common.

• Most businesses have an inherent hierarchical structure: one component of the business is made from other components.

28

ExampleA national company may have regional offices and eachregion comprises several branch offices. Consider thefollowing that shows part of the hierarchy for a hypotheticalvideo rental company.

VRental

East Coast Prairie

Halifax Montreal Prince Alberta Saskatoon Winnipeg

29

The diagram shows two of possibly many regions thecompany is divided into.

•The East Coast region has two branches: Halifax andMontreal.

•The Prairie region has three branches. Knowing that we have a company divided into regions thatare divided into branches, could lead us to the followingER model:

Company

Region

Branch

A company comprises regions,which comprises branches

30

The above illustrates the business rules very clearly, but atthe same time it can be considered a very rigid structure.  Consider: •If the structure of the company changes (perhaps we nowhave Divisions between regions and branches), we cannotaccommodate such a change in the model as it stands – we

need to change the model to allow for another entity set. •These kinds of changes are serious in the sense thatsignificant work may be required in the database and/orapplications, or to other documentation related to thesystem in question.

•Another approach is to use a more generic and flexiblemodel.

31

Suppose we consider the company to comprise organizationalunits, and so we can consider a new model of our requirements:

Comprises

1

m

OrganizationUnit

32

In this new view of our requirements, we now considerWinnipeg Branch, Halifax Branch, Prairie Region, etc. to beinstances of Organizational Unit. Now, if our organizationalstructure changes to include Divisions, we will just have anew instance of organizational unit.  We gain flexibility with this approach, but we also need toensure that when we build relationships between two entities,the two entities are of the proper type.  Exercise: Consider geographic units such as country,state/province, city, … . How would you model geographicunits? Give examples of some instances.