Создание информационно-справочной системы

Листинг 1 – Код SQL-сценария для создания базы данных «Бюро знакомств»

CREATE TABLE Профессия (

Код_профессии INT NOT NULL,

Название NVARCHAR ( 50 ) NOT NULL,

CONSTRAINT TC_Профессия4 UNIQUE NONCLUSTERED (Код_профессии),

CONSTRAINT TC_Профессия5 UNIQUE NONCLUSTERED (Название),

CONSTRAINT PK_Профессия3 PRIMARY KEY NONCLUSTERED (Код_профессии)

)

GO

 

CREATE TABLE Увлечение (

Код_увлечения INT NOT NULL,

Код_анкеты INT NOT NULL,

CONSTRAINT PK_Увлечение9 PRIMARY KEY NONCLUSTERED (Код_анкеты, Код_увлечения)

)

GO

 

CREATE TABLE Билет (

Код_анкеты INT NOT NULL,

Дата_вечера DATETIME NOT NULL,

CONSTRAINT PK_Билет8 PRIMARY KEY NONCLUSTERED (Дата_вечера, Код_анкеты)

)

GO

 

CREATE TABLE Вид_увлечения  (

Код_увлечения INT NOT NULL,

Название NVARCHAR ( 50 ) NOT NULL,

CONSTRAINT PK_Вид_увлечения5 PRIMARY KEY NONCLUSTERED (Код_увлечения),

CONSTRAINT TC_Вид_увлечения7 UNIQUE NONCLUSTERED (Код_увлечения),

CONSTRAINT TC_Вид_увлечения8 UNIQUE NONCLUSTERED (Название)

)

GO

 

CREATE TABLE Цель (

Код_цели INT NOT NULL,

Название NVARCHAR ( 50 ) NOT NULL,

CONSTRAINT TC_Цель0 UNIQUE NONCLUSTERED (Код_цели),

CONSTRAINT TC_Цель1 UNIQUE NONCLUSTERED (Название),

CONSTRAINT PK_Цель0 PRIMARY KEY NONCLUSTERED (Код_цели)

)

GO

 

CREATE TABLE Анкета (

Код_анкеты INT NOT NULL,

Фамилия NVARCHAR ( 50 ) NOT NULL,

Имя NVARCHAR ( 50 ) NOT NULL,

Отчество NVARCHAR ( 50 ) NOT NULL,

Серия NVARCHAR ( 2 ) NOT NULL,

Номер NVARCHAR ( 8 ) NOT NULL,

Телефон NVARCHAR ( 14 ) NOT NULL,

Почта NVARCHAR ( 50 ) NOT NULL,

Пол NVARCHAR ( 10 ) NOT NULL,

Дата_рождения DATETIME NOT NULL,

Дети BIT NOT NULL,

Фото NVARCHAR ( 100 ) NOT NULL,

Код_цели INT NOT NULL,

Код_профессии INT NOT NULL,

CONSTRAINT TC_Анкета15 UNIQUE NONCLUSTERED (Телефон),

CONSTRAINT TC_Анкета14 UNIQUE NONCLUSTERED (Номер),

CONSTRAINT TC_Анкета10 UNIQUE NONCLUSTERED (Фамилия),

CONSTRAINT TC_Анкета11 UNIQUE NONCLUSTERED (Имя),

CONSTRAINT PK_Анкета6 PRIMARY KEY NONCLUSTERED (Код_анкеты),

CONSTRAINT TC_Анкета12 UNIQUE NONCLUSTERED (Отчество),

CONSTRAINT TC_Анкета13 UNIQUE NONCLUSTERED (Серия),

CONSTRAINT TC_Анкета16 UNIQUE NONCLUSTERED (Почта),

CONSTRAINT TC_Анкета18 UNIQUE NONCLUSTERED (Пол),

CONSTRAINT TC_Анкета9 UNIQUE NONCLUSTERED (Код_анкеты),

CONSTRAINT TC_Анкета17 UNIQUE NONCLUSTERED (Дата_рождения)

)

GO

 

CREATE TABLE Вечер (

Дата_вечера DATETIME NOT NULL,

Возраст NVARCHAR ( 50 ) NOT NULL,

Цена_билета MONEY NOT NULL,

CONSTRAINT PK_Вечер7 PRIMARY KEY NONCLUSTERED (Дата_вечера),

CONSTRAINT TC_Вечер24 UNIQUE NONCLUSTERED (Возраст),

CONSTRAINT TC_Вечер23 UNIQUE NONCLUSTERED (Дата_вечера),

CONSTRAINT TC_Вечер25 UNIQUE NONCLUSTERED (Цена_билета)

)

GO

 

CREATE TABLE Вид_услуги (

Код_услуги INT NOT NULL,

Название NVARCHAR ( 50 ) NOT NULL,

CONSTRAINT TC_Вид_услуги6 UNIQUE NONCLUSTERED (Код_услуги),

CONSTRAINT PK_Вид_услуги4 PRIMARY KEY NONCLUSTERED (Код_услуги),

CONSTRAINT TC_Вид_услуги3 UNIQUE NONCLUSTERED (Название)

)

GO

 

CREATE TABLE Услуга (

Цена_услуги MONEY NOT NULL,

Код_анкеты INT NOT NULL,

Код_услуги INT NOT NULL,

CONSTRAINT PK_Услуга10 PRIMARY KEY NONCLUSTERED (Код_услуги, Код_анкеты),

CONSTRAINT TC_Услуга36 UNIQUE NONCLUSTERED (Цена_услуги)

)

GO

 

ALTER TABLE Увлечение ADD CONSTRAINT FK_Увлечение5 FOREIGN KEY (Код_увлечения) REFERENCES Вид_увлечения (Код_увлечения) 

GO

 

ALTER TABLE Увлечение ADD CONSTRAINT FK_Увлечение6 FOREIGN KEY (Код_анкеты) REFERENCES Анкета (Код_анкеты)

GO

 

ALTER TABLE Билет ADD CONSTRAINT FK_Билет3 FOREIGN KEY (Код_анкеты) REFERENCES Анкета (Код_анкеты)

GO

ALTER TABLE Билет ADD CONSTRAINT FK_Билет4 FOREIGN KEY (Дата_вечера) REFERENCES Вечер  (Дата_вечера)

GO

 

ALTER TABLE Услуга ADD CONSTRAINT FK_Услуга7 FOREIGN KEY (Код_анкеты) REFERENCES Анкета (Код_анкеты)

GO

 

ALTER TABLE Услуга ADD CONSTRAINT FK_Услуга8 FOREIGN KEY (Код_услуги) REFERENCES Вид_услуги (Код_услуги)

GO

 

ALTER TABLE Анкета ADD CONSTRAINT FK_Анкета2 FOREIGN KEY (Код_профессии) REFERENCES Профессия  (Код_профессии) 

GO

 

ALTER TABLE Анкета ADD CONSTRAINT FK_Анкета1 FOREIGN KEY (Код_цели) REFERENCES Цель (Код_цели)

GO

 

5 Хранимые процедуры для вставки данных

 

В целях упрощения программного доступа к разрабатываемой базе данных «Бюро знакомств» и обеспечения целостности данных следует разработать SQL-команды для осуществления вставки данных в таблицы и сохранить их на сервере для последующего использования. Хранимая процедура – это именованный набор операторов языка T-SQL, который компилируется системой SQL Server в единый план исполнения – готовую схему исполнения запроса. Для каждой таблицы базы данных создана хранимая процедура вставки, которая может быть использована для выполнения запроса на добавление новых записей в таблицу. Все процедуры вставки возвращают текстовый результат, который удобно вывести в панель сообщений в разрабатываемой программе управления базой данных.

Имя процедуры: добавить_вид_услуги

Назначение процедуры: добавление вида услуги.

Таблица 20 – Аргументы процедуры «добавить_вид_услуги»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@название	название  услуги	входной	нет	nvarchar(30)

 

Возвращаемый результат  процедуры:

• Ошибка: запись уже существует
• Ошибка: нельзя присвоить пустую строку
• Запись успешно вставлена

Листинг 2 – SQL код создания и использования  процедуры «добавить_вид_услуги»

create procedure добавить_вид_услуги

@result nvarchar(50) output,

@название nvarchar(50)

as

begin

-- Проверка  на одинаковую запись

if exists(select * from вид_услуги where

название = @название)

begin

select'Ошибка: запись уже существует'

return

end

-- Нельзя  вставлять пустые строки

if (@название ='')

begin

select'Ошибка: нельзя присвоить пустую строку'

return

end

begin

insert into вид_услуги(

название

)

values(

@название

)

if @@rowcount>0

select'Запись успешно вставлена'

end

end

go

 

--Пример использования:

declare @res nvarchar(50)

exec добавить_вид_услуги @res output, 'Фотосессия'

select @res

 

Аналогичным образом создаем  процедуры для добавления полей в таблицу «Вид увлечения».

Имя процедуры: добавить_вид_увлечения

Назначение процедуры: добавление вида увлечения.

 

Таблица 21 – Аргументы процедуры «добавить_вид_увлечения»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@название	название  увлечения	входной	нет	nvarchar(30)

 

Возвращаемый результат  процедуры:

• Ошибка: запись уже существует
• Ошибка: нельзя присвоить пустую строку
• Запись успешно вставлена

Листинг 3 – SQL код создания и использования процедуры «добавить_вид_ увлечения»

create procedure добавить_вид_увлечения

@result nvarchar(50) output,

@название nvarchar(50)

as

begin

-- Проверка  на одинаковую запись

if exists(select * from вид_ увлечения where

название = @название)

begin

select'Ошибка: запись уже существует'

return

end

-- Нельзя  вставлять пустые строки

if (@название ='')

begin

select'Ошибка: нельзя присвоить пустую строку'

return

end

begin

insert into вид_ увлечения (

название

)

values(

@название

)

if @@rowcount>0

select'Запись успешно вставлена'

end

end

go

--Пример использования:

declare @res nvarchar(50)

exec добавить_вид_ увлечения @res output, 'Марки'

select @res

 

Аналогичным образом создаем  процедуры для добавления полей  в таблицу «Цель».

Имя процедуры: добавить_цель

Назначение процедуры: добавление цели.

Таблица 22 – Аргументы процедуры «добавить_цель»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@название	название  цели	входной	нет	nvarchar(30)

 

Возвращаемый результат  процедуры:

• Ошибка: запись уже существует
• Ошибка: нельзя присвоить пустую строку
• Запись успешно вставлена

Листинг 4 – SQL код создания и использования процедуры «добавить_цель»

create procedure добавить_цель

@result nvarchar(50) output,

@название nvarchar(50)

as

begin

-- Проверка  на одинаковую запись

if exists(select * from цель where

название = @название)

begin

select'Ошибка: запись уже существует'

return

end

-- Нельзя  вставлять пустые строки

if (@название ='')

begin

select'Ошибка: нельзя присвоить пустую строку'

return

end

begin

insert into цель (

название

)

values(

@название

)

if @@rowcount>0

select'Запись успешно вставлена'

end

end

go

 

--Пример использования:

declare @res nvarchar(50)

exec добавить_цель @res output, 'Занятие спортом'

select @res

 

Аналогичным образом создаем  процедуры для добавления полей  в таблицу «Профессия».

Имя процедуры: добавить_профессию

Назначение процедуры: добавление профессии.

Таблица 23 – Аргументы процедуры «добавить_профессию»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@название	название  профессии	входной	нет	nvarchar(30)

 

Возвращаемый результат  процедуры:

• Ошибка: запись уже существует
• Ошибка: нельзя присвоить пустую строку
• Запись успешно вставлена

Листинг 5 – SQL код создания и использования процедуры «добавить_цель»

create procedure добавить_профессию

@result nvarchar(50) output,

@название nvarchar(50)

as

begin

-- Проверка  на одинаковую запись

if exists(select * from профессия where

название = @название)

begin

select'Ошибка: запись уже существует'

return

end

-- Нельзя  вставлять пустые строки

if (@название ='')

begin

select'Ошибка: нельзя присвоить пустую строку'

return

end

begin

insert into профессия (

название

)

values(

@название

)

if @@rowcount>0

select'Запись успешно вставлена'

end

end

go

 

--Пример использования:

declare @res nvarchar(50)

exec добавить_ профессию @res output, 'Военный'

select @res

 

Аналогичным образом создаем  процедуры для добавления полей  в таблицу «Вечер».

Имя процедуры: добавить_вечер

Назначение процедуры: добавление вечера с указанием даты вечера, возврастной категории и цены билета.

Таблица 24 – Аргументы процедуры «добавить_вечер»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@дата_вечера	Дата  вечера	входной	текущая дата	datetime
@возраст	Возрастная  категория	входной	пустая  строка	nvarchar(50)
@цена_билета	Цена  билета на вечер	входной	null	money

 

Возвращаемый результат  процедуры:

• Ошибка: запись уже существует
• Ошибка: нельзя присвоить пустую строку
• Запись успешно вставлена

Листинг 6 – SQL код создания и использования процедуры «добавить_вечер»

create procedure добавить_страну

@result nvarchar(50) output,

@дата_вечера datetime=now,

@возраст nvarchar(30)='',

@цена_билета money = null

as

begin

-- Проверка  на одинаковую запись

if exists(select * from вечер where

дата_вечера = @дата_вечера)

begin

select'Ошибка: запись уже существует'

return

end

-- Нельзя  вставлять пустые строки

if (@дата_вечера = null) and (@возраст='') and (@цена_билета=null)

begin

select'Ошибка: нельзя присвоить вечеру пустую строку'

return

end

insert into вечер(

дата_вечера,

возраст,

цена_билета

)

values(

@дата_вечера,

@возраст,

@цена_билета

)

if @@rowcount>0

select'Запись успешно вставлена'

end

go

 

--Пример использования:

declare @res nvarchar(50)

exec добавить_вечер @res output, 20.11.2012, '25-40', 450

select @res

 

Аналогичным образом создаем  процедуры для добавления полей  в таблицу «Анкета».

Имя процедуры: добавить_анкету

Назначение процедуры: добавление анкеты с указанием фамилии, имени, отчества, серии и номера паспорта, телефона, почтового адреса, пола даты рождения, наличия детей, ссылки на фото, цели и профессии.

Таблица 25 – Аргументы процедуры «добавить_анкету»

Аргумент	Смысл	Вид аргумента	Значение  по умолчанию	Тип
@result	результат работы процедуры	выходной	0	nvarchar(50)
@фамилия	фамилия	входной	нет	nvarchar(50)
@имя	имя	входной	нет	nvarchar(50)
@отчество	отчество	входной	нет	nvarchar(50)
@серия	серия паспорта	входной	нет	nvarchar(2)
@номер	номер паспорта	входной	нет	nvarchar(8)
@телефон	номер телефона	входной	пустая  строка	nvarchar(14)
@почта	почтовый  адрес	входной	пустая  строка	nvarchar(50)
@пол	пол	входной	нет	nvarchar(10)
@дата_рождения	дата  рождения	входной	нет	datetime
@дети	наличие детей	входной	0	bit
@фото	путь  к фото	входной	пустая  строка	nvarchar(100)
@код_цели	код цели	входной	null	int
@код_профессии	код профессии	входной	null	int