Главная
страница 1

ДИСЦИПЛИНА. «БАЗЫ ДАННЫХ И СУБД»



Пояснительная записка
Контрольно-измерительные материалы по дисциплине «Базы данных и СУБД» предназначены для проверки знаний студентов 3-го курса математического факультета ГГПИ (6-й семестр).
Критерии оценки и условия проведения
Задания по структурированному языку запросов SQL рассчитаны на использование базы данных, отражающей учет успеваемости студентов. В базе данных содержится четыре таблицы.

В таблице СТУДЕНТЫ (STUDENTS) содержится двенадцать полей с информацией о студентах: SNUM (номер студенческого билета), SFAM (фамилия), SIMA (имя), SOTCH (отчество), SRDATE (дата рождения), SPOL (пол), STELEFON (телефон), SOBLASTI (область), SGOROD (город), SADRES (улица, номер дома и квартира), GRUPPA (группа), STIP (размер получаемой стипендии).

В таблице ПРЕДМЕТЫ (PREDMET) содержится информация об учебных предметах. Назначение полей таблицы следующее: PNUM (код учебного предмета), PNAME (наименование учебного предмета), TNUM (код преподавателя), HOURS (продолжительность учебной дисциплины в часах), COURS (курс, на котором ведется данный учебный предмет).

В таблице ПРЕПОДАВАТЕЛИ (TEACHERS) содержится информация о преподавателях. Поля таблицы следующие: TNUM (код преподавателя), TDATE (дата принятия преподавателя на работу); поля TFAM, TIMA, TOTCH, TTELEFON, TOBLASTI, TGOROD, TADRES, значения которых аналогичны соответствующим значениям полей из таблицы STUDENTS.

В таблице УСПЕВАЕМОСТЬ (USP) хранится информация об успеваемости студентов по учебным дисциплинам. Таблица состоит из полей: UNUM (код факта сдачи учебной дисциплины), OCENKA (оценка, полученная студентом по учебному предмету), UDATE (дата сдачи), SNUM (номер студенческого билета), PNUM (код учебного предмета).

Контрольные задания по SQL оформляются на отдельном листе (письменно) и сдаются преподавателю для проверки. При выполнении всех 9-10 заданий выставляется оценка «отлично», при выполнении 7-8 заданий выставляется оценка «хорошо», при выполнении 5-6 заданий выставляется оценка «удовлетворительно», при выполнении менее 5 заданий выставляется оценка «неудовлетворительно». Время выполнения заданий составляет 1 академический час (40 минут).

Задания по нормализации БД оформляются на отдельном листе (письменно) и сдаются преподавателю для проверки. Оценка «отлично» выставляется студентам, которые верно выполнили оба задания, оценка «хорошо» выставляется студентам, выполнившим оба задания с незначительными замечаниями, оценка «удовлетворительно» выставляется при выполнении половины заданий, «неудовлетворительно» – выполнено меньше половины. Время выполнения задания составляет 2 академических часа (80 минут).
Текст заданий
I. Выполнение запросов SQL.


  1. Выведите данные о студентах (номер студенческого билета, фамилия, имя и дата рождения), родившихся в 1981 году и проживающих в г. Глазове или Ижевске.

  2. Выведите фамилию, имя и номер телефона студентов 124, 125 и 921 групп, проживающих по улице Кирова.

  3. Для каждого студента определите количество учебных дисциплин, которые он сдавал, и среднюю оценку по ним. Выведите номер студенческого билета, фамилию и имя. Список студентов упорядочите по алфавиту.

  4. Определите предметы, для которых средний балл больше 4. Выведите код и наименование предмета, средний балл и количество студентов, сдававших экзамен по данному предмету. Упорядочите данные по среднему баллу в порядке убывания.

  5. Составьте список студентов, стипендия которых выше средней, указав то, на сколько их стипендия превышает среднюю.

  6. Выведите список отличников (фамилию, имя и группу). Упорядочите данные по группе и по фамилии.

  7. Определите количество выставленных каждым преподавателем оценок «2».

  8. Сформируйте список, состоящий из ФИО студентов и преподавателей (при выводе данных отразите их принадлежность к списку студентов или преподавателей).

  9. В таблицу GLAZOV, имеющую ту же структуру, что и STUDENTS, добавьте из таблицы STUDENTS студентов из г. Глазова, имеющих телефон.

  10. Переведите студентов 135 группы на следующий курс и увеличьте им размер стипендии на 15%.


II. Нормализация баз данных

    1. Дайте определения понятий: нормализация баз данных, функциональная зависимость, полная функциональная зависимость, транзитивная зависимость. Дать определение 1НФ, 2НФ, 3НФ.

    2. Приведите последовательно БД «Поликлиника» к 1НФ, 2НФ и 3НФ.

Фамилия

пациента


Дата

рождения


Номер участка

Фамилия врача

Дата посещения

Диагноз

Лосев О.И.

20.04.65

2

Петрова О.И.

11.04.98

грипп










Петрова О.И.

26.07.98

бронхит

Орлова Е.Ю.

25.01.47

1

Андреева И.В.

05.05.98

ОРЗ










Андреева И.В.

11.07.98

гастрит

Дуров М.Т.

05.03.30

2

Петрова О.И.

14.03.98

стенокардия










Петрова О.И.

26.07.98

ОРЗ

Жукова Л.Г.

30.01.70

2

Петрова О.И.

11.04.98

ангина

Быкова А.А.

01.04.75

1

Андреева И.В.

15.06.98

ОРЗ

Эталоны правильных ответов

  1. Выполнение запросов SQL.

1. SELECT SNum, SFam, SIma, SRDate FROM Students

WHERE SRDate BETWEEN #01/01/1981# AND #31/12/1981# AND

(SGorod=”Глазов” OR SGorod=”Ижевск”)

2. SELECT SFam, SIma, STelefon FROM Students

WHERE Gruppa IN (124, 125, 921) AND SAdres LIKE “*Кирова*”

3. SELECT S.SNum, SFam, SIma, COUNT(PNum) AS Count_PNum, AVG(Ocenka) AS Avg_Ocenka FROM Students S, Usp U WHERE S.SNum=U.SNum

GROUP BY SNum, SFam, SIma ORDER BY SFam, SIma

4. SELECT P.PNum, PName, COUNT(P.PNum) AS Count_ PNum, AVG(Ocenka) AS AVG_Ocenka FROM Predmet P, Usp U WHERE P.PNum=U.PNum

GROUP BY PNum HAVING AVG(Ocenka)>4 ORDER BY AVG(Ocenka) DESC

5. SELECT SNum, SFam, SIma, Stip-(SELECT AVG(Stip) FROM Students) AS RAZN FROM Students WHERE Stip>(SELECT AVG(Stip) FROM Students)

6. SELECT SFam, SIma, Gruppa FROM Students

WHERE SNum IN (SELECT Snum FROM Usp AS U GROUP BY Snum

HAVING COUNT(Snum)=(SELECT COUNT(Snum) FROM Usp

WHERE Ocenka=5 and Snum=U.Snum ))

ORDER BY Gruppa, SFam;

7. SELECT TFam, TIma, COUNT(Ocenka) FROM Teachers T, Predmet P, Usp U

WHERE T.TNum=P.TNum and P.Pnum=U.PNum and Ocenka=2

GROUP BY T.TNum, TFam, TIma ORDER BY TFam, TIma;

8. SELECT SFam AS Fam, SIma AS Ima, SOtch AS Otch, ‘Student’ AS Status

FROM Students

UNION ALL

SELECT TFam, TIma, TOtch, ‘Teacher’ FROM Teachers;

9. INSERT INTO Glazov SELECT * FROM Students WHERE SGorod=’Глазов’ and STelefon IS NOT NULL

10. UPDATE Students SET Gruppa=145, Stip=Stip*1.15 WHERE Gruppa=135




  1. Нормализация баз данных

    1. Определения

Определение1

Нормализация – это формальный метод анализа отношений на основе их первичного ключа и существующих функциональных зависимостей. Он включает ряд правил, которые могут использоваться для проверки отдельных отношений таким образом, чтобы вся база данных могла быть нормализована до желаемой степени нормализации. Если некоторое требование не удовлетворяется, то нарушающее данное требование отношение должно быть декомпозировано на отношения, каждое из которых (в отдельности) удовлетворяет всем требованиям нормализации.



Определение2

Функциональная зависимость описывает связь между атрибутами отношения. Например, если в отношении R, содержащем атрибуты А и В, атрибут В функционально зависит от атрибута А (что обозначается как АВ), то каждое значение атрибута А связано только с одним значением атрибута В. (Причем каждый из атрибутов А и В может состоять из одного или нескольких атрибутов.)



Определение 3

Полная функциональная зависимость – в некотором отношении атрибут B называется полностью функционально зависимым от атрибута A, если атрибут B функционально зависит от полного значения атрибута A и не зависит ни от какого подмножества полного значения атрибута A.

Функциональная зависимость AB является полной функциональной зависимостью, если удаление какого-либо атрибута из A приводит к утрате этой зависимости. Частичной функциональной зависимостью называется такая зависимость AB, если в A есть некий атрибут, при удалении которого эта зависимость сохраняется.

Определение4

Если для атрибутов A, B и C некоторого отношения существуют зависимости вида AB и BC, то говорят, что атрибут C транзитивно зависит от атрибута A через атрибут B (при условии, что атрибут A функционально не зависит ни от атрибута B, ни от атрибута C).

В данном случае транзитивная зависимость AC осуществляется через атрибут B. Это утверждение справедливо только в том случае, если атрибут A функционально не зависит от атрибутов B и C.

Определение 1НФ

Первая нормальная форма (1НФ) – отношение, в котором на пересечении каждой строки и каждого столбца содержится только одно значение.



Определение 2НФ

Вторая нормальная форма (2НФ) – отношение, которое находится в первой нормальной форме и каждый атрибут которого, не входящий в состав первичного ключа, характеризуется полной функциональной зависимостью от этого первичного ключа.



Определение 3НФ

Третья нормальная форма (ЗНФ) – отношение, которое находится в первой и второй нормальных формах и не имеет не входящих в первичный ключ атрибутов, которые находились бы в транзитивной функциональной зависимости от этого первичного ключа.




    1. Приведение БД к 1НФ, 2НФ и 3НФ

Приведение БД к 1НФ.

БД «Поликлиника» является ненормализованной, если содержит повторяющуюся группу данных (поля: Фамилия врача, Дата посещения, Диагноз).

В качестве ключевого атрибута ненормализованной таблицы выберем атрибут Фамилия пациента. Повторяющаяся группа устраняется с помощью ввода в каждую строку с описанием участка соответствующих сведений о пациенте.
Отношение 1

Фамилия пациента

Дата посещения

Дата
рождения

Номер

участка


Фамилия врача

Диагноз

Лосев О.И.

11.04.98

20.04.65

2

Петрова О.И.

грипп

Лосев О.И.

26.07.98

20.04.65

2

Петрова О.И.

бронхит

Орлова Е.Ю.

05.05.98

25.01.47

1

Андреева И.В.

ОРЗ

Орлова Е.Ю.

11.07.98

25.01.47

1

Андреева И.В.

гастрит

Дуров М.Т.

14.03.98

05.03.30

2

Петрова О.И.

стенокардия

Дуров М.Т.

26.07.98

05.03.30

2

Петрова О.И.

ОРЗ

Жукова Л.Г.

11.04.98

30.01.70

2

Петрова О.И.

ангина

Быкова А.А.

15.06.98

01.04.75

1

Андреева И.В.

ОРЗ

Полученное в результате этих действий Отношение 1 находится в первой нормальной форме.



Приведение Отношения 1 к 2НФ.

Первичным ключом отношения является группа атрибутов – Фамилия пациента и Дата посещения. Атрибуты Дата рождения, Номер участка и Фамилия врача зависят от части ключа, т.е. от атрибута Фамилия пациента. Переместим данные атрибуты с копией части первичного ключа, от которого они функционально зависят, в новые отношения.

Отношение 2


Фамилия пациента

Дата рождения

Номер участка

Фамилия врача

Лосев О.И.

20.04.65

2

Петрова О.И.

Орлова Е.Ю.

25.01.47

1

Андреева И.В.

Дуров М.Т.

05.03.30

2

Петрова О.И.

Жукова Л.Г.

30.01.70

2

Петрова О.И.

Быкова А.А.

01.04.75

1

Андреева И.В.

Отношение 3




Фамилия пациента

Дата посещения

Диагноз

Лосев О.И.

11.04.98

грипп

Лосев О.И.

26.07.98

бронхит

Орлова Е.Ю.

05.05.98

ОРЗ

Орлова Е.Ю.

11.07.98

гастрит

Дуров М.Т.

14.03.98

стенокардия

Дуров М.Т.

26.07.98

ОРЗ

Жукова Л.Г.

11.04.98

ангина

Быкова А.А.

15.06.98

ОРЗ

Отношения 2 и 3 находятся во второй нормальной форме.



Приведение Отношения 2 и 3 к 3НФ

В отношении 3 отсутствуют транзитивные зависимости, следовательно, оно находится в 3НФ. В отношении 2 существует транзитивная зависимость: Фамилия пациентаНомер участкаФамилия врача. Удалим транзитивную зависимость путем создания двух новых отношений.

Отношение 4


Фамилия пациента

Дата
рождения

Номер участка

Лосев О.И.

20.04.65

2

Орлова Е.Ю.

25.01.47

1

Дуров М.Т.

05.03.30

2

Жукова Л.Г.

30.01.70

2

Быкова А.А.

01.04.75

1

Отношение 5


Номер участка

Фамилия

врача


2

Петрова О.И.

1

Андреева И.В.



Отношения 3, 4 и 5 находятся в третьей нормальной форме.



Смотрите также:
Дисциплина. «Базы данных и субд»
127.3kb.
1 стр.
Краткое содержание курса Теория баз данных Модели данных и языки запросов Транзакции и согласованность
32.52kb.
1 стр.
«база данных access»
297.79kb.
1 стр.
В системы управления базами данных
52.4kb.
1 стр.
Занятие 1 Создание базы данных. Ввод и форматирование данных
275.48kb.
1 стр.
Знакомство с ms access. Создание базы данных. Ввод и форматирование данных
668.73kb.
6 стр.
Создание среды хранения объектов над иерархической субд
315.78kb.
1 стр.
Дисциплина "Базы данных" Пояснительная записка к курсовой работе Тема: "Библиотека"
510.35kb.
1 стр.
Методические указания к курсовой работе по дисциплине Базы данных  для направления 230700. 62 «Прикладная математика» подготовки бакалавра
522.91kb.
9 стр.
Варианты к заданию по созданию базы данных с использованием субд
118.69kb.
1 стр.
Практические занятия к теме №3 задача 1 Задание Создание базы данных
83.93kb.
1 стр.
Материал из Semantic Future
425.22kb.
2 стр.