BDGN.sql

DROP TABLE IF EXISTS Demande_perso;
DROP TABLE IF EXISTS Trame;
DROP TABLE IF EXISTS Inscrit_event;
DROP TABLE IF EXISTS Persos;
DROP TABLE IF EXISTS Event;
DROP TABLE IF EXISTS Inscrit_site;

CREATE TABLE Inscrit_site
(
	pseudo VARCHAR(20) PRIMARY KEY,
	email VARCHAR(40) NOT NULL,
	mdp VARCHAR(512) NOT NULL
);

CREATE TABLE Inscrit_event
(
	pseudo VARCHAR(20) PRIMARY KEY,
	nom VARCHAR(20) NOT NULL,
	prenom VARCHAR(20) NOT NULL,
	adresse VARCHAR(100) NOT NULL,
	telephone VARCHAR(10) NOT NULL,
	FOREIGN KEY (pseudo) REFERENCES Inscrit_site ON DELETE CASCADE
);

CREATE TABLE Event(
id_event INT PRIMARY KEY NOT NULL,
nom_event VARCHAR(50) NOT NULL,
e_date DATE,
ambiance VARCHAR(20) NOT NULL
);

CREATE TABLE Persos(
nom_perso VARCHAR(50) PRIMARY KEY NOT NULL,
pseudo VARCHAR(20) NOT NULL,
pj_pnj VARCHAR(3) NOT NULL,
id_event INT NOT NULL,
description VARCHAR(500),
FOREIGN KEY (id_event) REFERENCES Event(id_event),
FOREIGN KEY (pseudo) REFERENCES Inscrit_site(pseudo),
CONSTRAINT check_role CHECK (pj_pnj IN ('PJ', 'PNJ'))
);

CREATE TABLE Trame(
id_trame INT PRIMARY KEY NOT NULL,
id_event INT NOT NULL,
FOREIGN KEY (id_event) REFERENCES Event(id_event)
);

CREATE TABLE Demande_perso
(
	pseudo VARCHAR(20) NOT NULL,
	id_event INT NOT NULL,
	description VARCHAR(5000),
	PRIMARY KEY (pseudo, id_event),
	FOREIGN KEY (pseudo) REFERENCES Inscrit_site ON DELETE CASCADE,
	FOREIGN KEY (id_event) REFERENCES Event ON DELETE CASCADE
);

CREATE TABLE forum_sujets (
  id int(6) NOT NULL auto_increment,
  auteur VARCHAR(30) NOT NULL,
  titre text NOT NULL,
  date_derniere_reponse datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id)
);

CREATE TABLE forum_reponses (
  id int(6) NOT NULL auto_increment,
  auteur VARCHAR(30) NOT NULL,
  message text NOT NULL,
  date_reponse datetime NOT NULL default '0000-00-00 00:00:00',
  correspondance_sujet int(6) NOT NULL,
  PRIMARY KEY  (id)
);