Author: paul.gray

Tables

Tables are used to store records (pieces of data) within a database.

Records are entered into tables under fields that are created when editing the table. Fields are similar to the headings given to columns in a spreadsheet.

An unlimited number of tables can be created in a database, containing different types of data.

[the_ad id=”12355″]
[the_ad id=”12356″]

For example, a shop may keep records of stock, employees, sales, revenue, etc. all in the same database, but in various tables with different titles.

Open, save and close a table

To open a table in an existing database, click on ‘Tables’ in the ‘Database’ area of the main window.

Then double-click on one of the tables in the ‘Tables’ area.

If you make changes to any records in the table, click the disc icon to save it.

To close the table, click the cross in the upper right corner of the table window.

Queries

Queries are a method of extracting data from the tables within a database. Data can be extracted from more than one table at a time using a query, as long as the tables have a relationship created between them.

For instance, a company may use a query that searches through employee data to find out who is paid the most, or which employees are paid more than a certain amount.

There are 3 ways to create queries in LibreOffice Base. They are design view, wizard and SQL (Structured Query Language). SQL is the most advanced of these and the most versatile.

Open, save and close a query

To open a query, first click on ‘Queries’ in the ‘Database’ area of the main window.

Then double click the relevant query in the ‘Queries’ area to open it.

Forms

A database form is a user interface that helps with data entry and maintenance.

For instance, an engineering company may use a database to log faults with machinery. They can create a form that allows engineers to enter relevant details of the fault in a clear, well laid out form, rather than trying to enter them directly into a table which is more difficult and a slower process.

Forms can also be used to allow a quick view of records within a table. They can be displayed in order, one record at a time, or filtered to show records with specific data only.

A form could be described as a window into a database where people can view and edit records in a simple way. Changes to data have to be saved before exiting forms.

Open, save and close a form

To open a form, first click on ‘Forms’ in the ‘Database’ area of the main window.

Then double click on the relevant form in the ‘Forms’ area.

To save the form click the disc icon to ‘Save as’.

Choose ‘Save copy as’ then select a location and name the file.

Reports

A database report is a formatted display of records from a table or the results of a query.
Reports provide users with information that can help decision making or analysis, in an easy-to-view format.

For example, a bank could use database reports to list monthly loan summaries for customers. They can use the report to highlight which customers are behind on their repayments. They could break down the data further by creating reports linked to queries that break down the number of customers that are behind on payments for large loans, compared to those for smaller ones.

Output parameters and restrictions can be applied to reports, as well as grouping data so that it isn’t repeated. This is explained in more detail later on in the course.

Open, save and close a report

To open a report Click on ‘Reports’ in the ‘Database’ area of the main window.

Then double click on the relevant report in the ‘Reports’ area.

The report will open in a LibreOffice Writer window.

To save the report as a text document, click the ‘Save’ icon and name the file and save to a specified Folder.

To close the report document, click the cross in the top right corner of the window

[the_ad id=”12397″]

AND

A logical operator that is used in queries to state that values must satisfy all of the set criteria.

Autovalue

A feature that can be attributed to data under a certain field (often the primary key) of a table that will automatically create a unique number for each entry. Best used with ‘integer’ data type.

[the_ad id=”12355″]

Field

The separate areas/titles in a table, which form the columns. For instance, First Name may be one field, Surname may be another.

Foreign Key

A key from a second table that is related to the first. E.g. Table A may have Item Code as the primary key, linked to ID number in table B which is the foreign key

Form

An on-screen user interface for entering, modifying or viewing data records.

Integer

A positive or negative whole number or zero, with no decimals.

OR

A logical operator that is used in queries to state that values must satisfy at least one of two or more criteria.

Primary Key

A field which uniquely identifies a record, e.g. Staff ID number or Item Barcode. The database creator can specify which field to set as the primary key. If none of the existing fields have unique identifiers, the autovalue feature can be set for integers to create a unique ID for each record.

[the_ad id=”12356″]

Query

A method of extracting specific data from one or more tables. This is done by using the Wizard (simplest method) or Structured Query Language (SQL) (complex method).

Record

Data that is stored in a table, under a particular field. Records form the rows of a table.

Relationships

A method of linking records between tables to save repeating the same data. E.g. Names in one table, phone numbers in another, linked by staff ID number. Data cannot be altered in a secondary table without the primary table being altered first to preserve integrity.

Report

A report is an output displaying specific records from a database. Reports can display data directly from tables or queries and exports them to a document which can be saved or printed.

Table

A table contains fields (columns) and records (rows), that store data. There is no limit on the number of tables that can be created within a database.

Wildcard

Characters including * and ? that are used in searches or queries to represent one or more other characters.

[the_ad id=”12397″]

Open a database application

To start LibreOffice Base, search from the ‘Start’ menu or find the LibreOffice folder and click the LibreOffice Base icon.

[the_ad id=”12355″]
[the_ad id=”12356″]

Open an existing database

To open an existing database, choose the option ‘open an existing database file’ from the wizard, which is the first screen you see.

Choose a file from the drop-down menu of recently used files or click ‘Open’ to select a file from your drive.

Click on the file you want to open then click finish to open the database.

Create a new database file

To create a new database file, click on ‘File’ from the main menu bar, then click ‘New’ from the drop-down menu. Click ‘Database’ from the sub-menu. This will open the ‘LibreOffice Database Wizard’

In the LibreOffice Database Wizard, select ‘Create a new database’ then click ‘Next’

From the ‘Save and proceed’, select ‘Yes, register the database for me’. This allows you to import data into other applications. Select ‘Open the database for editing’, then click ‘Finish’.

Choose a location on your drive and name the file saving it as file type ‘ODF Database’. Click ‘Save’.

Display, hide built-in toolbars

The option to display or hide the built-in toolbars can be controlled by clicking ‘View’ from the main menu bar.

Then click ‘Toolbars’ from the drop-down menu.

You can then check or uncheck various toolbars to display or hide them respectively.

Use available Help functions

Click ‘Help’ on the menu bar.

Click ‘LibreOffice Help’ or press ‘F1’ key on the keyboard.

This will open a web browser window with online help.

[the_ad id=”12397″]

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)

[the_ad id=”12355″]
[the_ad id=”12356″]

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.

[the_ad id=’12397′]