reforef.ru 1
Создание Базы данных


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

Постановка задачи

На одном из факультетов некоторого учебного заведения (например, ВУЗа) проводятся учебные занятия.

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

-  у каждой группы может проводиться несколько занятий;

-  в каждой группе учится несколько студентов;

-  каждый преподаватель может проводить несколько занятий;

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

В этой БД предусмотреть:


  • возможность хранения сведений об основных объектах факультета;

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

  • средства для поиска необходимых данных;

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

Логическая модель

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

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


Для поставленной задачи выделим объекты (таблицы): Занятие, Группа, Студент,
Преподаватель, Дисциплина.

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

Выделим следующие характеристики (атрибуты) для всех объектов, также укажем требования к атрибутам.

Группа:


Студент:

Преподаватель:

  • Код преподавателя – обязательный атрибут;

  • Фамилия, Имя, Отчество – обязательные атрибуты;

  • Кафедра – обязательный атрибут;

  • Должность – обязательный атрибут;

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


Дисциплина:

  • Код дисциплины – обязательный атрибут;

  • Название – обязательный атрибут;

  • Количество лекционных часов – обязательный атрибут;

  • Количество практических часов – обязательный атрибут.

Занятие:

  • Группа – обязательный атрибут;

  • Дисциплина – обязательные атрибуты;

  • Вид занятия – обязательный атрибут;

  • Преподаватель – обязательный атрибут.

 3 шаг – определение связей. Проектируя БД, стараются не допускать избыточности данных в ней. Избыточные данные приводят к увеличению размера базы, а также к нарушению целостности данных при обновлении. Связи, которые устанавливаются между таблицами, как раз являются одним из средств устранения избыточности. Необходимо определить, по каким полям таблиц установить связь. При этом вводится понятие внешнего ключа. Внешним ключом таблицы называется поле (или группа полей), ссылающихся на первичный ключ другой таблицы. При этом оба ключа должны иметь одинаковый тип данных. Таблица, первичный ключ которой участвует в связи, называется ссылочной (главной), а таблица, содержащая соответствующий внешний ключ – ссылающейся (подчиненной). Пример см. на рис. 1.

Существует три типа связей между двумя таблицами:

– один-к-одному: для каждой строки главной таблицы имеется не более одной строки подчиненной таблицы, а для каждой строки подчиненной таблицы – не более одной строки главной таблицы. Такие типы связей встречаются нечасто, т.к. данные из одной таблицы можно просто включить в другую. Пример см. на рис. 2.

– один-ко-многим: для каждой строки главной таблицы имеется ноль, одна или несколько строк подчиненной таблицы, а для каждой строки подчиненной таблицы имеется только одна строка главной таблицы. Это наиболее распространенный тип связи. Пример см. на рис. 1.


– многие-ко-многим: каждой строке одной таблицы соответствует несколько строк второй таблицы и наоборот. В СУБД такая связь представлена разбиением ее на две связи один-ко-многим. Пример см. на рис. 3.

Для нашей задачи, проанализировав ограничения (см. постановку задачи), существуют следующие связи между объектами Занятие, Группа, Студент, Преподаватель, Дисциплина:


  • между объектами Группа и Занятие связь один-ко-многим (1:М);

  • между объектами Группа и Студент связь один-ко-многим (1:М), значит в таблицу Студент нужно добавить внешний ключ, ссылающийся на первичный ключ таблицы Группа
    (КодГруппы);

  • между объектами Дисциплина и Занятие связь один-ко-многим (1:М);

  • между объектами Преподаватель и Занятие связь один-ко-многим (1:М).

Задание 1.1. Постройте на листе схематично структуру БД, аналогично примерам, представленным на рис. 1-3. Для каждой таблицы подчеркните первичные ключи и линиями покажите связи между таблицами (концы связей пометьте 1 или М).

Проверить себя!!!

Физическая модель. Создание базы данных в СУБД Access

При запуске MS Access предлагает создать новую БД, запустить мастеров или открыть готовую БД.

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

В процессе дальнейшей работы с БД все созданные объекты будут сохраняться в этом файле.

Создание таблиц данных

Для создания таблицы необходимо выбрать объект Таблицы и открыть окно Новая таблица (командой меню Вставка/Таблица или кнопкой Создать в окне базы данных).


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

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

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

Импорт таблиц предусматривает введение в текущую базу данных таблицы, созданной в другой БД или в другом приложении (например, в табличном процессоре Excel). Этот способ будет рассмотрен позднее.

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

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

Примечание 2. Если значения поля будут выбираться из заранее известного и не слишком большого множества значений, удобно создать поле со списком, из которого при заполнении таблицы можно будет выбрать нужное значение. Для этого в разделе свойств поля на вкладке Подстановка нужно установить следующие значения параметров: Тип элемента управления – «Поле со списком» или «Список»; Тип источника строк – «Список значений», в параметре Источник строк перечислить все необходимые значения в кавычках через точку с запятой. Например, для поля Пол можно задать список из двух значений: «ж»; «м».

Возможно также в качестве источника строк использовать другую таблицу (Тип источника строк – «Таблица или запрос»). В этом случае значения не надо перечислять – достаточно указать имя таблицы, из которой они будут взяты (это обычно делают для внешних ключей). Например, для поля Группа таблицы Студент будут использоваться коды групп из таблицы Группа.


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

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

 

Задание 1.2. Создайте все таблицы базы данных ФАКУЛЬТЕТ, самостоятельно определив и задав типы полей, а также задав свойства полей согласно описанию. Не забудьте определить для всех таблиц первичные ключи.

Рекомендации по выполнению задания 1.2:

1. В таблице Студент:

 для поля Пол организуйте ввод значений через перечисление «Ж»; «М» (см. рис. 5);

 для поля ДатаРождения в свойстве Условие на значение задайте условие Year([ДатаРождения])>1900 And [ДатаРождения]) возвращает год из указанной даты, а функция Date() возвращает текущую дату;

 для поля Адрес в соответствующем свойстве поля задайте значение по умолчанию «неизвестно»;

 для поля СотовыйТелефон задайте маску ввода для сотового телефона: 8(000)000-00-00;

 для реализации связи 1:М не забудьте добавить внешний ключ (Группа), ссылающийся на первичный ключ таблицы Группа. Для этого поля организуйте ввод значений через ссылку на таблицу Группа (см. рис. 5).

 для поля ПроживаетВОбщежитии

2. В таблице Преподаватель:

 для поля Кафедра организуйте ввод значений через перечисление (например, ИТ и методики обучения информатике, прикладной математики и информатики, высшей математики);

 для поля Должность организуйте ввод значений через перечисление (ассистент, старший преподаватель, доцент или профессор);


 для поля УченаяСтепень организуйте ввод значений через перечисление (кандидат наук или доктор наук);

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

3. В таблице Занятие:

 для поля Группа организуйте ввод значений через ссылку на таблицу Группа;

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

 для поля ВидЗанятия организуйте ввод значений через перечисление (лекция, семинар или лабораторное занятие);

 для поля Преподаватель организуйте ввод значений через ссылку на таблицу
Преподаватель;

 обратите внимание на то, что первичный ключ должен быть составной (включающий поля Группа, Дисциплина и ВидЗанятия).

Установление связей

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

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

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


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

Правила внешних ключей определяются установкой следующих флажков.

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

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

Для изменения связи необходимо выделить линию связи и вызвать окно Изменение связи (через основное или контекстное меню), а для удаления достаточно выделить связь и воспользоваться клавишей DELETE или командой контекстного меню.

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

 Задание 1.3. Создайте все необходимые связи между таблицами БД ФАКУЛЬТЕТ.

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


Заполнение таблиц

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

Таблицы БД можно заполнять непосредственно в режиме таблицы, но часто удобнее это делать через формы, о которых – в следующем разделе.

 Задание 1.4. Частично заполните БД ФАКУЛЬТЕТ:

-  в таблицу Группа внесите данные, по крайней мере, о двух группах;

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

-  в таблицу Преподаватель внесите сведения о 4 преподавателях;

-  в таблицу Дисциплина внесите сведения о 6 дисциплинах;

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