Проектирование и разработка реляционной базы данных «Электронная кулинарная книга»

 

Таблица Composition_11 служит для хранения информации об составе блюда. Ее структура приведена в таблице 3.10.

Таблица 3.10 – Характеристика атрибутов таблицы Composition_11

Имя атрибута	Тип	Описание
Composition_ID	INT	Идентификатор записи. Ключевое поле
Comp_Ingridient_ID	INT	Идентификатор ингридиента
Comp_recipe_ID	INT	Идентификатор рецепта
Comp_Unit_measure_ID	INT	Идентификатор единицы измерения
Comp_condition_ID	DATE	Идентификатор состояния
Quantity	CHAR(10)	Колличество

 

Таблица Ingridient содержит сведения об ингридиентах, необходимых для приготовления блюда . Структура приведена в таблице 3.11.

Таблица 3.11 – Характеристика атрибутов таблицы Ingridient

Имя атрибута	Тип	Описание
Ingridient_ID	INT	Идентификатор ингридиента
Ingridient_name	CHAR(50)	Название ингридиента

 

Таблица Unit_measure содержит сведения об единице измерения блюда. Структура приведена в таблице 3.12.

Таблица 3.12 – Характеристика атрибутов таблицы Account

Имя атрибута	Тип	Описание
Unit_measure_ID	INT	Идентификатор единицы измерения
Unit_measure_name	CHAR(50)	Название единицы измерения

 

Таблица Condition хранит информацию о состоянии блюда. Ее структура приведена в таблице 3.13.

Таблица 3.13 – Характеристика атрибутов таблицы Condition

Имя атрибута	Тип	Описание
Condition_ID	INT	Идентификатор состояния
Condition_name	CHAR(50)	Название состояния

 

4 ЗАПИСЬ ВЫРАЖЕНИЙ, УКАЗАННЫХ В ВАРИАНТЕ 
ЗАДАНИЯ ТИПОВ ЗАПРОСОВ НА ЯЗЫКЕ SQL

1. Процедура проверки существующего логина, при авторизации пользователя

CREATE PROCEDURE CheckLogin

   @par char(20)

AS

BEGIN

DECLARE @a int;

SELECT @a = COUNT([Login]) FROM User_1

WHERE [Login] = @par

GROUP BY [Login]

IF(@a >0)

BEGIN

SELECT 'Логин занят';

END

ELSE

BEGIN

SELECT 'Логин свободен';

END

END

 

2. Триггер на вставку в таблицу User_1 на преобразование первой буквы фамилии, имени к верхнему регистру

CREATE TRIGGER Tr_letters

ON User_1 FOR INSERT

AS

BEGIN

update User_1

set Surname = STUFF(Lower(i.Surname),1,1,Upper(LEFT(i.Surname,1)))

from User_1 i

join inserted t on t.Surname = i.Surname

 

update User_1

set Name = STUFF(Lower(i.Name),1,1,Upper(LEFT(i.Name,1)))

from User_1 i

join inserted t on t.Name = i.Name

    END

 

3. Процедура добавления нового отзыва о рецепте

CREATE PROCEDURE NewOtzivAd

 

@Mess char(250),

@CurrDate char(10),

@CurrRID char(20),

@CurrRUID char(20),

@CurrID int

AS

BEGIN

 

INSERT INTO Reference_1([Message],[Date],[Ref_Recipe_ID],[Ref_User_ID]) VALUES (@Mess, @CurrDate,@CurrRID,@CurrRUID)

SELECT [Message],[Date],[Login] FROM Reference_1,User_1,Recipe

WHERE (Ref_User_ID = [User_ID]) AND (Ref_Recipe_ID = Recipe_ID)

AND (Recipe_ID = @CurrID)

END

 

4. Процедура подбора блюда по введенным ингредиентам

CREATE PROCEDURE ViewIngridientRecipes

@p1 char(20),

@p2 char(20),

@p3 char(20)

AS

BEGIN

 

SELECT Recipe_name FROM Composition_11,Ingridient,Recipe

WHERE (Comp_Ingridient_ID = Ingridient_ID) AND

(Ingridient_name = @p1 OR Ingridient_name = @p2 OR Ingridient_name = @p3)

AND (Recipe_ID = Comp_recipe_ID)

GROUP BY Recipe_name

END

 

5. Процедура вывода ТОП 10 активнейших пользователей за введенные интервалы времени

ALTER PROCEDURE ViewRecipeActivity

 

@par1 char(10),

@par2 char(10)

 

AS

-- ТОП 10 САМЫХ популярных  блю за определенный промежуток  времени

BEGIN

SELECT TOP (10) [Recipe_name],COUNT([Reference_ID]) AS Popularity FROM Reference_1,Recipe

WHERE ([Date] BETWEEN @par1 AND @par2) AND (Ref_Recipe_ID = Recipe_ID)

GROUP BY [Recipe_name]

ORDER BY Popularity DESC

 

6. Тригер на проверку ввода одинакового логина при регистрации, 
   CREATE TRIGGER Check_LOG

on User_1 for insert

as

begin

if exists(Select * from User_1,inserted where User_1.[User_ID]<>inserted.[User_ID]

and User_1.[Login]=inserted.[Login])

begin

print 'ЛОГИН уже существует'

rollback

end

end

7. Триггер на вставку в таблицу Reference_1. Запрещается вставка даты больше чем текущая.

CREATE TRIGGER Check_referenseDate

ON Reference_1 FOR INSERT,UPDATE

AS

BEGIN

Declare @din date

set @din=(SELECT CONVERT (date,GETDATE()))   

IF EXISTS (Select [Date] from Reference_1  where [Date]> @din)

begin

PRINT 'Дата отзыва  не может быть больше текущей  даты'

Rollback

end

     END

8. Тригер на проверку ввода одинакового рецепта

CREATE TRIGGER CHECK_RECIPE_NAME

ON Recipe FOR INSERT

AS

BEGIN

IF EXISTS(SELECT * FROM Recipe,INSERTED WHERE Recipe.[Recipe_ID]<>INSERTED.[Recipe_ID]

AND Recipe.Recipe_name=INSERTED.Recipe_name AND Recipe.Rec_Cuisine_ID = INSERTED.Rec_Cuisine_ID

AND Recipe.Rec_Category_ID = INSERTED.Rec_Category_ID AND Recipe.Rec_Book_ID = INSERTED.Rec_Book_ID

AND Recipe.Rec_Basis_ID = inserted.Rec_Basis_ID)

BEGIN

PRINT 'Такой рецепт уже  существует'

ROLLBACK

END

END 

9. Процедура вывода ТОП 10 популярных рецептов за введенные интервалы времени

ALTER PROCEDURE ViewRecipeActivity

 

@par1 char(10),

@par2 char(10)

 

AS

BEGIN

SELECT TOP (10) [Recipe_name],COUNT([Reference_ID]) AS Popularity FROM Reference_1,Recipe

WHERE ([Date] BETWEEN @par1 AND @par2) AND (Ref_Recipe_ID = Recipe_ID)

GROUP BY [Recipe_name]

ORDER BY Popularity DESC

 

10. Процедура фильтрации рецепта по калорийности

CREATE PROCEDURE CalorFilter_1

@a1 int

--@a2 int

--@typeproc int

AS

BEGIN

 

SELECT Recipe_ID,Recipe_name,Cuisine_name,Category_name,Tittle,[Author],Caloric_content,Dish_weight,[Login]

    FROM Recipe,Cuisine,Category,Book,User_1

WHERE (Rec_Cuisine_ID = Cuisine_ID) AND (Rec_Category_ID = Category_ID)

AND (Rec_Book_ID = Book_ID) AND (Rec_User_ID = [User_ID])

AND (Caloric_content > @a1)

 

END

 

11. Процедура вывода всех отзывов для конкретного рецепта

CREATE PROCEDURE [dbo].[Otzivi]

 

@par int

 

AS

BEGIN

 

SELECT [Message],[Date],[Login] FROM Reference_1,User_1,Recipe

WHERE (Ref_User_ID = [User_ID])

AND (Ref_Recipe_ID = Recipe_ID)

AND (Recipe_ID = @par)

END

 

 

 

5 ВЫБОР И ОБОСНОВАНИЕ СРЕДСТВ 
РАЗРАБОТКИ ПРИЛОЖЕНИЯ

 

Для реализации поставленной задачи была выбрана СУБД MS SQL Server 2012. Данная СУБД является одной из популярных систем управления баз данных.  В ней применяются новейшие разработки и последние достижения в области  проектирования, построения и обслуживания баз данных. Основной используемый язык запросов — Transact-SQL, создан совместно Microsoft и Sybase. Transact-SQL является реализацией стандарта ANSI/ISO по структурированному языку запросов (SQL) с расширениями. Используется для работы с базами данных размером от персональных до крупных баз данных масштаба предприятия; конкурирует с другими СУБД в этом сегменте рынка.

Данная СУБД полностью реализовывает все аспекты реляционной модели данных и позволяет нам с помощью встроенных функций поддерживать ссылочную целостность данных. Так же MS SQL Server стал де факто стандартом источника данных для всех решений Microsoft, что облегчает его интеграцию с различными программными пакетами.

Для создания приложения используется среда разработки Microsoft Visual Studio 2012, а языком написания приложения был выбран – C#. Был выбран именно этот язык по следующим причинам:

• Претендует на подлинную объектную ориентированность (а всякая языковая сущность претендует на то, чтобы быть объектом);
• Призван практически реализовать компонентно-ориентированный подход к программированию, который способствует меньшей машинно-архитектурной зависимости результирующего программного кода, большей гибкости, переносимости и легкости повторного использования как программ целиком так и ее фрагментов;
• Изначальная ориентация на безопасность кода (что особенно заметно в сравнении с языками C и C++);

• Расширенная поддержка событийно-ориентированного программирования;
• Идеальная совместимости с базами данных на SQL Server 2012.

 

 

 

 

 

 

6  РЕАЛИЗАЦИЯ ЗАКОНЧЕННОГО ПРИЛОЖЕНИЯ, РАБОТАЮЩЕГО С СОЗДАННОЙ БАЗОЙ ДАННЫХ

 

6.1 Разработка и построение  интерфейса главной и рабочей  форм

Главная форма приложения является объектом класса Form, наследуемый от класса Windows.Forms, определенного в .NET Framework. Создание всех компонентов формы, в частности главного меню, управляющих элементов, окон сообщений, диалогов настроек и выбора файлов, панели с закладками и др., происходит в методе по мере их выpова, соответствующими им конструкторами.

Все основные таблицы для представления данных были выполнены в виде DataGrid, что упрощает понимание и просмотр информации, т.к. она представляется в табличном виде.

Все основные формы и виды приложения выполнены в одном окне на основе страничного интерфейса. Дочерние (вспомогательные) формы выполнены в виде диалоговых окон. Основной упор при проектировании интерфейса приложения был сделан на привлекательность и понятность для конечного пользователя.

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

 

 

 

6.2 Построение главного меню и кнопок панели инструментов

В данном приложении существует 2 роли: администратор, и пользователь. Для удобства, все манипуляции с базой буду осуществляться на одной форме, так мы избежим нагроможденности окон. После запуска программы все типы пользователей видят поля для ввода ip адреса сервера на котором находится база (по умолчанию выставляется local) и поле для ввода каталога инициализации (по умолчанию Kulinarnaya_kniga). После нажатия кнопки «Открыть книгу» пользователей перекидывает на форму авторизации. После ввода данных у нас отображается главное окно, в котором показываются все рецепти и средства необходимые для работы с книгой.

Для пользователя доступны следующие кнопки: «Добавить рецепт» , «Подробнее о рецепте», «Подбор рецепта по ингридиентам», «Топ 10 активных пользователей», «Топ 20 популярных рецептов». Нажав на кнопку  «Подробнее о рецепте» у пользователя показывается подробная информация о рецепте: Ингридиенты, способ приготовления, отзывы, состав. Нажав на кнопку «Подбор рецепта по ингридиентам» пользователь должен будет в новом окне ввести ингридиенты и по этим ингредиентам ему выведутся те рецепты в которых присутсвуют данные ингридиенты.  Так же пользователь может отфильтровать рецепты по любой категории, кухне, калорийности и подобрать понравившийся.

Для администратора доступны следующие кнопки: «Добавить рецепт» , «Удалить рецепт», «Редактировать рецепт». Нажав на любую из кнопок администратору выведется новое окно в котором он сможет добавить, удалить или отредактировать рецепт.

Просмотреть интерфейс можно в Приложении С.

 

6.3 Выполнение программного кода в среде Microsoft Visual C#

Опишем работу приложения с базой данных. Подключение к базе данных осуществляется следующим образом:

Листинг 6.3.1 Подключение к серверу

homeconnect = new SqlConnection(@"Data Source=" + textBox1.Text + ";Initial Catalog=" + textBox2.Text + ";Integrated Security=True;"); if (cn.State == ConnectionState.Closed { homeconnect.Open(); }

 

После подключения для осуществления запросов необходимо проверить достоверность базы данных, если проверка проходит успешно, то будут разблокированы возможности по работе с базой. Пример вызова процедуры добавления С# отображен в листинге 6.3.2.

Листинг 6.3.2 Пример вызова процедуры SQL

frm1.homecommand = frm1.homeconnect.CreateCommand(); frm1.homecommand.CommandType = CommandType.Text; frm1.homecommand.CommandText = "EXEC AddRecipe " + textBox4.Text + ", " + richTextBox1.Text + ", " + typein_4 + ", " + typein_3 + ", " + typein_2 + ", 1," + textBox1.Text + ", " + textBox5.Text; frm1.homeconnect.Open(); frm1.homedatareader = frm1.homecommand.ExecuteReader(); ReloadMainPage();

 

Функция ReloadMainPage() из вышеприведенного кода – обновляет данные в  dataGridView и выводит данные на форму. Код функции отображен в листинге 6.3.3.

Листинг 6.3.3 Вывод результата запроса в datagridView

SqlDataAdapter sqlda = new SqlDataAdapter(); DataTable DT = new DataTable(); DataSet DS = new DataSet(); frm1.homecommand = frm1.homeconnect.CreateCommand(); frm1.homecommand.CommandType = CommandType.Text; frm1.homecommand.CommandText = "EXEC ViewMainPage"; frm1.homeconnect.Open(); sqlda.SelectCommand = frm1.homecommand; sqlda.Fill(DS); DT = DS.Tables[0]; dataGridView1.DataSource = DT; GridOptions(); frm1.homeconnect.Close();