· 6 years ago · Nov 21, 2019, 03:58 PM
1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school CHARSET 'utf8';
3USE school;
4
5CREATE TABLE Students(
6 Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
7 Name VARCHAR(150) NOT NULL,
8 Num INTEGER NOT NULL,
9 ClassNum INTEGER NOT NULL,
10 ClassLetter CHAR(1) NOT NULL,
11 Birthday DATE,
12 EGN CHAR(10),
13 EntranceExamResult NUMERIC(3,2)
14);
15
16CREATE TABLE Subjects(
17 Id INTEGER NOT NULL AUTO_INCREMENT,
18 Name VARCHAR(100),
19
20 PRIMARY KEY(Id)
21);
22
23CREATE TABLE StudentMarks(
24 StudentId INTEGER NOT NULL,
25 SubjectId INTEGER NOT NULL,
26 ExamDate DATETIME NOT NULL,
27 Mark NUMERIC(3,2) NOT NULL,
28
29 PRIMARY KEY( StudentId, SubjectId, ExamDate ),
30 FOREIGN KEY (StudentId) REFERENCES Students(Id),
31 FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
32);
33
34CREATE TABLE MarkWords(
35 RangeStart NUMERIC(3,2) NOT NULL,
36 RangeEnd NUMERIC(3,2) NOT NULL,
37 MarkAsWord VARCHAR(15),
38
39 PRIMARY KEY(RangeStart, RangeEnd)
40);
41
42CREATE TABLE H_Students(
43 UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
44 EventDate DATETIME NOT NULL,
45 EventType CHAR(1),
46 Id INTEGER,
47 Name VARCHAR(150),
48 Num INTEGER,
49 ClassNum INTEGER,
50 ClassLetter CHAR(1),
51 Birthday DATE,
52 EGN CHAR(10),
53 EntranceExamResult NUMERIC(3,2)
54);
55
56CREATE TABLE H_StudentMarks(
57 UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
58 EventDate DATETIME NOT NULL,
59 EventType CHAR(1),
60
61 StudentId INTEGER,
62 SubjectId INTEGER,
63 ExamDate DATETIME,
64 Mark NUMERIC(3,2)
65);
66
67DELIMITER $
68
69CREATE TRIGGER Students_AI_Hist AFTER INSERT ON Students FOR EACH ROW
70BEGIN
71 INSERT INTO H_Students
72 VALUES(NULL, NOW(), 'I', NEW.Id, NEW.Name, NEW.Num,
73 NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.EntranceExamResult);
74END$
75
76CREATE TRIGGER Students_AU_Hist AFTER UPDATE ON Students FOR EACH ROW
77BEGIN
78 INSERT INTO H_Students
79 VALUES(NULL, NOW(), 'U', NEW.Id, NEW.Name, NEW.Num,
80 NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.EntranceExamResult);
81END$
82
83CREATE TRIGGER Students_AD_Hist AFTER DELETE ON Students FOR EACH ROW
84BEGIN
85 INSERT INTO H_Students
86 VALUES(NULL, NOW(), 'D', OLD.Id, OLD.Name, OLD.Num,
87 OLD.ClassNum, OLD.ClassLetter, OLD.Birthday, OLD.EGN, OLD.EntranceExamResult);
88END$
89
90CREATE TRIGGER StudentMarks_AI_Hist AFTER INSERT ON StudentMarks FOR EACH ROW
91BEGIN
92 INSERT INTO H_StudentMarks
93 VALUES(NULL, NOW(), 'I', NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
94END$
95
96CREATE TRIGGER StudentMarks_AU_Hist AFTER UPDATE ON StudentMarks FOR EACH ROW
97BEGIN
98 INSERT INTO H_StudentMarks
99 VALUES(NULL, NOW(), 'U', NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
100END$
101
102CREATE TRIGGER StudentMarkss_AD_Hist AFTER DELETE ON StudentMarks FOR EACH ROW
103BEGIN
104 INSERT INTO H_StudentMarks
105 VALUES(NULL, NOW(), 'D', OLD.StudentId, OLD.SubjectId, OLD.ExamDate, OLD.Mark);
106END$
107
108DELIMITER ;
109
110INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
111INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
112INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
113INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
114INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
115
116INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
117INSERT INTO Subjects(Name) VALUES( 'Литература' );
118INSERT INTO Subjects(Name) VALUES( 'Математика' );
119INSERT INTO Subjects(Name) VALUES( 'СУБД' );
120
121INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-03', 6 );
122INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-31', 5.50 );
123INSERT INTO StudentMarks VALUES( 102, 11, '2018-04-28', 5 );
124INSERT INTO StudentMarks VALUES( 103, 12, '2018-04-28', 4 );
125INSERT INTO StudentMarks VALUES( 104, 13, '2018-03-03', 5 );
126INSERT INTO StudentMarks VALUES( 104, 13, '2018-04-07', 6 );
127INSERT INTO StudentMarks VALUES( 104, 11, '2018-04-07', 4.50 );
128
129INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
130INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
131INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
132INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
133INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );