Microsoft® SQL Server® 2012 Bible

(Ben Green) #1

152


Part II: Building Databases and Working with Data


FIGURE 7-2
A one-to-many relationship consists of a primary entity and a secondary entity. The
secondary entity’s foreign key points to the primary entity’s primary key. In this case,
the Sales.SalesOrderDetail’s SalesOrderID is the foreign key that relates to Sales
.SalesOrderHeader’s primary key.

If the database was not properly normalized, you would see the order information for a
specifi c order repeated for each order detail associated with that order. You can see several
examples of primary keys and foreign keys in the “Data Design Patterns” section later in
this chapter.

Cardinality
The cardinality of the relationship describes the number of tuples (rows) on each side of
the relationship. Either side of the relationship may be restricted to allow zero, one, or mul-
tiple tuples.

The type of key enforces the restriction of multiple tuples. Primary keys are by defi nition
unique and enforce the single-tuple restriction, whereas foreign keys permit multiple
tuples.

There are several possible cardinality combinations, as shown in Table 7-2. Within this sec-
tion, each of the cardinality possibilities is examined in detail.

c07.indd 152c07.indd 152 7/30/2012 4:18:11 PM7/30/2012 4:18:11 PM


http://www.it-ebooks.info
Free download pdf