Databases Reference
In-Depth Information
Name
Product
Price
Product ID
Figure 4-3. The ER diagram representation of the product entity
tion, it's possible that we could stock different products with the same name and price.
For example, we could sell two models of “Four-port USB 2.0 Hub,” both at $4.95
each. To distinguish between products, we can assign a unique product ID number to
each item we stock; this would be the primary key. Each product entity would
have name, price, and product ID attributes. This is shown in the ER diagram in
Figure 4-3.
Representing Relationships
Entities can participate in relationships with other entities. For example, a customer
can buy a product, a student can take a course, an artist can record an album, and so on.
Like entities, relationships can have attributes: we can define a sale to be a relationship
between a customer entity (identified by the unique email address) and a given number
of the product entity (identified by the unique product ID) that exists at a particular
date and time (the timestamp).
Our database could then record each sale and tell us, for example, that at 3:13 p.m. on
Wednesday, March 22, Ali Thomson bought one “Four-port USB 2.0 Hub,” one “300
GB 16 MB Cache 7200 rpm SATA Serial ATA133 HDD Hard Disk,” and two sets of
“2000 Watt 5.1 Channel Sub-Woofer Speakers.”
Different numbers of entities can appear on each side of a relationship. For example,
each customer can buy any number of products, and each product can be bought by
any number of customers. This is known as a
many-to-many
relationship. We can also
have
one-to-many
relationships. For example, one person can have several credit cards,
but each credit card belongs to just one person. Looking at it the other way, a
one-to-
many
relationship becomes a
many-to-one
relationship; for example, many credit cards
belong to a single person. Finally, the serial number on a car engine is an example of a
one-to-one
relationship; each engine has just one serial number, and each serial number
belongs to just one engine. We often use the shorthand terms 1:1, 1:N, and M:N for
one-to-one, one-to-many, and many-to-many relationships, respectively.
The number of entities on either side of a relationship (the
cardinality
of the relation-
ship) define the
key constraints
of the relationship. It's important to think about the
cardinality of relationships carefully. There are many relationships that may at first
seem to be one-to-one, but turn out to be more complex. For example, people some-
