UNIT 6. DATABASES - MODULE 2

 

LIBRE OFFICE BASE

MODULE 2.

Relations

Objectives

Provide consistency and referential integrity to the Databases creating relations among the tables

Contents

The concepts treated in this module are:

• Relation concept

• Indexed fields and key fields

• Relation creation and settings

• Creation of a whole relation scheme in a database

Relation Concept

There is a relation between two tables when the values in a column of the first table must exist in a column of the second table

 

 

 

 

 

 

 

 

 

 

For example, in Pedidos there is a field IDEmpleat meant to contain the identifier of the employee in charge of the order ( el pedido). To keep the consistency of the information in this database, it's important to protect this field to prevent the user from entering in this column a fictitious employee. A fictitious employee is an employee who doesn't have a record in the table Empleados. That's what a relation is, a rule linking the data from two columns in different tables which must be kept consistent.

Therefore (por tanto), a relation constrains (restringe) the possible values in a field, (called alien key or clave ajena), to those existing in the identifier field (called the primary key or clave primaria) of the referenced table, avoiding in this way the insertion of fictitious data.

Types of relations

There are three types

1. Relation 1 to 1. Example : A teacher can be the tutor of a single students group (ONE) and a students group can only have a single tutor (ONE)

 

 

 

2. Relation 1 to N: Example: Students and Groups. A student belongs to a single group (ONE) but a group has a number of students belonging to it (N)

   

3. Relation N to N: Teachers and Groups. A teacher give class to a number of groups (N) and a group is given class by a number of teachers In LO Base this kind of relation can not be represented. If there is a N to N relation, this is represented by an additional table, but this issue won't be treated in this course.

If you couldn't understand these concepts, call the teacher, he will explain it to you.

IMPORTANT: To make a relation, the two fields (the primary key and the alien key) must contain the same data types. That means, it's not possible to relate a text field with a numeric one.

BUILDING A RELATIONS SCHEME

Download from Moodle the database biblioteca.odbb.

• Open the Database Biblioteca. Let's make a relation between the tables Llibres and Llengua.

• First make sure all the needed requirements are fulfilled (se cumplen)

  • Do both tables have a common field? Yes, there is Codi Llengua, meaning the same, the code of a language, in both tables

  • Are they of the same type? Yes, both are text fields.

  • Codi llengua is a primary key in any of the tables. Yes, it is in the table llengua.

So we can create a relation between Llibres and Llengua, linking the field Codi llengua in both tables.

• Let's consider which type of relation will it be: From the standpoint (punto de vista) of a book, only can be written in a single language (ONE). From the language standpoint, a language is associated to A NUMBER of books written in that language (N). If you couldn't understand this concept, call the teacher, he will explain it to you.

• 

• In LO Base main window select the menu Ferramentes | Relacions, as seen in the image below:
  
  

• The window add tables opens:
  
  
  .

• Select Llibres and Llengua and click in the butto Afegir taules , and then tanca. The selected tables appeared in the top side of the relations window
  
  

• Select the field codi llengua from the table Llengua, and drag it over Codi Llengua in the table Llibres . The relation is created. Remember to create a relation, you must drag always the primary key from the referenced table over the alien key
  
  

Notice that there is a new line linking both fields. This is how LO Base represents a relation. Notice the little numbers 1..N showing the relation type

Adding more relations

• We are going to create the relations shown in the image below. Remember, you must drag the primary key field (check out the yellow key) over the alien key in the table Libros. I you do it the other way round (si lo haces al revés) you'll get an incorrect relation

• 

• Arrange the tables position as shown in the image above. Llibres must be placed in a centered position and the rest of tables around it

•  

  .

 

 

 

 

 

 

 

 

 

• Make the relation between Llibres and Llengua dragging the field Llengua.Codillengua over Llibres.Codillengua

• Make a relation between Llibres and lloc edició, using the field lloc_edicio.

• Capture your screen desktop and ctrl + clic here to upload it to Moodle using the link to exercise 11

• Create the rest of relations

  • Llibre-Material

  • Llibre- Nivell

  • Llibre- editor

  • Llibre Lloc edicio

• Capture your screen desktop and ctrl + clic here to upload it to Moodle using the link to exercise 12

  
  Notice that we still haven't done some relations because there are some problems with the data in those tables For instance, try to create a relation between País and Llibres. The result is a mistake. Capture the desktop screen showing this error window and then upload it to the Moodle link exercise 13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

• Remember that the relation must make sure that all values in Llibres.CodiPais, exist in Pais.codiPais. This error pops up because at the moment of creating the relation, LO Base detects that there are values of codiPais in Llibres non existent in Pais.codiPais. Let's check it out

  • Open the tables país and llibres.

  • Click in the column codi pais header in both tables to select whole column, and press the button order from Z to A . This action orders the table in descending order

  • 

• Compare the field codipais in both tables. Check if there's any 'va' value in Pais.Codepais, And check the same in Llibres.codipais.

• To provide a solution Insert a 'va' record for Valencia in the table Pais as shown in the image below

• Now try to create the relation between Pais and Llibres. You shouldn't have any problem

• Now let's carry out the relation Alumnes and prestecs. Drag Alumnes(Id_alumne) over Prestecs(Id_alumne). In this case the relation is created, but a warning error pops up.Capture the desktop screen showing this error window and then upload it to the Moodle link exercise 14

•  

 

 

 

 

 

 

• Delete the relation (deploy the contextual menu, and select the option eliminar relacion)

• The error tells us what's the problem. The related fields have different data types. Let's check it.

  • Open the main LO Base main window and select the table Prestecs. Deploy the contextual menu on the tables name and select edit or click on the button edit

  • Do the same on the table Alumnes

  • Compare the data types in both tables. Prestecs(id_alumnes) is indeed an integer column when it should had been text. Solve the problem and create the relation

  • Create the relation Llibres-Prestecs, using the common field Registro

EXERCICI 15 Puja a moodle la base de dades biblioteca.odb amb totes les modificacions realitzades en aquest modul