bc6bMMZC

1drop procedure if exists insertrole;
2delimiter $$
3create procedure insertrole(p_RoleId int ,p_RoleName varchar(50),p_RoleDescription varchar(50))
4begin 
5insert into role values (p_RoleId,p_RoleName,p_RoleDescription );
6end $$
7delimiter ;
8
9
10drop procedure if exists insertuser;
11delimiter $$
12create procedure insertuser(p_UserId int ,p_UserName varchar(45),p_Password varchar(45),p_EmailId varchar(45),p_RoleId int )
13begin 
14insert into user values (p_UserId ,p_UserName ,p_Password ,p_EmailId ,p_RoleId);
15end $$
16delimiter ;
17
18
19drop procedure if exists insertpayment;
20delimiter $$
21create procedure insertpayment(p_PaymentId int ,p_PaymentType varchar(45),p_PaymentAmount int,p_UserId int )
22begin 
23insert into payment values (p_PaymentId,p_PaymentType,p_PaymentAmount,p_UserId );
24end $$
25delimiter ;
26
27drop procedure if exists insertinvoice;
28delimiter $$
29create procedure insertinvoice(p_InvoiceId int ,p_UserId int)
30begin 
31insert into invoice values (p_InvoiceId int ,p_UserId int);
32end $$
33delimiter ;
34
35drop procedure if exists insertcontactus;
36delimiter $$
37create procedure insertcontactus(p_ContactId int ,p_MessageEnquiry varchar(300),p_UserId int)
38begin 
39insert into contactus values (p_ContactId ,p_MessageEnquiry ,p_UserId);
40end $$
41delimiter ;
42
43drop procedure if exists insertpackages;
44delimiter $$
45create procedure insertpackages(p_PackageId int ,p_PackageName varchar(45),p_PackageImage varchar(300),p_PackageDescription varchar(500),p_CategoryIdint,p_Days int,p_Price int)
46begin 
47insert into packages values (p_PackageId,p_PackageName,p_PackageImage,p_PackageDescription,p_CategoryId,p_Days,p_Price);
48end $$
49delimiter ;
50
51drop procedure if exists insertindiantour;
52delimiter $$
53create procedure insertindiantour(p_IndiaId int ,p_IndiaName varchar(45),p_IndiaDescription varchar(500),p_CategoryId int,)
54begin 
55insert into indiantour values (p_IndiaId ,p_IndiaName,p_IndiaDescription,p_CategoryId);
56end $$
57delimiter ;
58
59drop procedure if exists insertworldtour;
60delimiter $$
61create procedure insertworldtour(p_WorId int ,p_WorIdName varchar(45),p_WorIdDescription varchar(500),p_CategoryId int,)
62begin 
63insert into indiantour values (p_WorIdId ,p_WorIdName,p_WorIdDescription,p_CategoryId);
64end $$
65delimiter ;
66
67
68
69CREATE TABLE `packages` (
70  `PackageId` int(11) NOT NULL,
71  `PackageName` varchar(50) DEFAULT NULL,
72  `PackageImage` blob,
73  `PackageDescription` varchar(50) DEFAULT NULL,
74  `CategoryId` int(11) DEFAULT NULL,
75  `Days` int(11) DEFAULT NULL,
76  `Price` int(11) DEFAULT NULL,
77  PRIMARY KEY (`PackageId`),
78  KEY `CategoryId2_idx` (`CategoryId`),
79  CONSTRAINT `CategoryId2` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION
80  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81CREATE TABLE `indiantour` (
82  `IndiaId` int(11) NOT NULL,
83  `IndiaName` varchar(50) DEFAULT NULL,
84  `IndiaDescription` varchar(50) DEFAULT NULL,
85  `CategoryId` int(11) DEFAULT NULL,
86  `PackageId` int(11) DEFAULT NULL,
87  PRIMARY KEY (`IndiaId`),
88  KEY `CategoryId1_idx` (`CategoryId`),
89  KEY `PackageId1_idx` (`PackageId`),
90  CONSTRAINT `CategoryId1` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
91  CONSTRAINT `PackageId1` FOREIGN KEY (`PackageId`) REFERENCES `packages` (`PackageId`) ON DELETE NO ACTION ON UPDATE NO ACTION
92) ENGINE=InnoDB DEFAULT CHARSET=utf8;
93CREATE TABLE `worldtour` (
94  `WorldId` int(11) NOT NULL,
95  `WorldName` varchar(50) DEFAULT NULL,
96  `WorldDescription` varchar(50) DEFAULT NULL,
97  `CategoryId` int(11) DEFAULT NULL,
98  `PackageId` int(11) DEFAULT NULL,
99  PRIMARY KEY (`WorldId`),
100  KEY `CategoryId_idx` (`CategoryId`),
101  KEY `PackageId_idx` (`PackageId`),
102  CONSTRAINT `CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
103  CONSTRAINT `PackageId` FOREIGN KEY (`PackageId`) REFERENCES `packages` (`PackageId`) ON DELETE NO ACTION ON UPDATE NO ACTION
104) ENGINE=InnoDB DEFAULT CHARSET=utf8;
105
106
107 drop procedure if exists selectrole ;
108delimiter $
109create procedure selectrole()
110begin
111select * from role;
112end $
113delimiter ;
114
115
116 drop procedure if exists selectuser ;
117delimiter $
118create procedure selectuser()
119begin
120select * from user;
121end $
122delimiter ;
123
124
125drop procedure if exists selectpayment ;
126delimiter $
127create procedure selectpayment()
128begin
129select * from payment;
130end $
131delimiter ;
132
133drop procedure if exists selectinvoice ;
134delimiter $
135create procedure selectinvoice()
136begin
137select * from invoice;
138end $
139delimiter ;
140
141drop procedure if exists selectcontactus ;
142delimiter $
143create procedure selectcontactus()
144begin
145select * from contactus;
146end $
147delimiter ;
148
149 drop procedure if exists selectcategories ;
150delimiter $
151create procedure selectcategories()
152begin
153select * from categories;
154end $
155delimiter ;
156
157  drop procedure if exists selectpackages;
158delimiter $
159create procedure selectpackages()
160begin
161select * from packages;
162end $
163delimiter ;
164
165
166
167
168
169drop procedure if exists selectindiantour ;
170delimiter $
171create procedure selectindiantour()
172begin
173select * from indiantour;
174end $
175delimiter ;
176
177drop procedure if exists selectworldtour ;
178delimiter $
179create procedure selectworldtour()
180begin
181select * from worldtour;
182end $
183delimiter ;
184
185
186CREATE TABLE `role` (
187  `RoleId` int(11) NOT NULL,
188  `RoleName` varchar(50) DEFAULT NULL,
189  `RoleDescription` varchar(50) DEFAULT NULL,
190  PRIMARY KEY (`RoleId`)
191) ENGINE=InnoDB DEFAULT CHARSET=utf8;
192CREATE TABLE `user` (
193  `UserId` int(11) NOT NULL,
194  `UserName` varchar(50) DEFAULT NULL,
195  `Password` varchar(50) DEFAULT NULL,
196  `EmailId` varchar(50) DEFAULT NULL,
197  `RoleId` int(11) DEFAULT NULL,
198  PRIMARY KEY (`UserId`),
199  KEY `RoleId_idx` (`RoleId`),
200  CONSTRAINT `RoleId` FOREIGN KEY (`RoleId`) REFERENCES `role` (`RoleId`) ON DELETE NO ACTION ON UPDATE NO ACTION
201) ENGINE=InnoDB DEFAULT CHARSET=utf8;
202CREATE TABLE `payment` (
203  `PaymentId` int(11) NOT NULL,
204  `PaymentType` varchar(50) DEFAULT NULL,
205  `PaymentAmount` int(11) DEFAULT NULL,
206  `UserId` int(11) DEFAULT NULL,
207  PRIMARY KEY (`PaymentId`),
208  KEY `UserId_idx` (`UserId`),
209  CONSTRAINT `UserId` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
210) ENGINE=InnoDB DEFAULT CHARSET=utf8;
211CREATE TABLE `invoice` (
212  `InvoiceId` int(11) NOT NULL,
213  `UserId` int(11) DEFAULT NULL,
214  PRIMARY KEY (`InvoiceId`),
215  KEY `UserId_idx` (`UserId`),
216  CONSTRAINT `UserId1` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
217) ENGINE=InnoDB DEFAULT CHARSET=utf8;
218CREATE TABLE `contactus` (
219  `ContactId` int(11) NOT NULL,
220  `MessageEnquiry` varchar(50) DEFAULT NULL,
221  `UserId` int(11) DEFAULT NULL,
222  PRIMARY KEY (`ContactId`),
223  KEY `UserId2_idx` (`UserId`),
224  CONSTRAINT `UserId2` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
225) ENGINE=InnoDB DEFAULT CHARSET=utf8;
226CREATE TABLE `categories` (
227  `CategoryId` int(11) NOT NULL,
228  `CategoryName` varchar(50) DEFAULT NULL,
229  `CategoryDescription` varchar(50) DEFAULT NULL,
230  PRIMARY KEY (`CategoryId`)
231) ENGINE=InnoDB DEFAULT CHARSET=utf8;
232SELECT * FROM dac16.emp;
233CREATE TABLE `packages` (
234  `PackageId` int(11) NOT NULL,
235  `PackageName` varchar(50) DEFAULT NULL,
236  `PackageImage` blob,
237  `PackageDescription` varchar(50) DEFAULT NULL,
238  `CategoryId` int(11) DEFAULT NULL,
239  `Days` int(11) DEFAULT NULL,
240  `Price` int(11) DEFAULT NULL,
241  PRIMARY KEY (`PackageId`),
242  KEY `CategoryId2_idx` (`CategoryId`),
243  CONSTRAINT `CategoryId2` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION
244  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
245CREATE TABLE `indiantour` (
246  `IndiaId` int(11) NOT NULL,
247  `IndiaName` varchar(50) DEFAULT NULL,
248  `IndiaDescription` varchar(50) DEFAULT NULL,
249  `CategoryId` int(11) DEFAULT NULL,
250  `PackageId` int(11) DEFAULT NULL,
251  PRIMARY KEY (`IndiaId`),
252  KEY `CategoryId1_idx` (`CategoryId`),
253  KEY `PackageId1_idx` (`PackageId`),
254  CONSTRAINT `CategoryId1` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
255  CONSTRAINT `PackageId1` FOREIGN KEY (`PackageId`) REFERENCES `packages` (`PackageId`) ON DELETE NO ACTION ON UPDATE NO ACTION
256) ENGINE=InnoDB DEFAULT CHARSET=utf8;
257CREATE TABLE `worldtour` (
258  `WorldId` int(11) NOT NULL,
259  `WorldName` varchar(50) DEFAULT NULL,
260  `WorldDescription` varchar(50) DEFAULT NULL,
261  `CategoryId` int(11) DEFAULT NULL,
262  `PackageId` int(11) DEFAULT NULL,
263  PRIMARY KEY (`WorldId`),
264  KEY `CategoryId_idx` (`CategoryId`),
265  KEY `PackageId_idx` (`PackageId`),
266  CONSTRAINT `CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`CategoryId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
267  CONSTRAINT `PackageId` FOREIGN KEY (`PackageId`) REFERENCES `packages` (`PackageId`) ON DELETE NO ACTION ON UPDATE NO ACTION
268) ENGINE=InnoDB DEFAULT CHARSET=utf8;