Gestió de dades | 1.5. Creació de taules i restriccions

1.5. Creació de taules i restriccions

Una base de dades està formada per un conjunt de taules que ens permetran estructurar la informació que percebem en un escenari concret del món real. Cada taula emmagatzemarà en forma de registres la sèrie d’exemples de cada classe d’entitats o relacions entre entitats especificades prèviament. Per crear una taula de registres buida amb l’ordre CREATE TABLE cal primer declarar els seus atributs:

CREATE TABLE nom
    (camp1 tipus1 [NOT NULL, AUTO_INCREMENT],
    camp2 tipus2 [NOT NULL, AUTO_INCREMENT],
    ...
    campN tipusN [NOT NULL, AUTO_INCREMENT],
    PRIMARY KEY (campx, campy, ...),
    [FOREIGN KEY (campx, campy, ...)
    REFERENCES taula(campa, campb, ...)]);

A l’hora de definir la classe d’informació que emmagatzemarem en cada atribut, MySQL proporciona una gran varietat de tipus numèrics i alfanumèrics bàsics (taula 2). L’espai de memòria requerit per emmagatzemar cada variable depèn de la precisió especificada en cada cas. Els tipus DATE i TIME resulten especialment útils per portar el registre de les nostres activitats en el temps. L’usuari pot declarar, a més, variables del tipus objecte (en anglès, Binary Large Objects o BLOB) per emmagatzemar fitxers de text, documents en format PDF o fins i tot imatges dins d’alguna taula de la base de dades.

Taula 2. Tipus de dades en MySQL.

Tipus genèric	Tipus MySQL
Sencer	`TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT`
Decimal	`DECIMAL, FLOAT, DOUBLE/REAL`
Text	`CHAR, VARCHAR, TINYTEXT, TEXT`
Objectes	`BLOB, MEDIUMBLOB, LONGBLOB`
Temps	`DATE, TIME`

Font: elaboració pròpia.

Juntament amb la declaració dels atributs, per crear una taula hem d’especificar quin atribut o combinació d’atributs serà la clau primària, identificant de forma unívoca cada instància. En cas d’existir, les claus foranes per referenciar els atributs d’altres taules també s’han d’indicar explícitament.

El dissenyador pot activar dos controls interns sobre el valor d’un atribut en el moment de registrar noves instàncies a la base de dades. En primer lloc, és possible rebutjar aquells registres que no posseeixin un valor definit per a un atribut concret. Aquesta circumstància s’especifica amb la construcció NOT NULL, just després de la declaració de tipus. NOT NULL seria una restricció de camp obligatori, no accepta valors nuls. En cas contrari, el sistema assignarà per defecte el valor NULL a aquest camp i acceptarà valors nuls. Aquest requeriment s’ha de satisfer inexcusablement en aquells atributs que pertanyen a la clau primària. En segon lloc, per als identificadors numèrics associats a cada instància, el propi sistema pot encarregar-se de gestionar un comptador automàtic de valors mitjançant la construcció AUTO_INCREMENT.

Tornant novament a la nostra base de dades cataleg, ens trobem ara en disposició de crear les taules del catàleg de gens especificades formalment a les figures 18, 19, 20 i 21. Hem d’escollir adequadament els tipus de dades per a cada atribut o camp, segons el seu contingut, definint clarament quines són les claus primàries i foranes. L’usuari pot començar creant les taules més elementals, és a dir, aquelles que no posseeixen claus foranes (genomes i funcions). Observeu com declarem la clau primària i ens assegurem que cap instància pot donar-se d’alta a la base de dades amb un valor nul per a les claus primàries, espècie i funció. Per verificar que el procés de creació ha funcionat correctament, l’usuari pot consultar la base de dades sobre les taules existents amb l’ordre SHOW TABLES.

mysql> SHOW TABLES;

Empty set (0,00 sec)

mysql> CREATE TABLE genomes
    -> especie VARCHAR(100) NOT NULL,
    -> nom VARCHAR(100),
    -> descripcion TEXT,
    -> PRIMARY KEY (especie));

Query OK, 0 row affected (0.28 sec)

mysql> CREATE TABLE funcions
    -> funcion VARCHAR(20) NOT NULL,
    -> descripcion VARCHAR(100),
    -> PRIMARY KEY (funcion));

Query OK, 0 row affected (0.03 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_cataleg |
+-------------------+
| funcions          |
| genomes           |
+-------------------+
2 rows in set (0.00 sec)

És possible revisar la definició d’una taula amb la instrucció DESCRIBE:

mysql> DESCRIBE genomes;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| especie     | varchar(100) | NO   | PRI | NULL    |       |
| nom         | varchar(100) | YES  |     | NULL    |       |
| descripcio  | text         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

A continuació, per crear la taula gens, a més de la clau primària, indiquem un camp o atribut que és la clau forana (que ha d’apuntar o fer referència a la clau primària de la taula genomes):

mysql> CREATE TABLE gens
    -> (nom VARCHAR(20) NOT NULL,
    -> cromosoma VARCHAR(5),
    -> cadena VARCHAR(1),
    -> inici INT,
    -> final INT,
    -> proteina VARCHAR(20),
    -> especie VARCHAR(100),
    -> PRIMARY KEY (nom),
    -> FOREIGN KEY (especie)
    -> REFERENCES genomes(especie));

Query OK, 0 rows affected (0.06 sec)

mysql> DESCRIBE gens;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nom        | varchar(20) | NO   | PRI | NULL    |       |
| cromosoma  | varchar(5)  | YES  |     | NULL    |       |
| cadena     | varchar(1)  | YES  |     | NULL    |       |
| inici      | int(11)     | YES  |     | NULL    |       |
| final      | int(11)     | YES  |     | NULL    |       |
| proteina   | varchar(20) | YES  |     | NULL    |       |
| especie    | varchar(100)| YES  | MUL | NULL    |       |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Finalment, creem la taula anotacions per relacionar els gens amb les anotacions funcionals. Juntament amb els dos valors que identifiquen cada registre (gen i funció), afegirem un atribut per registrar l’origen de la informació:

mysql> CREATE TABLE anotacions
    -> (nom VARCHAR(20) NOT NULL,
    -> funcio VARCHAR(20) NOT NULL,
    -> origen VARCHAR(20),
    -> PRIMARY KEY (nom, funcio),
    -> FOREIGN KEY (nom)
    -> REFERENCES gens(nom),
    -> FOREIGN KEY (funcio)
    -> REFERENCES funcions(funcio));

Query OK, rows affected (0.11 sec)

mysql> DESCRIBE anotacions;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| nom     | varchar(20)  | NO   | PRI | NULL    |       |
| funcio  | varchar(20)  | NO   | PRI | NULL    |       |
| origen  | varchar(20)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

És possible definir altres restriccions als camps de les taules amb l’ordre CHECK.

Per exemple, podem indicar que un camp numèric com el camp inci de la taula gens que és tipus numèric només accepti valors positius CHECK (inici >0), o que un camp de tipus cadena de caràcters només accepti determinats valors; per exemple, perquè el camp hebra de la taula gens només accepti els caràcters «+» o «-», podem fer hebra ENUM(‘+’,’-’).

Un cop creada la taula podem modificar-la amb la instrucció ALTER TABLE.

Per exemple, si volem eliminar el camp origen de la taula anotacions escrivim

ALTER TABLE anotacions DROP COLUMN origen;

i si volem tornar a afegir-hi el mateix camp escrivim

ALTER TABLE anotacions ADD origen VARCHAR(20);

Durant el temps de vida d’una base de dades és freqüent que n’haguem d’actualitzar el contingut. En determinats casos, això pot implicar l’eliminació completa d’usuaris, de taules o, fins i tot, de la pròpia base de dades. Per implementar aquests serveis, MySQL posseeix la família d’ordres DROP.

DROP DATABASE basededades;
DROP USER usuari;
DROP TABLE taula;

Search

1. Bases de dades relacionals

1.5. Creació de taules i restriccions