U6: ADVANCED DATABASES

Health Warning Please remember that the Exam Board

doesn’t provide Mas for anything beyond U4, so this is my best interpretation of the markgrids and other paperwork…

Note: this one requires access to a relational database – such as Access – and you CANNOT complete without this!

All thanks to the Screen Bean teams for the clipart images used to lighten this one!

Scenario A new pre-school age nursery has been set

up, and already has nearly 75 children on its books. With so many children, each with unique needs & details, a database is the only way forward.

Oh, and there’s one small twist: the person running it has NO IDEA of how to use anything ‘complicated’ on the computer, so your work has to be user-proof!

Scenario

Your database must beEasy to use – including menus/buttonsCapable of storing all children’s recordsEasy to search, add and edit recordsCapable of creating reports on

○ Illness and allergy○ Daily registers○ Contact home letters

Task 1: Rolling up your sleeves

Here’s where you get a grip. OutlineWHAT you need to do

○ Audience?○ Purpose?○ AT LEAST 3 tables showing entities &

attributes○ ERD (that’s entity relationship diagram)○ And that’s just Pass – the others go onto the

next slide as well!

Task 1: Rolling up your sleeves

ALSO:MERIT:

○ User Interface design○ Forms design

DISTINCTION○ Report design

I don’t usually say this, but for this one: do the designs at least in some detail first: relational databases bite!

Techie Stuff

You need to make sure you’re comfortable with some serious stuff here:What the heck is an entity anyway?ERDAttributes of entitiesNormalisation in general, esp 3rd normalised

formValidation…And that’s just AO1. Be sure you’re up for

this: it’s a real challenge!

Markgrid – AO1 PASS

Candidates produce a design for a working relational database.

The design includes purpose, audience, at least three tables showing the entities and attributes and an ERD diagram.

The entities and attributes may not be correct. The database design may be normalised to 3rd

normal form. There will be some attempt to define the data

validation required.

Markgrid – AO1 MERIT

Candidates produce a design for a working relational database.

The design includes purpose, audience, at least three tables showing the entities and attributes, ERD diagram, user interface and forms.

The entities and attributes are appropriate. The database design is normalised to 3rd normal

form. Most of the data validation required has been

defined.

Markgrid – AO1 DISTINCTION

Candidates produce a design for a working relational database.

The design includes purpose, audience, at least three tables showing the entities and attributes, ERD diagram, user interface, forms and reports.

The entities and attributes are all correct. The designs for reports are for reports both printed and

on screen. The database design is correctly normalised to

3rdnormal form. All of the data validation required has been defined.

Whoa…!

Task 2: Doing the Do

OK – now it’s time to put your plans into action. Put in the basic linked structure, repeatedly check it meets the needs of the user from AO1

Four data types at least, and at least 60 records input

Include guidance for the user: e.g. messages, validation

Task 2: Doing the Do

This bit will take the time, but don’t let it just drag: keep focused on the task.

You can import the data from another source at this point: the forms have to work, but you can prove that at testing rather than this stage!

Markgrid – AO2 PASS

Candidates produce a relational database that allows a user to add, delete, and edit records using forms.

Primary key and foreign keys are defined and tables are linked.

Data validation and customised error messages may have been set up.

There are at least four data types used and at least 60 records have been entered in the database.

The database will be mostly similar to design.

Markgrid – AO2 MERIT

Candidates produce a working relational database that allows a user to add, delete, and edit records using forms.

Primary key and foreign keys are defined, tables are linked, and data validation and customised error messages have been set up.

There are at least four data types used and at least 60 records have been entered in the database.

The database will be similar to the design.

Markgrid – AO2 DISTINCTION

Candidates produce a working relational database that allows a user to add, delete, and edit records using forms.

Primary key and foreign keys are defined, tables are linked, and data validation and customised error messages have been set up.

There are at least four data types used and at least 60 records have been entered in the database.

The database will exactly match the design.

Task 3: Making it DO something

Ah. Yes, the sticky bit. It looks good, why spoil it? Because. No, that’s it: because.

So now you need to create queries that mean you can use the data you’ve bunged into this database. We’re talking different range operators, calculated fields, parameter and crosstab queries, queries on linked tables… check your level on the grid for the nitty gritty… or is that nitty griddy?

Markgrid – AO3

PASSCandidates create queries that include:

○ at least three different logical operators, ○ at least three different range operators and ○ calculated fields using multiple criteria on

linked tables. Some queries produced may not give the

correct results.

Markgrid – AO3

MERITCandidates create queries that include:

○ at least three different logical operators,○ at least three different range operators,○ calculated fields, ○ parameter queries and crosstab queries,

using multiple criteria on linked tables.

Most queries produced give the correct results.

Markgrid – AO3

DISTINCTIONCandidates create queries that include:

○ at least three different logical operators, ○ at least three different range operators, ○ calculated fields, ○ parameter queries and crosstab queries,

using multiple criteria on linked tables. All the queries give the correct results

and are efficient.

Task 4: Managing the User So we have a cautious user. We need to

make this as friendly as poss…In the next few AOs we’re looking at managing the user, the most important part of any IT/ICT system…AO4: Creating a user interface, including

formsAO5: Creating reports, with levels of

complexity (depends on your level)AO6: Creating a user guide

Markgrid – AO4

PASSCandidates produce a user interface that

gives access to the main parts of the database.

The interface may lack structure. They create at least one form for each

table.

Markgrid – AO4

MERITCandidates produce a user interface that

gives access to the main parts of the database.

The interface mostly matches the design.

They create at least one form for each table and a customised form.

Markgrid – AO4

DISTINCTIONCandidates produce a user interface that

accesses all parts of the database. The interface matches the design. They create at least one form for each

table, and a customised form and at least one sub form.

Markgrid – AO5

PASSCandidates produce reports to include at

least one customised report and at least one report that is sorted on more than one field.

The reports may not match the designs.

Markgrid – AO5

MERITCandidates produce reports to include at

least one customised report, at least one report that is sorted on more than one field and at least one report that is grouped on one or more fields.

The reports mostly match the designs.

Markgrid – AO5

DISTINCTIONCandidates produce reports to include at

least one customised report, at least one report that is sorted on more than one field, at least one report that is grouped on one or more fields and at least one report that shows grouped and overall summaries.

The reports match the designs.

Markgrid – AO6

PASSCandidates produce a user guide that

covers some of the processes that allow a user to make effective use of the database.

They define the database structure, data relationships and a data dictionary in the technical documentation.

Markgrid – AO6

MERITCandidates produce a user guide, using

graphics and screenshots, that covers some of the processes that allow a user to make effective use of the database.

They clearly define the database structure, data relationships and a data dictionary in the technical documentation.

Markgrid – AO6

DISTINCTIONCandidates produce a user guide, using

graphics and screenshots that covers most of the processes that allow a user to make effective use of the database.

They clearly and accurately define the database structure, data relationships and a data dictionary in the technical documentation.

Task 5: Does It Work?

Ah, testing. PLANNED testing, that is. Some of the testing will have gone on as you built this thing, and now you’re experienced in this type of project you should have kept evidence of that to use as part of this… Do I hear an ‘oops!’?

Again, the level of testing depends on the level you’re going for, but the next slide highlights what you’re looking at…

Task 5: Does it Work?

Produce a test plan covering the following checks: as a minimum Database meets original design briefValidationFormsQueriesReports

Make changes if appropriate

Task 5: Does it Work?

And then judge it! Does it do what YOU said, on the tin? How do you know?

What should be available in v 2.0 for the user? What else would you think the user would want it to do – and for Distinction, how would you do it?

Note you don’t have to DO these improvements, but they do have to be valuable and detailed recommendations for Distinction level especially!

Markgrid – AO7

PASSCandidates provide a basic test plan that

covers some of the checks as outlined in the Knowledge, Understanding and Skills. (That’s the list on the previous slide!)

Markgrid – AO7

MERITCandidates provide a test plan that

covers most of the checks as outlined in the Knowledge, Understanding and Skills. (That’s the list on the previous slide!)

Markgrid – AO6

DISTINCTIONCandidates provide a test plan that

covers most of the checks as outlined in the Knowledge, Understanding and Skills. (That’s the list on the previous slide!)

Changes are made as appropriate to the database.

Markgrid – AO8

PASSCandidates provide a brief and not

always accurate evaluation of the effectiveness of the database in relation to user needs.

Markgrid – AO8

MERITCandidates provide a detailed and

accurate evaluation of the operation of the database and how well it meets user needs.

Candidates will provide a detailed description of improvements for the user.

Markgrid – AO6

DISTINCTIONCandidates provide a comprehensive

evaluation of how well the database meets the specification.

Candidates will suggest detailed improvements and recommendations to refine the solution.