Unit introduction

A The purpose and structure of relational database management systems

A1 Relational database management systems

A2 Manipulating data structures and data in relational databases

A3 Normalisation

B Standard methods and techniques to design relational database solutions

B1 Relational database design

B2 Design documentation

C Creating a relational database structure

C1 Producing a database solution

C2 Testing and refining the database solution

D1 Database design evaluation

D2 Evaluation of database testing

D3 Evaluation of the database

Summary of assessment

Assessment outcomes

Integrity constraints

Entity relationships

updating, inserting, deletion

Retrieval of data for queries, reports

Referential Integrity

Cascading update

Deletion techniques

Joins, Unions, and Intersects

Stages of normalisation:

Database design: conceptual, logical and physical modeling and entity-relationship modeling.

Relational algebra:

RDMS and SQL software selection.

Application design: user interface, software applications.

Database implementation techniques:

Quality, effectiveness, and appropriateness of the solution:

Security and legal considerations:

Week 1

T&L Activities:

Learning Aims and Objectives:
Aim: In this week's page, students will learn the.

Objectives:
1. By the end of this week's page students will be able to.

2. By the end of the week's page students will be able to.

3. By the end of the week's page students will be able to .

4. By the end of the week's page students will be able to

5. By the end of the week's page students will be able to.

In order to produce information to support many business processes as well as our social lives, relational databases are widely used to manage and process data. From the smallest in-house systems to stock control systems for large online retailers, databases are repositories of information that are a significant part of organisational operating requirements.

You will examine the structure of data and its origins, and how an efficient data design follows through to an effective and useful database. You will examine a given scenario and develop an effective design solution to produce a database system. You will then test your solution to ensure that it works correctly. Finally, you will evaluate each stage of the development process and the effectiveness of your database solution. To complete the assessment tasks within this unit, you will need to draw on your learning from across your programme.

The skills you gain in this unit support progression to IT-related higher education courses and to employment in a role that requires computing-related expertise.

This unit is externally assessed through a task set and marked by Pearson. The set task will be completed under supervised conditions for 10 hours in a one-week period set by Pearson, which can be arranged over a number of sessions. The set task will assess learners’ ability to design, create, test and evaluate a relational database system to manage information. The number of marks for the unit is 66. The assessment availability is December/January and May/June each year. The first assessment availability is December 2017/January 2018. Sample assessment materials will be available to help centres prepare learners for assessment.

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

AO3 Analyse information about database problems and data from test results to optimise the performance of a database solution

AO4 Evaluate evidence to make informed judgements about the success of a database’s design and performance

AO5 Be able to develop a database solution to meet a client brief with appropriate justification

Files that support this week

English:

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

Awarding Organisation Criteria:

Maths:

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 2

T&L Activities:

• Types of relational database management systems (RDBMS) and their characteristics.

What is a RDMS?

A (RDMS) Relational database management systems is a database that is used to keep data in tables (this is also refered to as an entity or relation), each of these tables holds linked data, for example client details. The relational data structures join the tables into datasets

RDMS can be:

Desktop, for example MS Access®

Client-server, for example Oracle®

Open source, for example MySQL

• RDBMS based on relational models:

o relational data structures – relation, attribute, domain, tuple, cardinality and relational database

o relational algebra sets – symbols, union, intersect, join, select

ᴗ (union) combines two tables with matching fields for all the records, removing any duplicates:

Products_1 ᴗ Products_2

Creates a dataset of all the records from both tables

ᴖ (intersect) creates a dataset where records match:

Products_1 ᴖ Products_2

Creates a dataset of matched records from these tables

Can you suggest more examples of these relationships?

⋈ (join) creates a dataset from two or more tables:

Staff ⋈ Department

Joins the Staff and Department tables into a dataset

σ (select) for a subset of records matching criteria:

σ Genre = “tablet”(Products)

Selects records with tablet in Genre field of Products

Can you suggest more examples of these relationships?

o database relations – entity relationship, generic, semantic

o relational keys –

Super key - As an example, a table could contain columns like those below

Customer name
Customer ID
National Insurance Number (NI)
Address
Date of birth

A set of columns could be used, Examples of superkeys are as follows:

Name+NI+Birthdate
ID+Name+NI

The process may be reduced. You can assume that the customer ID is unique to each customer. So the superkey could be reduced to just one field the customer ID, which is the candidate key. However, to ensure absolute uniqueness, a composite candidate key may be formed by combining customer ID with NI.

candidate key,

primary key: - This type of key is used as a unique identifer for a given data set, this can be a combination of letters and numbers. You as an individual in the UK will at the age of 15-16 be sent your National Insurance Number, this number stays with you up to and beyond your death. The number is used to allow things like tax and national insurance to be collected against you, but to also enable you to use this when it comes to claiming benefits, state pensions, and health benefits. This number needs to be unique to you. In databases, we use (in most places) an auto number to provide this detail. Within Access, the auto number starts at 1 and increments by one. If a number is deleted from the database, for example, a mis-entry the number will not be able to be used again.

foreign key : - This key is used often in databases as it is essentially the primary key again, however, in this instance the key is in another table. For example, if you took your national insurance number and put it into the table, employment ( this is a table that would have your employment information) your id could appear more than once as you may be employed by different employers over the years. Within the "Employment" table, it would have its own primary key so that your foreign keys could appear more than once.

entity integrity,

referential integrity

one-to-one,

one-to-many : - Found in almost all database, one-to-many relationships are the link between a data set to another relevant dataset, for example, you are a student at the college, you have a student ID, this id is unique to you. You are studying a course that has a Course ID. You are not the only one studying on this course there are others. So we would have a table that records the enrolment. So your unique id would be put into the enrolment table and next to it would be the course id too.

many-to-many.

Use of RDBMS software tools and structured query language (SQL) for defining,

modifying and removing data structures and data:

When using SQL we can use a series of commands to enter data in to tables, the below are examples of SQL commands used;

INSERT INTO Table_Name (FIELD1, FIELD2) VALUES ("Hello","World");

https://www.w3schools.com/sql/exercise.asp?filename=exercise_insert1

UPDATE Table_Name SET (FIELD1= "Hello" FIELD2="World") WHERE Condition;

https://www.w3schools.com/sql/exercise.asp?filename=exercise_update1

DELETE FROM Table_Name WHERE condition;

https://www.w3schools.com/sql/exercise.asp?filename=exercise_delete1

You will often use the below structure to recall data and information from a system to enable the creation of a report;

SELECT * FROM Table_Name WHERE Condition;

the above statement can be shortened to

SELECT * FROM Table_Name

This statement will recall all information from the identified table and display it to the user, whereas the previous statement can enable the user to define specific search criteria, for example looking for only females.

https://www.w3schools.com/sql/exercise.asp?filename=exercise_select1

• administration of users

• security, integrity, recovery.

Files that support this week

Week 1 Task

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Sentence Structure: Use a range of sentence structures for clarity, purpose and effect, with accurate punctuation and spelling.

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Product Evidence - Learners to create product evidence to demonstrate understanding

Question and Answer - Verbal discussion with learners to quantify understanding

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

Awarding Organisation Criteria:

Maths:

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Collect and record data: Questionnaires, Observation, Tally

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 3

T&L Activities:

The role of normalisation to develop efficient data structures:

Normailistaion is a process that is vital to the overall success of a database. In most situations databases are created out of necessity to store data that has already been created or gathered, the database is a tool to provide better access and use to the data to essentially turn it into useful information.

When provided with the raw data a process of normalisation is required to be undertaken to find out how best to store the data without allowing for un-necessary duplication or redundancy, as not only could this lead to confusion but also to increased file sizes of a database, which could, in turn, slow down its ability to retrieve data and information.

https://www.bbc.co.uk/bitesize/guides/zvq634j/test

• anomalies – update, insertion, deletion

• primary keys, foreign keys, composite keys

As mentioned in earlier lessons the use of keys be them primary, foreign or composite as core to the success of a database, in the normalisation process we will identify the primary key within the data sets that are gathered/gained/provided and then move to split them out in to their composite tables, then we will be able to see the links between the tables where they happen naturally, for example, a person living at the same address could be done using foreign keys for the house as a number of people can live in the same place.

• indexing

Referential integrity is a database concept that is used to create and maintain logical relationships between tables.

Usually, referential integrity is made up by the combination of primary keys and foreign keys within database tables.

The main purpose of the referential integrity concept is that it does not allow to add any record in a table that contains the foreign key unless the reference table containing a corresponding primary key.

If any record in the referenced table (i.e. the table who contain primary key) is deleted, all the corresponding records in the referencing table will be deleted for the referential integrity.

• data dictionary – tables, fields, data types, validation

Data dictionarty's are important to the developer to ensure that the correct structure of the database is created. this is essentially an instruction manual to a developer to create the correct input types to the fields in a database system, for example, the use of the number for the age of an individual and that the field length of this should only be at most 3. I do not know of anyone that is older than 999 do you!!?

The cascade update feature of a database will enable the user to update a database value of a primary key in its table and for the associated foreign key in other linked tables to be updated. For example, the primary key in a table for bikes, the id was originally just the number, so 1,2, 3,4, the company have now updated the field to include 1000 before the primary key number so the record will look like 10001, 10002, 10003, 10004 and so on.

o un-normalised form (UNF) : - UNF is completely raw, this is where the data and the headings that they are under are in a single "table" ASSIGNMENT TIP (Look for the ID's)

o first normal form (1NF) : - 1st normal form is when the data is where you try to create individual table entities, grabbing the IDs as an indication to the tables will help

o second normal form (2NF) : - 2nd NF is where the duplication is attempted to be removed, it may be that another table may need to be created, this will require a new id to be created.

o third normal form (3NF) : - 3rd NF should provide a completely normalized version of the tables and entities within them including the required foreign keys that would need to be in the tables to establish the links.

Exercise 1
Exercise 2 Exercise 3

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Organising Information: Organise information and ideas into structured and sequenced sentences, paragraphs and whole texts, using a variety of linguistic and structural features to support cohesion and overall coherence.

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Peer Assessment of Independent Work - Learners work in groups to assess each other's work and make recommendations for improvement.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

Awarding Organisation Criteria:

Maths:

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Using numbers: Written or mental methods, using a calculator, rounding and estimating, problem solving

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 4

T&L Activities:

Selection of RDBMS and SQL software, tools, techniques, and processes.

When designing a database we can create a series of design documents to present our idea's and solutions. Two such diagrams are the conceptual and logical designs, they plot the links between table entities and the flow of data between them. However careful consideration must be given to them, due to the very nature of a concept. The concept its self may not be able to be produced as it the programs that would be expected to create it may have limitations that do not enable this to happen. Therefore we create a logical diagram that works closely to how the system would work, therefore allowing the user to consider extra tables and entities to be included in the database.

The area of Database development is one that is covered with acronyms and algebra. The areas that you will be most exposed to this is in the creation of tables and queries.

Tables that are created in a database will offer the ability to be linked using, one to many, one to one, many to many relationships.

Within queries we use command words, AND, OR, NOT, > (this symbol stands for greater), < (this symbol stands for less than), ≥ (this symbol stands for greater and equal to), ≤ (this symbol stands for greater and equal to) the combination of these key command words will allow the data to be turned in to information based on our requirements and parameters that we have provided the query.

There are several different database software available to use. Some are more appropriate for small to medium-sized organisations, whereas others are geared towards larger corporations that use data and information almost every second of their working and non-working day.

Software that is well-known cover products such as;

Microsoft Access,

Oracle,

MySQL,

Microsoft SQL Server.

Inside of most database development tools you will find that they have a section where user input can be designed to enable the entry of data and information.

These application/user interfaces will provide users with tools to create user-friendly entry points. Within the Microsoft Access program users are able to use the Forms feature to build input entry dialogue boxes to enable entry. The feature offers features such as text boxes, labels, buttons, dropdowns, tooltips, alert boxes, sound and many more. The use of these inputs will provide easy input of content. Each of them having adjustable properties such as colour, font and alignment.

When creating a database system/user interface the manipulation of the layout will provide opportunities for the developer to customise the application to meet the companies house style and look.

Prototyping

Prototyping a database is often used to enable developers and designers the ability to produce a product that can demonstrate a vision/ view of a potential solution to a given brief. Prototypes will give users the feel and look to a system, however, it may have limited features and capabilities. This could provide a negative bias towards the product by frustrating potential test users as features that may be expected to function are not. Another potential pitfall would be that due to its limitations the solution would not provide sufficient capabilities so it is agreed to move from prototype to final product that implemented version still falls short of its requirements.

Data conversion

Testing

Testing of any product is key and important to its ultimate success. The testing process within the creation of a database solution requires sufficient dummy data to test all aspects. This would range from data validation rules to relationships between tables, to report outputs and queries to generate information from the data.

All testing MUST be recorded to demonstrate to others that features function correctly, and, as an audit of checking each database feature. If tests results return as a fail, it is ESSENTIAL that this is resolved and recorded.

In larger databases, and in some corporate settings it may require copies of live data to be input into the system to simulate normal operation and to give a clear opportunity to identify problems.

Correctness of data

Relationships between data

Data integrity

Normalisation

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Question and Answer - Verbal discussion with learners to quantify understanding

Product Evidence - Learners to create product evidence to demonstrate understanding

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

Awarding Organisation Criteria:

Maths:

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Statistic extracting information: Tables, Diagrams, Charts and Graphs

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 5

T&L Activities:

The features and characteristics of relational database design techniques and their application to

solve problems:

• requirements of the brief (audience, purpose and client’s requirements)

When creating a database solution, it is essential to completely understand the requiremetns of the client and the purpose of the required database. This should be where you read back to the client you understanding of thier problem and the intended solution to this. Once you and the cleint are in agreement to the issues, requirement, and solution then the development SHOULD be smoother.

When creating the solution, key legislation and legal elements must be completely clear and understood as neglecting these may lead to legal fines or imprisonment, such laws to reflect on are,

o Data Protection Act 1998 : -

Go to the link below and review the legislation and its principal parts, identify how many principles that there are;
Government Website on DPA Act 1998

o The European Union (EU) Directive on Data Protection (legislation must be current and applicable to England, Wales, Northern Ireland)

Within Europe, there are a number of principles that as a "current member (30/09/2019) the UK must obey. The legislation can be linked can be found in the link inside the activity.

Review and research the legislation that is enforceable within the EU for Data protection.
EU Legislation

• data structure designs:

Data structure designs are a useful tool for the ability to understand the problems solution and the intended approach to the implementation of the solution.

o data dictionaries and their use: tables, field attributes, validation

As discussed in the previous weeks areas, data dictionaries enable the developer to outline and follow the correct structure and field formats and any validation rules or attributes associated with a field area, for example, the telephone number, most automatically choose the number as its a telephone "NUMBER" however in a database the input is seen as a number that has a zero "0" at the beginning and therefor removes it as an integer will not have a zero in the front. So in this instance, we would need to consider the use of text, and then apply some rules to it, using an input mask, and defining its field length to the same as a telephone number.

Data Dictionary
Table Name	Games
Field Name	Attributes
Game_ID	Primary Key, Integer
Game_Title	Text,25 characters, no validation, no input mask
Producer	Text,25 characters, no validation, no input mask
Genre	Text, Using a look-up table to display the following options, "Fighter", "Racing", "Role Play"
Price	Currency,
Platform_ID	Foreign Key, Must be an entry on the Console platform table.
Pegi Rating	Text using a look-up table to display the following options "3", "7", "12", "16", "18"

Data Dictionary
Table Name	Console Platform
Field Name	Attributes
Platform_ID	Primary Key, Integer
Platform_Name	Text,25 characters, no validation, no input mask

o use of naming conventions

When creating any database using any DBMS tool there are is guidance and rules that should be followed to ensure that the database once set up is easy to manage and maintain. Some of the simplest are the naming conventions that should be adopted. When creating forms, reports, queries, and tables we should follow the below;

frm_Form_Name = form

qry_Query_Name = Query

tbl_Table_Name = Table

rpt_Report_Name = Report

o entity-relationship diagrams

An entity-relationship diagram is used to visually demonstrate the links between the intended tables, it will identify the primary keys that will be implemented and the foreign keys that will be used to link the tables. The image will use special symbols to represent the links and also the primary and foreign keys, these are as follows

ENTER IMAGE OF RELATIONSHIP DIAGRAM SYMBOLS HERE

o normalisation

As covered in the previous weeks, you will r through a normalisation process that requires you to break the tables down, this process should be demonstrated and presented, this could look something like below;

UNF: name, surname, address 1, address 2, post code, telephone number, person_id,

1st NF: person_id, name, surname

address 1, address 2, postcode, telephone

2nd NF : person_id, name, surname

house_id, address 1, address 2, postcode, telephone

3rd NF : person_id, name, surname

house_id, person_id, address 1, address 2, postcode, telephone

Try the following exersicse on normalisation. Thanks to seneca college
Exercise 1

Exercise 2
Exercise 3
Exercise 4

• user interface design:

o data entry/input – verification, validation, calculated fields, masks, directed input

It is again key to represent how you intend on the solution will work, but also its visual representation. your solution will need to be appropriate for the user to be able to input in to the system using data input screens, the layout of these need to be as straight forward as possible, if the user is unclear as to the inputs needed then the data going in to the system may jeopardise the content in the system already and essentially provide misinformation as an output. So creating mock-ups of the input screen will allow clients and test users to have input before the solution is created, saving time and also potential issues later. Within these designs the type of inputs and features that are going to be appearing need to be clearly identified too, for example, input masks, colours , verification areas, button names.

o reports – fields, queries, presentation of data, calculations

As with the design on the input forms that output needs to be designed and consider too. The use of the reporting element within a database and especially in the access tool enables users to get output in a representation that can enable them to give quick and informed suggestions to thier company or services.

So in the design of the reports key information such as the titles and calculated columns need to be identified.

o task automation – imports, updates, deletions

• extracting and presenting data:

o queries using multiple criteria, form values, and wild cards

When creating a query we are able to search using part of a criteria. In the screenshot below, you will be able to see that there are 3 entries about a beach.

When we move to the design view we can begin to construct our criteria using part of the criteria.

In this instance, you will be able to see that the criteria that we are looking at is asking the database to display any results that have "sand and *" (the * tells the database to allow for anything after). The result displayed will be,

So, in this example our planning would allow us to work out that we would need the following statement; Like "sand and*" to enable us to display the previous output.

o action queries

o calculated queries

A calculated query can be very powerful when dealing with a large amount of data that is required to have information calculated from it. In the following example we will use a sweet shop. The sweetshop has a large amount of sweets that it stocks and the cost of these sweets is recorded in the table against the sweets name. However, the owner would like a query that adds on a 10p profit to each sweet and to add the VAT charge too. This is easily achieved in access by doing the following.

As the image above demonstrates we will add another field, in this case, Net Cost. This is achieved by simply typing the field name followed by a : then we are now able to identify the field that we wish to take the value from, in this instance, it is "Sweet_Cost" in order to tell access this we need to enclose the name inside square brackets [ ]. Our calculation now moves on to the addition of the 10p, and then finally outside of our original brackets, we will now add the VAT calculation on the cost of the sweets that have had the 10 added.

So our criteria should read, Net Cost: ( [Sweet_Cost] + 10) * 1.2

Yes the eagle eyes will spot that we are adding 20% to the cost and because we are adding this value to the original value we put 1 in front of the 0.20 calculation.

So the output should look at pictured below;

Further video of how to create calculated fields

o reports

• design and use of test plans: to check correctness of data, functionality,

accessibility, usability.

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Interactive Speaking : Interact with others, shaping meanings through suggestions, comments and questions and drawing ideas together.

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Question and Answer - Verbal discussion with learners to quantify understanding

Product Evidence - Learners to create product evidence to demonstrate understanding

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

AO3 Analyse information about database problems and data from test results to optimise the performance of a database solution

Awarding Organisation Criteria:

Maths:

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Organising and Representing data: Drawing tables, charts and graphs

Formulae: using formulas, using words to express operations e.g. direction

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 6

T&L Activities:

Select and configure appropriate RDBMS and SQL tools to produce a database solution to meet

client’s requirements:

• creating, setting up and maintaining data tables

• creating links, relationships between data tables

Creating Lookup/Value Tables

• applying data validation rules

• generating outputs – user-generated queries, automated queries, reports

• user interface – navigation, data-entry forms, sub-forms

• automated functions

• populating the database:

o importing

o adding data

o manipulating data

Creating Composite Primary Keys.

• devising and using SQL statements to extract, manipulate and modify data

Here are some walkthrough solutions for Exams from previous years.

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Listening: Listen and respond to speakers ideas and perspectives, and how they construct and express meanings

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

GOs for Formative Teacher Assessment - Teachers give feedback to learners telling them specifically how to improve their visual demonstration of their understanding.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

AO3 Analyse information about database problems and data from test results to optimise the performance of a database solution

Awarding Organisation Criteria:

Maths:

Statistic extracting information: Tables, Diagrams, Charts and Graphs

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Collect and record data: Questionnaires, Observation, Tally

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 7

T&L Activities:

• Different types of testing: referential integrity, functionality, security.

• Selection and use of appropriate test data: erroneous data, extreme data.

• Recording appropriate test documentation.

When creating any product the key to its ultimate success is the appropriate testing that occurs throughout its development and at the point before release. This is done within database development also as it is essential to the integrity of the information that is created from the data contained within.

We use test plans to record the checks, tests, and features of the databases, this typically will be presented to users in a table format as displayed below.

Test Table

Test No	Type of test (N= Normal , R= Erroneous, X = Extreme)	Test data	Expected results	Screenprint(s) of the results	Results are not as expected, correction of the error and how, plus any addition screenprint(s)

Normal Tests = Normal, day to day entry,

Erroneous Tests = Data that is known to be incorrect to test error handling.

Extreme Tests = Data that is beyond the limits of input, ie. a name that is longer than the 50 field length permitted.

• Using testing outcomes to improve and refine a database solution.

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Listening: Listen and respond to speakers ideas and perspectives, and how they construct and express meanings

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Peer Assessment of Independent Work - Learners work in groups to assess each other's work and make recommendations for improvement.

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

AO2 Apply knowledge and understanding of database development terminology, standards, concepts and processes to create a software product to meet a client brief

AO3 Analyse information about database problems and data from test results to optimise the performance of a database solution

Awarding Organisation Criteria:

Maths:

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Collect and record data: Questionnaires, Observation, Tally

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 8

T&L Activities:

D Evaluating a database development project

The characteristics, concepts, impact and implications of testing methodologies to monitor and

evaluate database design, the database created, testing processes and success of the solution.

Evaluating a design against the given requirements:

• use and application of an entity-relationship diagram, data dictionary, normalisation

• coverage of functionality requirements and identification of any omissions

• identification of design strengths and potential further improvements to meet given

requirements.

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Writing: Write to communicate clearly, effectively and imaginatively, using and adapting forms and selecting vocabulary appropriate to task and purpose in ways that engage the reader

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Question and Answer - Verbal discussion with learners to quantify understanding

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO3 Analyse information about database problems and data from test results to optimise the performance of a database solution

AO4 Evaluate evidence to make informed judgements about the success of a database’s design and performance

AO5 Be able to develop a database solution to meet a client brief with appropriate justification

Awarding Organisation Criteria:

Maths:

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→

Week 9

T&L Activities:

Evaluating the application of test data to ensure that the database solution meets requirements.

• Different types of testing:

o normal test data

o erroneous test data

o extreme test data.

• Recording of actual results and analysis.

• Commenting on results.

• Test records:

o completion of test records

o taking of and storing screenshots of tests.

• Making use of testing outcomes.

• Using iterative processes to improve accuracy, readability and robustness.

• Identifying and recording which tests were successfully met and which test data issues

were not resolved.

Table Name

Description

Field Name

Data Type

Primary/
Composite Key (Y/N)

Foreign Key (Y/N)

Field Size
(if applicable)

Format/
Input Mask
(if applicable)

Validation
(if applicable)

Evaluating the software outcome against the given requirements.

• Strengths and weaknesses of the database:

o solution fitness for purpose

o intuitiveness and ease of use

o constraints of the database software used

o maintainability of the database

o extent to which database meets the given requirements.

Files that support this week

English:

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Writing: Write to communicate clearly, effectively and imaginatively, using and adapting forms and selecting vocabulary appropriate to task and purpose in ways that engage the reader

Listening: Listen and respond to speakers ideas and perspectives, and how they construct and express meanings

Assessment:

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Simple Self Assessment - Learners compare their work against success criteria given to them by the teacher and identify ways to improve the work done so far.

Question and Answer - Verbal discussion with learners to quantify understanding

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Learning Outcomes:

AO4 Evaluate evidence to make informed judgements about the success of a database’s design and performance

AO5 Be able to develop a database solution to meet a client brief with appropriate justification

Awarding Organisation Criteria:

Maths:

Collect and record data: Questionnaires, Observation, Tally

Organising and Representing data: Drawing tables, charts and graphs

Statistic extracting information: Tables, Diagrams, Charts and Graphs

Stretch and Challenge:

E&D / BV

Homework / Extension:

ILT

→