· 7 years ago · Jan 10, 2019, 09:48 AM
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 PhysicsExamResult 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 BeginDate DATETIME NOT NULL,
45 EndDate DATETIME,
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 PhysicsExamResult NUMERIC(3,2)
54);
55
56CREATE TABLE H_StudentMarks(
57 UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
58 BeginDate DATETIME NOT NULL,
59 EndDate DATETIME,
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(), NULL, NEW.Id, NEW.Name, NEW.Num,
73 NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.PhysicsExamResult);
74END$
75
76CREATE TRIGGER Students_AU_Hh_studentsist AFTER UPDATE ON Students FOR EACH ROW
77BEGIN
78 UPDATE H_Students SET EndDate = NOW()
79 WHERE Id = OLD.Id AND EndDate IS NULL;
80
81 INSERT INTO H_Students
82 VALUES(NULL, NOW(), NULL, NEW.Id, NEW.Name, NEW.Num,
83 NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.PhysicsExamResult);
84END$
85
86CREATE TRIGGER Students_AD_Hist AFTER DELETE ON Students FOR EACH ROW
87BEGIN
88 UPDATE H_Students SET EndDate = NOW()
89 WHERE Id = OLD.Id AND EndDate IS NULL;
90END$
91
92CREATE TRIGGER StudentMarks_AI_Hist AFTER INSERT ON StudentMarks FOR EACH ROW
93BEGIN
94 INSERT INTO H_StudentMarks
95 VALUES(NULL, NOW(), NULL, NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
96END$
97
98CREATE TRIGGER StudentMarks_AU_Hh_studentsist AFTER UPDATE ON StudentMarks FOR EACH ROW
99BEGIN
100 UPDATE H_StudentMarks SET EndDate = NOW()
101 WHERE StudentId = OLD.StudentId AND SubjectId = OLD.SubjectId AND
102 ExamDate = OLD.ExamDate AND EndDate IS NULL;
103
104 INSERT INTO H_StudentMarks
105 VALUES(NULL, NOW(), NULL, NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
106END$
107
108CREATE TRIGGER StudentMarkss_AD_Hist AFTER DELETE ON StudentMarks FOR EACH ROW
109BEGIN
110 UPDATE H_StudentMarks SET EndDate = NOW()
111 WHERE StudentId = OLD.StudentId AND SubjectId = OLD.SubjectId AND
112 ExamDate = OLD.ExamDate AND EndDate IS NULL;
113END$
114
115DELIMITER ;
116
117INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
118INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ИÑидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
119INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
120INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
121INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ÐлекÑандър Момчев', 1, 8, 'а', '2002-06-11', NULL );
122
123INSERT INTO Subjects(Id, Name) VALUES( 11, 'ÐнглийÑки език' );
124INSERT INTO Subjects(Name) VALUES( 'Литература' );
125INSERT INTO Subjects(Name) VALUES( 'Математика' );
126INSERT INTO Subjects(Name) VALUES( 'СУБД' );
127
128INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-03', 6 );
129INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-31', 5.50 );
130INSERT INTO StudentMarks VALUES( 102, 11, '2018-04-28', 5 );
131INSERT INTO StudentMarks VALUES( 103, 12, '2018-04-28', 4 );
132INSERT INTO StudentMarks VALUES( 104, 13, '2018-03-03', 5 );
133INSERT INTO StudentMarks VALUES( 104, 13, '2018-04-07', 6 );
134INSERT INTO StudentMarks VALUES( 104, 11, '2018-04-07', 4.50 );
135
136INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
137INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
138INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
139INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
140INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );