Создать базу данных «РАБОТА С НЕДВИЖИМОСТЬЮ». Возможные таблицы: районы, типы домов, типы квартир, заявки на покупку, заявки на продажу.
Содержание: 1. Разработка с помощью ERwin логической и физической модели данных для проектируемой базы данных. 2. Путем прямого проектирования получить файл базы данных в формате Access. 3. Создать SQL-сценарий, который позволяет сгенерировать базу данных в формате MS SQL Server, а также наполнить созданную базу данных конкретными данными. 4. Дополнить сценарий текстами перечисленных ниже хранимых процедур: • Создать хранимую процедуру, которая для указанной таблицы подсчитывает число строк, содержащихся в этой таблице. Эта процедура должна иметь один входной параметр (с помощью которого задается имя таблицы) и один выходной параметр. • Создать хранимую процедуру, которая изменяет все даты, имеющиеся в таблицах базы данных, добавляя к ним или отнимая от них некоторое число дней. Эта процедура должна иметь два входных параметра. Первый параметр определяет, нужно ли увеличивать или уменьшать все даты. Второй параметр указывает количество дней, которые должны добавляться или отниматься. Примечание. Если в таблицах отсутствуют даты, то аналогичную операцию необходимо проделать применительно ко всем имеющимся ценам и стоимостям. При этом второй параметр будет задавать процент, на который должны измениться все цены и стоимости. • Создать хранимую процедуру, которая для конкретной таблицы подсчитывает минимальное, среднее, максимальное и суммарное значения в столбце числового типа. Эта процедура должна иметь один входной параметр (с помощью которого указывается имя или номер столбца) и четыре выходных параметра. • Создать хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы). • Создать хранимую процедуру, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку, содержащую фамилию и инициалы, и при этом все строчные буквы должны быть заменены прописными. Эта процедура должна иметь один входной параметр (с помощью которого задается исходная текстовая строка) и один выходной параметр. При этом необходимо расширить возможности процедуры таким образом, чтобы была допустима исходная строка (задаваемая входным параметром), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией. 5. Создать в базе данных таблицу Протокол со структурой, приведенной ниже.
Номер ДатаВремя Пользователь Действие ЧислоСтрок . . . . . . . . . . . . . . .
Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие будет указываться одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк. 6. Создать триггер, связанный с конкретной таблицей базы данных, который предназначен для автоматической фиксации в таблице Протокол всех действий, вызванных со вставкой, обновлением или удалением данных в таблице, снабженной триггером. Каждая SQL-команда, изменяющая содержимое этой таблицы, должна быть отражена отдельной строкой в таблице Протокол. 7. Разработать SQL-код для проверки работы созданных хранимых процедур и триггера.
1. Разработка с помощью ERwin логической и физической модели данных для проектируемой базы данных.
Создадим в ERwin модель данных с именем Недвижимость. Тип модели (New Model Type) – Логическая/Физическая, целевая база данных (Target Database) – Access. Логическая модель данных:
Физическая модель данных:
2. Путем прямого проектирования получить файл базы данных в формате Access.
Запустим СУБД Access и создадим новую пустую базу данных Недвижимость.mdb, после чего закроем Access. В ERwin с помощью команды меню Tools►Forward Engineer/Schema Generation откроем окно Access Schema Generation и нажмём кнопку Generate. В появившемся окне Access Connection зададим имя пользователя (User Name) равным Admin, а также с помощью кнопки Browse (первой сверху) зададим полное имя созданной базы данных Недвижимость.mdb. Далее нажмём кнопку Connect и выполним процесс прямого проектирования (Forward Engineer) с наполнением файла базы данных Недвижимость.mdb метаданными согласно созданной физической модели данных. После завершения процесса прямого проектирования с помощью команды меню Database►Database Connection откроем окно Access Connection и разорвём соединение с базой данных Недвижимость.mdb путем нажатия кнопки Disconnect. Запустим СУБД Access, откроем базу данных Недвижимость.mdb. Откройте окно Схема данных:
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.
4. Дополнить сценарий текстами перечисленных ниже хранимых процедур: • Создать хранимую процедуру, которая для указанной таблицы подсчитывает число строк, содержащихся в этой таблице. Эта процедура должна иметь один входной параметр (с помощью которого задается имя таблицы) и один выходной параметр. На панели инструментов нажмём кнопку New Query, и откроется новое пустое окно Query, предназначенное для формирования нового сценария Script3.sql. CREATE PROCEDURE Подсчёт_числа_строк_в_таблице @Имя_табл VARCHAR(20), @число_строк INT OUTPUT AS IF @Имя_табл='Типы_домов' SELECT @число_строк=COUNT(*) FROM Типы_домов IF @Имя_табл='Типы_квартир' SELECT @число_строк=COUNT(*) FROM Типы_квартир IF @Имя_табл='Районы' SELECT @число_строк=COUNT(*) FROM Районы IF @Имя_табл='Заявки_на_покупку' SELECT @число_строк=COUNT(*) FROM Заявки_на_покупку IF @Имя_табл='Заявки_на_продажу' SELECT @число_строк=COUNT(*) FROM Заявки_на_продажу GO SQL-код для проверки работы созданной хранимой процедуры: DECLARE @Name VARCHAR(20), @Number INT SET @Name ='Районы' EXEC Подсчёт_числа_строк_в_таблице @Name, @Number OUTPUT SELECT @Number AS Число_строк GO
• Создать хранимую процедуру, которая изменяет все даты, имеющиеся в таблицах базы данных, добавляя к ним или отнимая от них некоторое число дней. Эта процедура должна иметь два входных параметра. Первый параметр определяет, нужно ли увеличивать или уменьшать все даты. Второй параметр указывает количество дней, которые должны добавляться или отниматься. Примечание. Если в таблицах отсутствуют даты, то аналогичную операцию необходимо проделать применительно ко всем имеющимся ценам и стоимостям. При этом второй параметр будет задавать процент, на который должны измениться все цены и стоимости. CREATE PROCEDURE Увелич_или_умен_цены_в_проц @УвеличУменьш VARCHAR(1), @Процент INT AS IF @УвеличУменьш='+' UPDATE Заявки_на_продажу SET Цена = Цена*(100+@Процент)/100 ELSE UPDATE Заявки_на_продажу SET Цена = Цена*(100-@Процент)/100 IF @УвеличУменьш='-' UPDATE Заявки_на_покупку SET ПредельнаяЦена=ПредельнаяЦена*(100-@Процент)/100 ELSE UPDATE Заявки_на_покупку SET ПредельнаяЦена=ПредельнаяЦена*(100+@Процент)/100 GO
SQL-код для проверки работы созданной хранимой процедуры:
SELECT Цена FROM Заявки_на_продажу SELECT ПредельнаяЦена FROM Заявки_на_покупку DECLARE @Percent INT, @УвелУмен VARCHAR(1) SET @Percent =5 SET @УвелУмен='+' EXEC Увелич_или_умен_цены_в_проц @УвелУмен, @Percent SELECT Цена FROM Заявки_на_продажу SELECT ПредельнаяЦена FROM Заявки_на_покупку GO
• Создать хранимую процедуру, которая для конкретной таблицы подсчитывает минимальное, среднее, максимальное и суммарное значения в столбце числового типа. Эта процедура должна иметь один входной параметр (с помощью которого указывается имя или номер столбца) и четыре выходных параметра. CREATE PROCEDURE Мин_Среднее_Макс_Сумм @Имя_столбца VARCHAR(20)='Цена', @мин INT OUTPUT, @средн INT OUTPUT, @макс INT OUTPUT, @сумм INT OUTPUT AS IF @Имя_столбца='НомерЗаявкиНаПрод' SELECT @мин=MIN(НомерЗаявкиНаПрод), @средн=AVG(НомерЗаявкиНаПрод), @макс=MAX(НомерЗаявкиНаПрод), @сумм=SUM(НомерЗаявкиНаПрод) FROM Заявки_на_продажу IF @Имя_столбца='Цена' SELECT @мин=MIN(Цена), @средн=AVG(Цена), @макс=MAX(Цена), @сумм=SUM(Цена) FROM Заявки_на_продажу IF @Имя_столбца='НомерКвартиры' SELECT @мин=MIN(НомерКвартиры), @средн=AVG(НомерКвартиры), @макс=MAX(НомерКвартиры), @сумм=SUM(НомерКвартиры) FROM Заявки_на_продажу IF @Имя_столбца='НомерЭтажа' SELECT @мин=MIN(НомерЭтажа), @средн=AVG(НомерЭтажа), @макс=MAX(НомерЭтажа), @сумм=SUM(НомерЭтажа) FROM Заявки_на_продажу GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @ИмяСтолбца VARCHAR(20), @min INT, @avg INT, @max INT, @sum INT SET @ИмяСтолбца ='НомерЭтажа' EXEC Мин_Среднее_Макс_Сумм @ИмяСтолбца, @min OUTPUT, @avg OUTPUT, @max OUTPUT, @sum OUTPUT SELECT @min AS Минимальное, @avg AS Среднее, @max AS Максимальное, @sum AS Суммарное GO
DECLARE @ИмяСтолбца VARCHAR(20), @min INT, @avg INT, @max INT, @sum INT SET @ИмяСтолбца ='Цена' EXEC Мин_Среднее_Макс_Сумм @ИмяСтолбца, @min OUTPUT, @avg OUTPUT, @max OUTPUT, @sum OUTPUT SELECT @min AS Минимальное, @avg AS Среднее, @max AS Максимальное, @sum AS Суммарное GO
• Создать хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы). CREATE PROCEDURE Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT AS IF @Условие_для_ТипДома IS NOT NULL BEGIN IF @Условие_для_Этажность IS NOT NULL SELECT * FROM Типы_домов WHERE ТипДома=@Условие_для_ТипДома AND Этажность<=@Условие_для_Этажность ELSE SELECT * FROM Типы_домов WHERE ТипДома=@Условие_для_ТипДома END ELSE IF @Условие_для_Этажность IS NOT NULL SELECT * FROM Типы_домов WHERE Этажность<=@Условие_для_Этажность IF @Условие_для_ТипДома IS NULL AND @Условие_для_Этажность IS NULL SELECT * FROM Типы_домов GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT SET @Условие_для_ТипДома ='Кирпичный' SET @Условие_для_Этажность=9 EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT SET @Условие_для_ТипДома ='Блочный' SET @Условие_для_Этажность=5 EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT SET @Условие_для_ТипДома =NULL SET @Условие_для_Этажность=5 EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT SET @Условие_для_ТипДома =NULL SET @Условие_для_Этажность=NULL EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность GO
• Создать хранимую процедуру, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку, содержащую фамилию и инициалы, и при этом все строчные буквы должны быть заменены прописными. Эта процедура должна иметь один входной параметр (с помощью которого задается исходная текстовая строка) и один выходной параметр. При этом необходимо расширить возможности процедуры таким образом, чтобы была допустима исходная строка (задаваемая входным параметром), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией. CREATE PROCEDURE Преобразование_текс_строки @Строка1 VARCHAR(50), @Строка2 VARCHAR(30) OUTPUT AS DECLARE @Ind1 INT, @Фамилия VARCHAR(30), @И VARCHAR(1), @О VARCHAR(1), @Строка VARCHAR(30), @ИмяОтчество VARCHAR(40), @Ind2 INT, @Отчество VARCHAR(20) SET @Строка1 = LTRIM(@Строка1)--удаляет лидирующие пробелы SET @Ind1=CHARINDEX(' ', @Строка1)--первый пробел в строке SET @Фамилия=LEFT(@Строка1, @Ind1-1) --возвращает крайние левые символы из строки SET @ИмяОтчество=SUBSTRING(@Строка1, @Ind1, LEN(@Строка1)-@Ind1+1) --извлекает LEN(@Строка1)-@Ind1+1 из строки начиная с позиции @Ind1 SET @ИмяОтчество=LTRIM(@ИмяОтчество) SET @Ind2=CHARINDEX(' ', @ИмяОтчество) SET @И=LEFT(@ИмяОтчество,1) SET @Отчество= SUBSTRING(@ИмяОтчество, @Ind2, LEN(@ИмяОтчество)-@Ind2+1) SET @Отчество=LTRIM(@Отчество) SET @О=LEFT(@Отчество,1) SET @Строка=@Фамилия+' '+@И+'.'+@О+'.' SET @Строка2=LOWER(@Строка)--преобразует все символы строки в символы нижнего регистра GO SQL-код для проверки работы созданной хранимой процедуры: DECLARE @Строка_1 VARCHAR(50), @Строка_2 VARCHAR(30) SET @Строка_1 ='Писарчик Екатерина Викторовна' EXEC Преобразование_текс_строки @Строка_1, @Строка_2 OUTPUT SELECT @Строка_2 AS Результат GO
DECLARE @Строка_1 VARCHAR(50), @Строка_2 VARCHAR(30) SET @Строка_1 =' Писарчик Екатерина Викторовна' EXEC Преобразование_текс_строки @Строка_1, @Строка_2 OUTPUT SELECT @Строка_2 AS Результат GO
5. Создать в базе данных таблицу Протокол со структурой, приведенной ниже.
Номер ДатаВремя Пользователь Действие ЧислоСтрок . . . . . . . . . . . . . . .
Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие будет указываться одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк.
CREATE TABLE Протокол ( Номер INT IDENTITY(1,1)PRIMARY KEY, ДатаВремя DATETIME NOT NULL, Пользователь VARCHAR(20) NULL, Действие VARCHAR(20) NULL, ЧислоСтрок INT NOT NULL )
6. Создать триггер, связанный с конкретной таблицей базы данных, который предназначен для автоматической фиксации в таблице Протокол всех действий, связанных со вставкой, обновлением или удалением данных в таблице, снабженной триггером. Каждая SQL-команда, изменяющая содержимое этой таблицы, должна быть отражена отдельной строкой в таблице Протокол.
CREATE TRIGGER tr_Триггер1 ON Заявки_на_продажу FOR INSERT AS INSERT INTO Протокол VALUES (getdate(), suser_sname(), 'Вставка', @@ROWCOUNT) GO CREATE TRIGGER tr_Триггер2 ON Заявки_на_продажу FOR UPDATE AS INSERT INTO Протокол VALUES (getdate(), suser_sname(), 'Обновление', @@ROWCOUNT) GO CREATE TRIGGER tr_Триггер3 ON Заявки_на_продажу FOR DELETE AS INSERT INTO Протокол VALUES (getdate(), suser_sname(), 'Удаление', @@ROWCOUNT) GO
SQL-код для проверки работы созданного триггера:
SELECT * FROM Протокол INSERT INTO Заявки_на_продажу --добавить строку VALUES (120000, 'Зелёная', '23', 120, '74м2','59м2',10,'Нет',NULL,2) UPDATE Заявки_на_продажу --обновить все строки SET Цена=Цена/2 DELETE --удалить строки FROM Заявки_на_продажу WHERE КодТипаКвартиры=2 SELECT * FROM Протокол GO
Список использованной литературы:
1. Бутов А. А., Орешко И. Г., Шестаков Е. А. Визуальные и программные средства проектирования реляционных баз данных. Лабораторный практикум для студ.– Минск : БГУИР, 2009. – 108 с. 2. Джеффри Р.Гарбус, Дэвид Ф.Паскузи, Элвин Т.Чанг Database design on SQL Server 7 – Питер, 550с. 3. Мамаев Е. MS SQL SERVER 2000.
