Category: Advanced Database

Joins are links between table that are used to modify the way queries select records.

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

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 click the Run Query icon.

Notice that it repeats some of the records multiple times.

To prevent this we need to create a join between the tables.

Press F4 to clear the query table.

To create an inner join, click and drag from the ‘Car Reg Number’ field in one table to the corresponding field in the other table.

This creates a connection between the two fields, that applies only to this query.

Press F5 to run the query.

Notice that it only returns records where the related Car Reg Number fields are identical, cutting down on the repetition.

To modify the inner join, right click on the connecting line between the tables and click Edit.

This brings up a Join Properties window where you can change the fields and type of join.

[the_ad id=”12397″]

Open the Relation Design window again, this time change both Update options to ‘Update cascade’ for both lines.

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

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 time it updates the cascade, meaning any related field in the foreign table will be instantly updated.

Open the ‘Order Details’ table to check. Notice that all the instances of 201 have been changed to 450.

The other two options are Set null and Set default.

Set null – if primary key is changed, this will empty any corresponding foreign key records.

Set default – if primary key is changed, this will set the foreign key record to the default specified in the field properties.

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).

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

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 ‘Update’ and ‘Delete’ options.

Open the Relation Design window, and right-click on each line for the relationships made in the last example.

Set them to ‘No action’ for update and delete. No action means changing the primary key won’t affect foreign keys.

Close the window and open the ‘Orders’ table.

Try to change the Order ref for the first record to ‘450’ for instance.

You will get an error message like the one shown. This is because we have set the update option to ‘No action’.

The same would apply if we tried to delete the row, as we also set the Delete option to ‘No action’ for the relationship.

This option enforces referential integrity of the database.

For this example, create 3 new tables with the designs shown.

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

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 explained in a later slide.

Then populate each table with the data shown.

A many-to-many relationship is used when a record in the first table can have matching records in the second and vice versa.

In this example, a single part may have many orders, and a single order may contain many parts.

Therefore, if we create a relationship between ‘Part Ref’ and ‘Order ref’ fields, it will be a many-to-many relationship. To do this we need a junction table.

We need a junction table as you cannot directly connect the two fields as there are multiple records in the Parts and Orders tables.

We can use the Order Details as the junction table. This is the reason we created two primary keys, so we can connect to both of the other tables.

Click Tools-Relationships, then add tables: Parts, Orders and Order Details

The junction table is the primary table, start by clicking ‘Order Ref’ field in the Order Details table and drag it over to the ‘Order Ref’ in the Orders table. (If you do it the other way around, it won’t work)

Then, click and drag ‘Part Ref’ from the Order Details table to the ‘Part Ref’ in the Parts table.

This makes 2, 1 – n lines, i.e. one-to-many. By linking through the junction table, overall it creates n-n, many-to-many.

Using a junction table has made a relationship between two fields, Part Ref and Order Ref, that would have been impossible otherwise.

If you don’t have a junction table, you can create one, as long as it contains foreign keys from both tables.

To modify the relationships, right-click on each line separately and select Edit.

A one-to-many relationship links the primary key to a field in a second table that may have more than one records (row).

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

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 the ‘Jobs’ table to the Staff Number field in the ‘Mechanics’ table.

It automatically sets it as a one-to-many relationship, with 1 at the primary end and ‘n’ at the other.

This is because there is only one staff number per mechanic, but there are more than one job record for each staff number.

To modify the relationship, right click and select Edit.

To delete the relationship, right click and select Delete.

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.

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

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 and choose a format that shows pounds and pence e.g. £1,234.00

Save the table with the name ‘Job Costs’ and close it.

Enter values for the cost of the various jobs.

This data could have been included on the main jobs table, but sometimes it is better to keep data in a separate table.

There are many reasons for doing this. You may want to split up a large table, keep some data separate for security reasons or if the second table has optional data that isn’t used very often.

As we have created a separate table for job costs, we need to link this to the main jobs table.

To do this we use a one-to-one relationship. Click Tools-Relationships in main window.

We are going to link the Job No primary key field from the primary table (Jobs) to the Job No foreign key field in the secondary table (Job Costs).

This is called one-to-one as there is only one record (row) for each Job No in each table.

After clicking on Relationships an ‘Add Tables’ box appears.

Select Jobs, click ‘Add’ then select Job Costs and click ‘Add’.

This adds both tables to the Relation Design window. Click ‘Close’ in the Add Tables box.

Leave the Relation Design window open.

Click and hold the Job No field in the Jobs table, then drag to the Job No field in the other table.

This creates a one-to-one relationship between them. (hence the 1 at each end)

This allows you to create queries that will reference between the two tables.

E.g. you can run a query that lists Job No, Car Reg No and Cost, although Cost is in a different table.

To modify the relationship, right click on the joining line and select Edit.

This opens a relations dialogue box, where you can change the fields involved.

The update options specify what happens when you make changes to the primary key, we’ll discuss this when we talk about referential integrity later in the course.

To delete the relationship, simply right-click the joining line, then click ‘Delete’.

[the_ad id=”12397″]

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.

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

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 is not required, then make sure ‘No’ is selected from the drop-down menu.

In Base you can only create input masks in a form (not a table as in MS Access).

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

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 then select ‘Edit’.

Then change the data type in the table of the Date field to Text, as shown and close the table.

Open the form ‘Jobs Entry’ in design mode by right clicking and select ‘Edit’.

Press ‘Ctrl’ and left click the text box next to ‘Date’ to select it

Right click, select ‘Replace with’ and select ‘Pattern field’. This allows us to create an input mask.

An input mask is like a template that only allows you to enter data in a specific way, this helps prevent errors in the data.

Right click the box and select ‘Control…’

Masks use the following characters: N=numbers 0-9, a=letters a-z, A=letters A-Z (converts lower case to upper case), L=locked i.e. this can’t be overwritten, c or C = all characters (a-z 0-9)
x or X = all printable characters

In Edit mask enter: NNaaLAAALNN to represent e.g. 07th FEB 13 (notice the spaces are L for locked)

In Literal mask enter: ____ ___ __ (that is 4 underscores, space, 3 underscores, space, 2 underscores)

This is what will appear as default in the input box on the form.

Select Yes for the ‘Strict format’ option, this means if data is entered in the wrong format it won’t be changed. This is useful for preventing mistakes in the data.

Toggle out of design mode by clicking the design mode icon, and click the ‘data source as table’ icon to view the table.

Try typing dates into the Date field. Notice that you have to follow the format set in the input mask.

If you try using the Tab key to cycle through the form, you may notice the order is wrong for the new box. Go back to design mode by clicking the design mode icon.

Click the ‘Activation order’ icon on the toolbar to open a dialogue box.

Select the date pattern field and move it up or down until it is between Description and Reg No.

To modify the mask, right click and select ‘Control’, then use the Edit mask option.

To delete the input mask, just change the box back to a text box by right clicking and selecting ‘Replace with’

[the_ad id=”12397″]

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.