Coursework Specifications

1: Information Retrieval

Design queries to meet the following requirements:

1. Listing of children and their associated activities

A listing showing details of each child together with the name and day of each of their associated

activities.

The query output should be in three columns: child name (first name and surname), activity name,

activity day.

The output should be ordered alphabetically by child surname, with children with the same surname

being listed alphabetically by first name. For each child in the output, their activities should be ordered

alphabetically by activity name.

This query should be used to create a view named Childlist and saved to your database (i.e

yourusernamedb on the College SQL server).

2. Count the number of activities for each child

This should include any children who do not currently have activities associated with them. This is not

the case with the current data so in order to test the query you should first add yourself, using your

real name but any value you like for date of birth, to the Child table but DO NOT add any associated

activities.

The output should be in two columns: Child name (first name and surname), and a count of the

associated activities. (This will be 0 for children with no associated activities.)

Save the output of this query to a spreadsheet named yourusername_output (replacing yourusername

with your actual ITS username).

3. A child and activity list for a specified carer

Before this query is run a carer's first name and surname (e.g. 'Liane' and 'Cartman') should be

assigned to variables.

The query should then output a list of the children cared for by the specified carer, along with their

activities and the days of those activities.

The output should be in three columns: the child's first name and surname, the activity name and the

day of the activity. The list should be sorted alphabetically by child surname, with children with the

same surname being listed alphabetically by first name.

4. Total amount earned by each activity.

The output for this query should show the activity name, the total number of children participating in

the activity and the total amount of money earned by that activity. This total should be shown as a

currency amount (e.g. £14.50). Hint: You will need to use the CONCAT() and ROUND() functions in

order to do this.

The output should be sorted by the total amount earned, with the activity earning the most appearing

first.

(e.g., 8 children are registered for Football @£2.00, making a total earning of £16.)/n2: Referential Integrity

A) Describe under what circumstances a new Childactivity record can be successfully inserted into

the database. Can an existing Childactivity record always be deleted? In both cases, justify your

answers.

B) Describe under what circumstances an existing Carer record can be successfully deleted from the

database. Can a new Carer record always be inserted? In both cases, justify your answers.

C) Now assume that all the foreign key settings are changed from 'On Delete: NO ACTION' to 'On

Delete: SET NULL'. Explain how your answers to A) and B) above would change as a result. Explain

the advantages and disadvantages of using this setting.

D) For each foreign key in the database, suggest a setting for 'On Delete', justifying why you think

that setting is most appropriate.

3: Design an extension to the database

The current database assumes that each child has the same carer and also does not allow for children

to attend only on certain days of the week. The play scheme manager now wishes to introduce greater

flexibility as follows. Children attend the play scheme on certain days of the week, each day being

represented by its first three letters (e.g., "'Mon' for 'Monday'). If a child attends every day of the

week, then there will be five entries for that child. The assumption is that attendance patterns are

the same each week. A particular child has only a single carer on any particular day of attendance but

could have more than one carer when considering all the days of attendance. A carer can still look

after many children (on the same day). There is no need to check that children only undertake

activities on days they attend the play scheme. Also, carers do not supervise activities.

This data cannot be stored in the current child and carer tables. To show how these requirements

could be incorporated into the database you are required to:

• Produce a simple ER diagram (in box/crow's foot format) of the complete play scheme

database including any new entities and/or relationships needed to meet these new

requirements.

•

Produce a listing of the columns required for any new table(s), showing each column name

and datatype, and also indicating which columns are primary and foreign keys.

•

Document any other changes that would need to be made to the database to implement these

new requirements.

Note: You are only required to produce the design for these

requirements. You are not expected to implement them in the

database.