Author: paul.gray

Libreoffice Base doesn’t allow you to create a ‘lookup’ list in a table in the way it does in MS Access. We will have to use a form to do the same thing.

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

Right click on the Jobs Entry form and select ‘Edit’

This will open up the design mode for the form.

We’re going to create a ‘lookup’ list for the Staff Number field, so select the box by holding ‘Ctrl’ on the keyboard and left clicking the text box.

Now, right click and hover over ‘Replace with’ and select ‘List Box’.

A list box allows you to create a drop-down list so people can enter only certain specified values.

Next, right click and click on ‘Control…’

This will open the control dialogue box.

Click the Data tab, make sure the Data field is ‘Staff Number’, Input required ‘Yes’ and select ‘Valuelist’ for Type of list contents.

Click on List content. If we need the option of a null value we’d press Shift+Enter together on the keyboard to leave a blank line, but null values aren’t an option for this field.

Type in the first value 101, then press Shift+Enter together to skip to the next line.

Then type 104, press Shift+Enter and so on until you have the six possible options.
Press enter when finished to store the list.

Click the drop down menu for List content again, select all the values and press Ctrl+c to copy the values for the next step.

Click the ‘General’ tab at the top of the dialogue box.

Click the List entries drop-down menu and press Ctrl+v to paste the list values.

Select ‘Yes’ from the Dropdown option.

Type ‘6’ into the Line count box, as we have 6 options in our list.

Close the properties dialogue box.

Click the Design Mode icon to toggle out of design mode, allowing you to make changes to records.

Click the Data source as table icon to display the Jobs table on the top of the window.

Click the New record icon to add a new record.

Use the drop-down box for Staff Number to check that you can only select the values we set in the list box.

To modify the lookup/list, just go back to form design mode, right click the list box, select control then edit the lists under the data and general tabs.

To delete the lookup/list, go into form design mode, Ctrl+right-click the list box, select Replace with and change it back to a text box.

A combo box allows you to include a list of possible values to enter (like a list box), but also allows you to type an entry.

Only use a combo box if you don’t mind other entries that aren’t on the list, i.e. maintaining data integrity isn’t crucial for that field.

To specify a combo box, ctrl+right click on the entry box, select ‘Replace with’ and choose ‘Combo Box’.

Right click the combo box and select ‘Control…’

Similar to the previous example for List box, go to the Data tab, choose Valuelist and add the specific values.

Copy those values and paste them in List Entries under the General tab.

Close the control and switch from design mode to entry mode.

Try typing in a value that isn’t in the drop-down menu.

Notice that unlike a List Box, a Combo Box will allow you to type in a value that isn’t on the list.

This is good for fields where you may have lots of common entry values, but occasionally may need to enter a completely different value or text string.

E.g. an address form where most live in the 3 closest towns, but some live further afield.

[the_ad id=”12397″]

Create a form for inputting jobs. Go to forms in the main window, then click on ‘Use wizard to create form’.

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

Select all of the fields from the ‘Jobs’ table, select the arrangement you prefer.
Name the form ‘Jobs Entry’ then close it.

Open the form by double clicking on it in the main window.

To enter new records, click on the new record icon in the form navigation toolbar.

You can now enter new records directly into the form.

Notice that the date input box will only accept the default input mask that we set earlier. It will either change the entry to that form or use the same entry as the previous record.

For this module we are going to create a database for a car repairs garage.

We are going to set up some tables and a form in this section to demonstrate the principles.

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

First, create a new database in LibreOffice Base called ‘Car Garage’.

Then select tables and click create table in design view.

Add the fields shown, setting the Car Reg Number field as the primary key by right-clicking on the grey area to the left of the field and selecting primary key.

Save the table, giving it the name ‘Car and owner details’.

Open the table and enter some data.

Notice that the account settled field, which was set to ‘Boolean Yes/No’ data type has a checkbox.

Create another table with the fields shown, setting ‘Staff Number’ as the primary key.

Populate with similar data to that shown below.

Name the table ‘Mechanics’ and save the data.

Create one more table in design view and add the fields to the left, setting ‘Job No’ as the primary key and selecting ‘Autovalue’ so it automatically creates unique job numbers.

For the ‘Date’ field, in the properties box below, click the … symbol next the Format example box.

This will open a new dialogue box.

Here you can select from a selection of default formats for entering the date records.

These input formats are known as input masks. We will look at these in more detail later on in this chapter.

For now, select the top format from the list.

Save the table as ‘Jobs’ and close it.

Open the table ‘Jobs’ from the main window and enter data.

Structured Query Language (SQL) is used to manage data within a database.

It uses relational algebra to create commands that retrieve or maintain the data.

Some examples of commands are CREATE – to create tables, RENAME – to rename tables, SELECT – to select data records, INSERT – to insert data records and DELETE – to delete data records.

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

To see examples of SQL code, open the database ‘Company Employees’ (created in module 1 or available for download).

Right click on one of the queries, and select ‘Edit in SQL mode’

This brings up a new window displaying the SQL code for that query, with the commands highlighted in blue text.

The main use for SQL is in querying databases to retrieve information.

All queries created in LibreOffice Base use SQL, even ones created in design mode.

The average user won’t need to use SQL code, it’s mainly used when dealing with highly complex queries or to write entire systems.

It’s useful to know about it, but you don’t need deep understanding for this module.

Logical Design – must be carefully designed to incorporate all the data and applications needed, including possible future changes and developments. Define the output(s) required, then work backwards to include all the elements needed.

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

Database Creation – set up tables, relationships, forms for inputting data, queries and reports. This needs someone with good technical understanding of databases.

Data Entry – data can be inputted using the forms created in the last step. This doesn’t require technical knowledge if done manually, but can be a long process. Sometimes data can be imported from other sources.

Data Maintenance – this is an important step to maintain integrity of the data. For a large database, a database administrator will be needed to add new data and delete obsolete data.

Information Retrieval – information can be retrieved via queries, forms and reports. Depending on the applications of the database, users may be restricted to using the queries and reports that are provided or they may be able to create their own. Larger organizations may hire database managers to use SQL for complex functions.

Databases are systems used for the storage, maintenance and retrieval of data.

In the modern ‘internet age’, databases are used widely to power ‘dynamic websites’, meaning prices, product details, mailing lists and other large groups of data can be easily updated and searched.

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

Website content management and customer relationship managements (CRM) systems use databases to improve services and marketing.

As well as commerce and service provision businesses, databases are also frequently used in other industries such as manufacturing, to facilitate logistics, inventory, distribution and invoicing. These are called enterprise resource planning databases.

For example, a large e-commerce website that sells a range of products will probably use:

– A dynamic website powered by a relational database that contains prices, product details and specification, reviews, etc. Data can be filtered and retrieved easily using queries that can be built-in to the website’s User Interface (UI).

– A website content management system which uses a simplified database interface so data can also be updated and changed easily by a site administrator without much technical knowledge.

– A customer relationship management system that tracks metrics such as number of visitors, time spent on site, purchases made, etc. This data can be retrieved and reported to provide insights for the business owners on how effective sales and marketing efforts are, for instance.

Hierarchical databases are designed using a ‘tree structure’, that has groups of parent/child relationships. They are fast to create and easy to use, but limited and inflexible as they contain lots of one-to-many relationships.

Relational databases consist of a set of related tables. They are flexible as they can be rearranged in various ways without having to reorganise the tables. They are fairly easy to create, maintain and make data retrieval simple.

Object-oriented databases use object-oriented programming languages. Each object belongs to a class. These types of database are limited as they don’t all support Structured Query Language (SQL), thus making data retrieval difficult.