DATABASE DESIGN 2: DESIGN METHOD - Concepts of Database Management

Databases 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 particu-

lar 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.

●

219

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 rectangle;

the primary key appears above the line in the rectangle; the remaining columns appear below the line; alter-

nate 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 iden-

tified to the rectangle that corresponds to the table containing the foreign key. A dot at the end of the line indi-

cates 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.

●

The design method presented in this chapter is a bottom-up method. By listing potential relations before begin-

ning 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 func-

tional 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 indepen-

dent 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 repre-

sent 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.

●

Key Terms

artificial key

bottom-up design method

cardinality