Database Reference
In-Depth Information
Summary
Database design is a two-part process of determining an appropriate database structure to satisfy a given
set of requirements. In the information-level design, a clean DBMS design that is not dependent on a par-
ticular DBMS is created to satisfy the requirements. In the physical-level design, the final information-level
design is converted into an appropriate design for the particular DBMS that will be used.
A user view is the set of necessary requirements to support a particular user's operations. To simplify the
design process, the overall set of requirements is split into user views.
The information-level design method involves applying the following steps to each user view: represent the
user view as a collection of tables, normalize these tables (convert the collection into an equivalent collec-
tion that is in third normal form), represent all keys (primary, alternate, secondary, and foreign), and merge
the results into the cumulative design.
A database design is represented in a language called Database Design Language (DBDL).
Designs can be represented visually using entity-relationship (E-R) diagrams. Such diagrams have the fol-
lowing characteristics: there is a rectangle for each entity; the name of the entity appears above the rect-
angle; the primary key appears above the line in the rectangle; the remaining columns appear below the
line; alternate keys, secondary keys, and foreign keys are identified with the letters AK, SK, and FK,
respectively; and for each foreign key, there is a dashed line from the rectangle that corresponds to the
table being identified to the rectangle that corresponds to the table containing the foreign key. A dot at the
end of the line indicates the “many” part of a one-to-many relationship.
When a relational DBMS is going to be used, the physical-level design process consists of creating a table
for each entity in the DBDL design. Any constraints (primary key, alternate key, or foreign key) that the
DBMS cannot enforce must be enforced by the programs in the system; this fact must be documented for
the programmers.
223
The design method presented in this chapter is a bottom-up method. By listing potential relations before
beginning the method, you have the advantages of both the top-down and bottom-up approaches.
A survey form is useful for documenting the information gathered for the database design process.
To obtain information from existing documents, list all attributes present in the documents, identify potential
functional dependencies, make a tentative list of tables, and use the functional dependencies to refine
the list.
To implement a one-to-one relationship, include the primary key of one of the two tables in the other table
as a foreign key and then indicate the foreign key as an alternate key.
If a table's primary key consists of three (or more) columns, you must determine whether there are inde-
pendent relationships between pairs of these columns. If there are independent relationships, the table is
not in fourth normal form and you must split it. If there are no independent relationships, you can't split the
table because doing so produces incorrect information.
If a table contains columns that can be null and the nulls represent the fact that the column is inapplicable
for some rows, you can split the table, placing the null column(s) in separate tables. These new tables rep-
resent entity subtypes.
It is possible that the result of merging third normal form tables may not be in third normal form. To avoid
this problem, include determinants for columns in the individual tables before merging them.
The entity-relationship (E-R) model is a method of representing the structure of a database using an E-R
diagram. In an E-R diagram, a rectangle represents an entity, a diamond represents a relationship, and an
oval represents an attribute.
