Advanced Database Lessons

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. Website content management and customer relationship managements (CRM) systems use databases to…

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. Database Creation – set up tables, relationships, forms for inputting data, queries and reports. This needs someone with good technical understanding…

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 –…

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. First, create a new database in LibreOffice Base called ‘Car Garage’. Then select tables and click create table in design view. Add…

Create a form for inputting jobs. Go to forms in the main window, then click on ‘Use wizard to create form’. 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.…

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. Right click on the Jobs Entry form and select ‘Edit’ This will open up the design mode for the form. We’re going…

In Base you can only create input masks in a form (not a table as in MS Access). We are going to edit the date field to only allow dates to be inputted in the format DD** MMM YY (e.g. 21st NOV 15) First we must open the Jobs table in design mode (right click…

Open the ‘Jobs’ table in design mode. If we want to ensure that data is always entered for a certain field, click on the relevant field. In the Field Properties box at the bottom of the screen, select ‘Yes’ from the drop-down menu next to ‘Entry required’. To make it so entry for that field…

For this section we need to create a new table. Create a new table in design mode, with the two fields shown for the cost of repair jobs. For the Cost field, select Decimal data type, set decimal places to 2, then click the ‘…’ icon next to Format example, in field properties. Select Currency…

A one-to-many relationship links the primary key to a field in a second table that may have more than one records (row). Click Tools – Relationships from the main window ribbon. The Relation Design window opens. Click ‘Insert’ – Add Tables. Add the ‘Mechanics’ table. Create a one-to-many relationship between the Staff Number field in…

For this example, create 3 new tables with the designs shown. Note the Order Details table has 2 fields combined to create the primary key. To do this, hold down Ctrl key, click in the grey area to the left of each field, then right-click and select primary key. The reason for this will be…

Referential integrity means controlling how records are altered or deleted in a relational database (i.e. one with links/relationships between fields in different tables). To define the rules of how records are altered across relationships, you must right click on the relationship line in the Relation Design window and select Edit. This allows you to change…

Open the Relation Design window again, this time change both Update options to ‘Update cascade’ for both lines. Update cascade – automatically updates any related foreign keys to match the primary key. Also set the Delete options to ‘Delete cascade’. In the ‘Orders’ table, try changing the record with Order Ref 201, to ‘450’. This…

Joins are links between table that are used to modify the way queries select records. To demonstrate, create a new query in design view. Add the tables ‘Car and owner details’ and ‘Jobs’ In the Field drop down menus, select all the fields from both tables using the wildcard *, as shown. Press F5 or…

An outer join allows you to display ALL records from one table, but only records in the other table that match the related fields. You can choose between a Left outer join or Right outer join in the Type drop-down menu. Choosing Left join will display all records from the left-hand table, Right will display…

For this example we need to add a record to the ‘Car and owner details’ table with a Car Reg Number that doesn’t appear in the ‘Jobs’ table. Open the Car and owner details table and add a new record at the bottom of the table. Don’t add anything to the jobs table. Close the…

For this example, we need to create a new table with the properties and data shown. A self join links a table to itself. In the employee table for instance, if we want a query to list each employee and the name of who they report to, we would need a self join. Let’s first…

Many of the ‘action’ queries that we are going to look at in this section differ in the way that they are executed between MS Access and LibreOffice Base. Queries that directly update, add to, append or delete records in a table can be done without using SQL code in MS Access, by using certain…

Open the Order Details table. If we notice that there is an error with certain data, sometimes it is easy to change it using the ‘Update’ command. Imagine we notice that Part Ref ‘D1’ (spark plugs) can only be bought in packs of 10, but many records show less than 10 – a human error.…

To add records to a table in Base you must use an SQL statement, using the command INSERT. Go to Tools->SQL and in the SQL Command box type: INSERT INTO “Jobs” VALUES (’18’, ‘105’, ‘Repair handbrake’, ’17th JAN 17′, ‘RS1’) Notice that the command inserts a record that includes all 5 fields in the same…

Appending records means taking them from one table and putting them into another. In MS Access there is a built-in function to do this, but in Base you will have to use an SQL query. Imagine we want to create an archive system for completed jobs. To start with, create a new table called New…

Apply, remove bound control properties like: limit to list, distinct values. Limit to list and distinct values options are only available in MS Access, not Libreoffice Base. Limit to list changes the properties of a combo box to only allow values from the list. To do this in Base, you simply need to select the…

Imagine we wanted to delete the last 3 jobs from the ‘Jobs’ table as the customers cancelled last minute. We could go through and manually delete them, but imagine if we had hundreds of records to delete (maybe we archived them somewhere else). This would take a long time. We can use the DELETE SQL…

Imagine we want to copy all of the records with a job number of 10 and above from the ‘Jobs’ table into a new table called ‘Recent Jobs’ Of course, we could do this manually by creating a new table and entering the data records one by one. This is time consuming, so we prefer…

A crosstab query involves creating a cross join between two tables, where every record of the left table is combined with every record in the right-hand table. Create a new query in design mode and add the tables Orders and Parts. Select to display all fields from both tables. Create a Cross join by dragging…

There is no easy way to do this in LibreOffice Base. We have to use a roundabout solution. First create a duplicated record by adding a record to the Mechanics table, repeating the first name and grade under a different staff number. This could happen in real-life as an admin error. Close the table. Create…

Again, there is no automatic way to do this in Base, unlike Access. The only way to do it is to use a subtract join which we covered in the relationships section. As a reminder, let’s set a new one up. Open the Mechanics folder and add a new staff member. Let’s say we want…

A parameter is a value for a certain field, often used as a query selection, that can be changed each time the query is run. Start by creating a new query in design mode and adding the ‘Jobs’ table. Select to display all fields from Jobs using the * wildcard in the first query column.…

To add a second variable, open up the same query as we used in the last example. In the third column, choose Staff Number field and type :Staff into the Criterion section. This will set up a second parameter for the staff number. Notice that Visible is unchecked for both the Car Reg No and…

We have already used the asterisk wildcard * in queries to display all fields. The other most common wildcard is ‘?’ which was covered in module 1 and replaces exactly one character. For instance if we run a query to find Car Reg No with the Criterion LIKE ‘RS?’ it will return any car reg…

First, we are going to try and display the 3 highest priced jobs. Create a new query and add the table Job Costs In the first column display all fields *. In the second column choose the Cost field and select ‘descending’ in the Sort box. Press F5 to run the query and notice that…

For this example we are going to create a new table called ‘Job Hours’ Create and save it with the data shown (NB. Set decimal places to 2 for the Normal and Overtime Hours) Create a new query and add the new Job Hours table. In the first column select the all fields *. In…

Add a ‘Staff Number’ field to the Job Hours table and fill in the Staff Numbers from the jobs table. We could have used a join to link the Staff Number field between the two tables, but grouping information in a query doesn’t work with joined tables. Create a new query and add the Job…

Create, modify, delete bound controls: text box We are going to use the form ‘Jobs Entry’ that we created in an earlier lesson. Right click on Jobs Entry form and select ‘Edit’ to open design view. Hold CTRL and right click on the box next to Staff Number, then select ‘Replace with’ and click ‘Text…

Create, modify, delete bound controls: combo box Replace the Staff Number entry box with a Combo Box. A combo box allows you to select from a list or manually enter data. Right click and select ‘Control’ You have the options to change the empty string result or set input required, but with a combo box…

Create, modify, delete bound controls: list box A list box is similar to a combo box, but you cannot enter values manually. They are a good option if you want to tightly control which values can be entered for a certain field. Open the Jobs Entry form in design mode by right clicking and selecting…

Create, modify, delete bound controls: check box For this example, we need to set up a new form. Start the wizard and create a form from ‘Car and owner details’ including all the fields. Then open this in design mode by right clicking and selecting ‘edit Notice that the ‘Account settled?’ field is a check…

Create, modify, delete bound controls: option group Open the car and owner detail form in design mode. We are going to create options for the title field to save time typing them out. The options are going to be Mr, Mrs and other. Start by making the text box next to ‘Title’ smaller by dragging…

Modify sequential tab order of controls on a form. You may need to change the tab order of a form, for instance if you add a new response box or want to give certain fields priority over others. Right click the Jobs Entry form and select edit. If we want to change the order of…

Create, delete a linked subform. A subform allows you to display associated data from a different table within a form. Create a new form using the wizard. On the first step, add all of the fields from the table ‘Jobs’, then click next. On the second step, we can add a subform based on the…

We are going to use the query that we created in a previous tutorial. The query is shown to the left, it calculates the total hours worked on each job for each member of staff. Then the query calculates and displays the average number of hours worked for each staff member. We are going to…

In Libreoffice Base, there is no automatic option to do this in a report. We must first create a query that we will then use to run a report. Create a new query in design view. Add the table Job Costs twice, the reason for this will be explained shortly. Creating a query that gives…

Concatenate fields means to merge them together. There is no option to do this automatically in Base, so we first have to create a query in design mode. Add the Car and owner details table. We want to merge First Name and Surname fields. In the Field column type: “First Name” || ‘ ‘ ||…

Create a report in wizard mode, select the table Car and owner details and add the first 5 fields only. Click Next twice. Add grouping levels for Surname and First Name. Click Next twice. Choose ‘Columnar, two columns’ layout. Click Next. Name the report. Select Dynamic report Select ‘Modify report layout’ Click Finish. Notice the…

To change the sorting and grouping in a report, click the sorting and grouping icon. In the window that pops up, you can select the grouped fields and change their properties. You can choose to sort ascending or descending, as well as choose whether to display headers and footers. Execute the report with the settings…

Select the Surname group section on the left hand side (in blue) In the properties section on the right hand side, where it says ‘Force New Page’ select ‘Before Section’. This will insert a page break before each new section. Execute the report to see the effect of this change. Notice that a page break…

For this tutorial, first we are going to create a LibreOffice Calc spreadsheet. Open a new Calc file. In sheet 1, enter the data shown to the left. Then right click on the tab for the sheet and select ‘Rename Sheet’ Type Customer Names and Ages, click OK. Save the sheet as ‘Example 1’ Click…

For this tutorial, we are going to create a text file in LibreOffice Writer. Open LibreOffice Writer. Enter the text shown. The first line designates field names, separated by commas. Each new line represents a new record with fields in the same order as the top line. Select Save As from the File menu. Name…

To link data from a different database source, open a new LibreOffice Base file. In step 1 of the wizard, choose ‘Connect to an existing database’ and select the format of the database e.g. Microsoft Access 2007. Click Next and then select a database file. Finish and save. The database will then import data from…

Open two separate database files. To import data, you can simply drag and drop between the windows. For example, to copy the Names table from the lower database in the example shown, left click and hold on ‘Names’ in the Tables section and drag it to the Tables section of the other database. A dialogue…

First, make sure that macro recording is enabled. Open a Libreoffice Base database file. From the main window taskbar select Tools->Options then select LibreOffice->Advanced. Check the optional feature: Enable macro recording. Please note that macro recording is limited in LibreOffice Base due to program stability issues. In the current version of Base, macros can only…

Open the form and select Tools->Customize Select the ‘Standard’ Toolbar from the drop-down list. Click Add Command. At the bottom of the Category section, select LibreOffice Macros. Navigate to the ‘Newrecord’ macro we created. Click Add. Notice that there is now a button on the Standard form toolbar that says Newrecord. If you click on…

Another way to directly run a macro is to create a button in the form. In the main Base window, right click the form and click Edit to open it in design view. Select the ‘Push button’ icon from the controls toolbar. Click and drag in the form to draw a button. Right click on…