Testing SQL

As part of the Design process you will have created Entity Relationship diagrams and a data dictionary. The data dictionary will then have allowed you to implement the database tables through the use of CREATE statements

A reminder of the structure of the Travel Agency Database

The data dictionary and SQL CREATE statements for each table is shown as a reminder below:

Implemented SQL CREATE Statement

How to Compare against the implemented version

To test that the CREATE statements and the implemented tables match we can export a Data Dictionary from MySQL. Please note as already discussed the CHECK constraints will not be present. In phpMyAdmin from a database or table you can export the data dictionary as seen below.

The output from the Data Dictionary hyperlink will look like the following screenshot ( the actual pdf file is here)

You will notice that the data validation is not displayed. But the fields, types and key details are displayed.

As stated earlier the validation would have to be implemented using views/triggers. Any validation should also be tested using appropriate test data.

SQL Operations Working Correctly

Just as in Software Development it is important to test the results of your queries to verify that the output is accurate. Just because you get some output does not mean you should accept it as correct.

Likewise sometimes a query may generate 0 rows as a result.

Looking at the source tables

If you go into the Browse tab in phpMyAdmin for a table you can look at the source data.

The Browse tab allows us to see the amount of records and all of the source data in the table.

Based on the data above if we searched for the amount of different Hard Drives we stock we would expect 3 results.

That will allow you to look at the data that is in the table. You can then manually check/calculate your results.

Filtering records in a Table

It is possible for you to apply a temporary filter to a database table in phpMyAdmin to only view records relevant to ones you are concerned with. The screenshot below show the results before the filter is applied.

Other ways to test SQL Databases

You can also test your database by:

Attempting to enter duplicate data for primary keys as entity integrity should ensure that each primary key is unique.
Entering data for foreign keys that don't exist, which would break referential integrity, as every foreign key value has to exist in the other table.
Testing validation using a wide range of test data.
Verify query results using a spreadsheet/formulae

You will notice that all of the testing has been conducted in phpMyadmin.

This means that if these results are valid and there is differing output if a database has been implemented using PHP then there is a problem with the integration of the two components as opposed to the components themselves.