CREATE.sql 7.88 KB
Newer Older
Dimitri Nicolas's avatar
Dimitri Nicolas committed
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS Utilisateur(
    login VARCHAR(64) NOT NULL,
    motDePasse VARCHAR(128) NOT NULL,
    nom VARCHAR(32) NOT NULL,
    prenom VARCHAR(32) NOT NULL,
    adresse VARCHAR(128) NOT NULL,
    mail VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Utilisateur PRIMARY KEY (login)
);
CREATE TABLE IF NOT EXISTS Personnel(
    login VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Personnel PRIMARY KEY (login),
    CONSTRAINT fk_Personnel_Utilisateur FOREIGN KEY (login) REFERENCES Utilisateur(login)
14
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLE IF NOT EXISTS Adherent(
    login VARCHAR(64) NOT NULL,
    numCarte VARCHAR(64) NOT NULL,
    dateNaissance DATE NOT NULL,
    numTel VARCHAR(32) NOT NULL,
    dateAdhesion DATE NOT NULL,
    blacklisted BOOLEAN DEFAULT FALSE,
    CONSTRAINT pk_Adherent PRIMARY KEY (login),
    CONSTRAINT fk_Adherent_Utilisateur FOREIGN KEY (login) REFERENCES Utilisateur(login),
    CONSTRAINT uq_NumCarte UNIQUE(numCarte)
    /*CONSTRAINT chk_DateNaissance */
    /*CONSTRAINT chk_dateAdhesion  */
);
CREATE TABLE IF NOT EXISTS Sanction(
    sanctionID SERIAL NOT NULL,
    enCours BOOLEAN DEFAULT TRUE,
    dateSanction DATE NOT NULL,
    adherent VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Sanction PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Sanction_Adherent FOREIGN KEY(adherent) REFERENCES Adherent(login)
    /* CONSTRAINT chk_DateSanction */
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
37
CREATE TABLE IF NOT EXISTS Ressource (
38
39
40
41
42
43
44
    codeUnique VARCHAR PRIMARY KEY, 
    titre VARCHAR NOT NULL,
    dateApparition DATE NOT NULL,
    editeur VARCHAR NOT NULL, 
    genre VARCHAR NOT NULL,
    codeClassification VARCHAR NOT NULL
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
45
46
47
48
49
50
51
CREATE TABLE IF NOT EXISTS Suspension(
    sanctionID INT NOT NULL,
    duree INT,
    CONSTRAINT pk_Suspension PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Suspension_Sanction FOREIGN KEY (sanctionID) REFERENCES Sanction(sanctionID),
    CONSTRAINT chk_Duree CHECK(duree >= 0)
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
52
CREATE TABLE IF NOT EXISTS Exemplaire (
Dimitri Nicolas's avatar
Dimitri Nicolas committed
53
    ref VARCHAR,
54
55
56
57
    etat VARCHAR NOT NULL,
    CHECK (etat = 'Neuf' OR etat = 'Abimé' or etat = 'Bon' or etat = 'Perdu'),
    disponible BOOLEAN NOT NULL,
    ressource VARCHAR NOT NULL,
Dimitri Nicolas's avatar
Dimitri Nicolas committed
58
59
    CONSTRAINT pk_Exemplaire PRIMARY KEY(ref, ressource),
    CONSTRAINT uq_ref UNIQUE(ref),
60
61
    FOREIGN KEY (ressource) REFERENCES Ressource(codeUnique)
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
CREATE TABLE IF NOT EXISTS Remboursement(
    sanctionID INT NOT NULL,
    dateEcheance DATE,
    montant FLOAT NOT NULL,
    refExemplaire VARCHAR NOT NULL,
    CONSTRAINT pk_Remboursement PRIMARY KEY(sanctionID),
    CONSTRAINT fk_Remboursement_Sanction FOREIGN KEY(sanctionID) REFERENCES Sanction(sanctionID),
    CONSTRAINT fk_Remboursement_Exemplaire FOREIGN KEY(refExemplaire) REFERENCES Exemplaire(ref),
    CONSTRAINT chk_montant CHECK(montant >= 0.0)
    /* CONSTRAINT DateEcheance */
);
CREATE TABLE IF NOT EXISTS Pret(
    pretID SERIAL NOT NULL,
    date DATE NOT NULL,
    duree INT NOT NULL,
    adherent VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Pret PRIMARY KEY(pretID),
    CONSTRAINT fk_Pret_Adherent FOREIGN KEY(adherent) REFERENCES Adherent(login),
    CONSTRAINT chk_duree CHECK(duree >= 0)
    /* CONSTRAINT CHECK DATE */
Marine Marsal's avatar
Marine Marsal committed
82
83
);

84

Marine Marsal's avatar
Marine Marsal committed
85

Dimitri Nicolas's avatar
Dimitri Nicolas committed
86
CREATE TABLE IF NOT EXISTS Livre (
Marine Marsal's avatar
Marine Marsal committed
87
88
89
90
91
92
93
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    ISBN VARCHAR NOT NULL,
    resume VARCHAR NOT NULL, 
    langue VARCHAR NOT NULL
); 

Dimitri Nicolas's avatar
Dimitri Nicolas committed
94
CREATE TABLE IF NOT EXISTS OeuvreMusicale (
Marine Marsal's avatar
Marine Marsal committed
95
96
97
98
99
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    longueur INTEGER NOT NULL
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
100
CREATE TABLE IF NOT EXISTS Film (
Marine Marsal's avatar
Marine Marsal committed
101
102
103
104
105
106
    codeUnique VARCHAR PRIMARY KEY, 
    FOREIGN KEY (codeUnique) REFERENCES Ressource(codeUnique),
    longueur INTEGER NOT NULL,
    synopsis VARCHAR NOT NULL
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
107
CREATE TABLE IF NOT EXISTS Contributeur (
Marine Marsal's avatar
Marine Marsal committed
108
109
110
111
112
113
114
    contributeurId INTEGER PRIMARY KEY,
    nom VARCHAR NOT NULL,
    prenom VARCHAR NOT NULL,
    dateNaissance DATE NOT NULL,
    nationalite VARCHAR NOT NULL
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
115
CREATE TABLE IF NOT EXISTS Auteur (
Marine Marsal's avatar
Marine Marsal committed
116
117
118
119
120
121
122
    livre VARCHAR NOT NULL,
    auteur INTEGER NOT NULL,
    FOREIGN KEY (auteur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (livre) REFERENCES Livre(codeUnique),
    PRIMARY KEY (livre, auteur)
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
123
CREATE TABLE IF NOT EXISTS Interprete (
Marine Marsal's avatar
Marine Marsal committed
124
125
126
127
128
129
130
    oeuvreMusicale VARCHAR NOT NULL,
    interprete INTEGER NOT NULL,
    FOREIGN KEY (interprete) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (oeuvreMusicale) REFERENCES OeuvreMusicale(codeUnique),
    PRIMARY KEY (oeuvreMusicale, interprete)
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
131
CREATE TABLE IF NOT EXISTS Compositeur (
Marine Marsal's avatar
Marine Marsal committed
132
133
134
135
136
137
138
    oeuvreMusicale VARCHAR NOT NULL,
    compositeur INTEGER NOT NULL,
    FOREIGN KEY (compositeur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (oeuvreMusicale) REFERENCES OeuvreMusicale(codeUnique),
    PRIMARY KEY (oeuvreMusicale, compositeur)
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
139
CREATE TABLE IF NOT EXISTS Acteur (
Marine Marsal's avatar
Marine Marsal committed
140
141
142
143
144
145
146
    film VARCHAR NOT NULL,
    acteur INTEGER NOT NULL,
    FOREIGN KEY (acteur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY (film) REFERENCES Film(codeUnique),
    PRIMARY KEY (film, acteur)
);

Dimitri Nicolas's avatar
Dimitri Nicolas committed
147
CREATE TABLE IF NOT EXISTS Realisateur (
Marine Marsal's avatar
Marine Marsal committed
148
149
150
151
152
153
154
    film VARCHAR NOT NULL,
    realisateur INTEGER NOT NULL,
    FOREIGN KEY(realisateur) REFERENCES Contributeur(contributeurId),
    FOREIGN KEY(film) REFERENCES Film(codeUnique),
    PRIMARY KEY (film, realisateur)
);

155

Marine Marsal's avatar
Marine Marsal committed
156

Dimitri Nicolas's avatar
Dimitri Nicolas committed
157
CREATE TABLE IF NOT EXISTS ExemplairePret (
Marine Marsal's avatar
Marine Marsal committed
158
159
160
161
162
163
    ref VARCHAR NOT NULL,
    pret INTEGER NOT NULL,
    FOREIGN KEY (ref) REFERENCES Exemplaire(ref),
    FOREIGN KEY (pret) REFERENCES Pret(pretID),
    PRIMARY KEY (ref, pret)
);
Dimitri Nicolas's avatar
Dimitri Nicolas committed
164
165


Marine Marsal's avatar
Marine Marsal committed
166
167
 /* ****** TRIGGERS ****** */

Dimitri Nicolas's avatar
Dimitri Nicolas committed
168
169
170
171

CREATE OR REPLACE FUNCTION check_exclusivite_realisateur()
RETURNS trigger AS
$$
Marine Marsal's avatar
Marine Marsal committed
172
173
DECLARE 
    var INTEGER;
Dimitri Nicolas's avatar
Dimitri Nicolas committed
174
BEGIN
Marine Marsal's avatar
Marine Marsal committed
175
176
    SELECT * INTO var FROM Acteur a WHERE a.acteur = NEW.realisateur AND film = NEW.film;
    RAISE EXCEPTION 'Erreur_exclu';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
177
    EXCEPTION 
Marine Marsal's avatar
Marine Marsal committed
178
179
180
181
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Auteur';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
182
183
184
185
186
187
188
189
190
191
192
193
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_REALISATEUR BEFORE INSERT ON Realisateur 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_realisateur();

CREATE OR REPLACE FUNCTION check_exclusivite_acteur()
RETURNS trigger AS
$$
Marine Marsal's avatar
Marine Marsal committed
194
195
DECLARE 
    var INTEGER;
Dimitri Nicolas's avatar
Dimitri Nicolas committed
196
197
BEGIN 
    SELECT realisateur INTO var FROM Realisateur WHERE realisateur = NEW.auteur AND film = NEW.film;
Marine Marsal's avatar
Marine Marsal committed
198
    RAISE EXCEPTION 'Erreur_exclu';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
199
    EXCEPTION 
Marine Marsal's avatar
Marine Marsal committed
200
201
202
203
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Realisateur';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
204
205
206
207
208
209
210
211
212
213
214
215
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_ACTEUR BEFORE INSERT OR UPDATE ON Acteur
FOR EACH ROW
EXECUTE PROCEDURE check_exclusivite_acteur();

CREATE OR REPLACE FUNCTION check_exclusivite_compositeur()
RETURNS trigger AS
$$
Marine Marsal's avatar
Marine Marsal committed
216
217
DECLARE 
    var INTEGER;
Dimitri Nicolas's avatar
Dimitri Nicolas committed
218
219
BEGIN
    SELECT interprete INTO var FROM Interprete WHERE interprete = NEW.compositeur AND oeuvreMusicale = NEW.oeuvreMusicale;
Marine Marsal's avatar
Marine Marsal committed
220
    RAISE EXCEPTION 'Erreur_exclu';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
221
    EXCEPTION 
Marine Marsal's avatar
Marine Marsal committed
222
223
224
225
        WHEN NO_DATA_FOUND THEN 
            NULL;
        WHEN OTHERS THEN 
            RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Interprete';
Dimitri Nicolas's avatar
Dimitri Nicolas committed
226
227
228
229
230
231
232
233
234
235
236
237
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_COMPOSITEUR BEFORE INSERT OR UPDATE ON Compositeur 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_compositeur();

CREATE OR REPLACE FUNCTION check_exclusivite_interprete()
RETURNS trigger AS
$$
Marine Marsal's avatar
Marine Marsal committed
238
239
DECLARE 
    var INTEGER;
Dimitri Nicolas's avatar
Dimitri Nicolas committed
240
241
BEGIN
    SELECT compositeur INTO var FROM Compositeur WHERE compositeur = NEW.interprete AND oeuvreMusicale = NEW.oeuvreMusicale;
Marine Marsal's avatar
Marine Marsal committed
242
243
244
245
246
247
248
    RAISE EXCEPTION 'Erreur_exclu';
        EXCEPTION 
            WHEN NO_DATA_FOUND THEN 
                NULL;
            WHEN OTHERS THEN 
                RAISE EXCEPTION 'Erreur : ce contributeur existe déjà dans la table Compositeur';
    RETURN NEW;
Dimitri Nicolas's avatar
Dimitri Nicolas committed
249
250
251
252
253
254
255
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER T_EXCUSIVITE_INTERPRETE BEFORE INSERT OR UPDATE ON Interprete 
FOR EACH ROW 
EXECUTE PROCEDURE check_exclusivite_interprete();