What is a database?

A database is a collection of data organised for storage and management. Databases allow information to be stored in a way that makes it easy to access and update. Databases consist of one or more tables. The main benefit of databases is their ability to handle searches and generate reports, for example, identifying the best-selling albums from sales records.

Database structure: tables and fields

Tables form the core of a database, organising data into rows and columns for clarity.

Key components of a table

• Fields - These are the columns in a table, each designed to store a single type of information. Every field has a name that describes its content, such as "Artist" or "Price".
• Records - These are the rows in a table, where each row represents a complete set of data for one item.
• Primary key - This is a special field that uniquely identifies each record, preventing duplicates. It is often a number, such as an "ID" field that automatically assigns a unique value to each entry.

Tables are created in a design view, where you define the fields and their properties before adding any data.

Data types and properties

Each field in a table must have a data type, which specifies the kind of information it can hold.

Common data types

• Text
• Number
• Currency
• Date/time

Fields also have extra properties. For example, a date field might use a "Short Date" format and an input mask like "00/00/0000" to ensure dates display consistently.

Datasheet view for data management

Once fields are set up, you switch to datasheet view to work with the actual data. This view resembles a spreadsheet, with fields as column headers and records as rows.

How to use datasheet view

• Input new data by typing into empty cells.
• Amend existing data by clicking and editing a cell.
• Delete data by selecting and removing rows or individual entries.

Forms for user-friendly data entry

Forms offer a clearer and more intuitive way to add or edit data compared to datasheet view. They are designed to be user-friendly.

Features of forms

• Forms are linked to a specific table, so any changes made in the form update the table directly.
• They include elements like text boxes for typing, tick boxes for yes/no options, and drop-down lists for selecting from predefined choices.
• Users can navigate through existing records using buttons (e.g., next or previous) and edit them, or create entirely new records.

For example, a form for album stock might have labelled boxes for artist, album name, price, and release date, with a navigator showing the current record number out of the total.

Sorting data in databases

Sorting rearranges records in a table based on the values in a chosen field.

How sorting works

• Sorting can be applied to numerical fields or text fields (into alphabetical order).
• Sorts can be in ascending order (lowest value first) or descending order (highest value first).

Filtering data to show specific records

Filtering allows you to view records that match a specific condition.

Applying filters

• Set a condition, such as "Price less than £7", and the database displays only matching records.
• Combine multiple filters, like "Price less than £7" and "Date of Release after 2019".
• Remove the filter to view the full table again.

Searching with queries

Queries allow you to search a database for records matching specific criteria. They are similar to filters but more powerful, as their results can be saved or used in reports.

Types of queries

• Simple queries - Search for records meeting one condition, e.g., "Price < 7" to list albums under £7.
• Complex queries - Use logical operators to combine conditions:
  • AND - Both conditions must be true, e.g., "Price < 8 AND Artist = 'The Melodies'".
  • OR - At least one condition is true, e.g., "First name = 'David' OR First name = 'Susan'".
  • NOT - Excludes records, e.g., "Price < 8 NOT Artist = 'The Crooners'".

Presenting data with reports

Reports format and present data from tables or queries in a professional way, often for sharing with others.

Features of reports

• They display query results, such as a list of cheap albums with their names and prices.
• Reports can include calculations (e.g., total sales), charts, or graphs to analyse data visually.
• They help in comparing values.

For example, a report might use a table to list albums under £7, with annotations explaining its purpose, making it ideal for customer recommendations or business analysis.