Understanding Databases
Key Concepts
A database is a collection of data stored in tables to be easily accessed, managed, and updated.
Examples might be a shop’s stock inventory or airline booking system.
Tables are split up into records (rows) and fields (columns)
A database offers more complex ways of using data than spreadsheets and data can be entered via forms.
A database form is a user interface that allows users to enter new data records and modify existing ones.
Forms also allow a quick and simple way to view the records without searching through the entire table.
Forms can be searched with a sophisticated search tool, built-in to the software.
Database Organization
Each table should only contain one subject type
You can create other tables within the same database to contain gender, age, phone numbers etc.
Fields are the columns in a table, and are given different titles, depending on the data contained in the records (rows).
Fields have set data types (e.g: text, number, date/time, yes/no) and set properties (e.g: field size, format, default value)
Databases have one primary key which is a unique ID and is set for the main table in the database.
The primary key is numeric data and is often set as an integer (whole number).
You can choose which field to make the primary key, so you can use existing unique item codes or staff ID numbers, for instance.
Example
An example of a database structure could be for a sports shop.
They may have a table that lists general details of stock, with fields including ‘Item code’ – the primary key, ‘Type of equipment’, ‘Cost’, ‘Size’, ‘Location in store’ etc.
In another table they may list other data such as ‘Brand’, ‘Model’ etc.
Another table may hold records listing sales figures for each item.
It’s best to create different tables for different data/fields.
By separating out the data into different tables, it is possible to filter the data more easily using a ‘Query’ which is a way of extracting data from a database.
Relationships can be built between the tables that link the data together, in this case by the item code, for example.
Another table could hold information on employees, which would be completely separate from the stock data. There is no limit on the number of tables you can create within a database.
An index can be created from a table, allowing data to be sorted by different fields.
Indexes allow you to easily locate data without the need to search each row.
Relationships
In a relational database, different tables can be related to each other.
Relationships between tables prevents duplication of data, meaning only a single subject type is used in each table.
The most common relationship is to relate primary keys between tables
You can also create relationships between fields using the relationships tool
Relationships allow you to extract data from more than one table by linking the primary key from one table to a foreign key from another. E.g. you can extract names from one table and phone numbers from another.
This can be used when extracting data from more than one table to show in a report.
Reports are database outputs that display data in a customisable user-friendly format. Relationships make it easy to display data from various tables.
You can choose which fields and records to include in a table and also apply functions to show things like sum totals and averages.
An example of a database report could be a quarterly financial statement.
Operation
Professional databases are normally designed and created by database specialists.
Data entry and information retrieval are done by users – e.g. a flight booking clerk or customer will fill out a form for data entry and the airline will retrieve the information.
A database administrator provides access to specific data for users. They are also responsible for data recovery in the event of a crash or error.
