Учетные записи пользователей

Учётная запись пользователя состоит из имени пользователя и атрибутов пользователя, включающих:

1. Пароль для проверки прав доступа к базе данных.

2. Привилегии и роли.

3. Табличное пространство по умолчанию для объектов базы данных.

4. Временное табличное пространство по умолчанию для обработки запросов.

 

Оборудование

Персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

 

Задание на работу

1. Установите СУБД PostgreSQL 9.6 (или новее) и клиент управления СУБД pgAdmin4. Приведите скриншоты шаговой установки.   

2. Подключитесь к базе данных через текстовый терминал (с помощью программы psql) от имени пользователя PostgreSQL и создайте базу данных. Приведите в отчете скриншоты терминала с выполненными командами и их результатом.

 

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

1. Каковы основные функции СУБД?

2. Назовите отличительные черты реляционных баз данных?

3. Что представляют собой базы данных?

4. Назовите преимущества архитектуры «клиент-сервер»?

5. Какие функции перешли к среднему уровню обработки данных в трехуровневой архитектуре «клиент-сервер»?

6. Определите роль сервера в двухуровневой архитектуре «клиент-сервер».

 

 

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

Создание таблиц

Цели и задачи работы

Целью лабораторной работы является изучение и практическое применение функций для создания таблиц.

 

Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

 

Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.

 

Теоретические сведения

Создание объектов базы данных осуществляется с помощью операторов языка запросов SQL.

Для создания таблицы базы данных (БД) используется команда CREATE TABLE. CREATE TABLE создаёт новую, изначально пустую таблицу в текущей базе данных. CREATE TABLE также автоматически создаёт составной тип данных, соответствующий одной строке таблицы. Таким образом, имя таблицы не может совпадать с именем существующего типа в этой же схеме.

Синтаксис команды CREATE TABLE:

CREATE TABLE name_table (name_columndata_type,…);

Для удаления таблицы используется команда DROP TABLE. DROP TABLE удаляет таблицы из базы данных. Удалить таблицу может только её владелец, владелец схемы или суперпользователь. Чтобы опустошить таблицу, не удаляя её саму, вместо этой команды следует использовать DELETE или TRUNCATE.

Синтаксис команды DROP TABLE:

DROP TABLE name_table;

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

Возможные синтаксисы команды ALTER TABLE:

• Запрос добавляющий столбец в конец таблицы ALTER TABLE table1 (column1 varchar(10));

• Изменение размера столбца ALTER TABLE table1 MODIFY (column1 varchar(20));

• Удаление столбца таблицы ALTER TABLE table1 DROP COLUMN column1;

• Изменение имени таблицы ALTER TABLE table1 RENAME TO table2;

При создании (изменении) таблицы могут быть определены ограничения на вводимые значения. В этом случае SQL будет отвергать любое из них при не соответствии заданным критериям. Ограничения могут быть статическими, ограничивающими значения или диапазон значений, вставляемых в столбец (CHECH, NOT NULL). Они могут иметь связь со всеми значениями столбца, ограничивая новые строки значениями, которые не содержатся в столбцах или их наборах (уникальные значения, первичные ключи). Ограничения могут также определяться связью со значениями, находящимися в другой таблице, допуская, например, вставку в столбец только тех значений, которые в данный момент содержатся так- же в другом столбце другой или этой же таблицы (внешний ключ). Эти ограничения носят динамический характер.

Существует два основных типа ограничений - ограничения на столбцы и ограничения на таблицу. Ограничения на столбцы (COLUMN CONSTRAINTS) применимы только к отдельным столбцам, а ограничения на таблицу (TABLE CONSTRAINTS) применимы к группам, состоящим из одного или более столбцов. Ограничения на столбец добавляются в конце определения столбца после указания типа данных и перед окончанием описания столбца (запятой). Ограничения на таблицу размещаются в конце определения таблицы, после определения последнего столбца.

Чтобы запретить возможность использования в поле NULL-значений, можно при создании таблицы командой CREATE TABLE указать для соответствующего столбца ключевое слово NOT NULL. Это ограничение применимо только к столбцам таблицы. NULL – это специальный маркер, обозначающий тот факт, что поле пусто.

Если при создании таблицы для столбца указывается ограничение UNIQUE, то база данных отвергает любую попытку ввести в это поле какой- либо строки значение, уже содержащееся в том же поле другой строки. Это ограничение применимо только к тем полям, которые были объявлены NOT NULL.

Можно сделать уникальными группу полей, указав UNIQUE в качестве ограничений таблицы. При объединении полей в группу важен порядок, в котором они указываются. Ограничение на таблицу UNIQUE является полезным, если требуется поддерживать уникальность группы полей. Ограничения уникальности гарантируют, что данные в определённом столбце или группе столбцов уникальны среди всех строк таблицы. Например, если в нашей базе данных не допускается, чтобы студент сдавал в один день больше одного экзамена, то можно в таблице объявить уникальной комбинацию значений полей STUDENT_ID и EXAM_DATE. Для этого следует создать таблицу EXAM_MARKS таким способом.

CREATE TABLE EXAM_MARKS

(EXAMID INTEGER NOT NULL,

STUDENTID INTEGER NOT NULL,

SUBJID INTEGER NOT NULL,

MARK CHAR(1),

EXAM_DATEDATE NOTNULL,

UNIQUE (STUDENT_ID, EXAM_DATE));
Ограничениям таблиц можно присваивать уникальные имена. Преимущество явного задания имени ограничения состоит в том, что в этом случае при выдаче системой сообщения о нарушении установленного ограничения будет указано его имя, что упрощает обнаружение ошибок.

Для присвоения имени ограничению используется несколько изменённый синтаксис команд CREATE TABLE и ALTER TABLE.

Приведённый выше пример запроса изменяется следующим образом:

CREATE TABLE EXAM_MARKS

(EXAMID INTEGER NOT NULL,

STUDENTID INTEGER NOT NULL,

SUBJID INTEGER NOT NULL,

MARK CHAR(1),

EXAM_DATEDATE NOTNULL,

CONSTRAINT STUD_SUBJ_CONSTR

UNIQUE (STUDENT_ID, EXAM_DATE);

В этом запросе STUD_SUBJ_CONSTR - это имя, присвоенное указанному

ограничению таблицы. Первичные ключи таблицы - это специальные случаи комбинирования ограничений UNIQUE и NOT NULL. Первичные ключи имеют следующие особенности:

• таблица может содержать только один первичный̆ ключ;

• внешние ключи по умолчанию ссыпаются на первичный̆ ключ таблицы;

• первичный̆ ключ является идентификатором строк таблицы (строки, однако, могут идентифицироваться и другими способами).

Улучшенный вариант создания таблицы первичным ключом имеет теперь следующий вид:

CREATE TABLE STUDENT (STUDENTID INTEGER PRIMARY KEY,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

Ограничение PRIMARY KEY может также быть применено для нескольких полей, составляющих уникальную комбинацию значений – составнойпервичный ключ. Мы можем применить ограничение таблицы PRIMARY KEY, объявив пару EXAM_ID и STUDENT_ID первичным ключом таблицы:

CREATE TABLE NEW_EXAM_MARKS

(STUDENTID INTEGER NOT NULL,

SUBJID INTEGER NOT NULL,

MARK INTEGER,

DATA DATE,

CONSTRAINT EX_PR_KEY PRIMARY KEY(EXAM_ID,STUDENT_ID));

Ограничение CHECK позволяет определять условие, которому должно удовлетворять вводимое в поле таблицы значение, прежде чем оно будет принято. Любая попытка обновить или заменить значение поля такими, для которых предикат, задаваемый ограничением CHECK, имеет значение ЛОЖЬ, будет отвергаться.

Рассмотрим таблицу STUDENT. Значение столбца STIPEND в этой таблице STUDENT выражается десятичным числом. Наложим на значения этого столбца следующее ограничение – величина размера стипендии должна быть меньше 200.

Соответствующий запрос имеет следующий вид.

CREATE TABLE STUDENT

(STUDENTID INTEGER PRIMARY KEY,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER CHECK (ST IPEND < 200),

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

В SQL имеется возможность при вставке в таблицу строки, не указывая значений некоторого поля, определять значение этого поля по умолчанию. Наиболее часто используемым значением по умолчанию является NULL. Это значение принимается по умолчанию для любого столбца, для которого не было установлено ограничение NOT NULL. Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:

Значение поля по умолчанию указывается в команде CREATE TABLE тем же способом, что и ограничение столбца, с помощью ключевого слова

DEFAULT <значение по умолчанию>.

Строго говоря, опция DEFAULT не имеет ограничительного свойства, так как она не ограничивает значения, вводимые в поле, а просто конкретизирует значение поля в случае, если оно не было задано.

Когда каждое значение, присутствующее в одном поле таблицы, представлено в другом поле другой или этой же таблицы, говорят, что первое поле ссылается на второе. Это указывает на прямую связь между значениями двух полей. Поле, которое ссылается на другое поле, называется внешним ключом, а поле, на которое ссылается другое поле, называется родительским ключом. Так что поле UNIV_ID таблицы STUDENT – это внешний ключ (оно ссылается на поле другой таблицы), а поле UNIV_ID таблицы UNIVERSITY, на которое ссылается этот внешний ключ – это родительский ключ.

На практике внешний ключ не обязательно может состоять только из одного поля. Подобно первичному ключу, внешний ключ может состоять из любого числа полей. Внешний ключ и родительский ключ, на который он ссылается, конечно же, должны быть определены на одинаковом множестве полей (по количеству полей, типам полей и порядку следования полей).

Для решения вопросов поддержания ссылочной целостности в SQL используется ограничение FOREIGN KEY. Назначение FOREIGN KEY – это ограничение допустимых значений поля множеством значений родительского ключа, ссылка на который указывается при описании данного ограничения FOREIGN KEY. Ограничение внешнего ключа указывает, что значения столбца (или группы столбцов) должны соответствовать значениям в некоторой строке другой таблицы. Это называется ссылочной целостностью двух связанных таблиц.

Проблемы обеспечения ссылочной целостности возникают как при вводе значений поля, являющегося внешним ключом, так и при модификации/удалении значений поля, на которое ссылается этот ключ (родительского ключа). Одно из действий ограничения FOREIGN KEY – это отклонение (блокировка) ввода значений внешнего ключа, отсутствующих в таблице с родительским ключом. Также это ограничение воздействует на возможность изменять или удалять значения родительского ключа.

Ограничение FOREIGN KEY используется в командах CREATE TABLE и ALTER TABLE при создании или модификации таблицы, которая содержит поле, которое требуется объявить внешним ключом. В команде указывается имя родительского ключа, на который имеется ссылка в ограничении FOREIGN KEY.

Синтаксис ограничения FOREIGN KEY имеет следующий вид:

FOREIGNKEY < список столбцов >

REFERENCES < родительская таблица > [<родительский ключ >];

В этом предложении список столбцов – это список из одного или более столбцов таблицы, которые будут созданы или изменены командами CREATE TABLE или ALTER TABLE (должны быть отделены друг от друга запятыми). Параметр родительская таблица - это имя таблицы, содержащейродительский ключ. Это, в частности, может быть и таблицей, которая создается или изменяется текущейкомандой. Параметр родительский ключ представляет собой список столбцов родительской таблицы, которые составляют собственно родительский ключ. Оба списка столбцов, определяющих внешний и родительский ключи, должны быть совместимы, а именно:

• содержать одинаковое число столбцов.

• последовательность (1-й, 2-й, 3-й и т.д.) столбцов списка внешнего ключа должны иметь типы данных и размеры, совпадающие с соответствующими (1-м, 2-м, 3-м и т.д.) столбцами списка родительского ключа.

При применении команды ALTER TABLE к какой-либо таблице для задания ограничения FOREIGN KEY, значения внешнего ключа этой таблицы и родительского ключа соответствующей таблицы должны находиться в состоянии ссылочной целостности. В противном случае команда будет отклонена.

Синтаксис команды ALTER TABLE в этом случае имеет следующий вид:

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

ADD CONSTRAINT <имя ограничения>

FOREIGN KEY (< список столбцов внешнего ключа >)

REFERENCES < имя родительской таблицы >

[(< список столбцов родительского ключа >)];

Ограничение внешнего ключа может указываться не для всей таблицы, как это было показано выше, а непосредственно на соответствующий столбец таблицы. При таком варианте, называемом ссылочным ограничением столбца, ключевое слово FOREIGN KEY фактически не используется. Просто используется ключевое слово REFERENCES и далее указывается имя родительского ключа, подобно следующему примеру:

CREATE TABLE STUDENT

(STUDENTID INTEGER PRIMARY KEY,

SURNAME CHAR (25),

NAME CHAR (10),

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER REFERENCES UNIVERSITY(UNIV_ID));
Команда определяет поле STUDENT.UNIV_ID как внешний ключ, использующий в качестве родительского ключа поле UNIVERSITY.UNIV_ID, являющееся ключом таблицы UNIVERSITY.

Эта форма эквивалентна следующему ограничению таблицы STUDENT:

FOREIGN KEY (UNIV_ID) REGERENCES UNIVERSITY (UNIV_ID) или, вдругой записи,

CONSTRAINT UNIV_FOR_KEY FOREIGN KEY (UNIV_ID) REFERENCES UNIVERSITY (UNIV_ID).

Как уже говорилось, при использовании команд INSERT и UPDATE для модификации значений столбца, объявленного как внешний ключ, вновь вводимые значения должны уже быть обязательно представлены в фактически присутствующих значениях столбца, объявленного родительским ключом. При этом можно помещать в эти поля пустые (NULL) значения, несмотря на то, что значения NULL не допустимы в родительских ключах. Можно также удалять (DELETE) любые строки с внешними ключами из таблицы, в которой̆ эти ключи объявлены.

При необходимости модификации значений родительского ключа дело обстоит иначе. Использование команды INSERT, которая осуществляет ввод новой записи, не вызывает никаких особенностей, при которых возможно нарушение ссылочной целостности. Однако команда UPDATE, изменяющая значение родительского ключа и команда DELETE, удаляющая строку, содержащую такой̆ ключ, содержат возможность нарушения согласованности значений родительского и ссылающихся на него внешних ключей. Например, может возникнуть так называемая "висячая" ссылка внешнего ключа на несуществующее значение родительского ключа, что совершенно не допустимо. Чтобы при применении команд UPDATE и DELETE к полю, являющемуся родительским ключом, не нарушалась целостность ссылки, возможны следующие варианты действий̆.

• Любые изменения значений родительского ключа запрещаются и при по- пытке их совершения отвергаются (ограничение NO ACTION или RESTRICT). Эта спецификация действия применяется по умолчанию.

• Изменения значений родительского ключа разрешаются, но при этом автоматически осуществляется коррекция всех значений внешних ключей, ссылающихся на модифицируемое значение родительского ключа. Это называется каскадным изменением (ограничение CASCADE).

• Изменения значений родительского ключа разрешаются, но при этом соответствующие значения внешнего ключа автоматически удаляются, то есть за- меняются значением NULL (ограничение SET NULL).

• Изменения значений родительского ключа разрешаются, но при этом соответствующие значения внешнего ключа автоматически заменяются значением по умолчанию (ограничение SET DEFAULT).

При описании внешнего ключа должно указываться, какой из приведенных вариантов действий следует применять, причем в общем случае это должно быть указано раздельно для каждой из команд UPDATE и DELETE. В качестве примера использования ограничений, накладываемых на операции модификации родительских ключей, можно привести следующий запрос:

CREATE TABLE NEW_EXAM_MARKS

(STUDENTID INTEGER SUB J_I D INTEGER NOT NULL,

MARK INTEGER,

DATA DATE,

CONSTRAINT EXAM_PR_KEY PRIMARY

REFERENCES SUBJECT,

CONSTRAINT STUDENT_ID_FOR_KEY FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT ON UPDATE CAS CADE ON DELETE NO ACTION);

В этом примере при попытке изменения значения поля STUDENT_ID таблицы STUDENT будет автоматически обеспечиваться каскадная корректировка этих значений в таблице EXAM_MARKS. При изменении идентификатора студента STUDENT_ID в таблице STUDENT сохранятся все ссылки на его оценки. Однако любая попытка удаления (DELETE) записи о студенте из таблицы STUDENT будет отвергаться, если в таблице EXAM_MARKS существуют записи об оценках данного студента.

Более подробная информация: https://www.postgresql.org/docs/9.5/tutorial-sql.html

 

Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

 

Задание на работу

1. Напишите три SQL запроса, создающие таблицы в соответствии с вашим вариантом. В работе вы должны продемонстрировать использование различных типов данных: целочисленные, числа с плавающей точкой, числа с фиксированной точкой, строковые типы (с фиксированной и с переменной длиной строки), дата. Чем больше разных типов данных вы используете, тем лучше. Приведите в отчете скриншоты терминала с выполненными командами и их результатом.

2. С помощью pgAdmin заполните таблицы данными: в каждую таблицу добавьте 10 строк.

 

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

1. Создать базу данных basa1, причем для данных на приросте 10%, для журнала транзакций – на диске E один файл с начальным размером 50 Мб, но не более 100 Мб, с величиной прироста 10 Мб.

2. Создать базу данных basa1, причем для данных на диске D определить два файла с начальным размером по 100 Мб, но не более 500 Мб, с величиной прироста 10%, для журнала транзакций - на диске E один файл с начальным размером 50 Мб, но не более 100 Мб, с величиной прироста 10 Мб.

3. Создать базу данных basa1, причем для данных на диске D определить два файла с начальным размером по 100 Мб, но не более 500 Мб, с величиной прироста 10%, для журнала транзакций - на диске E один файл с начальным размером 50 Мб, но не более 100 Мб, с величиной прироста 10 %.

4. Создать базу данных basa1, причем для данных на диске D определить два файла с начальным размером по 100 Мб, но не более 500 Мб, с величиной прироста 10%, для журнала транзакций - на диске E один файл с начальным размером 50 Мб, но не более 100 Мб, с величиной прироста 10 Мб.

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