[MySql] Urgente qualche domandina

[D4rkAng3l]

Dopodomani devo presentare il progetto per base di dati.

Si tratta di un sistema di gestione di aste online.
Con MySql ho appena creato le 12 tabelle necessarie a rappresentare il mio schema relazionale e sono le seguenti:


create table UTENTE
(
User_Id varchar(20) not null primary key,
Nome varchar(20) not null,
Cognome varchar(20) not null,
Data_Nascita date not null,
Citta varchar(30) not null,
Indirizzo varchar(50) not null
) type=InnoDB;


create table INSERZIONE
(
Id_Oggetto int not null auto_increment primary key,
Titolo varchar(60) not null,
Descrizione text,
Costo_Base float(4,2),
Scadenza datetime,
Compralo_Subito float(4,2),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Id_Categoria varchar(30) not null references CATEGORIA(Id_Categoria)
) type=InnoDB;


create table FOTO
(
Id_Foto int not null auto_increment primary key,
Titolo varchar(50) not null,
Descrizione varchar(255) not null,
Link_File varchar(255) not null,
Id_Oggetto int not null,
foreign key(Id_Oggetto) references INSERZIONE(Id_Oggetto)
) type=InnoDB;


create table OFFERTA
(
Id_Offerta int not null auto_increment primary key,
Partecipante varchar(20) not null,
Id_Oggetto int not null,
Valore_Offerta float(4,2) not null,
Data datetime not null,
foreign key (Partecipante) references UTENTE(User_Id),
foreign key (Id_Oggetto) references INSERZIONE(Id_Oggetto)
) type=InnoDB;

create table CATEGORIA
(
Id_Categoria int not null auto_increment primary key,
Nome_Cat varchar(40) not null,
Descrizione varchar(255) not null,
Padre int references CATEGORIA(Id_Categoria)
) type=InnoDB;


create table AMMINISTRATORE
(
Username varchar(20) not null primary key,
Nome varchar(20) not null,
Cognome varchar(20) not null,
Citta varchar(30) not null,
Indirizzo varchar(50) not null
) type=InnoDB;


create table AMMINISTRA
(
Admin varchar(20) not null,
Id_Categoria int not null,
Inizio_Amministrazione datetime,
foreign key (Admin) references AMMINISTRATORE(Username),
foreign key (Id_Categoria) references CATEGORIA(Id_Categoria),
primary key (Admin, Id_Categoria)
) type=InnoDB;


create table MESSAGGIO
(
Id_Messaggio int not null auto_increment primary key,
Mitt varchar(20) not null references UTENTE(User_Id),
Dest varchar(20) not null references UTENTE(User_Id),
Data datetime not null,
Testo text not null
) type=InnoDB;


create table ACQUISTI
(
Id_Oggetto int not null primary key references INSERZIONE(Id_Oggetto),
Acquirente varchar(20) not null references UTENTE(User_Id),
Spesa float(4,2) not null,
Data datetime not null
) type=InnoDB;


create table COMMENTO
(
Id_Commento int not null primary key,
Commentatore varchar(20) not null,
Oggetto_Commentato int not null,
Data datetime not null,
Commento text not null,
foreign key (Commentatore) references UTENTE(User_Id),
foreign key (Oggetto_Commentato) references INSERZIONE(Id_Oggetto)
) type=InnoDB;


create table FEEDBACKVENDITORE
(
Id_Oggetto int not null references INSERZIONE(Id_Oggetto),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Id_Acquirente varchar(20) not null references UTENTE(User_Id),
Feedback enum('Positivo', 'Neutro', 'Negativo') not null,
Commento varchar(255),
primary key (Id_Oggetto)
) type=InnoDB;


create table FEEDBACKACQUIRENTE
(
Id_Oggetto int not null references INSERZIONE(Id_Oggetto),
Id_Acquirente varchar(20) not null references UTENTE(User_Id),
Id_Venditore varchar(20) not null references UTENTE(User_Id),
Feedback enum('Positivo', 'Neutro', 'Negativo') not null,
Commento varchar(255),
primary key (Id_Oggetto)
) type=InnoDB;


Come potete vedere la tabella AMMINISTRA contiene una foreign key che punta verso AMMINISTRATORE.
C'è modo che se un utente viene cancellato dalla tabella AMMINISTRATORE venga anche automaticamente tolto da tutti i recordo di AMMINISTRA?

Per favore è urgente...ormai manca poco :eek:

Grazie
Andrea

[vizzz]

io ho esperienza con postgres, e questo comportamento si ottiene con un constraint di tipo cascade sull'evento delete.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;

in questo esempio per mysql capisci bene come funziona

[D4rkAng3l]

io ho esperienza con postgres, e questo comportamento si ottiene con un constraint di tipo cascade sull'evento delete.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;

in questo esempio per mysql capisci bene come funziona

non mi apre quel link...cmq sono in crisi...manca poco tempo e c'è ancora molto da fare...se qualcuno potrebbe darmi una manina pratica del tipo: "Si fà così" gliene sarei grato perchè cmq non ho molto tempo per mettermi a studiare cose...devo assolutamente finire e consegnare entro il 20 se no se ne riparla a Giugno per fare l'orale e verbalizzare :cry:

[D4rkAng3l]

io ho esperienza con postgres, e questo comportamento si ottiene con un constraint di tipo cascade sull'evento delete.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;

in questo esempio per mysql capisci bene come funziona

ok...che fà esattamente on delete cascade? vuol dire che quando elimini una certa riga da parent identificata dal campo id vengono tolte le righe da child che usano quell'id per referenziarle in parent?

Grazie

[vizzz]

non mi apre quel link...cmq sono in crisi...manca poco tempo e c'è ancora molto da fare...se qualcuno potrebbe darmi una manina pratica del tipo: "Si fà così" gliene sarei grato perchè cmq non ho molto tempo per mettermi a studiare cose...devo assolutamente finire e consegnare entro il 20 se no se ne riparla a Giugno per fare l'orale e verbalizzare :cry:

nell'esempio che ti ho incollato quando cancelli un record da parent che è foreign key su child, i record di child corrispondenti vengono cancellati.

[D4rkAng3l]

nell'esempio che ti ho incollato quando cancelli un record da parent che è foreign key su child, i record di child corrispondenti vengono cancellati.

ok ti ringrazio...era proprio quello che mi serviva...spero che funzioni anche su MySql...ma credo di si....ora provo...tnx

[D4rkAng3l]

ok ti ringrazio....un'altra cosa simile.
Posso in qualche modo dirgli che non è possibile eliminare una riga di UTENTE se questo ha pubblicato un'inserzione. Cioè io nella dichiarazione della tabella INSERZIONE ho questa riga:
Id_Venditore varchar(20) not null references UTENTE(User_Id),

E' possibile fare in modo che non possa eliminare un record di utente se vi è un Id_Venditore in INSERZIONE uguale al suo User_Id in UTENTE?

Grazie
Andrea

[gugoXX]

Ciao. Lo fa gia' il motore di Database in automatico, con il vincolo di intergrita' referenziale.
Ovviamente quel vincolo non devi metterlo "DELETE on CASCADE", altrimenti oltre a cancellare l'utente cancellerebbe anche tutte le sue inserzioni.

[D4rkAng3l]

Ciao. Lo fa gia' il motore di Database in automatico, con il vincolo di intergrita' referenziale.
Ovviamente quel vincolo non devi metterlo "DELETE on CASCADE", altrimenti oltre a cancellare l'utente cancellerebbe anche tutte le sue inserzioni.

cioè ti riferisci allamia seconda domanda e non alla prima, giusto?

AMMINISTRA l'ho modificata così:



create table AMMINISTRA (
Admin varchar(20) not null,
Id_Categoria int not null,
Inizio_Amministrazione datetime,
FOREIGN KEY (Admin) REFERENCES AMMINISTRATORE(Username)
ON DELETE CASCADE,
foreign key (Id_Categoria) references CATEGORIA(Id_Categoria),
primary key (Admin, Id_Categoria)
) type=InnoDB;


Credo vada bene...

Tnx

[gugoXX]

Si', scusa, mi riferisco a questa

ok ti ringrazio....un'altra cosa simile.
Posso in qualche modo dirgli che non è possibile eliminare una riga di UTENTE se questo ha pubblicato un'inserzione. Cioè io nella dichiarazione della tabella INSERZIONE ho questa riga:
Id_Venditore varchar(20) not null references UTENTE(User_Id),

E' possibile fare in modo che non possa eliminare un record di utente se vi è un Id_Venditore in INSERZIONE uguale al suo User_Id in UTENTE?

Grazie
Andrea

Ciao. Lo fa gia' il motore di Database in automatico, con il vincolo di intergrita' referenziale.
Ovviamente quel vincolo non devi metterlo "DELETE on CASCADE", altrimenti oltre a cancellare l'utente cancellerebbe anche tutte le sue inserzioni.

[D4rkAng3l]

ok perfetto...però vediamo se ho capito come funziona sto delete on cascade.

Metti caso che viene pubblicata un'inserzione fraudolenta e che un'amministratore la cancella manualmente in un modo che ora come ora chissene frega. Vorrei che quando un'inserzione venisse cancellata allora venissero cancellate anche le foto di quell'inserzione. Per fare questo modifico la tabella FOTO in questo modo:


create table FOTO
(
Id_Foto int not null auto_increment primary key,
Titolo varchar(50) not null,
Descrizione varchar(255) not null,
Link_File varchar(255) not null,
Id_Oggetto int not null,
foreign key(Id_Oggetto) references INSERZIONE(Id_Oggetto) ON DELETE CASCADE
) type=InnoDB;


Va bene?

Grazie
Andrea

[gugoXX]

Si', va bene.

[D4rkAng3l]

e stesso discorso per COMMENTO che se un utente viene cancellato allora anche i suoi commenti vengono eliminati


create table COMMENTO
(
Id_Commento int not null primary key,
Commentatore varchar(20) not null,
Oggetto_Commentato int not null,
Data datetime not null,
Commento text not null,
foreign key (Commentatore) references UTENTE(User_Id) ON DELETE CASCADE,
foreign key (Oggetto_Commentato) references INSERZIONE(Id_Oggetto)
) type=InnoDB;


Madò stò indietrissimo....per stasera devo almeno fare tutti gli inserimenti di dati fittizzi e domani chiudere con una ventina di query...secondo te è fattibile?

[gugoXX]

e stesso discorso per COMMENTO che se un utente viene cancellato allora anche i suoi commenti vengono eliminati


create table COMMENTO
(
Id_Commento int not null primary key,
Commentatore varchar(20) not null,
Oggetto_Commentato int not null,
Data datetime not null,
Commento text not null,
foreign key (Commentatore) references UTENTE(User_Id) ON DELETE CASCADE,
foreign key (Oggetto_Commentato) references INSERZIONE(Id_Oggetto)
) type=InnoDB;


Devi mettere on cascade anche la foreign key verso inserzione, altrimenti quanto cancelli un utente, a seconda che il motore tenti di cancellare prima l'inserzione oppure prima il commento (che e' impredicibile, per questo le oncascade non vengono usate a livello di prodotto serio), la cancellazione potrebbe fallire.


Madò stò indietrissimo....per stasera devo almeno fare tutti gli inserimenti di dati fittizzi e domani chiudere con una ventina di query...secondo te è fattibile?

Massi', un po' di dati inseriti e qualche query e ce la fai.
La ventina di query cos'e'? Sono domande esplicite che la professoressa ha richiesto oppure ha solo detto "Inventatevi 20 query"...
Comunque se non devi fare anche parti di UI ce la fai in una nottata.
Ti serve solo un bel thermos di buon caffe'.

[D4rkAng3l]

Devi mettere on cascade anche la foreign key verso inserzione, altrimenti quanto cancelli un utente, a seconda che il motore tenti di cancellare prima l'inserzione oppure prima il commento (che e' impredicibile, per questo le oncascade non vengono usate a livello di prodotto serio), la cancellazione potrebbe fallire.



Massi', un po' di dati inseriti e qualche query e ce la fai.
La ventina di query cos'e'? Sono domande esplicite che la professoressa ha richiesto oppure ha solo detto "Inventatevi 20 query"...
Comunque se non devi fare anche parti di UI ce la fai in una nottata.
Ti serve solo un bel thermos di buon caffe'.

che intendi con UI?
Il progetto è personale..le query le devo creare io...dice solo 20 quey significative più 2 query in algebra relazionale ed altre 2 in calcolo relazionale...ufff

[D4rkAng3l]

oddio che intendi con la foreign key verso inserzione?

Grazie
Andrea

[gugoXX]

oddio che intendi con la foreign key verso inserzione?

Grazie
Andrea


Questa:
foreign key (Oggetto_Commentato) references INSERZIONE(Id_Oggetto)

Anche questa deve essere ON CASCADE, se vuoi che, cancellando un inserzione, possa essere cancellato anche ogni suo riferimento, compresi i commenti.