i4pntnXR

1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school;
3USE school;
4
5#https://moodle.elsys-bg.org/pluginfile.php/1994/mod_resource/content/3/school.sql
6
7CREATE TABLE H_Students(
8	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
9    BeginDate DATE NOT NULL,
10    EndDate DATE NOT NULL,
11	Id INTEGER NOT NULL,
12	Name VARCHAR(150) NOT NULL,
13	Num INTEGER NOT NULL,
14	ClassNum INTEGER NOT NULL,
15	ClassLetter CHAR(1) NOT NULL,
16	Birthday DATE,
17	EGN CHAR(10),
18	PhysicsExamResult NUMERIC(3,2)
19);
20
21CREATE TABLE H_Subjects(
22	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
23    BeginDate DATE NOT NULL,
24    EndDate DATE NOT NULL,
25	Id INTEGER NOT NULL,
26	Name VARCHAR(100)
27);
28
29CREATE TABLE H_StudentMarks(
30	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
31    BeginDate DATE NOT NULL,
32    EndDate DATE NOT NULL,
33    StudentId INTEGER NOT NULL,
34	SubjectId INTEGER NOT NULL,
35	ExamDate DATETIME NOT NULL,
36	Mark NUMERIC(3,2) NOT NULL,
37	FOREIGN KEY (StudentId) REFERENCES Students(Id),
38	FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
39);
40
41CREATE TABLE Students(
42	Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
43	Name VARCHAR(150) NOT NULL,
44	Num INTEGER NOT NULL,
45	ClassNum INTEGER NOT NULL,
46	ClassLetter CHAR(1) NOT NULL,
47	Birthday DATE,
48	EGN CHAR(10),
49	PhysicsExamResult NUMERIC(3,2)
50);
51
52CREATE TABLE Subjects(
53	Id INTEGER NOT NULL AUTO_INCREMENT,
54	Name VARCHAR(100),
55	
56	PRIMARY KEY(Id)
57);
58
59CREATE TABLE StudentMarks(
60	StudentId INTEGER NOT NULL,
61	SubjectId INTEGER NOT NULL,
62	ExamDate DATETIME NOT NULL,
63	Mark NUMERIC(3,2) NOT NULL,
64	
65	PRIMARY KEY( StudentId, SubjectId, ExamDate ),
66	FOREIGN KEY (StudentId) REFERENCES Students(Id),
67	FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
68);
69
70CREATE TABLE MarkWords(
71	RangeStart NUMERIC(3,2) NOT NULL,
72	RangeEnd NUMERIC(3,2) NOT NULL,
73	MarkAsWord VARCHAR(15),
74	
75	PRIMARY KEY(RangeStart, RangeEnd)
76);
77
78INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
79INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
80INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
81INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
82INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
83
84INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
85INSERT INTO Subjects(Name) VALUES( 'Литература' );
86INSERT INTO Subjects(Name) VALUES( 'Математика' );
87INSERT INTO Subjects(Name) VALUES( 'СУБД' );
88
89INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
90INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
91INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
92INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
93INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
94INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
95INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
96
97INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
98INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
99INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
100INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
101INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
102
103delimiter //
104CREATE TRIGGER studentLogger BEFORE INSERT ON Students
105FOR EACH ROW BEGIN
106	DECLARE startDate DATETIME;
107	DECLARE endDate DATETIME;
108    
109    SET startDate = CURDATE();
110    
111    INSERT INTO H_Students() VALUES(startDate, CURDATE(), NEW.Id, New.Name, New.Num, New.ClassNum, New.ClassLetter, New.Birthday, NEW.EGN, NEW.PhysicsExamResult);
112END; //
113
114DELIMITER ;