Sql Excel : What Is an Entity-Relationship Diagram?

The “relational” in the name “relational databases” refers to the fact that different
tables relate to each other via keys, and to the fact that columns in a given row
relate to the values for that column via the column name. For instance, a zip code
column in any table can link (that is “relate”) to the zip code table. The key makes
it possible to look up information available in the zip code table. Figure 1.1 shows
the relationships between tables in the purchases dataset
These relationships have a characteristic called cardinality, which is the number
of items related on each side. For instance, the relationship between Orders and
ZipCensus is a zero/one-to-many relationship. This specifies every row in Orders
has at most one zip code. And, every zip code has zero, one, or more orders.
Typically, this relationship is implemented by having a column in the first table
contain the zip code, which is called a foreign key. A foreign key is just a column
whose contents are the primary key of another table (ZipCode in Orders is a foreign
key; zcta5 in ZipCensus is a primary key). To indicate no match, the foreign key
column would typically be NULL.
The zero/one-to-one relationship says that there is at most one match between
two tables. This is often a subsetting relationship. For instance, a database might
contain sessions of web visits, some of which result in a purchase. Any given
session would have zero or one purchases. Any given purchase would have exactly
one session.
Another relationship is a many-to-many relationship. A customer might purchase
many different products and any given product might be purchased by many
different customers. In fact, the purchase dataset does have a many-to-many
relationship between Orders and Products; this relationship is represented by the
OrderLines entity, which has a zero/one-to-many relationship with each of those.
An example of the one-at-a-time relationship is a customer who resides in a
particular zip code. The customer might move over time. Or, at any given time, a
customer might have a particular handset or billing plan, but these can change
over time.
With this brief introduction to entity-relationship diagrams, the following sections
describe the datasets used in this book.