AVIATIONAL SCIENCE DATABASE
The
Aviational Science Database designed and discussed here is the first version of
the system. The database has been
created for the purposes of an assignment for a course at the
In Brief:
Open the Access File –
Aviational Science (if you do not have a copy of this file you will need to
contact Kelli Karl on 0412 449 467). For
standard use you will work off the main switchboard that has been designed with
access to the forms and reports. The
help will bring you to this website. All
information that is required for the database can be added through the
switchboard and any reports can be viewed that are listed. Follow the prompts for each form, a logical
tab sequence is used, descriptions have been used to prompt the user for the
type of information required. To view
information on how to use access or add/edit records please consult the
Microsoft Access Help Menu or the Office Assistant. The addition of information into this database
is via basic data entry. Where possible
all attempts have been made to assist the user with entry of data however, not
all human error can be completely eliminated.
To ensure data consistency and accuracy within the system certain
considerations have been made on form design, properties selected and
referential integrity. The forms used to
enter the required data for the Aviational Science Database have been designed
to be simple and easy to use. The forms
will reject invalid responses and where applicable have drop down boxes to
ensure only valid values in fields are chosen.
Through the use of input masks and property selections (eg. date values
or text), there will be at least some sort of error detection for the input of
data. The forms have asked for values
that are based around standardised values – eg. Flight hours are usually talked
about as 1.4 hours or 1.5 hours and so these are the values to be input (not
all in minutes or fractions etc.). All
results for assessments in Gradebook are to be given as a percentage value
which means boundaries have been set for the data to be a number between 0 and
100. The use of the Yes/No Values has
been used for the CASA Approved Table, with the default set to ‘No’.
Relationships have been
set up between tables so that when entering an ARN (which is also the Primary
Key) invalid name values cannot be entered – this is to ensure consistency
between tables and to reduce transpositional errors.
In Detail:
At
this stage the Aviational Science database has been designed to allow teachers
to record student details required, their grades for exams and flight
information in a Microsoft Access Database.
It is fairly simple to use if you do not want to modify anything and
work from the main ‘switchboard’.
Obviously, knowledge of Access will help but not essential. Modification of the queries and reports would
initially be done through me and once more established would have all reports
required. The Aviational Science
Database can be modified for each user – with queries and reports
modified. For the purposes of this trial
version a set of ‘test’ data has been used, with a sample of reports given –
please note that these reports are not restrictive and could consist of
certificates, graph charts for hours and types of aircraft flown plus anymore
that you could think of. The database
has been set up with primary keys (two were used – Student Number issued by the
school and ARN-Aviational Reference Number) so as to allow a unique data reference
and to setup relationships between tables.
By doing this the need to constantly type student names and information
has been eliminated to just one form/table (this will hopefully reduce any
input errors and mismatch of data eg. the way in which names have been either
input or spelt). The uniformness of
forms and reports has helped to establish a user friendly environment.
The
teacher needs to input the student details in the frmStudentDetails
form; the fields have default values, validation rules and formats to assist
the user in entering data and to avoid common data entry mistakes. The
information for this will be relayed either from student records on file at the
school or through students directly – they will need to be supervised if
security levels have not been established to avoid release of sensitive data.
Each
student MUST obtain an ARN number from CASA before enrolling in this course or
prior to any assessment.
The
Gradebook Form – frmGradebook is used for teachers
to input examination and assessment grades of students – this information is
collected after the student has completed their exam/assignment. Again, ‘descriptions’ have been used to
prompt the user for the required information required. All marks are given as a
result ‘Between 0 and 100’ and this has been set as a Validation Rule. The report has been used to calculate an
Averaged Final Result for the Students and can be seen in the report
rptGradebook. This form will enable teachers to obtain an accurate result with
no need for self calculating results.
The rules are designed to pick up errors and Error messages will display
if trying to input non valid data.
The
Student Flight Hours Form – frmStudentFlightHours
is used to input flight details for each student. At this stage this information will still be
collected by the use of a ‘hardcopy’ form as per the design in Assignment 1,
this will be authorised via the Flight School Instructor. It is envisaged that this will be completed
electronically via the Flight Instructor at a latter stage when the database
system and the Aviational Science course is more established. Students will want a record of this data for
their own logbooks and so there is a report (rptFlightHours) to enable
calculation of individual students (via ARN) total flight hours.
Once
the students have completed some of the flight components of the Aviational
Science course they may be entitled to receive official CASA (Civil Aviation
Safety Authority) accreditation. The
students will be notified via mail with these endorsements and ratings; any
Commercial or Flight Theory will also be mailed by CASA. The CASA Approval Form – frmCASAApproval will keep track of students’
individual CASA ratings.
There
are two other forms within this Access database – the Switchboard form and the
About form. Neither will be used to
input data and are solely for the simplicity of the user and so are not
discussed in great detail here.
Whilst
various reports can be run from this database, the test data used for this
assignment concentrates on three of the main reports that will be used. The first of these is for the teacher to view
the enrolled students in Aviational Science, this is organised in alphabetical
order sorted by class grade level (see rptAviationalScienceEnrolment). The report uses the query
qryAllEnrolledStudents to obtain its data.
A parameter query could be used for Grade Level if this report needed to
be broken up into just grade levels.
The
second report generated is for teachers to use to give students a print out of
their logbook/flight hours. The report
uses the relationships established between tables and uses a parameter query
for the ARN to determine which students’ flight times will be displayed. For the rptFlightHours
used below the test data ARN was 538423.
The report then displays those entries associated with that ARN, relates
it to the students name and uses a calculation function to sum the total hours.
The
third example of a report generated by this database is for the Gradebook – rptGradebook.
This report generates for all students enrolled in Aviational Science,
sorted by year level and alphabetised within each grade. Again, the relationships allow for student
names to be displayed on this report.
The final field for the report is the final grade – this is calculated
by using an expression within the field to average the results displayed for
exams and assignments.
For
the basic use of this system, which is for entering information and printing
reports, the switchboard will be utilised.
Please check with your administrator for backup details and procedures.
Note this listbox for a list of values to “lookup”
for grade levels. The Student Details Form
– frmStudentDetails has used 2 Primary Keys (Student ID and ARN). This is the only form that requires First
and Last Name input, the queries and reports will pick up this data via
established relationships. The DOB
and Medical Renewal field are formatted as a ‘Date’ to eliminate errors and
the criteria for the DOB is set to be before todays date whereas the
criteria for the Medical Renewal is to be after todays date (ie. >) The Year Level field has
a lookup box, so that user can choose from grades 8 through to 12. The use of a check box has been used for
the Permission Granted field with the default set to ‘No’. The Phone Number field is set as ‘text’
to avoid any confusion with numbers and to allow the user to enter numbers
of different formats including brackets around area codes. Where appropriate Descriptions have been
entered to prompt the user of the type of information they should be
entering.
The Gradebook Form –
frmGradebook has made use of the association between tables and thus only
askes for the Student ID to be input – this will be enough to relate this
data to the individual students. For
the examination fields of Meteorology, Human Factors, Navigation, AGK,
Aircraft Performance and Airlaw, Validation Rules have been set to “Between
0 and 100”. The Validation
Descriptions will appear “Please ensure your mark is between 0 and 100” if
the incorrect value is typed. Please
note also the “Descriptive” text for each field is displayed. The Assignment information follows
exactly the same format as the examinations but has been grouped
differently for a friendlier user interface.
Again, the Flight Hour
Form does not require the input of names – just the primary key of
ARN. This will be sufficient to link
to other student information. The
Date Flown is set to a date format and the Criteria is set to < so that
the date is one in the past and not a future date. The Aircraft Type field is a “lookup”
type listbox with the common type of aircraft flown listed – this is to
ensure the correct/consistant formatting of data. Other Aircraft types may be typed in this
box. The flight times are given as a
1 decimal number. Descriptions are
again used for the fields to assist the user.
The CASA Flight Approval
Form – frmCASAApproval. The form uses the ARN as
the Primary Key. Check boxes are
used for the rest of the form – defaults are set to no. Descriptions are used. Unless students are avid flyers it is not
expected that students will complete these as standard.
REPORTS
rptAviationalScienceEnrolments
There
are four tables used to store data for the Aviational Science Database. They are the Student Details Table –
tblStudentDetails, Student Flight Hours Table – tblStudentFlightHours, CASA
Approval Table – tblCASAApprovals and the Gradebook Table – tblGradebook. The tables have been designed so as to try
not to double up on inputting data. The
primary keys link the data. The relationships
are outlined below showing the links between tables. Each of the four relational tables shows the
fields associated in each. All field
names are “spaceless” so as to be interchangeable if wanting to use within a
program. Through use of the captions a
more readable name is viewed.
Descriptions have been entered for all fields – this can be viewed
within the sample database.
To
view the relationships when in Access you can expand the data as shown below.
Note the expansion of data
to view the other tables once the relationships (with referential integrity)
have been set.
Backing up to create a safety copy of
your work is extremely important in the event that something might happen to
your original file, or if it becomes corrupt you won’t be left with
nothing. For the Aviational Science
Database, User-Level-Security was used (however there is no security
implemented on the “test” database); therefore, backing up both the database
and the workgroup information file will be important (eg. backing up the .mdb
to a .bak and the secured.mdw) otherwise we won’t be able to start Access until
we restore or build it. This ideally
would be backed up whenever incremental changes were made however, practically
speaking if the files (assuming both DB and WIF) were fully Backed Up every
fortnight and incremental changes backed up on Wednesdays and Fridays this
should suffice.
One-step Security Wizard Report
This report contains the information you need to re-create your workgroup
file and regain
access to your secured database in case of corruption. It is highly
recommended that you print or export this report and keep it somewhere
secure.
Unsecured Database:
C:\Documents and Settings\Kelli\My Documents\Aviational Science
Database.bak
Secured Database:
C:\Documents and Settings\Kelli\My Documents\Aviational Science
Database.mdb
Workgroup Information File:
C:\Documents and Settings\Kelli\My Documents\Secured.mdw
User Name:
Kelli Karl
Company:
Workgroup ID:
2uyWrt54
Secured Objects:
Tables:
CASA Approvals
Gradebook
Student Flight Hours
Students Details
<New Tables/Queries>
Queries:
Student Flight Hours Query
Student Overall Performance
<New Tables/Queries>
Forms:
Student Details
Student Flight Hour Entry
<New Forms>
Reports:
Gradebook
Student List
<New Reports>
Macros:
<New Macros>
Database:
VBE Password not set
Groups:
Name: Admins
Group ID: <Previously Created>
Users:
Kelli
Name: Users
Group ID: <Previously Created>
Users:
Kelli
Sherilyn
PeterSmith
Users: Name: Kelli
Personal ID: a412636
Password: a412636
Groups:
Admins
Name: admin
Personal ID: <Previously Created>
Password: t1ECh5AbRRgf5
Groups:
One-step Security Wizard Report
To ensure an effective
backup procedure is in place at least once a month the Aviational Science
Database and Workgroup Information File should be loaded from a Backup Copy to
guarantee its effectiveness.
The
initial design for the Aviational Science Database was a lot more involved and
complicated than it is at this stage. I
didn’t realise that to create a user friendly database system efficiently that
there could be so much work and thought involved. Whilst testing the database system with
“dummy” data along the way I realised that designing the database takes more
time and thought than actually implementing it.
I
streamlined my tables so as to not have repetitive data – thus making use of
the Primary Keys in Microsoft Access.
Making use of the calculation tools made it easier to total grades or
flight hours than manually doing these.
I would’ve like to have integrated further scenarios into this database,
for example, account details for the students flight, photo id’s on the
database, graphs of the flight hours, statistical reports and a working
administration system.
As
I implemented parts of the database early on in Access a lot of the data and
reports have been kept fairly similar – just format changes and extra Access
tools used (eg. Captions, Validation Rules, Parameter Queries).
I
have thoroughly enjoyed implementing this database and hope to use it in the
future.