Создание структуры бд средствами sql
 Скачать 249 Kb.
|
|
Лабораторная работа № 1 Тема: Создание структуры БД средствами SQL. Цель работы: Изучить синтаксис инструкций SQL для создания и изменения таблиц БД. Создать структуру БД и расширить ее вспомогательными таблицами – справочниками. Закрепить навыки работы с реляционными СУБД. 1. Реализация запросов SQL в СУБД Access: 1. Запустить СУБД Access. 2. После появления диалогового окна Microsoft Access выбрать переключатель Новая база данных и щёлкнуть на кнопке ОК — открывается диалоговое окно Файл новой базы данных. 3. В раскрывающемся списке Папка установить имя папки, в которую будет помещена создаваемая БД, а в раскрывающийся список Имя файла впечатать имя файла создаваемой БД. 4. Щёлкнуть на кнопке Создать — открывается диалоговое окно «Имя_файла_БД»: база данных (Имя_базы_данных — имя, которое присваивается создаваемой БД). 5. Для запуска Конструктора запросов нужно: 5.1. Перейти в окне БД на вкладку « Запросы» и щёлкнуть на кнопке Создать — открывается диалоговое окно « Новый запрос». 5.2. Выбрать опцию Конструктор и щёлкнуть на кнопке ОК — открывается бланк запроса и диалоговое окно « Добавление таблицы». 5.3. Закрыть диалоговое окно « Добавление таблицы». Щелкнуть на кнопку в верхнем левом углу. В окне « Запрос: запрос на выборку» набрать команду SQL и активизировать запрос кнопкой 6. Результатом выполнения запроса на создания таблицы будет наличие таблицы в окне « База данных» в разделе «Таблицы» с именем определенным в операторе 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: 1. Создаем таблицу – справочник для поля author таблицы authors. CREATE TABLE spr_authors (author VARCHAR(25) PRIMARY KEY); 2. Изменяем свойство поля 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 47 7,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 из соответствующих таблиц. Таблицы – справочники 1. NAIMENOV (naimenov (char (50)) PK (naimenov) 2. ZAKAZCHIK (zakazchik (char (50)) PK (zakazchik) 7. Контрольные вопросы 1. Какие свойства полей могут быть заданы при создании таблицы? Какие свойства задаются обязательно? 2. Как задается первичный ключ? Могут ли ключевые поля иметь неопределенные значения? 3. Как задаются связи между таблицами БД? Как установить связь от одной таблицы к нескольким? 4. Чем определяется тип связи между таблицами? 5. В чем заключается поддержка ссылочной целостности? Основные свойства внешнего ключа. 6. Какова реакция системы на удаление записей ссылочного отношения? 7. Какова реакция системы на добавление записей ссылочного отношения? 8. Какое из двух отношений является ссылочным в случае задания связь типа «1:1» между ними? |