Submission Instructions

The task will be submitted electronically into the Year 10 IST Cloud Storage (Google) folder or GitHub (as directed by your teacher). It is the student’s responsibility to ensure that all files are saved and working correctly.

Outcomes being assessed

●       5.1.1 Selects and justifies the application of appropriate software programs to a range of tasks.

●       5.2.2 Designs, produces and evaluates appropriate solutions to a range of challenging problems.

●       5.5.2 communicates ideas, processes and solutions to a targeted audience

Task Description

In order to undertake this task you need to :

A. Finalise Requirements

CREATE A NEW DATABASE DESIGN DOCUMENT

1. Produce a completed Database Schema

1. Reproduce and then complete the schema diagram (using a drawing tool such as http://Draw.io) by performing the following tasks:

●     Identify the Primary Key fields each table by underlining the field and placing a "PK" next to it.

●     Indicate the Relationships between the tables on the above schema - tagging the Foreign Key (FK) and ensuring the correct "cardinality" is indicated in the drawing.

2. Produce a Data Dictionary for the above DOE Schema

1. Document it using a data dictionary format for each and all Database Tables identified above,  an example as follows:

Table Name:  Families

Table Description: This table is used to store information about …. 

Please ensure you use an appropriate datatype and size with a meaningful description for each field and table

B. Creating Database

CREATE A DATABASE FILE

1. Create Tables in a Database

Create the tables in a database management system using the information above as a guide.

Please ensure you have created the following:

●     Tables

●     Fields - identifying appropriate data types, sizes

●     Relationships - linking multiple tables together

Fill in the names and descriptions from your data dictionary

2. Create Test Data

Create at least 10 rows per table of data that will enable you to have

●     5-10 families

●     10+/- students - with multiple students linked to at one family

●     10 awards - with students having an Award each with a range of awards types

●     3 different Award Types - Bronze, Silver and Gold awards

3. Create Customised Forms to Enter/Maintain Data on the following tables:-

•  Students
• (including automated subforms – child table underneath)
•  Award Types
• (including automated subforms – child table underneath)

Please choose your own appropriate Award Scheme LOGO and colour scheme that enhances the basic forms

Please include customised buttons for:

●     Go to first

●     Go to previous

●     Go to next

●     Go to last records

●     Close Form

Give the forms appropriate names

4. Create Query to interrogate the database:

Create a Awards Recipients Query to the return the following information:

●     Family: ParentName, Street, Suburb, Postcode

●     Student: Given Name, Surname

●     Award: DateOfAward

●     Award Type: AwardName

Filter:

●     DateOfAward must be in since and including 01-01-2018

Sort results automatically by

●     ParentName (Asc),

●     DateofAward (Desc)

Give the query an appropriate name

5. Create a Customised Report

The DOE Awards Committee will need to have a report that lists all Awards Recipients with their Family/Student details and the Awards they received

Using the Awards Recipient Query (above) create an appropriately formatted report based on that above query, using your customised logo and colours that match your forms.

Give an appropriate name to the Report.

6. Create a Mail Merge Document

Create a letter from the DOE Awards Scheme to the Family and Student congratulating them on receiving the Award.

Using an appropriate Word Processing tool, create a mail merge that links in the Query you created previously.

Using the Awards Recipient Query to select the data include ALL the fields in your query in your mail merge.

You must provide a copy of the Mail Merge TEMPLATE, and the GENERATED LETTERS from the merge process.