кпарвп. Лабораторная работа 1. Создание базы данных в sql server 2022 Основные объекты структуры базы данных sql сервера
 Скачать 0.97 Mb.
|
|
РАЗДЕЛ 1. СОЗДАНИЕ БАЗЫ ДАННЫХ В SQL SERVER 2022 1.1. Основные объекты структуры базы данных SQL - сервера Основные объекты базы данных SQL Server представлены в таблице 1: Таблица 1. Объекты базы данных SQL Server Tables Таблицы базы данных, в которых хранятся собственно данные Views Просмотры (виртуальные таблицы) для отображения данных из таблиц Stored Procedures Хранимые процедуры Triggers Триггеры – специальные хранимые процедуры, вызываемые при изменении данных в таблице User Defined function Создаваемые пользователем функции Indexes Индексы – дополнительные структуры, призванные повысить производительность работы с данными User Defined Data Types Определяемые пользователем типы данных Keys Ключи – один из видов ограничений целостности данных Constraints Ограничение целостности – объекты для обеспечения логической целостности данных Users Пользователи, обладающие доступом к базе данных Roles Роли, позволяющие объединять пользователей в группы Rules Правила базы данных, позволяющие контролировать логическую целостность данных Defaults Умолчания или стандартные установки базы данных Таблицы Все данные содержатся в объектах, называемых таблицами. Таблицы представляют собой совокупность каких-либо сведений об объектах, явлениях, процессах реального мира. Никакие другие объекты не хранят данные, но они могут обращаться к данным в таблице. Таблицы в SQL имеют такую же структуру, что и таблицы всех других реляционных СУБД и содержат: − cтроки; каждая строка (или запись) представляет собой совокупность атрибутов (свойств) конкретного экземпляра объекта; − cтолбцы; каждый столбец (поле) представляет собой атрибут или совокупность атрибутов. Поле строки является минимальным элементом таблицы. Каждый столбец в таблице имеет определенное имя, тип данных и размер. Представлениями (просмотрами) называют виртуальные таблицы, содержимое которых определяется запросом. Подобно реальным таблицам, представления содержат именованные столбцы и строки с данными. Для конечных пользователей представление выглядит как таблица, но в действительности оно не содержит данных, а лишь представляет данные, расположенные в одной или нескольких таблицах. Информация, которую видит пользователь через представление, не сохраняется в базе данных как самостоятельный объект. Хранимые процедуры представляют собой группу команд SQL, объединенных в один модуль. Такая группа команд компилируется и выполняется как единое целое. Триггерами называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении или удалении данных из таблицы. Функции в языках программирования – это конструкции, содержащие часто исполняемый код. Функция выполняет какие-либо действия над данными и возвращает некоторое значение. Индекс – структура, связанная с таблицей или представлением и предназначенная для ускорения поиска информации в них. Индекс определяется для одного или нескольких столбцов, называемых индексированными столбцами. Он содержит отсортированные значения индексированного столбца или столбцов со ссылками на соответствующую строку исходной таблицы или представления. Повышение производительности достигается за счет сортировки данных. Использование индексов может существенно повысить производительность поиска, однако для хранения индексов необходимо дополнительное пространство в базе данных. Пользовательские типы данных – это типы данных, которые создает пользователь на основе системных типов данных, когда в нескольких таблицах необходимо хранить однотипные значения; причем нужно гарантировать, что столбцы в таблице будут иметь одинаковый размер, тип данных и чувствительность к значениям NULL. Ограничения целостности – механизм, обеспечивающий автоматический контроль соответствия данных установленным условиям (или ограничениям). Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся: ограничение на значение NULL, проверочные ограничения, ограничение уникальности (уникальный ключ), ограничение первичного ключа и ограничение внешнего ключа. Последние три ограничения тесно связаны с понятием ключей. Правила используются для ограничения значений, хранимых в столбце таблицы или в пользовательском типе данных. Они существуют как самостоятельные объекты базы данных, которые связываются со столбцами таблиц и пользовательскими типами данных. Контроль значений данных может быть реализован и с помощью ограничений целостности. Умолчания – самостоятельный объект базы данных, представляющий значение, которое будет присвоено элементу таблицы при вставке строки, если в команде вставки явно не указано значение для этого столбца. 1.2. Создание базы данных. Создание и заполнение таблиц Создание БД Успеваемость Создание самой базы данных и таблиц осуществляется с помощью программы SQL Server 2022, которая запускается командой: Пуск-Microsoft SQL Server Management Studio . После установки соединения (рис 1) с текущим сервером на экране появится окно программы, показанное на рис. 2. Рис.1 Подключение к серверу Рис.2. Окно Обозревателя объектов программы Microsoft SQL Server Management Studio В левой части в окне Обозревателя объектов можно увидеть содержимое текущего сервера, а именно базы данных и другие объекты. На одном компьютере-сервере можно расположить несколько баз данных. Весь список БД доступных на сервере можно просмотреть, открыв папку Базы данных (см. рис.3). Рис.3. Содержимое папки Базы данных Процесс создания базы данных в системе SQL-сервера состоит из двух этапов: сначала организуется сама база данных, а затем принадлежащий ей журнал транзакций. Информация размещается в соответствующих файлах, имеющих расширения *.mdf (для базы данных) и *.ldf. (для журнала транзакций). В файле базы данных записываются сведения об основных объектах (таблицах, индексах, просмотрах и т.д.), а в файле журнала транзакций – о процессе работы с транзакциями (контроль целостности данных, состояния базы данных до и после выполнения транзакций). Итак, для создания новой базы данных необходимо щелкнуть правой кнопкой мыши по значку папки Базы данных и выбрать команду контекстного меню Создать базу данных (см. рис.4). После чего на экране появится диалоговое окно Создание базы данных, в котором необходимо указать в поле Имя базы данных имя создаваемой БД. Имя базы данных должно быть уникальным в пределах сервера и не может превышать 128 символов. Например, укажем название - Успеваемость (см. рис.5). В разделе Файлы базы данных можно указать дополнительную информацию о файлах базы данных. Здесь можно изменить Путь, Начальный размер файла в мегабайтах, Авторасширение (см. рис.6). Рис.4. Команда создания новой БД. Рис.5. Создание БД Успеваемость. Имя файла для данных генерируется на основе имени базы данных, в нашем случае Успеваемость, тип файла Rows data (Данные). В поле Путь можно поменять расположение файла данных. По умолчанию указывается C :\ Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\. В поле Начальный размер можно определить первоначальный размер файла, по умолчанию принимается 8 Мб. В поле Авторасширение нажав соответствующую кнопку можно в дополнительном окне изменить параметры авторасширения для создаваемой базы данных (см. рис.6), а именно, разрешить или запретить автоматический рост его размера Разрешить авторасширение и указать следующие параметры: 1) Увеличение размера файлов - приращение с помощью абсолютной величины в мегабайтах или в процентном соотношении В процентах; 2) Максимальный размер файла. При выборе параметра Неограниченный рост размера файлов - максимальный размер базы данных ограничивается только свободным местом на диске, при выборе Ограничение размера файла нужно указать максимальный размер файла в Мб. Рис.6. Создание БД Успеваемость. Изменение параметра Авторасширение Такие же параметры можно настроить у файла журнала транзакций. Имя файла для журнала транзакций генерируется на основе имени базы данных, и в конце к нему добавляются символы _log. Файл журнала транзакций должен находиться в одной папке с файлом базы данных. После настройки всех параметров можно нажать кнопку ОК, в результате чего будет создан файл базы данных с выбранными параметрами (рис.7) Рис.7. База данных Успеваемость Создание таблиц. Типы данных. Нулевой статус. После того как создана база данных можно приступить к созданию таблиц, необходимых для учета успеваемости студентов. Для создания таблицы необходимо открыть БД Успеваемость и перейти к объекту Tables. Здесьможно увидеть стандартный набор системных таблиц. Таблицы, создаваемые пользователем для хранения информации, являются пользовательскими. Таблица создается с помощью команды контекстного меню Создать таблицу (рис.8). Сначала нужно описать структуру таблицы, т.е. указать имена полей, типы данных, нулевой статус, ключевые поля и другие свойства (рис.9). Рис.8. Создание новой таблицы Рис.9. Окно создания новой таблицы Названия полей должны быть четко продуманными и должны соответствовать данным, которые будут вводиться в это поле. Имена полей не должны содержать пробелов и буквы русского алфавита. Один из основных моментов процесса создания таблицы – определение типов данных для ее полей. Тип данных поля таблицы определяет тип информации, которая будет размещаться в этом поле. SQL-сервер поддерживает большое число различных типов данных. Полный перечень представлен в таблице 2. Таблица 2. Список типов данных Для хранения символьной информации: char (длина) текстовые значения имеют фиксированную длину от 1 до 8000 символов. Если во вводимом в поле текстовом значении фактическое число символов меньше числа, определенного параметром длина, то значение поля автоматически дополняется справа пробелами до заданного числа символов. varchar (длина) Текстовые значения имеют переменную длину от 1 до 8000 символов. В отличие от char при вводе строки, фактическая длина которой меньше заданной, не производится ее дополнение пробелами до заданного максимального значения. По сравнению с char тип данных varchar позволяет более экономно использовать память, выделяемую для хранения текстовых значений, и оказывается более удобным при сравнении текстовых констант. nchar (длина) для хранения до 4000 символов Unicode. nvarchar (длина) text для хранения символьных данных большого объема (до 2 Гб) ntext К целочисленным типам данных относятся: int 4 байта (диапазон хранимых данных от -2 31 (-2 147 483 648) до 2 31 -1 (2 147 483 647)) smallint 2 байта (диапазон от -2 15 (-32 768)до 2 15 -1 (32767)) tinyint 1 байт (диапазон от 0 до 255) bigint 8 байт (диапазон от -2 63 (-9223372036854775808) до 2 63 -1 (9223372036854775807)) Числа, в составе которых есть десятичная точка, называются нецелочисленными. Нецелочисленные данные разделяются на два типа – десятичные и приблизительные. Десятичные: decimal [(точность[,масштаб])] Позволяют самостоятельно определить формат точности числа с плавающей запятой. Параметр точность указывает максимальное количество цифр вводимых данных этого типа (до и после десятичной точки в сумме), а параметр масштаб – максимальное количество цифр, расположенных после десятичной точки. В обычном режиме сервер позволяет вводить не более 28 цифр, используемых в типах DECIMAL и NUMERIC (от 2 до 17 байт). numeric [(точность[,масштаб])] Приблизительные: float FLOAT (точность до 15 цифр, 8 байт) и REAL (точность до 7 цифр, 4 байта). Эти типы представляют данные в формате с плавающей запятой, т.е. для представления чисел используется мантисса и порядок, что обеспечивает одинаковую точность вычислений независимо от того, насколько мало или велико значение. real Для хранения информации о дате и времени предназначены такие типы данных, как: smalldatetime 4 байт для хранения дат в пределах от 1 января 1900 до 6 июня 2079 datetime 8 байт с 1 января 1753 до 31 December 9999 Для хранения информации денежного типа money обеспечивают точность значений до 4 знаков после запятой и используют 8 и 4 байта соответственно. smallmoney Логический тип данных bit Для хранения значений 0 или 1 Другие типы данных timestamp применяется в качестве индикатора изменения версии строки в пределах базы данных uniqueidentifier используется для хранения глобальных уникальных идентификационных номеров sysname предназначен для идентификаторов объектов sql_variant позволяет хранить значения любого из поддерживаемых SQL Server типов данных за исключением TEXT, NTEXT, IMAGE и TIMESTAMP image Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes. Дополнительные свойства полей: − Размер поля (Длина). После выбора типа данных можно указать дополнительные свойства полей в нижней части окна Свойства столбцов. Значение длина – размер поля можно редактировать только у полей имеющих символьный тип данных: char (длина), varchar (длина), nchar (длина), nvarchar (длина). − Нулевой статус (Разрешить значения Null). Для обозначения отсутствующих, пропущенных или неизвестных значений поля в SQL используется ключевое слово NULL. NULL не является значением 0 в обычном понимании, а используется для обозначения того факта, что действительное значение поля на самом деле пропущено или неизвестно. При описании полей таблицы проектировщик в столбце Разрешить значения Null разрешает ( ) или запрещает ввод значений NULL в то или иное поле. В ключевые поля автоматически запрещается ввод неизвестных значений. Например, при описании поля Фамилия_студента проектировщик запретил ввод значений NULL в это поле. Следовательно, при заполнении таблицы реальными данными нельзя будет оставить поле Фамилия_студента незаполненным, т.е. если пользователь не будет знать фамилию или ошибочно пропустит это поле, то при переходе к следующей строке SQL Server выдаст предупреждение о том, что поле Фамилия_студента является обязательным для заполнения. Задание 1. Рассмотрим процесс создания таблицы на примере таблицы STUDENT. Изучите и выполните все действия, описанные ниже. Таблица Студент предназначена для хранения сведений о студентах личного характера, т.е. персональный код, имя и фамилию, дату рождения, город проживания, курс, стипендию и код университета в котором учится студент. Т.е. структура полей таблицы будет иметь следующий вид: Свойство Название поля Назначение PK (Primary key, ключевое поле) Student_id Числовой код, однозначно идентифицирующий студента см. описание типов полей ниже Surname Фамилия студента Name Имя студента Stipend Стипендия, которую получает студент Kurs Курс, на котором учится студент City Город, в котором живет студент Birthday Дата рождения студента Univ_id Числовой код, идентифицирующий университет, в котором учится студент Создадим таблицу Student, определив тип данных и нулевой статус столбцов. − Поле Student_id предназначено для хранения идентификационных номеров студентов. Каждый студент будет иметь свой персональный неповторяющийся числовой код. Предполагаем, что количество студентов, а, следовательно, количество разных идентификационных номеров, будет больше 255, но не больше 32767, в связи с этим целесообразнее выбрать тип данных – smallint,чем tinyint (значения см. в таб.2). Если число студентов будет больше чем 32767, то нужно выбрать тип данных – int. − Из таких же рассуждений дляполя Univ_id выбран тип данных smallint. − Поля Surname, Name, City с целью экономии памяти определяем как текстовые поля переменной длины – varchar. Предполагаем, что фамилия и город будет не больше 20 символов, поэтому длину полей Surname и City установим равными 20 символам, а Name - 15 символов (см. рис.11). − Поле Stipend будет содержать нецелочисленные значения стипендий, поэтому выбираем тип данных – real. − Поле Kurs будет содержать значения от 1 до 5, поэтому целесообразнее выбрать самый малый целочисленный тип данных – tinyint. − Поле Birthday предназначено для хранения дат рождения, поэтому выбираем тип данных smalldatetime (описание типа данных см. в таб.2) − Нулевой статус полей. Прежде чем определять нулевой статус полей, проектировщик должен разделить все поля таблицы на 2 категории: главные и второстепенные. Главные (или основные) поля таблицы содержат основную информацию, они зачастую предназначены для персонификации или идентификации субъекта/объекта, поэтому не должны содержать пустые значения - Null значения. Второстепенные поля содержат дополнительные характеристики субъекта/объекта. Право отнесения поля к той или иной категории принадлежит проектировщику исходя из постановки задачи. − Итак, ввод значений Null запретим в поля Student_id, Surname и Name. Почему именно эти поля не могут быть пропущены при заполнении строки? Это объясняется тем, что код студента является главным полем данной таблицы, кроме того, оно является ключевым полем, поэтому в него автоматически запрещается вводить значения Null. А поскольку код обозначает определенного студента, то мы должны ввести его фамилию и имя, чтобы точно знать, кому принадлежит введенный код. Иначе, если мы сделаем эти 2 поля необязательными для заполнения, то возникает вопрос: «Кого (что) мы идентифицируем (однозначно обозначаем) с помощью поля КодСтудента»? В остальные поля (стипендия, город проживания и.т.д.) можно разрешить ввод значений Null. Т.е. в момент заполнения сведений о текущем студенте, если не будут известны его дата рождения или город проживания, соответствующее поле можно будет пропустить и вернуться к его заполнению позже, когда информация станет известной. В нашем примере в поля Stipend, Kurs, City, Birthday, Univ_id разрешается ввод неизвестных значений. − Главным полем таблицы, первичным ключом (Primary Key) является Student_id. Значения этого поля будут однозначно обозначать каждую строку таблицы, а именно студента. Если даже в таблицу нужно будет ввести сведения о двух студентах, имеющих совпадающие фамилии и имена, то это можно будет сделать без особой путаницы, поскольку номера этим студентам будут присвоены разные, что позволит распознавать их как двух разных людей. Чтобы сделать поле ключевым необходимо перейти к нему и выбрать в контекстном меню пункт Set primary key (Рис. 10) Рис.10 Назначение поля ключевым Рис. 11. Структура таблицы Student После описания структуры полей создаваемой таблицы нужно закрыть окно новой таблицы, сохранить, указав название таблицы Student. Далее для заполнения таблицы реальными данными нужно в контекстном меню таблицы выбрать команду Edit Top 200 Rows (рис.12). Чтобы вернуться к макету таблицы с целью изменения описания полей, нужно в контекстном меню таблицы выбрать команду Проект/Design (рис.13). Рис.12. Открытие таблицы в режиме таблицы для заполнения данными Рис.13. Открытие таблицы в режиме конструктора. Откройте созданную таблицу Student в режиме таблицы и заполните ее следующими данными: Student (Студент) Student_id Surname Name Stipend Kurs City Birthday Univ_id 1 Иванов Иван 150 1 Орел 03.12.1982 11 3 Петров Петр 200 1 Курск 01.12.1980 11 6 Сидоров Вадим 150 4 Москва 07.06.1979 22 10 Кузнецов Борис null 2 Брянск 08.12.1981 10 12 Зайцева Ольга 250 2 Липецк 01.05.1981 10 32 Котов Павел 150 5 Белгород null 14 55 Белкин Вадим 250 5 Воронеж 07.01.1980 10 265 Павлов Андрей null 3 Самара 05.11.1979 10 276 Петров Антон 200 4 null 05.08.1981 22 654 Лукин Артем 200 3 Воронеж 01.12.1981 37 Практическая работа №1. Создание БД Успеваемость Аналогичным образом создайте следующие таблицы БД Успеваемость: Lecturer (Преподаватель) Lecturer_id Surname Name City Univ_id 24 Колесников Борис Воронеж 11 46 Никонов Иван Воронеж 10 74 Лагутин Павел Москва 22 108 Строков Николай Воронеж 22 276 Николаев Виктор Орел 14 328 Сорокин Андрей Москва 10 455 Леонтьев Мирослав Томск 15 Lecturer_id-числовой код, идентифицирующий преподавателя; Surname-фамилия преподавателя; Name-имя преподавателя City-город, в котором живет преподаватель; Univ_id-идентификатор университета, в котором работает преподаватель; Subject (Предмет) Subj_id Subj_name Hour Semester 10 Информатика 56 1 11 Информатика 72 2 22 Физика 34 1 43 Математика 56 2 56 История 34 4 73 Физкультура 34 5 94 Английский 56 3 Subj_id- идентификатор предмета обучения Subj_name-наименование предмета обучения Hour-количество часов, отводимых на изучение предмета Semester-семестр, в котором изучается данный предмет University (Университеты) Univ_id Univ_name Rating City 10 НГУ 303 Новосибирск 11 ВГУ 296 Воронеж 14 БГУ 326 Белгород 15 ТГУ 368 Томск 18 ВГМА 327 Воронеж 22 МГУ 606 Москва 32 РГУ 416 Ростов 37 СГТУ null Белгород Univ_id- идентификатор университета; Univ_name-название университета Rating-рейтинг университета; City-город, в котором расположен университет Exam_marks (Экзаменационные оценки) Exam_id Student_id Subj_id Mark Exam_date 34 6 10 3 23.01.2000 34 32 10 4 23.01.2000 145 12 10 3 12.01.2000 238 41 10 5 22.01.1999 238 12 10 5 22.01.1999 43 6 22 4 18.01.2000 639 12 22 4 22.06.1999 639 32 22 2 22.06.1999 639 55 22 null 22.06.1999 75 6 43 5 05.01.2000 75 55 43 5 05.01.2000 101 265 11 3 12.06.2001 101 276 11 4 12.06.2001 101 654 11 3 12.06.2001 122 265 94 5 15.01.2002 122 654 94 4 15.01.2002 Exam_id – идентификатор экзамена Student_id-идентификатор студента Subj_id- идентификатор предмета обучения Mark-экзаменационная оценка Exam_date-дата экзамена Subj_lect (Учебные дисциплины преподавателей) Lecturer_id Subj_id 24 10 24 11 46 11 46 22 46 43 74 43 108 22 108 56 276 94 328 10 328 22 455 73 Lecturer_id- идентификатор преподавателя Subj_id- идентификатор предмета обучения Контрольные вопросы: 1. Перечислите основные объекты SQL Server? 2. Из каких двух файлов состоит база данных SQL Server и какое расширение они имеют? 3. Что необходимо указать при создании базы данных SQL Server? 4. Что означает атрибут Авторасширение? 5. Как изменить путь к базе данных? 6. Для чего нужен файл журнала транзакций и какое у него расширение? 7. Перечислите типы таблиц SQL Server? 8. Как создать новую таблицу? 9. Что необходимо указать при создании таблицы? 10. Что такое Null значения? 11. Что такое ключевое поле и как сделать поле ключевым? 12. Какие типы данных Вы знаете и как определить какой тип нужно выбрать для создаваемого поля? 13. Как открыть созданную таблицу в режиме таблицы и посмотреть ее содержимое? 14. Как открыть созданную таблицу в режиме конструктора чтобы изменить ее макет? |