метод лабы. Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования
 Скачать 1.67 Mb.
|
|
Министерство науки и высшего образования Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования «Алтайский государственный технический университет им. И.И. Ползунова» Факультет информационных технологий Кафедра информатики, вычислительной техники и информационной безопасности Качесова Л.Ю. Методические указания к выполнению лабораторных работ по дисциплине «Базы данных» Барнаул 2020 2 УДК 004.42 Качесова Л.Ю. Методические указания к выполнению лабо- раторных работ по дисциплине «Базы данных»/АлтГТУ им. И.И. Пол- зунова. – Барнаул, 2020. – 51c. Методические указания предназначены для студентов, обу- чающихся по направлению: 09.03.01 — Информатика и вычислитель- ная техника . Структура и содержание методических указаний соот- ветствуют образовательному стандарту высшего образования по ука- занному направлению подготовки. Рассмотрены и одобрены на заседании кафедры «ИВТ и ИБ» Протокол № 1 от 30.08.2020 г. 3 Содержание Порядок выполнения лабораторных работ…………………………… 4 Лабораторная работа 1. ………………………………………….……. 5 Лабораторная работа 2. .................………………………………….… 15 Лабораторная работа 3. ……………………..…………………………. 20 Лабораторная работа 4. ……………………………………….……….. 31 Лабораторная работа 5……………………………………………….... Лабораторная работа 6……………………………………………….... 38 45 Лабораторная работа 7 ……….…….………………………………….. 50 Список литературы……………………………………………………... 51 4 Порядок выполнения лабораторных работ 1. Познакомиться с темой и целью лабораторной работы. 2. Изучить необходимые к выполнению лабораторной работы теоретиче- ские сведения и примеры. 3. Познакомиться с заданием к лабораторной работе. 4. Выполнить лабораторную работу в соответствии с заданием. 5. Оформить отчет. Оборудование, технические средства, инструмент Для выполнения лабораторных работ на компьютерах должно быть ус- тановлено следующее программное обеспечение: 1. Операционная система Windows. 2. Toad DataModeler Freeware. 3. СУБД MySQL (MariaDB) или PostgreSQL 4. GUI-клиенты для работы с MySQL (MariaDB) или PostgreSQL, на- пример EMS SQL Manager for MySQL или EMS SQL Manager for PostgreSQL. 5. ODBC, JDBC, NET коннекторы для доступа к базам данных MySQL (MariaDB) или PostgreSQL. 6. Среды программирования на языках C++ (Qt), С# и Java. 5 Лабораторная работа № 1 Построение информационно-логической модели данных Цель работы: Научиться создавать информационно-логическую модель данных предметной области в Toad Data Modeler . Toad DataModeler Freeware: http://www.toadworld.com/products/downloads?type=Freeware&download=toad- data-modeler Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [1]. 1.1 Задание Создать в Toad Data Modeler информационно-логическую модель дан- ных предметной области индивидуального варианта. 1.2 Отчет по лабораторной работе должен содержать: - титульный лист; - цель работы; - задание; - описание предметной области индивидуального варианта; - построенную в Toad Data Modeler информационно-логическую модель данных предметной области индивидуального варианта с описанием сущностей и связей между ними. 1.3 Пример построения информационно-логической модели данных предметной области «Поставка продукции на склад» Некоторое предприятие приобретает продукцию у различных постав- щиков. Поставщиками могут быть как юридические лица, так и физические лица – субъекты предпринимательской деятельности. Приобретение продук- ции осуществляется партиями и оформляется в виде договоров на поставку. Каждый договор на поставку продукции имеет уникальный номер и может быть заключен только с одним поставщиком. Основанием для поставки яв- ляется какой-либо документ (предварительный заказ, счет-фактура и т.п.). В документах по каждому договору для каждого вида продукции указываются: наименование, размер поставленной партии и цена (в гр.). Поставленная продукция приходуется на склад с целью последующей реализации путем продаж, отпуска на реализацию и т.п. Анализ бизнес-процессов позволил выделить следующие информаци- онные массивы, которые могут входить в состав базы данных. 6 1. Сведения о продукции Включают в себя информацию о названии продукции, идентификаторе, который однозначно определяет каждую конкретную продукцию (код, ар- тикул и т.п.), единицу измерения продукции (штука, ящик, кг и т.п.) и т.д. Продукция объединена в некоторые группы (товарные группы) – например, продукты питания, парфюмерия, бытовая химия и т.д. Предполагается, что каждая продукция может входить только в одну товарную группу. 2. Сведения о поставщиках продукции Включают в себя информацию о субъектах предпринимательской дея- тельности, работающих на рынке и предлагающих продукцию, в приобре- тении которой заинтересовано рассматриваемое предприятия. Поставщи- ками могут быть как юридические лица, так и физические лица. К сведени- ям о поставщиках относятся такие данные, как название субъекта предпри- нимательской деятельности, индивидуальный налоговый номер, номер свидетельства плательщика НДС (для юридического лица); фамилия, имя, отчество, номер свидетельства о регистрации (для физического лица); ад- рес местонахождения, контактный телефон (для физического и юридиче- ского лица) и т.д. 3. Сведения о ценах на продукцию, предлагаемую различными постав- щиками Одна и та же продукция может предлагаться на рынке различными по- ставщиками, причем каждый поставщик может предлагать продукцию по разным ценам (розничной, мелкооптовой, оптовой и т.д.) в зависимости от объема закупки, условий договора и т.д. 4. Сведения о поставках продукции Каждая поставка осуществляется на основании договора на поставку, который заключается между поставщиком и предприятием. Для каждой по- ставки известна следующая информация: поставщик, дата поставки, общая сумма поставки, данные о поставленной продукции. Данные о поставлен- ной продукции включают в себя (по каждой продукции): наименование продукции, количество единиц, цену за единицу. Цена, по которой постав- ляется продукция, может отличаться от стандартного прайс-листа постав- щика (для конкретного покупателя могут действовать специальные скидки, цена на отдельные виды продукции может назначаться индивидуально и т.д.) На основе анализа перечисленных информационных массивов можно выделить следующие сущности: - Товарные группы (Код товарной группы, Название товарной группы); - Единицы измерения продукции (Код единицы измерения, Название единицы измерения); - Продукция (Код продукции, Название продукции); - Виды цен на продукцию (Код вида цены, Название вида цены); 7 - Поставщики (Код поставщика, Название поставщика (для юридиче- ского лица), Индивидуальный налоговый номер (для юридического лица), Номер свидетельства плательщика НДС (для юридического лица), Фами- лия, имя, отчество (для физического лица), Номер свидетельства о регист- рации (для физического лица), Адрес, Контактный телефон); - Цены на рынке (Значение цены, Условие предоставления цены). - Договоры на поставку продукции (Код договора, Дата поставки, Ком- ментарий (некоторая дополнительная информация о поставке); - Поставленная продукция (Поставленное количество, Цена за единицу). Создание логической модели Для создания логической модели в Toad Data Modeler нужно выбрать в меню File/New Model/Logical Data Model/Logical Model и ввести имя логи- ческой модели и нажать кнопку OK. Логическую модель будем строит ис- пользуя синтаксис IDEF1X (устанавливается в меню View/Notation). Для из- менения цветности модели можно воспользоваться меню Settings/Options- Logical Model. Установим на вкладке General: Brush Color – White; Pen Color – Black, а на вкладке Entity: Optional Attribute – Black. Создание сущностей и атрибутов Рассмотрим последовательность действий при создании сущности и ее атрибутов на примере сущности «Товарные группы»: 1. щелчком мыши нажать кнопку «Entity» на панели инструментов; 2. перенести курсор к тому месту экрана, где необходимо разместить сущность и щелкнуть левой кнопкой мыши. На экране появится изображение сущности с произвольным именем; 3. изменить имя сущности на требуемое, для чего щелкнуть по изобра- жению сущности правой кнопкой мыши и выбрать пункт «Edit …». В поя- вившемся окне «Entity Properties» на вкладке «General» ввести новое назва- ние сущности (Caption) «Товарные_группы» (без кавычек) и имя будущей таблицы базы данных ( Name) «Tovarnie_gruppi» (без кавычек) и нажать кнопку «ОК»; 4. ввести атрибуты сущности, для чего щелкнуть по изображению сущности правой кнопкой мыши и выбрать пункт «Edit …». В появившемся окне «Entity Properties» на вкладке «Attributes» щелкнуть мышью по кнопке «Add» и в появившемся окне ввести имя атрибута (Caption) «Код_товарной_группы» (без кавычек) и имя будущего столбца реляционной таблицы (Name) «Kod_tov_gr» (без кавычек). В разделе окна «Data Type» установить тип «Number» и нажать кнопку «ОК». Аналогично ввести второй атрибут – «Название_товарной_группы». Для него в «Data Type» выбрать тип «Text»; 8 5. установить первичный ключ для данной сущности. Для этого вы- брать атрибут «Код_товарной_группы» и щелкнуть напротив в столбце «Ident.». 6. щелкнуть в окне «Entity Properties» по кнопке «ОК». Сущность «То- варные_группы» создана (рис. 1.1). Рисунок 1.1 – Сущность «Товарные группы» Остальные сущности, которые нужно создать на данном этапе, создают- ся аналогично. Данные о создаваемых сущностях и атрибутах приведены в таблице 1. В результате создания сущностей и их размещения на экране, внешний вид логической модели соответствует виду, приведенному на рис. 1.2. Сохранение модели Чтобы сохранить созданную модель, нужно щелкнуть мышью по кноп- ке «Save model as» в панели инструментов и ввести имя файла. Создание связей Рассмотрим последовательность действий при создании связи между сущностями на примере создания не идентифицирующей связи между сущностями «Продукция» и «Товарные группы»: 1) щелкнуть мышью по кнопке «Non-identifying relationship» в панели инструментов; 9 2) щелкнуть мышью сначала по сущности «Товарные группы», а затем по сущности «Продукция». В результате между сущностями появится не идентифицирующая связь (рис. 1.3). Аналогично можно создать связи между созданными ранее сущностями. Перечень связей между сущностями и типы связей приведены в таблице 2. Таблица 1 Сущность Атрибут Ключ Data Type Единицы_изме- рения Код_единицы_измерения PK Number Название_единицы_изме- рения Character(256) Продукция Код_продукции PK Number Название_продукции Character(256) Поставщики Код_поставщика PK Number Адрес Character(256) Телефон Character(256) Виды_цен Код_вида_цены PK Number Название_вида_цены Character(256) Договоры Код_договора PK Number Дата_поставки Date&Time Комментарий Character(256) Поставленная_про- дукция Количество Number Цена_за_единицу Number Юр_лица Название Character(256) Налоговый_номер Character(256) Номер_свид_НДС Character(256) Физ_лица Фамилия Character(256) Имя Character(256) Отчество Character(256) Номер_свидетельства Character(256) 10 Рисунок 1.2 – Сущности логической модели Рисунок 1.3 – Не идентифицирующая связь между сущностями «Продукция» и «Товарные группы» Таблица 2 Главная cущность Подчиненная cущность Тип связи Кнопка Товар- ные_группы Продукция Один ко многим. Не идентифици- Non- identifying 11 рующая relationship Едини- цы_измерения Продукция Один ко многим. Не идентифици- рующая Non- identifying relationship Поставщики Договоры Один ко многим. Не идентифици- рующая Non- identifying relationship Продукция Поставлен- ная_продукция Один ко многим. Идентифицирую- щая Identifying relationship Договоры Поставлен- ная_продукция Один ко многим. Идентифицирую- щая Identifying relationship В результате создания связей разрабатываемая диаграмма должна при- нять вид, аналогичный рис. 1.4. Рисунок 1.4 – Сущности и связи логической модели Создание категориальных связей Иерархия наследования (или иерархия категорий) представляет собой особый тип соединения сущностей, которые разделяют общие характери- стики. В рассматриваемом примере это относится к сущности «Поставщи- 12 ки». Поскольку поставщиками могут быть как юридические, так и физиче- ские лица, хранить информацию о поставщиках–физических лицах и по- ставщиках–юридических лицах с помощью одной сущности нецелесооб- разно. В связи с этим ранее помимо сущности «Поставщики» были созданы сущности «Юр_лица» и «Физ_лица». Для создания иерархия наследования, в которую войдут сущности «По- ставщики», «Юр_лица» и «Физ_лица», необходимо: 1. щелкнуть мышью по кнопке «Inheritance» в панели инструментов; 2. щелкнуть мышью сначала по сущности «Поставщики», а затем по сущности «Юр_лица». В результате между сущностями появится категори- альная связь (рис. 1.5); 3. щелкнуть мышью по кнопке «Inheritance» в панели инструментов; 4. щелкнуть мышью сначала по категориальной связи (по символу ка- тегориальной связи), а затем по сущности «Физ_лица». В результате между сущностями появится категориальная связь (рис. 1.5). 5. Для установки полной категории редактируем категориальную связь (щелкнуть по изображению категориальной связи правой кнопкой мыши и выбрать пункт «Edit …»). В появившемся окне «Inheritance Properties» уста- навливаем флажок Complete. Рисунок 1.5 – Сущности и связи логической модели Создание между сущностями связи типа «многие – ко - многим» На- личие связи «многие – ко - многим» между сущностями «Поставщики» и «Продукция» показывает, что каждый поставщик может предлагать на рынке различную продукцию, и, наоборот, каждая продукция может поставляться 13 разными поставщиками. Однако наличие такой связи не дает возможность указать, по какой цене продукция предлагается поставщиками. В связи с этим необходимы сущность «Цены_на_рынке» и «Виды цен» (рис. 1.6). Корректировка свойств связей В процессе разработки модели данных могут возникнуть ситуации, тре- бующие корректировки свойств ранее созданных связей. Это, в частности, может быть связано с тем, что свойства связей, устанавливаемые по умолча- нию, не соответствуют требованиям, предъявляемым к проектируемой базе данных. Рассмотрим пример корректировки свойств связи, используя связь меж- ду сущностями “Продукция” и “Товарные группы”. Эта связь показывает, что каждая продукция обязательно входит только в одну товарную группу (установлено свойство mandatory у сущности “Товарные группы”, рис. 1.7). Рисунок 1.6 – Сущности и связи информационно - логической модели А также связь показывает, что к каждой товарной группе относится ноль, одна или более продукция (не установлено свойство mandatory у сущ- ности “Продукция”, рис. 1.7). То есть могут существовать товарные группы, не содержащие продукции. 14 Рисунок 1.7 – Свойства связи Допустим, что предметная область требует, чтобы к товарной группе относилась хотя бы одна продукция. Для этого выделяем связь, в контекст- ном меню выбираем команду Edit и устанавливаем у сущности “Продук- ция” свойство mandatory (рис. 1.8). Изображение связи в модели изменить- ся (рис. 1.9). Рисунок 1.8 – Свойства связи 15 Рисунок 1.9 – Сущности и связи информационно - логической модели Лабораторная работа № 2 Проектирование реляционной базы данных Цель работы: Научиться создавать в Toad Data Modeler реляцион- ную схему базы данных на основе информационно-логической модели данных предметной области. Научиться формулировать правила под- держки ссылочной целостности. Теоретический материал, необходимый для выполнения работы приведен в слайдах лекций и литературе [1]. 2.1 Задание На основе информационно-логической модели данных предметной об- ласти индивидуального варианта получить в Toad Data Modeler реляционную схему базы данных для СУБД MySQL или PostgreSQL. Для каждой связи, исходя из требований предметной области, сформулировать правила под- держки ссылочной целостности. Обозначить на реляционной схеме базы данных правила поддержки ссылочной целостности. 2.2 Отчет по лабораторной работе должен содержать: - титульный лист; 16 - цель работы; - задание; - реляционную схему базы данных, полученную в Toad Data Modeler на основе информационно-логической модели данных предметной об- ласти индивидуального варианта. - описание правил поддержки ссылочной целостности. 2.3 Пример создания реляционной схемы базы данных. В Toad Data Modeler можно на основе логической модели данных полу- чить реляционную схему базы данных для выбранной СУБД, которую в Toad Data Modeler принято называть физической моделью данных. Преобразуем информационно-логическую модель данных предметной области «Поставка продукции на склад» в реляционную схему базы данных для СУБД MySQL. Для этого в Toad Data Modeler необходимо в меню Model выбрать Convert Model -> Simple Conversion. Полученную физическую модель данных демонстрирует рисунок 2.1. Обратите внимание на наличие внешних ключей (FK), которые используют- ся в реляционной модели данных для организации связей между таблицами. В полученной реляционной схеме базы данных была выполнена коррек- тировка связей между таблицей «Поставщики» и таблицами «Юр_лица» и «Физ_лица» (рис. 2.2). Рисунок 2.1 – Реляционная схема базы данных 17 Рисунок 2.2 – Корректировка связей между таблицами «Поставщики» и «Юр_лица» (см. Cardinality). Сформулируем для связей правила поддержки ссылочной целостности. Рассмотрим связь между таблицами «Поставщики» и «Договоры». Встав- ка строк в таблицу «Поставщики» всегда разрешена (стандартное правило поддержки ссылочной целостности), поскольку могут существовать постав- щики еще не заключившие договоры. Удаление из таблицы «Поставщики» ограничено (Restrict), если за поставщиком числится какой-то договор, то запись о поставщике удалять нельзя, поскольку каждый договор должен принадлежать какому-то поставщику (стандартное правило поддержки ссы- лочной целостности). Когда меняется код поставщика в таблице «Поставщи- ки», нужно распространить это изменение на одноименный столбец в табли- це «Договоры», производя каскадное (Cascade) обновление. Рисунок 2.3 де- монстрирует установку в Toad Data Modeler правил Restrict и Cascade со стороны таблицы «Поставщики» в рассматриваемой связи. Со стороны таб- лицы «Договоры» действуют стандартные правила поддержки ссылочной целостности (удаление договора разрешено; запрещено добавление договора для не существующего поставщика; запрещено обновлять «Номер поставщи- ка» (внешний ключ) номером не существующего поставщика). Аналогичные правила поддержки ссылочной целостности можно сфор- мулировать и для связи между таблицами «Единицы_измерения» и «Про- дукция». 18 Рисунок 2.3 – Правила поддержки ссылочной целостности для связи меж- ду таблицами «Поставщики» и «Договоры» (см. Referential Integrity). Рассмотрим связь между таблицами: «Товарные_группы» и «Продукция». Здесь процедуры обеспечения ссылочной целостности более сложны, так как в этой связи имеется обязательный потомок (Товарная группа должна содер- жать хотя бы одну продукцию). Для таблицы «Товарные_группы» действуют следующие правила под- держки ссылочной целостности: Delete Restrict (если к товарной группе при- надлежит какая-либо продукция, то запись об этой товарной группе удалять нельзя – стандартное правило); Update Cascade (каскадное обновление номе- ра товарной группы); Insert Cascade (при добавлении новой товарной группы нужно для неё добавить продукцию в таблицу «Продукция»). Правила Delete Restrict и Update Cascade можно установить в Toad Data Modeller. Правило Insert Cascade нужно программировать в СУБД, например, MySQL с исполь- зованием триггеров. Для таблицы «Продукция» действуют следующие правила поддержки ссылочной целостности: запрещено добавление продукции для не сущест- вующей товарной группы (стандартное правило); если удаляемая или обнов- ляемая продукция (обновляется код товарной группы) является единствен- ной в товарной группе, то удалять ее и обновлять для неё код товарной груп- пы запрещено (эти правила нужно программировать в СУБД, например, MySQL с использованием триггеров). 19 Рассмотрим связи между таблицами: «Поставщики» и «Цены_на_рынке», «Виды_цен» и «Цены_на_рынке», «Продукция» и «Цены_на_рынке», «Про- дукция» и «Поставленная_продукция», «Договоры» и «Поставлен- ная_продукция». Со стороны родительских таблиц (т.е. таблиц, которые яв- ляются источниками связи) действуют правила каскадного обновления и удаления (Cascade). Добавление строк в эти таблицы разрешено (стандартное правило), так как эти таблицы не имеют обязательных потомков. Со стороны дочерних таблиц (т.е. таблиц, которые являются приемниками связи) дейст- вуют стандартные правила поддержки ссылочной целостности (Insert Restrict; Update Restrict; удаление разрешено). Рассмотрим связи между таблицами: «Поставщики» и «Юр_лица», «По- ставщики» и «Физ_лица». Обновление и удаление строк в таблице «Постав- щики» должны инициировать соответственно каскадное обновление и уда- ление в таблицах «Юр_лица» и «Физ_лица». Это имеет смысл, поскольку если меняется Код_поставщика, данное изменение должно распространяться и на все категории этого поставщика. Аналогичным образом, если поставщик удаляется из базы данных, все категории этого поставщика должны быть удалены. При добавлении новой строки в таблицу «Поставщики» необходи- мо вставить строку в одну из таблиц «Юр_лица» и «Физ_лица» (программи- руется триггером). Обновление атрибута Код_поставщика в таблицах «Юр_лица» и «Физ_лица» должно быть запрещено. Операции удаления строк и добавления строк для таблиц «Юр_лица» и «Физ_лица» должны быть запрещены. Эти правила должны быть поддержаны триггерами. Реляционная схема базы данных с нестандартными правилами обеспече- ния ссылочной целостности показана на рисунке 2.4. Рисунок 2.4 – Реляционная схема базы данных 20 |