Documentation Final

Table of Contents SCREEN-SHOTS IN DESIGN VIEW.....................................................................................3 Tables...................................................................................................................................................3 ExaminerTbl.....................................................................................................................................3 ..........................................................................................................................................................3 ...............................................................................................................................................4 SubjectTbl.........................................................................................................................................5 ..........................................................................................................................................................5 TblScripts.........................................................................................................................................6 ..........................................................................................................................................................6 UI (User Interface) Screen-Shots.....................................................................................................7 Main Menu.......................................................................................................................................7 ..........................................................................................................................................................7 Second Layer....................................................................................................................................8 ..........................................................................................................................................................8 Adding Data Form............................................................................................................................9 Manipulation Form.........................................................................................................................10 ........................................................................................................................................................10 Queries...............................................................................................................................................11 ........................................................................................................................................................11 Reports...............................................................................................................................................18 Entity Relationship Diagram...........................................................................................................22 ............................................................................................................................................................22 SCREEN-SHOTS IN DATA VIEW.......................................................................................23 Tables.................................................................................................................................................23 ExaminerTbl...................................................................................................................................23 ........................................................................................................................................................23 SubjectTbl.......................................................................................................................................24 ........................................................................................................................................................24 ScriptsTbl.......................................................................................................................................25 ........................................................................................................................................................25 Queries...............................................................................................................................................27 ........................................................................................................................................................27 ........................................................................................................................................................28 ........................................................................................................................................................29 ........................................................................................................................................................30 ........................................................................................................................................................31 ........................................................................................................................................................32 ........................................................................................................................................................33 1

TESTING..............................................................................................................................34 ADDITIONAL TEST EVIDENCE.........................................................................................35 ........................................................................................................................................................35 ........................................................................................................................................................35 ........................................................................................................................................................35 ........................................................................................................................................................36 ........................................................................................................................................................37 ........................................................................................................................................................37 DESIGN................................................................................................................................38 Table Design......................................................................................................................................38 Examiner Table...............................................................................................................................38 Subject Table..................................................................................................................................39 Scripts Table...................................................................................................................................39 Entity Relationship Diagram...........................................................................................................40 Structure Chart.................................................................................................................................41 Security & Backup...........................................................................................................................42 Security:..........................................................................................................................................42 Hardware Layer..............................................................................................................................42 Software Layer...............................................................................................................................42 Backup............................................................................................................................................42 Data Flow Diagram..........................................................................................................................43

2

Screen-Shots in Design View Tables ExaminerTbl

As you can see; an input mask was set up for the examiner number, and no duplicates are allowed, the field size was also set to 6 characters, to save disk space (approx 44 bytes/record saved)

3

As you can see the subject reference is set to a lookup, and the field size is set to 5 characters (saving approximately 45 characters per record.)

4

SubjectTbl

As you can see, the field size is set to 5, saving approximately 45 characters per record, and an input mask is used to prevent erroneous data entry. The payment, is of course set as a currency field.

5

TblScripts

As you can see, a long integer was used (value above 2^16, (which is then divided by two, as all access numeric data-types are signed integers), and the appropriate input mask and validation rule as used, as per the issues specification. A composite key is used, as for this table (functioning as an effective link table) it’s the only arrangement that would have worked at this level of simplicity. SubjectRefCode is a lookup from the Subject table, and ExaminerNumber is a lookup from the examiner table. Forming the main relationship of my data (detailed in ERD)

6

UI (User Interface) Screen-Shots Main Menu

This form is opened using a macro named autoexec, which is an Access preset to fire the event on database load. The buttons each share the same (wizard generated) code, with the obvious exceptions of the form names matching the corresponding buttons, the code is as thus: Private Sub CmdExaminerMenu_Click() On Error GoTo Err_CmdExaminerMenu_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "ExaminerMenuFrm" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_CmdExaminerMenu_Click: Exit Sub Err_CmdExaminerMenu_Click: MsgBox Err.Description Resume Exit_CmdExaminerMenu_Click End Sub

7

Second Layer

All of the second layer, of course use the same code, generated by the wizard, leading to the appropriate forms. The forms themselves are of two main types, one for adding records, and one fore viewing, modification and deletion of data purposes. They map to the appropriate names. The forms are created by the wizard, the only difference being that the addition form has no record selectors, and has a button that adds the data entered in the controls to a new record, before clearing them via adding another new record, which access deletes from the table if the form is closed while the controls are empty.

8

Adding Data Form DoCmd.GoToRecord , , acNewRec

is used to add new records

All forms use the controls detailed in their table definitions, I.e. manual entries with textboxes, and lookups with combo boxes. The Add Record button contains the same error handling as all the others, but uses the above line to add a record.

9

Manipulation Form

This form was designed completely with an Access wizard, and contains record selectors, which can be used to browse between records, and a filter can be used (via the access toolbar) to find specific records. For modification, viewing or deletion.

1 0

Queries

This query totals the candidates of each examiner, and is used in subsequent reports.

1 1

This query was too wide to display in QBE mode, so I have screen-shotted the SQL of it, generated by Access, from by QBE specification. It is the query responsible for generating payslips.

1 2

This query is used to select all the appropriate data to list all by examiner, found in later reports.

1 3

This query totals the amount of candidates there are for each subject, this data is then used for subsequent reports.

1 4

This query is used to deduce each examiners pay rate. Based on the subject they have been marking.

1 5

This query is used to find the amount of students that enrol for a subject at each centre. The data is then grouped by subject.

1 6

This query is used to find the overall totals of money paid out to examiners, and the total number of scripts marked.

1 7

Reports

This report is based on the ByExaminerListAll query, and is used to list the number of candidates from each centre number ordered within each examiner’s number.

1 8

This report is used to form the payslip of each examiner, and uses purely expressions, with no VBA coding at all, as it did not seem able to register the checkbox control, so reference to the recordset forming the report itself had to be made.

1 9

This report lists all of the script data, including which centre the script came from, and who it was marked by, all ordered by the subject reference code.

2 0

This report is used for the overall summary, showing the total amount of money paid out, and the total number of scripts market.

2 1

Entity Relationship Diagram

2 2

Screen-Shots in Data View Tables ExaminerTbl

Above can be seen the input mask in action, and below can be found the lookup field working on a combo box.

2 3

SubjectTbl

Above can be seen the input mask on the subject table in action, allowing only numbers to be entered, and ensuring the field is precisely 5 characters long.

2 4

ScriptsTbl

Above can be seen the examiner ID lookup working correctly and below, can be seen the subject reference code look-up working correctly. Further down still, proof of the validation rule on the centre number can be found.

2 5

There is also an input mask, as can be seen below

2 6

Queries

2 7

2 8

2 9

3 0

3 1

3 2

3 3

Testing Test Number

Description

Functional?

Page Reference/section reference

1

Menu Form Loads on autoexec

Yes

6

2

New Form Loads When Button Pressed

Yes (Note, button still has focus)

7

3

All addition/modification forms load

Yes

35

4

Data added using form (all forms share the same code, so only one set of evidence is shown to prevent needless repetition)

Yes

36

5

Data Modified using form(all forms share the same code, so only one set of evidence is shown to prevent needless repetition)

Yes

34

6

Combo Boxes are functional (forms automatically inherit from tables when a wizard is used, so only the tables were shown, to avoid repetition)

Yes

22,24

7

Validations and input masks are functional Yes (forms automatically inherit from tables when a wizard is used, so only the tables were shown, to avoid repetition)

25

8

Validation rule for centre number works as intended

Yes

25

9

All reports function as intended (though all reports were tested for representation separately, they all functioned as intended, and so have been merged into one row of this test sheet)

Yes

Printouts Section.

3 4

Additional Test Evidence

3 5

3 6

3 7

Design Table Design Examiner Table Attribute Name

Data Type Default Input Mask Required Lookup In AQA Purpose Value spec?

Examiner Number

Text

Null

000000

Yes

None

Yes

To hold a unique examiner identifier

Examiner Forename

Test

Null

None

Yes

None

Yes

To hold the examiner's first name

Examiner Surname

Text

Null

None

Yes

None

Yes

To hold the examiner's last name

Examiner Address Line 1

Text

Null

None

Yes

None

Yes

To hold the examiner's address

Examiner Address Line 2

Text

Null

None

Yes

None

Yes

To hold the examiner's address

Examiner Address Line 3

Text

Null

None

Yes

None

Yes

To hold the examiner's address

Subject Text Reference Code

Null

00000

Yes

Subject Yes Table

To hold the code representing the subject which the examiner marks

Tax Flag

False

None

Yes

None

To represent the examiner's possession, or lack thereof, a tax deduction

Boolean

Yes

3 8

Subject Table Attribute Name

Data Type Default Input Mask Required Lookup In AQA Purpose Value spec?

Subject Text Reference Code

Null

00000

Yes

None

Yes

To hold a unique subject identifier

Payment

Null

None

Yes

None

Yes

To be used to calculate examiner payments

Currency

Scripts Table Attribute Name

Data Type Default Input Mask Required Lookup In AQA Purpose Value spec?

Centre Number

Integer

Null

Range Check of (10000 79999)

Yes

None

Yes

To hold a unique examiner identifier combined with the Examiner Number

Examiner Number

Test

Null

000000

Yes

None

Yes

To hold a unique examiner identifier combined with the Centre Number

Subject Text Reference Code

Null

00000

Yes

None

Yes

To hold the subject reference code

Number of Integer Candidates

Null

Range Yes check (>0)

None

Yes

To hold the number of candidates being marked from one centre, and by one examiner.

3 9

Entity Relationship Diagram

4 0

Structure Chart

Main Menu

Scripts menu

Add a script

view/edit /delete a script

Subject Menu

Add a subject

view/edit /delete a subject

Examiner Menu

Add an examiner

view/edit /delete an examiner

4 1

Security & Backup Security: For security, I propose two main levels, the hardware layer, and the software layer.

Hardware Layer I propose that the Access program itself (for speed’s sake) be stored locally, on the workstation’s main HDD. The .mdb file holding the data, and code of the front-end itself however is to be stored on a memory stick of suitable size (to be determined by end user, as only the size of the test data required, not actual data was submitted.)

Software Layer I propose that the database is kept in an obscure location on the hard disk, to be either memorised by the system’s users, or written down dead-tree, to prevent remote unauthorised acquisition of the system.

Backup I propose that the system be backed up on 3 memory sticks in a son, father, grandfather format. The data ‘snapshots’ should be made every day, but only during a time period when data is being entered, as there is little, if any sense of backing up a system during times in which there are no changes to the system, such an action could well carry a large opportunity cost. This choice however, is down to the discretion of the end user.

4 2

Data Flow Diagram

List of centres by examiner number

Add Script Add Examiner Add Subject

Edit Scripts

System

List of centres by subject Payslips List of centres by subject

Edit Examiners Summary of all

Edit Subjects

TblExaminer

TblScript

TblSubject

4 3