Database Reference
In-Depth Information
own identifier. No one ever says, “Give me the data for line 12.” Instead, they say, “Give me the data
for line 12 of order 12345.” Hence, the identifier of a line is a composite of the identifier of a particular
line and the identifier of a particular order. Thus, entries for line items are always ID-dependent on
the order in which they appear. In Figure 5-33, ORDER_LINE_ITEM is ID-dependent on SALES_
ORDER. The identifier of the ORDER_LINE_ITEM entity is (SalesOrderNumber, LineNumber).
Now, and this is the part that is sometimes confusing for some students, ORDER_ LINE_
ITEM is not existence dependent on ITEM. It can exist even if no item has yet been assigned to
it. Further, if an ITEM is deleted, we do not want the line item to be deleted with it. The dele-
tion of an ITEM may make the value of ItemNumber and other data invalid, but it should not
cause the line item itself to disappear.
Now consider what happens to a line item when an order is deleted. Unlike with the de-
letion of an item, which only causes data items to become invalid, the deletion of the order
removes the existence of the line item. Logically, a line item cannot exist if its order is deleted.
Hence, line items are existence dependent on orders.
Work through each of the relationships in Figure 5-33 and ensure that you understand
their type and their maximum and minimum cardinalities. Also understand the implications
of this data model. For example, do you see why this sales order data model is unlikely to be
used by a company in which salespeople are on commission?
Other Mixed Patterns
Mixed identifying and nonidentifying relationships occur frequently. Learn to look for a mixed
pattern when a strong entity has a multivalued composite group and when one of the ele-
ments in the composite group is an identifier of a second strong entity.
Consider, for example, baking recipes. Each recipe calls for a certain amount of a specific
ingredient, such as flour, sugar, or butter. The ingredient list is a multivalued composite group,
but one of the elements of that group, the name of the ingredient, is the identifier of a strong
entity. As shown in Figure 5-34, the recipe and the ingredients are strong entities, but the
amount and instructions for using each ingredient are ID-dependent on RECIPE.
Or consider employees' skill proficiencies. The name of the skill and the proficiency level
the employee has are a multivalued group, but the skill itself is a strong entity, as shown in
Figure 5-35. Dozens of other examples are possible.
Before continuing, compare the models in Figures 5-33, 5-34, and 5-35 with the association
pattern in Figure 5-22. Make sure that you understand the differences and why the model in Figure
5-22 has two identifying relationships and the models in Figures 5-33, 5-34, and 5-35 have just one.
The For-Use-By Pattern
As stated earlier in this chapter, the major reason for using subtypes in a database design is to
avoid value-inappropriate nulls. Some forms suggest the possibility of such nulls when they
show blocks of data fields that are grayed out and labeled “For Use by
someone/something
RECIPE
INGREDIENT
Figure 5-34
Mixed Relationship Pattern
for Baking Recipes
RecipeName
IngredientName
Description
NumberServed
Description
AmountOnHand
StorageLocation
INGREDIENT_USE
RecipeName
IngredientNumber
Amount
Instructions
Search WWH ::
Custom Search