Database Applications

Logical operators

Analysing two values to return either true or a false result

What are the logical operators

AND, OR, BETWEEN

Comparison operators

Comparing a value against other data when queried

What are the comparison operators?

<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
=	Equal to
<>	Not equal to

Database

Collection of data or information which is held together in an organised way

Where might databases be used?

• Schools

• Police

• Government

Table

structure consisting of organised rows and columns made up of records

Record

Collection of data items relating to an object or person as a row on a table

Field

Part of a record that holds a single item of data, represented by a column

Key field/ primary key

Used to uniquely identify a record e.g Customer ID

Foreign key

primary key from another table, linked to a column

Query

Searches and filters data in a database that satisfies the criteria

Form

Collects data to create, modify or view records

Report

Presents results from queries in an effective manner

Macro

Small programs to perform a repetitive task automatically by executing code when clicked (button)

Relationship

Link two or more tables in a database

Referential integrity

Prevents addition of a new record to ensure data is consistent

Data redundancy

When data is repeated unnecessarily

Data integrity

The correctness, reliability and accuracy of the data

How do relationships work?

link two tables with a common field, the key field from one table is stored as the foreign key another

How are relationships defined?

One-one, one-many, many-many

Why do we import data?

gives more flexibility in handling data

Data validation

checking input to ensure data entered is sensible, reasonable and within acceptable limits

Why do we use data validation?

to ensure data integrity and reduce errors, inconsistences or mistakes

Presence check

Ensure it is present in the field and not left blank e.g compulsory field in a form

Length check

Ensure data is the correct number of characters e.g phone number be is 11 digits

Type check

Ensure it is the correct data type e.g Name should be text only

Format check

Ensure it is in the correct format that matches a predefined pattern e.g Postcode should be AABB ABB

Range check

Ensure it is within upper and lower limits e.g Percentage should range from 1-100

Big data

large amounts of data that have the potential to be mined for information

What are the 3 V’s

volume, variety and velocity

Volume

amount of data stored by organisations, large amounts of data require large storage capacity hardware

Vareity

different types and varieties of data, in many formats which can be structured, semi structured or unstructured

Velocity

speed data can be processed, large volumes need more powerful computers to process data quicker

Data analytics

analysing large volumes of data to produce useful information

Need for data analytics

beneficial to organisations for making decisions and planning for future growth

What makes data analytics harder?

different formats makes preparing and processing the data more challenging

What is used to speed up analytics?

Specialised software such as data mining are designed to process vast quantities

Data type

defines the type of data associated with the field

Text/ string

a mixture of letters and numbers e.g address

Number

Numeric data that requires mathematical calculation e.g telephone number

Date/Time

Dates and times e.g 9/12/24

Currency

Monetary data that includes 2 decimal places e.g £34.56

Autonumber

When a new record is created the key field generates a unique ID in a sequence e.g 1, 2, 3

Boolean

Binary value that can either be yes/no

OLE Object

Object Linking and embedding, created using a software package other than a database e.g spreadsheet

Hyperlink

Link to a web address e.g email address

Attachment

A file attached to a record e.g image

Calculated

The result of a calculation produced from another field e.g x + y

Lookup Wizard

A value can be selected from a predefined list e.g dropdown menu