Database Reference
In-Depth Information
Figure 11.1. Tables from the Bel Air High School and Lake City High School data-
bases
Both schools are at the same stage of the database design process and are currently estab-
lishing business rules. As it turns out, each school is using the following business rule in
their respective databases:
A student cannot have more than two instruments checked out at
the same time.
This business rule applies to the
degree of participation
between the STUDENTS table
andSTUDENTINSTRUMENTStable.Inthisinstance,asinglerecordintheSTUDENTS
table cannot be associated with more than two records in the STUDENT INSTRUMENTS
table where the value of C
HECK
-I
N
D
ATE
for each record is null; a null value in the C
HECK
-
I
N
D
ATE
field indicates that the instrument is still in the student's possession.
The rule
does
apply to both schools, yet each school requires it for a different reason. Bel
Air High School requires the rule because of the manner in which its music program has
been established, whereas Lake City High School requires the constraint because of the
physical limitations of its instrument inventory. The fact that both schools developed an
identical rule is pure coincidence. This example illustrates that a business rule is, indeed,
based on the way an organization functions or conducts its business and demonstrates why
every organization must have its own specific set of business rules.
The example also illustrates another issue: You cannot establish constraints imposed by
certain business rules, such as this one, within the logical design of the database. For in-
stance, there is no clear way for you to indicate that the C
HECK
-I
N
D
ATE
values must be
tested in order to determine whether a student can check out another instrument. You must
