Домашнее задание по курсу
“Теоретические основы баз данных”
Данякина Ивана и Тютютнниковой Юлии
Студентов группы К06-224.
Задания: 5, 17, 30, 41, 47
Текст запроса: Сформировать список студентов группы к5-221, которые сдали все экзамены в срок:
Написание запроса на реляционной алгебре.
Экзамены_в_срок_К5-221(#Nз, Фио)=
(
Студент[
Студент.[#Nз] = Успеваемость.[#Nз]
]Успеваемость
Студент[
Студент.[ Nгр] =ОтчетГруппы. [#Nгр]
]ОтчетГруппы
Успеваемость [
Успеваемость [#ВидОтчета = ‘Экзамен’]
ОтчетГруппы.[#Nгр] = "К05-221"
Успеваемость [#Семестр = ‘5’]
Успеваемость[#УчНед] = ОтчетГруппы[#УчНед]
Успеваемость[#ИдКурса] = ОтчетГруппы[#ИдКурса]
(Успеваемость [Оценка != «Неудовл»] )
] ОтчетГруппы
)[#Nз, Фио]
Запишем исчисление на кортежах.
z
(#Nз(z), Фио(z))~
х / Студент
y / ОтчетГруппы
t / Успеваемость
#Nз(x) = #Nз(t) & Nгp(x) = #Nгр(y) & ФИО(z) = ФИО(х) & [#ВидОтчета(t) = ‘Экзамен’] & [#Семестр(t) = ‘5’] &
#УчНед(t)! = #УчНед(y) &
#ИдКурса(t) = #ИдКурса(y)
(Оценка(t) != “Неудовл”)
(#Nгр(y) = "К05-221")
Написание запроса на SQL
Экзамены_в_срок_К5-221(#Nз, Фио)
SELECT DISTINCT Студент.*
FROM ОтчетГруппы, Успеваемость, Студент,
(
SELECT Студент.[#Nз] as N
FROM ОтчетГруппы, Успеваемость, Студент
WHERE Успеваемость.[#УчНед] <> ОтчетГруппы.[#УчНед] AND ОтчетГруппы.[#Nгр] = Студент.Nгр AND ОтчетГруппы.[#ИдКурса] = Успеваемость.[#ИдКурса] AND Студент.[#Nз] = Успеваемость.[#Nз] AND (Успеваемость.[#Семестр]="5") AND (Успеваемость.[#ВидОтчета]="Экзамен")
) AS t1
WHERE (Студент.[#Nз] = Успеваемость.[#Nз]) AND (Студент.Nгр = ОтчетГруппы.[#Nгр]) AND (ОтчетГруппы.[#ИдКурса] = Успеваемость.[#ИдКурса]) AND (ОтчетГруппы.[#УчНед] = Успеваемость.[#УчНед])
AND (ОтчетГруппы.[#Nгр] = "К05-221") AND (Успеваемость.[#ВидОтчета]="Экзамен") AND (Успеваемость.Оценка<>"Неудов") AND Студент.[#Nз] <> t1.N
Таблица Студент
Таблица Отчет группы
Таблица Успеваемость
После применения запроса.
Запрос «Экзамены_в_срок_К5-221» получает виртуальную таблицу (view) со студентами сдавшими во время экзамен:
Тест запроса: Сформировать список групп, которые сдают в 5 семестре только 2 экзамена.
Написание запроса на реляционной алгебре.
Напишем дополнительный запрос, определяющий два или более экзаменов сдающихся на пятом семестре.
2_и более_экзамен_5(#Nгр, Спец, Nкаф)=
(
Группа[
Группа.[#Nгр] = ОтчетГруппы[ #Nгр]
]ОтчетГруппы
Успеваемость[
Успеваемость[#ИдКурса] = ОтчетГруппы[#ИдКурса]
]ОтчетГруппы
ОтчетГруппы[
( ОтчетГруппы[ #ВидОтчета = “’экзамен”] ) &
(ОтчетГруппы[#ИдКурса] != ОтчетГруппы’[#ИдКурса])
] ОтчетГруппы’
Успеваемость[
(Успеваемость[#Семестр] = ‘5’])
]Успеваемость’
) [#Nгр, #ИдКурса. Спец, Nкаф]
Напишем дополнительный запрос, определяющий три или более экзаменов сдающихся на пятом семестре.
3_и более_экзамен_5(#Nгр, Спец, Nкаф)=
(
Группа[
Группа.[#Nгр] = ОтчетГруппы[ #Nгр]
]ОтчетГруппы
Успеваемость[
Успеваемость[#ИдКурса] = ОтчетГруппы[#ИдКурса]
]ОтчетГруппы
ОтчетГруппы[
( ОтчетГруппы[ #ВидОтчета = “’экзамен”] ) &
(ОтчетГруппы[#ИдКурса] != 2_и более_экзамен_5 [#ИдКурса])
] 2_и более_экзамен_5
Успеваемость[
(Успеваемость[#Семестр] = ‘5’])
]Успеваемость’
) [#Nгр, #ИдКурса. Спец, Nкаф]
Запишем вспомогательный запрос, выдающий все экзамены на 5 семестре.
экзамен_5(#Nгр, Спец, Nкаф) =
(
Группа[ Группа.[#Nгр] = ОтчетГруппы[ #Nгр] ]ОтчетГруппы AND Успеваемость[Успеваемость[#ИдКурса] = ОтчетГруппы[#ИдКурса] ]ОтчетГруппы
Успеваемость[
(Успеваемость[#Семестр] = ‘5’])
]Успеваемость’
ОтчетГруппы[
( ОтчетГруппы[ #ВидОтчета = “’экзамен”]
] ОтчетГруппы’
) [#Nгр, #ИдКурса. Спец, Nкаф]
Напишем дополнительный запрос, определяющий менее трех экзаменов сдающихся на пятом семестре.
менее_3_экзамен_5(#Nгр, Спец, Nкаф) = ( ( экзамен_5(#Nгр, #ИдКурса. Спец, Nкаф) \ 3_или более_экзамен_5(#Nгр, #ИдКурса, Спец, Nкаф) ) [#Nгр, Спец, Nкаф]
Запишем запрос, определяющий равно один или менее.
1_ или менее_экзамен_5(#Nгр, Спец, Nкаф) = ( ( экзамен_5(#Nгр, #ИдКурса. Спец, Nкаф) \ 2_или более_экзамен_5(#Nгр, #ИдКурса, Спец, Nкаф) ) [#Nгр, Спец, Nкаф]
Запишем равно два экзамена.
2_экзамена_5 (#Nгр, Спец, Nкаф) = (менее_3_экзамен_5 (#Nгр, #ИдКурса. Спец, Nкаф) \ 1_ или менее _экзамен_5 (#Nгр, #ИдКурса, Спец, Nкаф) ) [#Nгр, Спец, Nкаф]
Запишем запрос в исчислении на кортежах.
Напишем вспомогательный запрос, определяющий два и более экзаменов.
z (#Nгр(z), Спец(z), Nкаф(z)) ~
x/ ОтчетГруппы
y1/ Расписание
y2/ Расписание
u/ Группа
#Nгр (x)= #Nгр (y1) & #Nгр(u) = #Nгр (x) & #Nгр(z) = #Nгр (x) &
Спец(x) = Спец(z), & Nкаф(z)= Nкаф(u) &
(#ВидОтчета(x) = ‘экзамен’ ) & (#Семестр(y) = ‘5’) &
( (ИдКурса(y1) != ИдКурса(y2) ).
Запишем запрос, вычисляющий три или более экзаменов.
z (#Nгр(z), Спец(z), Nкаф(z)) ~
x/ ОтчетГруппы
y1/ Расписание
y2/ 2_или_более
u/ Группа
#Nгр (x)= #Nгр (y1) & #Nгр(u) = #Nгр (x) & #Nгр(z) = #Nгр (x) &
Спец(x) = Спец(z), & Nкаф(z)= Nкаф(u) &
(#ВидОтчета(x) = ‘экзамен’ ) & (#Семестр(y) = ‘5’) &
( (ИдКурса(y1) != ИдКурса (y2 ) ).
Запишем запрос, определяющий один или менее экзамена.
z (#Nгр(z), Спец(z), Nкаф(z)) ~
x/ ОтчетГруппы
y/ Расписание
u/ Группа
p/ 2_или_более
#Nгр (x)= #Nгр (y) & #Nгр(u) = #Nгр (x) & #Nгр(z) = #Nгр (x) &#Nгр(z)!=#Nгр(p)
Спец(x) = Спец(z), & Nкаф(z)= Nкаф(u) &
(#ВидОтчета(x) = ‘экзамен’ ) & (#Семестр(y) = ‘5’) &
Запишем запрос определяющий все экзамены.
z (#Nгр(z), Спец(z), Nкаф(z)) ~
x/ ОтчетГруппы
y/ Расписание
u/ Группа
#Nгр (x)= #Nгр (y) & #Nгр(u) = #Nгр (x) & #Nгр(z) = #Nгр (x) &
Спец(x) = Спец(z), & Nкаф(z)= Nкаф(u) &
(#ВидОтчета(x) = ‘экзамен’ ) & (#Семестр(y) = ‘5’)
Запишем запрос определяющий ровно два экзамена.
z (#Nгр(z), Спец(z), Nкаф(z)) ~
x/ ОтчетГруппы
y/ Расписание
u/ Группа
p/ 3_или_более
f/ 1_или_менее
#Nгр (x)= #Nгр (y) & #Nгр(u) = #Nгр (x) & #Nгр(z) = #Nгр (x) &#Nгр(z)!=#Nгр(p) & #Nгр(z) != #Nгр (f)
Спец(x) = Спец(z), & Nкаф(z)= Nкаф(u) &
(#ВидОтчета(x) = ‘экзамен’ ) & (#Семестр(y) = ‘5’)
Запишем запрос на SQL
SELECT DISTINCT Группа.[#Nгр], Группа.Спец, Группа.Nкаф
FROM Успеваемость, Группа, ОтчетГруппы, (SELECT ОтчетГруппы.[#Nгр], COUNT(*) as cs FROM ОтчетГруппы GROUP BY ОтчетГруппы.[#Nгр]) g
WHERE ОтчетГруппы.[#ВидОтчета]="экзамен" AND Успеваемость.[#Семестр]="5" AND Группа.[#Nгр] = ОтчетГруппы.[#Nгр] AND Успеваемость.[#ИдКурса] = ОтчетГруппы.[#ИдКурса] AND g.cs = 2 AND g.[#Nгр] = ОтчетГруппы.[#Nгр];
Таблица Группа.
Таблица Отчет Группы
Таблица Успеваемость
Итоговая таблица
