лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
11. База данных для обслуживания склада База данных должна обеспечить автоматизацию складского учета. В ней должны содержаться следующие данные: информация о «единицах хранения» – номер ордера, дата, код поставщика, балансный счет, код сопроводительного документа по справочнику документов, номер сопроводительного Документа, код материала по справочнику материалов, счет материала, код единицы измерения, количество пришедшего материала, цена единицы измерения; информация о хранящихся на складе материалах (справочник материалов – код класса материала, код группы материала, наименование материала); информация о единицах измерения конкретных видов материалов – код материала, единица измерения (метры, килограммы, литры и т.д.); информация о поставщиках материалов – код поставщика, его наименование, ИНН, юридический адрес (индекс, город, улица, дом), адрес банка (индекс, город, улица, дом), номер банковского счета. Помимо SQL-запросов для создания таблиц базы данных, разработать пакет, состоящий из процедур и функций, позволяющий: посчитать количество поставщиков данного материала; предоставить возможность добавления единицы хранения с указанием всех реквизитов; вывести список поставщиков с указанием всех реквизитов данного материала на склад; вывести сведения о самых крупных поставщиках по данному виду материала; подсчитать общее количество данного материала по всем поставщикам; вывести информацию о материалах, количество которых минимально (меньше заданного), и о поставщиках, у которых можно его заказать; для указанного адреса банка посчитать количество поставщиков склада, пользующихся услугами этого банка; по заданному поставщику определить его банковские реквизиты. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа осуществить при помощи соответствующих триггеров. 12. База данных фирмы Фирма отказалась от приобретения некоторых товаров у своих поставщиков, решив самостоятельно наладить их производство. С этой целью она организовала сеть специализированных цехов, каждый из которых принимает определенное участие в технологическом процессе. Каждому виду выпускаемой продукции присваивается, как обычно, свой шифр товара, под которым он значится в файле товарных 96 запасов. Этот же номер служит и шифром продукта. В записи с этим шифром указывается, когда была изготовлена последняя партия этого продукта, какова ее стоимость, сколько операций потребовалось. Операцией считается законченная часть процесса производства, которая целиком выполняется силами одного цеха в соответствии с техническими требованиями, перечисленными на отдельном чертеже. Для каждого продукта и для каждой операции в базе данных фирмы заведена запись, содержащая описание операции, ее среднюю продолжительность и номер чертежа, по которому можно отыскать требуемый чертеж. Кроме того, указывается номер цеха, обычно производящего данную операцию. В запись, связанную с конкретной операцией, заносится требуемое количество расходуемых материалов, а также присвоенные им шифры товара. Расходуемыми называют такие материалы, как, например, электрический кабель, который нельзя использовать повторно. Когда, готовясь к выполнению операции, расходуемый материал забирают со склада, регистрируется фактически выданное количество соответствующий шифр товара; номер служащего, ответственного за выдачу; дата и время выдачи; номер операции и номер наряда на проведение работ, который будет обсуждаться ниже. Реально затраченное количество материала может не совпадать с расчетным, из-за того, например, что часть изготовленной продукции бракуется. Каждый из цехов располагает многочисленными инструментами и приспособлениями. При выполнении некоторых операций их все же не хватает, и цех вынужден обращаться в центральную инструментальную за недостающими. Каждый тип инструмента снабжен отдельным номером, и на него заведена запись со словecным описанием. Кроме того, там отмечено, какое количество инструментов этого типа выделено цехам и какое осталось в инструментальной. Экземпляры инструмента конкретного типа, например гаечные ключи одного размера, различаются по своим индивидуальным номерам. На фирме для каждого типа инструмента имеется запись, содержащая перечень всех индивидуальных номеров. Кроме того, указаны даты их поступления на склад. По каждой операции в фирме отмечают типы и количество инструментов этих типов, которые должны использоваться при ее выполнении. Когда инструменты действительно берутся со склада, фиксируется индивидуальный номер каждого экземпляра, указываются номер заказавшего их цеха и номер наряда на проведение работ. И в этом случае затребованное количество не всегда совпадает с заказанным. Наряд на проведение работ по форме напоминает заказ на приобретение товаров, но, в отличие от последнего, он направляется не поставщику, а в один из цехов. Оформляется этот наряд после того, как руководство фирмы сочтет необходимым выпустить партию некоторого продукта. В наряд заносятся шифр продукта, дата оформления наряда, срок, к которому должен быть выполнен заказ, а также требуемое количество продукта. Разработайте структуру таблиц базы данных, подберите имена таблиц и полей, в которых могла бы разместиться вся эта информация. Помимо SQL-запросов для создания таблиц базы данных, разработать пакет, состоящий из процедур и функций, позволяющий: для выбранного цеха выдать список операций, выполняемых им; для каждой операции – список расходуемых материалов с указанием количества; показать список инструментов и предоставить возможность добавления нового; выдать список используемых инструментов; для указанного интервала дат вывести список нарядов; показать список операций и предоставить возможность добавления новой операции; 97 выдать список расходуемых материалов, используемых в различных нарядах; выдать список товаров с указанием используемых инструментов; показать список нарядов и предоставить возможность добавления нового; выдать отчет о производстве товаров различными цехами, указав наименование цеха, название товара и его количество. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа сделать при помощи соответствующих триггеров. 13. База данных музыкального магазина Таблицы базы данных содержат информацию о музыкантах, музыкальных произведениях и обстоятельствах их исполнения. Нескольких музыкантов, образующих единый коллектив, называют ансамблем. Это может быть классический оркестр, джазовая группа, квартет, квинтет и т.д. К музыкантам причисляют исполнителей (играющих на одном или нескольких инструментах), композиторов, дирижеров и руководителей ансамблей. Кроме того, в базе данных хранится информация о пластинках, которыми магазин торгует. Каждая пластинка, а точнее, ее наклейка, идентифицируется отдельным номером, так что всем копиям, отпечатанным с матрицы в разное время, присвоены одинаковые номера. На пластинке может быть записано несколько исполнений одного и того же произведения – для каждого из них в базе заведена отдельная запись. Когда выходит новая пластинка, регистрируется название выпустившей ее компании (например, EMI), а также адрес оптовой фирмы, у которой магазин может приобрести эту пластинку. Не исключено, что компания-производитель занимается и оптовой продажей своих пластинок. Магазин фиксирует текущие оптовые и розничные цены на каждую пластинку, дату ее выпуска, количество экземпляров, проданных за прошлый год и в нынешнем году, а также число еще не распроданных пластинок. Помимо SQL-запросов для создания таблиц базы данных, разработать пакет, состоящий из процедур и функций, позволяющий: определить количество музыкальных произведений заданного ансамбля; вывести название всех пластинок заданного ансамбля; вывести всех исполнителей заданной композиции; вывести все новинки (за последние 2 недели); вывести аутсайдеров продаж; показать лидеров продаж текущего года, то есть названия пластинок, которые чаще всего покупали в текущем году; предусмотреть изменения данных о пластинках и ввод новых данных; предусмотреть ввод новых данных об ансамблях. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа осуществить при помощи соответствующих триггеров. 98 14.База данных соревнований по биатлону В базе данных должны записываться даты начала и завершения этапов, место проведения этапа, виды гонок в данном этапе, страны, выставившие команды на этап, фамилии и имена спортсменов, время каждой гонки для каждого спортсмена и количество промахов на каждой гонке для каждого спортсмена, а также количество очков, набранных спортсменом в гонке (по принципу: 1-е место = 50 очков, 2-е = 45, 3-е = 42, 4-е = 40 …30-е = 1 очко). На одном этапе не может быть более 3 гонок, Гонки идут с интервалом в 1-2 дня. Написать пакет, состоящий из процедур и функций, выполняющих следующее: для каждого этапа соревнований показать призеров (это первые 6 мест в биатлоне); предоставить возможность добавления нового этапа (с указанием места проведения, сроков проведения и типов гонок. На каждом этапе не более 3 гонок, сроки проведения – 5 дней); показать список спортсменов, участвующих в соревнованиях в заданный период времени и в заданной гонке; предоставить возможность добавления новой страны-участницы; предоставить возможность замены спортсмена и снятия спортсмена с соревнования; показать информацию о количестве набранных очков каждым спортсменом в рейтинговом порядке; показать список этапов (с видами гонок), в которых участвовал данный биатлонист, и его результаты по каждой гонке. Вывести также его положение в общем зачете; вывести информацию о первой тройке стран, чьи команды лидируют в общем зачете (считается по количеству золотых медалей, затем серебряных и бронзовых). Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа реализовать при помощи соответствующих триггеров. 15. Чемпионат по футболу В базе должны храниться названия команд, их составы, фамилии тренеров команд, даты проведения и место проведения игр, счет, количество забитых и пропущенных мячей в каждой игре для каждой команды, очки, полученные командой в каждой игре, нарушения и карточки для игроков. Написать пакет, состоящий из процедур и функций, выполняющих следующее: показать даты соревнований и участвующие в них команды, счет игры; показать список игроков команды и их тренера; показать общее количество пропущенных и забитых мячей для каждой команды; выявить команду-лидера и команду-аутсайдера; составить сводную таблицу по матчам каждого круга; предусмотреть возможность замены игрока; для заданного игрока определить количество матчей, проведенных на поле; для заданного игрока определить количество нарушений; выявить лучшего форварда и вратаря; определить победителей. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа реализовать при помощи соответствующих триггеров. 99 16. База данных системного администратора В базе данных должны храниться сведения о группах пользователей: годе их поступления и годе их окончания; фамилии, имена и отчества пользователей; их логины и пароли; дата последней смены пароля; количество и вид нарушений правил работы в сети; сведения об отключении пользователя от сети; список ресурсов, к которым открыт доступ определенной группы пользователей. Написать пакет, состоящий из процедур и функций, выполняющих следующее: позволить добавлять и удалять группы пользователей; позволить добавлять и удалять пользователей; позволить восстановление пароля для пользователя; разрешить смену пароля пользователю; добавлять и изменять ресурсы, к которым есть доступ у групп пользователей; вывести всех нарушителей. Если количество нарушений равно 3, то отключить пользователя от сети; вывести дату последней смены пароля. Если пароль меняли ранее, чем полгода назад, отключить пользователя от сети; предусмотреть возможность изменения фамилии пользователя при сохранении логина. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа осуществить при помощи соответствующих триггеров. 17. База данных оператора сотовой связи База должна хранить данные о датах операций, фамилии, имена и отчества подключившихся в этот день, их адреса и паспортные данные, номера SIM-карт, тарифный план, баланс, пополнения счетов ранее подключившихся клиентов (дату и сумму). Написать пакет, состоящий из процедур и функций, которые должны: определить сумму выручки в заданный период времени; определить количество подключившихся на каждый тарифный план; определить самый популярный и самый непопулярный тарифные планы; определить баланс для каждого клиента. Предусмотреть возможность изменения баланса; блокировать номера SIM-карт для клиентов с нулевым и отрицательным балансом; предусмотреть возможность добавления нового тарифного плана и перевод на него некоторых клиентов. Предусмотреть разработку триггеров, обеспечивающих каскадные изменения в связанных таблицах. Вставку числовых значений первичного ключа реализовать при помощи соответствующих триггеров. 100 Библиографический список 1. Чекалов, А. Базы данных: от проектирования до разработки приложений [Текст] / А. Чекалов – СПб.: БХВ-Петербург, 2003. – 384 с. 2. Дейт, К. Дж. Введение в системы баз данных [Текст] / К.Дж. Дейт – 7-е изд., перераб. и доп. – М.: Издательский дом «Вильямс», 2001. – 1072 с.: ил. 3. Кайт, Т Oracle для профессионалов [Текст] / Т.Кайт – СПб.: ООО «ДиаСофтЮП», 2003. – 672 с. 4. Генник, Дж. Oracle SQL*Plus: карманный справочник [Текст] / Дж. Генник – 2-е. изд., перераб. и доп.– СПб.: Питер, 2004. – 189 с.: ил. 5. Фейерштейн, С. Oracle PL/SQL для профессионалов [Текст] / С. Фейерштейн, Б. Прибыл – 3-е изд., перераб. и доп. – СПб.: Питер, 2004. – 941 с.: ил. 6. Миллсап, К. Oracle. Оптимизация производительности [Текст] / Д. Хольт, К. Миллсап. - СПб.: Символ-Плюс, 2006. – 464 с.: ил. 101 Содержание Часть 1. Введение в СУБД Open Office Base .................................................................................... 3 Лабораторная работа №1 Создание таблиц и установление связей ...............................3 Вопросы ........................................................................................................................7 Лабораторная работа №2 Создание форм. Ввод данных с помощью форм ...................8 Вопросы и задания ...................................................................................................... 11 Лабораторная работа №3 Создание и обработка запросов ........................................... 12 Вопросы и задания ...................................................................................................... 14 Лабораторная работа №4 Генерация отчетов ................................................................ 15 Вопросы и задания ...................................................................................................... 15 Лабораторная работа №5 Индивидуальные задания ..................................................... 16 Часть 2. Oracle ................................................................................................................................. 19 Лабораторная работа №1 Подключение к Oracle через SQL Developer. Создание и заполнение таблиц. Выборка данных ............................................................................ 19 Задания ........................................................................................................................ 23 Лабораторная работа №2 Однострочные и групповые функции.................................. 24 Однострочные функции ............................................................................................. 24 Групповые функции ................................................................................................... 27 Предложения GROUP BY и HAVING в команде SELECT ....................................... 27 Предложение GROUP BY .......................................................................................... 29 Предложение HAVING ............................................................................................... 29 Задания ........................................................................................................................ 30 Требования к сдаче лабораторной работы ................................................................. 31 Лабораторная работа №3 Подзапросы ........................................................................... 32 Общие сведения .......................................................................................................... 32 Синтаксис: ................................................................................................................... 32 Однострочные подзапросы ......................................................................................... 33 Многострочные подзапросы ...................................................................................... 34 Предложение HAVING с подзапросами .................................................................... 34 Задания ........................................................................................................................ 34 Лабораторная работа №4 Определение переменных во время выполнения ................ 35 Переменные подстановки с одним амперсандом ...................................................... 35 Определение переменных пользователя .................................................................... 36 Команда UNDEFINE ................................................................................................... 37 Передача значений переменных в командный файл ................................................. 38 Задания ........................................................................................................................ 38 Требования к сдаче лабораторной работы ................................................................. 38 Лабораторная работа №5 Представления ...................................................................... 39 Создание представления ............................................................................................. 39 Выполнение операции DML над представлением .................................................... 41 Вывод имен и структур представления ..................................................................... 42 Задания ........................................................................................................................ 43 Требования к сдаче лабораторной работы ................................................................. 43 Лабораторная работа №6 Управление доступом пользователей .................................. 44 Привилегии ................................................................................................................. 44 Схема ........................................................................................................................... 44 Создание пользователя ............................................................................................... 44 Системные привилегии .............................................................................................. 45 Роли ............................................................................................................................. 45 Изменение своего пароля ........................................................................................... 46 Привилегии на объекты .............................................................................................. 46 Предложение WITH GRANT OPTION ...................................................................... 48 Ключевое слово PUBLIC ............................................................................................ 48 Просмотр предоставленных привилегий, отмена привилегий ................................. 48 Создание синонима объекта ....................................................................................... 49 Вопросы ...................................................................................................................... 50 Задания ........................................................................................................................ 50 Требования к сдаче лабораторной работы ................................................................. 50 Лабораторная работа №7 Обработка данных. Управление транзакциями ................... 51 Вставка строк в таблицу ............................................................................................. 51 Обновление строк ....................................................................................................... 53 Удаление строк ........................................................................................................... 54 Транзакции базы данных: ........................................................................................... 54 Обработка транзакций ................................................................................................ 54 Задания ........................................................................................................................ 57 Требования к сдаче лабораторной работы ................................................................. 57 Лабораторная работа №8 Создание последовательностей ........................................... 58 Создание последовательности ................................................................................... 58 Псевдостолбцы NEXTVAL и CURVAL..................................................................... 59 Изменение параметров последовательности ............................................................. 60 Вопросы: ..................................................................................................................... 61 Задания: ....................................................................................................................... 61 Лабораторная работа №9 Триггеры базы данных ......................................................... 62 Создание триггеров..................................................................................................... 62 Доступ к значениям столбцов в триггерах строки .................................................... 63 Условные предикаты .................................................................................................. 64 Удаление триггеров .................................................................................................... 65 Включение и выключение триггеров ......................................................................... 66 Вывод информации о триггерах ................................................................................. 67 Примеры применения триггеров ................................................................................ 67 Вопросы ...................................................................................................................... 67 Задания ........................................................................................................................ 68 Требования к сдаче лабораторной работы ................................................................. 68 Лабораторная работа №10 Создание индексов. Оптимизация работы запросов при использовании индексов ................................................................................................ 69 Создание индекса ........................................................................................................ 69 Структура индекса и типы индексов.......................................................................... 70 Индексы с обращенным ключом ................................................................................ 71 Индексы по убыванию ................................................................................................ 71 Индексы на основе битовых карт ............................................................................... 72 Индексы по функциям ................................................................................................ 73 Просмотр индексов, удаление индексов. ................................................................... 75 Вопросы ...................................................................................................................... 75 Задания ........................................................................................................................ 76 Лабораторная работа №11 Курсоры .............................................................................. 77 Явный курсор .............................................................................................................. 77 Неявный курсор .......................................................................................................... 80 Задания ........................................................................................................................ 81 Требования к сдаче лабораторной работы ................................................................. 81 Лабораторная работа №12 Подпрограммы. Пакеты ..................................................... 82 Процедуры .................................................................................................................. 82 Функция ...................................................................................................................... 83 Фактические и формальные параметры ..................................................................... 84 Позиционная и именная нотация ............................................................................... 84 Моды параметров ....................................................................................................... 85 Перекрытие имен ........................................................................................................ 85 Вызов хранимых подпрограмм .................................................................................. 86 Пакет ........................................................................................................................... 86 Вопросы ...................................................................................................................... 87 Задания ........................................................................................................................ 88 Лабораторная работа №13 Индивидуальные задания ................................................... 89 Библиографический список.......................................................................................... 100 Содержание ................................................................................................................... 101 Методические материалы Русакова Маргарита Сергеевна Рогачева Елена Валерьевна ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО БАЗАМ ДАННЫХ Методические указания |