Advanced Database

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…

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…

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…

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…

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

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…

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…

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…

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