Выполнение экзаменационного задания в дистанционном формате по дисциплине ОП.08 «Основы проектирования баз данных» для студентов специальности 09.02.07 «Информационные системы и программирование»

Автор: Хрущева Лариса Гавриловна

Организация: ГАПОУ «МЦК-КТИТС»

Населенный пункт: Республика Татарстан, г. Казань

Внедрение в педагогическую деятельность новых информационных технологий, связанных с использованием удаленных методов обучения позволило преподавателям достойно выдержать период пандемии. Дистанционное обучение используется и сейчас. Например, при проведении промежуточной аттестации по дисциплине «Основы проектирование баз данных» для студентов специальности 09.02.07 «Информационные системы и программирование» в качестве промежуточного контроля по этой дисциплине предлагается провести экзамен на платформе Moodle. Экзамен можно провести как в аудитории, так и удаленно. Результат выполнения задания загружается в систему Moodle в виде одного файла с расширением doc или docx, который должен содержать таблицы, схемы, скрины задания.

Для проведения экзамена необходимо наличие следующих программ: Visio, SQL-Server, Paint.

Экзаменационное задание проверяет компетенции, умения и знания, перечисленные в таблице 1

Таблица 1

Проверяемые компетенции, умения и знания

Код ПК, ОК

Умения:

Знания:

ОК 1, ОК 2, ОК 4, ОК 5, ОК 9, ОК 10,

ПК 11.1-11.6

- проектировать реляционную базу данных;

- использовать язык запросов для программного извлечения сведений из баз данных

- основ теории баз данных;

- моделей данных;

- особенностей реляционной модели и проектирование баз данных;

- изобразительных средств, используемых в ER- моделировании;

- основ реляционной алгебры;

- принципов проектирования баз данных;

- обеспечения непротиворечивости и целостности данных;

- средств проектирования структур баз данных;

- языка запросов SQL

 

В статье приведено типовое экзаменационное задание для студента и вариант его решения. Также даны описания предметных областей, которые можно использовать на экзамене.

Задание.

  1. Для предметной области составить ER-диаграмму, которая находится в 3-ей нормальной форме, используя программу VISIO

Описание предметной области Проекты клиентов

На предприятии в отделах работают сотрудники. Каждый отдел имеет номер и название. В каждом отделе работает несколько сотрудников, но каждый сотрудник может работать только в одном отделе. Каждый сотрудник имеет определенную должность. Сотрудник может работать только на одной должности, но определенную должность может иметь несколько сотрудников Сотрудник имеет табельный номер, фамилию, имя, отчество, оклад.

Также у предприятия есть клиенты, которые имеют код, наименование, адрес. Клиенты заключают с предприятием контракты на выполнение проектов, у которых есть номер, дата заключения, сумма. Один клиент может заказать выполнение нескольких проектов, но у каждого проекта может быть может быть только один клиент.

Каждый проект может выполнять несколько сотрудников, и один сотрудник может участвовать в нескольких проектах.

  1. Даталогическое проектирование. Составить набор таблиц специальной формы (таблица 2) на основании построенной ER-диаграммы.

Таблица 2

Таблица «_________»

Название сущности

Название

Идентификатор

Тип данных

Не пусто

Ограничение

  1. Создать файл базы данных в SQL-SERVER:

- создать все таблицы;

- задать все ключи;

- создать диаграмму базы данных

  1. Заполнить базу данных исходными данными
  2. Выполнить следующие запросы:
  • Определить табельные номера сотрудников, которые отработали в проектах больше 90 дней.
  • Найти сколько сотрудников работает в каждом проекте.
  • Найти клиентов, название которых начинается на букву С.
  • Сделать расчет начисленной заработной платы сотрудников и вывести об этом информацию в следующем виде: Фамилия, Оклад, Надбавка, Итого начислено.
  • Найти табельные номера сотрудников, которые не находятся на должностях с кодами 100101, 100202, 100501.
  • Найти максимальную выплату по каждому проекту.
  • Найти сумму всех выплат по проектам, которую получил каждый сотрудник.
  • Вывести номера всех контрактов, заключенных с сотрудником с табельным номеров 408.
  • На соединение двух таблиц. Вывести фамилии сотрудников, которые участвуют в проекте 302.
  • На соединение двух таблиц. Вывести номера всех контрактов, заключенных с сотрудником по фамилии Сухой.

 

Задание оценивается экзаменатором по следующим критериям:

Описание критерия

Баллы

1

Составлена ER-диаграмма

2

2

ER-диаграмма составлена правильно (за каждый невыполненный пункт -1 балл):

- указаны все сущности

- сущности названы правильно

- описаны все атрибуты

- первичные ключи указаны правильно

- внешние ключи указаны правильно

- связи указаны правильно

Мах 12

2

2

2

2

2

2

2

3

Набор таблиц для даталогического проектирования составлен

 

2

4

Набор таблиц для даталогического проектирования составлен (за каждый невыполненный пункт -1 балл)

- описаны все сущности

- есть название таблицы

- есть название атрибута

- есть название идентификатора

- есть тип данных

- указано обязательность заполнения

- указано ограничение

Мах 14

 

2

2

2

2

2

2

2

5

Создан файл базы данных в SQL-SERVER:

 

2

6

Файл базы данных в SQL-SERVER создан правильно:

- созданы все таблицы;

- заданы все ключи;

- диаграмма базы данных имеет стандартный вид

 

Мах 16

4

4

8

7

Таблицы заполнены данными (видно по скринам из запросов и таблицам с данными)

 

Мах 8

8

Запросы сделаны

 

4

9

Запросы выполнены и оформлены правильно (Словесное описание запроса (2 балл) и скрин запроса с результатом (2 балл) За каждый правильный запрос мах 4 балла)

Мах 40

 

ИТОГО БАЛЛОВ

100 баллов

 

Шкала перевода в рамках РСКА

Результат экзамена

(Баллы)

Результат экзамена (оценка)

 

От 50 %

2 (неудовлетворительно)

от 66 % до 51 %

3 (удовлетворительно)

от 83 % до 67%

4 (хорошо)

84 % и выше

5 (отлично)

Результат выполнения задания.

  1. ER-диаграмма

 

  1. Набор таблиц специальной формы (таблица 1) на основании построенной ER-диаграммы

Таблица Otdel ОТДЕЛ

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

№ отдела

Namber_otdel

Целый int

Да

2

Название отдела

Name_otdel

Текстовый

Varchar(40)

Нет

 

3

Телефон отдела

Phone

Текстовый char(12)

Нет

 

 

Таблица Position ДОЛЖНОСТЬ

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

Код должности

Kod_position

Целый int

 

Да

2

Название должности

Name_position

Текстовый

Nvarchar(20)

Нет

 

 

Таблица Klient КЛИЕНТ

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

Код клиента

Kod_klient

Целый int

 

Да

2

Название клиента

Name_klient

Текстовый

Nvarchar(50)

Нет

 

 

Таблица Project ПРОЕКТ

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

Номер проекта

Namber_Project

Целый int

 

Да

2

Название проекта

Name_Project

Текстовый

Nvarchar(50)

Нет

 

3

Стоимость проекта

Price_Project

Decimal(8,0)

Нет

 

4

Код клиента

Kod_klient

Целый int

 

Да

5

Начало проекта

Begin_Project

Дата

Нет

 

6

Конец проекта

End_Project

Дата

Нет

 

 

Таблица Employee СОТРУДНИК

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

Таб № сотрудника

Id_employee

Целый int

Да

2

Имя сотрудника

FName

Текстовый

Nvarchar(20)

Нет

 

3

Фамилия сотрудника

LName

Текстовый Nvarchar(50)

Нет

 

4

№ отдела

Namber_otdel

Целый int

Да

5

Код должности

Kod_position

Целый int

Да

6

Оклад

Oklad

Decimal(7,2)

Да

 

7

Надбавка

Nadbavka

Decimal(6,2)

Да

 

 

Таблица Project_execution ИСПОЛНЕНИЕ

Название

Идентификатор

Тип данных

Не пусто

Ограничение

1

№ договора

Id_contract

Целый int

Да

2

Таб № сотрудника

Id_employee

Целый int

Да

3

Номер проекта

Namber_Project

Целый int

 

Да

4

Количество дней

Quantity_day

Целый int

Нет

 

5

Сумма

Amount

Decimal(7,2)

Нет

 

 

  1. Создать файл базы данных в SQL-SERVER:

- создать все таблицы;

- задать все ключи;

- создать диаграмму базы данных

 

Диаграмма базы данных

  1. Исходные данные таблиц

Таблица Otdel

Namber_otdel

 

Name_otdel

Phone

1001

Экономический отдел

(843)2723508

1002

ИТ-отдел

(843)2733509

1003

Мастерская

(843)2733510

1004

Отдел маркетинга

(843)2713528

1005

Типография

(843)2733538

1006

Отдел проектирования

(843)2733548

 

Таблица Position

Kod_position

Name_position

100101

Экономист

100102

Бухгалтер

100201

Стажер

100202

Программист

100203

Тестировщик

100204

Админ. приложения

100301

Мастер

100302

Техник

100401

Маркетолог

100402

Снабженец

100501

Верстальщик

100502

Технический писатель

100601

Администратор БД

100602

Аналитик

100603

Проектировщик

100604

Руководитель проекта

 

Таблица Klient

Kod_klient

Name_klient

2001

АО «Спартак»

2002

ЗАО «ЗУК»

2003

ООО «РУМБА»

2004

ЧП «Сальца»

2005

ЗАО «Степ»

 

Таблица Project

Namber_Project

Name_Project

Price_Project

Kod_klient

Begin_Project

End_Project

301

Звезда Востока

100000

2001

01.02.2019

31.12.2020

302

Каламбур

350000

2002

12.01.2019

30.04.2019

303

Колледж

500000

2001

17.10.2018

31.12.2019

304

Затейник

200000

2003

01.01.2020

31.12.2021

305

Детские игры

300000

2002

20.12.2019

20.12.2021

306

Танцуй с нами

400000

2002

01.08.2019

31.05.2020

307

Бюджет семьи

200000

2004

01.10.2019

20.09.2021

308

Сидим дома

200000

2002

30.03.2018

20.09.2020

 

Таблица Employee

Id_employee

FName

LName

Namber_otdel

Kod_position

Oklad

Nadbavka

401

Иванова

Ксения

1001

100101

20000

NULL

402

Чумаков

Евгений

1001

100102

22000

2500

403

Красиков

Михаил

1002

100201

24000

3000

404

Крынкина

Аграфена

1002

100602

30000

2000

405

Султанов

Амир

1002

100202

30000

NULL

406

Троицкий

Игорь

1002

100203

20000

2000

407

Сухой

Олег

1002

100203

25000

3000

408

Зайцева

Татьяна

1002

100204

34000

NULL

409

Стрункин

Андрей

1003

100301

35000

2900

410

Лисицин

Петр

1003

100301

35000

2000

411

Отрепьев

Евдоким

1003

100602

26000

2000

412

Аннушкин

Евлампий

1003

100302

26000

2000

413

Браткин

Павел

1003

100302

20000

3000

414

Субботина

Анна

1004

100401

45000

2200

415

Павлов

Антон

1004

100402

40000

NULL

416

Семакин

Дмитрий

1005

100501

40000

3200

417

Зарубина

Ольга

1005

100501

35000

3500

 

 

 

 

 

 

 

418

Билан

Дмитрий

1005

100502

35000

2000

419

Захарова

Светлана

1006

100601

36000

2000

420

Примочкина

Евдокия

1006

100602

37000

3800

421

Савушкин

Айрат

1006

100603

42000

NULL

422

Брунов

Роман

1006

100603

41000

2000

423

Громушкин

Алексей

1006

100604

56000

5000

424

Грамушкина

Аглая

1006

100604

59000

6000

 

Таблица Project_execution

Id_contract

 

Id_employee

 

 

Namber_Project

Quantity_day

 

 

Amount

501

401

301

40

10000

502

403

302

20

80000

503

404

301

53

75000

504

401

303

17

12000

505

405

301

19

90000

506

406

306

23

45000

507

402

307

25

46000

508

405

302

27

50000

509

407

307

24

54000

510

409

308

18

60000

511

402

305

60

70000

512

408

302

98

73000

513

410

306

101

200000

514

423

308

78

110000

515

403

304

15

80000

516

408

307

38

50000

517

407

308

45

60000

518

423

305

67

300000

519

424

306

76

75000

520

411

306

89

65000

521

415

307

91

45000

522

413

301

41

26000

523

416

306

31

34000

524

413

304

14

19000

525

420

307

18

45000

526

415

303

45

60000

527

416

301

67

45000

 

5. Запросы

--1 Определить табельные номера сотрудников, которые отработали в проектах больше 90 дней

select Id_employee 'Табельный номер'

from Project_execution

where Quantity_day>90

--2 Найти сколько сотрудников работает в каждом проекте

select COUNT(Id_employee)'количество сотрудников ', Namber_Project 'номер проекта '

from Project_execution

group by Namber_Project

--3 Найти клиентов название которых начинается на букву с

select Name_klient 'название клиента' , Kod_klient 'код клиента'

from klient

where Name_klient like '%С%'

 

--4 Сделать расчет начисленной заработной платы сотрудников и вывести об этом информацию в следующем виде:Фамилия, Оклад ,Надбавка , Итого начислено

select FName 'Фамилия ' , Oklad 'Оклад' , Nadbavka 'Надбавка ', Oklad+Nadbavka 'Итого' from Employee

--5 Найти табельные номера сотрудников, которые не находятся на должностях с кодами 100101 , 100202 , 100501

select Id_employee 'Табельный номер '

from Employee

where Kod_position!=100101 and Kod_position!=100202 and Kod_position!=100501

--6 Найдите максимальную выплату по каждому проекту

select MAX(Amount)'Максимальная выплата' , Namber_Project 'Номер проекта'

from Project_execution

group by Namber_Project

--7 Найдите сумму всех выплат по проектам, которую получил каждый сотрудник

select SUM(Amount) 'Сумма' , id_employee 'Код сотрудника '

from Project_execution

group by Id_employee

--8 Вывести номера всех контрактов, заключенных с сотрудником с табельным номером 408

 

select Namber_Project 'Номер контракта'

from Project_execution

where Id_employee=408

--9 Вывести фамилии сотрудников, которые учувствуют в проекте 302

select FName 'Фамилия'

from Employee , Project_execution

where Project_execution.Id_employee=Employee.Id_employee and Namber_Project=302

--10 Вывести номера всех контрактов заключенных с сотрудником по фамилии Сухой

select Namber_Project 'Номер контракта'

from Employee , Project_execution

where Project_execution.Id_employee=Employee.Id_employee and [FName ]='Сухой'

Варианты для выполнения задания

 

Вариант 1.

Предметная область: Налоговая инспекция

В налоговой инспекции зарегистрированы предприятия с разными формами собственности и организационными структурами. Одна форма собственности и одна организационная структура может быть у разных предприятий. У предприятия может быть несколько учредителей (собственников). У каждого предприятия может быть несколько видов деятельности и один вид деятельности может быть у разных предприятий.

 

Вариант 2

Предметная область: Кинотеатр.

В кинотеатре есть несколько залов, в которых идут фильмы. Фильмы заказывают в кинокомпаниях. Одна компания может поставлять несколько фильмов. У фильмов могут быть разные возрастные категории и жанры. В кинотеатре есть расписание, в котором указано какие фильмы в каких залах идут и в какое время.

 

Вариант 3

Предметная область: Турагентство

В турагентстве работают менеджеры с туристами. Менеджеры продают путевки разным туристам в разные страны. Один менеджер работает с разными туристами. В стране прибытия для туриста забронирована гостиница. В одной стране может быть много гостиниц. Гостиницы имеют разные категории. До страны назначения можно добраться разным транспортом .

 

Вариант 4

Предметная область: Ремонт дорог

В городе существуют дороги, которые нуждаются в ремонте. Дороги имеют разный вид покрытия. Один вид покрытия может быть у разных дорог. Ремонтом дорог занимаются бригады, которые ремонтируют разные дороги. Для ремонта дорог требуется техника, которая принадлежит определенной бригаде. Для ремонта разных дорог требуется разный материал.

 

 

Вариант 5

Предметная область: Риэлтерское агентство

В риэлтерском агентстве работают риелторы по продаже недвижимости. Каждый риелтор может работать с несколькими владельцами недвижимости (продавец). У одного владельца может быть разная недвижимость. Недвижимость может быть разного типа (например: дом, квартира, участок). Также риелтор может работать с несколькими покупателями недвижимости и покупатель недвижимости может работать с несколькими риелторами.

 

Вариант 6

Предметная область: Производство 6

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

 

Вариант 7

Предметная область: Музей

В музее находится несколько залов, в которых выставлены разные картины. В одном зале выставлено насколько картин. Каждая картина имеет название, автора и исполнение (например: карандаш, масляные краски, акварель, гуашь и прочее). Один автор может написать много картин. Картины могут отправлять на выставки, о чем хранится информации в истории. Разные картины могут участвовать в разных выставках.

 

Вариант 8

Предметная область: Больница

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

 

Вариант 9

Предметная область: Готовые блюда

Столовая готовит разные блюда из разных продуктов. Продукты и готовые блюда имеют определенный срок годности (например: неделя, два дня, один час и прочее). Один и тот же срок годности может быть у разных продуктов и готовых блюд. Продукты поставляют поставщики по накладным. Один поставщик делает поставки по разным накладным. По одной накладной могут быть поставлены разные продукты. Один и тот же продукт могут поставлять по разным накладным.

 

Вариант 10

Предметная область: Библиотека

В библиотеке хранятся книги. Каждая книга хранится в определенной отделе (например: художественный, научный и прочее). Книги выдают читателям. На каждого читателя открывают абонемент, в который записывают какие книги выданы. Читателям могут выдать разные книги. Книги выдают сотрудники. Работа у сотрудников посменная. В разные смены могут работать разные сотрудники.

 

Вариант 11

Предметная область: Концертный зал

В концертном зале проводятся разные виды концертов (например: спектакль, джаз, балет и прочее). В одном концерте могут участвовать разные артисты. Одни и те же артисты могут участвовать в разных концертах. Все артисты имеют свое амплуа (например: певец, музыкант). Одно и то же амплуа может быть у разных артистов. На один концерт продают много билетов.

 

Вариант 12

Предметная область: Детский сад

В детском саду есть несколько групп, в которых находятся дети. Дети могут ходить в кружки. В разные кружки могут ходить разные дети. Каждый кружок проводит открытые мероприятия. Их может быть много. Кружки работают по расписанию, в котором указано какой кружок в какой день недели работает и в какое время.

 

В заключении хочется сказать, что проведение экзамена в таком формате положительно сказалось как на преподавателях, так и на студентах.

А именно, проверка экзаменационного задания по критериям, приведенным в таблице 1 позволило повысить объективность оценки, уменьшило время проверки.

Также в задание были включены и проверены все общие и профессиональные компетенции. Студенты продемонстрировали знания и умения полученные ими в ходе изучения дисциплины «Основы проектирования баз данных» и смогут успешно использовать их при изучении профессиональных модулей.

Загрузка результата выполнения задания в систему Moodle позволило студентам за короткий срок продемонстрировать навыки проектной работы и использовать их в дальнейшем при написании дипломной работы.

Список использованной литературы

  1. Базы данных. В 2-х кн. Кн. 2. Распределенные и удаленные базы данных: Учебник / В.П. Агальцов. - М.: ИД ФОРУМ: НИЦ Инфра-М, 2017. - 272 с. Режим доступа: http://znanium.com/catalog.php?bookinfo=372740
  1. Голицина О.Л. Основы проектирования баз данных: учебное пособие для СПО. – М.: ФОРУМ, 2018.-416 с.
Опубликовано: 23.03.2021