This is a Clilstore unit. You can .
Provide consistency and referential integrity to the Databases creating relations among the tables
The concepts treated in this module are:
Indexed fields and key fields
Relation creation and settings
Creation of a whole relation scheme in a database
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.
There are three types
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)
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)
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.
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
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.
Create the rest of relations
Llibre Lloc edicio
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
Short url: https://clilstore.eu/cs/4681