отчет. Отчет По дисциплине "Базы данных" Студент гр. Бис1902 Е. Р. Тимохина

• Название: Отчет По дисциплине "Базы данных" Студент гр. Бис1902 Е. Р. Тимохина
• Анкор: отчет
• Дата: 14.06.2022
• Размер: 1.44 Mb.
• Имя файла: OTChET_BD_33.docx
• Тип: Отчет; #590769
• страница: 5 из 7

Лабораторная работа №5

1. Создать БД Успеваемость :

1.1. Таблица студенты: Код, Фамилия, Код группы

create table groupp (

id int not null auto_increment,

name varchar(15) not null,

avg_point int default null, /*2.3 - средний балл по группе*/

primary key (id)

);

create table students (

id int not null auto_increment,

fio varchar(30) not null,

group_id int not null,

primary key (id),

foreign key (group_id)

references groupp (id)

);

insert into groupp (name) values ('БИС-19-02'), ('БИС-20-02'), ('БИС-21-02');

insert into students (fio, group_id) values ('Романенко Степан', 1),('Романенко Роман', 1),

('Петров Петр', 2), ('Лиза Петровна', 2),('Иванов Иван', 3),('Иванов Степан', 3);

1.2. Таблица дисциплины: Код дисциплины, Наименование дисциплины

create table disciplines (

id int not null auto_increment,

name varchar(50) not null,

avg_point int default null, /*2.4 - средний балл по дисциплине*/

primary key (id)

);

insert into disciplines (name) values ('Программирование'), ('АИС'), ('БД');

1.3. Таблица успеваемость: Код студента, Код дисциплины, Группы, Курс, Количество баллов, Оценка

create table progress(

student_id int not null,

discipline_id int not null ,

group_id int default null,

course int default null,

pointt int not null,

mark varchar(20) default null,

foreign key (student_id)

references students (id),

foreign key (group_id)

references groupp (id),

foreign key (discipline_id)

references disciplines (id)

);

create table not_attestovn ( /*задание 2.5 - курс и студентов*/

course int not null,

quantity int default null,

primary key(course)

);

2. Создать триггеры для таблицы успеваемость:

2.1. При внесении группы автоматически определяет и вносит курс (Например: Группа БПИ-14-01 – 1 курс, БИС-13-01 – 2 курс, т.е. курс определяется по году поступления) рисунок 25.

create trigger course_and_mark before insert on progress

for each row

begin

select group_id from students where id = new.student_id into @group_id;

set new.group_id = @group_id;

select name from groupp where id = new.group_id into @group_name;

SET @yea = right(@group_name,5);

SET NEW.course = left(@yea,2);

if (month(CURRENT_DATE) >= 9) then

SET NEW.course = (YEAR(CURRENT_DATE) - 2000 - NEW.course + 1);

else

SET NEW.course = (YEAR(CURRENT_DATE) - 2000 - NEW.course);

end if;

2.2. При внесении количества баллов вносит оценку рисунок 25.

(>=61 Удовлетворительно, >=76 Хорошо, >= 91 Отлично, < 61 Не аттестован)

IF NEW.pointt >= 91 THEN SET NEW.mark = 'Отлично';

ELSEIF NEW.pointt >= 76 THEN SET NEW.mark = 'Хорошо';

ELSEIF NEW.pointt >= 61 THEN SET NEW.mark = 'Удовлетворительно';

ELSEIF NEW.pointt < 61 THEN SET NEW.mark = 'Не аттестован';

END IF; END//

#Для проверки триггера course_and_mark определения курса и оценки

insert into progress (student_id, discipline_id , pointt) values (1, 1, 61),(2, 1, 81),(3, 2, 91),(4, 2, 71),(5, 3, 81),(6, 3, 91),(6, 1, 69)//

select s.fio, g.name, p.course, p.pointt, p.mark from progress as p join groupp as g on (g.id = p.group_id) join students as s on (s.id = p.student_id);



Рисунок 25 - автоматическое внесение курса и оценки

2.3. Рассчитывает средний балл по группе рисунок 26.

create trigger progress_trigger_after after insert on progress

for each row

begin

select avg(pointt) from progress where group_id = new.group_id into @group_average;

update groupp set avg_point = @group_average where id = new.group_id;…

select * from groupp;



рисунок 26 – средний балл по группе

2.4. Рассчитывает средний балл по дисциплине рисунок 27.

select avg(pointt) from progress where discipline_id = new.discipline_id into @discipline_average;

update disciplines set avg_point = @discipline_average where id = new.discipline_id;…

select * from disciplines;



Рисунок 27 - средний балл по дисциплине

2.5. Считает количество студентов, неаттестованных хотя бы по одному предмету, на каждом курсе рисунок 28.

select count(distinct student_id) from progress where course = new.course and pointt < 61 into @countOfquantity;

select count(*) from not_attestovn where course = new.course into @iscourseExixts;

if @iscourseExixts <> 0 then

update `not_attestovn` set `quantity` = @countOfquantity where `not_attestovn`.`course` = new.`course`;

else

insert into not_attestovn (`course`, quantity) values (new.`course`, @countOfquantity);

end if; END//

#Для проверки триггера progress_trigger_after не аттестованных

insert into progress (student_id, discipline_id , pointt) values (1, 2, 31),(2, 2, 21), (3, 1, 51), (5, 1, 42),(6, 2, 32)//

select * from not_attestovn;//



Рисунок 28 - количество студентов, неаттестованных хотя бы по одному предмету, на каждом курсе

Дополнение к лабораторной работе № 5

Триггеры к собственной БД `mydb`.

После добавления товаров в заказ - считать сумму заказа рисунок 29.

create trigger sum_and_quantity after insert on goods_in_Neworder

for each row

begin

SELECT sum(application_price * quantity) FROM goods_in_Neworder where fromNeworder_id = new.fromNeworder_id into @summa;

update Neworder set summa = @summa where id_Neworder = new.fromNeworder_id;



Рисунок 29 - сумма заказа, после добавления товаров в заказ

#количество товара на складе рисунок 30;

SELECT quantity from goods_in_Neworder where fromGoods_id = new.fromGoods_id and id_goodsinNeworder=new.id_goodsinNeworder into @q_use;

SELECT stock_quantity from goods where id_good = new.fromGoods_id into @q_stock;

if (@q_stock >= @q_use) then

update goods set stock_quantity = @q_stock - @q_use where id_good = new.fromGoods_id;



Рисунок 30 - количество товара на складе

#Сколько товаров не хватает к заказу и стоимость докупки рисунок 31;

select name from goods where id_good = new.fromGoods_id into @name;

select starting_price from goods where id_good = new.fromGoods_id into @start_price;

set @not_enough = @q_use - @q_stock;

set @prise = @start_price*@not_enough;

update goods set stock_quantity = 0 where id_good = new.fromGoods_id;

INSERT INTO must_buy ( `fromGINlist_id`, `good`,`not_enough`, `price`) VALUES ( new.id_goodsinNeworder, @name, @not_enough, @prise);

end if;



Рисунок 31 - Сколько товаров не хватает к заказу и стоимость докупки

#заказ на максимальную сумму у сотрудника (при добавлении новых товаров к заказу - пересчитывается) рисунок 32;

select fromStaff_id from Neworder as o join goods_in_Neworder as gin on (o.id_Neworder = gin.fromNeworder_id)

where fromNeworder_id = new.fromNeworder_id and id_goodsinNeworder=new.id_goodsinNeworder into @staff;

select sum(gin.application_price * gin.quantity) FROM goods_in_Neworder as gin join Neworder as o on (o.id_Neworder = gin.fromNeworder_id)

join staff as s on (s.id_staff=o.fromStaff_id) where s.id_staff = @staff group by gin.fromNeworder_id

having sum(gin.application_price * gin.quantity) >= all(select sum(gin.application_price * gin.quantity)

FROM goods_in_Neworder as gin join Neworder as o on (o.id_Neworder = gin.fromNeworder_id)

join staff as s on (s.id_staff=o.fromStaff_id) where s.id_staff = @staff group by gin.fromNeworder_id) into @suma;

if (@suma>0) then

update staff set total_sum = @suma where id_staff=@staff;

else

update staff set total_sum = 0 where id_staff=@staff;

end if;END//



Рисунок 32 - заказ на максимальную сумму у сотрудника

Добавление данных для проверки триггеров:

INSERT INTO goods (`name`, `starting_price`, `stock_quantity`) VALUES ('Тест2', 50, 100);//

insert into goods_in_Neworder (`quantity`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) value (120, 300, 1, 10, 6);//

insert into goods_in_Neworder (`quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) value (5, 50, 200, 1, 6, 5);

insert into goods_in_Neworder (`quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) value (4, 50, 100, 1, 7, 5);

insert into goods_in_Neworder (`quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) value (2, 50, 150, 1, 7, 5);

insert into goods_in_Neworder (`quantity`, `old_price`, `application_price`, `fromDrawings_id`, `fromNeworder_id`, `fromGoods_id`) value (15, 50, 10, 1, 5, 5);//

select * from Neworder; #сумма заказа

select * from goods; #остаток товара на складе

select * from must_buy; #таблица сколько товаров не хвататет

create trigger staff_completedOrders after insert on Neworder

for each row

begin

#сколько всего, выполненных заказов и за текущий месяц рисунок 33.

select count(id_Neworder) from Neworder where fromStaff_id=new.fromStaff_id into @total;

select count(id_Neworder) from Neworder where fromStaff_id=new.fromStaff_id and month(start_date) = (month(CURRENT_DATE)) into @permonth;

select count(id_Neworder) from Neworder where fromStaff_id=new.fromStaff_id and month(start_date) = (month(CURRENT_DATE)) and status like "завершен" into @permonth_done;

update staff set total_all=@total where id_staff=new.fromStaff_id;

update staff set order_permonth=@permonth where id_staff=new.fromStaff_id;

update staff set done_permonth=@permonth_done where id_staff=new.fromStaff_id;

END//



Рисунок 33 - сколько всего, выполненных заказов и за текущий месяц

create trigger bad_order after insert on Neworder

for each row

begin

#вывести список не завершенных заказов рисунок 34.

SELECT articule from Neworder where status not like "завершен" and id_Neworder=New.id_Neworder into @articule;

SELECT c.name from client as c join contracts as cn on (c.id_client=cn.fromClient_id) join

Neworder as n on (cn.id_contract = fromContracts_id) where n.status not like "завершен" and n.id_Neworder=New.id_Neworder into @client;

SELECT status from Neworder where status not like "завершен" and id_Neworder=New.id_Neworder into @status;

SELECT end_date from Neworder where status not like "завершен" and id_Neworder=New.id_Neworder into @end_date;

INSERT INTO ForBadOrder ( `articule`,`client`, `status`, `end_date`) VALUES ( @articule, @client, @status, @end_date);

END//

INSERT INTO Neworder ( `articule`, `start_date`, `prepayment`, `status`, `fromContracts_id`, `fromStaff_id`, `end_date`) VALUES ( 'ZZ-21', '2022-03-25', 0, 'в процессе', 6, 3, '2022-03-28');//

select * from ForBadOrder



Рисунок 34 - список не завершенных заказов