Progettazione fisica

Nelle precedenti lezioni abbiamo visto il ciclo di progettazione di un database:

1. Modello concettuale: dalla realtà di riferimento viene predisposto un diagramma E/R che mostra entità, attributi, relazioni e loro cardinalità. E’ un modello agnostico rispetto alla sua realizzazione digitale.
2. Modello logico-relazionale: si predispone il modello logico-relazionale, che trasforma le entità in tabelle, gli attributi sono definiti con un tipo di dato, vengono definite le chiavi primarie, le relazioni sono definite tramite chiavi esterne, e nelle relazioni molti a molti si usano tabelle di relazione aggiuntive.
3. Normalizzazione: si analizza matematicamente il modello, e lo si aggiusta per permettergli di raggiungere almeno la terza forma normale o la forma normale di Boyce-Codd.

E’ giunto ora il momento di trasformare il modello in un database reale. Per farlo useremo il progetto libreria musicale usato nelle precedenti lezioni.

DDL-DML e SQL

I database relazionali prevedono due tipologie di istruzioni per la loro gestione:

• DDL (Data Design Language): un sistema per gestire la struttura del database, ovvero per creare le tabelle, le relazioni, i vincoli, ma anche per gestire la cancellazione o alterazione delle stesse. Il DDL permette anche di gestire altre componenti del database, come le transazioni, che vedremo più avanti.
• DML (Data Manipulation Language): un sistema per manipolare dati, ovvero inserimento, modifica, cancellazione, estrazione dei dati. Queste operazioni vengono anche chiamate CRUD (Create, Read, Update, Delete).

DDL e DML sono concettualmente distinti. Il primo è utilizzato dal progettista per ragioni di sicurezza ed integrità del sistema. I database di norma sono progettati per non essere modificati nella loro struttura, a meno di una revisione del sistema informatico nel suo complesso, che ha un impatto però che va oltre il database stesso ma coinvolge l’intero sistema informativo.

Il secondo è invece utilizzato dagli utenti finali: essi sono di norma l’amministratore di sistema, l’utente con i maggiori privilegi, l’utente finale per la normale attività di CRUD, così come in modo automatico dal software applicativo che accede al database.

La quasi totalità degli RDBMS usa come linguaggio principale SQL, che ha sia una parte DDL che una DML. Non si tratta di un linguaggio di programmazione tradizionale (ovvero che rispetta il teorema di Bohm-Jacopini), quindi non ha, almeno nella sua versione standard, variabili, condizioni e cicli. Il linguaggio invece permette di creare delle istruzioni, dette query, strutturate secondo un proprio lessico ed una propria grammatica da eseguire sul database.

La query è quindi un comando composito che consente di eseguire una specifica istruzione che può, in DDL, alterare lo schema delle tabelle, e in DML, eseguire una operazione CRUD su una o più tabelle.

In queste lezioni useremo, a livello laboratoriale, Mysql/MariaDB come RDBMS. Si tratta di un RDBMS gratuito, semplice da usare, con un dialetto SQL standardizzato e che dispone di una serie di tool online che consentono di creare database su piattaforme cloud, almeno a livello scolastico. Mysql Di norma useremo quindi alcuni costrutti del dialetto di MySQL.

Realizzare un database con SQL

Ripartiamo dal progetto visto nelle scorse lezioni.

Creazione del database

Per creare il database è sufficiente questa query:

CREATE DATABASE LibreriaMusicale;

Creazione di tabelle

La prima operazione da svolgere è indicare la lista delle tabelle da creare:
– Tabelle indipendenti

CasaDiscografica (Id, nome)
Supporto(Id, descrizione)
Genere(Id, descrizione)
Autore(Id, nome, cognome)

– tabelle dipendenti direttamente dalle tabelle indipendenti
Contratto(Id, durata, AutoreId, AlbumId)
Album(Id, titolo, anno, CasaDiscograficaId, AutoreId)

– tabelle dipendenti dalle precedenti
Booklet(Id, pagine, AlbumId)
Album_Supporto(AlbumId, SupportoId)
Album_Genere(AlbumId, GenereId)
Canzone(Id, Titolo, AlbumId)


Tabelle indipendenti

Con questa suddivisione otteniamo l’ordine di creazione delle tabelle. In SQL infatti dobbiamo creare le tabelle partendo da quelle indipendenti, così come in qualsiasi linguaggio non possiamo dichiarare un simbolo senza prima dichiararlo.

CREATE TABLE AUTORE (
    Id INT AUTO_INCREMENT,
    Nome VARCHAR(100) NOT NULL,
    Cognome VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Autore PRIMARY KEY (Id)
);

CREATE TABLE CASA_DISCOGRAFICA (
    Id INT AUTO_INCREMENT,
    Nome VARCHAR(150) NOT NULL,
    CONSTRAINT PK_CasaDiscografica PRIMARY KEY (Id)
);

CREATE TABLE GENERE (
    Id INT AUTO_INCREMENT,
    Descrizione VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Genere PRIMARY KEY (Id),
    CONSTRAINT UQ_Genere_Descrizione UNIQUE (Descrizione)
);

CREATE TABLE SUPPORTO (
    Id INT AUTO_INCREMENT,
    Descrizione VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Supporto PRIMARY KEY (Id)
);

Vediamo l’anatomia di questa query:

CREATE TABLE NOME_TABELLA {
  NOME_ATTRIBUTO1 TIPO REGOLE,
  NOME_ATTRIBUTO2 TIPO REGOLE,
 ...
  CONSTRAINT NOME_CONSTRAINT REGOLA_DI_TABELLA
 ...
};

Dove:

• CREATE TABLE NOME_TABELLA: comando di creazione
• NOME_ATTRIBUTO TIPO REGOLE: indica il nome dell’attributo, il tipo (già visto nella precedente lezione) e le regole dell”attributo. Le regole possono essere:
  • AUTO_INCREMENT: applicabile solo ad elementi di tipo INT. Il database ad ogni inserimento, incrementa il valore dell’attributo (partendo da 1).
  • NOT NULL: è un vincolo, il database controlla se all’inserimento è presente un valore per l’attributo, altrimenti da errore
  • DEFAULT valore: è un valore che viene inserito dal sistema se la query di inserimento non lo contiene.
• REGOLE_DI_TABELLA: sono regole che sono applicate a livello di tabella. Eccone alune:
  • PRIMARY KEY(attributo1, attributo2, …): indica gli attributi chiave primaria
  • UNIQUE (attributo1, attributo2, …): indica che gli attributi indicati hanno il vincolo UNIQUE, visto nella precedente lezione. Possono esserci più regole UNIQUE.

Le regole di tabella hanno sempre un nome, per poterle modificare o cancellare successivamente.

Tabelle dipendenti

Creiamo adesso le tabelle dipendenti direttamente da queste:

CREATE TABLE CONTRATTO (
    Id INT AUTO_INCREMENT,
    Durata INT NOT NULL,
    AutoreId INT NOT NULL,
    CasaDiscograficaId INT NOT NULL,
    CONSTRAINT PK_Contratto PRIMARY KEY (Id),
    CONSTRAINT FK_Contratto_Autore FOREIGN KEY (AutoreId) REFERENCES AUTORE(Id),
    CONSTRAINT FK_Contratto_CasaDiscografica FOREIGN KEY (CasaDiscograficaId) REFERENCES CASA_DISCOGRAFICA(Id)
);

CREATE TABLE ALBUM (
    Id INT AUTO_INCREMENT,
    Titolo VARCHAR(200) NOT NULL,
    Anno INT NOT NULL,
    CasaDiscograficaId INT NOT NULL,
    AutoreId INT,
    CONSTRAINT PK_Album PRIMARY KEY (Id),
    CONSTRAINT FK_Album_CasaDiscografica FOREIGN KEY (CasaDiscograficaId) REFERENCES CASA_DISCOGRAFICA(Id),
    CONSTRAINT FK_Album_Autore FOREIGN KEY (AutoreId) REFERENCES AUTORE(Id)
);

Come si vede è introdotta una nuova regola di tabella:

CONSTRAINT Nome_Constraint FOREIGN KEY (Attributo) REFERENCES NOME_ALTRA_TABELLA(Attributo_altra_tabella) ON DELETE CASCADE

In dettaglio:

• la Foreign Key indica il vincolo dell’attributo locale con quello dell’attributo dell’altra tabella
• ON DELETE indica il comportamento automatico da svolgere in caso di eliminazione, in questo caso CASCADE

Vincoli delle foreign key

Esaminiamo in questa tabella quali vincoli si possono mettere ed il loro significato:

Questo meccanismo di controllo dei vincoli si chiama integrità referenziale e controlla che non ci siano inserimenti errati con chiavi inesistenti, o cancellazioni di dati con chiavi referenziate altrove.

Ultime tabelle

Creiamo le tabelle restanti:

CREATE TABLE CANZONE (
    Id INT AUTO_INCREMENT,
    Titolo VARCHAR(200) NOT NULL,
    AlbumId INT NOT NULL,
    CONSTRAINT PK_Canzone PRIMARY KEY (Id),
    CONSTRAINT FK_Canzone_Album FOREIGN KEY (AlbumId) REFERENCES ALBUM(Id) ON DELETE CASCADE
);

CREATE TABLE BOOKLET (
    Id INT AUTO_INCREMENT,
    Pagine INT NOT NULL,
    AlbumId INT NOT NULL,
    CONSTRAINT PK_Booklet PRIMARY KEY (Id),
    CONSTRAINT FK_Booklet_Album FOREIGN KEY (AlbumId) REFERENCES ALBUM(Id) ON DELETE CASCADE
);

CREATE TABLE ALBUM_GENERE (
    AlbumId INT NOT NULL,
    GenereId INT NOT NULL,
    CONSTRAINT PK_AlbumGenere PRIMARY KEY (AlbumId, GenereId),
    CONSTRAINT FK_AlbumGenere_Album FOREIGN KEY (AlbumId) REFERENCES ALBUM(Id) ON DELETE CASCADE,
    CONSTRAINT FK_AlbumGenere_Genere FOREIGN KEY (GenereId) REFERENCES GENERE(Id) ON DELETE CASCADE
);

CREATE TABLE ALBUM_SUPPORTO (
    AlbumId INT NOT NULL,
    SupportoId INT NOT NULL,
    CONSTRAINT PK_AlbumSupporto PRIMARY KEY (AlbumId, SupportoId),
    CONSTRAINT FK_AlbumSupporto_Album FOREIGN KEY (AlbumId) REFERENCES ALBUM(Id) ON DELETE CASCADE,
    CONSTRAINT FK_AlbumSupporto_Supporto FOREIGN KEY (SupportoId) REFERENCES SUPPORTO(Id) ON DELETE CASCADE
);

A questo punto il database è pronto per essere utilizzato, e si può passare al DML per le normali operazioni di inserimento, modifica, cancellazione, ricerca, che vedremo nella lezione successiva.

Modificare il database

Alter

Con DDL si può modificare una tabella con il comando ALTER.

ALTER TABLE AUTORE 
ADD Nazionalita VARCHAR(50) DEFAULT 'Italiana';

Il comando ADD aggiunge un attributo alla tabella.

ALTER TABLE ALBUM 
MODIFY Titolo VARCHAR(500) NOT NULL;

Il comando MODIFY modifica le proprietà di un attributo.

ALTER TABLE CASA_DISCOGRAFICA 
CHANGE Nome RagioneSociale VARCHAR(250) NOT NULL;

Il comando CHANGE modifica invece il nome dell’attributo.

ALTER TABLE ALBUM 
DROP COLUMN Anno;

Il comando DROP elimina un attributo.

ALTER TABLE CANZONE 
DROP FOREIGN KEY FK_Canzone_Album; 
ALTER TABLE CANZONE 
ADD CONSTRAINT FK_Canzone_Album 
FOREIGN KEY (AlbumId) REFERENCES ALBUM(Id) ON DELETE RESTRICT;

Per poter modificare una constraints, va prima cancellata e poi riaggiunta.

Si può anche solo cancellare.

ALTER TABLE CANZONE 
DROP FOREIGN KEY FK_Canzone_Album;

Si può cancellare anche la primary key:

ALTER TABLE BOOKLET
DROP PRIMARY KEY

E’ infine possibile rinominare una tabella, con RENAME:

ALTER TABLE CASA_DISCOGRAFICA 
RENAME TO ETICHETTA_DISCOGRAFICA;

Drop

E’ possibile eliminare una tabella con DROP:

DROP TABLE AUTORE;

Nella cancellazione occorre fare attenzione: non si può cancellare una tabella per cui esistono delle dipendenze, cioè foreign key, in altre tabelle. Vanno prima cancellate le tabelle dipendenti:

DROP TABLE ALBUM_GENERE;
DROP TABLE GENERE;

Per evitare errori, specie con script automatici, si usa la clausola IF EXISTS:

DROP TABLE GENERE IF EXISTS;

che non da errore se la tabella non esiste più.

Infine è possibile cancellare l’intero database

DROP DATABASE LibreriaMusicale;

Conclusioni

Per la creazione di un database e la sua struttura, partiamo dallo schema logico relazionale, e tramite SQL (DDL) creiamo le tabelle seguendo la specifica sintassi del linguaggio, basato sul concetto di query.

Tramite SQL possiamo eseguire le seguenti operazioni:

• CREATE, che crea database e tabelle, con i relativi attributi, relazioni e vincoli
• ALTER, che modifica le singole tabelle, eliminando/modificando/aggiungendo attributi, relazioni e vincoli
• DROP, che elimina le singole tabelle o il database.