153 lines
5.7 KiB
Plaintext
Executable File
153 lines
5.7 KiB
Plaintext
Executable File
--Suppression
|
|
DROP TABLE FERME CASCADE CONSTRAINTS;
|
|
DROP TABLE VOLAILLE CASCADE CONSTRAINTS;
|
|
DROP TABLE ARTICLE CASCADE CONSTRAINTS;
|
|
DROP TABLE MARCHE_VEND CASCADE CONSTRAINTS;
|
|
DROP TABLE ENTREPOSE CASCADE CONSTRAINTS;
|
|
DROP TABLE A_VENDU CASCADE CONSTRAINTS;
|
|
|
|
--Table ferme
|
|
CREATE TABLE FERME
|
|
(
|
|
ID_FERMIER VARCHAR2(15) NOT NULL,
|
|
NOM VARCHAR2(50) NOT NULL,
|
|
MDP VARCHAR2(15) NOT NULL,
|
|
MAIL VARCHAR2(50) NOT NULL,
|
|
DATE_ENREGISTREMENT DATE NOT NULL,
|
|
DATE_CONNECTION DATE NOT NULL,
|
|
ECUS INTEGER DEFAULT 500 CHECK( ECUS >= 0),
|
|
SCORE INTEGER DEFAULT 0 CHECK( SCORE >= 0),
|
|
CLASSEMENT INTEGER NULL,
|
|
HIBERNATION CHAR(1) DEFAULT 'F' CHECK (HIBERNATION IN ('T','F')),
|
|
NB_ACHATS_JOUR INTEGER DEFAULT 0 CHECK (NB_ACHATS_JOUR BETWEEN 0 AND 12),
|
|
NB_MALE INTEGER DEFAULT 0 CHECK ( NB_MALE >= 0),
|
|
NB_FEMELLE INTEGER DEFAULT 0 CHECK ( NB_FEMELLE >= 0),
|
|
NB_GROS INTEGER DEFAULT 0 CHECK ( NB_GROS >= 0),
|
|
NB_MOYEN INTEGER DEFAULT 0 CHECK ( NB_MOYEN >= 0),
|
|
NB_PETIT INTEGER DEFAULT 8 CHECK ( NB_PETIT >= 0),
|
|
ABREUVAGE_L CHAR(1) DEFAULT 'F' CHECK (ABREUVAGE_L IN ('T','F')),
|
|
NB_ABREUVAGE_L INTEGER CHECK (NB_ABREUVAGE_L BETWEEN 0 AND 4),
|
|
NOURRI_L CHAR(1) DEFAULT 'F' CHECK (NOURRI_L IN ('T','F')),
|
|
NB_JEUNE_L INTEGER CHECK (NB_JEUNE_L BETWEEN 0 AND 4),
|
|
SALE_L CHAR(1) DEFAULT 'F' CHECK (SALE_L IN ('T','F')),
|
|
NB_MALADE_L INTEGER CHECK(NB_MALADE_L BETWEEN 0 AND 4),
|
|
ABREUVAGE_J CHAR(1) DEFAULT 'F' CHECK (ABREUVAGE_J IN ('T','F')),
|
|
NB_ABREUVAGE_J INTEGER CHECK (NB_ABREUVAGE_J BETWEEN 0 AND 4),
|
|
NOURRI_J CHAR(1) DEFAULT 'F' CHECK (NOURRI_J IN ('T','F')),
|
|
NB_JEUNE_J INTEGER CHECK(NB_JEUNE_J BETWEEN 0 AND 4),
|
|
SALE_J CHAR(1) DEFAULT 'F' CHECK ( SALE_J IN ('T','F')),
|
|
NB_MALADE_J INTEGER CHECK(NB_MALADE_J BETWEEN 0 AND 4),
|
|
ABREUVAGE_VACHE CHAR(1) DEFAULT 'F' CHECK (ABREUVAGE_VACHE IN ('T','F')),
|
|
NB_ABREUVAGE_VACHE INTEGER CHECK(NB_ABREUVAGE_VACHE BETWEEN 0 AND 4),
|
|
NOURRI_VACHE CHAR(1) DEFAULT 'F' CHECK (NOURRI_VACHE IN ('T','F')),
|
|
NB_JEUNE_VACHE INTEGER CHECK(NB_JEUNE_VACHE BETWEEN 0 AND 4),
|
|
SALE_VACHE CHAR(1) DEFAULT 'F' CHECK (SALE_VACHE IN ('T','F')),
|
|
NB_MALADE_VACHE INTEGER CHECK (NB_MALADE_VACHE BETWEEN 0 AND 4),
|
|
POIDS_VACHE INTEGER DEFAULT 0 CHECK ( POIDS_VACHE BETWEEN 0 AND 750),
|
|
AGE_VACHE INTEGER DEFAULT 0,
|
|
NB_LAIT INTEGER DEFAULT 0 CHECK ( NB_LAIT BETWEEN 0 AND 16),
|
|
CONSTRAINT PK_FERMIER PRIMARY KEY (ID_FERMIER)
|
|
);
|
|
|
|
--Table volaille
|
|
CREATE TABLE VOLAILLE
|
|
(
|
|
ID_VOLAILLE INTEGER NOT NULL,
|
|
ID_FERMIER VARCHAR2(15) NOT NULL,
|
|
ABREUVAGE_V CHAR(1) DEFAULT 'F' CHECK (ABREUVAGE_V IN ('T','F')),
|
|
NB_ABREUVAGE_V INTEGER CHECK (NB_ABREUVAGE_V BETWEEN 0 AND 4),
|
|
NOURRI_V CHAR(1) DEFAULT 'F' CHECK (NOURRI_V IN ('T','F')),
|
|
NB_JEUNE_V INTEGER CHECK (NB_JEUNE_V BETWEEN 0 AND 4),
|
|
SALE_V CHAR(1) DEFAULT 'F' CHECK (SALE_V IN ('T','F')),
|
|
NB_MALADE_V INTEGER CHECK (NB_MALADE_V BETWEEN 0 AND 4),
|
|
POIDS_V FLOAT DEFAULT 0.05 CHECK (POIDS_V BETWEEN 0 AND 3.5),
|
|
AGE_V INTEGER DEFAULT 0 CHECK (AGE_V >= 0),
|
|
PRODUCTION_V INTEGER DEFAULT 0,
|
|
CONSTRAINT PK_VOLAILLES PRIMARY KEY (ID_VOLAILLE)
|
|
);
|
|
|
|
--Table article
|
|
CREATE TABLE ARTICLE
|
|
(
|
|
ID_ARTICLE INTEGER NOT NULL,
|
|
NOM VARCHAR2(25) NOT NULL,
|
|
DESCRIPTION VARCHAR2(500) NOT NULL,
|
|
COLLECTIONNABLE CHAR(1) DEFAULT 'F' CHECK (COLLECTIONNABLE IN ('T','F')),
|
|
QUANTITE INTEGER DEFAULT 0 CHECK(QUANTITE >= 0),
|
|
PRIX_VENTE INTEGER DEFAULT 0 CHECK(PRIX_VENTE >= 0),
|
|
PRIX_ACHAT INTEGER DEFAULT 0 CHECK(PRIX_ACHAT >= 0),
|
|
CONSTRAINT PK_ARTICLE PRIMARY KEY (ID_ARTICLE)
|
|
);
|
|
|
|
--Table marche_vend
|
|
CREATE TABLE MARCHE_VEND
|
|
(
|
|
ID_FERMIER VARCHAR2(15) NOT NULL,
|
|
ID_ARTICLE INTEGER NOT NULL,
|
|
NB_VENTE INTEGER DEFAULT 0 CHECK (NB_VENTE >= 0),
|
|
PRIX INTEGER DEFAULT 0 CHECK (PRIX >= 0),
|
|
CONSTRAINT PK_MARCHE_VEND PRIMARY KEY (ID_FERMIER, ID_ARTICLE)
|
|
);
|
|
|
|
--Table entrepose
|
|
CREATE TABLE ENTREPOSE
|
|
(
|
|
ID_FERMIER VARCHAR2(15) NOT NULL,
|
|
ID_ARTICLE INTEGER NOT NULL,
|
|
QUANTITE INTEGER DEFAULT 0 CHECK (QUANTITE >= 0),
|
|
COLLECTION CHAR(1) DEFAULT 'F' CHECK (COLLECTION IN ('T','F')),
|
|
CONSTRAINT PK_ENTREPOSE PRIMARY KEY (ID_FERMIER, ID_ARTICLE)
|
|
);
|
|
|
|
--Table a_vendu
|
|
CREATE TABLE A_VENDU
|
|
(
|
|
ID_FERMIER VARCHAR2(15) NOT NULL,
|
|
ID_ARTICLE INTEGER NOT NULL,
|
|
NB_VENDU INTEGER DEFAULT 0 CHECK (NB_VENDU >= 0),
|
|
DATE_VENTE DATE NULL,
|
|
CONSTRAINT PK_A_VENDU PRIMARY KEY (ID_FERMIER, ID_ARTICLE)
|
|
);
|
|
|
|
--Alter table pour les cles etrangeres
|
|
ALTER TABLE VOLAILLE ADD (
|
|
CONSTRAINT FK_VOLAILLE_FERMIER
|
|
FOREIGN KEY (ID_FERMIER)
|
|
REFERENCES FERME (ID_FERMIER)) ;
|
|
|
|
ALTER TABLE MARCHE_VEND ADD (
|
|
CONSTRAINT FK_MARCHE_VEND_FERMIER
|
|
FOREIGN KEY (ID_FERMIER)
|
|
REFERENCES FERME (ID_FERMIER)) ;
|
|
|
|
ALTER TABLE MARCHE_VEND ADD (
|
|
CONSTRAINT FK_MARCHE_VEND_ARTICLE
|
|
FOREIGN KEY (ID_ARTICLE)
|
|
REFERENCES ARTICLE (ID_ARTICLE)) ;
|
|
|
|
ALTER TABLE ENTREPOSE ADD (
|
|
CONSTRAINT FK_ENTREPOSE_FERMIER
|
|
FOREIGN KEY (ID_FERMIER)
|
|
REFERENCES FERME (ID_FERMIER)) ;
|
|
|
|
ALTER TABLE ENTREPOSE ADD (
|
|
CONSTRAINT FK_ENTREPOSE_ARTICLE
|
|
FOREIGN KEY (ID_ARTICLE)
|
|
REFERENCES ARTICLE (ID_ARTICLE)) ;
|
|
|
|
ALTER TABLE A_VENDU ADD (
|
|
CONSTRAINT FK_A_VENDU_FERMIER
|
|
FOREIGN KEY (ID_FERMIER)
|
|
REFERENCES FERME (ID_FERMIER)) ;
|
|
|
|
ALTER TABLE A_VENDU ADD (
|
|
CONSTRAINT FK_A_VENDU_ARTICLE
|
|
FOREIGN KEY (ID_ARTICLE)
|
|
REFERENCES ARTICLE (ID_ARTICLE)) ;
|
|
|
|
|
|
-- View : coop_stock
|
|
-- View : Clapier
|
|
-- View : Paturage
|
|
|