- •Контрольная работа
- •Вариант № 16
- •Задание 16
- •Содержание:
- •3. Создать sql-сценарий, который позволяет сгенерировать базу данных в формате ms sql Server, а также наполнить созданную базу данных конкретными данными.
- •4. Дополнить сценарий текстами перечисленных ниже хранимых процедур:
- •Список использованной литературы:
3. Создать sql-сценарий, который позволяет сгенерировать базу данных в формате ms sql Server, а также наполнить созданную базу данных конкретными данными.
Запустим утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскройте папку Databases. Далее на панели инструментов нажмём кнопку New Query, что вызовет появление на экране окна создания запросов к базе данных. В этом окне будем формировать сценарий (скрипт):
/*Созданине новой базы данных*/
CREATE DATABASE Недвижимость_702302_47
ON PRIMARY
( NAME = Недвижимость_Data,
FILENAME = 'E:\Work\Недвижимость_702302_47_Data.mdf',
SIZE = 3MB,
MAXSIZE = 50MB,
FILEGROWTH = 2MB ),
FILEGROUP Secondary
( NAME = Недвижимость2_Data,
FILENAME = 'E:\Work\Недвижимость_702302_47_Data2.ndf',
SIZE = 2MB,
MAXSIZE = 70MB,
FILEGROWTH = 20% ),
( NAME = Недвижимость3_Data,
FILENAME = 'E:\Work\Недвижимость_702302_47_Data3.ndf',
SIZE = 2MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = Недвижимость_Log,
FILENAME = 'E:\Work\Недвижимость_702302_47_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 15% ),
( NAME = Недвижимость2_Log,
FILENAME = 'E:\Work\Недвижимость_702302_47_Log2.ldf',
SIZE = 512KB,
MAXSIZE = 5MB,
FILEGROWTH = 10% )
GO
/*Подключение к базе данных*/
USE Недвижимость_702302_47
GO
/*Создание правила*/
CREATE RULE Logical_Rule AS @value IN ('Нет', 'Да')
GO
/*Создание умолчания*/
CREATE DEFAULT Logical_Default AS 'Нет'
GO
/*Создание пользовательского типа данных*/
EXEC sp_addtype Logical, 'char(3)', 'NOT NULL'
GO
/*Связывание правила с пользовательским типом данных*/
EXEC sp_bindrule 'Logical_Rule', 'Logical'
GO
/*Связывание умолчания с пользовательским типом данных*/
EXEC sp_bindefault 'Logical_Default', 'Logical'
GO
/* Создание таблицы базы данных */
/* Районы */
CREATE TABLE Районы (
КодРайона INT PRIMARY KEY,
Наименование VARCHAR(20) NOT NULL,
CONSTRAINT CIX_Районы UNIQUE (Наименование)
ON Secondary
)
/* Типы_домов */
CREATE TABLE Типы_домов (
КодТипаДома INT IDENTITY(1,1) PRIMARY KEY,
ТипДома VARCHAR(20) NULL,
Этажность INT NOT NULL
)
/* Типы_квартир */
CREATE TABLE Типы_квартир (
КодТипаКвартиры INT IDENTITY(1,1) PRIMARY KEY,
КоличествоКомнат INT NOT NULL,
НаличиеМебели VARCHAR(4) Default 'Нет' NOT NULL,
НаличиеТелефона VARCHAR(4) Default 'Есть' NOT NULL,
КодРайона INT NULL,
КодТипаДома INT NULL,
CONSTRAINT FK_Типы_квартир_Районы FOREIGN KEY (КодРайона)
REFERENCES Районы ON UPDATE CASCADE,
CONSTRAINT FK_Типы_квартир_Типы_домов FOREIGN KEY (КодТипаДома)
REFERENCES Типы_домов ON UPDATE CASCADE
)
/* Заявки_на_покупку */
CREATE TABLE Заявки_на_покупку (
НомерЗаявкиНаПокупку INT IDENTITY(1,1) PRIMARY KEY,
ПредельнаяЦена INT NOT NULL,
ОбщаяПлощадь CHAR(10) NULL,
ПолезнаяПлощадь CHAR(10) NULL,
НомерЭтажа INT NULL,
НаличиеКапремонта CHAR(4) Default 'Нет' NULL,
Особенности CHAR(30) NULL,
КодТипаКвартиры INT NULL,
CONSTRAINT FK_Заявки_на_покупку_Типы_квартир FOREIGN KEY (КодТипаКвартиры)
REFERENCES Типы_квартир ON UPDATE CASCADE
)
/* Заявки_на_продажу */
CREATE TABLE Заявки_на_продажу (
НомерЗаявкиНаПрод INT IDENTITY(1,1) PRIMARY KEY,
Цена INT NOT NULL,
Улица CHAR(20) NOT NULL,
НомерДома CHAR(20) NOT NULL,
НомерКвартиры INT NOT NULL,
ОбщаяПлощадь CHAR(10) NOT NULL,
ПолезнаяПлощадь CHAR(10) NULL,
НомерЭтажа INT NOT NULL,
НаличиеКапремонта CHAR(4) Default 'Нет' NULL,
Особенности CHAR(30) NULL,
КодТипаКвартиры INT NULL,
CONSTRAINT FK_Заявки_на_продажу_Типы_квартир FOREIGN KEY (КодТипаКвартиры)
REFERENCES Типы_квартир ON UPDATE CASCADE
)
GO
/*Создание индекса таблицы*/
CREATE UNIQUE INDEX UIX_Районы ON Районы (Наименование)
ON Secondary
CREATE UNIQUE INDEX UIX_Типы_домов ON Типы_домов (КодТипаДома)
ON Secondary
CREATE UNIQUE INDEX UIX_Типы_квартир ON Типы_квартир (КодТипаКвартиры)
ON Secondary
CREATE INDEX IX_Заявки_на_покупку ON Заявки_на_покупку (ПредельнаяЦена) ON Secondary
CREATE INDEX IX_Заявки_на_продажу ON Заявки_на_продажу (Цена)
ON Secondary
GO
/*Вставка новой строки в таблицу базы данных*/
INSERT INTO Районы
VALUES (101, 'Курасовщина')
INSERT INTO Районы
VALUES (201, 'Чижовка')
INSERT INTO Районы
VALUES (202, 'Серебрянка')
INSERT INTO Районы
VALUES (206, 'Зелёный Луг')
INSERT INTO Районы
VALUES (301, 'Уручье')
GO
INSERT INTO Типы_домов
VALUES ('Блочный', 5)
INSERT INTO Типы_домов
VALUES ('Кирпичный', 5)
INSERT INTO Типы_домов
VALUES ('Панельный', 9)
INSERT INTO Типы_домов
VALUES ('Кирпичный', 3)
INSERT INTO Типы_домов
VALUES ('Блочный', 9)
GO
INSERT INTO Типы_Квартир
VALUES (5, '', '', 101,3)
INSERT INTO Типы_Квартир
VALUES (3, 'Есть', '', NULL, NULL)
INSERT INTO Типы_Квартир
VALUES (4, '', 'Нет', 101,1)
INSERT INTO Типы_Квартир
VALUES (2, 'Есть', 'Есть', 202, NULL)
GO
INSERT INTO Заявки_на_покупку
VALUES (100000, '50м2', NULL, NULL, '', NULL, 2)
INSERT INTO Заявки_на_покупку
VALUES (80000, NULL, NULL, 1, 'Есть', NULL, 3)
INSERT INTO Заявки_на_покупку
VALUES (130000, '70м2', NULL, NULL, '', NULL, 1)
INSERT INTO Заявки_на_покупку
VALUES (90000, '40м2', NULL, NULL, 'Есть', NULL, 4)
GO
INSERT INTO Заявки_на_продажу
VALUES (98000, 'Ташкентская', '30', 16, '80', NULL, 2, '', NULL, 1)
INSERT INTO Заявки_на_продажу
VALUES (55000, 'Магистральная', '2а', 87, '43м2', NULL, 4, 'Есть', NULL, 3)
INSERT INTO Заявки_на_продажу
VALUES (110000, 'Восточная', '12/3', 59, '78', '61', 7, 'Есть', NULL, 1)
INSERT INTO Заявки_на_продажу
VALUES (80000, 'Клумова', '15', 8, '60', NULL, 1, '', NULL, 2)
GO
/*Создание представления базы данных*/
CREATE VIEW Запрос1 AS
SELECT TOP 100 PERCENT Районы.Наименование, Типы_квартир.КоличествоКомнат,
Заявки_на_продажу.Цена, Заявки_на_продажу.ОбщаяПлощадь, Заявки_на_продажу.Улица,
Заявки_на_продажу.НомерДома, Заявки_на_продажу.НомерКвартиры
FROM Типы_квартир
INNER JOIN Районы
ON Типы_квартир.КодРайона = Районы.КодРайона
INNER JOIN Заявки_на_продажу
ON Типы_квартир.КодТипаКвартиры = Заявки_на_продажу.КодТипаКвартиры
ORDER BY Типы_квартир.КоличествоКомнат, Заявки_на_продажу.Цена DESC
GO
/*Предоставление доступа к серверу учётной записи Windows XP*/
EXEC sp_grantlogin 'kate\sql1'
EXEC sp_grantlogin 'kate\sql2'
EXEC sp_grantlogin 'kate\sql3'
EXEC sp_grantlogin 'kate\sql4'
GO
/*Добавление учетной записи в фиксированную роль сервера*/
EXEC sp_addsrvrolemember 'kate\sql1', 'dbcreator'
GO
/*Создание нового пользователя и связывание его с учетной записью*/
EXEC sp_grantdbaccess 'kate\sql1', 'sql1'
EXEC sp_grantdbaccess 'kate\sql2', 'sql2'
EXEC sp_grantdbaccess 'kate\sql3', 'sql3'
EXEC sp_grantdbaccess 'kate\sql4', 'sql4'
GO
/*Создание пользовательской роли*/
EXEC sp_addrole 'Гл.бухгалтер', 'sql1'
EXEC sp_addrole 'Бухгалтера', 'sql1'
EXEC sp_addrole 'Экономисты', 'sql1'
GO
/*Добавление нового члена в роль (как фиксированную,
так и пользовательскую) базы данных*/
EXEC sp_addrolemember 'db_accessadmin', 'sql1'
EXEC sp_addrolemember 'Гл.бухгалтер', 'sql1'
EXEC sp_addrolemember 'Бухгалтера', 'sql2'
EXEC sp_addrolemember 'Бухгалтера', 'sql3'
EXEC sp_addrolemember 'Бухгалтера', 'Гл.бухгалтер'
EXEC sp_addrolemember 'Экономисты', 'sql4'
EXEC sp_addrolemember 'Экономисты', 'Гл.бухгалтер'
GO
/*Предоставление привилегий доступа к объектам базы данных*/
GRANT SELECT, INSERT, UPDATE, DELETE
ON Районы TO [Гл.бухгалтер] WITH GRANT OPTION
GRANT UPDATE
ON Заявки_на_продажу TO [Гл.бухгалтер] WITH GRANT OPTION
GRANT SELECT
ON Запрос1 TO [Гл.бухгалтер] WITH GRANT OPTION
GRANT UPDATE, DELETE
ON Типы_домов TO [Гл.бухгалтер] WITH GRANT OPTION
GRANT UPDATE, DELETE
ON Типы_квартир TO [Гл.бухгалтер] WITH GRANT OPTION
GRANT SELECT, INSERT
ON Заявки_на_продажу TO Бухгалтера
GRANT SELECT, INSERT
ON Типы_домов TO Бухгалтера
GRANT SELECT, INSERT
ON Типы_квартир TO Экономисты
GRANT SELECT
ON Заявки_на_продажу TO Экономисты
GRANT SELECT, INSERT, UPDATE, DELETE
ON Заявки_на_покупку TO public
GO
DENY UPDATE /*Запрещение доступа к объектам базы данных*/
ON Заявки_на_продажу (ОбщаяПлощадь) TO [Гл.бухгалтер] CASCADE
GO
При помощи пользовательского меню Windows запустим утилиту SQL Server Management Studio, после чего на панели Object Explorer в древовидной структуре раскроем папку Databases.
С помощью команды меню File►Open►File загрузим сценарий из файла Script.sql в окно Query.
Выполним сценарий, нажав на панели инструментов кнопку Execute. В результате будет создана база данных Недвижимость_702302_47. Обновим данные на панели Object Explorer. Для этого используем команду Refresh в контекстном меню папку Databases. В результате база данных Недвижимость_702302_47 станет видимой на панели Object Explorer.