User-Schema Evaluation Checklist

Set the context—I get a high-level understanding of what the database is about by reviewing the physical, external, and logical layers of the database environment.

Create a physical design—I create a physical model of the database, either by using the SQL Server diagram function or by reverse-engineering the database with a CASE tool.

List ambiguous tables—I make a list of tables that have an unclear purpose or ambiguous names. I clarify why they're in the database because some might be work or temporary tables that are left over from previous operations.

Find hidden arrays—I look for hidden arrays (lists of values contained in a table). A table that contains a list of values is pre­first normal form (1NF) or unnormalized. I make a note to decompose that table into third normal form (3NF).

List ambiguous field names—I look for fields that have ambiguous names. I find out what the fields are and what they mean. You can't properly determine a database's normalization level without looking at the data and understanding what it means.

Look for dirty data—I look for incomplete or incorrect data so that it doesn't distract me.

Locate redundancy across tables—I locate metadata redundancy across tables. I resolve such redundancies by removing all but one instance of duplicate attributes or by renaming attributes to make them more meaningful and accurate.

Find transitive dependencies within tables—I analyze each table for a transitive dependency (the existence of second normal form—2NF—data). If one or more transitive dependencies exist in a table, I make a note to decompose that table into 3NF.

Identify less-than-full functional dependency—If a table has less-than-full functional dependency (existence of 1NF data), I make a note to decompose that table into 3NF.

Propose database changes—I propose the list of changes to the database and get the client's approval to make the changes. Then, I check with the DBA to arrange a time to make the changes.

Design views that support existing applications—I list the views, either materialized or regular, that I need to build to support applications that were written against under- or overnormalized data.

Implement the changes—I implement the architectural changes to the database and create the compensating views.

Monitor performance—After I've completed the restructuring, I work with the DBA to measure performance. If a problem still exists, I suggest that the client move to more powerful hardware and a greater-bandwidth network.