Introduction
In this article, I explained about databases, tables and stored procedures which I implemented in this food court project. Let's see the code snippet of how I created a database, tables and stored procedures as per the requirements for the food court project.
Before continuing this article, I strongly recommend reading my previous article i.e Overview of Food Court Project
Create FoodCourt Database:
Create Database FoodCourtDB The above single line code is used to create database i.e FoodCourtDB
Food Item Type Table:
This table contains only two columns i.e fooditemtypeno is a primary key and auto-generated column and another column is fooditemtype is used to store only string data or food item type data i.e Cake | Fruits | Puff | Pizza | Cool Drinks | Burger | Snacks | Biscuts.
Correction: fooditemtypeno column data type is int, not varchar(20). Please noted this change
Create Table Fooditemtypemaster
(
fooditemtypeno int primary key identity(1,1),
fooditemtype varchar(30)
)
Insert Food Item Type Stored Procedure
A stored procedure, which is used to insert new food item types into database tables i.e footitemtypemaster table. The store procedure accepts only food item names only because the food item no is auto-generated, so it's not required to pass any fooditemno values.
USE [FoodCourtDB]
GO
CREATE PROCEDURE [dbo].[Insert_FoodItemTypeMaster](
@fitemname varchar(30))
AS BEGIN
Insert into fooditemtypemaster(fooditemtype) values(@fitemname)
End
Retrieve Food Item Type Stored Procedure
To fetch all the records from the fooditemtypemaster database table. I created a stored procedure which helps us to fetch records from this table i.e proc_getFoodItemType.
USE [FoodCourtDB]
GO
CREATE PROCEDURE [dbo].[proc_getFoodItemType]
AS BEGIN
Select * from fooditemtypemaster
END
Food Items Table:
The food items table is used to store lists of food items based on food item types. Let's we understand through code snippet
Correction: fooditemno and fooditemtypeno column data type is int, not varchar(10) and varchar(20). Please noted this change
Create Table fooditems
(
fooditemno int primary key identity(1,1),
fooditemname varchar(30),
foodquantity int,
foodprice money,
fooditemtypeno int references Fooditemtypemaster(fooditemtypeno)
)
The fooditemtypeno column is a foreign key which represents the list of food item
Inserting Food Item Names Stored Procedure
Adding new food items into fooditems table using proc_insertFoodItem stored procedure. To add food items not required to pass fooditemno, because it is an auto-generated column but we need to pass the remaining column values i.e fooditemname, foodquantity, foodprice and fooditemtypeno .
USE [FoodCourtDB]
GO
create procedure [dbo].[proc_InsertFoodItem](
@itemname varchar(30),
@itemqty int,
@itemprice money,
@fitemtypeno int)
as begin
Insert Into fooditems(fooditemname, foodquantity, foodprice, fooditemtypeno) values(@itemno,@itemname,@itemqty, @itemprice,@fitemtypeno)
end
Retrieve Food Item Names Stored Procedure
To retrieve a list of food item's names based on a specific condition, the condition is completely based on the food item type.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_getFoodItemNames](
@FoodItemTypeId int)
as begin
Select * from fooditems where fitemtypeno=@FoodItemTypeId
end
Retrieve Quantity and Price from Food Item Stored Procedure
The stored procedure is used to retrieve particular columns i.e Quantity and Price from fooditems table based on a condition. The conditions we can apply to fooditemno column because the list of food items has a unique ID, so if you want to retrieve particular food item details then we have to pass foot item id.
USE [FoodCourtDB]
GO
CREATE PROCEDURE [dbo].[proc_getFoodItem_Qty_Price](@itemno int)
AS BEGIN
Select fquantity, fprice from fooditems where fitemno=@itemno
END
Food Item Employee Table:
The admin can add newly joined employee details in these tables. The employee ID is not customized it is an auto-generated ID and not editable, rest of the fields are editable. When the admin creates employee records he will create temporary passwords for employees, After an employee login for the first time he'll change the password at the moment.
Create Table employee
(
empid int primary key identity(1,1),
empname nvarchar(50),
emppwd nvarchar(50),
empaddress nvarchar(200),
empphone nvarchar(50)
)
Correction: empid column data type is int, not nvarchar(50). Please noted this change
StoredProcedure: Insert Employee Details
this stored procedure is used to insert newly joined employee record and is not required to pass empid because it is an auto-generated column.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_InsertEmployee](
@ename varchar(50),
@password varchar(50),
@address varchar(50),
@phone varchar(15))
as begin
Insert into employee(empname, emppwd, empaddress, empphone) values(@ename,@password, @address,@phone)
end
StoredProcedure: Retrieving Employee Details
to get all the records from the employee table without any condition then this stored procedure fetches all the records from the database employee table.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_getEmployee]
as begin
Select empid, empname, empaddress, empphone from employee
end
StoredProcedure: Verifying Employee Details
The newly joined employee tries to login then their credential should be verified, so this stored procedure is used to verify those credentials from the database employee table.
If those credentials are valid it will return complete employee records or return null.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_VerifyEmployee](
@empid int,
@pwd varchar(50))
as begin
Select * from employee where empid=@empid and emppassword=@pwd
end
StoredProcedure: Update Employee Details
this stored procedure is used to update employee details if any modification occurs like an employee changed phone number or address then this update will records through stored procedure.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_UpdateEmployee](
@empid int,
@empname nvarchar(50),
@empaddress nvarchar(200),
@empphone nvarchar(50))
as begin
Update employee set empname=@empname, empaddress=@empaddress, empphone=@empphone where empid=@empid
end
StoredProcedure: Respet Employee Password
If in case, the employee forgets his credentials then the admin will reset the employee's password to the default password. Again employee has to login and he has to change to the new password. This stored procedure is used for this scenario.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_RestPassword](
@empid int)
as begin
Update employee set emppwd='12345' where empid=@empid
end
To get more information regarding blood donar project source code like Business Object, Database & Stored Procedure, Database Logic and User Interface.
0 Comments