17217

Посторонние структуры БД средствами SQL в среде СУБД MS ACCESS

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 1 Тема:Посторонние структуры БД средствами SQL в среде СУБД MS Access. Цель работы: Изучить принципы и приемы построения таблиц БД схемы реляционной БД с использованием синтаксических инструкций SQL запросов. Закрепить навыки работы с реляционн...

Русский

2013-06-30

473.5 KB

10 чел.

Лабораторная работа № 1

Тема: Посторонние структуры БД средствами SQL в среде СУБД MS Access.

Цель работыИзучить принципы и приемы построения таблиц БД, схемы реляционной БД с использованием синтаксических инструкций SQL - запросов. Закрепить навыки работы с реляционными СУБД.

Структура лабораторной работы

  1.  Общие сведенья по работе с СУБД ACCESS. 
    1.  Формирование запросов.
    2.  Создание таблиц.
    3.  Удаление таблиц.
    4.  Модификация таблиц и структуры данных.
  2.  Типы данных Jet SQL.
  3.  Задания к лабораторной работе.
  4.  Оформление отчета.
  5.  Контрольные вопросы.

1. Общие сведенья по работе с СУБД ACCESS.

1.1. Формирование запросов в СУБД Access средствами SQL

Для начала работы с СУБД Access необходимо активизировать соответствующую программу в меню «Пуск» или на «Рабочем столе» компьютера.

После появления диалогового окна Microsoft Access выбрать переключатель «Новая база данных».

В раскрывающемся окне «Файл новой базы данных» в поле «Папка» установить имя папки, в которую будет помещена создаваемая БД, а в поле «Имя файла» внести имя создаваемой БД и щёлкнуть на кнопке «Создать»

Далее все действия с БД будут осуществляться через окно БД, внешний вид которого представлен на рисунке 1.

Рисунок 1. Окно создание объектов базы данных.

Так как создавать таблицы будем выполнять средствами SQL, то все действия будем выполнять в режиме запросов, используя «Конструктор запросов» режим редактора запросов SQL.

Для запуска «Конструктора запросов» нужно:

  1.  Перейти в окне БД на вкладку «Запросы» и щёлкнуть на кнопке «Создать» – открывается диалоговое окно «Новый запрос».
  2.  Выбрать опцию «Конструктор» и щёлкнуть на кнопке «ОК» – открывается бланк запроса и диалоговое окно «Добавление таблицы».
  3.  Закрыть диалоговое окно «Добавление таблицы». Щелкнуть на кнопку в верхнем левом углу, перейдя в режим редактирования текста SQL - запроса. В окне «Запрос: запрос на выборку» набрать команду SQL и активизировать запрос кнопкой . (По умолчанию в редакторе текста SQL устанавливается начальное предложение запроса на выборку SELECT. Для формирования запроса на создание таблицы необходимо удалить SELECT и ввести соответствующую команду.)

Результатом выполнения запроса на создания таблицы будет наличие таблицы в окне «База данных» в разделе «Таблицы» с именем определенным в операторе создания таблиц.

При формировании запросов другого вида (выборка, добавление, удаление, изменение) используется та же последовательность действий пользователя. При этом каждый вид запросов будет сохраняться в своем разделе окна БД: таблицы – в разделе «Таблицы», запросы любого вида – в разделе «Запросы».

Замечание. Описанные ниже синтаксические конструкции SQL соответствуют стандарту СУБД Access. Дополнительные инструкции языка описанные, например в [  ], реализованы в таких СУБД, как Oracle, SQL Server, DB2, Informix и др.

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

Для создания таблиц используется инструкция SQL CREATE TABLE. Общая синтаксическая конструкция представлена ниже.

CREATE TABLE <имя таблицы>

   (<имя столбца> <тип(размер)>

   [NOT NULL  UNIQUE],...,

   [PRIMARY KEY <(имя столбца)>],

   [CONSTRAINT <имя ограничения>] [FOREIGN KEY

   <(имя столбца)> REFERENCES <имя базовой таблицы>

   <имя столбца>],...)

Обязательными параметрами инструкции CREATE TABLE являются:

имя таблицы и список столбцов;

для каждого столбца указываются его имя и тип (см. таблицу типов ниже).

Также могут быть указаны параметры:

NOT NULL - в этом случае элементы столбца всегда должны иметь определенное значение;

один из взаимоисключающих параметров:

  •  UNIQUE - значение каждого элемента столбца должно быть уникальным или
  •  PRIMARY KEY - столбец является первичным ключом.

CONSTRAINT <имя ограничения> - определяет имя ограничения на столбец таблицы.

Замечание. Если в БД устанавливается несколько ограничений, то имена ограничений должны быть уникальны.

FOREIGN KEY <(имя столбца)> REFERENCES <имя базовой таблицы> (<имя столбца>) - эта конструкция определяет тот факт, что столбец текущей таблицы является внешним ключом и указывает на первичный ключ таблицы, на которую он ссылается.

Замечание: Инструкция CREATE TABLE реализованная в СУБД Access не поддерживает некоторых предложений, например, таких как DEFEULT, CHEK и др.

Пример: создания таблиц базы данных publications: 

CREATE TABLE authors (au_id INT,

                    author VARCHAR(25) NOT NULL,

                    PRIMARY KEY(au_id));

CREATE TABLE publishers (pub_id INT,

            publisher VARCHAR(255) NOT NULL,

            url VARCHAR(255), PRIMARY KEY(pub_id));

CREATE TABLE titles (title_id INT,

                    title VARCHAR(255) NOT NULL,

                    yearpub INT, pub_id INT,

                    PRIMARY KEY(title_id),

                    CONSTRAINT a1

                    FOREIGN KEY (pub_id) REFERENCES

                    publishers(pub_id));

CREATE TABLE titleautors (au_id INT,

  title_id INT,

  CONSTRAINT a2 FOREIGN KEY (au_id)

  REFERENCES authors(au_id),

  CONSTRAINT a3 FOREIGN KEY (title_id)

  REFERENCES titles(title_id));

CREATE TABLE wwwsites (site_id INT,

                      site VARCHAR(255) NOT NULL,

                      url VARCHAR(255),

                      PRIMARY KEY(site_id));

CREATE TABLE wwwsiteauthors (au_id INT, site_id INT,

  CONSTRAINT a4 FOREIGN KEY (au_id)

  REFERENCES authors(au_id),

  CONSTRAINT a5 FOREIGN KEY (site_id)

  REFERENCES wwwsites(site_id));

После выполнения запросов на создание таблиц “Схема данных” должна выглядеть как на рисунке 2.

Рисунок 2. Схема базы данных publications

1.3. Удаление таблицы

Для удаления таблиц используется инструкция SQL DROP TABLE.

DROP TABLE <имя таблицы>

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

DROP TABLE titleautors

Если DROP TABLE применять к базовой таблицы (таблица, которая в схеме данных соответствует признак уникальности значений в связном атрибуте «1»), то перед удалением таблицы необходимо удалить связи с другими, связными, таблицами.

Синтаксическая конструкция удаления связи рассматривается в следующем подразделе.

1.4. Модификация таблиц и структуры данных

Модификация таблиц может быть реализована для различных случаев с помощью инструкции ALTER TABLE. Возможные ситуации изменения таблицы и соответствующий синтаксис команд приведен в таблице 1.

Таблица 1. Примеры использования инструкции ALTER TABLE.

1.

Добавить столбцы

ALTER TABLE <имя таблицы> ADD COLUMN
            <имя столбца> <тип(размер)>
           [NOT NULL  UNIQUE],
           [PRIMARY KEY (<имя столбца>)],...

2.

Удалить столбцы

ALTER TABLE <имя таблицы>

             DROP COLUMN <имя столбца>,...

3.

Модификация типа столбцов

ALTER TABLE <имя таблицы> ALTER COLUMN
            <имя столбца> <тип(размер)>
           [NOT NULL  UNIQUE],
           [PRIMARY KEY (<имя столбца>)],...

4.

Определение внешнего ключа. Установить связь

ALTER TABLE <имя таблицы> ADD CONSTRAINT <имя ограничения>

            FOREIGN KEY (<имя поля>)

            REFERENCES <имя базовой таблицы> (<имя поля>)

5.

Снять ограничения внешнего ключа. Удалить связь

ALTER TABLE <имя таблицы> DROP CONSTRAINT <имя ограничения>

Для добавления новой таблицы (CREATE  TABLE) к уже существующей базе данных, необходимо установить связь с одной из таблиц базы данных, назначив внешним ключом поле связной таблицы, то есть необходимо модифицировать структуру соответствующей таблицы БД инструкцией ALTER  TABLE по примеру таблицы 1, строка 4 “Определение внешнего ключа. Установить связь.

Пример: расширение структуры БД publications таблицей publishers: 

  1.  Создаем таблицу – справочник издательств spr_publisher для поля publisher таблицы publishers.

CREATE TABLE spr_publisher (publisher VARCHAR(255),

                             address VARCHAR(150), PRIMARY KEY (publisher));

  1.  Изменяем свойство поля publisher таблицы publishers, назначив ему статус “внешнего ключа”.

ALTER TABLE publishers ADD CONSTRAINT a6 FOREIGN KEY (publisher)

                          REFERENCES spr_publisher (publisher);

После создания таблицы – справочника и определения внешнего ключа “Схема данных” дополнится новой таблице spr_publisher как показано на рисунке 3. Если при просмотре схемы данных новая таблица не отображается, то можно, нажав правую клавишу мыши, выбрать режим «Отобразить все», отобразить недостающую таблицу. После этого сохранить «Схему данных».

 

Рисунок 3. Расширение структуры БД publications 

2. Типы данных SQL (Microsoft Jet SQL)

Типы данных языка SQL включают 13 основных типов данных, определенных в ядре базы данных Microsoft Jet. Название типов данных и их свойства представлены в таблице 2.

Таблица 2. Типы данных Microsoft Jet

Тип данных

Размер

Описание

1

2

3

BINARY

1 байт на знак

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

BIT

1 байт

Значения «Да» (Yes) и «Нет» (No), а также поля, содержащие одно из двух возможных значений.

TINYINT

1 байт

Целое значение от 0 до 255.

MONEY

8 байтов

Масштабируемое целое от
–922 337 203 685 477,5808 до 922 337 203 685 477,5807.

DATETIME

8 байтов

Дата или время; допустим любой год от 100 до 9999.

UNIQUEIDENTIFIER

128 битов

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

1

2

3

REAL

4 байта

Число с плавающей точкой и одинарной точностью
от –3,402823E38 до –1,401298E-45 для отрицательных значений,
от 1,401298E-45 до 3,402823E38 для положительных значений или значение 0.

FLOAT

8 байтов

Число с плавающей точкой и двойной точностью
от –1,79769313486232E308
до –4,94065645841247E-324 для отрицательных значений,
от 4,94065645841247E-324
до 1,79769313486232E308 для положительных значений или значение 0.

SMALLINT

2 байта

Короткое целое от –32 768 до 32 767 (см. «Примечания»).

INTEGER

4 байта

Длинное целое от –2 147 483 648
до 2 147 483 647.

DECIMAL

17 байтов

Тип данных для хранения точных числовых значений от -10^28 - 1 до 10^28 - 1. Точность (1 - 28) и фактор масштабирования (от 0 до заданной точности) определяются пользователем. По умолчанию точность и фактор масштабирования равны соответственно 18 и 0.

TEXT

2 байта на знак

От 0 до 2,14 Гбайт.

IMAGE

Не ограничено

От 0 до 2,14 Гбайт. Используется для объектов OLE.

CHAR

2 байта на знак

От 0 до 255 знаков.

3. Задание к лабораторной работе

(I)

Создание схемы БД.

Создать структуру БД POST_IZDEL_<номер рабочего места> (поставка изделий), в которую входят таблицы POST (поставщик), DETAL (деталь), POSTKA (поставка).

(II)

Изменение схемы БД.

Создать таблицу – справочник материалов изделий SP_MATER. Установить связь с основной структурой БД.

(III)

Удаление таблиц из схемы БД.

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

(IV)

Определение текущего состояния БД.

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

Выполнение лабораторной работы. Свойства таблиц и атрибутов.

I). Для создания таблиц использовать инструкцию CREATE TABLE в соответствии с выше описанным синтаксисом команды. Убедится в правильности схемы БД, просмотрев окно схемы данных (см. рисунок 2). Замечание. В Access 97 синтаксическая конструкция CREATE  TABLE не использует предложение PRIMARY  KEY, таким образом, установление первичного ключа нужно выполнить в конструкторе построения таблиц. Для этого после создания таблицы с помощью инструкции CREATE  TABLE необходимо в окне БД выделить таблицу и нажать кнопку «Конструктор». В таблице выделить поле, соответствующее первичному ключу и на панели инструментов нажать кнопку ключа . Сохранить таблицу.

Структура таблиц:

Таблица:

POST (NP int, IMIA char(30), ADRES char(50))

Свойства полей:

NP – первичный ключ

IMIA – обязательное значение

Таблица:

DETAL (ND char(3), NAIM char(25), MATER char(30), CENA money)

Свойства полей:

ND – первичный ключ

NAIM, MATER – обязательное значение

Таблица:

POSTKA (NP int, ND char(3), KOL int)

Свойства полей:

NP, ND – составной первичный ключ

Замечание. При создании таблицы в Access 97 не задавать первичный ключ, так как в этой версии не поддерживается предложение PRIMARY  KEY.

NP – внешний ключ для таблицы POST

ND – внешний ключ для таблицы DETAL

KOL – обязательное значение

Таким образом, связи между таблицами будут соответствовать схеме

DETAL - POSTKA (1:M)

POST - POSTKA (1:M).

II). Для создания справочника необходимо

изменить тип поля MATER таблицы DETAL, установив тип char(3) (см. таблицу 1 строка 3). Замечание. В Access 97 синтаксическая конструкция подобная таблицы 1 строки 3 не работает. Таким образом, для изменения типа поля нужно его удалить (см. таблицу 1 строка 2) и добавить новый с требуемыми параметрами (см. таблицу 1 строка 2).

создать таблицу SP_MATER с помощью CREATE TABLE

добавить свойство внешнего ключа к связному полю MATER зависимой, относительно справочника, таблицы DETAL с помощью ALTER TABLE (см. таблицу 1 строка 4), установив связь с полем NM таблицы SP_MASTER

убедится в правильности схемы БД, просмотрев окно схемы данных (см. рисунок 2).

Таблица:

SP_MATER (NM char(3), NAZVANIE char(30))

Свойства полей:

NM – первичный ключ

NAZVANIE – обязательное значение

III) Для выполнения задания необходимо:

создать таблицу SP_ADRES с помощью CREATE TABLE.

добавить свойство внешнего ключа к связному полю ADRES зависимой, относительно справочника, таблицы POST с помощью ALTER TABLE (см. таблицу 1 строка 4) установив связь с полем ADRES таблицы SP_ADRES

убедится в правильности схемы БД, просмотрев окно схемы данных (см. рисунок 2)

удалить таблицу SP_ADRES с помощь DROP TABLT, предварительно удалив связь с помощью ALTER TABLE (см. таблицу 1 строка 5). Замечание. Попробовать выполнить удаление таблицы без предварительного удаления связи.

Таблица:

SP_ADRES (ADRES char(50))

Свойства полей:

ADRES – первичный ключ

Таким образом, связь между таблицами будет соответствовать схеме

SP_ADRES - POST (1:M)

IV) Для заполнения таблиц необходимо воспользоваться мастером заполнения Access. Для этого необходимо:

  •  перейти в окно БД (см. рисунок 1), где после выполнения соответствующих инструкций SQL будет отображен список таблиц
  •  выделить нужную таблицу и нажать на кнопку «Открыть»
  •  заполнить таблицы в следующем порядке: сначала заполняются базовая таблица SP_MATER, затем базовые таблицы POST и DETAL (здесь порядок неважен), и последней заполняется зависимая таблица POSTKA.

Пример заполнения таблиц:

Поставки

POSTKA

NP

ND

KOL

1

Д1

10

1

Д2

25

1

Д3

48

1

Д4

8

1

Д5

32

1

Д6

25

1

Д7

13

1

Д8

7

2

Д1

17

2

Д5

17

2

Д8

25

3

Д1

16

3

Д4

52

3

Д7

9

4

Д5

40

4

Д7

67

5

Д5

173

5

Д8

13

6

Д1

41

6

Д2

52

Деталь

DETAL

ND

NAIM

MATER

CENA

Д1

ГАЙКА

M1

1,25

Д2

БОЛТ

M2

3

Д3

ГАЙКА

M2

2,25

Д4

КОРПУС

M2

20,6

Д5

ГАЙКА

M2

1,87

Д6

БОЛТ

M1

2,5

Д7

ФЛАНЕЦ

M2

5,2

Д8

КОРПУС

M3

10,57

Поставщики

POST

NP

IMIA

ADRES

1

ДЖОН

СУМЫ

2

БОРИС

МОСКВА

3

МАША

ТОКИО

4

ДЖОН

СИЭТЛ

5

КОЛЯ

ХАРЬКОВ

6

МАША

ХАРЬКОВ

54

ПЕТЯ

СУМЫ

Справочник материалов

SP_MATER

NM

MATER

M1

СТАЛЬ

M2

ЧУГУН

M3

ПЛАСТИК

4. Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все инструкции SQL реализованные в лабораторной работе, включая инструкции создания и удаления таблицы SP_ADRES.
  4.  Вывод.

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

  1.  Что такое отношение реляционной БД?
  2.  Атрибут, схема, подсхема, экземпляр схемы БД.
  3.  Свойства ключа отношения.
  4.  Может ли отношений содержать несколько первичных ключей.
  5.  Типы связей между таблицами.
  6.  Какие свойства полей задаются обязательно при создании таблицы БД?
  7.  Как задается первичный ключ? Могут ли ключевые поля иметь неопределенные значения?
  8.  Как задаются связи между таблицами БД? Как установить связь от одной таблицы к нескольким?
  9.  Чем определяется тип связи между таблицами?
  10.  В чем заключается поддержка ссылочной целостности? Основные свойства внешнего ключа.


 

А также другие работы, которые могут Вас заинтересовать

15283. Технологія та прийоми програмування мовою Асемблер 142 KB
  Лабораторна робота № 9 Тема: Технологія та прийоми програмування мовою Асемблер. Мета роботи: Набути навичок роботи з масивами при програмуванні мовою Асемблера. Навчитися описувати одновимірні масиви в програмі; ініціювати масив; орган...
15284. Налагодження програми мовою Асемблер з оброблення двовимірних масивів 149 KB
  Налагодження програми мовою Асемблер з оброблення двовимірних масивів Лабораторна робота № 10 Тема: Технологія та прийоми програмування мовою Асемблер. Мета роботи: Набути навичок роботи з масивами при програмуванні мовою Асемблера.
15285. Налагодження програми мовою Асемблер з використанням циклів та розгалужень 102.5 KB
  Налагодження програми мовою Асемблер з використанням циклів та розгалужень Тема: Технологія та прийоми програмування мовою Асемблер. Мета роботи: набути навичок з реалізації задач мовою Асемблер що містять цикли та розгалуження. Ко...
15286. Налагодження програми мовою Асемблер з використанням арифметичних операцій 76.5 KB
  Налагодження програми мовою Асемблер з використанням арифметичних операцій Лабораторна робота № 13 Тема: Технологія та прийоми програмування мовою асемблера. Мета роботи: Набути навичок з реалізації математичних задач мовою асемблера....
15287. Налагодження програми мовою Асемблер з використанням логічних операцій 76 KB
  Налагодження програми мовою Асемблер з використанням логічних операцій Лабораторна робота № 14 Тема: Технологія та прийоми програмування мовою Асемблера. Мета роботи: Набути навичок застосування логічних команд при програмуванні мов...
15288. Налагодження програми мовою Асемблер з використанням підпрограм 104 KB
  Налагодження програми мовою Асемблер з використанням підпрограм Лабораторні роботи № 15 16 Тема: Технологія та прийоми програмування мовою Асемблер. Мета роботи: Набути навичок роботи з підпрограмами. Навчитися передавати параметри з осно...
15289. Налагодження програми мовою Асемблер з використанням екранних операцій 132.5 KB
  Налагодження програми мовою Асемблер з використанням екранних операцій Лабораторна робота Тема: Переривання та введення/виведення інформації. Мета роботи: Набути навичок роботи з функціями введення/виведення інформації. ...
15290. Налагодження програми мовою Асемблер з використанням макросів 99 KB
  Налагодження програми мовою Асемблер з використанням макросів Лабораторна робота № 18 Тема: Технологія та прийоми програмування мовою Асемблер. Мета роботи: Набути навичок роботи з макросами при програмуванні мовою Асемблера. Навчитися с
15291. Компьютерная графика в визуальном программировании 1.18 MB
  Данные методические указания предназначены для самостоятельной работы студентов всех специальностей и используются при выполнении лабораторных и контрольных работ по курсам Информатика и Алгоритмизация и программирование на языке Visual Basic.