CCNC/CCNC Module 5/Designing and creating tables/Table Relationships/Testing By Adding Data

---

Testing the design

Outcomes

Upon completion of this tutorial the learner will be able to: Identify test data to obtain desired test results Add and modify data to test the tables and relationships

Its a very good idea to test your database designs on a frequent and regular basis as you iterate through their development. One of the first tasks in testing a design in to add, update and delete data to and from the new database tables. When the database has relationships defined with referential integrity rules applied this testing will require some thought. When developing a testing strategy you should consider the following steps;

1. Identify which tables are parent tables and which are child tables, and potentially which are grandchild tables. The parent tables contain NO foreign key references, child tables do.
2. Design data sets for each parent table. These data sets should contain data for all the mandatory fields.
3. Add data to the parent tables and keep reference of any primary key (PK) values you will want to reference as foreign keys (FK).
4. Design data sets for each child table. These data sets should contain data for all the mandatory fields and have foreign keys referencing back to primary key values in parent tables.
5. Add data sets to the child tables. Try and break this data entry by entering foreign key values that do not exist as primary key values from parent tables.
6. Design update data sets, where some of the updates violate the rules assigned to the table(s).
7. Make updates to data in the rows of all tables.
8. Try to delete records from parent tables with dependent child records in related tables.
9. First delete child records then their parent records.

Identifying the tables

Figure 1. The relationships

In our example database we currently have two parent tables and two child tables. The People and Addresses are the parents and the PhoneNumbers and People_xref_Addresses are the children. Once you have identified the parent and child tables it is time to develop test data.

Designing the test data

Designing the test data begins with the parent tables, this is due to the child tables having dependencies on data values already being in the parent tables. It is a good idea to create a variety of records for the parent tables that test any of the mandatory fields and particular formatting requirements. Formatting requirements refer to date formats (i.e. DD/MM/YY; MM/DD/YYYY; etc.) or Numeric Formats (i.e. 0.00; 0,00; etc.).

Adding parent table data

Figure 2. People data

Figure 3. Address data

Adding data to parent tables is easy as all you need is enter values into the tables fields. Due to the table being a parent table there will be no need to reference data in other tables. While testing directly against the parent tables it would be useful to write down the primary key values of the new entries as these values will be used when testing child tables.

Adding child table data

Figure 3. PhoneNumber data

Figure 4. No related parent PK

When entering data into child tables new records will have to reference the primary key values of parent tables. These values are known as foreign keys and should be entered into the field in the child table corresponding to the parent record. When an incorrect foreign key value is entered into a child table an error will occur.

Adding cross reference data

Figure 5. _xref_ data

In a many-to-many relationship the child table (or cross reference table) contains foreign key values for both the parent tables. As shown in the People_xref_Addresses table values need to be added to the person_id and address_id fields that have corresponding values in the respective People and Addresses tables. Failing to enter values that exist in these two parent tables will cause a PK error similar to the one in the previous step.

Updating the data

When updating data in a record only the non-key fields can be updated. And when making updates these values need to meet any rules applied to the fields of the table having data being updated.

Deleting the data

Figure 6. Deleting data error

When deleting data from within a one-to-many or many-to-many relationship the child data needs to be deleted first. Deleting parent data before the child data would orphan the child data.

Test your knowledge

Testing with data

1. Child records should be created before parent records? TRUE → Incorrect, click here to review the topic. FALSE → Absolutely Correct! 2. Adding a child record without a valid FK reference will cause an error? TRUE → Absolutely Correct! FALSE → Incorrect, click here to review the topic. Your score is 0 / 0