Smart Baby Nursery and Kindergarten has started its operation since 2 years ago. As the number of children in the nursery and kindergarten increases, their manually done management system is no longer sufficient to support their operation. Thus, they need a computerized database system, which will help them to store the following information: a. The personal particulars of every child, which include ID, name, birth certification number, age, parents contact information, health condition, entry date and any other relevant information. b. The personal particulars of the staffs, which include staff ID, name, IC number, contact information, date started working and any other relevant information. c. Each staff is assigned to take care of maximum 10 children in every year. d. The learning subjects offered are learning the Letters, Learning the Numbers, Drawing and Music. The system should be able to keep track of the childrens performance for each of the subject taken. e. Monthly fee is charged and amount paid, payment due (if any), due date and payment date must be recorded properly in the system. Your task is to carry out the following exercises.

Exercise One

Draw the E-R Diagram to show the relationship between the entities and attributes.

Include the entity integrity & referential integrity in your data model.

Create the tables required and set the appropriate data type, field size, format and input mask as required. Justify your design.

Set up the default relationships between the three tables ensuring that you enforce referential integrity with no cascade options.

Enter 10 sample data for each of the tables created.

(30 marks)

Exercise Two

Amend the table validation etc. as follows:

Set an input mask for ID that forces default uppercase(s) alpha character (ABC) and three numeric digits (e.g. C001 for child ID or STA001 for staff ID)

Set an input mask to ensure that the data entered for name is capitalized automatically.

Set up a validation rule that ensures that the DOB entered is not more than the current date.

Set up a validation rule that permits the childrens gender to be one of the following only: Male or Female only.

Set the default value of the fees payment date to automatically display the current date during data entry.

(10 marks)

Exercise Three

Create one menu form, to enable maintenance of data and operation of the system.

Create and save custom made forms for data entry in all tables. Ensure that all your forms have the required navigation buttons. Format your forms accordingly.

Exercise Four

1. Create and save a query that would display all children by age, 4, 5 and 6 years old and their classes.

2. Create a query that will display the children from each class.

3. Create a query that will display the children details sorted from the oldest to youngest in age followed by their name.

4. Create a parameter query that will display all children details based on the children name entered.

5. Create a query to display the total number of subjects available.

6. Calculate the fees collected by childrens age and month.

7. Calculate the total outstanding by class and month.

8. Create a query to display the total payment for each children.

9. Create and save a parameter that will show the childrens performance records based on subject and by children.

10. Using SQL statement, increase the monthly fees by 10%.

11. Using SQL statement, write a query to display the outstanding fees for children whom has outstanding for two(2) months or more.

12. Using SQL (not QBE), create and save an SQL statement that depicts the total fees collection yearly in each year 4, year 5 and year 6 classes.