Проектирование и создание многопользовательских баз данных

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

«…целесообразно применение лабораторных работ по информатике не на стадии закрепления пройденного материала, а в процессе усвоения новой темы…Главной задачей преподавателя в этом случае становится подготовка материала для самостоятельного исследования и усвоения обучаемым новой темы… Оказалось, методом лабораторных практикумов можно охватить постепенно почти весь курс информатики, вводя его постепенно и плавно» [6].

Итак, наиболее оптимальной для нашего курса явилась форма лабораторного практикума в сочетании с индивидуальной и парной формой учебной работы студентов.

Средство является одним из равноправных компонентов дидактической системы наряду с другими её звеньями: целями, содержанием, формами, методами, деятельностью обучающего и деятельностью обучаемого [4]. Все эти звенья взаимосвязаны, и изменения в одном из них обусловливают изменения во всех других. Как новое содержание требует новых форм его организации, так и новое средство предполагает переориентацию всех других компонентов дидактической системы.

Эффективными средствами обучения способам и программным средствам разработки информационных систем распределенной обработки данных являются технические средства обучения (компьютерное оборудование), программное обеспечение и учебно-методические материалы. «В качестве базового процесса для привлечения компьютерных средств обучения мы рассматриваем процесс решения задач и проблем. Успешность решения задач и проблем, а также процессов моделирования этих процедур, сведения реальных процессов мышления к форме решения задач и проблем зависит от приобретения способностей к схематизации, обобщению, конкретизации, абстрагированию. …Что необходимо решающему задачу и проблему от технического средства? Если машина обеспечивает оперирование с частями и целостностью выраженного на экране изображения, оставляет следы попыток, облегчает переход к любому фрагменту зафиксированного размышления для его квалификации, для реконструкции формы движения в поиске, то она становиться эффективным средством достижения целей мыслительного поиска» [1].

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

Задача выбора системы управления базой данных, содействующей успешному усвоению материала студентами, представляется нами особенно важной.

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

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

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

1) Определить требования к знаниям и умениям будущего специалиста по основным методам проектирования, создания и управления БД;

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

a) Выявить основные понятия, которые должны быть сформированы у студентов в процессе обучения

b) Определить те виды деятельности, которые должны быть освоены студентами в процессе обучения

3) Разработать содержание и структуру лабораторного практикума.

В соответствии с требованиями Образовательного стандарта Высшего образования Специальности Н.08.01.00 «Прикладная математика» будущий специалист должен:

овладеть знаниями и умениями в области современных программных средств, в том числе и средств разработки многопользовательских баз данных

иметь представление об основных моделях баз данных, способах их построения и современных программных средствах их создания

уметь выполнять оптимизацию схем реляционных баз данных. [22]

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

компьютеризованная информационная система,

предметная область системы и её объекты,

распределённая база данных и СУБД,

распределённая обработка данных,

трёхуровневая архитектура СУБД, языки БД, классификация моделей данных,

инфологическая модель «сущность-связь»,

сущность, связь, атрибут,

категоризация сущностей,

типы связей,

ограничения целостности,

представления,

запросы,

хранимые процедуры,

курсоры,

триггеры,

параллельная обработка транзакций,

безопасность данных и привилегии,

экспорт и импорт данных, каскадное обновление и удаление данных.

Эти понятия имеют значение как для теоретической, так и для практической подготовки студентов, дают представление об этапах создания ИС и позволяют разрабатывать ИС средствами конкретной СУБД.

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

v Разработка концептуального представления и логической структуры базы данных; построение ER-диаграммы и реляционной модели

v Физическое проектирование и выбор конкретной целевой СУБД (MS SQL Server)

v Создание базы данных и пользовательских типов данных в SQL Server.

v Создание объектов базы данных: таблиц, индексов и первичных ключей таблицы. Использование ограничений.

v Использование диаграмм для разработки структуры базы данных. Ввод данных в таблицу. Модификация данных таблиц.

v Создание запросов на выборку и модификацию данных; сортировка результатов запроса; подчинённые и вложенные запросы.

v Создание представлений, триггеров, хранимых процедур.

v Администрирование баз данных; управление привилегиями и правами доступа; использование системного каталога; резервное копирование данных.

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

Понятие	Смысл	Вид деятельности	№ лаб. раб.
Сущность	реальный или представляемый объект, информация о котором должна сохраняться и быть доступна	построение ER-диаграммы	Л.р. №1
Связь	графически изображаемая ассоциация, устанавливаемая между двумя сущностями
Атрибут	любой элемент, который служит для уточнения, идентификации, классификации, числовой характеристики или выражения состояния сущности
База Данных	некоторый набор таблиц, связанных между собой определенной связью для выполнения различного рода задач.	Создание базы данных	Л.р. №2
Журнал транзакций	файл, в котором хранится информация о процессе работы с транзакциями
Пользовательский тип данных	тип данных, используемый при определении специфического или часто употребляемого формата	Создание пользовательских типов данных
Первичный ключ	уникальное поле (или несколько полей), однозначно определяющее записи таблицы базы данных	Создание таблиц и первичных ключей, задание ограничения целостности таблиц	Л.р. №1,3
Внешние ключи	поля таблицы, которые соответствуют первичным ключам из других таблиц	Задание ограничений ссылочной целостности	Л.р. №1,3
Индекс	упорядоченный список полей или групп полей в таблице, используемый для ускорения операции поиска записей в таблице, а также выполнения других операций, использующих поиск	Создание индексов	Л.р. №3
Ограничения	условия, которым должны удовлетворять хранимые в базе данные	Задание ограничений целостности	Л.р. №3
Стандартное значение (значение по умолчанию)	значение, которое будет присвоено колонке таблицы при вставке строки, если в команде явно не указано значение для этой колонки	Задание ограничений целостности атрибутов	Л.р. №3
Диаграмма	объект SQL- Server, который в графическом виде отображает все объекты базы данных и связи между ними	Создание диаграммы базы данных и таблицы в дизайнере диаграмм	Л.р. №4
Запрос	Специальным образом описанное требование, определяющее состав производимых над базой данных операций по выборке или модификации хранимых данных	Создание запросов на выборку и модификацию данных	Л.р. №5
Представление	некоторое подобие таблицы, содержание которого выбирается из других таблиц с помощью выполнения запроса, причем, при изменении значений в таблицах, данные автоматически меняются и в представлении	Создание представлений	Л.р. №6
Хранимая процедура	подпрограмма (процедура) обработки данных, хранящаяся и выполняемая на компьютере-сервере	Создание хранимых процедур
Триггеры	это специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении и удалении данных из таблицы	Создание триггеров
Резервное копирование	создание резервной копии базы данных	Создание резервной копии и восстановление базы данных	Л.р. №7
Учетная запись	Специальное идентификационное имя пользователя	Создание учётной записи
Привилегии	права пользователя на проведение тех или иных действий над определенным объектом базы данных	Назначение серверных ролей для создаваемой учётной записи

Структура лабораторного практикума следующая:

Название темы лабораторной работы, цель работы, основные сведения (теоретический материал), комплекс примеров и упражнений, контрольные вопросы, порядок выполнения работы (задания для самостоятельной и индивидуальной работы).

ГЛАВА 3 Учебно-методические материалы (лабораторный Практикум по MS SQL Server 7.0)

ЛАБОРАТОРНАЯ РАБОТА №1

Тема: Проектирование базы данных.

Цель работы: Изучить методы проектирования БД на основе инфологического моделирования. Ознакомиться с основными этапами разработки концептуального представления и логической структуры базы данных.

ОСНОВНЫЕ СВЕДЕНИЯ

Создание инфологической модели методом «сущность-связь». Создание ER-диаграмм

Предварительный этап создания инфологической модели предусматривает выполнение системного анализа и словесного описания информационных объектов предметной области. На первом этапе проектирования создается концептуальная схема БД, которая затем преобразуется к реляционной схеме. В результате создается реляционная БД в 3-ей нормальной форме. Рассмотрим одну из наиболее важных и распространенных семантических моделей данных - модель “Сущность связь” (ER-модель). Основные понятия ER- модели: сущность, связь и атрибут.

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

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

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

Примеры:

5

Конец связи имеет, означает, что в каждом отделении компании работает 1-5 и более сотрудников. Конец связи с именем работает позволяет соединить с одним сотрудником не более 1-го отделения, то есть сотрудник не может работать одновременно в нескольких отделениях. Трактовка изображенной диаграммы следующая: каждый сотрудник может работать в одном из отделений компании (не все сотрудники обязательно принадлежат отделениям, так как у компании может быть свой офис и административный аппарат), каждое отделение обязательно имеет 1-5 и более сотрудников.

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

Некоторый набор атрибутов назначается уникальным идентификатором (ключом).

Как и в реляционных схемах БД, так и в ER-схемах вводится понятие нормальных форм, причем их смысл очень близко соответствует смыслу реляционных нормальных форм.

Определения 3-х первых нормальных форм (НФ):

В 1 НФ ER-схемы устраняются повторяющиеся атрибуты или группы атрибутов. Во 2 НФ устраняются атрибуты, зависящие от части уникального идентификатора. Эта часть уникального идентификатора определяет отдельную сущность.

В 3 НФ устраняются атрибуты, зависящие от атрибутов, не входящих в уникальный идентификатор. Они являются основой отдельной сущности.

В ER-модели допускается принцип категоризации сущностей.

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

Эти элементы ER-модели делают ее более мощной, но существенно усложняют ее использование. Например, сущность может быть расщеплена на 2 или более взаимоисключающих подтипа, каждый из которых включает общие атрибуты. В подтипах могут определяться собственные атрибуты и связи.



- кружок категоризации, U - объединение

Преобразование ER-модели в реляционную схему осуществляется в соответствии со следующими правилами:

каждая простая сущность превращается в отношение. Имена отношений могут отличаться от имен сущностей, так как могут быть ограничены требованиями конкретной СУБД;

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

компоненты уникального идентификатора сущности превращаются в первичный ключ отношения;

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

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

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

сбор информации об объектах, выставляемых на продажу;

представление данных в общую БД;

организация просмотра объектов потенциальными покупателями;

составление договоров на продажу недвижимости.

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

Описание предметной области

Выделим объекты предметной области:

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

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

Данные обо всей выставленной на продажу недвижимости можно получить в любом отделении компании. Информация, описывающая каждый объект недвижимости, включает номер объекта, адрес его местонахождения (почтовый индекс, город, район, улица, дом и квартира), тип объекта, количество комнат в нём, отпускную цену, а также имя и адрес владельца этого объекта. Отпускная цена ежегодно пересматривается. Каждый объект недвижимости имеет единственного владельца.

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

Потенциальный покупатель обращается в отделение компании, в котором ему могут предложить осмотреть разные объекты недвижимости. Информация, сохраняемая по каждому проведённому осмотру объекта, включает имя и адрес клиента, номер и адрес осмотренного объекта недвижимости, дату осмотра, а также комментарии по результатам осмотра. Клиент может осматривать любое количество объектов недвижимости.

О каждом клиенте хранится следующая информация: фамилия и имя, адрес, номер телефона, предпочтительное количество комнат в покупаемой квартире, а также максимальная цена, которую клиент согласен уплатить. Каждый клиент получает личный номер, уникальный для всех отделений компании.

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

Необходимо предусмотреть следующие ограничения на информацию в системе:

В каждом отделении компании работает, по крайней мере, 5 сотрудников, а максимальное их количество не ограничено.

Каждый сотрудник может отвечать не более чем за 10 объектов недвижимости одновременно.

Компания требует сохранять данные об уволившемся сотруднике в течение года.

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

В данной информационной системе должны реализовываться определённые задачи, за выполнение которых несут ответственность сотрудники компании. А именно:

Создание и корректировка записей с данными о сотрудниках каждого отделения.

Обновление сведений о зарплате некоторого сотрудника.

Создание отчёта со сведениями о сотрудниках каждого отделения.

Удаление сведений об уволившемся сотруднике из базы данных и передача ответственности за все курируемые им объекты недвижимости другому сотруднику.

Создание и корректировка записей с данными о выставленных на продажу объектах недвижимости в конкретном отделении компании.

Создание отчёта с данными о выставленных на продажу объектах недвижимости в данном отделении компании.

Создание и корректировка записей с описанием потенциальных покупателей и их требований.

Поиск всех объектов недвижимости, удовлетворяющих требованиям покупателя.

Поиск возможного покупателя для вносимого в базу данных объекта недвижимости.

Создание и корректировка записей со сведениями об осмотре объектов недвижимости.

Создание и корректировка записей со сведениями о заключённых договорах.

Распечатка договора.

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

Описание сущностей и типов связей

Определим основные типы сущностей исходя из описания предметной области.

Отделение (BRANCH)

Каждое отделение имеет следующий набор атрибутов: номер отделения, адрес (почтовый индекс, город, улица, дом), номер телефона и номер факса.

Сотрудник (STAFF)

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

Объект недвижимости для продажи (PROPERTY_FOR_SALE)

Характеризуется такими атрибутами как: номер объекта недвижимости, дата регистрации, полный адрес (почтовый индекс, город, улица, дом и квартира), тип объекта (N-этажный панельный или кирпичный), этаж, количество комнат, площадь (общая, жилая, площадь кухни), балкон (балкон, лоджия, застеклён, их количество или отсутствие), наличие телефона (есть, нет) и отпускная цена.

Владелец (PRIVATE_OWNER)

Владелец имеет атрибуты: номер владельца, фамилия, имя, адрес и номер телефона.

Покупатель (BUYER)

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

Теперь определим типы связей, которые существуют между основными сущностями.

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

Из описания предметной области известно, что каждый объект недвижимости, выставленный на продажу, закрепляется за конкретным отделением компании. В каждом отделении компании есть сотрудник, отвечающий за работу с выставленными на продажу объектами недвижимости. Для отражения этой ситуации необходимо провести связь между сущностями Объект недвижимости для продажи и Отделение (ключевым атрибутом сущности Объект недвижимости для продажи является Номер объекта недвижимости (Property_no)). Для того чтобы узнать, какой объект недвижимости обслуживается каким сотрудником и, с другой стороны, какой сотрудник отвечает за данный объект, вводится дополнительная связь между сущностями Объект недвижимости для продажи и Сотрудник. Между сущностями Отделение и Объект недвижимости для продажи установлена связь 1:М, обязательная с 2-х сторон. Между сущностями Сотрудник и Объект недвижимости для продажи - связь 1:М, необязательная с 2-х сторон, так как из всех сотрудников компании только торговые агенты занимаются продажей недвижимости и отвечают за работу с ними. В обратном направлении, объект может быть не связан ни с одним из сотрудников. Например, когда объект впервые регистрируется в компании.

Теперь необходимо отразить связь между сущностями Владелец (атрибут Номер владельца (Owner_no) является ключевым) и Объект недвижимости для продажи. Если рассмотреть эту связь с одной стороны, то можно заметить, что один владелец может владеть несколькими объектами недвижимости. С другой стороны, каждый объект принадлежит только одному владельцу. Следовательно, связь между сущностями - 1:М. Поскольку каждый владелец владеет, по крайней мере, одним объектом недвижимости, а каждый объект должен иметь одного владельца, связь является обязательной с обеих сторон.

Из описания предметной области известно, что потенциальный покупатель обращается в одно из отделений компании (только когда клиент становится потенциальным покупателем агентства, данные о нём, заносятся в базу данных), в котором ему могут предложить осмотреть разные объекты недвижимости. Клиент, как правило, желает осмотреть один или несколько, предлагаемых ему объектов недвижимости. Сведения о таком просмотре включают дату осмотра объекта и комментарии потенциального покупателя (согласен он или нет купить данную квартиру и др.). Образуется необязательная с двух сторон связь М:М между сущностями Покупатель и Объект недвижимости для продажи. Отдельный клиент может осмотреть или осматривает несколько выставленных на продажу объектов (1:М), а каждый объект может быть осмотрен несколькими клиентами (1:М). Связь необязательна со стороны клиента из-за отсутствия объекта, отвечающего его требованиям. С другой стороны, сведения о некоторых объектах просто регистрируется в компании, а осмотр их клиентами не производится.

Если клиент согласен купить некоторый объект, то он заключает с компанией договор на покупку выбранного им объекта. Сотрудник компании должен оформить это соглашение. Каждый объект может быть продан единственному клиенту, и каждый клиент может купить один или более объектов в одно и то же время. Образуется необязательная с двух сторон связь 1:М между сущностями Покупатель и Объект недвижимости для продажи. Но так как, всякий раз, при покупке клиент заключает договор с компанией, мы определим две связи. Связь 1:М между сущностями Покупатель и Договор на покупку, а также связь 1:М между сущностями Объект недвижимости для продажи и Договор на покупку. Связи обязательны со стороны сущности Договор на покупку. Ключевым атрибутом для сущности Покупатель является Код покупателя (Buyer_no), а для сущности Договор на покупку - атрибут Номер договора (Sale_no). Кроме этого сущность Договор на покупку (CONTRACT_ON_SALE) имеет атрибуты: название нотариальной конторы, дата заключения договора, стоимость услуг.

Переход к реляционной модели

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

Для связи М:М между сущностями Покупатель и Объект недвижимости для продажи введем дополнительное связующее отношение, которое связано с каждым исходным связью 1:М.. Атрибутами этого связующего отношения, помимо даты осмотра и комментарии, будут первичные ключи связываемых отношений, т.е. Property_no и Buyer_no. Для нового отношения они являются внешними ключами, а вместе они образуют первичный ключ новой связующей сущности Осмотр (VIEWING).

Реляционная модель

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. С чего начинается разработка базы данных?

2. Охарактеризуйте основные понятия ER- модели: сущность, связь и атрибут.

3. Каким образом на ER диаграмме отображается обязательность и необязательность связи.

4. Перечислите правила преобразования ER-модели в реляционную схему.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Выполните проектирование базы данных. Исходные данные в соответствии с вариантом задания находятся в Приложении 1.

ЛАБОРАТОРНАЯ РАБОТА №3

Тема: Создание объектов базы данных: таблиц, индексов и первичных ключей таблицы. Использование ограничений.

Цель работы: Освоить основные методы и приемы создания таблиц базы данных, редактирования структуры таблиц.

ОСНОВНЫЕ СВЕДЕНИЯ

Таблицы баз данных

Создание, изменение структуры и удаление таблиц

В реляционных базах данных для хранения информации используются таблицы, представляющие собой подобие двумерных массивов. Создание таблицы базы данных в системе SQL-сервер может осуществляться следующими способами:

Первый способ: С помощью оператора CREATE TABLE через утилиту SQL Server Query Analyzer.

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

При создании таблиц необходимо учитывать следующие ограничения:

· Число полей в таблицы ограничено 1024;

· Длина записи не может превышать 8060 байт (типы данных image и text используют 16 байт на запись).

· База данных может содержать до двух миллиардов таблиц;

При определении таблицы можно разрешить или запретить использование в поле значений NULL. Если не указано, может ли поле содержать пустые значения, SQL Server определит это самостоятельно, в зависимости от стандартных параметров для текущей базы данных.

Синтаксис:

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

(имя_поля тип_данных [NULL| NOT NULL]

[, …n])

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

Синтаксис:

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

(имя_поля, числовой_тип_данных

IDENTITY [(начальное_значение, шаг)] [NOT NULL]

Используя свойство IDENTITY, следует иметь в виду следующее:

в таблице может быть только одно поле IDENTITY;

значение поля IDENTITY нельзя изменить;

значения NULL в поле IDENTITY не допускаются;

в поле IDENTITY должны использоваться целые (int, smallint или tinyint), числовые или десятичные типы данных, причём два последних надо задать с нулевой разрядностью. Выбирая тип поля, надо оценить возможное число записей таблицы;

информацию об определении поля IDENTITY две системные функции: IDENT_SEED (начальное значение) и IDENT_INCR (шаг);

получить значение ключа последней вставленной во время текущей сессии записи можно средствами функции @@IDENTITY.

Упражнение: При помощи оператора CREATE TABLE создайте таблицу OWNER в базе данных DreamHome_db, определив её поля, как указано в таблице:

Имя поля	Тип данных	Значения NULL	Свойство IDENTITY
Owner_no	member_no	Не допускаются	Seed=1 Increment=1
FName	shortstring	Не допускаются	Нет
LName	shortstring	Не допускаются	Нет
City	shortstring	Не допускаются	Нет
Street	shortstring	Не допускаются	Нет
House	nchar	Не допускаются	Нет
Flat	smallint	Допускаются	Нет
Otel_no	phononumber	Допускаются	Нет

CREATE TABLE OWNER

(Owner_no member_no NOT NULL IDENTITY(1,1),

FName shortstring NOT NULL,

LName shortstring NOT NULL,

City shortstring NOT NULL,

Street shortstring NOT NULL,

House nchar(6) NOT NULL,

Flat smallint NULL,

Otel_no phonenumber NULL)

рис. 3.8

После того, как таблица была создана, ее можно изменить. Для этого используется оператор ALTER TABLE. Чаще всего с его помощью добавляют поля к таблице или изменяют их тип данных. Однако следует помнить, что новое поле станет последним по порядку в списке.

Синтаксис:

ALTER TABLE таблица

ADD имя_поля тип_данных [NULL| NOT NULL] [, …n]

ALTER TABLE таблица

ALTER COLUMN имя_поля новый_тип_данных [NULL| NOT NULL]

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

Синтаксис:

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

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

Второй способ: С помощью утилиты SQL Server Enterprise Manager.

Для создания таблицы с помощью утилиты SQL Server Enterprise Manager необходимо:

Выбрать в списке объектов созданной базы данных группу Tables, после чего в правой части утилиты SQL Server Enterprise Manager будет отображен список всех ее таблиц, в том числе и системных.

Выполнить команду New Table меню Action, после чего на экране отобразиться запрос ввода имени создаваемой таблицы. На запрос ввода следует ввести имя таблицы, после чего подтвердить ввод нажатием кнопки OK. Затем утилита отобразит окно дизайнера таблиц.

В колонку Column Name необходимо ввести название столбца таблицы, после чего определить его тип данных, воспользовавшись колонкой Datatype окна дизайнера. Здесь в выпадающем списке отображается перечень всех доступных типов данных, определенных в SQL-сервере. После выбора типа данных для создаваемого поля система автоматически подставит для него параметры Length (размер поля), Precision (десятичный размер), Scale (точность числового типа данных). В зависимости от типа данных система определит доступ к редактированию этих параметров. Если ввод данных в создаваемое поле требует наличия какого-либо значения, устанавливаемого по умолчанию, то его следует ввести в колонке Default Value окна дизайнера таблицы.

В СУБД имеется поддержка так называемых NULL значений. С помощью SQL-сервера можно определить их использование в таблицах. Убрав флажок в колонке Allow Nulls для некоторого поля, можно потребовать обязательный ввод значений в это поле.

При создании таблицы можно определить свойство IDENTITY для какого-либо ее поля. Для этого в первую очередь надо убрать флажок Allow Nulls, чтобы избежать неопределенности информации. Следующим шагом будет установка флажка в поле Identity, после чего требуется ввести начальное значение Identity Speed и шаг Identity Increment (добавляемая сумма).

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

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

Если необходимо в процессе работы с SQL-сервером переименовать ранее созданную таблицу, то следует выбрать команду Rename меню Action, после чего в списке будет выделено имя этой таблицы, доступное для редактирования.

Каждая таблица в SQL-сервере обладает рядом свойств, для просмотра которых необходимо воспользоваться командой Properties меню Action. При этом на экран будет выведено диалоговое окно, с помощью которого можно просмотреть перечень полей таблицы, их типы данных, размерность, разрешение на ввод Null-значений, первичные ключи таблицы и т.д. Здесь также будет показана БД, которой принадлежит таблица, дата ее создания и владелец.

Для удаления таблицы из базы данных SQL-сервера необходимо сначала выбрать ее в списке, после чего выполнить команду Delete меню Action. В этом случае на экран будет выведено диалоговое окно, с помощью которого можно будет окончательно определиться в необходимости удаления этой таблицы. Воспользовавшись кнопкой Show Dependencies, можно просмотреть перечень таблиц, связанных с данной таблицей. Выбор кнопки Drop All данного диалогового окна приведет к удалению таблицы из базы данных.

Упражнение: Создайте таблицу BRANCH в базе данных DreamHome_db при помощи SQL Server Enterprise Manager.

1. Раскройте группу серверов, а затем - сервер.

2. Раскройте Databases и щёлкните правой кнопкой базу данных DreamHome_db.

3. Щёлкните Tables правой кнопкой и в контекстном меню выберите команду New Table.

4. В диалоговом окне Choose Name введите имя таблицы, BRANCH, и нажмите кнопку OK.

5. Определите поля в соответствии со следующей таблицей (каждая строка соответствует одному полю):

6.

Имя поля	Тип данных	Значения NULL	Флажок Allow Nulls
Branch_no	member_no	Не допускаются	Не установлен
Postcode	postcode	Допускаются	Установлен
City	shortstring	Не допускаются	Не установлен
Street	shortstring	Не допускаются	Не установлен
House	nchar(10)	Не допускаются	Не установлен
Btel_no	phononumber	Не допускаются	Не установлен
Fax_no	phononumber	Допускаются	Установлен

7. Закройте окно New Table и сохраните изменения, выполненные в таблице BRANCH.

Создание индексов и первичных ключей таблицы

Создание ключей в системе SQL-сервер

Одним из основных понятий баз данных, используемых при контроле целостности информации, является ключ. Разделяют первичные и внешние ключи. Первичный ключ (PRIMARY KEY) - это уникальное поле (или несколько полей), однозначно определяющее записи таблицы базы данных. Внешние ключи (FOREIGN KEY) - это поля таблицы, которые соответствуют первичным ключам из других таблиц.

Создать первичный ключ можно одним из следующих способов:

Первый способ: Через утилиту SQL Server Query Analyzer.

При создании первичного ключа надо помнить, что поле не должно содержать Null - значений.

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

(имя_поля тип_данных [(размер)] NOT NULL PRIMARY KEY,

имя_поля тип_данных [(размер)][NULL| NOT NULL],

…);

Второй способ: С помощью утилиты SQL Server Enterprise Manager.

Для создания ключа с помощью утилиты SQL Server Enterprise Manager необходимо:

Выбрать нужную таблицу в списке объектов базы данных.

Выполнить команды Design Table меню Action, что приведет к загрузке дизайнера таблиц, с помощью которого и можно осуществить установку первичного ключа.

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

Далее следует выбрать нужное поле и выделить его.

Установка первичного ключа осуществляется с использованием кнопки .

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

Упражнение: Установите ключ в таблице BRANCH базы данных DreamHome_db при помощи SQL Server Enterprise Manager:

1. Раскройте группу серверов, а затем - сервер.

2. Раскройте Databases и базу данных DreamHome_db.

3. Щёлкните таблицу BRANCH правой кнопкой мыши и в контекстном меню выберите команду Design Table.

4. Выделите поле Branch_no.

5. Щёлкните по кнопке

6. Закройте окно и сохраните изменения.

Создание индексов в системе SQL-сервер

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

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

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

В индексы следует включать поля, к которым часто выдаются запросы при выполнении операций поиска. К ним относятся:

основные ключи;

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

поля, в которых производится поиск диапазонов ключевых значений;

поля, к которым производится упорядоченный доступ.

Создать индекс можно несколькими способами:

Первый способ: С помощью утилиты SQL Server Query Analyzer.

Для этого надо открыть SQL Server Query Analyzer и подключиться к серверу с использованием средств аутентификации пользователей. Для создания индекса используется оператор CREATE INDEX.

Синтаксис:

CREATE INDEX имя_индекса ON таблица (поле[, …n])

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

Для создания уникальных (не содержащих повторяющихся значений) индексов используется ключевое слово UNIQUE в операторе CREATE INDEX (CREATE UNIQUE INDEX …).

Для удаления индекса используется оператор DROP INDEX.

Синтаксис:

DROP INDEX таблица.индекс[,…n]

Второй способ: С помощью утилиты SQL Server Enterprise Manager.

Для создания индекса с помощью утилиты SQL Server Enterprise Manager необходимо:

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

Выполнить команду ALL Task / Manager Indexes меню Action. При этом на экране отобразиться диалоговое окно управления индексами базы данных. Выпадающие списки данного диалогового окна Databases и Table позволяют перемещаться между базами данных и их таблицами. При этом в списке Existing indexes отображаются имеющиеся индексы для выбранных таблиц баз данных.

В нижней части диалогового окна расположены управляющие кнопки, выполняющие следующие действия:

New - создание нового индекса для выбранной таблицы базы данных;

Edit - редактирование параметров существующего индекса;

Delete - удаление предварительно выбранного индекса;

Close - закрытие диалогового окна;

Help - получение справочной информации по данному вопросу.

Для создания нового индекса следует воспользоваться кнопкой New. Это приведет к открытию нового диалогового окна Create New Index, с помощью которого и устанавливаются параметры индекса.

В поле Index name необходимо ввести имя создаваемого индекса, после чего определить перечень полей, участвующих в индексе, в представленном списке. Для добавления определенного поля в индекс надо установить флажок слева от его имени. С помощью области Change column order можно менять порядок расположения полей в представленном списке. Для этого используются кнопки Move Up и Move Down. Группа опций Index options позволяет настроить дополнительные параметры создаваемого индекса. Описание дополнительных параметров приведено в Приложении 7.

Основные различия между понятиями индекс и ключ:

· Использование первичного ключа требует уникальности данных в таблице по определенному полю, что можно также выполнить при создании уникального индекса. Однако SQL-сервер разрешает определить только один первичный ключ, тогда как уникальных индексов можно создавать несколько.

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

Ограничения

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

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

§ ограничения целостности атрибутов, или полей: значения по умолчанию, задание обязательности или необязательности значений (NULL), задание условий на значения полей;

§ ограничения целостности сущностей, или таблиц: значение первичного ключа, выражения, которые применимы ко всей таблице;

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

§ ограничения целостности, определяемой пользователем: пользовательский тип данных.

Различные типы ограничений, определяемые в SQL-сервере, описаны в Приложении 4.

Ограничения определяются в операторах CREATE TABLE и ALTER TABLE.

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

Синтаксис:

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

( {<определение_поля>

|<ограничение_таблицы>}

[,…n]

)

Где

<определение_поля>::={имя_поля тип_данных}

[[CONSTRAINT имя_ограничения]

{DEFAULT константное_выражение

|CHECK (логическое_выражение)

|PRIMARY KEY [CLUSTERED |NON CLUSTERED]

|UNIQUE [CLUSTERED |NON CLUSTERED]

|[FOREIGN KEY] REFERENCES таблица_на_которую_ссылаются [(поле_таблицы_на_которую_ссылаются)]

}]

[…n]

<ограничение_таблицы>::=

[CONSTRAINT имя_ограничения]

|CHECK (логическое_выражение)

|PRIMARY KEY [CLUSTERED |NON CLUSTERED] (поле [,…n])

|UNIQUE [CLUSTERED |NON CLUSTERED] (поле [,…n])

|FOREIGN KEY

(поле [,…n])

REFERENCES таблица_на_которую_ссылаются

[(родительской_таблицы_на_которую_ссылаются [,…n])]

}

Используя ограничения FOREIGN KEY можно не указывать список полей родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. А также, задавая это ограничение, можно использовать только слово REFERENCES.

Например, при создании таблицы BUYER это может выглядеть так:

Branch_no member_no NOT NULL, FOREIGN KEY(Branch_no) REFERENCES BRANCH(Branch_no) или так: Branch_no member_no NOT NULL REFERENCES BRANCH

В соответствии со стандартом, изменение или удаление значений родительского ключа не допускается. Это означает, что нельзя изменить или удалить данные об отделении (например, при его закрытии) из таблицы BRANCH до тех пор, пока в таблице BUYER имеются клиенты, у которых в поле Branch_no содержится номер изменяемого или удаляемого отделения. Однако, довольно часто возникает необходимость в таких изменениях. В таких случаях задаются каскадирования или ограничения действий.