Лабораторная работа № 6
Проектирование и изменение структуры таблицы. Условия целостности данных
Цели лабораторной работы. Изучение синтаксиса команд создания и модификации структуры таблиц. Рассмотрения примеров запросов на создание, модификацию и удаление таблиц.
& Теоретический материал.
Синтаксис операторов:
Проектирование структуры таблицы:
CREATE TABLE <имя таблицы> (<имя поля> <тип поля>
[Null | Not Null]
[Default <выражение>]
[CONSTRAINT <имя_ключа> Primary Key| Foreign Key (<индексируемый_атрибут>)]
[References <имя родит таблицы>]];
Модификация структуры таблицы:
ALTER TABLE <имя таблицы>
Add | Alter| Drop [Column] <имя поля> [<тип поля>]
Null | Not Null
[Default <выражение>]
[CONSTRAINT <имя_ключа> Primary Key| Foreign Key (<индексируемый_атрибут>)]
[References <имя родит таблицы>]];
Add [Column] <имя поля> – имя добавляемого поля.
Alter [Column] <имя поля>– имя изменяемого (уже существующего) поля.
Drop [Column] <имя поля> – имя удаляемого поля.
Удаление таблицы (включая все кортежи, хранящиеся в этой таблице):
DROP TABLE <имя_таблицы>;
Создание индекса в SQL:
CREATE INDEX < имя_индеха> ON < имя_таблицы> (< имя_индексируемого_атрибута> );
Определение представлений:
CREATE VIEW <имя_представления>AS <выражение_выборки>;
Уничтожение индекса:
DROP INDEX < имя_индекса> ;
Удаление заданного представления:
DROP VIEW <имя_представления>;
Операции применяемые в выражениях
Арифметические операции: +, –, *, %
Операции над строками: || (+)
Операции сравнения (числовые): =,!= (<>), <, >, <=, >=
Операции сравнения (строковые): =, LIKE
Логические операции: AND, OR, NOT
Функции в SQL операторах.
I. Неагрегатные функции.
1. Арифметические функции.
• ABS
• CEIL, FLOOR
• COS, COSH, SIN, SINH, TAN, TANH
• EXP
• LN, LOG
• MOD
• SIGN
• SQRT
2. Строковые функции
• LOWER, UPPER
Переводят все символы в нижний (верхний) регистр.
• TRIM
Удаляет из строки (слева, справа, с обоих концов) указанные символы.
• OVERLAY
Осуществляет замену в строке заданной подстроки
• SUBSTRING, SUBSTR
Осуществляет извлечение из строки подстроки.
• POSITION, STRPOS
Возвращает позицию подстроки в строке.
• LENGTH
Возвращает длину строки.
3. Функции преобразования типов.
• TO_CHAR
Преобразует данные различных типов в символьные.
• TO_NUMBER
Преобразует строку символов в число.
4. Функции даты и времени.
• CURRENT_DATE
Возвращает текущую системную дату.
5. Другие функции.
II. Агрегатные функции – возвращают единственное значение для нескольких записей. Вначале происходит отбор записей в соответствии с условиями, заданными в предложении WHERE и/или условиях группировки данных, затем, отобранные записи передаются в агрегатную функцию. Агрегатные функции не могут присутствовать в предложении WHERE.
• COUNT
Подсчитывает число записей, соответствующих условиям, заданным в предложении WHERE.
• SUM
Подсчитывает сумму всех значений в указанном столбце.
• AVG
Подсчитывает среднее среди всех значений в указанном столбце.
• MAX
• MIN
Типы связей между таблицами в SQL-операторах
1. Внутреннее связывание (inner join).
Выбираются записи, имеющие значения столбцов, удовлетворяющие условию связывания.
2. Внешнее связывание (outer join).
a) Левое внешнее связывание (left outer join).
В результирующую выборку включаются все значения столбцов, выбираемых из первой (“левой”) таблицы. Если во второй таблице нет записи, удовлетворяющей условиюсвязывания, в качестве значений столбцов второй таблице устанавливается специальное значение NULL.
б) Правое внешнее связывание (right outer join).
в) Полное внешнее связывание (full outer join).
3. Перекрестное связывание (cross join).
Выполняет декартово произведение.
Задания для самостоятельной работы
1. Запустите pgAdmin III.
2. Выполните подключение к серверу.
3. Выберите соответствующую вам базу данных.
4. Создайте представления данных для запросов разработанных в лабораторной работе № 5.
5. Проанализируйте условия целостности данных, определенные в базе данных.
6. Создайте запрос на изменение данных в таблице “Reader”. В результате выполнения запроса все номера читательских билетов, меньшие 100, должны увеличиться на 200. Как повлияют ранее определенные условия целостности на изменение данных в БД?
7. Создайте запрос на изменение структуры таблицы “Place_Cover” с помощью оператора Alter Table. Предусмотрите удаление поля “idPlace_Cover”, создание одноименного поля типа Int8. Созданное поле является первичным ключом таблицы.
8. Создайте запрос на разработку структуры таблицы. Предусмотрите создание 3-4 полей, первичный и внешний ключи, значения по умолчанию для некоторых полей.
9. Удалите таблицу созданную в задании № 8 с помощью команды Drop Table.
10. Создайте индексы для полей cAuthor таблицы Author, cSurname таблицы Reader.
11. Создайте запрос для заполнения пустых ячеек поля “nCurrency” таблицы “Cover” псевдослучайными значениями (функция random() возвращает псевдослучайные вещественные значения из диапазона [0; 1)).
12.
Замечание. Полученные операторы,запишите в тетрадь. Проанализируйте опции применявшихся операторов.
F Контрольные вопросы:
1. Как выполняется проектирование структуры таблицы?
2. Как можно модифицировать структуру таблицы?
3. Как поместить данные в таблицу?
4. Как можно удалить запись из таблицы?
5. 
Что нужно сделать для создания таблицы?
6. Оператор Create Table.
7. Оператор Alter Table.
8. Оператор Drop Table.
9. Оператор Insert Into.
10. Оператор Select.
11. Функции в SQL-операторах.
12. Операции в SQL-операторах.
13. Типы связей между таблицами в SQL-операторах
14. Группировка данных в операторе Select.
15. Первичный и внешние ключи таблицы.
16. Создание представления данных.
17. Удаление представления данных.
18. Условия целостности данных.