This is a Clilstore unit. You can .
This unit expects to introduce the basic concepts related to Relational Data Bases.
Relational data base concept and components
Description of the LibreOffice Base main window
Searching information in a Table
Creating and designing tables
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:
To store large amounts of information
A quick access to the information, through the user's queries. The application answers these queries with tables of filtered data.
Download from Moodle the file Gardenco-lliurex. To open it, double click in it
In the image below, you can see the main LO Base widow. There are three panels: the column panel on the left shows the four different main objects in a database: tables, queries ( consultas), forms (formularios) and reports (informes).
Select the icon tablas
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
Double-click in the table Pedidos, in order to open it
Each row is a record
Each column is a field
In each table there is one or several special fields called primary Key fields. These columns are used to identify the record from the others, and for this reason the values they contain can not be repeated in any other record. A NIF may be a primary key field for a person. Other examples: the number plate in a car (la matrícula), or the record number in a school student are other examples.
NOTE: In GardenCo, it's easy to identify the Key fields. They are called ID+ the table name. For example the key field in the table pedidos is called IDpedidos
EXERCISE 1. ANSWER TO THE FIRST QUESTION IN THE MOODLE QUIZZ, keeping in mind what has been said in the previous paragraph about the foreseeable (previsible) name of a key field in GardenCo. ...Ctrl+click to open the exercise in Moodle.
In a table, there might be fields referencing fields in other tables. For example, in Pedidos the field Idempleado contains values of the empleados Key table.
EXERCISE 2. Answer in Moodle the following question: What's the worker's name who has served the order (pedido) number…..?Ctrl+clic here to open this Moodle exercise
Close the Pedidos and Empleados windows and open the table Clientes
Click on the row with Idclient DREKA. Change her name (Kate) to Katherine
Now we are going to move around the table using the navigation bar shown below
Press the button left to move to the last record
Add a new client record. Press the button to create a new record.
Insert a record for the following clients. The IdClient field is made linking the 3 first letters in the field Apellidos with the 2 first letters in the field Nombre
Teresa Ruiz, 121 Bush Crest, Seattle, WA, 34222 (USA) (360) 555-0394
Albert Rurn, 23 Clinton Crest, Tacoma, CA, 34563,USA ,(253) 555-0224
Bill Rutheford, 223 Obama Crest, Sausalito, CA, 12341, USA ,(253) 555-0242
Capture your desktop screen, showing the 3 records you have just created and upload it to Moodle, in the task Exercise3. Ctrl+clic to accees the exercice
Insert the following record:
Terence Ruinsfeld, 987 Reagan Crest, Palo Alto,CA, 12233 USA ,(253) 555-1762... Read carefully the Error message that opens. What's the cause of the problem?? Sort it out. (Solucionalo)
Delete the record Albert Rurn. Select it and press the key Supr
Save the table (button save on the window tool bar)
Go back to LO Base main window and open the table empleados
Insert in this table the following record:
Save an close the table empleados
Open the table Productos and look for a record with the value Agrostis palustris in the latin name field. To look for a value in the table click in the button filtro estándar
Change the value in the field Idproveedor to 100, and click in any other cell. A new error window opens. Capture your desktop showing the open table and the error window and upload it to Moodle in the task EXERCISE 4. (Ctrl+clic to open it)
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.
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.
Close all the open tables and go to the main window
Click on the table Empleados with the right button and select the option Edita from its contextual menu
The table design window opens. There are three columns on the top side. The left one is the field name, the second one is the data type, and the one on the right is the field description. This last is used to write a short text describing the field meaning
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
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
[INTEGER], integer number from -2147483648 to 2147483647.
[SMALLINT], integer number from -32.768 to 32.767.
Text [V A R C H A R], Any chain of text of undefined length. Chain text can be made out of alphabetic (letters), symbolic(signs) and numeric characters. No arithmetic operations allowed to chain texts.
Text [V A R C H A R_I G N O R E C A S], Text chain in which the capital letters are seen as equal to lower letters.
Text (fix) [CHAR], text of defined maximum length
[DATE], it stores data in date format, that is dd/mm/aa.
Si/No [BOOLEAN], it store binary values, which can have just 2 values: Si o No, 0 o 1, true o false.
Hour [TIME], it stores dates with time format dd/mm/aa hh:mm:ss.
Picture [LONGVARBINAR], allows to store pictures, graphics etc
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
White: No, False, 0
Orange: No value
Marked orange: Yes, True, 1
We are going to create a new database from scratch (desde el principio) called ALUMNAT.
First close the Gardenco window, selecting the menu archivo->salir.
Open again LibreOffice Base selecting Aplicaciones/Oficina/LibreOffice.org Base. A new window opens. Select the option Crea una base de dades nova and then clik in the button Finalitza.
The next window allows you to name the new database. Call it Alumnat and save it in your documents folder
Creating a new table
To create the first table, in the main window, click in the tablas icon in the left vertical panel.
Then in the top horizontal panel select Crea una taula en vista disseny…
Create the table Alumnat setting the fields shown in the image below:
First write the name of the field, for example Idalumne
Next set the data type for example [INTEGER]
Idalumne is going to be the primary key. Set automatic value to 'yes'. In this way, when a new record is created in the table, LO Base automatically will write a not repeated sequential number in the field
Next define the fields Nom, and Cognoms, both of TEXTO[VARCHAR] type. Set for the first a (maximum) length of 20, and 40 for the second. Set required to true in both.
Curs will be a text field, 9 characters length, required, and default value 2016-2017. (Don't pay attention to the default value shown in the image below)
Finally insert the field Comentari. Set the type to TEXTO[VARCHAR], length 150, not required.
To set the field id_alumnes as the primary key, click in the button on the left column and select the option clave primaria. See the image below.
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.
Insert 5 student records in the new table (Make up (invéntate) all the data):
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, 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
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)
This is the design of the table Materies:
These are the fields properties:
Insert the following records, and add two others (make them up)
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:
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
In this exercise you are going to get familiar with the searching tools in the table window
Open the database GardenCo-Linux
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.
Open the table clientes
Search a client record containing the text Ukiah in the field dirección . Capture the desktop screen showing the Client you have just found. Upload to Moodle the capture using Ctrl+click on this link
Ordering the table
You can see the table with its contents ordered using any of its fields.
Open the table Productos
Click on the icon ordenar to deploy the ordering window, shown in the image below
Order the table by the field PrecioUnidad in descending order. Use NombreProducto as second ascending order criteria
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.
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
Open the database biblioteca, Open the table libros.
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
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
The filtre estandar window will open. Enter the selection criteria shown in the image:
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.
Another example: Select all the books except those edited in the 50's. In this case we use the OR operator to combine the effect of the condicions:
ANY EDICIO<1950 OR ANY EDICIO>=1960
The expected result will be:
Select books in french (field codi llengua= "fr") and for high school students (bachillerato, codi nivell= "bat") The standard filter will be:
And the expected result:
Open the table productos.
Filter the table so that it shows the products of category 14, (use field IDCategoria=14), (AND) which have a price per unit under 5€ (field preciounitat< 5)
Disable the table filter clicking in the button
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:
* Replaces whatever text chain
? Replaces whatever single character
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”
NOMBREPRODUCTO COMO '*dron': Selects all the records where their name ends in 'dron', because * equals whatever chain of text.
Nombreproducto como 'Fra*' Selects all the records where their name start with 'Fra'. For example: Fragaria, Fragis, Frangelicus
Nombreproducto como '*elix*' Selects all the records where their name contains wherever (en cualquier lugar) the chain elix. For instance: velixorum, Helixeria
Nombreproducto como 'F??gance*' Selects all the records where their name begins by an “F”, then there are whatever two letters and then the text chain 'gance' For exemple Fragance, Flagance, Frigance etc.
Open the table productes
Do a filter to show the products which their Nombrelatino field starts with Pinus. You should get two records.
Disable the table filter clicking in the button
Short url: https://clilstore.eu/cs/4680