CREATE SCHEMA S_COMMERCIAL AUTHORIZATION U_MONA_LISA
DEFAULT CHARACTER SET LATIN1

CREATE VIEW V_CLIENT_CLI
AS
    SELECT * 
    FROM   T_CLIENT_CLI 
           FULL OUTER JOIN T_PROSPECT_PSP
              ON T_CLIENT_CLI.PRS_ID = T_PROSPECT_PSP.PRS_ID

CREATE TABLE T_CLIENT_CLI 
(PRS_ID INTEGER NOT NULL PRIMARY KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 CLI_NOM VARCHAR(100),
 CLI_SIRET CHAR(14) NOT NULL)

CREATE TABLE T_PROSPECT_PSP
(PRS_ID INTEGER NOT NULL PRIMARY KEY REFERENCES T_PERSONNE_PRS (PRS_ID),
 PSP_NOM CHAR(16),
 PSP_SIRET CHAR(14) NOT NULL)

CREATE TABLE T_PERSONNE_PRS
(PRS_ID INTEGER NOT NULL PRIMARY KEY)

CREATE ASSERTION AK_SIRET
CHECK NOT EXISTS
   (SELECT *
    FROM   T_CLIENT_CLI 
           INNER JOIN T_PROSPECT_PSP
              ON T_CLIENT_CLI.PRS_ID = T_PROSPECT_PSP.PRS_ID
              AND T_CLIENT_CLI.CLI_SIRET_ID <> T_PROSPECT_PSP.PSP_SIRET);
