Advanced Database – Database Development and Use
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 … Take lesson
Advanced Database – The ‘life cycle’ of a database
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, … Take lesson
Advanced Database – Structured Query Language (SQL)
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, … Take lesson
Advanced Database – Tables
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 … Take lesson
Advanced Database – Form
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’ … Take lesson
Advanced Database – Create, modify or delete a lookup in a field
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 … Take lesson
Advanced Database – Create, modify, delete an input mask in a field
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. … Take lesson
Advanced Database – Set data entry for a field/column: required, not required
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 … Take lesson
Advanced Database – Create, modify, delete a one-to-one relationship between tables
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 … Take lesson
Advanced Database – Create, modify, delete a one-to-many relationship
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 … Take lesson
Advanced Database – Create, modify a many-to-many relationship using a junction table.
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 … Take lesson
Advanced Database – Apply referential integrity between tables
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 … Take lesson
Advanced Database – Apply automatic update or deletion of related fields.
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’. … Take lesson
Advanced Database – Apply, modify an inner join
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 … Take lesson
Advanced Database – Apply, modify an outer join
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 … Take lesson
Advanced Database – Apply, modify a subtract join
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 … Take lesson
Advanced Database – Apply a self join
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 … Take lesson
Queries in LibreOffice Base compared to MS Access
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 … Take lesson
Advanced Database – Create, run a query to update data in a table.
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 … Take lesson
Advanced Database – Create, run a query to add records to a table.
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’) … Take lesson
Advanced Database – Create, run a query to append records to a table.
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 … Take lesson
Advanced Database – bound control properties
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 … Take lesson
Advanced Database – Create, run a query to delete records in a table.
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 … Take lesson
Advanced Database – Create, run a query to save selected data as a new table.
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 … Take lesson
Advanced Database – Create, run a crosstab query
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 … Take lesson
Create, run a query to show duplicated records within a table.
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 … Take lesson
Advanced Database – Create, run a query to show unmatched records in related tables.
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 … Take lesson
Advanced Database – Create, modify, run a one variable parameter query.
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 … Take lesson
Advanced Database – Create, modify, run a two variable parameter query.
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 … Take lesson
Advanced Database – Use wildcards in a query
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 … Take lesson
Advanced Database – Show highest & lowest range of values in a query.
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 … Take lesson
Advanced Database – Create and name a calculated field that performs arithmetic operations.
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 … Take lesson
Advanced Database – Group information in a query using functions.
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 … Take lesson
Advanced Database – bound controls: text box
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 … Take lesson
Advanced Database – bound controls: combo box
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 … Take lesson
Advanced Database – bound controls: list 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 … Take lesson
Advanced Database – bound controls: check box
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 … Take lesson
Advanced Database – bound controls: option group
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, … Take lesson
Advanced Database – sequential tab order
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 … Take lesson
Advanced Database – linked subform
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’, … Take lesson
Advanced Database – Format arithmetic calculation controls in a report
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 … Take lesson
Advanced Database – Apply a running sum for a group, over all.
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 … Take lesson
Advanced Databases – Concatenate fields in a report.
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 … Take lesson
Advanced Databases – Insert, delete a data field in group, page, report headers and footers.
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 … Take lesson
Advanced Databases – Sort, group records in a report by field(s)
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 … Take lesson
Understanding Databases – Force page breaks for groups in a report
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. … Take lesson
Advanced Database – Link external data to a database: spreadsheet
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 … Take lesson
Advanced Database – Link external data to a database: text
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 … Take lesson
Advanced Database – Link external data to a database: existing database
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 … Take lesson
Advanced Database – Import from existing database into a database.
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 … Take lesson