Практическое задание по базе данных, Росдистант

Практическое задание 2 Тема 2.1. Методы проектирования баз данных Задание На основе заданного описания предметной области разработать структуру реляционной базы данных, выполнив следующую последовательность действий: 1. Выполнив анализ описания предметной области и заданного перечня атрибутов, для каждой сущности выбрать набор определяющих ее атрибутов. 2. Создать первичные ключи для каждой сущности. 3. Представить схему связей между сущностями. 4. Перейти к представлению сущностей в виде реляционных таблиц: • определить состав полей таблиц базы данных; • в каждой таблице определить первичный ключ; • определить тип связей между таблицами; • определить свойства полей в таблицах. Описание предметной области Магазин закупает товар по некоторой закупочной цене и продает его по цене продажи, которая, как правило, выше закупочной. Цена продажи может изменяться в процессе реализации товара. При продаже товара фиксируется наименование товара, продавец, дата продажи, цена продажи, количество проданных единиц товара. В магазине работают несколько продавцов. Каждый продавец получает комиссионное вознаграждение за каждый проданный товар, которое равно произведению цены продажи товара на количество проданных единиц товара и на процент вознаграждения, устанавливаемый индивидуально каждому продавцу. Этот процент не зависит от характеристик товара, цены и даты продажи, а также от количества проданных товаров. В проектируемой базе данных должны храниться данные о товарах, продавцах и продаже товаров. Предлагаемый набор сущностей проектируемой базы данных: 1. ТОВАР. 2. ПРОДАВЕЦ. 3. ПРОДАЖА. Минимальный перечень атрибутов: 1. Наименование (товара). 2. Единица измерения (товара). 3. Цена закупки (единицы товара). 4. Цена продажи (единицы товара). 5. Дата продажи. 6. Количество (проданных единиц товара). 7. ФИО (продавца). 8. Процент комиссионных (продавца). Задание оформляется в соответствии с бланком выполнения задания и сохраняется в файле «Задание_2_Фамилия.doc» или «Задание_2_Фамилия.docx». Образец выполнения задания Внимание! Это задание демонстрирует ход рассуждений, которые могут быть проведены при выполнении основного задания, и на проверку преподавателю НЕ предоставляется. Требуется спроектировать базу данных для хранения информации о выполнении ремонтных работ сотрудниками фирмы. Описание предметной области Фирма выполняет ремонт компьютеров. Сотрудники фирмы (исполнители заказов) выполняют определенный набор операций (работ). Стоимость каждого вида работы устанавливается заранее. Каждый исполнитель получает некоторый процент вознаграждения от стоимости выполнения работы. Этот процент устанавливается персонально каждому исполнителю и не зависит от вида выполняемой работы. При оформлении заказа фиксируется дата исполнения работы, вид работы, исполнитель работы. Анализ описания предметной области позволяет выделить минимально необходимый набор данных (атрибутов), которые должны храниться в проектируемой базе данных: 1. Наименование (работы). 2. Стоимость (работы). 3. Дата исполнения (работы). 4. Фамилия (исполнителя). 5. Имя (исполнителя). 6. Отчество (исполнителя). 7. Процент вознаграждения (исполнителя). Анализ описания предметной области позволяет определить два базовых информационных объекта (сущности): ИСПОЛНИТЕЛЬ (Фамилия, Имя, Отчество, Процент вознаграждения) и РАБОТА (Наименование, Стоимость). При определении сущности нужно из числа атрибутов отнести к данной сущности только те, которые характеризуют лишь ее и не зависят от других сущностей. Так, атрибут «Фамилия» характеризует исполнителя и не зависит от выполняемой работы. Аналогично атрибут «Стоимость» характеризует работу и не зависит от исполнителя. Анализ показывает, что ни один из выбранных для сущности «ИСПОЛНИТЕЛЬ» атрибутов не определяет однозначно каждый ее экземпляр и может содержать повторяющиеся значения, то есть не может играть роль первичного ключа. Введем новый атрибут «Код исполнителя», значения в котором будут уникальными для каждого исполнителя. Этот атрибут будет первичным ключом для сущности «ИСПОЛНИТЕЛЬ». С этой же целью для сущности «РАБОТА» введем первичный ключ «Код работы». Полученные сущности схематически показаны на рис. 2.1. Рис. 2.1. Сущности «ИСПОЛНИТЕЛЬ» и «РАБОТА» Заметим, что атрибут «Дата исполнения» из первоначального набора атрибутов не отнесен ни к одной из сущностей. Определимся с типом связи между сущностями. Один исполнитель может выполнять различные виды работ. Один и тот же вид работы может быть выполнен разными исполнителями. Очевидно, что между сущностями «ИСПОЛНИТЕЛЬ» и «РАБОТА» должна быть установлена связь типа «многие-ко-многим». Для организации такой связи потребуется новая сущность, в которую первичные ключи из «РАБОТА» и «ИСПОЛНИТЕЛЬ» («Код работы» и «Код исполнителя» соответственно) войдут как внешние ключи. Такой сущностью в нашем случае может быть «ЗАКАЗ». В число ее атрибутов войдет также «Дата исполнения» из первоначального набора атрибутов. Определим первичный ключ сущности «ЗАКАЗ», введя новый атрибут «Код заказа». Каждый экземпляр этой сущности будет соответствовать факту оформлении заказа на выполнение какой-либо работы тем или иным исполнителем. Окончательно получим следующее описание сущностей через наборы атрибутов: 1. ИСПОЛНИТЕЛЬ (Код исполнителя, Фамилия, Имя, Отчество, Процент вознаграждения) 2. РАБОТА (Код работы, Наименование, Стоимость) 3. ЗАКАЗ (Код заказа, Код работы, Код исполнителя, Дата исполнения) Установим связи между сущностями посредством совпадающих атрибутов (первичный ключ одной связывается с внешним ключом другой). Тип связи между сущностями «ИСПОЛНИТЕЛЬ» и «ЗАКАЗ» – «один-ко-многим», между сущностями «РАБОТА» и «ЗАКАЗ» – также «один-ко-многим», между сущностями «ИСПОЛНИТЕЛЬ» и «РАБОТА» – «многие-ко-многим». В первой паре для связи используется атрибут «Код исполнителя», во второй – атрибут «Код работы» (рис. 2.2). Рис. 2.2. Связи между сущностями проектируемой базы данных Каждой сущности поставим в соответствие реляционную таблицу. Получим таблицы «ИСПОЛНИТЕЛИ», «РАБОТЫ» и «ЗАКАЗЫ». Количество и наименования полей (столбцов) в каждой таблице будет совпадать с количеством и наименованием атрибутов соответствующей сущности. Первичные ключи таблиц выделены жирным шрифтом (рис. 2.3). Связи между таблицами будут соответствовать схеме связей между соответствующими сущностями. Рис. 2.3. Схема данных (структура таблиц и связей между ними) базы данных Таблица «ИСПОЛНИТЕЛИ» будет хранить записи (строки) вида: Код исполнителя Фамилия Имя Отчество Процент вознаграждения 1 Коробов Илья Игоревич 10 2 Зотова Анна Сергеевна 15 Таблица «РАБОТЫ» будет хранить записи (строки) вида: Код работы Наименование Стоимость 1 Установка видеокарты 200 2 Замена блока питания 250 Таблица «ЗАКАЗЫ» будет хранить записи (строки) вида: Код заказа Код работы Код исполнителя Дата исполнения 1 1 2 04.05.2017 2 2 4 04.05.2017 3 2 1 05.05.2017 4 3 2 06.05.2017 Определим свойства полей для каждой таблицы. Тип данных в поле выбирается исходя из характера данных, которые предполагается в нем хранить. Будем использовать типы данных, принятые в базах данных Microsoft Access. Необходимо учесть, что поле, являющееся первичным ключом таблицы, не может содержать значений, повторяющихся в двух или более записях таблицы. Типы данных для полей таблицы «ИСПОЛНИТЕЛИ»: Имя поля в таблице Тип данных Код исполнителя числовой целый (значения не повторяются) Фамилия текстовый Имя текстовый Отчество текстовый Процент числовой Типы данных для полей таблицы «РАБОТЫ»: Имя поля в таблице Тип данных Код работы числовой целый (значения не повторяются) Наименование текстовый Стоимость денежный Типы данных для полей таблицы «ЗАКАЗЫ»: Имя поля в таблице Тип данных Код заказа числовой целый (значения не повторяются) Код работы числовой Код исполнителя числовой Дата исполнения дата/время Бланк выполнения практического задания 2 Представленные в базе данных информационные объекты (сущности): 1. ТОВАР (Атрибут 1, Атрибут 2, …). 2. ПРОДАВЕЦ (Атрибут 1, Атрибут 2, …). 3. ПРОДАЖА (Атрибут 1, Атрибут 2, …). Схема таблиц проектируемой базы данных: ТОВАРЫ ПРОДАЖИ ПРОДАВЦЫ Код товара Код продажи Код продавца Тип связи между таблицами «ТОВАРЫ» и «ПРОДАЖИ» – «…-ко-…». Связь установлена по полю «…». Тип связи между таблицами «ПРОДАВЦЫ» и «ПРОДАЖИ» – «…-ко-…». Связь установлена по полю «…». Тип связи между таблицами «ТОВАРЫ» и «ПРОДАВЦЫ» – «…-ко-…». Таблица «ТОВАРЫ» будет хранить записи вида: Код товара Таблица «ПРОДАВЦЫ» будет хранить записи вида: Код продавца Таблица «ПРОДАЖИ» будет хранить записи вида: Код продажи Типы данных для полей таблицы «ТОВАРЫ»: Имя поля в таблице Тип данных Код товара числовой целый (значения не повторяются Типы данных для полей таблицы «ПРОДАВЦЫ»: Имя поля в таблице Тип данных Код продавца числовой целый (значения не повторяются) Типы данных для полей таблицы «ПРОДАЖИ»: Имя поля в таблице Тип данных Код продажи числовой целый (значения не повторяются)