Experts in this subject field are ready to write an original essay following your instructions to the dot!Hire a Writer
There are 4 tables; CHILD, CARER, ACTIVITY, CHILD ACTIVITY.
The Database would take the following form.
Date of Birth
Date of Birth
CHILD ACTIVITY TABLE
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;
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
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.
The Play Group Table will contain the following columns: GroupID, Group Name, ChildID, 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.
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.,)
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.
This sample could have been used by your fellow student... Get your own unique essay on any topic and submit it by the deadline.
Hire one of our experts to create a completely original paper even in 3 hours!