Главная | Обратная связь | Поможем написать вашу работу!

Имя_столбца тип constraint имя_ограничения ограничение

УДК 681.3

ББК 32.973

В75

Издается по решению «Учебно-методического совета Северо-Казахстанского государственного университета
им.М. Козыбаева» (протокол № 2 от 21.10.2005г.)

РЕЦЕНЗЕНТЫ:

Нач. УМС, к.т.н. Шинтемирова А.У.

Зав. каф. физики, к.ф-м.н. Леонтьев П.И.

Воронов А.В.

В75 Проектирование баз данных: методическое пособие по выполнению лабораторных работ.
Петропавловск: СКГУ им. М. Козыбаева, 2005, - 56 с.

Методическое пособие представляет собой лабораторный практикум по предмету «Проектирование баз данных». Оно содержит задания к работам, необходимый теоретический материал и примеры. В пособии рассматриваются процессы логического и физического проектирования. В качестве целевой СУБД используется MS SQL Server 2000.

Для студентов специальностей «Информационные системы» и «Информатика. Может быть использовано при подготовке к лабораторным работам, СРС, СРСП и в дистанционном обучении

УДК 681.3

ББК 32.973

Введение

Данное методическое пособие предназначено для студентов специальностей "Информационные системы в бизнесе, экономике и управлении" и «Информатика» изучающих дисциплину “Проектирование баз данных".

Методическое пособие выполнено в соответствии с государственным стандартом РК и учебным планом специальности "Информационные системы в бизнесе, экономике и управлении" и рассчитано на учебный курс, в котором лабораторные занятия проводятся один раз в неделю. На каждую лабораторную работу отводится 1-2 занятия.

В методическом пособии содержится теоретический и практический материал, необходимый студентам для выполнения лабораторных работ по данному предмету. Пособие разделено на 8 частей, каждая из которых соответствует конкретной лабораторной работе.

Для выполнения лабораторных работ необходимо следующее программное обеспечение:

1. Построение диаграмм IDEF1X – например Borland Together Designer, ERWin4.0.;

2. MS SQL Server 2000

Лабораторная работа №1:
«Логическое проектирование»

Задание:

Провести анализ предметной области;
Выявить классы пользователей, которые будут работать с проектируемой базой данных;
Для каждого класса пользователей построить концептуальную модель в нотации IDEF1X;
Построить интегрированную концептуальную модель.

Теоретический материал:

Целью логического проектирование является обеспечение наиболее естественных для человека способов сбора и представления той информации, которую предполагается хранить в создаваемой базе данных. Результатом процесса логического проектирования является концептуальная (другое название - инфологическая) модель данных. Данная модель строится по аналогии с естественным языком и не привязана ни к одной СУБД.

Процесс логического проектирования продолжается проектированием физическим, в котором происходит выбор СУБД, в которой будет реализована база данных, и концептуальная модель преобразуется в схему данных в соответствии с требованиями целевой СУБД.

Существует несколько подходов к проведению процесса логического проектирования. Мы будем использовать подход, в котором весь процесс разделяется на этапы анализа требований, моделирования требований и интеграции.

Этап анализа требований проводится с целью определения задач, для которых будет использована проектируемая база данных, объектов, которые потребуются для решения выявленных задач и пользователей, которые будут работать с базой данных. Для достижения поставленных целей проводится системный анализ предметной области, интервьюирование пользователей, чтение документации и т.п.

В результате этапа анализа требований для каждого класса пользователей мы получаем необходимый для их работы набор отношений, определяем атрибуты отношений, проводим нормализацию.

Следующий этап – моделирование требований. На данном этапе для каждого класса пользователей строится концептуальная модель. Далее, на этапе интеграции все построенные модели объединяются в одну. Как правило, процесс интеграции представляет собой объединение моделей пользователей, при этом точками стыковки являются общие для них отношения. Следует учитывать возможность появления аномалий именования, которые могут проявляться одним из следующих образов:

Одни и те же объекты разные у разных пользователей имеют разные имена. Аномалия разрешается путем переименования отношений (или атрибутов).
Разные объекты имеют одинаковые имена в разных пользовательских представлениях. Также разрешается путем переименования.
Отношения, описывающие один и тот же объект, в разных пользовательских требованиях имеют разный набор атрибутов. Аномалия разрешается либо путем определения некоторого общего набора атрибутов, либо путем объединения наборов атрибутов и последующей декомпозиции отношения.

Как правило, подобные аномалии могут возникнуть в том случае, когда с разными пользователями работают разные проектировщики, не взаимодействующие друг с другом. В случае работы согласованной команды подобные либо не возникают, либо разрешаются еще на этапе анализа требований.

Рассмотрим методологию построения концептуальной модели. Наиболее известной является модель «Сущность - Связь» (Entity - Relationship), предложенная П. Ченом в середине 70-х годов XX века. Мы будем использовать нотацию IDEF1X, которая основывается на модели Чена и используется в большинстве программных средств проектирования баз данных.

Модель «сущность-связь» предлагает использовать для создания концептуальной модели три основных блока: сущности, атрибуты и связи. Рассмотрим эти блоки более подробно:

Сущность – представляет собой объект реального мира, информация о котором хранится в базе данных. Сущность изображается в виде прямоугольника. В нотации IDEF1X имя сущности записывается над ее изображением. Сущности могут разделяться на слабые и сильные. Слабая сущность находится в зависимости от какой-либо другой сущности и не может существовать, если не существует эта сущность. На практике сущность является независимой если она может быть уникально идентифицирована без определения ее связей с другими сущностями. Слабые сущности изображаются в виде прямоугольника с закругленными краями.

Атрибут – представляет собой какое-либо свойство объекта. Список атрибутов записывается в текстовом виде и содержится внутри изображения сущности. При этом сущность графически делится на 2 части – верхнюю и нижнюю, разделенные горизонтальной чертой. В верхней части записываются атрибуты, являющиеся первичным ключом. Первичным ключом называется атрибут или группа атрибутов однозначно идентифицирующих экземпляр сущности. Атрибуты, входящие в состав первичного ключа имеют идентификатор (PK). Все остальные атрибуты располагаются в нижней части сущности.

Следует отметить, что возникают ситуации, когда на роль первичного ключа могут претендовать сразу несколько наборов атрибутов. В таких случаях в качестве первичного ключа выбирают наиболее подходящий для решения задач предметной области набор. Остальные претенденты объявляются альтернативными ключами. Они располагаются в нижней части сущности и имеют идентификатор (AKn), где n – некоторое натуральное число.

Еще одной возможностью группировки атрибутов является группировка по признаку принадлежности к инверсному входу. Инверсным входом называется атрибут или набор атрибутов, которые не могут уникально идентифицировать экземпляр сущности, но при этом часто используются для доступа к данным. Такие наборы имеют идентификатор (IEn), где n – натуральное число и, в дальнейшем, используются для построения индексов.

Связь – представляет собой функциональную зависимость между сущностями. При этом допускается связь сущности с самой собой. В нотации IDEF1X связи делятся на следующие разновидности:

· Идентифицирующая – если экземпляр дочерней сущности идентифицируется через ее связь с родительской сущностью. Дочерняя сущность при этом всегда является слабой, и ее первичный ключ содержит в себе первичный ключ родительской сущности. Данный тип связи изображается в виде сплошной линии, заканчивающейся точкой со стороны дочерней сущности;

· Неидентифицирующая – если экземпляр дочерней сущности идентифицируется независимо от родительской сущности. Связь изображается в виде пунктирной линии с точкой на стороне дочерней сущности. Первичный ключ родительской сущности входит в состав неключевых атрибутов дочерней сущности.

Замечание: следует отметить, что в любом случае атрибуты дочерней сущности, связанные с соответствующими атрибутами, входящими в состав первичного ключа родительской сущности, объявляются внешним ключом и имеют идентификатор (FK).

· Многие ко многим – данный тип связи используется, если экземпляру одной сущности соответствует несколько экземпляров другой сущности и наоборот. Связь изображается в виде сплошной линии с точками на обоих ее концах.

· Категоризация – связь используется, если некоторая сущность определяет целую категорию объектов одного типа. В этом случае создается родительская сущность для определения категории и отдельные дочерние сущности для каждой категории. Общая часть атрибутов помещается в родительскую сущность, а различающиеся наборы атрибутов размещаются в соответствующих дочерних сущностях. В родительской сущности всегда имеется атрибут, позволяющий различать подкатегории (признак разделения на категории). Данный атрибут называется дискриминатором. Связь категоризации изображается следующим образом: от родительской сущности сплошная линия ведет к специальному элементу категоризации изображаемому в виде круга, подчеркнутого одной или двумя линиями (двумя в случае полной категории и одной – в случае неполной). Рядом с элементом категоризации записывается имя атрибута-дискриминатора. От элемента категоризации проводятся сплошные линии к дочерним сущностям. Точки на концах линий не ставятся.

Кроме типа связи в нотации IDEF1X имеют также мощность (иначе называемую кардинальным числом или кардинальностью). Мощность определяется только для идентифицирующих и неидентифицирующих связей и изображается дополнительным символом возле дочерней сущности.

Отсутствие символа – означает мощность 0, 1, или больше (мощность по умолчанию);
P – означает 1 или больше;
Z – означает 0 или 1;
n, где n – некоторое натуральное число, означает ровно n

Как правило, для проектирования баз данных используются специальное программное обеспечение, позволяющее не только строить диаграммы, но и создавать спецификации, генерировать схему данных, создавать различные физические объекты целевых СУБД – такие как представления, сценарии, триггеры. Одним из таких программ является ERWin. Далее рассмотрим пример создания концептуальной модели в среде ERWin 4.0.

После запуска программы ERWin, Вам будет предложено либо создать новую модель, либо открыть уже существующую. Выбрав создание новой модели, мы попадаем в новое диалоговое окно «выбор типа модели». В нем предложено 3 варианта моделей:

Логическая – дальнейшая работа будет происходить только с логической моделью. Физическое проектирование выполняться не будет;
Физическая – выбирается в случае, если целевая СУБД уже определена и проектирование начнется непосредственно с разработки схемы. В данном случае требуется дополнительно выбрать (в раскрывающихся списках) целевую СУБД и ее версию;
Логико-физическая – выбирается в том случае, если будет производиться полный цикл проектирования. Также как и в предыдущем случае требуется выбрать целевую СУБД. Для выполнения лабораторной работы выбираем этот вид моделирования.

После всех описанных выше действий на экране появится основное окно Erwin (рис.1)

Рис. 1: Основное окно ERWin

Данное окно можно условно разделить на несколько основных частей. Наибольшее пространство занимает окно разработки модели, на рисунке оно имеет название «Display1». Окно может содержать несколько вкладок, на каждой из которых может вестись разработка модели. В лабораторной работе, мы можем поместить на вкладке концептуальные модели для различных классов пользователей.

Слева расположен диспетчер объектов, в котором перечислены группы объектов, из которых может состоять модель. Мы будем использовать группы «domains» (домены) и «entities» (сущности). Выбрав некоторый объект из группы, мы можем работать с ним, используя команды контекстного меню.

В верхней части расположено меню и панели инструментов. ERWin обладает богатым набором панелей инструментов, позволяющих выполнять множество разнообразных действий. Следует отметить, что при переключении на физический уровень модели (с помощью раскрывающегося списка на панелях инструментов) пункты меню и некоторые панели инструментов изменятся.

Для построения модели нам необходимо предпринять ряд шагов. Во-первых, размещаем в окне разработки модели сущности, определенные нами в результате анализа требований. Далее, для каждой сущности определяем первичные ключи. Данное действие можно произвести одним из следующих способов:

В группе «Entities» выбрать нужную сущность, раскрыть список входящих в нее объектов и, выбрав Attributes, выполнить команду New контекстного меню. После этого дать имя новому атрибуту и, дважды щелкнув по нему перейти в окно свойств сущности, где необходимо отметить флажок Primary key;
Дважды щелкнуть по изображению сущности в окне разработки модели. Откроется окно свойств сущности. В нем щелкаем по кнопке New и указываем имя атрибута и определяем, к какому домену он будет принадлежать. после этого отмечаем флажок Primary key. Данный способ более нагляден и быстр, поэтому автор предлагает воспользоваться данным способом при выполнении лабораторной работы.

После того, как определены все первичные ключи для всех сущностей, окно разработки модели может выглядеть примерно так (рис.2)

Рис. 2: Окно разработки модели

В качестве примера проектируется база данных, содержащая информацию, используемую некоторой строительной компанией при проведении работ. В базе должны храниться данные о работниках, зданиях и выполняемых (выполненных) работах. Используется один класс пользователей – менеджер компании. Предложено 3 сущности – worker, building и assignment в которых будет храниться необходимая информация. На рис.2 видно, что мы определили в качестве первичных ключей для сущностей worker и building коды работника и здания соответственно.

Сущность assignment пока не имеет первичных ключей. Это связано с тем, что данная сущность является слабой и будет идентифицирована через первичные ключи 2-х других сущностей.

Следующим шагом в создании логической модели является установление связей между сущностями. Для этого мы выбираем на панели инструментов кнопку с нужным видом связи и производим два щелчка левой кнопкой мыши – вначале по родительской сущности, а потом по дочерней.

На рис.3 показана модель рассматриваемого примера после установления связей.

Рис. 3: Установка связей между сущностями

Нетрудно заметить, что были использованы идентифицирующие связи между сущностью assignment и сущностями worker и building. В результате проведения этого типа связи, изображение сущности изменяется (на изображение слабой сущности) и в нем автоматически появляются первичные ключи wrkr_id и bldg_id (т.к. слабая сущность идентифицируется через сои родительские сущности). Следует отметить, что эти же атрибуты одновременно являются и внешними ключами для связи с родительскими отношениями, что отображено идентификаторами (FK). В случае неидентифицирующей связи мы бы увидели внешние ключи в разделе неключевых атрибутов сущности. Т.о., ERWin автоматически обеспечивает ссылочную целостность по внешним ключам.

Последним шагом создания модели является добавление неключевых атрибутов сущностей. Это можно выполнить аналогично тому, как мы создавали первичные ключи (с той разницей, что не требуется отмечать флажок primary key). На рис.4 изображен окончательный вид логической модели, рассматриваемой в модели.

Рис. 4: Окончательный вид модели

После того, как логическое моделирование завершено, начинаем процесс физического моделирования. В ERWin для переключения на физическую модель выбираем значение “Phisical” в раскрывающемся списке на панели инструментов.

Вопросы к допуску

1. Дайте определение процессу проектирования базы данных;

2. Какова основная цель логического проектирования?

3. Перечислите и кратко охарактеризуйте этапы логического проектирования;

4. Что называется сущностью?

5. В чем отличие между сильными и слабыми сущностями?

6. Дайте определение атрибуту сущности;

7. Что называют связью? Какие характеристики может иметь связь?

8. Как изображают сущности, атрибуты и связи в классических диаграммах Чена?

Контрольные вопросы

1. Какие виды моделей поддерживает ERWin?

2. Какие объекты позволяет создавать ERWin в логической модели?

3. Какими способами можно создавать сущности в ERWin?

4. На какие части делится сущность в ERWin?

5. Какими способами можно добавить атрибут к сущности?

6. Как указать атрибуты, входящие в состав первичного ключа?

7. Перечислите используемые в ERWin группировки атрибутов;

8. Какого рода атрибуты входят в состав группы AKn?

9. Для чего используют группу IEn?

10. Дайте определение идентифицирующей связи. В каких случаях используют этот вид связи?

11. Дайте определение неидентифицирующей связи. В каких случаях используют этот вид связи?

12. В каких случаях используют связи «многие ко многим»?

13. Что такое «связь категоризации»? Как создавать подобные связи?

14. Какие виды кардинальностей поддерживает ERWin?

Лабораторная работа №2:
«Физическое проектирование: построение схемы данных»

Задание: Провести преобразование концептуальной модели в физическую.

Произвести переименование отношений и атрибутов в соответствии с правилами именования в целевой СУБД;
Определить типы данных для атрибутов;
Сгенерировать SQL-код.

Теоретический материал:

Физическое проектирование, в отличие от логического, всегда направлено на разработку базы данных для конкретной СУБД. Поэтому первым шагом процесса физического проектирования является выбор целевой СУБД. Дальнейшие шаги процесса будут зависеть от возможностей выбранной СУБД, принятых в ней правил и соглашений и диалекта SQL, который в ней используется.

В данном лабораторном практикуме в качестве целевой СУБД рассматривается MS SQL Server 2000. Соответственно, в этой и следующих лабораторных работах мы будем придерживаться принципов работы в данной СУБД.

Выполняя проектирование базы данных в среде ERWin, для перехода к физической модели требуется переключиться на физический уровень в раскрывающемся списке (в том случае если изначально была выбрано логико-физическое проектирование). ERWin автоматически выполнит начальное преобразование логической модели в физическую. Рассмотрим, какие действия входят в это преобразование:

Переименование сущностей. В физической модели сущностям будут соответствовать таблицы базы данных. Создавая логическую модель, мы могли давать сущностям любые имена. Но на уровне физической модели, имена таблиц должны подчиняться правилам именования в целевой СУБД. В MS SQL Server 2000 имена таблиц могут содержать буквы, цифры и символ подчеркивания, при этом первым символом должна быть буква. Буквы могут быть как латинские, так и русские, однако рекомендуется использовать только латиницу. Эта рекомендация связана с тем, что в некоторых случаях происходит некорректная обработка таблиц, имеющих имена, написанные русскими буквами.
Переименование атрибутов. На физическом уровне атрибутам соответствуют столбца таблицы. К именам столбцов в MS SQL Server 2000 применяются те же правила, что и к именам таблиц (эти правила распространяются на все объекты MS SQL Server 2000).
Развязка связей типа «многие ко многим». Если в процессе логического проектирования некоторые сущности были связаны друг с другом связью данного типа, ERWin автоматически выполнит ее преобразование в идентифицирующие связи. При этом оба отношения, участвующие в связи «многие ко многим» будут выступать в роли родительских, а дочерним будет новое (автоматически созданное) отношение, состоящее из атрибутов, входящих в составы первичных ключей родительских отношений. Других атрибутов у него не будет. Такие отношения в теории баз данных принято называть связными. Фактически, на физическом уровне рассматриваемые сущности будут связаны между собой не напрямую, а через автоматически сгенерированное связное отношение.

Кроме указанных действий, ERWin также задаст для столбцов типы данных. Однако это происходит по определенной в нем схеме соответствия типов данных доменам и не всегда оптимально. На практике обычно требуется перепроверить схему данных и задать типы данных самостоятельно. Кроме типов данных мы также можем задать ограничения. Рассмотрим, какие типы данных и ограничения можно использовать в MS SQL Server 2000.

Типы данных для хранения символьных строк:

Char (n), где n – натуральное число. Хранит символьные строки фиксированного размера (n символов). Если символов с сроке меньше n, к ней добавляются пробелы. Позволяет хранить до 8 кб текста;
Varchar (n) – используется для хранения строк переменной длины, не более n символов. Также позволяет хранить до 8 кб текста;
Text – используется для хранения больших объемов текста;
nChar(n), nVarchar (n), nText – типы данных, предназначенные для хранения текста в формате Unicode (Предыдущие типы хранили текст в формате ASCII)

Числовые типы данных:

tinyint – целые числа [0, 255]. Отводится 1 байт на хранение данных этого типа;
smallint – целые числа [-32768, 32767]. Отводится 2 байта;
int – целые числа [-2147483648, 2147483647]. Отводится 4 байта;
bigint – целые числа. Отводится 8 байт;
real – числа с плавающей запятой. [-3.4 E38, 3.4 E38];
float – числа с плавающей запятой [-1.79E308, 1.79 E308];
decimal (n,m) – числа с фиксированной запятой. n – количество цифр в числе, m – количество цифр после запятой. Этот тип эквивалентен типу numeric из SQL-92;
smallmoney и money – эти типы используются для хранения денежных сумм. Отличаются от decimal тем, что после запятой может быть не более 4 цифр.

Типы даты и времени:

smallDateTime – хранит значения даты и времени. Можно использовать даты из промежутка 01.01.1990 – 06.06.2079. Занимает 4 байта;
DateTime - хранит значения даты и времени. Можно использовать даты из промежутка 01.01.1753 – 31.12.9999. Занимает 8 байт;

Другие типы данных:

Binary, varbinary, image – используются для хранения двоичных строк. Модно провести соответствие с char, varchar, text;
Uniqueidentifier – используется для хранения GUID (глобальных уникальных идентификаторов). В базе данных может быть только один столбец такого типа;
SQL-variant. Соответствует типу Variant в Visual Basic. Занимает 16 байт. Используется в тех случаях, когда возникает затруднения в выборе подходящего для столбца типа данных.

Типы данных позволяют определить, какого рода информация будет храниться в столбце. Для того, чтобы задать тип данных в ERWin, нужно дважды щелкнуть мышкой по таблице. Откроется диалоговое окно, в котором мы можем выбирать столбцы данной таблицы и задавать для них различные параметры, в том числе и типы данных. Кроме типа данных, для столбца можно задать ограничения. В MS SQL Server 2000 ограничения представляют собой механизм, с помощью которого можно контролировать значения хранящиеся полях строк. Ограничения делятся на ограничения уровня столбцов и ограничения уровня таблицы. Рассмотрим их более подробно:

Ограничения уровня столбцов – позволяют контролировать значения, хранящиеся в полях конкретного столбца.

NULL – разрешает хранение пустых (неопределенных) значений. Например, в столбце phone (телефон) далеко не все поля можно заполнить, т.к. не у всех есть телефон;
NOT NULL – запрещает хранение неопределенных значений;
DEFAULT – позволяет задать значение по умолчанию. Если в поле не вводится никаких значений, то в него подставляется значение по умолчанию. Например: default 2 или default ‘Office’;
UNIQUE – обеспечивает отсутствие повторяющихся значений в полях столбца;
PRIMARY KEY – определяет первичный ключ (на уровне столбцов используется в случае атомарного первичного ключа);
CHECK (logical_expression) – ограничение представляет собой логическое условие (logical_expression), при выполнении которого в поле разрешено вставлять значение. Является мощным механизмом контроля значений хранящихся в полях. Например: check (age > 18), check (status between 1 and 4).

Ограничения уровня таблицы – используются, если необходимо задать ограничение, распространяющееся на всю таблицу.

UNIQUE (column_list) – запрещает повтор значений в перечисленных столбцах (column_list). Используется в случаях когда необходимо обеспечить уникальность не значений каждого столбца в отдельности, а совокупности хранящихся в ней значений. Например: unique (f_name, l_name) – обеспечивает уникальность фамилии и имени. Пара значений «Иван Петров» не должна повториться в таблице. Если бы мы задали это ограничение для каждого из указанных столбцов в отдельности (на уровне столбцов), то мы не смогли бы вводить такие пары значений как «Иван Сидоров» или «Сергей Петров»;
PRIMARY KEY (column_list) – данное ограничение используется на уровне таблиц, если она имеет составной первичный ключ. Атрибуты, входящие в состав первичного ключа должны быть перечислены в column_list;
FORIGN KEY – позволяет определить внешний ключ. Атрибуты, являющиеся внешним ключом в одной таблице обязательно должны быть связаны с соответствующими им атрибутами, входящими в первичный ключ другой таблицы (правило ссылочной целостности). Ключевое слово REFERENCES позволяет указать, с какой таблицей устанавливается связь. Если столбцы входящие в первичный ключ этой таблицы имеют те же имена, что и столбцы внешнего ключа, то их указывать необязательно. В противном случае их указывают в скобках после имени таблицы. Синтаксис ограничения имеет следующий вид:

FOREIGN KEY (список_столбцов) REFERENCES имя_таблицы (список_столбцов) команда

В первый список включаются те столбцы, которые входят во внешний ключ (в случае одного столбца скобки не ставятся). Столбцы перечисляются через запятую. Во второй список включаются соответствующие им столбцы связываемой таблицы, входящие в ее первичный ключ (как было сказано выше, этот список может быть опущен). В случае одного такого столбца скобки все равно ставятся. Команда позволяет определить, какие действия будут предприняты в случае операций изменения или удаления данных. Может принимать следующие значения:

ON DELETE SET NULL – устанавливает в null связанные столбцы дочерней таблицы, в случае удаления соответствующих им значений из родительской таблицы;
ON DELETE CASCADE – разрешает каскадное удаление;
ON DELETE NO ACTION – не предпринимает никаких действий в случае удаления;
ON UPDATE CASCADE – разрешает каскадное обновление;
ON UPDATE NO ACTION - не предпринимает никаких действий в случае обновления.

Кроме работы с таблицами, процесс физического проектирования включает в себя разработку представлений, сценариев, хранимых процедур и триггеров. Все это можно создать с помощью ERWin, однако следует учитывать, что в нем можно создавать только достаточно простые представления, а для хранимых процедур и триггеров он использует собственный язык программирования, отличный от T-SQL. По этой причине мы будем рассматривать разработку указанных объектов непосредственно в среде MS SQL Server 2000. Конечный результат работы Erwin можно представить в виде sql-кода, который он генерирует автоматически (по команде schema generation из меню tools).

Вопросы к допуску

1. Какова цель физического проектирования базы данных?

2. Что такое «целевая СУБД»? Как в ERWin указать целевую СУБД?

3. Что входит в процесс физического проектирования базы данных?

4. Какие действия называют начальным преобразованием логической модели в физическую?

5. Какие объекты создаются на этапе физического проектирования? Кратко охарактеризуйте их;

Контрольные вопросы

Как перейти к физическому представлению модели в ERWin?
Какие объекты можно создавать в ERWin в физических моделях?
Какие преобразования осуществляет ERWin при переходе от логической модели к физической?
Как происходит развязка связей типа многие ко многим?
Как указать нужный тип столбца в среде Erwin?
Какие типы данных используются в MS SQL Server 2000 для хранения числовых данных? Чем они различаются?
Какие типы данных используются в MS SQL Server 2000 для хранения текстовых данных? В чем отличие между ними?
Что называют ограничением? Какие виды ограничений поддерживаются в MS SQL Server 2000?
Перечислите и кратко охарактеризуйте ограничения уровня столбцов. В каких случаях они используются?
Перечислите и кратко охарактеризуйте ограничения уровня таблицы. В каких случаях они используются?

Лабораторная работа №3:
«Создание таблиц базы данных в MS SQL Server 2000»

Задание:

Создать таблицы с помощью T-SQL, используя Query Analyzer;
Продемонстрировать умение работать с таблицами в Query Analyzer – просмотр, редактирование и удаление таблиц с помощью T-SQL;
Продемонстрировать работу с таблицами в Enterprise Manager – создание, редактирование, просмотр.

Теоретический материал:

MS SQL Server 2000 представляет собой сервер баз данных промышленного уровня. Он предоставляет большое количество разнообразных сервисов для работы с базами данных. Для работы с базами данных и их объектами в MS SQL Server 2000 обычно используют такие инструменты как Enterprise Manager и Query Analyzer. Enterprise Manager предоставляет возможность визуальной работы с базами данных и их объектами, и содержит в себе различные мастера и конструкторы. Query Analyzer представляет собой среду разработки программного кода на языке Transact SQL (в дальнейшем - T-SQL).

Таким образом, мы имеем возможность работать с объектами баз данных либо используя код T-SQL, либо используя мастера и конструкторы. В лабораторном практикуме нами будут рассматриваться и тот и другой способы.

Рассмотрим процесс работы с таблицами на языке T-SQL. Следует отметить, что, используя CASE-средства проектирования, такие как ERWin мы можем сгенерировать в нем sql-код для создания таблиц по созданной схеме данных. Однако далеко не всегда подобный инструментарий генерирует валидный и оптимальный код. Поэтому мы рассмотрим процесс создания таблиц на языке T-SQL с самого начала. При выполнении лабораторной работы, можно не создавать таблицы заново, а отредактировать созданный в ERWin программный код.

Для создания новых объектов в языке T-SQL используется команда CREATE. Данная команда может иметь разный синтаксис для разных объектов. Рассмотрим, как использовать команду create для создания таблиц.

CREATE TABLE имя_таблицы

(

описание столбца1,

описание столбца2,

…

описание столбцаN,

описание ограничения уровня таблицы1,

…

описание ограничения уровня таблицыN

)

Нетрудно заметить, что при описании таблицы мы описываем каждый из ее столбцов и ограничения табличного уровня. Все элементы описания отделяются друг от друга пробелами. Описание столбца включает в себя имя столбца, его тип и ограничения уровня атрибутов, применяемые к данному столбцу. Синтаксис описания столбца может выглядеть следующим образом:

имя_столбца тип constraint имя_ограничения ограничение

Имя столбца и его тип обязательно включаются в описание. Ограничению может предшествовать ключевое слово CONSTRAINT и имя ограничения, однако это не является обязательным и служит для возможной дальнейшей работы с данным ограничением (например, удалять ограничения при редактировании таблицы командой alter table можно только по их имени). Каждому столбцу может быть назначено произвольное количество ограничений уровня атрибутов, от нуля и более. Все ограничения также отделяются друг от друга пробелами. Признаком описания нового столбца (или описания ограничений уровня таблицы) служит запятая.

В T-SQL допускается использование вычисляемых столбцов в таблицах. В таких случаях синтаксис описания столбца выглядит следующим образом:

Имя_столбца AS выражение

Выражение может содержать в себе имена других столбцов, арифметические операции и встроенные функции T-SQL.

Описание ограничений табличного уровня также может начинаться с ключевого слова CONSTRAINT и имени ограничения (которые не являются обязательными) после чего следует непосредственно само ограничение.

В связи с тем, что типы столбцов и ограничения были подробно описаны в предыдущей лабораторной работе, мы не будем повторно описывать их. Заметим только, что кроме встроенных типов можно использовать пользовательские. Для этого используют следующую команду:

CREATE DOMAIN имя_пользовательского_типа имя_встроенного_типа ограничения

Таким образом, пользовательский тип строится на основе некоторого встроенного типа, на который наложены ограничения уровня атрибутов. Рассмотрим пример:

CREATE DOMAIN item_id char(5) NOT NULL CHECK (item_id like ‘[A-Z][A-Z][0-9][0-9][0-9]’)

В данном примере, мы создали пользовательский тип под именем item_id, который базируется на встроенном типе char(5), т.е. представляет собой текстовую строку из 5 символов, при этом первые 2 символа являются буквами латинского алфавита (заглавными), а последние три – цифры от 0 до 9. Кроме того, столбцы типа item_id не могут содержать пустых значений, что задается ограничением NOT NULL.

Завершим изучение команды Create table примеров sql-кода создания таблиц, которые мы проектировали на первых 2-х лабораторных работах:

CREATE TABLE worker

(

wrkr_id item_id PRIMARY KEY,

wrkr_name varchar(30) NOT NULL UNIQUE,

h_rate smallmoney,

skill_type varchar(10),

supv_id item_id

)

CREATE TABLE building

(

bldg_id item_id PRIMARY KEY,

address varchar(40),

bldg_type varchar(9) DEFAULT ‘Офис’ CHECK (bldg_type in ‘Офис’, ‘Склад’, ‘Жилой дом’, ‘Магазин’),

status decimal(1) DEFAULT 1 CHECK (status between 0 and 4)

)

CREATE TABLE assignment

(

wrkr_id item_id,

bldg_id item_id,

start_day datetime,

num_days smallint CHECK (num_days > 0),

CONSTRAINT assignment_pk PRIMARY KEY (wrkr_id, bldg_id),

CONSTRAINT assignment_fk_wrkr FOREIGN KEY wrkr_id REFERENCES worker ON DELETE SET NULL,

CONSTRAINT assignment_fk_bldg FOREIGN KEY bldg_id REFERENCES building ON DELETE SET NULL

)

После того, как таблицы созданы можно отредактировать их структуру или удалить их. Удаление производиться с помощью команды: DROP TABLE имя_таблицы. Команда изменения структуры таблицы имеет более сложный синтаксис:

ALTER TABLE имя_таблицы

{

alter column описание_столбца,

add описание_столбца,

drop column имя_столбца,

add описание_ограничения,

drop constraint имя_ограничения,

enable | disable trigger имя_триггера

}

Рассмотрим более подробно те команды, которые используются внутри alter table:

· Alter column – используется для изменения существующих в таблице столбцов. После нее следует новое описание некоторого столбца, при этом имя столбца сохраняется;

· Add – добавляет новый столбец в таблицу. Может быть добавлен как обычный, так и вычисляемый столбец. Кроме того, с помощью данной команды можно добавить новое ограничение уровня таблицы;

· Drop column – удаляет столбец с указанным именем;

· Drop constraint – удаляет ограничение с указанным именем;

· Enable trigger или Disable trigger – позволяют включить или временно отключить выполнение указанного триггера. Распространяется только на те триггеры, которые связаны с данной таблицей.

Для работы с данными T-SQL использует команды SELECT, INSERT, UPDATE и DELETE, которые подробно рассмотрены в [

12 Следующая ⇒

Воспользуйтесь поиском по сайту: