03bRelationships

Databases are collections of related tables

How are tables related?

Entity-relationship modeling

The oldest and most prevalent way for humans to understand a database is to construct an entity-relationship model

The model guides us in creating a database

The model usually comes first but is revised as the database grows and matures

You have already seen an admittedly eccentric example of an entity-relationship model, the flights model in the video 02aTerms

There are two main ways that people usually draw entity-relationship models: Peter Chen’s notation (cumbersome and mostly used in school) and the other way, Crows-foot notation (mostly used in industry)

An entity-relationship diagram (Chen notation)

Crowsfoot notation

The above picture shows the crowsfoot notation for five cardinalities, from left to right: zero or one, exactly one, many (don’t use this one in this course!), zero or many, one or many. Always use the most restrictive applicable notation.

The main reason for crowsfoot notation is to be specific about cardinality—how many of one entity relate to how many of another entity.

Another entity-relationship diagram (Crowsfoot notation)

This picture shows a many-to-many relationship. In the associated table, a supplier may supply many parts and more than one suppier may supply the same part. Every supplier in our database supplies at least one part and every part is supplied by at least one supplier.

A different entity-relationship diagram (Crowsfoot notation)

No matter how inhumane we are, we can’t put an orca into more than one pool. One pool could contain many orcas but no orca will appear in more than one pool. That’s what this diagram says. Note that a relationship phrase above the line is read from left to right, while such a phrase below the line is read from right to left. Also note that a pool could be empty according to this diagram.

Yet another entity-relationship diagram

Again, we see a many-to-many relationship. Now we also see the attributes of the entity.

A more sophisticated entity-relationship diagram

Here I have created an associative entity that lets us overcome the many-to-many relationship.

A more complete entity-relationship diagram

Now I’ve added primary keys to all three entities and foreign keys to quantity.

A finished entity-relationship diagram

Now I’ve removed the many-to-many relationship and consider this a solution to the many-to-many relationship problem.

The preceding problem

Many-to-many relationships lead to anomalies in insertion, deletion, and updating of databases

Consequently, we look for them and try to remove them by adding associative entities—in this case, an event, a shipment of a quantity of parts

Note that city in the supplier entity may differ from city in the quantity entity, the latter being the location where we’ve received the shipment

On the other hand, pid and sid in the quantity entity are foreign keys, shown in an oblique font, uniquely describing one instance in each of the other entities

I’ve added names to the relationships and, since they’re above the line, they’re read from left to right.