GuideResto_CREATE_TABLES.sql

DROP SEQUENCE SEQ_RESTAURANTS;
DROP SEQUENCE SEQ_TYPES_GASTRONOMIQUES;
DROP SEQUENCE seq_VILLES;
DROP SEQUENCE SEQ_EVAL;
DROP SEQUENCE SEQ_NOTES;
DROP SEQUENCE SEQ_CRITERES_EVALUATION;

DROP TABLE RESTAURANTS CASCADE CONSTRAINTS;
DROP TABLE TYPES_GASTRONOMIQUES CASCADE CONSTRAINTS;
DROP TABLE VILLES CASCADE CONSTRAINTS;
DROP TABLE COMMENTAIRES CASCADE CONSTRAINTS;
DROP TABLE LIKES CASCADE CONSTRAINTS;
DROP TABLE NOTES CASCADE CONSTRAINTS;
DROP TABLE CRITERES_EVALUATION CASCADE CONSTRAINTS;

CREATE TABLE RESTAURANTS (numero number(10) NOT NULL, nom varchar2(100) NOT NULL, adresse varchar2(100) NOT NULL, description clob, site_web varchar2(100), fk_type number(10) NOT NULL, fk_vill number(10) NOT NULL, PRIMARY KEY (numero));
CREATE TABLE TYPES_GASTRONOMIQUES (numero number(10) NOT NULL, libelle varchar2(100) NOT NULL UNIQUE, description clob NOT NULL, PRIMARY KEY (numero));
CREATE TABLE VILLES (numero number(10) NOT NULL, code_postal varchar2(100) NOT NULL, nom_ville varchar2(100) NOT NULL, PRIMARY KEY (numero));
CREATE TABLE COMMENTAIRES (numero number(10) NOT NULL, date_eval date NOT NULL, commentaire clob NOT NULL, nom_utilisateur varchar2(100) NOT NULL, fk_rest number(10), PRIMARY KEY (numero));
CREATE TABLE LIKES (numero number(10) NOT NULL, appreciation char(1) NOT NULL, date_eval date NOT NULL, adresse_ip varchar2(100) NOT NULL, fk_rest number(10) NOT NULL, PRIMARY KEY (numero));
CREATE TABLE NOTES (numero number(10) NOT NULL, note number(3) NOT NULL, fk_comm number(10) NOT NULL, fk_crit number(10) NOT NULL, PRIMARY KEY (numero));
CREATE TABLE CRITERES_EVALUATION (numero number(10) NOT NULL, nom varchar2(100) NOT NULL UNIQUE, description varchar2(512), PRIMARY KEY (numero));

ALTER TABLE RESTAURANTS ADD CONSTRAINT FK_REST_TYPE FOREIGN KEY (fk_type) REFERENCES TYPES_GASTRONOMIQUES (numero);
ALTER TABLE RESTAURANTS ADD CONSTRAINT FK_REST_VILL FOREIGN KEY (fk_vill) REFERENCES VILLES (numero);
ALTER TABLE COMMENTAIRES ADD CONSTRAINT FK_COMM_REST FOREIGN KEY (fk_rest) REFERENCES RESTAURANTS (numero);
ALTER TABLE NOTES ADD CONSTRAINT FK_NOTE_COMM FOREIGN KEY (fk_comm) REFERENCES COMMENTAIRES (numero);
ALTER TABLE NOTES ADD CONSTRAINT FK_NOTE_CRIT FOREIGN KEY (fk_crit) REFERENCES CRITERES_EVALUATION (numero);
ALTER TABLE LIKES ADD CONSTRAINT FK_LIKE_REST FOREIGN KEY (fk_rest) REFERENCES RESTAURANTS (numero);

CREATE SEQUENCE SEQ_RESTAURANTS;
CREATE SEQUENCE SEQ_TYPES_GASTRONOMIQUES;
CREATE SEQUENCE SEQ_VILLES;
CREATE SEQUENCE SEQ_EVAL;
CREATE SEQUENCE SEQ_NOTES;
CREATE SEQUENCE SEQ_CRITERES_EVALUATION;

CREATE OR REPLACE TRIGGER TR_BIF_RESTAURANTS
BEFORE INSERT ON RESTAURANTS
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_RESTAURANTS.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_TYPES_GASTRONOMIQUES
BEFORE INSERT ON TYPES_GASTRONOMIQUES
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_TYPES_GASTRONOMIQUES.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_VILLES
BEFORE INSERT ON VILLES
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_VILLES.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_COMMENTAIRES
BEFORE INSERT ON COMMENTAIRES
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_EVAL.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_LIKES
BEFORE INSERT ON LIKES
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_EVAL.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_NOTES
BEFORE INSERT ON NOTES
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_NOTES.NEXTVAL;
	END IF;
END;
/

CREATE OR REPLACE TRIGGER TR_BIF_CRITERES_EVALUATION
BEFORE INSERT ON CRITERES_EVALUATION
FOR EACH ROW
BEGIN
	IF :NEW.NUMERO IS NULL THEN
		:NEW.NUMERO := SEQ_CRITERES_EVALUATION.NEXTVAL;
	END IF;
END;
/