Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Кафедра «Информационные системы»
О.В. Амелина
БАЗЫ ДАННЫХ
Методические указания
по выполнению лабораторных работ
Дисциплина - «Базы данных»
Специальности: - 230105 «Программное обеспечение вычислительной
техники и автоматизированных систем»,
- 230100.62 – «Информатика и вычислительная
техника (бакалавры)»
Допущено ФГОУ ВПО «Госуниверситет – УНПК»
для использования в учебном процессе в качестве
методических указаний для высшего
профессионального образования
ОРЕЛ 2011
Автор: канд. экон. наук, доцент кафедры «Информационные системы» О.В. Амелина
Рецензент: канд. техн. наук, доцент кафедры «Информационные системы» А.П. Гордиенко
Настоящие методические указания предназначены для выполнения лабораторных работ по дисциплине «Базы данных» студентов очной формы обучения для специальностей: 230105 «Программное обеспечение вычислительной техники и автоматизированных систем», 230100.62 – «Информатика и вычислительная техника (бакалавры)». В методических указаниях содержатся рекомендации по выполнению и оформлению лабораторных работ, сведения о порядке защиты лабораторной работы, список рекомендуемой литературы, задания на лабораторные работы. Для более рациональной организации лабораторных работ методические указания содержат справочную информацию по некоторым разделам курса – построение ER-модели, переход к реляционной модели, сведения о языке SQL.
Редактор
Технический редактор
Федеральное государственное образовательное учреждение
высшего профессионального образования
«Государственный университет – учебно-научно-
производственный комплекс»
Лицензия ИД 00670 от 05.01.2000 г.
Подписано к печати Формат 6084 1/16.
Усл. печ. л. . Тираж экз.
Заказ №
Отпечатано с готового оригинал-макета
на полиграфической базе ОрелГТУ,
302030, г. Орел, ул. Московская, 65
© ФГОУ ВПО «Госуниверситет - УНПК, 2011
СОДЕРЖАНИЕ
|
[1] [2] 1 ЦЕЛЬ ПРОВЕДЕНИЯ ЛАБОРАТОРНЫХ РАБОТ [3] 2 ПОДГОТОВКА К ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ [4] 3 ПОСТРОЕНИЕ ER-МОДЕЛИ [5] 4 ПЕРЕХОД К РЕЛЯЦИОННОЙ МОДЕЛИ [6] 5 ЯЗЫК SQL [7] 6 ЗАДАНИЯ К ЛАБОРАТОРНЫМ РАБОТАМ [8] 6.1 ОПИСАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ [9] 6.2 РАБОТА С БАЗОЙ ДАННЫХ [10] 7 КОНТРОЛЬНЫЕ ВОПРОСЫ [11] 8 СПИСОК ЛИТЕРАТУРЫ |
На сегодняшний день реляционные базы уверенно являются неотъемлемой частью большинства информационных систем. В этой области прикладного программирования стремительно развиваются новые технологии, платформы реализации и среды разработки приложений. Тем не менее, остается классическая часть и общепризнанный подход к процессу проектирования, разработки и сопровождения реляционной базы данных, разбитый на последовательные этапы, которые обычно приходится реализовывать.
Данная работа имеет целью усвоение и закрепление теоретических знаний в области проектирования реляционных баз данных. Проектирование баз данных включает в себя следующие этапы:
1. Концептуальное проектирование, включающее в себя системный анализ рассматриваемой предметной области поставленной задачи и составление ее словесного описания. Этот этап в данной работе отсутствует, то есть каждый студент получает словесное описание объектов системной области и связей, существующих между ними, в качестве исходного задания. На данном этапе необходимо представить концептуальную модель в виде ER-диаграммы.
2. Логическое проектирование базы данных. На этом этапе осуществляется выбор СУБД (системы управления базами данных), с помощью которой база данных будет реализована. В лабораторной работе предполагается использование СУБД Firebird. Поскольку эта СУБД реализует реляционную модель данных, то необходимо осуществить переход от ER-модели к реляционной модели и затем нормализовать полученные отношения.
3. Создание таблиц, входящих в реляционную модель с учетом существующих между ними связей. В данной лабораторной работе требуется составить для каждой создаваемой таблицы оператор CREATE TABLE языка SQL.
4. Работа с базой данных, которая включает создание запросов, представлений, обеспечение целостности данных путем задания ограничений целостности (ссылочной целостности, ограничений доменов, альтернативных ключей, триггеров, хранимых процедур), обеспечение безопасности (создание пользователей с разными правами).
В отчете по данной работе студенты должны представить ER-диаграмму, созданную на основе выданного им в качестве задания словесного описания некоторой конкретной предметной области, схему реляционной модели, полученную из ER-модели, тексты операторов SQL CREATE TABLE для создания каждой таблицы реляционной модели, тексты запросов, триггеров и хранимых процедур.
Целью проведения лабораторных работ является приобретение навыков:
- построения концептуальной схемы предметной области;
- логического и физического проектирования структуры базы данных;
- работы с СУБД Firebird, построение структуры базы данных и занесения в нее информации.
Лабораторные работы рассчитаны на студентов, имеющих опыт работы в какой-либо интегрированной среде разработки программ – Delphi, Lazarus или других. Основной целью выполнения лабораторной работы является приобретение практических навыков проектирования и создания баз данных.
При подготовке к лабораторной работе студент должен повторить лекционный материал, относящийся к изучаемому вопросу, а также ознакомиться с материалом, приведенным в соответствующем разделе данного методического указания. При этом необходимо обращать особое внимание на разобранные примеры и фрагменты программ. Выполнение большинства работ основывается на материале, освоенном в предыдущих работах.
Готовность студента к работе определяется преподавателем путем проведения собеседования. Основным материалом для собеседования являются контрольные вопросы, приведенные в разделе 7.
Защита лабораторной работы производится после написания студентом программы и оформления отчета.
ER-модель (модель «сущность-связь») – широко используемый инструмент разработки баз данных, представляющий собой формализованное графическое представление предметной области в виде прямоугольников, линий и специальных символов.
Преимущество ER-модели заключается в том, что она понятна и разработчикам баз данных, и заказчикам, то есть будущим ее пользователям, и потому все возможные изменения в структуре данных согласуются между ними именно на уровне этой модели. ER-модель никак не связана ни с какой конкретной СУБД, этот выбор осуществляется впоследствии. В основе ER-модели лежат понятия «сущность» («entity») и «связь» («relation»). Не существует удовлетворительного формального описания понятия «сущность». Можно сказать, что это – совокупность значимых объектов, существующих в рамках предметной области. Например, при построении системы учета успеваемости студентов придется оперировать сущностями «студент», «преподаватель», «дисциплина», а в случае создания банковской системы – сущностями «клиент» и «счет» Каждая сущность имеет набор атрибутов, представляющих собой именованные свойства сущности. Например, сущность «студент» может иметь атрибуты:
1. Номер группы
2. Фамилия
3. Имя
4. Номер зачетной книжки
5. Дата рождения
Сущность – совокупность объектов, а каждый объект этой совокупности – экземпляр сущности. Экземпляры должны быть различимы, то есть не должно быть двух экземпляров с одинаковыми наборами атрибутов. Каждая сущность имеет набор атрибутов, уникальный для каждого экземпляра сущности, называемый ключом. Например, для сущности «студент» ключ состоит из одного атрибута «Номер зачетной книжки», так как точно не существует двух и более студентов с одинаковым значением этого атрибута. Между сущностями могут быть установлены связи, определяющие, как сущности соотносятся или взаимодействуют между собой. Связь может существовать между двумя сущностями (бинарная связь) или между сущностью и ею же самой (рекурсивная связь). Наличие связи означает, что одна из них имеет атрибут или набор атрибутов, составляющий ключ другой сущности. Например, сущность «студент» и сущность «учебная группа» связаны между собой атрибутом «номер группы». Этот атрибут является ключом сущности «учебная группа» и присутствует в наборе атрибутов сущности «студент», для сущности «студент» он зазывается внешним ключом. Сущности, у которых внешний ключ переходит в состав первичного ключа, называются зависимыми, а связь – идентифицирующей. Такая связь изображается сплошной линией, а зависимая сущность – прямоугольником со скругленными углами. Если внешний ключ входит в состав не ключевых атрибутов, то связь называется не идентифицирующей и изображается пунктирной линией.
Связи бывают трех типов:
1. Связь «один к одному». При этой связи один элемент первой сущности связан не более чем с одним экземпляром второй сущности. Этот тип связи встречается сравнительно редко.
2. Связь «один ко многим». В этом случае один экземпляр первой сущности связан с несколькими экземплярами второй сущности, но один экземпляр второй сущности связан не более чем с одним экземпляром второй сущности. Первая сущность считается в данной связи основной, вторая – подчиненной. Примером может служить рассмотренная выше связь между сущностями «учебная групп» и «студент». Сущность «учебная группа» является здесь основной, сущность «студент» – подчиненной, так как одна группа состоит из нескольких студентов, и, следовательно, одному экземпляру сущности «группа» соответствует несколько экземпляров сущности «студент», принадлежащих этой группе.
3. Связь «многие ко многим». В этом случае каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, а каждый экземпляр второй сущности – с несколькими экземплярами первой. В качестве примера можно привести связь между сущностями «преподаватель» и «дисциплина» системы учета успеваемости. Эта связь имеет тип «многие ко многим», так как один преподаватель может вести несколько учебных дисциплин, а одна дисциплина вестись несколькими преподавателями.
Связи также классифицируются по возможности null-значения. В какой-то момент времени студент может не принадлежать группе. Например, на момент академического отпуска или при переводе со специальности на специальность. Таким образом, атрибут «номер группы» сущности «студент», кроме значений первичного ключа сущности «группа», может принимать null-значение. То есть в семантической модели присутствует связь 1:М с возможностью неопределенного значения внешнего ключа. Такая связь отмечается ромбом у сущности-предка. Рассмотрим другой пример. Сотрудник работает в отделе. Можно провести рассуждения, аналогичные предыдущему примеру. Если каждый сотрудник должен работать в отделе, то между сущностями «сотрудник» и «отдел» присутствует связь 1:М без возможности неопределенного значения внешнего ключа. Она отображается таким же способом, как и в предыдущем примере, но ромб у сущности-предка отсутствует. Следовательно, для построения ER-модели необходимо сначала выявить сущности, характерные для заданной предметной области и определить для каждой сущности набор атрибутов, имеющих значение исходя из поставленной задачи. Затем необходимо определить все имеющиеся связи между сущностями и характеристики этих связей. После этого строится ER-диаграмма, то есть собственно графическое изображение модели с использованием одной из существующих нотаций. Для простых случаев ER- диаграмму можно рисовать вручную, для более сложных можно воспользоваться специализированным программным обеспечением (каким-либо CASE-средством). В данной работе мы будем придерживаться нотации IDEFX. В стандарте IDEFX сущность обозначается прямоугольником, разделенным на две части. В верхней части перечисляются атрибуты первичного ключа, в нижней – остальные атрибуты. Над прямоугольником записывается имя сущности, которое должно быть именем существительным в единственном числе. Все наборы атрибутов, которые могли рассматриваться, как ключи помечают как АК, то есть альтернативный ключ. Связи между сущностями изображаются в виде линий, соединяющих прямоугольники соответствующих сущностей. Для сущности, находящейся со стороны «многие», линия связи должна заканчиваться закрашенным кругом.
В качестве примера создадим несколько упрощенную модель системы учета успеваемости студентов. Оценки, получаемые студентами по изучаемым дисциплинам, заносятся в специальную таблицу, где указывается номер зачетной книжки студента, код дисциплины, табельный номер преподавателя, дата получения оценки и собственно оценка. Студент не может в один и тот же день два раза сдавать одну и ту же дисциплину. Студенты разбиты по группам. Каждая группа имеет свой номер и куратора из числа преподавателей. Каждый преподаватель может вести несколько дисциплин, каждая учебная дисциплина может вестись несколькими преподавателями. Таким образом, можно выделить следующие сущности:
1) студент;
2) преподаватель;
3) дисциплина;
4) учебная группа;
5) оценка.
Атрибутами сущности «студент» будут номер зачетной книжки, фамилия, имя, номер группы. Ключом здесь будет номер зачетной книжки.
Сущность «преподаватель» будет иметь атрибуты табельный номер, фамилия, имя, отчество. Ключом сущности, естественно, является табельный номер преподавателя.
У сущности «дисциплина» будет два атрибута – код дисциплины и наименование дисциплины, при этом первый атрибут - ключевой.
Сущность «учебная группа» следующие атрибуты - номер группы и табельный номер куратора группы; Здесь номер группы – ключевой атрибут.
И, наконец, сущность «оценка» имеет следующие атрибуты - номер зачетной книжки студента, код дисциплины, код преподавателя, дату получения оценки, оценка.
Напомним, что ключом сущности является атрибут или набор атрибутов, значения которых являются уникальными для каждого экземпляра сущности. В данном случае ключом сущности «оценка» будет набор из трех атрибутов – номер зачетной книжки, код дисциплины и дата получения оценки, так как предполагается, что студент в один день по некоторой дисциплине может получить только одну оценку.
Теперь рассмотрим связи, имеющиеся между сущностями.
1. Так как в одной группе числятся несколько студентов, между сущностями «учебная группа» и «студент» существует связь «один ко многим». Внешний ключ сущности «студент» может принимать Null значения, в случае, если студент еще не прикреплен к группе, или он находится в академическом отпуске.
2. Так как у каждой группы есть куратор из числа преподавателей, между сущностями «учебная группа» и «преподаватель» существует связь «один к одному».
3. Поскольку каждый преподаватель может вести несколько дисциплин, а одна дисциплина вестись несколькими преподавателями, то между сущностями «преподаватель» и «дисциплина» имеется связь «многие ко многим».
4. Между сущностью «студент» и сущностью «оценка» существует связь «ко многим». Каждый студент неоднократно получает оценки по изучаемым дисциплинам.
5. Между сущностью «преподаватель» и сущностью «оценка» существует связь «один ко многим». Каждый преподаватель выставляет оценки по своим дисциплинам.
6. Между сущностью «дисциплина» и сущностью «оценка» существует связь «один ко многим», так как по каждой дисциплине может быть выставлено много оценок.
На рисунке 1 показана ER-диаграмма, построенная для данной задачи, с учетом существующих связей.
Рисунок 1 – ER-модель предметной области
На следующем этапе проектирования необходимо преобразовать ER-модель в реляционную. Основной структурой реляционной модели является отношение (relation), графической интерпретацией которого служит таблица. Каждое отношение состоит из некоторого ограниченного числа кортежей, а содержание каждого кортежа определяется набором атрибутов отношения. Каждый атрибут имеет определенный тип, значение которого берется из определенного домена. Кортежам отношения соответствуют строки таблицы, количество столбцов таблицы равно количеству атрибутов отношения, а тип величины, находящейся в соответствующем столбце определяется типом соответствующего атрибута. Отношения могут быть связаны между собой посредством набора атрибутов, одинаково содержащихся в обоих отношениях. Связи между отношениями в реляционной модели, в отличие от ER-модели, могут иметь только тип «один ко многим», то есть одно отношение всегда будет основным, а второе – подчиненным и одному кортежу основного отношения могут соответствовать несколько кортежей подчиненного отношения. Данное соответствие означает, что у обоих кортежей значения набора атрибутов, по которому связаны отношения, совпадают. Обычно у основного отношения данный набор атрибутов является первичным ключом, и, следовательно, уникальным для каждого кортежа. У второго отношения данный набор атрибутов называется внешним ключом для данной связи. Кортежей с одинаковым значением внешнего ключа может быть сколько угодно, но не может существовать кортежей со значением внешнего ключа, которому не соответствовал какой-либо первичный ключ основного отношения. Связь «один к одному» рассматривается, как частный случай связи «один ко многим». Связи «многие ко многим» в реляционной модели быть не может. Чтобы преобразовать ER-модель в реляционную модель необходимо выполнить следующие действия.
1. Поставить каждой сущности ER-модели в соответствие отношение реляционной модели, при этом каждому атрибуту сущности ставится в соответствие атрибут отношения реляционной модели. Ключ сущности становится первичным ключом соответствующего отношения (PRIMARY KEY). При этом имена сущностей и отношений, равно, как и атрибутов, могут не совпадать. Желательно при указании имен отношений и атрибутов реляционной модели использовать латиницу, поскольку эти имена чаще всего являются идентификаторами в некотором языке программирования.
2. В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов, соответствующий ключу основной сущности, если, конечно, он там уже не присутствовал. В любом случае этот набор атрибутов становится внешним ключом в подчиненном отношении (FOREIGN KEY).
3. При обязательном характере связи у атрибутов, соответствующих внешнему ключу, устанавливается свойство отсутствия неопределенных значений (NOT NULL).
4. Если в ER-модели имеются связи «многие ко многим», то их надо преобразовать в связи «один ко многим», поскольку связи «многие ко многим» в реляционной модели не допускаются. Для этого в реляционную модель добавляется связующее отношение, атрибуты которого соответствуют атрибутам первичных ключей обоих отношений, участвующих в связи «многие ко многим». Связующее отношение будет находиться в связи «один ко многим» с каждым из этих отношений. В рассматриваемом примере связь «многие ко многим» имеют сущности «преподаватель» и «дисциплина». В реляционной модели вводится связующее отношение R, атрибутами которого будут «ID_Subject» и «Tab_num». Первый атрибут соответствует первичному ключу сущности «дисциплина», а второй – первичному ключу сущности «преподаватель». Это отношение будет иметь связь «один ко многим» к отношениям, соответствующим сущностям «преподаватель» и «студент» (см. Рис.2).
Рисунок 2- Преобразование связи «многие ко многим»
В принципе, уже на уровне ER-модели можно провести ее нормализацию. В данной работе требуется, чтобы модель удовлетворяла условиям третьей нормальной формы. Напомним, что отношение находится в первой нормальной форме, если значения всех его атрибутов атомарны, то есть представляют собой простые, а не составные типы данных. В нашем примере это условие выполняется для всех сущностей. Значениями атрибутов всех сущностей являются только числа, строки или даты. Массивов, структур или подтаблиц среди них нет. Сущность удовлетворяет условиям второй нормальной формы, если каждый не ключевой атрибут зависит от всего первичного ключа, но не зависит от какой-либо части первичного ключа. Из определения следует, что условие второй нормальной формы может быть нарушено только у составного первичного ключа. В приведенном примере составной первичный ключ имеется у сущности «оценка». Не ключевым атрибутом здесь является только один атрибут «оценка». Его значение определяется всей совокупностью ключевых атрибутов, поэтому сущность условиям второй нормальной формы отношение «оценки» удовлетворяет. Сущность находится в третьей нормальной форме, если она находится во второй нормальной форме, и каждый не ключевой атрибут зависит только от первичного ключа, но не зависит от какого-либо не ключевого атрибута. Все сущности нашего примера удовлетворяют условиям третьей нормальной формы. Полученную реляционную модель также изобразим в графической форме. Каждому отношению будет соответствовать прямоугольник, в который будут вписаны имена атрибутов отношения и их типы. Набор возможных типов определяется официальным стандартом языка SQL, однако у конкретных СУБД обычно имеются расхождения со стандартом. В нашей модели атрибуты могут принимать текстовые, числовые значения, или представляют собой какую-либо дату. Рассмотрим эти типы подробнее.
1. Тип даты вообще не определен в стандарте SQL и в каждой СУБД определяется по-своему. В настоящей работе для создания таблиц используется СУБД MS Firebird, в которой для указания даты и времени имеется тип Date.
2. Для представления строчных атрибутов используется стандартный тип Character(n) или, сокращенно, CHAR(n), поддерживаемый любой СУБД. Здесь n – максимальная длина атрибута в символах. Длину каждого строчного атрибута необходимо согласовывать с заказчиком, но в данной работе ее можно выбирать произвольно, по усмотрению разработчика.
3. Для числовых атрибутов можно использовать тип integer, если атрибут может принимать только целочисленные значения, или тип float, если атрибут может иметь дробную часть.
Рисунок 3 – Реляционная модель предметной области
На рисунке 3 приведена реляционная модель для нашего примера. В ней восемь отношений, причем семь из них соответствуют семи сущностям ER-модели, а восьмое отношение R появилось в результате преобразования связи «многие ко многим» между сущностями «преподаватели» и «дисциплины» к двум связям «один ко многим» между отношениями subjects и R , а также teachers и R. На рисунке 3 линии, соответствующие связям «один ко многим» четко соединяют именно те атрибуты двух отношений, которые участвуют в этой связи. Отношение, участвующее в связи со стороны «многие», является подчиненным, а это означает, что атрибут подчиненного отношения может брать значения только из списка значений соответствующего ему по связи атрибута главного отношения.
1. Связь между отношениями teacher и group является связью один к одному, поскольку только один из преподавателей может быть куратором группы. Однако эта связь рассматривается, как частный случай связи один ко многим, причем отношение group является подчиненным, так как значение атрибута curator (табельный номер преподавателя) берется из табельных номеров отношения teacher. Таким образом, атрибут curator подчиненного отношения group является внешним ключом для первичного ключа Tab_Num главного отношения teacher.
2. В ER-модели связь 3 между сущностями «преподаватель» и «дисциплина» имела тип «многие ко многим». Поскольку в реляционной модели могут существовать только связи «один ко многим», было введено новое отношение R и вместо одной связи тип «многие ко многим» появились две связи «один ко многим».
Идентификаторы
Идентификаторы языка SQL предназначены для обозначения объектов в БД, например таблиц (отношений) или полей (атрибутов). Идентификаторы пользователя должны соответствовать следующим правилам:
Типы данных
Основными типами данных в стандарте SQL сведены в таблице 1.
Таблица 1. – Типы данных в стандарте SQL
|
Тип данных |
Объявление |
|
Символьный постоянной длины |
char (<длина>) |
|
Символьный переменной длины |
varchar (<максимальная длина>) |
|
Целый |
int |
|
С плавающей точкой |
decimal, numeric |
|
Дата |
Date /*Значения вводятся в кавычках */ |
При реализации стандарта в различных СУБД возможны другие названия типов, например, time, dateTime.
В interbase есть еще двоичный тип не ограниченного размера blob. В других СУБД есть text и image.
Скалярные операторы
Язык SQL включает некоторое количество встроенных скалярных операторов и функций, которые могут использоваться для построения скалярных выражений. В качестве аргументов этих скалярных операторов могут выступать либо константы, либо атрибуты отношений.
Наиболее простые скалярные операторы – арифметические операторы (*, +, -, /). Кроме того, возможно использование функций, приведенных в таблице 2.
Таблица 2. – Основные функции стандарта SQL
|
Функция |
Назначение |
Пример |
|
1 |
2 |
3 |
|
Char_length |
Возвращает длину строки, переданной функции в качестве аргумента |
Char_length(‘строка’). Результат работы: 6 |
|
Cast |
Преобразует значение выражения одного типа к выражению другого типа |
Cast (‘500’ as integer) Результат работы: 500 |
|
|| |
Конкатенация строк |
‘ab’||’12’ Результат работы: ‘ab12’ |
|
Current_user или User |
Функция возвращает символьную строку, соответствующую текущему пользователю, подключенному к БД |
|
|
Lower |
Преобразование всех символов строки к нижнему регистру |
lower(‘ABcdD’) Результат работы: ‘abcdd’ |
Продолжение таблицы 2
|
1 |
2 |
3 |
|
Upper |
Преобразование всех символов строки к верхнему регистру |
upper(‘ABcdD’) Результат работы: ‘ABCDD’ |
|
Position |
Функция возвращает позицию одной строки в пределах другой строки. |
Position (‘ab’, ‘dfabfg’) Результат работы: 3 |
|
Substring |
Функция выполняет выделение подстроки из заданной строки. |
Substring(‘asdwq’, 2, 3) Результат работы: ‘sdw’ |
|
Current_date, Current_time |
Функции возвращают соответственно текущую дату и текущее время |
При реализации стандарта в различных СУБД те или иные функции могут отсутствовать или иметь другой формат.
Язык определения данных
Та часть языка SQL, которая служит для создания объектов базы данных, называется DDL (Data Definition Language). Таблицы создаются оператором
create table <имя таблицы>
(<имя поля> <тип поля> [null | not null] [default <значение по умолчанию>] [, …])
Из приведенного описания видно, что для каждого поля можно указать возможность принимать неопределенные значения. Неопределенные значения могут принимать поля любого типа. Такое значение обозначается как Null и не является каким-либо конкретным значением, так как символизирует отсутствие знаний об этом значении. Указание такой характеристики для поля не является обязательным. При ее отсутствии в различных СУБД принимается либо Null (Nullable), либо Not Null
Для того чтобы указать первичный ключ отношения используется конструкция Primary key. Она может быть написана двумя способами.
Если первичный ключ состоит из одного атрибута, то конструкция primary key указывается в рамках create table сразу за определением соответствующего поля.
Если первичный ключ состоит из нескольких атрибутов, то конструкция primary key указывается после объявления последнего поля (но до закрывающейся скобки), а после нее в круглых скобках перечисляются атрибуты первичного ключа.
Все атрибуты первичного ключа должны быть объявлены как not null.
Рассмотрим примеры создания таблиц. Пусть имеются следующие отношения
Предмет (название: строка 250 символов,
сокращение: строка 20 символов);
Студент (зачетка: целое,
ФИО: строка 250 символов);
Изучает (зачетка: целое,
название: строка 250 символов,
оценка: строка 10 символов)
Создание соответствующих таблиц будет иметь вид
Create table subject
( subject_name char(250) not null primary key,
subject_short char(20)
)
Create table student
( student_num int not null primary key,
student_name char(250) not null
)
Create table study
( subject_name char(250) not null,
student_num int not null,
mark char(10),
primary key (subject_name, student_num)
)
Таблицы удаляются операцией drop table <имя таблицы>.
Некоторые СУБД позволяют изменять структуру таблиц при помощи операции alter table. Для того, чтобы удалить поле из таблицы эта операция используется следующим образом:
alter table <имя таблицы>
drop <имя поля>
Для добавления столбца в таблицу выполняется следующая конструкция:
alter table <имя таблицы>
add <имя поля> <тип поля> [null | not null]
Этой же конструкцией можно добавить к таблице первичный ключ или другие правила целостности.
alter table <имя таблицы>
add primary key (список атрибутов первичного ключа)
/*Если у таблицы уже есть первичный ключ, то будет ошибка. Первичный ключ должен быть всегда, несмотря на то, что СУБД позволяют и по другому. */
Простые запросы DML
Чтение данных из БД производится с помощью оператора SELECT. Результатом работы этого оператора является отношение. Общий формат оператора SELECT имеет следующий вид.
Select [distinct]
{* | <выражение> [as <имя>] [, ...]}
from <имя таблицы> [<псевдоним>] [, ...]
[where <условие>]
[group by <список полей> [having <условие>]]
[order by <список полей>]
Рассмотрим основные разделы запроса.
Select – устанавливает, какие поля должны присутствовать в результирующем отношении.
From – определяет список таблиц, на основе которых нужно получить результирующее отношение.
Where – указывает условие, которому должны отвечать кортежи результирующего отношения.
Group by – служит для создания групп
Having – задает условие, на основе которого группы включаются или не включаются в результирующее отношение.
Order by – определяет упорядоченность кортежей результирующего отношения при выводе.
При написании запроса Select под выражением подразумевается либо имя поля, либо скалярное выражение, построенное на основе одного или нескольких полей таблиц раздела from запроса.
При обращении к полям используется следующая конструкция <имя таблицы>.<имя поля>. Если имя поля уникально для данного запроса, то имя таблицы при обращении может быть пропущено. Вместо имени таблицы можно использовать ее псевдоним, определяемый в разделе from запроса. Кроме того, псевдонимы используются для того, чтобы таблицу можно было несколько раз использовать в одном запросе.
Рассмотрим простейший запрос. Пусть необходимо получить информацию обо всех студентах.
select * from student
Использование символа «*» в данном запросе означает, что необходимо вывести все поля таблиц раздела from.
Если необходимо получить просто список фамилий, то можно воспользоваться одним из следующих запросов:
select student.student_name from student
select t1.sudent_name
from student t1 (использование псевдонима)
select student_name
from student (можно пропустить имя таблицы, так имя поля уникально в данном запросе)
При выводе можно переименовать столбцы следующим образом
select student_num as field1, student_name as field2
from student
Ниже приведен пример использования выражений при построении запросов.
select
cast(student_num as char(6)) ||
student_name as single_field
from student
Теперь обратим внимание на таблицу study. Пусть необходимо узнать какие оценки получали студенты. Запрос вида
select mark from study
позволит получить результат вида
|
mark |
|
отлично |
|
хорошо |
|
удовлетв. |
|
отлично |
|
хорошо |
Для того, чтобы получить кортежи без повторений нужно использовать конструкцию distinct в разделе select запроса. То есть нужно написать:
select distinct mark from study
Теперь рассмотрим более подробно раздел Where. Этот раздел содержит отдельные условия, которые могут соединяться при помощи операций and и or. К условиям может применяться операция отрицания not.
Существует пять основных типов условий в запросах.
1. Сравнение. Сравниваются результаты вычисления выражений. Например, для того, чтобы получить названия предметов, по которым были получены неудовлетворительные оценки нужно построить следующий запрос.
select subject_name
from study
where mark=’неудовл.’
Сравнение может иметь и более сложную форму. Например, чтобы найти номера зачеток студентов, получивших неудовлетворительную оценку по истории, необходимо написать запрос
select student_num
from study
where mark=’неудовл.’ and subject_name = ’история’
2. Диапазон. Проверяется, попадает ли результат вычисления выражения в заданный диапазон значений. Для формирования такого условия используется оператор between. Например, запрос, выводящий информацию о студентах, номера зачеток которых находятся в диапазоне от 10 до 100, имеет следующий вид.
select * from student
where student_num between 10 and 100
3. Принадлежность множеству. Проверяется, принадлежит ли результат вычисления выражения к заданному множеству значений. Такое условие формируется с использованием оператора in. Например, запрос, выводящий информацию по обучению истории, философии и математике, можно записать следующим образом.
select * from study
where subject_name in
(‘история’, ‘философия’, ‘математика’)
4. Соответствие шаблону. Проверяется, отвечает ли некоторое строковое значение заданному шаблону. В языке SQL существует два специальных символа шаблона, используемых при проверке символьных значений. «%» - представляет любую последовательность из нуля или более символов. «_» - представляет любой одиночный символ. Проверка соответствия шаблону осуществляется с помощью оператора Like. Например, найдем все предметы, в названии которых встречается слово «системы».
Select * from subject
where subject_name like ‘%системы%’
5. Значение Null. Проверяется, содержит ли данное поле неопределенное значение. Для сравнения со значением Null используется оператор is null. Например, запрос, выводящий информацию о неизвестных оценках, выглядит следующим образом.
select * from study
where mark is null
Изменение содержимого БД.
Добавление кортежей в БД осуществляется с использованием оператора INSERT. Существует две формы этого оператора.
Первая форма предназначена для добавления единственного кортежа и имеет вид:
Insert into <имя таблицы> [(<имя поля> [,…]]
values (<значение> [,…])
Количество значений должно совпадать либо с количеством имен полей в запросе, либо с общим количеством полей в таблице, если список полей отсутствует. Список полей должен быть указан, если известны значения не всех полей кортежа. В этом случае неуказанным полям будет присвоено неопределенное значение (Null).
Например, выполним запрос, добавляющий информацию о том, что студент с номером зачетки 12 изучает историю, но еще не получил оценку по этому предмету.
insert into study (student_num, subject_name)
values (12, ‘история’)
Вторая форма оператора insert позволяет вставлять в таблицу несколько кортежей, полученных при помощи оператора select. В этом случае оператор имеет вид:
Insert into <имя таблицы> [(<имя поля> [,…]]
select …
В этом случае количество полей в разделе select подчиняется тем же правилам, что и при добавлении одного кортежа.
Пусть при проектировании БД была допущена ошибка, и таблица subject не была создана изначально. Такая ситуация могла возникнуть в случае, когда изначально не было известно о существовании атрибута «сокращение». Если добавить этот атрибут в состав отношения «Изучает», то получится функциональная зависимость не от первичного ключа. Поэтому должна быть создана отдельная таблица. Для перекачивания в нее данных можно воспользоваться следующим запросом:
insert into subject (subject_name)
select distinct subject_name from study
Модификация кортежей осуществляется с помощью оператора UPDATE. Этот оператор имеет следующий формат.
Update <имя таблицы>
set <имя поля>=<значение> [, …]
[where <условие>]
Используя, этот оператор, можно изменить значения нескольких полей кортежа. Кортежи, которые подлежат изменению, задаются разделом where запроса. Формат раздела where аналогичен разделу where оператора select. Если раздел where пропущен в запросе update, то будут модифицированы все кортежи отношения.
Построим запрос, который вместо всех неудовлетворительных оценок проставит неопределенное значение
Update study
set mark=null
where mark=’неудовл.’
Удаление кортежей из таблицы осуществляется оператором DELETE. Его формат приведен ниже.
Delete from <имя таблицы>
[where <условие>]
Раздел where аналогичен оператору select. Отсутствие этого раздела в операторе delete приведет к удалению всех кортежей отношения.
Запрос, удаляющий всю информацию об обучения студента с номером зачетки 10, имеет вид:
Delete from study
where student_num=10
Многотабличные запросы.
Вернемся к оператору select.
Чаще всего запросы строятся более чем по одной таблице. В этом случае все необходимые для построения запроса таблицы перечисляются в разделе from, а в разделе where кроме обычных условий обязательно указываются условия соединения по связям между таблицами. Если эти условия пропустить, то в результате получится декартово произведение таблиц, а такой запрос достаточно редко встречается на практике.
Пусть необходимо построить запрос, который выводит фамилии студентов с оценками по всем предметам, при этом вместо полных названий нужно использовать сокращенные.
Select student_name, subject_short
from student t1, subject t2, study t3
where t3.student_num=t1.student_num
and t2.subject_name=t3.subject_name
Можно проследить соответствие подобных запросов с запросами на реляционной алгебре. Раздел from соответствует декартову произведению таблиц, раздел where – селекции этого произведения, а раздел select – проекции полученного отношения.
Немного усложним задачу. Пусть нет необходимости выводить неудовлетворительные оценки. Кроме того, фамилии студентов нужно упорядочить по алфавиту, а для каждого студента предметы выводить также в алфавитном порядке. В этом случае запрос будет иметь следующий вид:
Select student_name, subject_short,mark
from student t1, subject t2, study t3
where t3.student_num=t1.student_num
and t2.subject_name=t3.subject_name
and t3.mark<>’неудовл.’
order by student_name, subject_short
Когда в запросе соединяются две таблицы, то они перечисляются в разделе from, а в разделе where записываются условия равенства по связи. Однако такие запросы не вернут кортежей из одного отношения, если нет соответствия в другом.
Например, вывести ФИО и оценки студента, но в список включить и студентов, которые еще не сдали ни одного экзамена.
Для того, чтобы в результат можно было включать кортежи одного исходного отношения, независимо от наличия соответствия в другом отношении применяется внешнее соединение.
Внешнее соединение в стандарте SQL имеет три разновидности: левое, правое и полное в зависимости от того, кортежи какого из отношений обязательно должны войти в результат. Указание на внешнее соединение может быть помещено в разделе from любого SQL запроса.
from отношение 1 left | right | full отношение 2 on <условие1> left | right | full отношение 3 on <условие2>…
Левое соединение в любом случае сохраняет все кортежи первого отношения
При правом внешнем соединении сохраняются кортежи второго отношения
Полное соединение сохраняет кортежи обоих отношений.
Поставленную задачу решаем следующим образом.
Select student_name, mark
from student t1 left join study t2
on t2.student_num=t1.student_num
В стандарте SQL есть возможность писать запросы с использованием операции объединения. Для этого используется оператор UNION, в качестве аргументов которого выступают запросы select:
select ...
union
select ...
Агрегирующие функции
Стандарт SQL определяет пять агрегирующих функций, представленных в таблице 3.
Таблица 3. – Функции агрегирования
|
Функция |
Назначение |
|
1 |
2 |
|
count |
Возвращает количество строк в поле, указанном в качестве параметра. |
Продолжение таблицы 3.
|
1 |
2 |
|
sum |
Возвращает сумму значений в указанном поле |
|
avg |
Возвращает среднее значение в указанном поле |
|
min |
Возвращает минимальное значение в указанном поле |
|
max |
Возвращает максимальное значение в указанном поле |
Все функции принимают в качестве аргумента имя поля.
Sum, avg – принимают в качестве атрибутов имена только числовых полей.
Перед именем поля может быть ключевое слово distinct. При этом будут рассматриваться только различные значения указанного поля.
Выражение Count(*) вернёт количество кортежей результирующего отношения.
Организация «Центр стандартизации, сертификации и метрологии»
Основными задачами организации являются:
1) Выдача сертификатов качества производителям и продавцам продукции;
2) Контроль соответствия товаров выданным сертификатам (инспекционный контроль);
3) Поверка измерительных приборов
Выдача сертификатов качества.
Клиент (производитель или продавец) обращается в центр стандартизации с заявлением, которое составляется совместно клиентом и работником центра стандартизации.
Заявление включает следующую информацию:
1) дата подачи;
2) наименование заявителя;
3) код заявителя согласно ОКП
4) адрес заявителя;
5) наименование сертифицируемого товара;
6) вид выпуска (серийное, партия, единичное);
7) список нормативных документов, на соответствие которым будет осуществляться проверка (ГОСТы, ОСТы, ТУ);
8) номер схемы сертификации, которая описывает, каким образом и что будет проверяться (на выбор схемы сертификации оказывает вид выпуска);
9) дополнительные сведения.
Заявлению присваивается некоторый уникальный номер.
После подачи заявления работники центра осуществляют отбор образцов для испытания. Эти образцы отправляются в испытательные лаборатории, из которых приходят протоколы испытаний. Для работы системы нет необходимости знать, что именно содержит такой протокол. Важно только знать его номер, из какой лаборатории он пришел и по какому заявлению проводилось тестирование (тесты зависят от нормативных документов, которые указаны в заявлении).
Работники центра также проводят анализ производства, если он предусмотрен схемой сертификации.
После получения центром стандартизации протоколов сотрудник центра оформляет решение о выдаче сертификата. Это решение содержит следующую информацию:
1) номер и дату заявления;
2) номера протоколов испытаний;
3) решение о выдаче (положительное или отрицательное;
4) данные работника, принявшего заявление.
В зависимости от решения, клиенту выдается либо сертификат, либо отказ в его выдаче. Последний включает в себя номер и дату заявления, те же характеристики клиента, что и в заявлении.
Сертификат имеет уникальный номер, и в нем указывается следующая информация:
1) код центра стандартизации;
2) те же характеристики клиента и товара, что и в заявлении;
3) номера протоколов и названия испытательных лабораторий;
4) дата начала и дата окончания действия сертификата;
5) если схема сертификации предусматривает периодический контроль производства, то указывается периодичность этого контроля.
После этого сертификат регистрируется в государственном реестре.
Возможна ситуация, когда клиент подает не заявление, а декларацию, которая включает в себя следующую информацию:
1) дату подачи;
2) наименование заявителя;
3) код заявителя согласно ОКП
4) адрес заявителя;
5) наименование сертифицируемого товара;
6) вид выпуска (серийное, партия, единичное);
7) список нормативных документов, на соответствие которым будет осуществляться проверка (ГОСТы, ОСТы, ТУ);
8) документы, подтверждающие соответствие нормативным документам.
Декларации и заявления имеют сплошную нумерацию. Схема сертификации при подаче не выбирается, так как она существует только одна.
Если клиентом подается декларация, то отбор и анализ образцов не производится, а сразу принимается решение о выдаче сертификата. Вместо протоколов в решении указываются подтверждающие документы, представленные клиентом. Сертификат, выдаваемый по декларации, и отказ в выдаче сертификата имеют тот же вид, что и для заявления, но сертификат для декларации не может содержать контроль производства.
Контроль соответствия сертификатам.
Если сертификаты, выданные центром стандартизации, предусматривали контроль производства, то с указанной периодичностью на предприятие направляется сотрудник центра, который осуществляет анализ производства. Результаты проверки оформляются протоколом. По итогам проверки сертификат может быть отозван, то есть в государственном реестре он должен иметь соответствующую пометку.
С некоторой периодичностью центр проводит общие проверки продаваемой продукции. В этом случае производится контроль наличия сертификатов на продукцию, сверка их с реестром для выяснения, не является сертификат отозванным. того, производится отбор образцов и их испытания в лабораториях.
Если сертификат отсутствует или отозван, то результаты проверки направляются в правоохранительные органы.
Если образцы не проходят испытания, то сертификат отзывается.
Поверка приборов.
По заявлению клиента сотрудники центра стандартизации отправляются на предприятие, где выполняют поверку приборов и выдает документ на их соответствие нормативам.
Разработать структуру базы данных, создать соответствующие таблицы, заполнить их данными и выполнить задания:
1. Создать запросы:
1) Выбрать номера и даты заявок, в которых товар будет сертифицироваться по схемам 1, 6, 9;
2) Определить, сколько заявок было подано за март 2011;
3) Выбрать информацию о количестве заявок в 2011 г. по клиентам, расположенным в Орловской области;
4) Получить имена (названия) клиентов, подавших заявки с 10.04.2011 по 20.04.2011;
5) Получить сведения о заявках, которые курирует сотрудник Иванов;
6) Найти заявки, в которых продукция будет сертифицироваться по ГОСТам;
7) Найти заявки на сертификацию шоколада;
8) Найти имена (названия) клиентов, сертифицировавших в 2011 году шоколад и получивших отрицательное решение о выдаче сертификата;
9) Перечислить служащих, работающих над несколькими заявками в 2011 году, решение по которым было отрицательным;
10) Перечислить сотрудников, работающих только над одной заявкой;
11) Перечислить все лаборатории, задействованные по заявке № 100 от 12.04.2011;
12) Перечислить сотрудников, принимавших заявки в апреле 2011 года;
13) Перечислить клиентов, у которых по всем заявкам, поданным в 2011 году, получено положительное решение;
14) Изменить номер схемы сертификации на 10 для тех заявок, которые были поданы позднее 01.03.2011 и имели номера 7и 8.
15) Получить сведения по клиентам – для каждого найти даны и номера заявок, поданных в 2011 году и соответствующие им решения по заявке. Результат должен содержать информацию и по тем заявкам, решение по которым еще не принято;
16) Добавить в таблицу служащих поле «оклад». Заполнить значениями (alter, update);
17) создать новую таблицу и поместить в нее информацию о невыданных сертификатах;
2. Создать следующие представления:
1) содержащее информацию о сертификатах (номер сертификата, дата выдачи, название продукции, название клиента, имя служащего, выдавшего сертификат); определить вид представления; можно ли вставлять запись в это представление;
2) содержащее информацию о заявках, поданных позднее определенной даты; добавить в это представление новую запись, не удовлетворяющую условию; что произойдет с представлением и что произойдет с таблицей «Заявки»? Изменить это представление таким образом, чтобы в него нельзя было вставить запись, не удовлетворяющую условию;
3. Обеспечить целостность:
1) chek на тип документа – «декларация или заявление»
2) в таблице «Клиент» определить альтернативный ключ (название + адрес);
3) при удалении сотрудника все его работы принимают неопределенное значение (триггер);
4) при удалении нормативного документа обеспечить стратегию delete/cascade;
5) запретить ввод сертификата, если решение о его выдаче не принято;
4. Написать хранимые процедуры:
1) по названию продукции вывести все нормативные документы (их название);
2) по ФИО сотрудника вывести все виды работ;
3) на заполнение категорий о декларациях и заявителях;
4. Создать двух пользователей. Одному дать права на просмотр и изменение таблиц «Продукция», «Клиент», «Заявка» с правом передачи этих прав другим пользователям. Второму пользователю дать права на просмотр таблицы «Служащий» без права передачи прав. Подключиться к базе данных первым пользователем, передать свои права другому пользователю. Попытаться передать право на удаление записи из таблицы «Служащий» в пользу второго пользователя.
К. Дж. Дейт Введение в системы баз данных. – М.: Издательский дом «Вильямс», 2002. – 1072 с.
Г. Гарсиа-Молина, Дж. Ульман, Д. Уидом Системы баз данных. – М.: Издательский дом «Вильямс», 2003. – 1088 с.
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ
ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
«ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ – УЧЕБНО-НАУЧНО-ПРОИЗВОДСТВЕННЫЙ КОМПЛЕКС
УЧЕБНО-НАУЧНО-ИССЛЕДОВАТЕЛЬСКИЙ ИНСТИТУТ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ
ID_Subj int
Name Char(30)
Name
Value
Teacher
Teacher
Kurator Char(6) (FK)
Tab_num
Nom_zach Char(6)
Surname Char(20)
Name Char(20)
Group Char(8) (FK)
ID_Subject (FK)
Tab_num
Surname
Name
Patronymic
Surname Char(20)
Name Char(20)
Patronymic Char(20)
Tab_num Char(6)
Id_Subject
Group
Табельный номер
Subject
ID_Subject int(FK)
Tab_num Char(6) (FK)
Val int
Student
Subject
Num Char(8)
Фамилия
Имя
Отчество
R
Nom_zach Char(6) (FK)
ID_Subj int (FK)
Tab_num Char(6) (FK)
Dat Date
Оценка
Номер зачетки (FK)
Дисциплина (FK)
Преподаватель (FK)
Дата
Куратор (FK)
Номер
Наименование
Код
Фамилия
Имя
Отчество
Табельный номер
Преподаватель
Фамилия
Имя
Отчество
Табельный номер
Номер зачетки
Фамилия
Имя
Группа (FK)
Оценка
Группа
Дисциплина
Студент