17212

Создание структуры БД средствами SQL

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

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

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

Русский

2013-06-30

272 KB

24 чел.

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

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

Цель работы: Изучить синтаксис инструкций SQL для создания и изменения таблиц БД. Создать структуру БД и расширить ее вспомогательными таблицами – справочниками. Закрепить навыки работы с реляционными СУБД.

1. Реализация запросов SQL в СУБД Access:

Запустить СУБД Access.

После появления диалогового окна Microsoft Access выбрать переключатель Новая база данных и щёлкнуть на кнопке ОК — открывается диалоговое окно Файл новой базы данных.

В раскрывающемся списке Папка установить имя папки, в которую будет помещена создаваемая БД, а в раскрывающийся список Имя файла впечатать имя файла создаваемой БД.

Щёлкнуть на кнопке Создать — открывается диалоговое окно «Имя_файла_БД»: база данных (Имя_базы_данных — имя, которое присваивается создаваемой БД).

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

5.1. Перейти в окне БД на вкладку «Запросы» и щёлкнуть на кнопке Создать — открывается диалоговое окно «Новый запрос».

5.2. Выбрать опцию Конструктор и щёлкнуть на кнопке ОК — открывается бланк запроса и диалоговое окно «Добавление таблицы».

5.3. Закрыть диалоговое окно «Добавление таблицы». Щелкнуть на кнопку в верхнем левом углу. В окне «Запрос: запрос на выборку» набрать команду SQL и активизировать запрос кнопкой .

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

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

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

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

       (<имя_столбца> <тип_столбца>

                         [NOT NULL]

                         [UNIQUE | PRIMARY KEY],

                         [FOREIGN KEY <(ИМЯ_СТОЛБЦА)>

                          REFERENCES <имя_базовой_таблицы> [<имя_столбца>]]

                         , ...)

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

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

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

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

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

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

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

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

Контроль за выполнением указанных условий осуществляет СУБД.

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

CREATE TABLE authors (au_id INT PRIMARY KEY,

                    author VARCHAR(25) NOT NULL);

CREATE TABLE publishers (pub_id INT PRIMARY KEY,

            publisher VARCHAR(255) NOT NULL,url VARCHAR(255));

CREATE TABLE titles (title_id INT PRIMARY KEY,

                    title VARCHAR(255) NOT NULL,

                    yearpub INT, pub_id INT,

                    FOREIGN KEY (pub_id) REFERENCES publishers(pub_id));

CREATE TABLE titleautors (au_id INT REFERENCES authors(au_id),

                      title_id INT,

                      FOREIGN KEY (title_id) REFERENCES titles(title_id));

CREATE TABLE wwwsites (site_id INT PRIMARY KEY,

                      site VARCHAR(255) NOT NULL,

                      url VARCHAR(255));

CREATE TABLE wwwsiteauthors (au_id INT, site_id INT,

FOREIGN KEY (au_id) REFERENCES authors(au_id),

FOREIGN KEY (site_id) REFERENCES wwwsites(site_id));

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

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

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

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

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

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 <имя_базовой_таблицы> <имя_поля>;

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

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

Создаем таблицу – справочник для поля author таблицы authors.

CREATE TABLE spr_authors (author VARCHAR(25) PRIMARY KEY);

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

ALTER TABLE authors ADD CONSTRAINT A1 FOREIGN KEY (author) REFERENCES spr_authors (author);

После создания таблицы – справочника и определения внешнего ключа “Схема данных” пополнится новой таблице spr_authors как показано на рисунке 2.

Рисунок 2. Таблица - справочник spr_authors в базе данных publications 

5. Типы данных SQL

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

Тип данных

Размер

Описание

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
(
см. DOUBLE)

8 байтов

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

UNIQUEIDENTIFIER

128 битов

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

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 знаков.

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

Создать структуру БД POST_IZDEL в которую входят таблицы IZDELIE, ZAKAZ, POSTAVKA и добавить таблицы – справочники NAIMENOV и ZAKAZCHIK.

Обозначения: PK – первичный ключ, FK – внешний ключ.

1). Для создания таблиц использовать инструкцию CREATE TABLE в соответствии с выше описанным синтаксисом команды.

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

Таблица

IZDELIE (kod_modeli (int), naimenov (char (50)), harakteristika (char (80)), cena (money))

PK - kod_modeli

Таблица 

ZAKAZ (nomer_zakaza (int), zakazchik (char (50)), adres (char (50)), data_zakaza (datetime))

PK - nomer_zakaza

Таблица 

POSTAVKA (nomer_zakaza (int), kod_modeli (int), kolichestvo (int))

PK - nomer_zakaza, kod_modeli

FK - nomer_zakaza для таблицы ZAKAZ и kod_modeli для таблицы IZDELIE

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

IZDELIE - POSTAVKA (1:N)

ZAKAZ - POSTAVKA (1:N).

2). При создании таблиц – справочников необходимо

создать таблицу – справочников с помощью CREATE TABLE

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

Для таблиц – справочников NAIMENOV и ZAKAZCHIK связными таблицами являются таблицы IZDELIE и ZAKAZ соответственно. Связь между таблицами осуществляется по полям naimenov и zakazchik из соответствующих таблиц.

Таблицы – справочники

NAIMENOV (naimenov (char (50))

PK (naimenov)

ZAKAZCHIK (zakazchik (char (50))

PK (zakazchik)

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

Какие свойства полей могут быть заданы при создании таблицы? Какие свойства задаются обязательно?

Как задается первичный ключ? Могут ли ключевые поля иметь неопределенные значения?

Как задаются связи между таблицами БД? Как установить связь от одной таблицы к нескольким?

Чем определяется тип связи между таблицами?

В чем заключается поддержка ссылочной целостности? Основные свойства внешнего ключа.

Какова реакция системы на удаление записей ссылочного отношения?

Какова реакция системы на добавление записей ссылочного отношения?

Какое из двух отношений является ссылочным в случае задания связь типа «1:1» между ними?


 

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

69121. Дерева. Основні поняття. Алгоритм роботи з бінарними деревами 80 KB
  Розглянуті у розділі 10.2 списки, стеки та черги палежать до лінійних динамічних структур даних. Визначальною характеристикою лінійних структур є те, що зв’язок між іншими компонентами описується в терминах «попередній-наступний», тобто для кожного компонента лінійної структури...
69123. Масиви в динамічній пам’яті 37.5 KB
  Як уже зазначалось у розділі 10.2, зображення послідовностей однотипних у формі лінійних списків має і переваги, і недоліки. Основним недоліком є значна трудомісткістъ операції доступу до елемента лінійного списку за його номером. Цей недолік непритаманний масивам.
69124. Поняття архітектури комп’ютера. Архітектура комп’ютера фон Неймана. Типи комп’ютерів. Програмне забезпечення 192 KB
  Поняття архітектури обчислювальних систем є одним з основних в інформатиці. Уперше термін «архітектура комп’ютера» був введений фірмою IBM при розробці обчислювальних систем серії IBM 360 і застосований до тих засобів, які може використовувати програміст під час написання програм на рівні машинних команд.
69125. Засоби створення програм. Класифікація мов програмування. Технологія створення программ 74 KB
  Основна функція всіх мов програмування крім машинної полягає у тому щоб надати програмісту засоби абстрагування від характеристик та особливостей апаратного забезпечення на якому виконуватимуться програми. Такий спосіб написання програм називається програмуванням у числових кодах...
69126. Поняття алгоритму й основні алгоритмічні структури. Властивості та способи опису алгоритму. Алгоритмічна структура розгалуження і перетворення 56.5 KB
  Одним з базових понять інформатики є поняття алгоритму. Алгоритм вказує, які операції, пов’язані з обробкою даних, і в якій послідовності треба виконати, щоб отримати розв’язок задачі. Алгоритм розрахований на певного виконавця, з погляду котрого вказівки мають бути елементарними...
69127. Робота у середовищі Borland Pascal 7.0 202.5 KB
  Інтегроване середовище розробки Borland Pascal 7.0 - далі IDE (Integrated Development Environment) Borland Pascal 7.0 - складається з текстового редактора, компілятора, компонувальника, налагоджувача і довідкової системи. Стандартна поставка IDE Borland Pascal 7.0 являє собою...
69128. Словник мови та загальна структура програми. Алфавіт та словник мови 58 KB
  У будь-якій мові програмування програма — це набір зрозумілих компілятору команд. Для створення програм треба знати синтаксис мови, тобто правила запису команд і використання лексичних одиниць мови. Знайомство з мовою розпочнемо з алфавіту.
69129. Прості типи даних. Операції над даними 93 KB
  Поняття типу даних є одним із фундаментальних понять програмування. Тип даних визначає: множину допустимих значень яких може набувати змінна або константа зазначеного типу; множину допустимих операцій що застосовуються до даних певного типу; спосіб зображення даних...