-- ==============================================================================
-- Fichier      : description_base_ruche.txt
-- Auteur       : PASQUIER Léo
-- Date         : 13/12/2020
-- Base         : Ruche
-- Rôle         : Décrire la base de données avec ses tables et ses contraintes.
-- Projet       : Ruche
-- ==============================================================================

-- ==============================================================================
-- Nettoyage puis affichage de toutes les contraintes :
-- ==============================================================================

purge recyclebin;

PROMPT Toutes les contraintes de l’utilisateur :
SELECT CONSTRAINT_TYPE AS CTYPE, CONSTRAINT_NAME AS CNAME, TABLE_NAME AS CTABLE
FROM USER_CONSTRAINTS;

PROMPT ====================================================================================================
PROMPT

-- ==============================================================================
-- Configuration de l'affichage :
-- ==============================================================================

set linesize 100

column CTYPE format a6
column CNAME format a20
column CTABLE format a20
column CCOLUMN format a20
column RTABLE format a20
column RCOLUMN format a20
column COND format a50

-- ==============================================================================
-- Description de la table APICULTEUR :
-- ==============================================================================

PROMPT Table APICULTEUR :
DESC APICULTEUR

PROMPT Cles primaires et uniques de la table APICULTEUR :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'APICULTEUR'
AND UC.CONSTRAINT_TYPE IN ('P', 'U');

PROMPT Contraintes de conditions de la table APICULTEUR :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UC.SEARCH_CONDITION AS COND, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'APICULTEUR'
AND UC.CONSTRAINT_TYPE = 'C';

PROMPT ====================================================================================================
PROMPT

-- ==============================================================================
-- Description de la table RUCHER :
-- ==============================================================================

PROMPT Table RUCHER :
DESC RUCHER

PROMPT Cles primaires et uniques de la table RUCHER :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RUCHER'
AND UC.CONSTRAINT_TYPE IN ('P', 'U');

PROMPT Contraintes de conditions de la table RUCHER :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UC.SEARCH_CONDITION AS COND, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RUCHER'
AND UC.CONSTRAINT_TYPE = 'C';

PROMPT ====================================================================================================
PROMPT

-- ==============================================================================
-- Description de la table RUCHE :
-- ==============================================================================

PROMPT Table RUCHE :
DESC RUCHE

PROMPT Cles primaires et uniques de la table RUCHE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RUCHE'
AND UC.CONSTRAINT_TYPE IN ('P', 'U');

PROMPT Cles etrangeres pour la table RUCHE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC1.COLUMN_NAME AS CCOLUMN, UCC2.TABLE_NAME AS RTABLE, UCC2.COLUMN_NAME AS RCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC1, USER_CONS_COLUMNS UCC2
WHERE UPPER(UC.OWNER) = USER
AND UC.TABLE_NAME = 'RUCHE'
AND UC.CONSTRAINT_TYPE = 'R'
AND UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME;

PROMPT Contraintes de conditions de la table RUCHE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UC.SEARCH_CONDITION AS COND, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RUCHE'
AND UC.CONSTRAINT_TYPE = 'C';

PROMPT ====================================================================================================
PROMPT

-- ==============================================================================
-- Description de la table RECOLTE :
-- ==============================================================================

PROMPT Table RECOLTE :
DESC RECOLTE

PROMPT Cles primaires et uniques de la table RECOLTE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RECOLTE'
AND UC.CONSTRAINT_TYPE IN ('P', 'U');

PROMPT Cles etrangeres pour la table RECOLTE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UCC1.COLUMN_NAME AS CCOLUMN, UCC2.TABLE_NAME AS RTABLE, UCC2.COLUMN_NAME AS RCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC1, USER_CONS_COLUMNS UCC2
WHERE UPPER(UC.OWNER) = USER
AND UC.TABLE_NAME = 'RECOLTE'
AND UC.CONSTRAINT_TYPE = 'R'
AND UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME;

PROMPT Contraintes de conditions de la table RECOLTE :
SELECT UC.CONSTRAINT_TYPE AS CTYPE, UC.CONSTRAINT_NAME AS CNAME, UC.SEARCH_CONDITION AS COND, UCC.COLUMN_NAME AS CCOLUMN
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UPPER(UC.OWNER) = USER
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'RECOLTE'
AND UC.CONSTRAINT_TYPE = 'C';

PROMPT ====================================================================================================
PROMPT