Файл: SQL 3.docx

Скачать файл
Заказать решение

ВУЗ: Не указан

Категория: Задание

Дисциплина: Базы данных

Добавлен: 30.10.2018

Просмотров: 430

Скачиваний: 13

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

Задания для работы по теме:

Язык SQL: определение объектов баз данных

  1. Cоздать в БД необходимые таблицы (добавить один символ «1» к ж таблиц, чтобы отличать их от таблиц, использующихся в предыдущем задании) согласно варианту с помощью команд CREATE TABLE, определить типы таблиц (родительская или подчиненная), типы полей и их размеры, поля типа Primary key и Foreign key, а также создать следующие ограничения:


  • создать с использованием свойства IDENTITY для двух таблиц столбцы типа «счетчик». В SQL Server Management Studio проверить результаты добавления и удаления записей:


CREATE TABLE Tarif (

IDTarif int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Tarif PRIMARY KEY, --автоматический счетчик

NameTarif nvarchar(30) NOT NULL,

DescrTarif nvarchar(30) NOT NULL,

CostTarif money NOT NULL,

)

CREATE TABLE AddServices (

IDService int IDENTITY(0,1) NOT NULL, --автоматический счетчик

NameService nvarchar(30) NOT NULL,

DescrService nvarchar(30) NOT NULL,

CostService money NOT NULL,

CONSTRAINT PK_AddServices PRIMARY KEY (IDService,NameService),

)

  • создать один вычисляемый столбец;

CREATE TABLE Cars (

IDCar int NOT NULL CONSTRAINT PK_Cars PRIMARY KEY,

CarNum nvarchar(9) NOT NULL,

BrandID int NOT NULL,

BodyNum int NOT NULL CHECK(BodyNum BETWEEN 100000 AND 199999),

EngineNum int NOT NULL,

ReleaseYear date NOT NULL,

MileAge int NOT NULL,

AVGMileAge AS MileAge/DATEDIFF(YEAR,ReleaseYear,GETDATE()), --вычисляемый столбец

DateTO date NOT NULL,

Mechanic int NOT NULL,

CONSTRAINT FK_Cars_Mechanic FOREIGN KEY(Mechanic) REFERENCES Employees(IDEmployee),

CONSTRAINT FK_Cars_BrandID FOREIGN KEY(BrandID) REFERENCES CarBrand(IDCarBrand) ON DELETE NO ACTION,

)

  • для одной из таблиц создать первичный ключ (PRIMARY KEY), включающий два поля;

CREATE TABLE AddServices (

IDService int IDENTITY(0,1) NOT NULL,

NameService nvarchar(30) NOT NULL,

DescrService nvarchar(30) NOT NULL,

CostService money NOT NULL,

CONSTRAINT PK_AddServices PRIMARY KEY (IDService,NameService), --PK, включающий 2 поля

)

  • ДЛЯ числового поля одной из таблиц задать заполнение значением по умолчанию:


CREATE TABLE Employees (

IDEmployee int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY,

FullName nvarchar(255) NOT NULL,

Birthday date default GETDATE() check (Birthday<GETDATE()),

Gender nvarchar(1) NOT NULL,

Address nvarchar(30) NOT NULL,

Telephone nvarchar(30) NOT NULL,

Passport nvarchar(30) NOT NULL,

PositionID int NOT NULL,

YearsOFWork int NOT NULL DEFAULT 5, --значение по умолчанию

ManagerID int,

CONSTRAINT FK_Employees1_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(IDPositions),

)

  • для одного из числовых полей одной из таблиц задания 2 задать принадлежность значений от ... до …


CREATE TABLE Cars (

IDCar int NOT NULL CONSTRAINT PK_Cars PRIMARY KEY,

CarNum nvarchar(9) NOT NULL,

BrandID int NOT NULL,

BodyNum int NOT NULL CHECK(BodyNum BETWEEN 100000 AND 199999), -- проверка значения в диапазоне

EngineNum int NOT NULL,

ReleaseYear date NOT NULL,

MileAge int NOT NULL,

AVGMileAge AS MileAge/DATEDIFF(YEAR,ReleaseYear,GETDATE()),

DateTO date NOT NULL,

Mechanic int NOT NULL,

CONSTRAINT FK_Cars_Mechanic FOREIGN KEY(Mechanic) REFERENCES Employees(IDEmployee),

CONSTRAINT FK_Cars_BrandID FOREIGN KEY(BrandID) REFERENCES CarBrand(IDCarBrand) ON DELETE NO ACTION,

)

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


CREATE TABLE Cars (

IDCar int NOT NULL CONSTRAINT PK_Cars PRIMARY KEY,

CarNum nvarchar(9) NOT NULL,

BrandID int NOT NULL,

BodyNum int NOT NULL CHECK(BodyNum BETWEEN 100000 AND 199999),

EngineNum int NOT NULL,

ReleaseYear date NOT NULL,

MileAge int NOT NULL,

AVGMileAge AS MileAge/DATEDIFF(YEAR,ReleaseYear,GETDATE()),

DateTO date NOT NULL,

Mechanic int NOT NULL,

CONSTRAINT FK_Cars_Mechanic FOREIGN KEY(Mechanic) REFERENCES Employees(IDEmployee),

CONSTRAINT FK_Cars_BrandID FOREIGN KEY(BrandID) REFERENCES CarBrand(IDCarBrand) ON DELETE NO ACTION, --невозможность удаления

)

  • для одной из подчиненных таблиц задания 2 задать удаление строки, если из родительской таблицы удаляется связанная строка.


CREATE TABLE Calls (

IDCall int NOT NULL CONSTRAINT PK_Calls PRIMARY KEY,

WorkID int NOT NULL,

TimeCall datetime,

Phone nvarchar(30),

StartPlace nvarchar(30),

EndPlace nvarchar(30),

TafifID int NOT NULL,

ServiceID int NOT NULL,

NameService nvarchar(30) NOT NULL,

Operator int NOT NULL,

CONSTRAINT FK_Calls_TafifID FOREIGN KEY(TafifID) REFERENCES Tarif(IDTarif),

CONSTRAINT FK_Calls_ServiceID FOREIGN KEY(ServiceID,NameService) REFERENCES AddServices(IDService,NameService) ON DELETE CASCADE, --автоматическое удаление из потомка при удалении из предка

CONSTRAINT FK_Calls_WorkID FOREIGN KEY(WorkID) REFERENCES Work(IDWork),

CONSTRAINT FK_Calls_Operator FOREIGN KEY(Operator) REFERENCES Employees(IDEmployee),

)

  1. В разделе диаграмм создать новую диаграмму, в которую добавить созданные таблицы, проверить связи между таблицами.



  1. Создать таблицу (Люди) с рекурсивными связями. Таблица содержит информацию о людях, между которыми имеются родственные связи (КодЧеловека, Фамилия, КодСупруга). КодСупруга– внешний ключ. Заполнить таблицу 4-6 записями.


--Таблица люди с супружескими связями

CREATE TABLE People (

IDPeople int NOT NULL CONSTRAINT PK_People PRIMARY KEY,

FirstName nvarchar(30) NOT NULL,

IDFamily int FOREIGN KEY REFERENCES People(IDPeople),

)

INSERT People (IDPeople, FirstName, IDFamily)VALUES

(1,N'Иванов',2),

(2,N'Иванова',1),

(3, N'Петров',NUll),

(4,N'Соколова',6),

(5,N'Николаева',Null),

(6, N'Соколов',4)

SELECT * FROM People

  1. Создать таблицу Сотрудники, в которой задана древовидная иерархия подчиненности сотрудников (руководит/подчиняется).


--Таблица Сотрудники с руководитель/подчиненный

CREATE TABLE Empl (

ID int NOT NULL CONSTRAINT PK_Empl PRIMARY KEY,

FirstName nvarchar(30) NOT NULL,

IDBoss int FOREIGN KEY REFERENCES Empl(ID),

)

INSERT Empl(ID, FirstName, IDBoss)VALUES

(1,N'Иванова',2),

(2,N'Васильев',5),

(3, N'Морозов',2),

(4,N'Соколов',6),

(5,N'Нильсен',4),

(6, N'Смирнов',NULL)

SELECT * FROM Empl

  1. Используя оператор INSERT заполнить таблицы записями (создать 3-4 варианта использования оператора INSERT (использовать или не использовать список столбцов, признаки NULL и NOT NULL для столбцов, заполнение по умолчанию, столбцы типа «счетчик», вычисляемые столбцы).


--Заполнение таблиц

INSERT Tarif (NameTarif, DescrTarif, CostTarif)VALUES

(N'Бизнес',N'Дорогой',10),

(N'Комфорт',N'Средний',7),

(N'Эконом',N'Дешевый',5)

GO


INSERT CarBrand(IDCarBrand, Brand, TechParameters, Specific, Cost)VALUES

(1,N'Toyota Camry','2.0 150hp',N'Бизнес',1620000),

(2,N'Volkswagen Jetta','1.6 110hp',N'Комфорт',750000),

(3,N'KIA RIO','1.4 84hp',N'Эконом',670000)

GO


INSERT AddServices(NameService, DescrService, CostService)VALUES

(N'Нет, N'Обычная перевозка',0),

(N'Детское кресло, N'Предоставление кресла',300),

(N'Перевозка животного', N'Место для клетки',250),

(N'Дополнительный багаж', N'Использование багажника',350)

GO


INSERT Positions(IDPositions, NamePositions, Salary, JobResp, Requirements) VALUES

(1, N'Водитель',50000, N'Перевозить пассажиров', N'Быстро'),

(2, N'Механик',40000, N'Ремонт автомобилей', N'Качественно'),

(3, N'Оператор',30000, N'Прием заявок', N'Вежливо')

GO


INSERT Employees(IDEmployee, FullName, Birthday, Gender, Address, Telephone, Passport, PositionID,ManagerID)VALUES

(1, N'Иванов В.Б.','19941011',N'М',N'Щербаковская 52',N'89132344556', N'4566567765',1,3),

(2, N'Петров О.А.','19860415',N'М',N'Ленина 3', N'89455677678',N'6457789984',1,3),

(3, N'Сидоров А.С.','19840912',N'М',N'Артамонова 86', N'89161232425',N'5656678876',1,3),

(4, N'Куликов А.Ш.','19640107',N'М',N'Московская 12', N'89131232323',N'4545362718',2,NULL),

(5, N'Цветочек М.А.','19910619',N'Ж',N'Боженко 16',N'89121232678', N'4536789098',3,NULL),

(6, N'Андреев О.С.','19840819',N'М',N'Арбат 42', N'89044353456',N'4556678876',1,3),

(7, N'Соколов А.М.','19720604',N'М',N'Мосфильмовская 65', N'89077899009',N'4321456789',1,3)

GO


INSERT Cars(IDCar, BrandID,CarNum, BodyNum, EngineNum, ReleaseYear, MileAge, DateTO, Mechanic)VALUES

(1,2,N'K543MO750',138598, N'532343434', '2015',165087, '20180622',4),

(2,1,N'A666KK177',145599, N'542344324', '2016',103864, '20180315',4),

(3,2,N'P763OO777',133458, N'556564234', '2015',196453, '20180110',4),

(4,3,N'M734MH197',163298, N'532343442', '2014',215037, '20180302',4),

(5,1,N'C123CA199',167892, N'523478889', '2017',46783, '20180909',4)

GO


INSERT WORK(IDWork, CarID, Driver,DateWork,StartTime, EndTime) VALUES

(1,5,2,'20180910', '18:00:00', '18:23:00'),

(2,2,1,'20180912', '19:00:00', '19:12:00'),

(3,1,3,'20180912','20:00:00','20:41:00'),

(4,4,6,'20180913','21:00:00', '21:59:00'),

(5,3,7,'20180913', '22:00:00', '22:04:00')

GO


INSERT Calls (IDCall,WorkID,TimeCall, Phone, StartPlace, EndPlace, TafifID, ServiceID,NameService, Operator) VALUES

(1,4,'20:57:00',N'89121211111', N'Никольская 2',N'Тверская 3',1,0,N'Нет',5 ),

(2,1,'17:45:00',N'89090001234',N'Летчиков 5',N'Арбат 18',3,2,N'Перевозка животного',5),

(3,2,'18:52:00',N'89564789393',N'Ивана Франко 41',N'Смоленская 7',2,0,N'Нет',5),

(4,5,'21:40:00',N'89132456789',N'Кибальчича 8',N'Парк Победы 43',3,3,N'Дополнительный багаж',5),

(5,3,'19:53:00',N'89167899009',N'Проектируемый проезд 43',N'Планерная 12',3,1,N'Детское кресло',5)

GO


--Вывод заполненных таблиц

SELECT * FROM AddServices

SELECT * FROM Calls

SELECT * FROM CarBrand

SELECT * FROM Cars

SELECT * FROM Employees

SELECT * FROM Positions

SELECT * FROM Tarif

SELECT * FROM Work



  1. Проверить работу заданных ограничений (п. 1) с сохранением результата в отчете.


--Проверка работы ограничений на удаление

DELETE FROM CarBrand

W
HERE IDCarBrand=1


--Проверка работы ограничений на каскадное удаление

INSERT AddServices(NameService, DescrService, CostService)VALUES

(N'Проверка',N'Проверка',100000)

SELECT * FROM AddServices



INSERT Calls (IDCall,WorkID,TimeCall, Phone, StartPlace, EndPlace, TafifID, ServiceID,NameService, Operator) VALUES

(6,4,'20:57:00',N'AAAAAAAAA', N'ОТКУДА',N'НИКУДА',1,4,N'Проверка',5 )

SELECT * FROM Calls



DELETE FROM AddServices

WHERE DescrService=N'Проверка'

SELECT * FROM Calls

SELECT * FROM AddServices

7



Смотрите также файлы