Progetto basi di dati (tesina SQL - database)

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Dipartimento” della relazione IMPIEGATI referenzia l'attributo

“Numero” della relazione DIPARTIMENTI

DIREZIONE ≡ {Impiegato, Dipartimento}

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento}

DIPARTIMENTI ≡ {Numero, Nome, Sede, Direttore}

L'associazione uno ad uno “direzione” ha partecipazione obbligatoria soltanto da parte dell'entità

“dipartimento”, dunque viene fatta assorbire da quest'ultima. La relazione DIPARTIMENTI,

dunque, è stata modificata in quanto è stato aggiunto l'attributo “direttore”.

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Direttore” della relazione DIPARTIMENTI referenzia l'attributo

“Codice” della relazione IMPIEGATI

PARTECIPAZIONI ≡ {Impiegato, Progetto}

L'associazione molti a molti “partecipazione” viene tradotta in una relazione avente come attributi

proprio le entità che vi partecipavano.

La CHIAVE PRIMARIA è costituita proprio da entrambi gli attributi “impiegato” e “progetto”.

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Impiegato” della relazione PARTECIPAZIONI referenzia l'attributo

“Codice” della relazione IMPIEGATI

• L'attributo “Progetto” della relazione PARTECIPAZIONI referenzia l'attributo

“Numero” della relazione PROGETTI 13

HA A CARICO ≡ {Impiegato, Familiare}

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento}

FAMILIARI ≡ {Nome, AnnodiNascita, Sesso, Parentela, Acaricodi}

L'associazione uno a molti “ha a carico” presentava un identificatore esterno, pertanto viene fatta

assorbire dall'entità “familiare” che muta il suo schema grazie all'aggiunta dell'attributo

“Acaricodi”, che, tra l'altro, diventa chiave primaria insieme all'attributo “nome”.

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Acaricodi” della relazione FAMILIARI referenzia l'attributo “Codice”

della relazione IMPIEGATI.

GESTIONI ≡ {Dipartimento, Progetto}

L'associazione molti a molti “gestione” viene tradotta in una relazione avente come attributi proprio

le entità che vi partecipavano.

La CHIAVE PRIMARIA è costituita proprio da entrambi gli attributi “dipartimento” e “progetto”.

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Dipartimento” della relazione GESTIONI referenzia l'attributo

“Numero” della relazione DIPARTIMENTO

• L'attributo “Progetto” della relazione GESTIONI referenzia l'attributo “Numero”

della relazione PROGETTI

COORDINAZIONE ≡ {ImpiegatoResponsabile, Impiegato}

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento,

Responsabile}

L'associazione ricorsiva uno a molti “coordinazione” viene fatta assorbire dall'unica entità

partecipante “impiegato”, che viene modificata in quanto viene aggiunto l'attributo “responsabile”.

VINCOLI DI INTEGRITA' REFERENZIALE:

• L'attributo “Responsabile” della relazione IMPIEGATI referenzia l'attributo

“Codice” della relazione IMPIEGATI

Elaborato Basi di Dati Università degli Studi del Sannio

2.3 Ricapitolazione della traduzione dello schema E-R in schema

Relazionale

Relazioni:

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento,

Responsabile}

FAMILIARI ≡ {Nome, AnnodiNascita, Sesso, Parentela, Acaricodi}

DIPARTIMENTI ≡ {Numero, Nome, Sede, Direttore}

PROGETTI ≡ {Numero, Codice}

PARTECIPAZIONI ≡ {Impiegato, Progetto}

GESTIONI ≡ {Dipartimento, Progetto}

Vincoli di integrità referenziale

IMPIEGATI.Dipartimento DIPARTIMENTI.Numero



IMPIEGATI.Responsabile IMPIEGATI.Codice



FAMILIARI.Acaricodi IMPIEGATI.Codice



DIPARTIMENTI.Direttore IMPIEGATI.Codice



PARTECIPAZIONI.Impiegato IMPIEGATI.Codice



PARTECIPAZIONI.Progetto PROGETTI.Numero



GESTIONI.Dipartimento DIPARTIMENTI.Numero



GESTIONI.Progetto PROGETTI.Numero

 15

2.4 Normalizzazione dello schema Relazionale

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento,

Responsabile}

• E' in Prima Forma Normale in quanto non presenta attributi composti.

• E' in Seconda Forma Normale in quanto è in Prima Forma Normale e i suoi attributi non

primi sono tutti in dipendenza funzionale completa dalla chiave

• E' in Terza Forma Normale in quanto è in Seconda Forma Normale e Codice è una

superchiave. Gli attributi non chiave dipendono soltanto alla chiave, ossia non esistono

attributi che dipendono da attributi non chiave.

• E' nella Forma Normale di Boyce e Codd (BCNF) in quanto è in Terza Forma Normale e

per ogni dipendenza funzionale non banale, Codice è una superchiave.

FAMILIARI ≡ {Nome, AnnodiNascita, Sesso, Parentela, Acaricodi}

• E' in Prima Forma Normale.

• E' in Seconda Forma Normale.

• E' in Terza Forma Normale.

• E' nella Forma Normale di Boyce e Codd (BCNF).

DIPARTIMENTI ≡ {Numero, Nome, Sede, Direttore}

• E' in Prima Forma Normale.

• E' in Seconda Forma Normale.

Elaborato Basi di Dati Università degli Studi del Sannio

• E' in Terza Forma Normale.

• E' nella Forma Normale di Boyce e Codd (BCNF)

PROGETTI ≡ {Numero, Codice}

• E' in Prima Forma Normale.

• E' in Seconda Forma Normale.

• E' in Terza Forma Normale.

• E' nella Forma Normale di Boyce e Codd (BCNF)

PARTECIPAZIONI ≡ {Impiegato, Progetto}

• E' in Prima Forma Normale.

• E' in Seconda Forma Normale.

• E' in Terza Forma Normale.

• E' nella Forma Normale di Boyce e Codd (BCNF)

GESTIONI ≡ {Dipartimento, Progetto}

•

• E' in Prima Forma Normale.

• E' in Seconda Forma Normale.

• E' in Terza Forma Normale.

• E' nella Forma Normale di Boyce e Codd (BCNF) 17

Implementazione del database in SQL

3.1 Tabelle delle relazioni con relativo codice SQL implementato in

mySQL

IMPIEGATI ≡ {Codice, Nome, Cognome, AnnodiNascita, Sesso, Dipartimento,

Responsabile}

ATTRIBUTO TIPO VINCOLO NOTE

Codice VARCHAR(50) PRIMARY_KEY

Nome VARCHAR(50)

Cognome VARCHAR(50)

AnnodiNascita YEAR Può essere 'M' o 'F'

Sesso ENUM - Si riferisce a

Dipartimento INT (UNSIGNED) FOREIGN_KEY DIPARTIMENTI.Numero

- Ammesso anche NULL

Elaborato Basi di Dati Università degli Studi del Sannio

come valore

- Si riferisce a

Responsabile VARCHAR(50) FOREIGN_KEY IMPIEGATI.Codice

- Ammesso anche NULL

come valore

Codice SQL:

CREATE TABLE `impiegati` (

`Codice` VARCHAR(50) NOT NULL COMMENT 'Codice identificativo dell\'impiegato',

`Nome` VARCHAR(50) NOT NULL COMMENT 'Nome dell\'impiegato',

`Cognome` VARCHAR(50) NOT NULL COMMENT 'Cognome dell\'impiegato',

`Annodinascita` YEAR NOT NULL COMMENT 'Anno di nascita dell\'impiegato',

`Sesso` ENUM('M','F') NOT NULL COMMENT 'Sesso dell\'impiegato. Può assumere il

valore \'M\' o \'F\'',

`Dipartimento` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Dipartimento di

riferimento dell\'impiegato',

`Responsabile` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Responsabile

dell\'impiegato. Può essere \'NULL\' nel caso sia esso stesso il responsabile',

PRIMARY KEY (`Codice`),

INDEX `FK_impiegato_impiegato` (`Responsabile`),

INDEX `FK_impiegato_dipartimento` (`Dipartimento`),

CONSTRAINT `FK_impiegato_dipartimento` FOREIGN KEY (`Dipartimento`) REFERENCES

`dipartimenti` (`Numero`),

CONSTRAINT `FK_impiegato_impiegato` FOREIGN KEY (`Responsabile`) REFERENCES

`impiegati` (`Codice`)

)

COMMENT='Impiegati dell\'azienda'

Descrizione: Gli attributi della relazione IMPIEGATI sono: “Codice”, “Nome”, “Cognome”,

“AnnodiNascita”, “Sesso”, “Dipartimento” e “Responsabile”.

Codice è la chiave della relazione ed è di tipo VARCHAR(50), il che significa che è una stringa di

caratteri di dimensione variabile che comprende al massimo 50 caratteri.

Anche “Nome” e “Cognome” sono di tipo VARCHAR(50).

“AnnodiNascita” è di tipo YEAR, e rappresenta un valore numerico di 4 cifre che simboleggia un

anno.

“Sesso” rappresenta il sesso dell'impiegato, è una scelta limitata soltanto a due possibità: “M” per

Maschile e “F” per “Femminile”; questa scelta è presente nella relazione tramite la presenza del tipo

ENUM, ovvero un tipo che concede la possibilità di scegliere solo uno dei tipi preimpostati.

“Dipartimento” è di tipo INT (UNSIGNED) in quanto referenzia il numero del dipartimento al

quale afferisce l'impiegato. Esso permette anche il valore NULL in quanto un impiegato può anche

dirigere anziché afferire ad un dipartimento.

Infine “Responsabile” è di tipo VARCHAR(50) in quanto referenzia il codice dell'impiegato che ne

funge da coordinatore. Anch'esso permette il valore NULL nel caso in cui un impiegato sia esso 19

stesso responsabile di altri impiegati.

FAMILIARI ≡ {Nome, AnnodiNascita, Sesso, Parentela, Acaricodi}

ATTRIBUTO TIPO VINCOLO NOTE

Nome VARCHAR(50) PRIMARY_KEY

AnnodiNascita YEAR Può essere 'M' o 'F'

Sesso ENUM

Parentela VARCHAR(50) Si riferisce a

Acaricodi VARCHAR(50) PRIMARY_KEY IMPIEGATI.Codice

FOREIGN_KEY

Codice SQL:

CREATE TABLE `familiari` (

`Nome` VARCHAR(50) NOT NULL COMMENT 'Nome del familiare',

`Annodinascita` YEAR NOT NULL COMMENT 'Anno di nascita del familiare',

`Sesso` ENUM('M','F') NOT NULL COMMENT 'Sesso del familiare. Può assumere i

valori \'M\' o \'F\'',

`Acaricodi` VARCHAR(50) NOT NULL COMMENT 'Impiegato di cui il soggetto è

familiare',

PRIMARY KEY (`Nome`, `Acaricodi`),

INDEX `FK_familiare_impiegato` (`Acaricodi`),

CONSTRAINT `FK_familiare_impiegato` FOREIGN KEY (`Acaricodi`) REFERENCES

`impiegati` (`Codice`)

)

COMMENT='Familiar