T01 Concept of database and relation

A database is a set of interrelated information that represents a particular information system, and it is composed of relationships, commonly known as tables, that store the data referring to an object or an interrelation between objects.

Therefore, if we want to manage academic information using a Database Management System (DBMS) , we have to create(in this particular case) a database that comprises three tables: PROFESORES, ASIGNATURAS and IMPARTE. Each table have its columns, that represents the corresponding attributes of the entity or the foreign keys that allows relationships between tables. The DB that manages this information will be called Ejemplo, and its tables are shown in the next frame.

Database: Ejemplo

PROFESORES ( dni : varchar(10), nombre : varchar(40), categoria : char(4), ingreso : date )

Primary key: dni

ASIGNATURAS ( codigo : char(5), descripcion : varchar(35), creditos : number(3,1),

creditosp : number(3,1) )

Primary key: codigo

IMPARTE ( dni : varchar2(10), asignatura : char(5) )

Primary key: (dni, asignatura)

Foreign key: dni → PROFESORES

Foreign key: asignatura → ASIGNATURAS

Extensions of Ejemplo:

Selection of the working database

MariaDB/MySQL uses the concept of databases as independent contents, therefore in order to access to a particular table two ways are possible:

Adding the name of the database to the name of the table: select * from ejemplo.profesores
Selecting the database first: use ejemplo

Second option allows to perform queries without specifying the database that contains these tables.

Data types

As a rule, using more than one table implies the need of a relationship between these tables by a column that they share in common. In this case, dni from profesor, for the relationship between imparte and profesor, and código from asignatura, for the relationship between asignatura and imparte. However, notice that in the table imparte the code of the asignatura is called asignatura and in the table asignaturas is called código. Actually, these attributes are common because they have the same domain and therefore, they can be compared. Domains are defined by the possible types that the DBMS provides.

Data types in the scheme of the DB for every column in each table determines the possible values that these columns can have. They are very important when two tables are related in a Select sentence, since only identical data types columns can be compared, and also when manipulating data, due to the fact that each data type presents specific requirements for its manipulation

Some of the data types that can be found in MariaDB/MySQL are:

VARCHAR(x): cadena de caracteres de longitud variable con un máximo de x (1<=x<=4000)
CHAR(x): cadena de caracteres de longitud fija de longitud n (1<=x<=2000)
INT,INTEGER: números enteros
DECIMAL(p,s): números con precisión p y escala s (1<=p<=38) (-84<=s<=127)
DATE: datos de tipo fecha, con la forma 'yyyy-mm-dd' (año, mes y día).
DATETIME: fecha y hora 'yyyy-mm-dd hh:mm:ssss'

Without going into more specific details, such as automatic type conversion:

Text type constants are always enclosed in single quotes: 'Monday', 'Antonia', '001A'.
Numerical constants are never enclosed in quotation marks; no thousands, and the decimal point is a "dot": 20, 12500.50
Temporal constants are always enclosed in single quotation marks -except in the few cases discussed in later sessions-. '2019-01-29 22:10:52'

Select-from

In order to query a database, the select order of SQL will be used. With the syntax explained in the previous section, we can be able to formulate all possible requirements (queries) of the tables that compound a specific DB. In this moment, we will see the minimum "select" expression, formed by two clauses, select and from, compulsory elements in every query we made.

The following SQL statement will select all the records in "profesores" table:

select * from profesores

When using asterisk, the DBMS returns all the columns of the table.

select categoria from profesores

Now, we only obtained the "nombre" and "categoria" columns, avoiding the other columns in the output.

Therefore, it is possible to require as many columns as we want if they are defined in the table.

select nombre, categoria from profesores

If we want to eliminate duplicates the modifier distinct can be used.

select distinct categoria from profesores

Where

With the statement select-from we obtain the information of the columns of the table required. If we only want to select the rows that fullfil an specific condition, the clause where should be used.

select nombre

from profesores

where categoria = 'TEU'

These conditions can use logical conectors such as AND, OR, and NOT, as well as brackets to modify the left-to-right evaluation. Besides, the comparing operators can be also used: >, <, >=, <=, <>, !=. When in doubt, consult the table of MariaDB's precedence of operators, ( MySQL equivalent): "A and B or C" is not the same as "A and (B or C)".

select nombre

from profesores

where categoria = 'TEU' or categoria = 'ASO6'

Order by

The ORDER BY keyword is used to sort the result-set by a specified column. By default, it sorts the records in ascending order, but it can be done in descending order also.

select creditos, descripcion

from asignaturas

order by creditos

The previous result is in ascending order. If we specified the DESC keyword, the result is in descending order (also ASC keyword can be used to ascending ordering)

select creditos, descripcion

from asignaturas

where creditos > 4.5

order by creditos DESC

More complex ordering criteria can be applied. The next example is performing an ordering by two attributes: when two values are the same for the first attribute, the ordering is determined by the second one. Notice that is possible to combine the ordering criteria with different data types. In other words, it first sorts by credits, and if there are two equal values then it sorts descending by description.

select creditos, descripcion

from asignaturas

order by creditos, descripcion DESC

The order modifier affects only the column it accompanies, "descripcion". Note that the table is sorted ascending by credits and, when the rows have the same credits, by description in descending order. If we need to sort in descending order by both credits and description we would have to write

order by creditos DESC, descripcion DESC

Note also that it is possible to combine different types of data in the sorting criteria.

NULL

The relational DBs work with an special value called NULL, that represents missing "unknown" data. . NULL is not the same as "empty string" or "blank"; these ones are concrete values that belong to the character strings, and it is not the same as NULL.

If we have the query "subjects without practical credits" all these possible solutions can return void or erroneous results.

select * from asignaturas where creditosp = '' --simple-quote + simple-quoute = empty string

select * from asignaturas where creditosp = ' ' --simple-quote + blank + simple-quote = blank_space

select * from asignaturas where creditosp = 0 -- using any constant as the number zero

select * from asignaturas where creditosp = NULL -- comparing with equality operator

The good one is shown next. What we are really looking for is

select *

from asignaturas

where creditosp is NULL

There is a subject with no practical credits indeed. This is due to the fact that, in the DB loading, the NULL value was introduced in the creditosp column of the subject HI.

Notice that even the common comparison operator ("equal" symbol) is not possible, you have to use the IS NULL or IS NOT NULL operator.

Constants

It is possible to state constants in the select statement, having this constant value in all the rows.

select 'La asignatura ', descripcion, ' tiene ', creditos, ' créditos'

from asignaturas

order by creditos

Page updated

Google Sites

Report abuse