PHS badge

PHS CyberSchool

Computer Studies SG

Modified 04 June 2006

Orchestra database - Basic

The director of your school orchestra has asked you to create a database of orchestra players. He wants to keep track of the following data on each player: name (first name, surname), address (address1, address2, suburb), telephone number, date of birth, emergency contact name, emergency telephone number, type of musical instrument, instrument number (used to identify the instrument when storing and for insurance), whether the student owns or leases the instrument, and the date the player joined the orchestra.

It is suggested that you create a folder and put the database and all used files, in the folder. Put any images used in the folder and then insert into the database. The file path of the images can easily be incorrect when the database is moved. It would be a pity to lose the images.

Design and create a database to meet the director's needs. Create the necessary table(s), enter at least 30 records. Be careful of file type and file size. Use the LookupWizard to speed entry of data, particularly the instrument and suburb.
Create a form in which to add new records to the database. Arrange the fields on the image of a musical stand or around a baton. Check that all the fields are arranged in a suitable tab order. Give the form a suitable heading. Save the form with a suitable name.

Orchestra database - Queries - 10 marks

Perform the following. Save all the queries with clear names.

The director would like a telephone list of all the players. List the name (first name, surname) and telephone numbers of all players. [1]
The orchestra is going to a festival this weekend and it is important that the school officials are able to reach a parent/guardian in the event of an emergency. List the name (first name, surname) and emergency contact information for all players. [1]
The local music centre is offering a special weekend for all violin players. Identify all players that play the violin. [1]
The school has just arranged a new lease arrangement with a local music store. List the name (first name, surname), telephone number and type of instrument of those who lease their instruments. [1]
The director needs a list of all the players plus their date of birth and ages. [2]
Players who have been a member of the orchestra for five years or more are eligible for a special award. List the name (first name, surname), the date of joining, length of membership in years and instrument of these players. [2]
List the instrument, member name (first name, surname) in order by surname within band instrument type. [2]

Orchestra database - Maintaining the database

Create appropriate formats, and validity rules and text for entering data in numerous fields.
Ensure that data is always entered in the name (first name, surname), telephone number, emergency contact name and emergency contact number fields.

Orchestra database - Presenting information - 8 marks

Perform the following. Save all the reports with clear names.

Create a report showing the names (first name, surname) and instruments of all the players. It should have a suitable heading and image in the header area. In the report footer area indicate the number (count) of all the players. Save the report with a suitable name. [2]
Create a report showing the names of all the players plus their ages. In the report footer area indicate the average (avg) age of the players. Save the report with a suitable name. [2]
Create a report showing all players, instruments, instrument number grouped by instrument. Save the report with a suitable name. [2]
Create reports of ALL the queries with clear headings. Save with suitable names. (The public are not interested in ugly looking queries but smart looking reports based on queries.) [7]

Orchestra database - Creating an application system - 4 marks

Create a switchboard [1] on which a user can click buttons to open all forms [1] in form view and all reports (11 in total) in print preview.
The application title should be "PHS orchestra" [1] and should have a suitable icon, preferably the school badge.
When the database is opened it should open with the switchboard. [1]
Compact the database.
Submit on a disk zipping it if necessary.

Onsite learners be prepared for peer assessment. Online learners compact the database, zip it and submit to your teacher.

(Based on Access 2000 by Shelly, G.B., Cashman, T.J. and Pratt, P.J. published by Course Technology in 2000 www.scsite.com with many many alterations.)