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


 

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

79055. Вклад неопозитивизма в развитии логики и методологии науки 37 KB
  Вклад неопозитивизма в развитии логики и методологии науки. Логика науки – применение идей методов и аппарата логики в анализе научного познания. Развитие логики всегда было тесно связано с практикой теоретического мышления и прежде всего с развитием науки. Методология науки в традиционном понимании это учение о методах и процедурах научной деятельности а также раздел общей теории познания в особенности теории научного познания эпистемологии и философии науки.
79056. Концепция философии науки Т. Куна 25.5 KB
  Концепция философии науки Т. Важнейшей характеристикой знания является его динамика его рост изменение развитие В современной западной философии проблема роста развития знания является центральной в философии науки. переход к новому периоду нормальной науки . Причем последние гораздо более редки в истории развития науки по сравнению с первыми.
79057. Концепция философии науки К. Поппера 28.5 KB
  Карл Поппер предложил в 1967 году различать следующие три мира: во-первых мир физических объектов или физических состояний; во-вторых мир состояний сознания мыслительных ментальных состояний в-третьих мир объективного содержания мышления мир научных идей проблем поэтических мыслей и произведений искусства. Этот третий мир вполне объективен и осязаем. Это мир книг библиотек географических карт мир произведений живописи. Концепция Поппера подчёркивает своеобразие и загадочность знания как объекта исследования: для того чтобы...
79058. Развитие философии науки постпозитивизмом (И.Локатос, П. Фейерабенд, М. Полани) 38.5 KB
  В 60-70 годы 20 века в зап. философии науки развивается течение постпозитивизма. Постпозитивисты (Поппер, Мун, Лакатос, Фейрабенб, Полани) подвергли критике позитивистский идеал факта, введя в анализ науки историческое
79059. Право цивильное и право преторское. Римские магистраты и значение их эдиктов для выработки новой системы права. Процесс взаимодействия права цивильного и преторского. Кодификация эдиктов 28.54 KB
  Римские магистраты и значение их эдиктов для выработки новой системы права. Процесс взаимодействия права цивильного и преторского. civilis – гражданский или квиритское право римляне называли себя квиритами в честь бога войны Яна Квирина – совокупность норм права исходящих от народного собрания позднее – сената. Источники цивильного права – обычаи и законы.
79060. Понятие наследования. Сущность и происхождение наследования. Виды наследования 24.25 KB
  Подобно тому как собственность в экономическом смысле существовала и до образования государства и права а право собственности появилось только с образованием государства так и наследственное право в качестве завершения права собственности появилось только с возникновением государства. Универсальный характер наследования проявляется в том что к наследнику переходят сразу и права и обязанности входящие в состав наследства в том что наследник может приобрести в составе наследства даже такие права и обязанности о существовании которых он...
79061. Понятие права собственности. Содержание права собственности 27.13 KB
  Содержание права собственности. Понятие права собственности и развитие этого института в Риме. Римское право было системой права построенного на начале частной собственности.
79062. Понятие римского частного права. Отличие частного права от права публичного. Основные системы римского частного права 20.29 KB
  Отличие частного права от права публичного. Основные системы римского частного права. Один из римских юристов классического периода Ульпиан проводит разграничение этих двух областей права следующим образом.
79063. Понятие физического лица и правоспособности. Элементы правоспособности. Утрата и ограничение правоспособности (caput) 19.53 KB
  Древний Рим был рабовладельческим государством и поэтому признавал лицом далеко не каждого человека. Субъектом права признавался только свободный человек, поэтому рабы рассматривались не как субъект, а как объект права (говорящее орудие).