Database Reference
In-Depth Information
tion using database objects such as constraints and keys. However, RI is
documented in the logical model to ensure that business rules (as well as
general data consistency) are followed within the database.
Suppose you are designing a database that stores information about the
inventory of a library. In the logical model, you might have an Author en-
tity, a Publisher entity, and a Title entity, among many others. Any given
author may have more than one title in the inventory; in contrast, a title
probably has been published by only one publisher, although one publisher
may have published many titles. If users need to remove an author, simply
deleting that author would leave at least one title orphaned. Similarly,
deleting a publisher would leave at least one title orphaned.
Thus, you need to create definitions of the actions that are enforced
when these updates occur. Referential integrity provides these definitions.
With RI in place, you can specify that when an author is deleted, all related
titles are also deleted. You could also specify that the addition of a title fails
when there is no corresponding author. These might not be the most real-
istic examples, but they clearly illustrate the need to handle the interrela-
tion between data in multiple entities.
You document referential integrity in the logical model via PK and FK
relationships. Because each entity should have a key attribute that
uniquely identifies each record the entity contains, you can relate key at-
tributes in parent and child entities based on those keys. For example, take
a look at Figure 2.1.
F
IGURE
2.1
Primary key and foreign key
This example shows a basic relationship between two entities. After
creating the relationship, you specify in its definition any constraints on
data manipulation actions on the parent and child entities. For example,
you can specify that any INSERT into the child entity should fail if there
isn't already a parent instance with a matching primary key value. Similarly,
you could specify that any DELETE statement against the parent entity
Search WWH ::
Custom Search