Category: Base

Set a field as primary key

To set the primary key, right-click the grey box to the left of the field you want to set as primary key.

Check the ‘Primary Key’ option and a yellow key symbol will appear in the grey box.

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

It’s a good idea to set the primary key to an ‘Integer’ type and select Autovalue option to ‘Yes’, so that the table auto-populates with ascending unique values.

Index a field

An index is a copy of selected columns of data from a table, allowing more efficient searching of records.

For example, if we have a big table that contains fields: First Name, Surname, Gender, Age, Address. If we wanted to run a query that finds all people named ‘David’ of a certain age range, this could take a while as the computer has to search through thousands of records.

If we were to create an index that contains only the First Name field in alphabetical order, and set up the query to search only that index it will find all of the records containing ‘Dave’ within the specified age range far more quickly, even instantly.

Click the grey box to the left of the field you want to index. Then click on the Index Design icon.

Click the New Index button, choose the Index field from the drop-down menu and sort ascending or descending.

If you don’t want duplicates to be allowed for the indexed field, check the ‘Unique’ box.

Add a field to an existing table

To open an existing table, click ‘Tables’ in the Database area of the main window. Right-click the table you want to open in the ‘Tables’ area. Select ‘Edit’ from the drop-down menu.

Once you have saved the table for the first time in Base, you cannot add fields in-between existing ones. You can only add a new field below the last entered field. Use the same process as earlier to enter a field, set the type and change the properties.

Change column width

To change the column width in a table, click the vertical line joining columns and then drag right or left.

[the_ad id=”12397″]

Create a table

To create a table, click ‘Tables’ in the Database area. Then click ‘Create Table in Design View’ from the Tasks area to open table design window.

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

In this example, we’re going to create a table that holds data on company employees.

It will contain basic details on employees such as name, gender, start date, end date, address, phone number and department.

As mentioned earlier, it is a good idea to separate different types of data into different tables, so we won’t include salary data or bonus payments in this table.

We are using design view to create the table as it gives you more versatility than the ‘wizard’ option.

Specify fields with data types

Click on a cell in the Field Name column and give the data field a name.

Click in the next column ‘Field Type’ and select type of data from drop down menu. If you want the table to auto-populate with unique ascending values, select Autovalue ‘Yes’.

Finally, type a description of the data field in the ‘Description’ column.

Add desired fields to Field Name column.

Select type of data e.g. Text, Integer (whole number), Number, Date, Time, Yes/No, etc.
If it’s a field that can’t be left blank (null), click ‘Entry required’ drop down and select ‘Yes’

Name a table

Click the disc icon to save and name table. Type in the name you want to give the table and click OK to save the table.

Apply field property settings

Click on the field you want to edit. Adjust settings in the Field Properties area.

To specify the length of field entry, e.g. 2 digits, enter ‘2’ in the box.

Set a default value and change formatting if required.

Consequences of changing data types and field properties

Once the database is created and tables have been populated, changing the data types and field properties can have a range of consequences for the database.

Links are created between tables by creating relationships. If properties are changed this can invalidate the links, causing problems with queries and reports.

The more complex a database becomes, the more serious the consequences of changing data types and properties can be.

Spend extra time and thought at the beginning planning your database tables so that changes are unlikely further down the line.

[the_ad id=”12397″]

Add or delete records in a table

To add a record, click a cell in the table.

Enter valid data into the cell, for instance, for Start Date, make sure you enter it in a date format.

If you move down to the next row, and the ID is set to Integer auto-value, the next integer will automatically appear.

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

To enter data in another cell, simply click on the desired cell and type the data.

To delete an entire row of records, right click the grey box to the left of the row and select ‘Delete Rows’ from the drop-down menu.

Delete or modify data in records

To delete data in a record, either left click on the record then delete manually, or right click the cell and select ‘Delete’ from the drop-down menu.

To modify data, simply delete existing data in the record and type in new data.

View a table in design mode

Right click the table, query, form or report in the relevant area and select ‘Edit’

This will open the table, query, form or report in a design mode window

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

Navigate between records in a table, query, form

Use the navigation toolbar to navigate between records in a table, query or form.

Use the number entry box to jump straight to a specific record by typing the number and pressing the enter key.

Click the ‘Next Record’ and ‘Previous Record’ arrows to move one record at a time.

Use the ‘Last Record’ or ‘First Record’ arrows to skip to first or last record in the table, query or form.

Sort records in a table, form, query output

Click the ‘Sort’ icon in the navigation toolbar to sort records in a table, query or form.

Choose the Field you want to sort the records by, from the ‘Field name’ drop down menu.

Select either ascending or descending from the ‘Order’ drop down menu.

[the_ad id=”12397″]

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′]