Clilstore Facebook WA Linkedin Email
Login

This is a Clilstore unit. You can link all words to dictionaries.

UNIT 6. DATABASES - MODULE 1

 

LIBRE OFFICE BASE

MODULE 1.

Creating Tables in LO Base

Objectives

This unit expects to introduce the basic concepts related to Relational Data Bases.

Contents

Relational data base concept and elements

LibreOffice Base is a program designed to create, maintain and exploit relational databases.



A database is a data container. The information in the database is structured in a way that makes possible:

exercise 1. Editing a table





In the bottom panel are shown all the objects of the selected type. As we have just selected Tablas, the panel shows the list of tables in this database






 

 

 



 

 

 

 

 

 

 

 

 

 

 



 

 

 

 



 

 



 

You can not change to 100 the Idproveedor field, because in the table proveedores there isn't any record with that number in its primary key. LO Base protects the database integrity preventing the user to insert inconsistent data, for instance, a product, from a provider that doesn't exist.

How to modify the table structure

 

It's essential, before creating a table, to think up the fields needed, their data types and which one will be the primary key. However, once created, it is possible to add new fields at any moment.

We are going to edit the table empleados.









Click in the pull down button (boton desplegable) in the field tipo de campo. This allows you to select all the data types available for a table field.






In the bottom there's a panel where are shown the field properties of the currently selected field. These allow you to change some features. For example, if the field is a text field, you can state here its maximum length in characters




There is a little yellow key on the left of the field IDEmpleado. This means that this field is the primary key used to identify the record. You can mark a field as primary key, deploying the contextual menu and selecting clave primaria



Data types in fields

This image below shows all the data types available:



The data types constraint (restringen) values the field can store. For instance, a field of tipus integer can not store a value which contain letters as a NIF.

In the following list is shown the list of data types

Exercise 5 Add a new field to the table Empleado. The field name will be Permiso_conduccion, and the tipus Sí/No. The default value in this field will be NO. Don't forget to press the save button in order to save the changes.






Open the table Empleados and write the data shown in the picture below in the field permiso de conduccion. The color in the little square in the field means



 

 

 

 

 

 

Capture your desktop showing the table empleados with the data you have inserted and upload it to Moodle. Control+clic to acces the Moodle task

 

Creating a table and editing it



We are going to create a new database from scratch (desde el principio) called ALUMNAT.




Creating a new table

To create the first table, in the main window, click in the tablas icon in the left vertical panel.

imatge

 




To save the table once created, click on the Guardar button in the main tool bar or select the menu option Guarda in the menu Fitxer.

LO Base ask you a name for the table. Write: Alumnat.

In order to add records to the table, you have to open the table and start writing items in each cell, except the id_alumnes cell. Remenber this is the primary key and an autofilled cell, that means that LO Base takes on (se ocupa de) writing it.



The Records Bar

The records bar, in the bottom of the table window, shows the number of records, and the one being edited at that moment



Using this bar you can also move from one record to the next one, to the previous one, to the first, or to the last record. Or to the record number you write on it



Creating the rest of tables

We are going to add a new field Id_Grup to the table Alumnat:

Now create a new table Grups with the fields shown below.



These are the fields properties:

Insert the following records and add 3 others (make them up, inventatelos)

1

1A

Antonio Pérez

ESO

2

1B

Luís Rodriguez

ESO

3

4B

Angel López

ESO



This is the design of the table Materies:

These are the fields properties:

Insert the following records, and add two others (make them up)

1

MATEMATIQUES

2

LLENGUA

3

CIÈNCIES NATURALS



This is the structure of the table Notes: Notice that the three firsts fields are primary keys. To do this, select the three rows at the same time and mark them as primary keys deploying the contextual menu.




These are the properties of those fields:

 

Longitud

Valor requerido

Id_Alumne

    

Id_Materia

    

Id_Examen

    

Examen

30

  

Fecha

  

si

Nota

  

si
     



Insert 5 data rows in this table. You can make them up (inventartelos) but keep in mind that the students and subjects (materias) must exist in the Alumnat and Materias table



Save the database and close it



EXERCISE 6. Upload to Moodle the database ALUMNES.odt.



Searching records from the table window

Table tool bar

In this exercise you are going to get familiar with the searching tools in the table window



 

Look for a record

To look for a record select the option Cercar un registre from the standard toolbar on the top side of the window. (see image below)

Look at the image above. It shows a searching operation in a table of books. If you press the button Cerca you'll access the first record in which there is found the text Alfaguara in the field Editorial.

Ordering the table

You can see the table with its contents ordered using any of its fields.



If you want to order the table by a single field (instead of two or more), you can do it, just selecting the field and clicking the ascending and descending order buttons in the main tool bar.



EXERCISE 8: Capture your desktop screen showing the table productos you have just ordered. Upload it to Moodle, Ctrl+clicking on this link



SETTING A FILTER

 

In a table with many records, sometimes it's necessary to filter the table to see only a subset of records. To filter a table click on the icon Filtre estandard

 

To unset the filter and see the whole table again click on Esborrar filtre

 

 

There are two filters types. Standard filter, and Automatic filter. In this module we are going to focus on the standard filter

 

 

Automatic Filter

 

Imagine you have a table listing a long amount of books like these shown in the following image.

 

 

There are 1440 records, but we are just interested in those regarding the year 2000, We need to set a filter using the field any edició. Look for some record in which there is the value 2000 in the field any edició . Select that cell and click on the automatic filter button. The results are shown in the image below

 



Standard filter

 

This option provides a more complex filtering mechanism, setting more selection criteria using the AND and OR operators, and the wild cards characters (* and ?)

 

Let's go back to the LLibres table we have just used. Now we want to focus on the records edited between 2001 and 2002. We need a standard filter, Click on the button

 

ANY EDICIO>=2001 AND ANYEDICIO<=2002

The expected result is:

As you can see, we use the operators > = (bigger or equal) and < = (lower or equal) to set accurately the filtering criteria. Notice that the AND operator has also been used.

ANY EDICIO<1950 OR ANY EDICIO>=1960

 

 

The expected result will be:

Another example:

And the expected result:

EXERCISE 9

 

 

 

 

 

 

Filtering using wildcards (comodines)

 

It's possible to fix text patterns as selection conditions using wildcards and the operator COMO (or LIKE)

 

 

 

 

 

 

The wildcards are:

 

 



In the filter above the condition NOMBREPRODUCTO COMO '*i?y' selects the records from the table Productos where their name follows this pattern: whatever chain of characters +“i”+a single whatever character+”y”. That is the name ends in “i”+a single character whatever+”y, like “Family” or “entity”

 

Other examples

 

 

 

 

 

 

 

 

EXERCISE 10

 

 

 

 

 

 

Clilstore

Short url:   https://clilstore.eu/cs/4680