Heathfield ICT & Computing

RDrive logo Heathfield logo Twitter logo

Unit 7: Database - design and use (2010)

Learning outcomes

By completing this unit candidates will develop a thorough knowledge and understanding of how to create and use a database. Candidates will be able to:
  • design a database to meet the needs of an organisation
  • construct a database according to a design
  • interrogate a database
  • create reports
  • create a user interface
  • test a database and make recommendations for improvements.

Scenario

  • Yodafone International is a mobile phone company based in the Dagobah system. In their main office there are a number of employees who help with the day-to-day running. Yodafone have planned to launch 12 new mobile phone shops around England. They have asked for a database using Microsoft Access to store details of customers, phones in stock and the transactions that have occurred. Yodafone have contacted you to design, create and test this database to ensure it functions appropriately and is user-friendly for anyone to use in the organisation.
  • Yodafone already have a spreadsheet (flat file) of the top 40 phones in stock, but there are 30 customers and their transactions that will need to be imported/entered too.  Validation will need to be set-up to ensure mistakes are not made in the data entry stage.
  • Forms, queries (searches), reports and a user interface will need to be designed and created too.  Good Luck!
 Picture of girl with access database behind her



AO1 - Design a database to meet the needs of an organisation
  Pass Merit Distinction
  Candidates will produce a basic design for a database in line with identified user requirements.

The design will include basic details of: table structure, primary key, field names, field types, field lengths and an input form.

The database will bear some resemblance to the design work undertaken.		 Candidates will produce a design for a relational database in line with identified user requirements.

The design will include details of: table structures, primary keys, field names, field types, field lengths, combo boxes and validation rules, relationships and input forms for each table.

The design should be sufficiently  detailed to enable others to produce the database with some explanation.

The database will reflect the design work undertaken with any deviations from the original design identified.
 Candidates will produce detailed designs for a relational database in line with identified user requirements.

The design will include comprehensive details of: table structures, primary keys, field names, field types, field lengths, combo boxes, validation rules, input masks (or equivalent), relationships and custom forms for each table.

The design should be sufficiently detailed to enable others to produce the database.

The database will reflect the design work undertaken with any deviations from the original design justified.
A

Info:

  • You need to produce design plans for a database with tables. Your designs must be appropriate for scenario above.
  • You may produce your plans by hand or using ICT, but at this stage, you must not start creating the database. You may have a discussion with your tutor about the initial planning (eg. discuss suitable table structures, field headings etc).

Task:

  • On plain paper write down the field names, field types, field lengths and input form.  You will need to give each table a name and choose a primary key.

Info:

  • You need to produce design plans for a relational database with related tables. Your designs must be appropriate for scenario above.
  • You may produce your plans by hand or using ICT, but at this stage, you must not start creating the database. You may have a discussion with your tutor about the initial planning (eg. discuss suitable table structures, field headings etc).

Task:

  • On plain paper write down the field names, field types, field lengths and input form for each table.  You will need to give each table a name and choose a primary key and a foreign key for creating relationships between tables.  You will need to ensure you have combo boxes and validation rules.
  • You will also need to sketch the relationships you plan to make.

Info:

  • You need to produce design plans for a relational database with related tables. Your designs must be appropriate for scenario above.
  • You may produce your plans by hand or using ICT, but at this stage, you must not start creating the database. You may have a discussion with your tutor about the initial planning (eg. discuss suitable table structures, field headings etc).

Task:

  • On plain paper write down the field names, field types, field lengths and input form for each table.  You will need to give each table a name and choose a primary key and a foreign key for creating relationships between tables and design a custom form too.  You will need to ensure you have combo boxes, validation rules and input masks.
  • You will also need to sketch the relationships you plan to make.
 

AO2 - Construct the database according to the design
  Pass Merit Distinction
  Candidates will construct a database based upon the design work undertaken.A form will be created to enter data in the table.

They will ensure the database includes sufficient records to meet the specified needs.

The database will mostly reflect the design work undertaken.
 Candidates will construct a relational database based upon the design work undertaken.

Forms will be created to enter appropriate data in each table. At least one form will be customised.

They will ensure the database includes sufficient records to meet the specified needs.

The database will reflect the design work undertaken.		 Candidates will construct a relational database based upon the design work undertaken.

Custom forms will be created to enter appropriate data in each table. At least one form will be customised to add functionality.

They will ensure the database includes sufficient records to meet the specified needs.

The database will match the design work undertaken.
B

Info:

  • In this task you will need to create the database according to your design structure for each table.
  • The extent to which your database reflects your design work will be important in determining your grade for this assessment objective. If you need to deviate from your original plans there is no need to go back and change your plans but you need to explain why you have made these changes.

Task:

  • Create the database and save it in your unit 7 ao2 folder as Yodafone.mdb
  • You will need to take print screens of the structure of each table - including field properties.

Info:

  • In this task you will need to create the database according to your design structure for each table and their relationships.
  • The extent to which your database reflects your design work will be important in determining your grade for this assessment objective. If you need to deviate from your original plans there is no need to go back and change your plans but you need to explain why you have made these changes.

Task:

  • Create the database and save it in your unit 7 ao2 folder as Yodafone.mdb
  • You will need to take print screens of the structure of each table - including field properties.
  • The relationships created will need to be printed

Info:

  • In this task you will need to create the database according to your design structure for each table and their relationships.
  • The extent to which your database reflects your design work will be important in determining your grade for this assessment objective. If you need to deviate from your original plans there is no need to go back and change your plans but you need to explain why you have made these changes.

Task:

  • Create the database and save it in your unit 7 ao2 folder as Yodafone.mdb
  • You will need to take print screens of the structure of each table - including field properties.
  • The relationships created will need to be printed
C

Info:

  • In this task you will create the form for the Top 40 phones.

Task:

  • Create a form for the Top40 phones.
  • Printout the form for Top40 phones.

Info:

  • In this task you will create forms for all 3 tables, at least one will have been customised.

Task:

  • Create a form for every table.
  • Printout the form for every table.

Info:

  • In this task you will create customised forms for all 3 tables, with some increased functionality.

Task:

  • Create a form for every table.
  • Printout the form for every table.
D

Info:

  • In this task you will enter the data for the Top 40 phones.

Task:

  • Import the data from this file
  • Printout the datasheet for Top40 phones

Info:

  • In this task you will populate all 3 tables with at least 30 records

Task:

  • Ask your teacher for the files to import 30 records
  • Printout the datasheet for all of your tables

Info:

  • In this task you will populate all 3 tables with at least 30 records

Task:

  • Ask your teacher for the files to import 30 records
  • Printout the datasheet for all of your tables
 

AO3 - Interrogate the database
  Pass Merit Distinction
  Candidates will create at least two queries to select information to meet specified needs, at least one will use multiple criteria and at least one will include a sort.

They will state the purpose of each query.

The query results will meet the specified needs.		 Candidates will create at least two queries to select information to meet specified needs. At least one query must include data from more than one table. 

At least one query will use complex criteria and at least one query will use multiple criteria.  One of these queries will include a sort on at least two fields.

They will state the purpose of each query, giving reasons for the query design chosen.

The query results will meet the specified needs.		 Candidates will create queries to select information to meet specified needs. At least one query must include data from more than one table.

At least two queries will use different complex criteria and at least two queries will use multiple criteria. One of the queries will include a sort on at least two fields.

They will state the purpose of each query, justifying the query design chosen with reasons for rejecting alternatives.

The query results will meet the specified needs.
E

Info:

  • Using your database from AO2, you will need to create and use queries to search the database and sort data.
  • You will need to describe the purpose of the queries, so before you construct each query, think about what you are going to search for and make sure it will be appropriate for your users.

Task:

  • Carry out one simple query on multiple criteria and another simple query that has been sorted.
  • You will need to take print screens of the structure of each query and then print it out.  Then you must describe the purpose of the query, making annotations of the criteria by hand, wherever possible.
  • Print out the datasheet for each query too to prove that it works.

Info:

  • Using your database from AO2, you will need to create and use queries to search the database and sort data.
  • You will need to describe the purpose of the queries, so before you construct each query, think about what you are going to search for and make sure it will be appropriate for your users.

Task:

  • Carry out two queries on linked tables: one of your queries must use multiple criteria and one must use complex criteria (NOT, OR, BETWEEN or parameters).  Sort one of these queries on more than one field. All your queries must be appropriate.
  • You will need to take print screens of the structure of each query and then print it out.  Then you must describe the purpose of the query, making annotations of the criteria by hand, wherever possible.
  • Print out the datasheet for each query too to prove that it works.

Info:

  • Using your database from AO2, you will need to create and use queries to search the database and sort data.
  • You will need to describe the purpose of the queries, so before you construct each query, think about what you are going to search for and make sure it will be appropriate for your users.

Task:

  • Carry out three appropriate queries on linked tables: one of your queries must use multiple criteria and the other two must use complex criteria (NOT, OR,BETWEEN or parameters). Sort one of these queries on more than one field. All your queries must be appropriate.
  • You will need to take print screens of the structure of each query and then print it out.  Then you must describe the purpose of the query, making annotations of the criteria by hand, wherever possible.
  • Print out the datasheet for each query too to prove that it works.
 

AO4 - Create reports
  Pass Merit Distinction
  Candidates will produce at least two different types of report, at least one of which must be based on a query.

Reports will be appropriate to the data being presented.		 Candidates will produce at least two different types of report, at least one of which must be based on a query and at least one will be a customised report.

Reports will be appropriate to the data being presented.		 Candidates will produce at least three different types of customised report, at least one of which must be based on a query.

Reports will be appropriate to the data being presented.
F

Info:

  • In this task you will use your saved queries from AO3 to produce appropriate reports for Yodafone.
  • For each query that you created in AO3, produce a report in an appropriate report style. Ensure that you display appropriate fields in each report.

Task:

  • Create the two reports
  • Printout the first page of each report

Info:

  • In this task you will use your saved queries from AO3 to produce appropriate reports for Yodafone.
  • For each query that you created in AO3, produce a report in an appropriate report style. Customise the reports to ensure that you display appropriate fields and house style in each report.

Task:

  • Create the two reports ensuring that you customise them with a house style and that each field is readable.
  • Printout the first page of each report.

Info:

  • In this task you will use your saved queries from AO3 to produce appropriate reports for Yodafone.
  • For each query that you created in AO3, produce a range of different report styles that are appropriate. Customise all of the reports to ensure that you display appropriate fields and house style in each report.

Task:

  • Create three reports ensuring that you customise them with a house style and that each field is readable.
  • Printout the first page of each report.
 

AO5 - Create a user interface
  Pass Merit Distinction
  Candidates will create a user interface.The user interface will give access to some of the main areas of the database.

The user interface will meet some of the needs of the intended user.		 Candidates will create a user interface.The user interface will give access to the main areas of the database.

The user interface meets most of the needs of the intended user.		 Candidates will create a user interface.The user interface will give access to the main areas of the database.

The user interface meets the needs of the intended user and is user-friendly for non-expert users.
G

Info:

  • In this task you need to create an interface for the staff at Yodafone who will use the database. You need to make the opening screen clear and easy for the users. Your interface could take the form of a switchboard, menu or customised form, making use of a range of macros.
  • Using the database you have created for this assignment, create a user interface that will give the staff access to the main parts of the database eg the forms and reports you have created. You may use a wizard or template provided by your software but to achieve a Distinction you must fully customise your interface.

Task:

  • Create your user-interface
  • Print out a copy of your user-interface and annotate.

Info:

  • In this task you need to create an interface for the staff at Yodafone who will use the database. You need to make the opening screen clear and easy for the users. Your interface could take the form of a switchboard, menu or customised form, making use of a range of macros.
  • Using the database you have created for this assignment, create a user interface that will give the staff access to the main parts of the database eg the forms and reports you have created. You may use a wizard or template provided by your software.
  • Your user interface should be clearly structured, with elements in a logical order, and should give users access to the main areas of the database.

Task:

  • Create your user-interface
  • Print out a copy of your user-interface and annotate.

Info:

  • In this task you need to create an interface for the staff at Yodafone who will use the database. You need to make the opening screen clear and easy for the users. Your interface could take the form of a switchboard, menu or customised form, making use of a range of macros.
  • Using the database you have created for this assignment, create a user interface that will give the staff access to the main parts of the database eg the forms and reports you have created. You may use a wizard or template provided by your software.
  • You should implement a fully customised and easy-to-use user interface that will give access to all the main areas of the database.

Task:

  • Create your user-interface
  • Print out a copy of your user-interface and annotate.
 

AO6 - Test the database
  Pass Merit Distinction
  Candidates will provide evidence of some testing.

Candidates will make improvements to the functionality of their database if issues are identified through the tests they have carried out.		 Candidates will provide evidence of testing most of the main areas of the database (as shown in the KUS).

Most tests will be appropriate.

Candidates will make improvements to the functionality of their database if  issues are identified through the tests they have carried out.		 Candidates will provide evidence of testing all the main areas of the database (as shown in the KUS).

All tests will all be appropriate.

Candidates will make improvements to the functionality of their database if issues are identified through the tests they have carried out. 
H

Info:

  • This task is to test your database and make necessary changes to improve its functionality.
  • The test plan includes the following checks, eg:
    • database meets original design brief
    • validation
    • forms
    • queries
    • reports
  •  If you identify any problems during the testing, you are expected to make the changes necessary to improve how your database works. If you do not identify any problems, you must record this. However, if problems are then found in your database, this will affect your grade for this assessment objective.

Task:

  • Complete this test table
  • Save the document in your unit 7 ao6 folder as test table.

Info:

  • This task is to test your database and make necessary changes to improve its functionality.
  • The test plan includes the following checks, eg:
    • database meets original design brief
    • validation
    • forms
    • queries
    • reports
  •  If you identify any problems during the testing, you are expected to make the changes necessary to improve how your database works. If you do not identify any problems, you must record this. However, if problems are then found in your database, this will affect your grade for this assessment objective.

Task:

  • Complete this test table
  • Save the document in your unit 7 ao6 folder as test table.

Info:

  • This task is to test your database and make necessary changes to improve its functionality.
  • This task is to test your database and make necessary changes to improve its functionality.
  • The test plan includes the following checks, eg:
    • database meets original design brief
    • validation
    • forms
    • queries
    • reports
  •  If you identify any problems during the testing, you are expected to make the changes necessary to improve how your database works. If you do not identify any problems, you must record this. However, if problems are then found in your database, this will affect your grade for this assessment objective.

Task:

  • Complete this test table
  • Save the document in your unit 7 ao6 folder as test table.