How to read a one-to-many relationship in both directions

database-designerd

I'm having issues with reading this relationship.

Professor to Class one-to-many relationship

Let's take the Professor and Class entities in this example. If I read this from Professor to Class, I would say, "A Professor teaches one to many Classes". If I go the opposite direction, from Class to Professor, I would initially read this as "A Class can be taught by one and only one Professor".

Is this the correct way to describe those relationships in both directions? Or, rather, is the direction from Class to Professor a "many to one" relationship?

I think I may be mixing up two different things (cardinality and relationships), but I'm not entirely sure.

Source

This screenshot was taken from California State University, Bakersfield's ERD PowerPoint

Best Answer

There are a lot of different graphical conventions for relationships in a data model. See some examples in Wikipedia: Cardinalities.

The vastly most common way to represent cardinality in a diagram is to have the symbol beside an entity type represent the cardinality in the direction of that entity type. In other words, the symbol meaning many is at the many end of the relationship.

Specifically in your example: Each PROFESSOR teaches one or more CLASSES and each CLASS is taught by exactly one PROFESSOR.

Very rarely I've seen people do this backwards (e.g. (min, max)-notation), but if you do that you're going to confuse most people.

Related Solutions

When to use a multidimensional relationship in an ERD

I'm going to share some terminology with you that may help in your research.

The relationship you pictured is what is called a "ternary" relationship. It involves three entities. The simple relationships we usually deal with involve only two entities, and could be called "binary". There are "quaternary" relatinships, and so on. The general term is "n-ary".

You may also want to look up the following: Star schema, Ralph Kimball, Data warehousing, and multidimensional data model. A star schema is basically the intersection of relational tables and multidimensional modeling. There's too much here for me to do the subject justice. Happy hunting!

Why would I need a third table in a one-to-many relationship

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting.

In the article A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations (and tables). In Fig.8f (on the p 13 of the pdf) there is the following example for an 1:n relationship:

Each engineer can have at most one secretary. One secretary could work for several engineers.

There is a relationship "works-for" between the entities "secreatry" and "engineer".

A "secretary" "works-for" an "engineer".
Each "engineer" has at most one "secretary" that "works-for" her. It may be that an "engineer" has not "secretary" that "works-for" her.
A "secretary" can "work-for" one, more or no "engeneer"

In the article the authors propose to represent this as

ENGINEER(*EMP-NO,...,SEC-EMP-NO)
SECRETAY(*EMP-NO,...)

the fields marked by * are keys. The field SEC-EMP-NO referenes a EMP-NO from SECRETARY and is nullabel.

But there are authors that have the opinon that null values should not be allowed in relations. If one wants to avoid null values one needs a third relation to create the following model:

ENGINEER(*EMP-NO,...)
SECRETAY(*EMP-NO,...)
WORKS-FOR(*ENG-EMP-NO,SEC-EMP-NO)

ENG-EMP-NO references an EMP-NO in ENGINEER and SEC-EMP-NO references an EMP-NO in SECRETARY.

If an "engineer" always has a "secretary" that "works-for" her then the first representation with two relations does not contain null values.

Edit: I now found the comment in your post that links to the diagrams (http://i.imgur.com/gIG2D3h.jpg) of your professor: Actually he introduces a third table to avoid null values as described here.

Best Answer

Related Solutions

When to use a multidimensional relationship in an ERD

Why would I need a third table in a one-to-many relationship

Related Question