Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
Тема № 1. УПРАВЛЕНИЕ РАБОТАМИ Пользователи Автоматизируемые бизнес-процессы руководители; менеджеры; исполнители; клиенты (заказчики) ведение клиентской базы; оперативный учет поступления заказов от клиентов; кадровый учет; планирование и распределение работ между исполнителями; мониторинг и контроль исполнения работ; формирование аналитической и отчетной документации Варианты заданий Дополнительные требования 1.1 Управление проек- тами структуризация проектов; специализация исполнителей; повышение квалификации и профессиональная переподготов- ка сотрудников 1.2 Интернет- провайдер call-центр и техподдержка клиентов; услуги и тарифные планы; категории клиентов; оборудование, установленное у клиентов 1.3 Компьютер-сервис прайс-лист (сервис, ремонт, установка и настройка программ- ного обеспечения); прием заявок; выездные работы; доставка оборудования 1.4 Малое промышлен- ное предприятие (по отраслям — по выбору студента) номенклатура изделий; технологическая документация; складской учет материалов и комплектующих; складской учет готовой продукции 1.5 Фермерское сель- хозпредприятие (по отраслям — по вы- бору студента) номенклатура производства; учет основных фондов; договоры с заказчиками и поставщиками; складской учет готовой продукции Тема № 2. УПРАВЛЕНИЕ ОБРАЗОВАНИЕМ Пользователи Автоматизируемые бизнес-процессы руководители; преподаватели (учителя); технические сотрудники; студенты (учащиеся, слу- шатели) управление контингентом студентов (учащихся, слушателей); кадровый учет преподавательского состава; планирование; распределение и контроль исполнения работ; формирование аналитической и отчетной документации 3 / 19 80 Продолжение табл. Варианты заданий Дополнительные требования 2.1 Электронный днев- ник школьника тематические планы изучения предметов по параллелям с уче- том специализаций в старших классах; регистрация результатов текущей успеваемости учащихся и выполнения контрольных работ; формирование рейтинговых списков по предметам 2.2 Распределение го- довой учебной нагрузки препода- вателей вуза учебные планы по специальностям, образовательным уровням и формам обучения; дисциплины по семестрам и кафедрам; объем в часах по видам занятий; итоговый контроль; контингент студентов; штатное расписание преподавателей кафедр 2.3 Мониторинг успе- ваемости студентов учебные планы по специальностям, образовательным уровням и формам обучения; дисциплины по кафедрам и семестрам; итоговый контроль; контингент студентов по специальностям, образовательным уровням и формам обучения 2.4 Центр профессио- нальной переподго- товки специалистов управление образовательными программами; бухгалтерский учет (прием платежей от слушателей, оплата работы преподавателей); регистрация выдачи сертификатов 2.5 Администрирова- ние компьютерных классов оборудование и ПО рабочих мест; закрепление учебных дисциплин; расписание занятий в компьютерных классах; загруженность классов в течение семестра Тема № 3. АВТОМАТИЗИРОВАННЫЕ БИБЛИОТЕЧНЫЕ СИСТЕМЫ Пользователи Автоматизируемые бизнес-процессы библиотекари; читатели учет движения библиотечного фонда (поступление, списа- ние); поиск объектов библиотечного фонда; регистрация читателей; регистрация выдачи/возврата; анализ читательского спроса Варианты заданий Дополнительные требования 3.1 Абонемент публич- ной библиотеки система поиска книг, ориентированная на «неподготовленно- го» читателя: по авторам (с учетом соавторства); по названию и году издания книг; по редактируемому классификатору с возможностью «привяз- ки» одной книги к нескольким жанрам 3.2 Абонемент универ- ситетской библио- теки учебные планы по специальностям и образовательным уров- ням; контингент читателей (студентов); категории учебно-методических изданий; поиск по авторам, названиям, категориям, специальностям и учебным дисциплинам с учетом рекомендаций 4 / 19 81 Продолжение табл. 3.3 Читальный зал пе- риодических изда- ний редактируемый линейный классификатор изданий (например: научно-технические, литературно-художественные, детские, информационно-рекламные и др.); поиск статей в выпусках изданий по классификатору, авторам и названиям статей; предварительный просмотр аннотаций статей 3.4 Читальный зал научно-технических изданий редактируемый иерархический многоуровневый классифика- тор категорий изданий (например, книги (научные моногра- фии, учебники и др.), журналы (патентные, научные, популяр- ные и др.)); редактируемый иерархический классификатор отраслей науки и техники; поиск по категориям и отраслям науки и техники Тема № 4. СПОРТИВНЫЕ СОРЕВНОВАНИЯ Пользователи Автоматизируемые бизнес-процессы участники; болельщики; руководители; администраторы; аналитики регистрация участников; планирование; оперативный учет результатов; анализ Варианты заданий Дополнительные требования 4.1 Командные спор- тивные соревнова- ния (вид спорта — по выбору разра- ботчика) одна спортивная лига по одному виду спорта; хранение истории нескольких спортивных сезонов; регистрация и учет состояния спортивных арен; регистрация команд — участников спортивных соревнований в каждом сезоне; регистрация спортсменов — участников команд; рейтинги спортсменов с учетом их личных достижений в спортивных матчах 4.2 Командные спор- тивные соревнова- ния (вид спорта — по выбору разра- ботчика) несколько спортивных лиг; один спортивный сезон; регистрация и учет состояния спортивных арен; регистрация команд — участников спортивных соревнова- ний; регистрация спортсменов — участников команд; рейтинги 4.3 Индивидуальные спортивные сорев- нования (вид спор- та — по выбору разработчика) одна спортивная лига; хранение истории нескольких спортивных сезонов; регистрация спортсменов — участников соревнований в каж- дом сезоне; рейтинги спортсменов с учетом их личных достижений 4.4 Индивидуальные спортивные сорев- нования (вид спор- та — по выбору разработчика) несколько спортивных лиг по одному виду спорта; один спортивный сезон; регистрация спортсменов — участников спортивных соревно- ваний; рейтинги спортсменов с учетом их личных достижений 5 / 19 82 Продолжение табл. Тема № 5. ЗДОРОВЬЕ, ОТДЫХ, ТУРИЗМ Пользователи Автоматизируемые бизнес-процессы гости; пациенты (клиенты); руководители; врачи (тренеры); аналитики формирование прейскуранта услуг; кадровый учет; регистрация клиентов; планирование; оперативный учет; анализ Варианты заданий Дополнительные требования 5.1 Регистратура поли- клиники электронные амбулаторные карты пациентов поликлиники; расписание приема врачей-специалистов; запись пациентов на прием к врачам; диагнозы и назначения 5.2 Ветеринарная лечебница специализация ветеринаров; классификатор животных; расписание приема ветеринаров; запись пациентов на прием; диагнозы и назначения 5.3 Спортивно- оздоровительный комплекс прайс-лист (секции, группы, цены); оборудование и специализация спортзалов; формирование групп; контроль посещения занятий и прием платежей от клиентов; финансовая отчетность 5.4 Горнолыжный курорт прайс-лист; продажа путевок; размещение клиентов; аренда спортивного инвентаря; дополнительные услуги 5.5 Туристическое агентство прайс-лист; туры; маршруты; отели и транспортное обслуживание; экскурсии Тема № 6. ТРАНСПОРТ Пользователи Автоматизируемые бизнес-процессы гости; клиенты; руководители; менеджеры формирование прайс-листа; кадровый учет; регистрация клиентов; оперативный учет; анализ Варианты заданий Дополнительные требования 6.1 Автосалон поиск автомобиля по маркам, моделям, комплектациям, ценам; управление продажами: продажа автомобилей с пробегом; подбор комплектации и продажа новых автомобилей; оформление договоров с клиентами; Trade IN (продажа нового автомобиля с одновременной по- купкой автомобиля клиента) 6 / 19 83 Продолжение табл. 6.2 Прокат автомоби- лей поиск автомобиля по категориям, маркам, моделям, ценам, ти- пам двигателя и кузова; регистрация клиентов; регистрация парка автомобилей; выдача/возврат; финансовый анализ 6.3 Прокат велосипедов размещение велопарковок (в черте города); выбор велосипеда по типам, моделям; наличие велосипедов на велопарковках; выдача/возврат; финансовый анализ 6.4 Агрегатор такси адресный справочник; регистрация водителей и автомобилей; прием заказов; расчет стоимости поездки; учет исполнения заказов; финансовый учет и анализ 6.5 Автовокзал автопарк; междугородние маршруты; расписание рейсов; продажа билетов Тема № 7. ТОРГОВО-СКЛАДСКОЙ УЧЕТ Пользователи Автоматизируемые бизнес-процессы гости; клиенты; кладовщики; продавцы; менеджеры классификация товаров по категориям; формирование прайс-листа; кадровый учет; регистрация клиентов; учет поставок и отпуска товаров Варианты заданий Дополнительные требования 7.1 Оптовый склад продуктов питания учет предельных сроков реализации товаров; списание просроченных товаров; персональные скидки постоянным клиентам 7.2 Универсальный интернет-магазин формирование и контроль исполнения заказов; доставка товаров покупателям 7.3 Магазин по прода- же компьютерной и оргтехники контроль совместимости комплектующих; комплектование товаров по заявкам покупателей 7.4 Мебельный магазин комплектование товаров; выполнение дизайн-проектов; доставка товаров покупателям; сборка товаров у покупателей Тема № 8. ОБЩЕСТВЕННОЕ ПИТАНИЕ Пользователи Автоматизируемые бизнес-процессы гости; клиенты; официанты; менеджеры классификация блюд и напитков по категориям; формирование меню, состав блюд; кадровый учет; прием и учет исполнения заказов 7 / 19 84 Продолжение табл. Варианты заданий Дополнительные требования 8.1 Ресторан специализация ресторана («кухня»); предварительные заказы; банкеты 8.2 Диетическое пита- ние контроль состава блюд; рецепты приготовления блюд; расчет калорийности блюд 8 / 19 85 ЧАСТЬ 3. ПРОГРАММИРОВАНИЕ БАЗ ДАННЫХ 9 / 19 86 Поддержка языка управления данными — одна из важнейших функций СУБД, обеспечивающая разработчика языковыми средствами описания схемы базы данных и формирования запросов, связанных с извлечением или модифи- кацией хранимой в ней информации. При этом программист формулирует за- просы в терминах логической модели данных, а встроенный в СУБД трансля- тор преобразует высокоуровневый программный код в низкоуровневые проце- дуры, оперирующие соответствующими структурами физической модели. Ниже будут рассмотрены основные конструкции языка SQL (Structured Query Language — структурированный язык запросов), различные диалекты ко- торого поддерживаются реляционными СУБД. Процесс трансляции SQL-запро- са обсуждается в четвертой части данного учебника. 10 / 19 87 ГЛАВА 6. ОСНОВЫ ЯЗЫКА SQL В структуре языка SQL выделяют три группы операторов, называемых подъязыками SQL: • DDL (Data Definition Language) — язык определения данных; • DCL (Data Control Language) — язык управления доступом к данным; • DML (Data Manipulation Language) — язык манипулирования данными. 6.1. DDL — язык определения данных Язык DDL используется для описания структуры именованных логиче- ских объектов базы данных — баз данных, таблиц, представлений, индексов, процедур, функций, ограничений целостности, пользователей и т. д. Язык DDL включает три оператора (часто называемые командами, в отличие от операто- ров языка DML): CREATE, ALTER и DROP, используемых соответственно для со- здания, модификации и удаления объектов. Использование DDL-команд (в простейших вариантах их синтаксических конструкций) иллюстрируется листингом 3.1. а) CREATE MyDatabase; б) CREATE TABLE (Key IDENTITY PRIMARY KEY, Col INT, Data CHAR(60)); в) CREATE NONCLUSTERED INDEX Ind ON MyTable(Col); г) CREATE VIEW MyView(Data) AS SELECT Data FROM MyTable WHERE Col > 6; д) CREATE LOGIN SimpleClerk WITH PASSWORD = ‘simplePassword’; е) CREATE USER Clerk FOR LOGIN SimpleClerk; ж) ALTER USER Clerk WITH NAME = BigClerk; и) DROP VIEW MyView; к) CREATE ROLE All_Clerks; л) ALTER ROLE All_Clerks ADD MEMBER BigClerk; Листинг 3.1 Примеры использования DDL-команд Комментарии к примерам: а) создается база данных MyDatabase, все параметры файловой структуры которой определены по умолчанию; б) создается таблица MyTable со схемой из трех столбцов: первичный ключ Key целочисленного автоинкрементного типа, столбец Col целочисленно- го типа и столбец Data строкового типа; в) в таблице MyTable создается индекс Ind по столбцу Col; г) создается унарное представление MyView, строки которого содержат значение столбца Data тех строк таблицы MyTable, для которых значение Col > 6; д) создается учетная запись SimpleClerk с паролем SimplePassword; е) создается пользователь Clerk, связанный с учетной записью SimpleClerk; ж) пользователь Clerk получает новое имя BigClerk; 11 / 19 88 и) удаляется представление MyView; к) создается пользовательская роль (группа пользователей) All_Clerks; л) пользователь BigClerk становится членом роли All_Clerks. 6.2. DСL — язык управления доступом Язык DCL используется для управления разрешениями (permissions) до- ступа к объектам базы данных, таких как таблицы, представления, хранимые процедуры и функции, а также разрешениями на выполнение DСL-команд со стороны субъектов доступа — пользователей, ролей, хранимых процедур и функций. DCL включает три команды: GRANT (предоставить доступ), DENY (запре- тить доступ) и REVOKE (отменить ранее выданное разрешение), управляющие разрешениями следующих типов: – SELECT — разрешение на чтение строк таблицы или представления; – INSERT — разрешение на вставку строк в таблицу; – DELETE — разрешение на удаление строк таблицы; – UPDATE — разрешение на изменение (данных в строках таблицы или программного SQL-кода хранимых процедур и функций); – REFERENCES — разрешение субъекту доступа создавать внешние ключи в подчиненных таблицах без права доступа к главным таблицам; – EXECUTE — разрешение на выполнение хранимых процедур и функций. Правила применения и синтаксические конструкции DCL-команд деталь- но рассмотрены в пятой части данного учебника, листинг 3.2 содержит про- стейшие примеры их использования. а) GRANT SELECT ON MyTable(Data) TO All_Clerks; б) DENY INSERT, DELETE, UPDATE ON MyTable TO All_Clerks; в) GRANT SELECT ON MyTable TO BigClerk WITH GRANT OPTION; г) DENY ALL ON MyTable TO BigClerk CASCADE; д) REVOKE ALL ON MyTable TO BigClerk; Листинг 3.2 Примеры использования DCL-команд Комментарии к примерам: а) предварительно созданной пользовательской роли All_Clerks предо- ставляется право чтения столбца Data в таблице MyTable; б) пользовательской роли All_Clerks запрещается вставка, удаление и из- менение данных во всех столбцах всех строк таблицы MyTable; в) пользователю BigClerk предоставляется право чтения строк таблицы MyTable с правом предоставления этого права другим субъектам доступа (WITH GRANT OPTION); г) пользователю BigClerk запрещаются все операции над таблицей MyTable, при этом запрет каскадно (CASCADE) распространяется на всех субъектов доступа, получивших данные права от пользователя BigClerk; 12 / 19 89 д) отменяются ранее выданные пользователю BigClerk разрешения (как запрещающие, так и предоставляющие права доступа). 6.3. DМL — язык манипулирования данными В отличие от процедурных языков программирования, язык SQL (точнее его DML-подмножество) в своей основе является языком декларативного типа, и текст SQL-запроса к базе данных не содержит описания алгоритма получения результата, а только декларирует требования к этому результату. Например, SQL-запрос вида Select * From T Where T.x>T.y требует произвести выборку из таблицы T только тех ее строк, в которых значение поля x больше значения по- ля y, не описывая при этом алгоритма выполнения такой операции. Современ- ные версии языка SQL содержат ряд процедурных расширений, некоторые из них обсуждаются в п. 4.2. Язык DML содержит 4 составных оператора, наименования которых определяют основное их предназначение: – оператор SELECT — обеспечивает выборку множества кортежей отноше- ний (строк таблиц), удовлетворяющих заданным ограничениям, и (возможно) обработку выбранных данных; – оператор INSERT — обеспечивает вставку (добавление) в таблицы но- вых строк с заданными значениями их атрибутов; – оператор UPDATE — обновляет значения атрибутов в строках таблиц, удовлетворяющих заданным ограничениям; – оператор DELETE — удаляет из таблиц строки, удовлетворяющие задан- ным ограничениям. Среди перечисленных DML-операторов оператор SELECT, обеспечивающий выборку строк таблиц, является основным в том смысле, что семантически (не синтаксически!) он оказывается «вложенным» в каждый из остальных операто- ров: выборка строк производится перед их удалением (DELETE), обновлением значений (UPDATE) или генерацией перед их вставкой (INSERT) в таблицу. Составной оператор выборки SELECT включает 6 именованных разделов: SELECT, FROM, WHERE, ORDER BY, GROUP DY и HAVING, из которых только пер- вые два раздела являются обязательными. Результатом выполнения оператора SELECT является виртуальное отно- шение, схема которого определяется списком атрибутов, заданным в разделе SELECT этого оператора, а состав кортежей — параметрами остальных его раз- делов. Синтаксис и семантика оператора SELECT иллюстрируются примерами SQL-запросов (листинги 3.3–3.6), использующими учебную базу данных, схема которой представлена на рисунке 3.2. 6.3.1. Простейшие SQL-запросы Листинг 3.3 иллюстрирует синтаксис SQL-запросов, в результате выпол- нения которых из единственной таблицы БД производится выборка: а) всех строк и всех столбцов таблицы; 13 / 19 90 б) всех строк и четырех столбцов таблицы; в) всех строк таблицы с добавлением вычисляемого поля Стоимость; г) товаров, количество которых превышает минимально допустимый за- пас; д) заказов, размещенных позднее 1 января 2015 г.; е) заказов, размещенных в диапазоне дат от 1 января до 31 декабря 2015 г.; ж) заказов, размещенных в декабре 2015 г. (используются встроенные функции обработки темпоральных типов данных); и) имен и номеров телефонов представителей поставщиков и клиентов, имена которых включают заданный набор текстовых символов; к) имен и номеров телефонов представителей поставщиков, которые при этом не являются представителями клиентов. а) SELECT * FROM Склад; б) SELECT Товар, ОптоваяЦена, Количество, МинимальныйЗапас FROM Склад; в) SELECT Товар, ОптоваяЦена*Количество AS Стоимость FROM Склад; г) SELECT Товар, ОптоваяЦена*Количество AS Стоимость FROM Склад WHERE Количество> МинимальныйЗапас; д) SELECT Код_Заказа,ДатаРазмещения FROM Заказы WHERE Заказы.ДатаРазмещения>#01-01-2015#; е) SELECT Код_Заказа, ДатаРазмещения FROM Заказы WHERE ДатаРазмещения BETWEEN #01-01-2015# AND #31-12-2015#; ж) SELECT Код_Заказа, ДатаРазмещения FROM Заказы WHERE YEAR(ДатаРазмещения)=2015 AND MONTH(ДатаРазмещения)=12; и) SELECT DISTINCT Представитель, Телефон FROM Представители WHERE Представитель Like «*Peter*»; к) SELECT DISTINCT Представитель, Телефон FROM Представители WHERE Код_Клиента IS NULL AND Код_Поставщика IS NOT NULL. Листинг 3.3 Примеры простейших «однотабличных» SQL-запросов Следующие комментарии к рассмотренным выше примерам поясняют отдельные языковые конструкции и правила написания SQL-запросов. |