Why are databases used?

The purpose of database software

The purpose of database software is to store, manage and extract a large amount of organised information for one or more users.

Know why organisations use databases to:

● improve productivity

● make decisions

● present information

● interpret data

● perform calculations

● manage large datasets.

Uses of a database

Examples of uses of a database:

● health (data – doctors, patients, appointments)

● employment (data – name, payroll, department)

● agencies (data – client, services, reservations)

● sale of goods (data – orders, goods, invoices)

● libraries (data – books, loans, members)

● police (data – offenders, crime, officers).

Tools and techniques

used in a database Tools and techniques include, e.g.:

● table structures

Field characteristics

Validation rules

The use of validation rules in a database is key to the restriction of data entry into the database itself. A validation rule allows the creator of the database to specify a rule/condition that a field must-have for the data that is entered to be able to be saved. An example of this would be the use of a validation rule that specifies that the field will only accept a value of either M or F for male or female and no other letter or sentence.

Indexing

Indexing is a feature that enables faster searching and data retrieval of data from a database with a large amount of data in it. In most cases, indexing is automatically set up on fields that are set as primary keys.

Records

A record is a piece of data that is entered into a database table this could be information on an employee or an item in a shop's stock.

Relationships

One-to-One 

One-to-Many

Many-to-Many 

Forms

Sorts

Queries

Using databases to improve working practices

Improving productivity and accuracy in a database, e.g.:

● creating and presenting financial reports

● record-keeping

● backing up data

● collaborative working

● searching and planning information.

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

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

Clarifying Learning Objectives - Using coloured discs and/or peer explanation, check to ensure that learners have understood the learning objectives.

Just a Minute - At the end of the lesson teachers ask learners to summarise their learning. The comparison of these summaries against learning objectives informs planning.

Learning aim A:

Investigate online services and online communication

1A.1 Identify the uses of databases and how the tools/techniques are used in two different databases.

2A.P1 Explain the uses of databases and how the tools/techniques are used in two different databases.

2A.M1 Review how the tools/techniques are used in two databases to improve productivity, accuracy and usability.

2A.D1 Discuss the strengths and weaknesses of the databases.

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

Collect and record data: Questionnaires, Observation, Tally

Databases and relationships

Types of databases, e.g.:

● local

 When looking at local databases, we tend to use software that's installed onto a PC. Examples of local software Would be Microsoft Access. This software will allow a user or developer to create a database that sits on the computer for only the use of those people accessing that computer.

● online (web)

When we look at databases that are web-based, we can use websites like Amazon or eBay As examples. These websites have a large amount of information that the website will pull up on using a online database. There are a number of different web-based databases around, one of which is MYSQL.

● flat file (contains a single table of information)

When we refer to a flat-file database, this usually represents a database that has only one table. Within this table, every piece of data is entered and kept. This is not the most efficient use of a database as this will allow for data duplication and data redundancy to occur.

● relational (uses common identifiers found within a data set consisting of two or more related tables).

Relational Databases are the preferred type of database that is created by developers. These sorts of databases will allow for fewer bits of data to be duplicated or made redundant. These databases are much more efficient than Flat file databases.

Types of relationships, e.g.:

● one-to-one is a relationship between one record in the first table that corresponds to exactly one record in the related table

● one-to-many is a relationship where each record in the first table may have many linked records in the related table, but will still have only one corresponding record in the first table

● many-to-many is a relationship where each record in the first table may have linked records in the related table and vice versa.

Learning aim B: Design a relational database

Designing a database

Designs should be based on the intended purpose and user requirements defined in a brief to solve a problem. Design documentation, including:

Hardware, software and other resources required

When using or creating any database we need to consider the tools that we need. Our first area to look in to is the "Hardware". Hardware is anything that you can physically touch this could be any of the following;

• Desktop/Tower PC or Laptop
• Mouse 
• Keyboard
• Modem/Router

These all play an important role in enabling us to be able to create or use a database, without them, we will struggle.

Next, we must look at the "Software" this is something that we cannot touch and is most often computer programs and code. There are a number of software elements that we require to create or use a database these are;

• Operating Systems (Windows 11, Mac OSX)
• Microsoft office suite
• Microsoft Access
• Web browser

Entity Relationship Diagram (ERD), including entities, attributes and relationships

What is an Entiry Relationship and how do we show this in a diagram? An entity-relationship is the link between database tables and different types of links that are needed.

Within a database, we can create 1:1 (One to One), 1:M (One to Many) and M:M (Many to Many) links.

The links will indicate the following;

1:1 - One record may appear in one table only

1:M One record may appear many times in a table

M:M Many records may appear in a table many times. 

Validation, Verification Procedures and Data Dictionaries

Input and output screens/forms and reports

Constraints (e.g. hardware and software availability)

There are a few constraints when it comes to the creation of a database, one very obvious example is COST. The tools that you are likely to be using during this unit will be Microsoft Access. This product is not a free one and requires either a one-off payment or a yearly subscription to Office 365. However, this is not the only database out there, you have Oracle, MYSQL and others that are either free or have a very large cost.

All these software applications require some sort of operating system to run on, and, computers of a specific hardware specification.

Test plan with test data to test functionality

A brief outline of alternative design ideas.

When creating your work/designs toward the solution of the assignment you might want to consider the options that you have given, could they be changed, improved or presented in an alternative way. Once you have shown these alternative designs discuss what they are and why they are different to your initial designs. This will enable the discussion later why you selected the alternative designs over your initial designs when it comes to the creation of the database.

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

Sentences with different forms: statement, question, exclamation, command

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.

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

Capturing Progress - The teacher matches activities to learning objectives. Learners assess and record progress as they complete the planned activities.

Learning aim B: Design a relational database

1A.1 Identify the uses of databases and how the tools/techniques are used in two different databases.

2A.P1 Explain the uses of databases and how the tools/techniques are used in two different databases.

2A.M1 Review how the tools/techniques are used in two databases to improve productivity, accuracy and usability.

2A.D1 Discuss the strengths and weaknesses of the databases.

1B.2 Identify the purpose and user requirements for the database.

1B.3 Produce a design for a database with guidance, including a single table database structure with a data entry form.
2B.P2 Describe the purpose and user requirements for the database.

2B.P3 Produce a design for a relational database, including:

● a database structure

● a test plan.

2B.M2 Produce a detailed design for a relational database, including:

● alternative designs

● a detailed database structure

● test data.

2B.D2 Justify final design decisions, explaining how the relational database will fulfil the stated purpose and user requirements, and any constraints in the design.

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

DESIGNING A DATABASE

Designs should be based around the intended purpose and user requirements as defined in a brief to solve a problem. Design documentation, including:

● validation and verification procedures

● input and output screens/forms and reports

● a brief outline of alternative design ideas.

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

DESIGNING A DATABASE

Designs should be based around the intended purpose and user requirements as defined in a brief to solve a problem. Design documentation, including:

Constraints (e.g. hardware and software availability)

When using or creating any database we need to consider the tools that we need. Our first area to look in to is the "Hardware". Hardware is anything that you can physically touch this could be any of the following;

• Desktop/Tower PC or Laptop
• Mouse 
• Keyboard
• Modem/Router

These all play an important role in enabling us to be able to create or use a database, without them, we will struggle.

Next, we must look at the "Software" this is something that we cannot touch and is most often computer programs and code. There are a number of software elements that we require to create or use a database these are;

• Operating Systems (Windows 11, Mac OSX)
• Microsoft office suite
• Microsoft Access
• Web browser

Test plan with test data to test functionality

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

Learning aim C: Develop and test a relational database

Software tools and techniques to develop a relational database

Create and edit:

● single and multiple table structures with appropriate field characteristics, including

o field names

o field data types, e.g. alphabetic (text and memo), numeric (number, currency, and date/time), alphanumeric (text and memo), logical (yes/no and true/false), web (hyperlink), lookup wizards

o field sizes (e.g. byte, integer, long integer, single, double and decimal)

o field formats (e.g. fixed and decimal places)

o default values

● validation rules and text, which applies to a range, format and length for different data types including input masks

● indexing (e.g. primary key, foreign key)

● create new records (e.g. populate tables with data manually or import a data set from an external source (text file or spreadsheet))

● edit and delete existing records of data

● create, edit and delete relationships

● use wizards.

Sentences with different forms: statement, question, exclamation, command

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.

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

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

Just a Minute - At the end of the lesson teachers ask learners to summarise their learning. The comparison of these summaries against learning objectives informs planning.

1C.4 Develop a database with a realistic data set with guidance, including:

● a single table structure

● a data-entry form.

2C.P4 Develop a relational database with a realistic data set, which includes:

● two tables

● sort records

● data-entry forms.

2C.P5 Test the functionality and purpose of the relational database for functionality, repairing any faults.*

2C.M3 Develop the database demonstrating awareness of users’ requirements and accuracy. To include:

● customised dataentry forms

● queries and output data reports

● onscreen navigation and guidance

2C.M4 Gather feedback from others and use it to improve the database and test any additional functionality, repairing any faults.*

2C.D3 Refine the database solution using automated tools and techniques to improve productivity, accuracy and the presentation of output data, taking account of user feedback.* 1C.5 Test the functionality of the database and repair any faults with guidance.*

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

Money: Budgeting, Estimating, Rounding to nearest pound, using correct symbols, calculating costs using a calculator

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

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

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

Sentences with different forms: statement, question, exclamation, command

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.

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