Library-Management-System/sample_data.sql at main · SusanLYC/Library-Management-System · GitHub

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
CREATE TABLE Book(
Call_no CHAR(5),
ISBN CHAR(13),
Title CHAR(2),
Author CHAR(2),
Amount INTEGER,
Location CHAR(5),
PRIMARY KEY(Call_no));


CREATE TABLE Student(
Student_no CHAR(8),
Name CHAR(1),
Gender CHAR(1),
Major CHAR(4),
PRIMARY KEY(Student_no));


CREATE TABLE Reserve(
Student_no CHAR(8),
Call_no CHAR(5),
Request_date DATE,
PRIMARY KEY (Student_no),
FOREIGN KEY(Student_no) REFERENCES Student ON DELETE CASCADE,
FOREIGN KEY(Call_no) REFERENCES Book ON DELETE CASCADE);


CREATE TABLE Borrow_record(
Student_no CHAR(8),
Call_no CHAR(5),
Borrow_date DATE,
Due_date DATE,
Renewed_bit NUMBER(1),
PRIMARY KEY(Student_no, Call_no),
FOREIGN KEY(Student_no) REFERENCES Student ON DELETE CASCADE,
FOREIGN KEY(Call_no) REFERENCES Book ON DELETE CASCADE);


INSERT INTO STUDENT VALUES('12345678', 'A', 'M', 'Comp');
INSERT INTO STUDENT VALUES('11111111', 'B', 'M', 'Math');
INSERT INTO STUDENT VALUES('22222222', 'C', 'F', 'COMM');
INSERT INTO STUDENT VALUES('33333333', 'D', 'F', 'COMM');
INSERT INTO STUDENT VALUES('44444444', 'E', 'M', 'Comp');
INSERT INTO STUDENT VALUES('55555555', 'F', 'M', 'COMM');
INSERT INTO STUDENT VALUES('66666666', 'G', 'F', 'Comp');
INSERT INTO STUDENT VALUES('77777777', 'H', 'M', 'Math');
Commit;


INSERT INTO BOOK VALUES('A0000','0-306-40615-1','AA','XX',0,'S1E01');
INSERT INTO BOOK VALUES('B0000','0-306-40615-2','BB','YY',0,'S2E02');
INSERT INTO BOOK VALUES('C1111','0-306-40615-3','CC','ZZ',2,'D1E11');
INSERT INTO BOOK VALUES('B0001','0-306-40615-4','DD','UU',2,'G1E00');
INSERT INTO BOOK VALUES('A1111','0-306-40615-5','EE','VV',2,'B1E00');
INSERT INTO BOOK VALUES('D0101','0-306-40615-6','FF','WW',1,'B2E11');
INSERT INTO BOOK VALUES('E0000','0-306-40615-7','GG','PP',0,'X0E22');
INSERT INTO BOOK VALUES('E0100','0-306-40615-8','HH','QQ',2,'X0E21');
INSERT INTO BOOK VALUES('E0111','0-306-40615-9','II','RR',0,'X0E44');
Commit;


INSERT INTO Borrow_record VALUES('11111111','D0101','24-MAR-22','21-APR-22',0);
INSERT INTO Borrow_record VALUES('55555555','A1111','23-MAR-22','20-APR-22',0);
INSERT INTO Borrow_record VALUES('22222222','B0000','31-MAR-22','12-may-22',1);
INSERT INTO Borrow_record VALUES('11111111','A0000','1-Apr-22','29-APR-22',0);
INSERT INTO Borrow_record VALUES('33333333','A0000','3-Apr-22','1-May-22',0);
INSERT INTO Borrow_record VALUES('11111111','B0000','3-Apr-22','15-May-2022',1);
INSERT INTO Borrow_record VALUES('44444444','C1111','4-Apr-22','16-May-22',1);
INSERT INTO Borrow_record VALUES('44444444','A0000','6-Apr-22','4-May-22',0);
INSERT INTO Borrow_record VALUES('33333333','C1111','6-Apr-22','4-May-22',0);
INSERT INTO Borrow_record VALUES('33333333','A1111','6-Apr-22','4-May-22',0);
INSERT INTO Borrow_record VALUES('33333333','B0001','6-Apr-22','4-May-22',0);
INSERT INTO Borrow_record VALUES('44444444','D0101','10-Apr-22','8-May-22',0);
INSERT INTO Borrow_record VALUES('33333333','D0101','10-Apr-22','8-May-22',0);
INSERT INTO Borrow_record VALUES('44444444','A1111','14-Apr-22','12-May-22',0);
INSERT INTO Borrow_record VALUES('55555555','C1111','18-Apr-22','16-May-22',0);
INSERT INTO Borrow_record VALUES('22222222','E0111','19-Apr-22','17-May-22',0);
INSERT INTO Borrow_record VALUES('11111111','E0000','20-Apr-22','18-May-22',0);
INSERT INTO Borrow_record VALUES('44444444','B0001','21-Apr-22','19-May-22',0);
Commit;


INSERT INTO Reserve VALUES('12345678','A0000','20-Apr-22');
INSERT INTO Reserve VALUES('66666666','E0000','22-Apr-22');
COMMIT;


SET AUTOCOMMIT ON


CREATE OR REPLACE TRIGGER INCREASE_COPIES
AFTER DELETE ON Borrow_record
FOR EACH ROW
DECLARE
      cnt INTEGER;
BEGIN
SELECT Amount INTO cnt
FROM Book
WHERE Call_no =:old.Call_no;
cnt:=cnt+1;
UPDATE Book
SET amount = cnt
WHERE Call_no =:old.Call_no;
END;
/


CREATE OR REPLACE TRIGGER DECREASE_COPIES
AFTER INSERT ON Borrow_record
FOR EACH ROW
DECLARE
cnt INTEGER;
BEGIN
SELECT Amount INTO cnt
FROM Book
WHERE Call_no =:new.Call_no;
cnt:=cnt-1;
UPDATE Book
SET amount = cnt
WHERE Call_no =:new.Call_no;
END;
/
Commit;