jq5XhNPE

1DROP TABLE IF EXISTS Assure ;
2CREATE TABLE Assure
3(
4    noassure INT PRIMARY KEY,
5    nomassure text NOT NULL,
6    adresse text NOT NULL,
7    cp char(5) check (CP ~ '^\d{5}$') NOT NULL,
8    ville text NOT NULL
9
10);
11
12DROP TABLE IF EXISTS Contrat ;
13CREATE TABLE Contrat
14(
15    noPolice int primary key,
16    dateSouscription date NOT NULL,
17    noCarteGrise text NOT NULL,
18    dateMEC date NOT NULL,
19    immatriculation text UNIQUE NOT NULL,
20    puissance int NOT NULL,
21    noassure int references assure(noassure) NOT NULL
22);
23
24DROP TABLE IF EXISTS Expert;
25CREATE TABLE Expert
26(
27    noexpert INT PRIMARY KEY,
28    nom text NOT NULL,
29    telephone text NOT NULL
30);
31
32
33DROP TABLE IF EXISTS Sinistre;
34CREATE TABLE Sinistre
35(
36    noDossier INT PRIMARY KEY,
37    datesurvenance date NOT NULL,
38    circonstances text NOT NULL,
39    lieu text NOT NULL,
40    nature text NOT NULL,
41    blesses bool NOT NULL,
42    datedeclaration date NOT NULL CHECK (datedeclaration >= datesurvenance),
43    dossiercloture bool NOT NULL,
44    nopolice int references contrat(noPolice) NOT NULL,
45    noexpert int references Expert(noexpert) NOT NULL
46);
47
48DROP TABLE IF EXISTS TypeGarantie;
49CREATE TABLE TypeGarantie
50(
51    codegarantie INT PRIMARY KEY,
52    libelle TEXT NOT NULL,
53    franchise numeric(12, 2) CHECK (Franchise >= 0) NOT NULL,
54    plafond numeric(12, 2) CHECK (Plafond >= 0) CHECK (Franchise < Plafond) NOT NULL
55
56);
57
58DROP TABLE IF EXISTS MiseEnJeu;
59CREATE TABLE MiseEnJeu
60(
61    nodossier int references Sinistre(noDossier),
62    codegarantie int references TypeGarantie(codegarantie),
63    declare numeric(12, 2) CHECK (declare >= 0) NOT NULL,
64    rembourse numeric(12, 2) CHECK (rembourse >= 0),
65    PRIMARY KEY (nodossier, codegarantie)
66);
67
68DROP TABLE IF EXISTS Associative;
69CREATE TABLE Associative
70(
71    nopolice int references Contrat(noPolice),
72    codegarantie int references TypeGarantie(codegarantie),
73    PRIMARY KEY (nopolice, codegarantie)
74);
75
76DROP TABLE IF EXISTS HistoriqueBonus;
77CREATE TABLE HistoriqueBonus
78(
79    nopolice int references Contrat(noPolice),
80    datebonus date,
81    bonus numeric(3, 2) NOT NULL,
82    PRIMARY KEY (nopolice, datebonus)
83);