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.  В чем заключается поддержка ссылочной целостности? Основные свойства внешнего ключа.


 

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

29497. ЧЕЛОВЕК, ТОЛПА И МАССА В ОБЩЕСТВЕННОМ МНЕНИИ 104.5 KB
  В частности это относится к пугающему одних и ободряющему других в зависимости от позиции представлению о всемогуществе масскоммуникативного влияния на массовую аудиторию на массового человека. В конечном счете это приводит к одной из граней извечной проблемы общественного человека: как и насколько может и желает человек поддаваться давлению коммуникативных средств массового поражения. О генезисе массового общества С.
29498. ВОЗВРАЩАЯСЬ К ПРОБЛЕМЕ СОЦИАЛЬНОЙ ЭЛИТЫ 141.5 KB
  Но каждое время то есть каждая социальная ситуация выбирает поддерживает пестует продвигает подходящий для нее тип человека. Если на поверхности советской системы находился человек послушнокарьерный то с ее распадом на переднем плане в политической жизни бизнесе медиа социальнонаучной сфере и около них оказался человек ловкий ориентированный на ближайший успех и не связанный ни ценностными ни социальногрупповыми рамками ответственности. Массовый человек ориентируется практически не на те звездные образцы политкумиров...
29499. «СРЕДНИЙ ЧЕЛОВЕК»: ФИКЦИЯ ИЛИ РЕАЛЬНОСТЬ 102 KB
  В соответствии с такой исследовательской ориентацией предметом рассмотрения прежде всего становится человек как респондент массового исследования а лишь затем возникает проблема социальногрупповой типологии. в рамках исследовательской программы Советский человек. Средний показатель I высшая 1 1 1 1 1 1 1 II 1 1 1 1 1 1 1 III 3 4 3 2 3 3 3 IV 6 5 6 4 7 6 5 V 21 20 24 21 19 19 20 VI 17 11 15 14 13 13 12 VII 18 13 14 16 16 15 15 VIII 12 16 15 15 16 18 15 IX 9 11 10 10 10 10 9 X низшая 8 17 13 16 15 14 14 Средний статус 626 697 675...
29500. ИНДИКАТОРЫ И ПАРАДИГМЫ КУЛЬТУРЫ В ОБЩЕСТВЕННОМ МНЕНИИ 109 KB
  Нужен какойто переход к теоретическому уровню анализа построению работоспособных гипотез и моделей культуры или как было предложено Т. Исходное предположение состоит в том что получаемые исследователями в массовых опросах глубоких интервью статистике данные относятся к состоянию видимых терминалов скрытого от невооруженного глаза сложного и в определенном смысле целостного механизма культуры. Перемены социальные и культурные: разные шкалы времени Понятно что все феномены и процессы культуры могут существовать реально только в...
29501. ФЕНОМЕН ВЛАСТИ В ОБЩЕСТВЕННОМ МНЕНИИ: ПАРАДОКСЫ И СТЕРЕОТИПЫ ВОСПРИЯТИЯ 123.5 KB
  При этом за пределами внимания остаются особенности представлений о природе и функциях власти присущие массовому сознанию современного общества. Необходимые разграничения В нашем общественном мнении обычно слабо различаются механизмы и функции власти структура властных институтов роли и действия конкретных лиц наделенных властью. Многочисленные опросные данные относительно доверия к социальным институтам регулярно публикуемые в журнале Мониторинг показывают существенные различия в отношении населения к власти федерального центра и...
29502. ИНДЕКСЫ СОЦИАЛЬНЫХ НАСТРОЕНИЙ В «НОРМЕ» И В КРИЗИСЕ 164 KB
  В последнее время большую международную известность приобрел индекс потребительских настроений который в нашей стране разрабатывается группой ИПНРоссия на основе регулярных мониторинговых исследований ВЦИОМ; его результаты широко освещаются в периодической печати в том числе в журнале Мониторинге общественного мнения2. По аналогичным методикам могут быть построены как частные так и сводный индексы социальных настроений и установок. По своей методологической природе любой сконструированный индекс величина искусственно полученная в...
29503. ПРОБЛЕМА ЭМОЦИОНАЛЬНОГО БАЛАНСА ОБЩЕСТВА 182.5 KB
  Высказанные им соображения о методологической слабости психологической трактовки распространенных в исследованиях общественного мнения в том числе и проведенных ВЦИОМ перечней различных страхов представляются вполне правомерными. Представляется полезным обсудить особенности природы и функций социальных страхов различных типов в их соотношении с другими компонентами эмоционального поля общества например интересами радостями позитивными оценками и переживаниями. Первое и важнейшее различение с которым приходится сталкиваться при...
29504. «ЧЕЛОВЕК СОВЕТСКИЙ» ПЯТЬ ЛЕТ СПУСТЯ: 1989-1994 (Предварительные итоги сравнительного исследования) 86 KB
  ВЦИОМ провел обширное исследование Советский человек результаты которого составили основу коллективной монографии2. было осуществлено новое исследование направленное на выявление изменений в установках ценностях нормативных структурах поведения человека в условиях социальных перемен и потрясений прошедшего периода. В первом исследовании было опрошено 2700 человек в нескольких республиках СССР в том числе 1325 человек в России во втором 3000 человек в различных регионах России.
29505. ВОЗВРАЩАЯСЬ К ФЕНОМЕНУ «ЧЕЛОВЕКА СОВЕТСКОГО»: проблемы методологии анализа 89 KB
  Все это совершенно новые типы ориентаций не имеющие аналогов и корней в советской реальности или в традиционных характеристиках человека советского. В условиях продолжающегося кризиса государственно-политических институтов общества не только важнейшими показателями его состояния но в значительной мере и условием социального выживания являются именно те нормативные и ценностные структуры которые действуют на уровне социального человека то есть те которые интериоризированы в структуре усвоенных им ориентаций и рамок восприятия...