Preserving Data Harmony: Exploring Referential Integrity in Databases

There are 4 tables; CHILD, CARER, ACTIVITY, CHILD ACTIVITY.

The Database would take the following form.

CHILDS TABLE

CHILD ID

Surname

First Name

Date of Birth

Gender

Parent’s Name

CARER TABLE

TAG

Surname

First Name

Date of Birth

Gender

Skill

ACTIVITY TABLE

LABEL

Activity Name

Day

Start Time

End Time

Amount

TAG

CHILD ACTIVITY TABLE

CODE

Child ID

Activity ID

Reporting Time

End Time

TAG

1. Query to list the children and associated activity.

I use the SELECT QUERY on multiple tables.

Syntax: SELECT select_list FROM source WHERE condition(s) GROUP BY expression

HAVING condition ORDER BY expression

The result of running the query will be;

Surname

First Name

Activity Name

The SELECT statement will look like:

SELECT Surname, First Name, Name of Activity, Day of activity.

FROM Child, Activity

GROUP BY Surname

ORDER BY Surname, Activity Name

Count the number of activities for each child.

We still use the select query.  We introduce a new clause called COUNT. The statement will look like:

SELECT Surname, First Name, Activity name COUNT (*) NumActivityID.

FROM Child Table, Activity Table

WHERE ACTIVITY NAME (SELECT COUNT (*) NumActivityID.)

Child and Activity names for a specific carer.

SELECT Surname, First Name, Name of Activity

FROM Child, Activity

WHERE TAG = “xya”

Total amount earned by each activity.

SELECT Name of Activity, COUNT (*) NumActivityID

FROM Activity, Child Activity

Referential Integrity

Is data accuracy and integrity in a relationship in a database. Data is linked between two or more tables by a key. When the primary key of one table is associated with another table as a Foreign key. The foreign key must be referenced accurately. (Elmasri and Navathe, Batini et al.)

A new child activity can be inserted if the activity is unique from al the others already existing in the system. A child activity can be deleted if it resembles another activity within the table. This is called redundancy and can affect database integrity.

A Carer can be deleted if he/she is fired from the organization or his name conflicts with another name in the database system. A Carer can be inserted if his/her name is unique and they are new to the organization.

“On Delete: NO ACTION” means you can’t delete the parent if the children are there. While “On Delete: SET NULL” allows data referencing the parent key to be deleted but not updated. When this happens all rows of the child table that depend on the primary key have their foreign keys set to NULL. In SET NULL incase referenced data is updated or deleted, all data dependent to it is set to NULL. While in NO ACTION data associated and dependent on the deleted data will not be updated or deleted. (Kagalwala and Thompson, Bell and Grimson)

A design Extension to the Database.

Create new tables called Child Attendance and Play Group. Child Attendance store records about all the sessions a child has attended. Play Group keeps records on all the children under a particular Carer.

The new tables are as follows

Child Attendance will contain the following columns: Ticket, Child ID, Day, Time, Tag. 

Group ID

Group Name

Child ID

TAG

The Play Group Table will contain the following columns: GroupID, Group Name, ChildID, Tag.

TICKET

Child ID

Day

Time

TAG

The primary key is TICKET which is a ticket number indicating that the child attended the session. The Secondary Key is Tag. Tag represents the Unique identification number for the Carer as indicated in his/her employment tag.

PLAY GROUP

The primary key is TICKET which is a ticket number indicating that the child attended the session. The Secondary Key is Tag. Tag represents the Unique identification number for the Carer as indicated in his/her employment tag.  (Model et al., Batini et al.,)

Reference

Batini, Carlo, et al. Conceptual Database Design: An Entity-Relationship Approach. Vol. 116, Benjamin/Cummings Redwood City, CA, 1992.

---. Conceptual Database Design: An Entity-Relationship Approach. Vol. 116, Benjamin/Cummings Redwood City, CA, 1992.

Bell, David, and Jane Grimson. Distributed Database Systems. Addison-Wesley Longman Publishing Co., Inc., 1992.

Elmasri, Ramez, and Shamkant Navathe. Fundamentals of Database Systems. Addison-Wesley Publishing Company, 2010.

Kagalwala, Raxit, and John Thompson. Database Schema for Structure Query Language (SQL) Server. Google Patents, 2003.

Model, Entity Relationship. Entity-Relationship Model. 2000.