Databases Reference
In-Depth Information
The Employee table uses the EmployeeNum column as its primary key, the SocSecNum column as its alter-
nate key, the LastName column as its secondary key, and the DepartmentNum column as a foreign key that
matches the DepartmentNum column in the Department table. You must find a way for the DBMS to ensure that
the following conditions hold true:
●
Employee numbers are unique.
●
Social Security numbers are unique.
●
Access to an employee on the basis of his or her last name is rapid. (This restriction differs in that
it merely states that a certain type of activity must be efficient, but it is an important restriction
nonetheless.)
●
Department numbers must match the number of a department currently in the database.
When the DBMS can't enforce these restrictions, who should enforce them? Two choices are possible: the
users of the system or the programmers. If users must enforce these restrictions, they must be careful not to
enter two employees with the same EmployeeNum, an employee with an invalid DepartmentNum, and so
on. Clearly, this type of enforcement would put a tremendous burden on users.
When the DBMS can't 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 enforcement
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 doesn't, 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 database. If
it doesn't, 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.
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.
196
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 rep-
resented 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 ensuring
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.
