3.2.4 Креативная часть лабораторной работы:

Для работы в заданной базе данных необходимо использовать команду:

 

USE Education;

Прежде чем создавать таблицу, рассмотрим спецификацию identity (1,1)

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

Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй - какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце Spec_ID значение 1, вторая - 2 и т.д.

Бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи "один-ко-многим" со стороны "один". Таким образом, мы не можем допустить тут произвола. С другой стороны, нам не хочется отказываться от автоинкрементируемого поля, т.к. оно упростит обработку данных при последующей эксплуатации базы данных.

Таблицы создаются в схемах StudySchema ( «Группы» и «Студенты», «Предметы», «Учебный план», «Успеваемость») и DekanatSchema(«Преподаватели», «Кафедры»), таблица «Специальности» создается без схемы.

Создание таблицы Специальности:

Spec_ID int IDENTITY (1,1) PRIMARY KEY,

Spec_NAME VARCHAR (15) NOT NULL

);

Создание таблицы Группы:

CREATE TABLE StudySchema.Grup (

Grup_ID int identity (1,1) PRIMARY KEY,

Grup_NAME varchar (9) NOT NULL,

Grup_COURSE int NOT NULL

);

Так как поле Grup_NAME, определяющее название группы не является первичным ключом, название группы может дублироваться, чтобы этого не происходило используется конструкция UNIQUE,которая используется для запрета совпадения значений в полеGrup_NAME,т.е значения каждогоэлемента столбца дожнобыть уникальным. Конструкция UNIQUE работает также как уникальный ключ

Так как слово GROUP является зарезервированным (часть конструкции GROUP BY), чтобы использовать его в качестве названия, необходимо всегда брать его в квадратные скобки [ ] либо не использовать это служебное слово в названиях элементов БД. Поэтому наша таблица называется GRUP.

 

Создание таблицы Студенты (здесь приведены варианты описания ключей):

CREATE TABLE StudySchema.Students (

Stud_ID bigint,

Stud_FAM char(20) NOT NULL,

Stud_IMYA char(10),

Stud_OTCH char(15),

Stud_DATE datetime,

Stud_ADDRESS char(25),

Spec_ID int NOT NULL REFERENCES Speciality(Spec_ID),

Grup_ID int NOT NULL REFERENCES StudySchema.Grup(Grup_ID ),

Stud_STAR bigint,

CONSTRAINT PK_Students PRIMARY KEY (Stud_ID),

CONSTRAINT FK_Students_Students FOREIGN KEY (Stud_STAR) REFERENCES StudySchema.Students (Stud_ID)

);

КонструкцияREFERENCES <имя_мастер_таблицы> [<имя_поля>] используется дляограничения ссылочной целостности и определяет, что данный столбец является внешним ключом и указывает на ключ какой мастер_таблицы (родительской таблицы) он будет ссылаться.

Ссылку намастер_таблицу можно делать в строке, описывающей поле, но можно использовать конструкцию CONSTRAINT, также используемую для работы с ограничениями ссылочной целостности, которые накладываются на таблицу или на отдельный столбец. КонструкциЯ CONSTRAINT используется только в операторахCREATE TABLE и ALTER TABLE.

Создание таблицы Кафедры:

CREATE TABLE DekanatSchema.Chair (

Chair_ID int PRIMARY KEY,

Chair_NAME varchar(20) NOT NULL,

Chair_PHONE varchar (10) ,

Chair_CHIEF varchar (15)

) ;

 

Создание таблицы Преподаватели:

CREATE TABLE DekanatSchema.Teacher (

Teach_ID bigint not null PRIMARY KEY ,

Teach_FAM varchar (20) NOT NULL,

Яндекс.Директ

Промышленный дизайнПроф. переподготовка дистанционно. Диплом. Все Регионы. Звоните!rostbk.com

Повышение квалификациипедагогов!Дистанционно. ФГОС. Скидки! Быстро! Лицензия. 2 документа: удостоверение и сертификат!pedcampus.ru

Teach_IMYA varchar (10),

Teach_OTCH varchar (15),

Teach_POSITION varchar (18),

Teach_STEPEN varchar (12),

Chair_ID int NOT NULL,

) ;

 

Создание таблицы Предметы:

CREATE TABLE StudySchema.Subjects (

Subj_ID int PRIMARY KEY ,

Subj_NAME varchar(20) NOT NULL,

Total_Hours int,

Lection_Hours int,

Practice_Hours int,

Labor_Hours int,

) ;

 

Создание таблицы Учебный план:

CREATE TABLE StudySchema.Study (

Grup_ID int NOT NULL REFERENCES

StudySchema.Grup(Grup_ID ) ON DELETE CASCADE,

Subj_ID int NOT NULL REFERENCES StudySchema.Subjects (Subj_ID),

Teach_ID bigint NOT NULL REFERENCES DekanatSchema.Teacher (Teach_ID),

Kredit_count int,

Lesson_Hours int not null,

CONSTRAINT PK_Study PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID )

) ;

Создание таблицы Успеваемость:

CREATE TABLE StudySchema.Progress (

Grup_ID int not null,

Subj_ID int not null,

Teach_ID bigint not null,

Kredit_count int FOREIGN KEY REFERENCES StudySchema.Study (Kredit_count)

Pr_DATE datetime null,

OCENKA integer CHECK ( OCENKA in (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0),

CONSTRAINT FK_Progress_Study FOREIGN KEY (Grup_ID, Subj_ID, Teach_ID ) REFERENCES StudySchema.Study (Grup_ID, Subj_ID, Teach_ID ),

CONSTRAINT PK_Progress PRIMARY KEY (Stud_ID,Grup_ID, Subj_ID, Teach_ID )

);

 

Сжатие данных

В SQL Server 2008 появилась новая возможность сжатия данных для уменьшения размеров таблиц, индексов или их секций. Для этого данные типов с фиксированной длиной хранятся в формате переменной длины, и уменьшается избыточность данных.

 

Рассмотрим примеры создания полей таблиц для хранения подобных данных:

CREATE TABLE Tab1

(col1 int, col2 int, sum_col AS col1+col2 );

CREATE TABLE Tab2

(col1 int PRIMARY KEY,

Col2 nvarchar(200) SPARSE NULL ) ;

CREATE TABLE Tab3

(col1 int, col2 nvarchar(150) )

WITH (DATA_COMPRESSION = ROW);

 

Изменение структуры таблиц

 

Структуру таблиц можно изменять командой ALTER TABLE.

Добавление полей. Добавление в таблицу DekanatSchema.Chair поле DekanatSchema.Chair_Cab:

 

ALTER TABLE DekanatSchema.Chair

ADD Chair_Cab char(10) not null;

Удаление полей из таблицы. Удалите введенное поле из таблицы:

 

ALTER TABLE DekanatSchema.Chair

DROP COLUMN Chair_Cab ;

Добавьте в таблицу StudySchema.StudySchema.Students поле, где будет храниться информация о стипендии студентов:

 

ALTER TABLE StudySchema.Students ADD stud_stip int ;

Добавление ограничений. Если в таблице не были определенны первичные или внешние ключи, это также можно исправить с помощью ALTER TABLE.

Предположим, что в таблице DekanatSchema.Chair не был определен первичный ключ:

ALTER TABLE DekanatSchema.Chair

ADD CONSTRAINT PK_Chair PRIMARY KEY (Chair_ID);

 

В таблице DekanatSchema.Teacher (Преподаватели) не был описан один из внешних ключей, добавим его:

 

ALTER TABLE DekanatSchema.Teacher ADD CONSTRAINT fK_Chair_Teacher FOREIGN KEY (Chair_ID)REFERENCES DekanatSchema.Chair (Chair_ID) ;

Удаление таблиц

 

Для удаления таблиц (или объектов) предназначена команда

DROP <object type>.

 

Например

Добавление новых строк в таблицу.

Синтаксис

 

INSERT [INTO] table_name [ ( column_list ) ]

{ VALUES ( { DEFAULT | NULL | expression } [ ,...n] )

| derived_table

}

Примечания:

· чтобы заменить данные в таблице, необходимо использовать инструкцию DELETE для очистки существующих данных перед загрузкой новых данных с помощью INSERT. Чтобы изменить значения столбца в существующей строке воспользуйтесь инструкцией UPDATE;

· если вставка column_list пропущена, список вставки столбцов, в котором указываются все столбцы таблицы в возрастающем порядке в соответствии с их порядковыми номе-рами, становится неявным;

· в column_list столбец таблицы можно определить только один раз. Если в column_list столбец отсутствует, SQL Server должен предоставить значение на основании определения столбца; в противном случае строку загрузить не удастся. SQL Server автоматически предоставляет значение для столбца в столбце:

• имеется свойство IDENTITY. Используется следующее значение приращения для идентификатора;

• имеется стандартное значение. Используется стандартное значение для столбца;

• неопределенное значение. Используется значение Null.

 

При вставке явных значений в столбец идентификаторов необходимо использовать список столбцов и список VALUES. Если в списке VALUES значения не расположены аналогично столбцам таблицы или отсутствуют значения для каждого столбца таблицы, необходимо использовать column_list для явного указания столбца, в котором хранится каждое входящее значение.

Если для указания значения столбца используется значение DEFAULT, то для этого столбца вставляется стандартное значение. Если стандартного значения для столбца не существует и в столбце могут быть значения Null, то вставляется значение NULL. Значение DEFAULT недопустимо для столбца идентификаторов.

 

При вставке строк применяются следующие правила:

• если значение загружается в столбец с типом данных char, varchar или varbinary, добавление или усечение замыкающих пробелов (пробелов для char и varchar, нулей для varbinary) определяется в соответствии с приведенной ниже таблицей;

 

Тип данных	Стандартная операция
char/binary	Добавление исходного значения с замыкающими пробелами для столбцов char или с замыкающими нулями для столбцов binary к длине столбца.
varchar	Замыкающие пробелы в значениях символов, которые вставлены в столбцы varchar, не усекаются. Значения не добавляются к длине столбца.
varbinary	Замыкающие нули в двоичных значениях, которые вставлены в столбцы varbinary, не усекаются. Значения не добавляются к длине столбца.

 

• если инструкция INSERT нарушает константу или правило, либо в ней присутствует значение, несовместимое с типом данных столбца, при выполнении инструкции происходит сбой и отображается сообщение об ошибке;

• если INSERT загружает несколько строк с помощью SELECT, любое нарушение правила или ограничения, возникающее в результате загрузки значений, приводит к остановке полной инструкции и строки не загружаются.

Примеры кода:

1. Использование простой инструкции INSERT