Dj209RB5

1USE [MyMenu];
2GO
3IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Menus') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
4CREATE TABLE Menus(
5	id          INT            NOT NULL PRIMARY KEY,
6	name        NVARCHAR(300)  NOT NULL,
7	description NVARCHAR(3000) NOT NULL
8);
9GO
10
11
12IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
13CREATE TABLE Users(
14    id            INT          NOT NULL PRIMARY KEY,
15    menu_id       INT,
16    name          NVARCHAR(80) NOT NULL,
17    weight        INT          NOT NULL,
18    height        INT          NOT NULL,
19    proteins      FLOAT        NOT NULL DEFAULT 0.0,
20    fats          FLOAT        NOT NULL DEFAULT 0.0,
21    carbohydrates FLOAT        NOT NULL DEFAULT 0.0,
22    calories      FLOAT        NOT NULL DEFAULT 0.0,
23    
24    CONSTRAINT User_Menu_FK FOREIGN KEY (menu_id) REFERENCES Menus(id)
25);
26GO
27IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Dishes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
28CREATE TABLE Dishes(
29	id            INT            NOT NULL PRIMARY KEY,
30	name          NVARCHAR(300)  NOT NULL,
31	description   NVARCHAR(3000) NOT NULL,
32	instruction   NVARCHAR(3000) NOT NULL,
33	complexity    VARCHAR(80)    NOT NULL CHECK (complexity in ('easy', 'medium', 'hard')),
34	cooking_time  INT            NOT NULL
35);
36GO
37
38IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Ingridients') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
39CREATE TABLE Ingredients(
40	id            INT           NOT NULL PRIMARY KEY,
41	name          NVARCHAR(300) NOT NULL,
42	proteins      FLOAT         NOT NULL,
43    fats          FLOAT         NOT NULL,
44    carbohydrates FLOAT         NOT NULL,
45    calories      FLOAT         NOT NULL
46);
47
48IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'MenuReviews') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
49CREATE TABLE MenuReviews(
50	user_id     INT             NOT NULL,
51	menu_id     INT             NOT NULL,
52	score       INT             NOT NULL CHECK (score in (1, 2, 3, 4, 5)),
53	header      NVARCHAR(100),
54	review_text NVARCHAR(3000),
55	
56	CONSTRAINT MenuReview_User_FK FOREIGN KEY (user_id) REFERENCES Users(id),
57	CONSTRAINT MenuReview_Menu_FK FOREIGN KEY (menu_id) REFERENCES Menus(id),
58	PRIMARY KEY (user_id, menu_id)
59);
60
61IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'DishReviews') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
62CREATE TABLE DishReviews(
63	user_id     INT             NOT NULL,
64	dish_id     INT             NOT NULL,
65	score       INT             NOT NULL CHECK (score in (1, 2, 3, 4, 5)),
66	header      NVARCHAR(100),
67	review_text NVARCHAR(3000),
68	
69	CONSTRAINT DishReview_User_FK FOREIGN KEY (user_id) REFERENCES Users(id),
70	CONSTRAINT MenuReview_Dish_FK FOREIGN KEY (dish_id) REFERENCES Dishes(id),
71	PRIMARY KEY (user_id, dish_id)
72);
73
74IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'FavouriteDishes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
75CREATE TABLE FavouriteDishes(
76	user_id           INT NOT NULL,
77	dish_id           INT NOT NULL,
78	
79	CONSTRAINT FavouriteDish_User_FK FOREIGN KEY (user_id) REFERENCES Users(id),
80	CONSTRAINT FavouriteDish_Dish_FK FOREIGN KEY (dish_id) REFERENCES Dishes(id),
81	PRIMARY KEY (user_id, dish_id)
82);
83
84IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'FavouriteMenus') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
85CREATE TABLE FavouriteMenus(
86	user_id           INT NOT NULL,
87	menu_id           INT NOT NULL,
88	
89	CONSTRAINT FavouriteMenu_User_FK FOREIGN KEY (user_id) REFERENCES Users(id),
90	CONSTRAINT FavouriteMenu_Menu_FK FOREIGN KEY (menu_id) REFERENCES Menus(id),
91	PRIMARY KEY (user_id, menu_id)
92);
93
94IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Meals') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
95CREATE TABLE Meals(
96	dish_id INT          NOT NULL,
97	menu_id INT          NOT NULL,
98	meal    NVARCHAR(30) NOT NULL CHECK (meal in (N'завтрак', N'перекус', N'обед', N'полдник', N'первый ужин', N'второй ужин')),
99	
100	CONSTRAINT Meal_Dish_FK FOREIGN KEY (dish_id) REFERENCES Dishes(id),
101	CONSTRAINT Meal_Menu_FK FOREIGN KEY (menu_id) REFERENCES Menus(id),
102	PRIMARY KEY (dish_id, menu_id)
103);
104
105IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'Dish_Ingridient') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
106CREATE TABLE Dish_Ingridient(
107	dish_id       INT NOT NULL,
108	ingridient_id INT NOT NULL,
109	grams         INT NOT NULL,
110	importance    INT NOT NULL CHECK (importance in (1, 2, 3)),
111	
112	CONSTRAINT Dish_Ingridient_Dish_FK FOREIGN KEY (dish_id) REFERENCES Dishes(id),
113	CONSTRAINT Dish_Ingridient_Ingridient_FK FOREIGN KEY (ingridient_id) REFERENCES Ingridients(id),
114	PRIMARY KEY (dish_id, ingridient_id)
115);