2. Developing the Relational Database

The first step in our project involved developing a relational database using Microsoft Access. This related many tables including; building inventory, BC 31 Building Classification, replacement cost as well as structural and non-structural MDF’s for MMI event levels VI-XII.

2.1 Normalization

The first step in this process involved normalizing all the tables in the database, to improve data integrity, and reduce redundancy. This process assigns a unique identifier to describe a single item in the database, which can be used as a reference to that item in multiple tables throughout the database using relationships.

2.2 Relationships

These relationships can exist as one to one or one to many. For example, one structural mean damage factor was associated with one building prototype (one to one), where as one prototype can be linked to many buildings in the inventory (one to many). By enforcing referential integrity in the relationships, it allowed the changes in one table to be reflected throughout the entire database in all other related tables. The following figure provides a visual representation of the relationships used in our database.

Figure 2.2.1. Relationships used in the Database

2.3 Reports

Reports are used in Microsoft Access for the purpose of displaying information stored in the database in a neat and efficient manner. This allowed us to produce individual reports for each building in the database. These reports were able to include selected building characteristics as well as the results from the analysis: structural and non-structural mean damage factors, monetary losses and casualty estimation values (Figure 2.3.1.).

2.4 Queries

A query can be used as a powerful tool to ask questions of the database. These questions can be used to extract information of interest. For example, several queries were developed in Microsoft Access to summarize the information and multiply values across tables on a block by block basis. The results of the query are stored in a table, which can be exported out of Access into a database file (.dbf) to be imported into ArcMap 9.1. This allowed the data to be modeled using a GIS, connecting the attribute table information to a shape file using Block_ID as the linking variable.

Figure 2.3.1. Example of a Report for an Individual Building