СУБД MS Access** Лабораторная работа №1
Тема: Создание таблиц базы данных в режиме Конструктора.
Цель работы: Изучить процесс создания структуры базы данных. Получить начальные навыки работы в СУБД MS Access**. Освоить технологию создания объектов типа «Таблица» с помощью Конструктора таблиц. Освоить технологию заполнения таблицы и манипуляций с ними. Изучить технику связывания таблиц. Освоить технологию заполнения таблицы данными и редактирования.
Задание
Торговая фирма в г. Киеве, реализующая продовольственные товары, имеет в штате 4 продавца. Товары фирме поставляют 6 поставщиков. Каждый вид товара поставляется только одним поставщиком, но каждый поставщик может поставлять товары различных видов. Фирма имеет 5 заказчиков. Каждый заказчик может сделать несколько заказов (или ни одного), но отдельный заказ может быть сделан только одним заказчиком. Каждый продавец может обслужить несколько заказов (или ни одного), но один заказ может быть выполнен только одним продавцом. Каждый заказ может включать несколько видов товара, а каждый вид товара, в свою очередь, может входить в различные заказы (или ни в один). Создать базу данных товарооборота фирмы.
Указания к выполнению:
Часть 1. Разработка структуры базы данных
1. Разделим данные на группы (темы). Наиболее очевидно, что данные о продавцах следует отнести к одной группе, данные о поставщиках – к другой, данные о заказчиках – к третьей.
2. Следовательно, структура указанных трех таблиц может быть примерно следующей:
Таблица ПРОДАВЦЫ:
-
КодПродавца
Фамилия
Имя
Отчество
ДатаЗачисления
Телефон
Таблица ЗАКАЗЧИКИ:
-
КодЗаказчика
Заказчик
Город
Адрес
Телефон
Представитель
Таблица Поставщики:
-
КодПоставщика
Поставщик
Город
3. В каждой таблице имеется одно уникальное поле-ключ – простой ключ (КодПродавца, КодЗаказчика, КодПоставщика), которое необходимо для связывания таблиц базы данных. Данные в ключевом поле не должны повторяться.
4. Информацию о товарах также надо разместить в отдельную таблицу, в которой поле КодТовара – ключевое. Кроме того, в ней должно быть отдельное поле КодПоставщика (внешнее поле) для связывания отдельного вида товара с его поставщиком (для связи таблиц Товары и Поставщики):
Таблица Товары:
-
КодТовара
Товар
ЕдиницаИзмерения
Цена
КодПоставщика
5. К отдельной группе относится информация о заказах. В таблице Заказы поле КодЗаказа – ключевое; поля КодЗаказчика и КодПродавца – внешние поля для соответствующих ключевых полей таблиц Заказчики и Продавцы (для связывания таблицы Заказы с таблицами Заказчики и Продавцы):
Таблица Заказы:
-
КодЗаказа
КодЗаказчика
ДатаЗаказа
КодПродавца
6. Поскольку каждый заказ может содержать несколько видов товара, то это также необходимо выделить в отдельную таблицу Заказано. В этой таблице, во-первых, значения в поле КодЗаказа могут повторяться (следовательно, это поле не может быть простым ключом), во-вторых, каждый вид товара может входить в несколько заказов. Поэтому поля КодЗаказа и КодТовара должны быть ключевыми (составной ключ) для обеспечения отношения «многие-ко-многим» между таблицами Заказы и Товары. Кроме того, поле КодЗаказа таблицы Заказано является внешним для соответствующего ключевого поля таблицы Заказы, а поле КодТовара – внешнее для соответствующего ключевого поля таблицы Товары:
Таблица Заказано:
-
КодЗаказа
КодТовара
Количество
7. Структура базы данных готова.
Часть2. Создание структуры таблиц базы данных в режиме Конструктора
1. Создайте в своей папке папку Базы данных.
2. Запустие Microsoft Access**.
3. В окне щелкните Новая база данных, ОК. Можно нажать Отмена, тогда для создания пустой базы кнопка Создать… на панели База данных (или Файл, Создать). Для открытия существующей БД щелкнуть Открыть базу данных, выбрать нужный файл в списке ниже (или меню Файл, Открыть…или кнопка Открыть … на панели База данных).
4. MS Access** предлагает сразу же создать и сохранить пустую БД – сохраните с именем Товарооборот в своей папке (выберите папку, введите имя, кнопка Создать).
5. Ознакомьтесь с основным окном базы данных.
6. Работаем с объектом Таблица (на вкладке Таблица).
7. Создадим макет таблицы Продавцы в режиме Конструктора. Кнопка Создать (или меню Вставка, Таблица или кнопка-стрелка Новый объект на панели База данных, Таблица), Конструктор,ОК.
8. В окне Конструктора таблиц в первой строке столбца Имя поля введите КодПродавца, Tab (или щелчок рядом в поле Тип данных), появится кнопка-стрелка, щелкните на ней. Просмотрите раскрывающийся список. Выберите Счетчик. Сделаем это поле ключевым – щелкните в данной строке, кнопка Ключевое поле на панели Конструктора таблиц (или эта же команда в контекстном меню или в меню Правка).
9. Перейдите в следующую строку в столбце Имя поля, введите Фамилия, в поле рядом в списке выберите Текстовый (Текстовый устанавливается по умолчанию при нажатии Tab или щелчке в поле Тип данных). Ниже в окне Свойства поля на вкладке Общие можно установить Размер поля 30 (50 позиций для фамилии многовато…).
10. В следующих двух строках последовательно введите Имя (Текстовый, 20), Отчество (Текстовый, 30).
11.В следующей строке в столбце Имя поля введите ДатаЗачисления, в поле рядом в списке выберите Дата/время. Ниже в окне Свойства поля на вкладке Общие можно выбрать Маску ввода (для облегчения ввода даты в таблицу, когда вводим только цифры даты, а разделители вводятся автоматически) – щелкните в поле Маска ввода, справа появится кнопка с точками, щелкните ее. При задании маски ввода появится запрос на сохранение таблицы – Да, введите имя таблицы (Продавцы). Далее в окне Создание масок ввода выберите Краткий формат даты, Готово.
Примечания.
а) по умолчанию при первом сохранении таблицы предлагается имя Таблица 1 (или Новая таблица). Это имя можно оставить, а затем на вкладке Таблицы можно переименовать (соответствующая команда в контекстном меню);
б) при создании и изменении БД не нужно заботиться о ее сохранении – в MS Access** все изменения выполняются и сохраняются автоматически в режиме реального времени и имеют необратимый характер; при необходимости выдается запрос на сохранение (хотя можно периодически нажимать кнопку Сохранить, если она доступна);
в) созданную базу данных нельзя сохранять под другим именем или в другой папке (хотя потом, в окне Windows**, ее можно скопировать, переименовать и т.д.)
12. В следующей строке в столбце Имя поля введите Телефон также можно использовать маску ввода, если она есть в списке. Тип данных Текстовый, Размер поля 15.
13. Закройте окно Конструктора, на запрос сохранения таблицы – Да. Макет первой таблицы готов. На вкладке Таблица появилось название таблицы Продавцы.
14. При необходимости внесения изменений в макет таблицы – выделить имя таблицы на вкладке Таблицы, кнопка Конструктор (можете щелкнуть, но ничего не меняйте, если все правильно). В окне Конструктора строку можно добавить (щелкнуть в строке, перед которой надо вставить строку, меню Вставка, Строки), удалить (щелкнуть в удаляемой строке, меню Правка, Удалить строки), некоторые действия можно Отменить.
15. Создадим макет таблицы Поставщики – аналогично, тип поля КодПоставщика – Счетчик (не забудьте это поле сделать ключевым), тип других полей (Поставщик, Город) – Текстовый. Кроме того, для поля Поставщик в окне Свойства поля в строке Индексированное поле установите Да (Совпадения не допускаются) – щелкните, выберите в списке (при заполнении таблицы данными MS Access** предотвратит Вас от ошибочного ввода одинаковых названий поставщиков).
16. Создадим макет таблицы Заказчики – аналогично. Тип поля КодЗаказчика – Числовой, свойство – Длинное целое (можно было бы Счетчик, нобудем считать, что КодЗаказчика – это его налоговый идентификационный код). Типы остальных полей (Заказчик, Город, Адрес, Телефон, Представитель) – Текстовый. Не забудьте установить ключевое поле (КодЗаказчика). Кроме того, для поля Заказчик в окне Свойства поля в строке Индексированное поле установите Да (Совпадения не допускаются)
17. Создадим макет таблицы Товары – почти аналогично. Тип поля КодТовара – Счетчик (не забудьте это поле сделать ключевым). Тип поля Товар – Текстовый (индексированное поле: Да (Допускаются совпадения); ЕдиницаИзмерения –Текстовый (15); Цена –Денежный. А для поля КодПоставщика будьте внимательны. Чтобы потом не заполнять это поле вручную, выполните для этого поля следующее:
из списка Типов полей выберите Мастер подстановок;
в окне Создание подстановки щелкните столбец подстановки использует значения из таблицы или запроса, Далее;
в следующем окне выберите таблицу Поставщики, Далее;
в следующем окне последовательно перенесите с левого окна в правое поля КодПоставщика и Поставщик - выделите и перенесите, Далее;
в следующем окне оставьте «флажок» Скрыть ключевое поле, подкорректируйте ширину столбца Поставщик, Далее;
в следующем окне введите подпись столбца подстановки – Поставщик, Готово. На все запросы (если есть) – Да , ОК
В результате в окне Конструктора мы увидим: имя поля – КодПоставщика, тип – Числовой (Длинное целое), окне Свойства поля на вкладке Общие в строке Подпись – Поставщик. Это значит, что таблица Товары связывается с таблицей Поставщики посредством поля КодПоставщика (которое в таблице Товары будет иметь подпись Поставщик. Т.е. вместо поля КодПоставщика в таблице Товары будет отображаться столбец подстановки Поставщик. Для поля КодПоставщика установите также свойство Индексированное поле: Да (Допускаются совпадения). Использование индексированных полей увеличивает быстродействие поиска данных в БД. Для КодПоставщика просмотрите вкладку Подстановка.
Примечание: Если Вы забыли на последнем шаге Мастера подстановок ввести подпись Поставщик - Вы можете ввести эту подпись в окне Свойства поля на вкладке Общие в строке Подпись.
18. Создадим макет таблицы Заказы: тип поля КодЗаказа – Счетчик (не забудьте это поле сделать ключевым); для задания полей КодЗаказчика и КодПродавца используйте Мастер подстановок (аналогично полю КодПоставщика таблицы Товары – см.п.17.) Для поля КодЗаказчика – в окне Мастера подстановок выбираем таблицу Заказчики, поля КодЗаказчика и Заказчик, подпись столбца подстановки – Заказчик. Для поля КодПродавца - в окне Мастера подстановок выбираем таблицу Продавцы, поля - КодПродавца и Фамилия, подпись столбца подстановки – Продавец. Кроме того, для полей КодЗаказчика и КодПродавца установите свойство Индексированное поле: Да (Допускаются совпадения). Тип поля –ДатаЗаказа- Дата/Время.
19. Создадим макет таблицы Заказано:
а) тип поля КодЗаказа Числовой (Длинное целое, а также Индексированное поле: Да (допускаются совпадения) – это не может быть Счетчик, так какзначения в этом поле зависят от значений в соответствующем поле таблицы Заказы; для задания типа поля КодТовара используем Мастер подстановок: выбираемая таблица – Товары, выбираемые поля – КодТовара и Товар, подпись столбца подстановки – Товар; кроме того, также установите свойство Индексированное поле: Да (допускаются совпадения) – один вид товара может входить в разные заказы;
б) сделайте поля КодЗаказа и КодТовара составным ключом: выделите обе строки –щелчок на кнопке слева в строке КодЗаказа, затем при нажатой Shift щелчок на кнопке слева от поля КодТовара (если поля не смежные – при нажатой Ctrl ),кнопка Ключевое поле (в обеих строках слева появится знак ключа); в данном случае простой ключ КодЗаказа не подходит, т.к., значения в поле КодЗаказа могут повторяться (один заказ может включать несколько видов товара). Однако одинаковые пары значений КодЗаказа и КодТовара повторяться не должны, что и обеспечивается составным ключом;
в) тип поля Количество – Числовой (Длинное целое, хотя в некоторых случаях необходимо (С плавающей точкой, например, если в поле будут вещественные числа типа 4,8).
Часть 3. Создание схемы данных
1. Убедитесь, что окно Конструктора закрыто.
2. Меню Сервис, Схема данных (или кнопка Схема данных на панели База данных или Схема данных в контекстном меню окна базы данных).
3. В окне Добавление таблицы выделить все имена таблиц (щелкнуть первое, затем при нажатой клавише Shift – последнее), кнопка Добавить (если окна Добавление таблицы нет – команда Добавить таблицу в контекстном меню), это же действие выполняем, если к существующей схеме надо еще добавить одну или несколько таблиц). Закройте окно Добавление таблицы.
4. Расположение таблиц в окне Схема данных не имеет значения, но лучше их расположить так, чтобы связи были наиболее наглядны.
5. Если некоторые окна таблиц имеют полосы прокрутки – это значит, что не все поля таблицы видимы –измените размеры окошек таблиц так, чтобы все поля были видимы.
6. Теперь установим связи между таблицами:
а) между таблицами Заказчики и Заказы – поле КодЗаказчика таблицы Заказы перетащите мышкой на ключевое поле КодЗаказчика таблицы Заказчики; в открывшемся окне Связи убедитесь, что отображены нужные поля (если нет – их можно изменить, выбрав из списков в этом же окне, щелкнув справа от имени поля); установите “флажок” Обеспечение целостности данных, кнопка Создать;
б) между таблицами Продавцы и Заказы – поле КодПродавца таблицы Заказы перетащите мышкой на ключевое поле КодПродавца таблицы Продавцы; далее аналогично п.а);
в) между таблицами Поставщики и Товары - поле КодПоставщика табл. Товары перетащите мышкой на ключевое поле КодПоставщика табл. Поставщики; далее аналогично п.а);
г) между таблицами Заказано и Заказы – ключевое поле КодЗаказа таблицы Заказано перетащите мышкой на ключевое поле КодЗаказа таблицы Заказы; далее аналогично п.а);
д) между таблицами Заказано и Товары – ключевое поле КодТовара таблицы Заказано перетащите мышкой на ключевое поле КодТовара таблицы Товары; далее аналогично п.а).
В результате выполненных действий Вы получите схему данных . Если что-то не так – неверную линию связи можно удалить (щелкнуть на линии связи, Delete или меню Правка, Удалить или Удалить в контекстном меню линии связи). Для изменения типа связи – Изменить связь в контекстном меню или двойной щелчок на линии связи, Для удаления схемы данных – меню Правка, Очистить макет.
Часть 4. Заполнение таблиц данными
1. В первую очередь заполняются таблицы, которые не содержат записей, ссылающихся на записи других таблиц. У нас это таблицы Продавцы, Заказчики и Поставщики.
2.Заполнение таблиц осуществляется в режиме Таблица – выделить имя таблицы, кнопка Открыть (из режима Конструктора – меню Вид, Режим таблицы). Сначала просмотрите текущую таблицу, подкорректируйте ширину столбцов (тянуть мышкой за линию между заголовками столбцов или двойной щелчок на линии между заголовками). Поле типа Счетчик не заполняем – оно заполняется автоматически (1, 2, 3...) при переходе к очередной записи. Для перехода к очередному полю – клавиша Tab, стрелка – вправо (влево), или щелчок (лучше Tab или Enter). В конце ввода последней записи также Tab или Enter.
3. После окончания заполнения таблицы закройте таблицу. На запрос сохранения – Да .
4. Данные для заполнения таблицы Продавцы
Для ввода даты в кратком формате по маске ввода – вводим только цифры, например, дату 01.01.00 вводим как 010100 (разделители автоматические):
| КодПродавца | Фамилия | Имя | Отчество | ДатаЗачисления | Телефон |
| 1 | Безруков | Сергей | Иванович | 01.01.00 | 234-11-56 |
| 2 | Дроздова | Ольга | Олеговна | 01.01.90 | 234-11-57 |
| 3 | Большова | Ирина | Павловна | 01.10.92 | 266-34-17 |
| 4 | Певцов | Дмитрий | Борисович | 01.02.95 | 266-45-68 |
5. Данные для заполнения таблицы Поставщики
| КодПоставщика | Поставщик | Город |
| 1 | Агрофирма «Маков» | с.Маков |
| 2 | Завод «Рыба» | Керчь |
| 3 | ЗАО «Оболонь» | Киев |
| 4 | Молокозавод № 1 | Киев |
| 5 | ОАО «Мясо/птица» | Шепетовка |
| 6 | Фирма «Свиточ» | Львов |
6. Данные для заполнения таблицы Заказчики
В поле КодЗаказчика идентификационные коды вводим сами, это не Счетчик; значение Киев можно ввести один раз, а затем копировать:
| КодЗаказчика | Заказчик | Город | Адрес | Телефон | Представитель |
| 125647 | Ресторан «Бавария» | Киев | просп.Науки, 24 | 265-90-71 | Домогаров |
| 148970 | Кафе «Минутка» | Киев | Московская пл., 1 | 434-78-09 | Алексимова |
| 346739 | Столовая НАУ | Киев | просп.Комарова, 1 | 266-55-85 | Меньшова |
| 123509 | Ресторан «Киев» | Киев | бульв.Шевченко, 4 | 413-10-24 | Клуни |
| 987678 | Отель «Крещатик» | Киев | ул. Крещатик, 32 | 243-11-22 | Сталлоне |
7. Далее заполняем остальные таблицы, руководствуясь правилом: заполняемая в данный момент таблица не должна содержать записей, ссылающихся на записи еще незаполненых таблиц, т.е. заполняем ту таблицу, которая содержит записи, ссылающиеся только на записи уже заполненных таблиц. У нас это преждн Вего таблица Товары (записи которй содержат поле КодПоставщика, связанное с соответсвующим ключом таблицы Поставщики)
8. Данные для заполнения таблицы Товары. Для полей столбца Поставщик данные выбираем из списка в ячейке Поставщик):
| КодТовара | Товар | ЕдиницаИзмерения | Цена | Поставщик |
| 1 | Сардины в томате | Упак. | 50,00 | Завод «Рыба» |
| 2 | Шпроты в масле | Упак. | 70,00 | Завод «Рыба» |
| 3 | Пиво темное | Бутылка | 5,00 | ЗАО «Оболонь» |
| 4 | Пиво светлое | Банка | 3,00 | ЗАО «Оболонь» |
| 5 | Овощи (в ассортим.) | Центнер | 150,00 | Агрофирма «Маков» |
| 6 | Краб.палочки | Упак. | 10,00 | Завод Рыба» |
| 7 | Конфеты «Ирис» | Ящик | 32,00 | Фирма «Свиточ» |
| 8 | Конфеты «Прометей» | Коробка | 7,00 | Фирма «Свиточ» |
| 9 | Конфеты «Ассорти» | Коробка | 8,00 | Фирма «Свиточ» |
| 10 | Картошка | Ведро | 5,00 | Агрофирма «Маков» |
| 11 | Мясо | Упак. | 100,00 | ОАО «Мясо/птица» |
| 12 | Птица | Шт. | 11,00 | ОАО «Мясо/птица» |
| 13 | Молоко | Бидон | 15,00 | Молокозавод № 1 |
| 14 | Сметана | Упак. | 30,00 | Молокозавод № 1 |
9. Если приблизиться к реальности, то таблицы Продавцы, Поставщики, Заказчики и Товары – относительно статичные таблицы, т.е. необходимость их изменения (добавления/удаления записей) возникает не так уж часто. Однако таблицы Заказы и Заказано пополняются постоянно – это работа торговой фирмы. Поскольку первая таблица включает заказы, а вторая – еще и товары, которые в эти заказы входят, то заполнение таких таблиц обычно проводится параллельно. Поскольку данные для таблиц представлены ниже, то, конечно можно ввести их сначала в одну таблицу (Заказы), затем в другую (Заказано). Но мы сделаем так, как бы делали по мере поступления заказов.
10. Откройте обе таблицы Заказы и Заказано, расположите их рядом (меню Окно, Сверху вниз), можно перемещать или изменять размеры как для обычных окон; при этом окно базы данных Товарооборот можно минимизировать. Ввод начинаем в таблицу Заказы (невозможно ввести запись в таблицу Заказано, если в таблице Заказы нет соответствующего заказа). После ввода записи 1 в таблицу Заказы и Enter переходим в таблицу Заказано, в которую вводим все записи, соответствующие заказу 1 (завершаем нажатием Enter); затем опять возвращаемся в таблицу Заказы, вводим запись 2 и т.д.
11. Данные для таблицы Заказы. Данные для столбцов Заказчик и Продавец выбираем из соответствующего списка в ячейке):
| КодЗаказа | Заказчик | ДатаЗаказа | Продавец |
| 1 | Кафе «Минутка» | 03.01.03 | Безруков |
| 2 | Столовая НАУ | 08.01.03 | Большова |
| 3 | Кафе «Минутка» | 11.01.03 | Безруков |
| 4 | Ресторан «Киев» | 23.01.03 | Безруков |
| 5 | Отель «Крещатик» | 04.02.03 | Певцов |
| 6 | Ресторан «Киев» | 13.02.03 | Певцов |
| 7 | Ресторан «Бавария» | 16.02.03 | Певцов |
| 8 | Ресторан «Киев» | 23.02.03 | Большова |
| 9 | Ресторан «Бавария» | 03.03.03 | Дроздова |
| 10 | Столовая НАУ | 17.03.03 | Певцов |
| 11 | Ресторан «Бавария» | 19.03.03 | Дроздова |
| 12 | Отель «Крещатик» | 27.03.03 | Безруков |
12. Данные для заполнения таблицы Заказано. Поля КодЗаказа заполняем сами; коды должны быть такими же, как в таблице Заказы. Так как один заказ может состоять из нескольких видов товара (но в одном заказе не может быть одинаковых видов товара), то коды заказов могут повторяться. Данные для столбца Товар выбираем из списка в ячейке. Количество вводим сами.
| КодЗаказа | Товар | Количество |
| 1 | Сардины в томате | 400 |
| 1 | Краб.палочки | 500 |
| 1 | Сметана | 50 |
| 1 | Молоко | 50 |
| 2 | Краб.палочки | 350 |
| 2 | Шпроты в масле | 60 |
| 2 | Пиво темное | 500 |
| 2 | Пиво светлое | 500 |
| 2 | Конфеты «Прометей» | 100 |
| 3 | Молоко | 65 |
| 3 | Сардины в томате | 230 |
| 3 | Краб.палочки | 50 |
| 3 | Птица | 30 |
| 4 | Сардины в томате | 200 |
| 5 | Сметана | 25 |
| 5 | Мясо | 25 |
| 6 | Конфеты «Прометей» | 75 |
| 7 | Картошка | 30 |
| 7 | Овощи (в ассортим.) | 2 |
| 7 | Пиво темное | 500 |
| 7 | Пиво светлое | 500 |
| 8 | Сардины в томате | 300 |
| 8 | Молоко | 90 |
| 9 | Конфеты «Прометей» | 200 |
| 9 | Конфеты «Ассорти» | 100 |
| 9 | Краб.палочки | 20 |
| 9 | Сметана | 35 |
| 10 | Картошка | 50 |
| 10 | Мясо | 25 |
| 11 | Сметана | 30 |
| 11 | Краб.палочки | 45 |
| 12 | Пиво темное | 100 |
| 12 | Пиво светлое | 100 |
| 12 | Краб.палочки | 100 |
| 12 | Конфеты «Ассорти» | 50 |
| 12 | Шпроты в масле | 30 |