Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 161

Assess Table Structure

When someone gives you a set of tables and asks you to construct a database to store them,
your first step should be to assess the tables' structure and content. General guidelines for as-
sessing a table's structure are summarized in Figure 4-1.
As shown in Figure 4-1, you should examine the data and determine the functional dependen-
cies, multivalued dependencies, candidate keys, and each table's primary key. Also, look for possible
foreign keys. Again, you can base your conclusions on sample data, but that data might not have all
of the possible data cases. Therefore, verify your assumptions and conclusions with the users.
For example, suppose you receive data for the following SKU_DATA and BUYER tables
(with the primary keys logically determined at this point):

SKU_DATA ( SKU , SKU_Description, Buyer)
BUYER ( Buyer , Department)

Begin by counting the number of rows in each table using the SQL COUNT(*) function .
Then, to determine the number and type of the table's columns, use an SQL SELECT * state-
ment . If your table has thousands or millions of rows, however, a full query will take consider-
able time. One way to limit the results of this query is to use the SQL TOP {NumberOfRows}
property . For example, to obtain all columns for the first 5 rows of the SKU_DATA table, you
would code:

/* *** SQL-Query-CH04-01 *** */
SELECT TOP 5 *

FROM SKU_DATA;

This query will show you all columns and data for 5 rows, as shown in the following results. If
you want the top 50 rows, just use TOP 50 instead of TOP 5, and so on. At this point you should
confirm the primary key, and determine the data type for each of the columns in the table.

With regard to foreign keys, it is risky to assume that referential integrity constraints
have been enforced on the data. Instead, check it yourself. Suppose that, after investigation,
you confirm that SKU is the primary key of SKU_DATA and that Buyer is the primary key of
BUYER. You also think that SKU_DATA.Buyer is likely a foreign key linking to BUYER.Buyer.
The question is whether the following referential integrity constraint holds:

SKU_DATA.Buyer must exist in BUYER.Buyer

Figure 4-1
Guidelines for Assessing
Table Structure

• Count rows and examine columns
• Examine data values and interview users to determine:
Multivalued dependencies
Functional dependencies
Candidate keys
Primary keys
Foreign keys
• Assess validity of assumed referential integrity constraints