User-Schema Evaluation Checklist

I use the following checklist to guide me through each step of a user-schema evaluation. You can make this part of your own bag of tools if you like.

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