DAM - DAW - MP 0484 Bases de Dades - NF3

Pràctica 16: - Seqüències - empresa

Aquesta pràctica la podem realitzar a la base test.

Abans de començar crearem una còpia de la taula DEPARTMENTS i l’anomenarem DEP i una còpia d’EMPLOYEES i l’anomenarem EMP (per si no le tinguéssim)

CREATE OR REPLACE TABLE DEP 
    AS SELECT * FROM empresa.DEPARTMENTS;
CREATE OR REPLACE TABLE EMP 
    AS SELECT * FROM empresa.EMPLOYEES;

Crea una seqüència per a utilitzar-la com a columna de clau primària de la taula DEP. La seqüència ha de començar en 200 i tenir un valor màxim de 1000. Fes que la seqüència augmenti de 10 en 10. Assigna a la seqüència el nom DEP_ID_SEQ.
Possible Solució
```
CREATE SEQUENCE DEP_ID_SEQ
    START WITH 200
    MAXVALUE 1000
    INCREMENT BY 10;
```

Escriu una consulta per a mostrar el nom de totes les seqüències creades a la nostra base (consulta la taula TABLES de la base INFORMATION_SCHEMA on el camp TABLE_TYPE ens indica el tipus d’Objecte). Després fes un DESCRIBE de la la seqüència (com si fos una taula) i fes un SELECT d’aquesta per esbrinar els següents valors

minimum_value	maximum_value	start_value	increment
???	1000	200	10

Possible Solució

Quins tipus de taules hi ha a TABLES?

SELECT DISTCINT TABLE_TYPE FROM information_schema.TABLES;

Possible sortida

+-------------+
| TABLE_TYPE  |
+-------------+
| SYSTEM VIEW |
| BASE TABLE  |
| VIEW        |
| SEQUENCE    |
+-------------+

Quines seqüències tenim?

SELECT *
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'SEQUENCE'

Possible sortida

   TABLE_CATALOG: def
    TABLE_SCHEMA: test
      TABLE_NAME: dep_id_seq
      TABLE_TYPE: SEQUENCE
          ENGINE: InnoDB
         VERSION: 10
      ROW_FORMAT: Fixed
      TABLE_ROWS: 1
  AVG_ROW_LENGTH: 16384
     DATA_LENGTH: 16384
 MAX_DATA_LENGTH: 0
    INDEX_LENGTH: 0
       DATA_FREE: 0
  AUTO_INCREMENT: NULL
     CREATE_TIME: 2024-02-20 18:45:12
     UPDATE_TIME: NULL
      CHECK_TIME: NULL
 TABLE_COLLATION: utf8mb4_general_ci
        CHECKSUM: NULL
  CREATE_OPTIONS:
   TABLE_COMMENT:
MAX_INDEX_LENGTH: 0
       TEMPORARY: N

Describe de la seqüència:
```
DESCRIBE dep_id_seq;
```

SELECT dels camps...

SELECT minimum_value, maximum_value, start_value, increment
FROM dep_id_seq;

Crea la clau primària de la taula EMP pel camp EMPLOYEE_ID.

Possible solució

ALTER TABLE EMP 
    ADD CONSTRAINT PK_EMP PRIMARY KEY(EMPLOYEE_ID);

Crea un índex (no únic) pel camp LAST_NAME.

Possible solució

CREATE INDEX I_EMP_LAST_NAME ON EMP(LAST_NAME);

o fins i tot

ALTER TABLE EMP ADD INDEX I_EMP_LAST_NAME (LAST_NAME);

Crea un índex no únic a la columna de clau forana (DEPARTMENT_ID) a la taula EMP.

Possible Solució

CREATE INDEX I_EMP_DEPARTMENT_ID ON EMP(DEPARTMENT_ID);

o fins i tot

ALTER TABLE EMP ADD INDEX I_EMP_DEPARTMENT_ID (DEPARTMENT_ID);

Visualitza els índex i la unicitat d’aquests en el diccionari de dades (information_schema.statistics) per la taula EMP.

Possible solució

SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME
FROM information_schema.statistics 
WHERE TABLE_NAME = 'EMP';

Possible sortida

+--------------+------------+------------+--------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME          |
+--------------+------------+------------+--------------+---------------------+
| test         | emp        |          0 | test         | PRIMARY             |
| test         | emp        |          1 | test         | I_EMP_LAST_NAME     |
| test         | emp        |          1 | test         | I_EMP_DEPARTMENT_ID |
+--------------+------------+------------+--------------+---------------------+