Database Reference
In-Depth Information
When the DBMS cannot enforce these restrictions, the appropriate place for the enforcement to take
place is in the programs written to access the data in the database. Thus, the responsibility for this enforce-
ment should fall on the programmers who write these programs. Incidentally, users must update the data
through these programs and not through the built-in features of the DBMS in such circumstances; otherwise,
the users would be able to bypass all the controls you are attempting to program into the system.
To enforce restrictions, programmers must include logic in their programs. With respect to the DBDL for
the Employee table, this means the following:
1. Before an employee is added, the program should determine and process three restrictions:
a. Determine whether an employee with the same EmployeeNum is already in the database. If
so, the program should reject the update.
b. Determine whether an employee with the same Social Security number is already in the
database. If so, the program should reject the update.
c. Determine that the inputted department number matches a department number that is
already in the database; if it does not, the program should reject the update.
2. When a user changes the department number of an existing employee, the program should
check to make sure the new number matches a department number that is already in the data-
base. If it does not, the program should reject the update.
3. When a user deletes a department number, the program should verify that no employees work in
the department. If the employees do work in the department and the program allows the dele-
tion of the department, these employees will have invalid department numbers. In this case, the
program should reject the update.
202
Programs must perform these verifications efficiently; in most systems, this means the database adminis-
trator will create indexes for each column (or combination of columns) that is a primary key, an alternate
key, a secondary key, or a foreign key.
TOP-DOWN VERSUS BOTTOM-UP DESIGN
Another way to design a database is to use a bottom-up design method in which specific user requirements
are synthesized into a design. The opposite of a bottom-up design method is a top-down design method,
which begins with a general database design that models the overall enterprise and repeatedly refines the
model to achieve a design that supports all necessary applications. The original design and refinements are
often represented with E-R diagrams.
Both strategies have their advantages. The top-down approach lends a more global feel to the project; you
at least have some idea where you are headed, which is not so with a strictly bottom-up approach. On the
other hand, a bottom-up approach provides a rigorous way of tackling each separate requirement and ensur-
ing that it will be met. In particular, tables are created to satisfy each user view or requirement precisely.
When these tables are correctly merged into the cumulative design, you can be sure that you have satisfied
the requirements for each user view.
The ideal strategy combines the best of both approaches. Assuming the design problem is sufficiently
complicated to warrant the benefits of the top-down approach, you could begin the design process for Pre-
miere Products using a top-down approach by completing the following steps:
1. After gathering data on all user views, review them without attempting to create any tables. In
other words, try to get a general feel for the task at hand.
2. From this information, determine the basic entities of interest to the organization (sales reps,
customers, orders, and parts). Do not be overly concerned that you might miss an entity. If you
do miss one, it will show up in later steps of the design method.
3. For each entity, start a table. For example, if the entities are sales reps, customers, orders, and
parts, you will have the following:
Rep (
Customer (
Orders (
Part (
Search WWH ::




Custom Search