Creating a Physical Database from the Schema

The database schema is simply a map of the database; therefore a physical database needs to be created using this map. One of the easiest ways to create a physical database from a schema is through an SQL script (a representation of the schema) that “builds” database tables based on the schema-defined rules and restrictions.

To assist agencies in getting started on collecting TIM performance data, an SQL script that translates the schema into an empty database is available for download and use as part of this guidance ([filedownload file=”/uploads/files/SQL_Script_to_Create_an_Empty_TIM_Performance_Measurement_Database_final.sql” type=”plain-txt”]click here to download the creation script[/filedownload]). While SQL is a standard language that most databases understand, there is no one SQL script that will create all databases. Therefore, slight modifications may need to be made to the script depending on the database in use.

Once the script is used to generate the data tables, another script is needed to populate the lookup tables. Lookup tables store all of the possible values for a given data element so that these values do not have to be entered each time a record is created. Consequently, the user simply selects the value from a list of possible choices rather than entering the information in free form. As an example, there may be more than one way to refer to a particular roadway (by name, by state route number, or even abbreviations). In a roadway name lookup table, all roadways in a coverage area would already be entered into the lookup table for the user to choose from. As such, lookup tables help keep the data consistent across records. The script for populating the look-up tables with static data is also available for download and use ([filedownload file=”/uploads/files/SQL_Script_to_Populate_Database_Lookup_tables_final.sql” type=”plain/text”]click here to download the population script[/filedownload]).