68 DBMS 6 – ampliare il database 3

Pubblicato il di

Supponiamo di voler fare in modo che gli utenti possano creare uno o più questionari, effettuiamo un’associazione 1 a molti tra la nostra tabella utenti e una nuova che creiamo chiamata questionari.

CREATE TABLE IF NOT EXISTS `quizMaker`.`questionari` (
 `idquestionario` INT(11) NOT NULL AUTO_INCREMENT,
 `descrizione_questionario` VARCHAR(200) NULL DEFAULT NULL,
 `data_creazione` DATETIME NULL DEFAULT NULL,
 PRIMARY KEY (`idquestionario`))
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8

creiamo l’associazione 1:n tra fkusers della tabella questionari e iduser della tabella users (il codice sql sotto è generato automaticamente da mysql workbench)

ALTER TABLE `quizMaker`.`questionari` 
ADD COLUMN `fkusers` INT(11) NOT NULL AFTER `data_creazione`,
ADD INDEX `fk_questionari_users1_idx` (`fkusers` ASC)
ALTER TABLE `quizMaker`.`questionari` 
ADD CONSTRAINT `fk_questionari_users1`
 FOREIGN KEY (`fkusers`)
 REFERENCES `quizMaker`.`users` (`iduser`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

Ovviamente non so quante domande avranno i nostri questionari, potrebbero essere 1 come 100, quindi le colonne delle domande non possiamo inserirle manualmente.

Un questionario può essere associato a n domande e per una stessa domanda potrò avere più questionari quindi non avrebbe senso mettere in questionari un codice domande e viceversa in domande un codice questionari. In questo caso l’associazione sarà molti a molti, a più questionari possono corrispondere più domande, anche ripetute.

Il simbolo grafico per il molti a molti è n:m .

Per fare ciò avrò bisogno di una tabella intermedia tra questionari e domande dove memorizzerò il codice di un questionario e il codice di ua sua domanda, e poi si potrebbe avere una seconda riga con magari lo stesso codice di questionario con il codice di una sua seconda domanda (diversa ovviamente), poi stesso cod questionario e codice tutte le sue domande, senza limite. Non esiste ancora una domanda inserita per quel questionario, non ci saranno righe con quel codice questionario. Il questionario a 5 domande, avremo 5 righe con quel codice questionario, massima flessibilità.

Andando a cliccare sul simbolo n:m nell’ EER di workbench, verrà generata automaticamente la tabella che lega le altre 2. In questa tabella avrò il campo codice domanda e codice questionario, ma allora mi manca la chiave primaria che non potrà essere nessuno dei 2 campi autogenerati, perchè la primary key non può ripetersi con lo stesso valore, a differenza del cod questionario e del cod domanda che abbiamo detto possono ripetersi. Una soluzione è che entrambi i campi fungano da chiave primaria  e in coppia formano una chiave primaria multicampo (il cod questionario 12 ha il cod domanda 45, tale coppia di codici è univoca, non si ripeterà mai più). La chiave primaria multicampo può essere applicatanon solo su una coppia di campi, ma anche su più campi. In molti casi la chiave primaria multicampo, potrebbe rivelarsi scomoda e generare confusione, allora la soluzione è togliere l’attributo primary key dai 2 campi che rinomino in fkQuestionario e fkDomanda. La tabella la rinomino in items e aggiungo un campo idItems che fungerà da Primary key. Ecco il codice:

CREATE TABLE IF NOT EXISTS `quizMaker`.`Items` (
 `fkQuestionario` INT(11) NOT NULL,
 `fkDomanda` INT(11) NOT NULL,
 `idItems` INT(11) NOT NULL AUTO_INCREMENT,
 INDEX `fk_questionari_has_domande_domande1_idx` (`fkDomanda` ASC),
 INDEX `fk_questionari_has_domande_questionari1_idx` (`fkQuestionario` ASC),
 PRIMARY KEY (`idItems`),
 CONSTRAINT `fk_questionari_has_domande_questionari1`
 FOREIGN KEY (`fkQuestionario`)
 REFERENCES `quizMaker`.`questionari` (`idquestionario`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_questionari_has_domande_domande1`
 FOREIGN KEY (`fkDomanda`)
 REFERENCES `quizMaker`.`domande` (`iddomanda`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)

Supponiamo che ad ogni gruppo di corsisti che partecipa ai questionari vorremmo sottoporre le domande nello stesso ordine, magari per facilitarne la correzione, allora potrebbe essere utile aggiungere un campo, anche se poi non lo usiamo, quindi ad ogni gruppo di corsisti verrà sottoposta sempre quella, come prima domanda e sempre la stessa sequenze di domande. Possiamo chiamare il campo progressivo e avrà un incremento di valore per ogni items questionario, quando poi cambia questionario il conteggio del campo progressivo ripartirà da 1

ALTER TABLE `quizMaker`.`Items` 
ADD COLUMN `progressivo` INT(11) NULL DEFAULT NULL AFTER `idItems`

al momento avremmo un archivio comune tra docenti / domande / risposte. Il ragionamento logico incontra non poche falle: un docente inserisce 1 domanda, un altro da un’ assegnazione di punteggio a tale domanda, un altro la modifica, un altro attribuisce una risposta differente… Bisogna separare gli archivi domande risposte, basta aggiungere nella tabella argomenti il campo codice docente che l’ha inserito, tale codice docente viene ereditato dalle tabelle domande e risposte. Quindi creiamo un legame 1:n tra argomenti e users e chiamiamo il campo fkUsers:

ALTER TABLE `quizMaker`.`argomenti` 
ADD COLUMN `fkUsers` INT(11) NULL DEFAULT NULL AFTER `argomento`,
ADD INDEX `fk_argomenti_users1_idx` (`fkUsers` ASC)
ALTER TABLE `quizMaker`.`argomenti` 
ADD CONSTRAINT `fk_argomenti_users1`
FOREIGN KEY (`fkUsers`)
REFERENCES `quizMaker`.`users` (`iduser`)

Per  completare il programma creiamo una nuova tabella questionari_svolti con i campi inizio e fine, che ci permetterà di registrare quando un utente ha svolto tale questionario associato a tale docente, e magari vogliamo anche vedere se lo risvolge.

CREATE TABLE IF NOT EXISTS `quizMaker`.`questionari_svolti` (
 `idquestionario_svolto` INT(11) NOT NULL AUTO_INCREMENT,
 `inizio` DATETIME NULL DEFAULT NULL,
 `fine` DATETIME NULL DEFAULT NULL COMMENT ' ',
 PRIMARY KEY (`idquestionario_svolto`))

poi creiamo l’associazione 1:n tra users questionari_svolti e questionari e questionari_svolti

ALTER TABLE `quizMaker`.`questionari_svolti` 
ADD COLUMN `fkUsers` INT(11) NOT NULL AFTER `fine`,
ADD COLUMN `fkQuestionari` INT(11) NOT NULL AFTER `fkUsers`,
ADD INDEX `fk_questionari_svolti_users1_idx` (`fkUsers` ASC),
ADD INDEX `fk_questionari_svolti_questionari1_idx` (`fkQuestionari` ASC)
ALTER TABLE `quizMaker`.`questionari_svolti` 
ADD CONSTRAINT `fk_questionari_svolti_users1`
 FOREIGN KEY (`fkUsers`)
 REFERENCES `quizMaker`.`users` (`iduser`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_questionari_svolti_questionari1`
 FOREIGN KEY (`fkQuestionari`)
 REFERENCES `quizMaker`.`questionari` (`idquestionario`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

adesso posso sapere quale utente effettua quale questionario, interrogando la tabella qustionari_svolti.

Ora devo capire le domande associate a questi questionari svolti, quindi creiamo una tabella risposte_date e la associamo a domande, questionari_svolti e domande.

CREATE TABLE IF NOT EXISTS `quizMaker`.`risposte_date` (
 `idrisposta_data` INT(11) NOT NULL AUTO_INCREMENT,
 `fkDomanda` INT(11) NOT NULL,
 `fkQuestionarioSvolto` INT(11) NOT NULL,
 `fkRisposta` INT(11) NOT NULL,
 PRIMARY KEY (`idrisposta_data`),
 INDEX `fk_risposte_date_domande1_idx` (`fkDomanda` ASC),
 INDEX `fk_risposte_date_questionari_svolti1_idx` (`fkQuestionarioSvolto` ASC),
 INDEX `fk_risposte_date_risposte1_idx` (`fkRisposta` ASC),
 CONSTRAINT `fk_risposte_date_domande1`
 FOREIGN KEY (`fkDomanda`)
 REFERENCES `quizMaker`.`domande` (`iddomanda`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_risposte_date_questionari_svolti1`
 FOREIGN KEY (`fkQuestionarioSvolto`)
 REFERENCES `quizMaker`.`questionari_svolti` (`idquestionario_svolto`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_risposte_date_risposte1`
 FOREIGN KEY (`fkRisposta`)
 REFERENCES `quizMaker`.`risposte` (`idrisposta`)
 ON DELETE NO ACTION

Ecco uno screenshot del diagramma EER: