1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| create table Livre (
numero_b char not null,
title char not null,
price char not null CHECK (price > 0),
type ENUM ( 'dict', 'poe', 'novel', 'tb' ) not null,
year char not null DEFAULT '(SELECT add_months(SYSDATE, 12) FROM DUAL;)',
Editor char not null,
constraint ID_Livre primary key (numero_b));
create table Abonne (
numero_abo char not null,
name char not null,
surname char not null,
status char ,
street char not null,
town char not null,
constraint ID_abo primary key (numero_abo));
create table Auteur (
numero char not null,
writer char,
CONSTRAINT book FOREIGN KEY ( numero ) REFERENCES Livre ( numero_b),
constraint ID_writer primary key (numero, writer));
create table Emprunt (
numero_ex char not null,
numero_book char not null,
numero_abo char,
return_date char DEFAULT ( CASE Abonne.statut
WHEN 'VIP' THEN ( UPDATE Emprunt SET return_date=(SELECT add_months(SYSDATE, 24) FROM DUAL ;) WHERE Abonne.Numero=Emprunt.Numeroabo )
WHEN 'TEACHER' THEN ( UPDATE Emprunt SET return_date=(SELECT add_months(SYSDATE, 12) FROM DUAL ;) WHERE Abonne.Numero=Emprunt.Numeroabo )
WHEN 'STUDENT' THEN ( UPDATE Emprunt SET return_date=(SELECT add_months(SYSDATE, 6) FROM DUAL ;) WHERE Abonne.Numero=Emprunt.Numeroabo )
WHEN 'RETURNING' THEN ( UPDATE Emprunt SET return_date=(SELECT add_months(SYSDATE, 3) FROM DUAL ;) WHERE Abonne.Numero=Emprunt.Numeroabo )
Else ( UPDATE Emprunt SET return_date=(SELECT add_months(SYSDATE,1) FROM DUAL ;) WHERE Abonne.Numero=Emprunt.Numeroabo )
END ),
CONSTRAINT ID_emprunt PRIMARY KEY ( numero_ex, numero_b, numero_abo ),
CONSTRAINT exemplaire FOREIGN KEY ( numero_ex ) REFERENCES Exemplaire ( numero_ex);
CONSTRAINT abonne FOREIGN KEY ( numero_abo ) REFERENCES Abonne ( numero_abo);
create table Exemplaire (
numero char not null,
numero_ex char not null,
CONSTRAINT book FOREIGN KEY ( numero ) REFERENCES Livre ( numero_b),
CONSTRAINT ID_Exemplaire PRIMARY KEY (Num_Ex, Numero));
CREATE SEQUENCE seq_book;
CREATE SEQUENCE seq_abo;
CREATE SEQUENCE seq_ex ;
CREATE OR REPLACE TRIGGER trig_book
BEFORE INSERT ON Livre FOR EACH ROW
BEGIN
SELECT seq_book.NEXTVAL
INTO :NEW.numero_b
FROM DUAL;
END;
CREATE OR REPLACE TRIGGER trig_abo
BEFORE INSERT ON Abonne FOR EACH ROW
BEGIN
SELECT seq_abo.NEXTVAL
INTO :NEW.numero_abo
FROM DUAL;
END;
CREATE OR REPLACE TRIGGER trig_ex
BEFORE INSERT ON Exemplaire FOR EACH ROW
BEGIN
SELECT seq_ex.NEXTVAL
INTO :NEW.numero_ex
FROM DUAL;
END; |
