Advanced Databases
MSc Information Systems with Computing
System:
Online savour food point of sale
Scope:
Scope of the implemented database is
Business Requirements:
1. Stock management:
(i) Stock of ingredients in shop
Stored Procedures: code
ALTER PROCEDURE [dbo].[Sp_Ingradient]
@Action VARCHAR(10)
,@IngradientID INT = NULL
,@IngradientName VARCHAR(100) = NULL
,@IngradientPrice INT = NULL
,@QuntityInStock nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
begin
SELECT [IngradientID]
,[IngradientName],IngradientPrice,QuntityInStock FROM [dbo].[IngradientsList]end
END
Result:
(ii) add new stock:
Stored Procedures: code
ALTER PROCEDURE [dbo].[Sp_Ingradient]
@Action VARCHAR(10)
,@IngradientID INT = NULL
,@IngradientName VARCHAR(100) = NULL
,@IngradientPrice INT = NULL
,@QuntityInStock nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
IF @Action = 'Add'
BEGIN
declare @CurrentQty AS INT;
SELECT @CurrentQty = QuntityInStock
FROM IngradientsList
WHERE IngradientID =@IngradientID
UPDATE IngradientsList
SET QuntityInStock = (@CurrentQty + @QuntityInStock)
WHERE IngradientID = @IngradientID
end
END
Result:
Add new ingredient type:
Stored Procedures: code
ALTER PROCEDURE [dbo].[Sp_Ingradient]
@Action VARCHAR(10)
,@IngradientID INT = NULL
,@IngradientName VARCHAR(100) = NULL
,@IngradientPrice INT = NULL
,@QuntityInStock nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO IngradientsList(IngradientName,IngradientPrice,QuntityInStock)
VALUES (@IngradientName,@IngradientPrice,@QuntityInStock)
END
END
2: Expense Management:
Stored Procedures: code
ALTER PROCEDURE [dbo].[Sp_Expenses]
@Action VARCHAR(10)
,@ExpensesID INT = NULL
,@ExpensesName VARCHAR(50) = NULL
,@ExpensesDescription VARCHAR(100) = NULL
,@ExpensesPrice INT = NULL
,@ExpensesDate nvarchar(10) = NULL
,@ExpensesType VARCHAR(10) = NULL
AS
BEGIN
SET NOCOUNT ON;
IF @Action = 'SELECT2'
begin
SELECT [ExpensesID]
,[ExpensesName]
FROM [dbo].[ExpensesBeryani]
end
END
Stored Procedures: code
ALTER PROCEDURE [dbo].[Sp_Expenses]
@Action VARCHAR(10)
,@ExpensesID INT = NULL
,@ExpensesName VARCHAR(50) = NULL
,@ExpensesDescription VARCHAR(100) = NULL
,@ExpensesPrice INT = NULL
,@ExpensesDate nvarchar(10) = NULL
,@ExpensesType VARCHAR(10) = NULL
AS
BEGIN
SET NOCOUNT ON;
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO ExpensesBeryani(ExpensesName,ExpensesDescription, ExpensesDate,ExpensesType, ExpensesPrice)
VALUES (@ExpensesName,@ExpensesDescription ,@ExpensesDate,@ExpensesType,@ExpensesPrice)
END
END
3: Pot Management:
Balance Management:
Report:
A list of business rules for the system (at least 4 must be implemented using SQL code instead of showing at design stage). The implementation must be shown by the screenshots in the report.
Business Rules:
List of business rules is:
Completed Database System:
Relational diagram with 3f normalization:
Five test table record every datable:
Referential Integrity Constraints:
Referential integrity used between tables:
Why:
Use for get dish name and pictures in pot by primary key and foreign key Referential integrity
Why:
Use for stock and ingredients management’s removes duplication
In stock table its primary key and foreign key Referential integrity.
Primary key of ingredients used as foreign key in stock table
Why:
Add ingredients in dish with many to many Referential integrity
And a bright table that store dish id and ingredient Id,
Use for remove duplication and dish name and ingredient name fetching in different queries,
Why:
Use for numbers ingredients in different kg pots.
why:
use to store only unique emails of admin and users,
Why:
Use Primary key unique data in table
Why:
Use client need receipt for every invoice
Relational Schema:
UML notation:
Store table with Xml datatype:
Justify the use of XML data type that makes it a hybrid database.
Report must be well-structured with Conclusions and Bibliography. Screenshots and explanation must be provided for all requirements and business rules:
Bibliography:
NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence
Book by Martin Fowler and Pramod J. Sadalage
Learning SQL
Book by Alan Beaulie
Conclusions:
This advance database system is used for the management of balance (finance) and builds a report of everything recording all the information of sale, purchase, opening amount, closing amount, profit or loss covering several business requirements and creating a report to keep check and balance of everything. Keeping in mind the business rules this database can help solve many problems closing several loop holes that were causing trouble.
Select Dg.potId,Dg.Capacity,Dg.potDate,Dg.NumberOfSinglePlate,Dg.NumberOfdoublePlates,
Dg.CostOfSinglePlate,Dg.CostOfdoublePlate1, dis.DishName,Dg.CostOfpot,dis.DishImage
FROM pot as Dg
inner JOIN DishsList as dis ON Dg.DishID =dis.DishID
where
(Dg.potStatus =1)
order by Dg.potDate
SELECT COUNT (DISTINCT [potId])as TotalInpot
FROM pot where potStatus =1
Code:
CREATE TABLE Stores
(
StoreID INT PRIMARY KEY,
Survey_untyped XML,
Survey_typed XML(Sales.StoreSurveySchemaCollection)
);
INSERT INTO Stores
VALUES
(
'<StoreSurvey>
<AnnualSales>800000</AnnualSales>
<AnnualRevenue>80000</AnnualRevenue>
<BankName>United Security</BankName>
<BusinessType>food</BusinessType>
<YearOpened>2018</YearOpened>
<Specialty>rice food</Specialty>
<Brands>8</Brands>
<Internet>ISDN</Internet>
<NumberEmployees>13</NumberEmployees>
<Products Type="rice">
<Product>chicken rice</Product>
<Product>simple rice</Product>
<Product>special rice</Product>
</Products>
<Products Type="ingredient">
<Product>green chili</Product>
<Product>red chili</Product>
<Product>salt</Product>
</Products>
)
Retrieving data logically from a field with XML data type as well as data from fields from other data types.
Code:
SELECT
Survey_untyped.query('/StoreSurvey')
AS Info_untyped
FROM
Stores;
Code:
UPDATE Stores
SET Survey_untyped.modify('delete(/StoreSurvey/Comments)[1]')
WHERE StoreID = 1;
Code:
SELECT
Survey_untyped.query('/StoreSurvey/Products[@Type="ingredient"]')
AS ingredients
FROM
Stores
WHERE
Survey_untyped.exist('/StoreSurvey[BusinessType=" food "]') = 1;
Two triggers to demonstrate the implementation of business rules:
For insert on cutomers:
Code:
For employee delete:
Two Views to demonstrate the development of virtual tables. How Views would be helpful to generate customised View of your data?
View 1:
For Pot record:
Code:
/****** Object: View [dbo].[gatpotbydishid] Script Date: 14/11/2018 1:55:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[gatpotbydishid]
as
Select Dg.potId,Dg.Capacity,Dg.potDate,Dg.NumberOfSinglePlate,Dg.NumberOfdoublePlates,
Dg.CostOfSinglePlate,Dg.CostOfdoublePlate1, dis.DishName,Dg.CostOfpot,dis.DishImage
FROM pot as Dg
inner JOIN DishsList as dis ON Dg.DishID =dis.DishID
where
(Dg.potStatus =1)
GO
select * from [gatpotbydishid]
View 2:
For Pot by Dish Id record:
Code:
/****** Object: View [dbo].[gatpot] Script Date: 14/11/2018 1:55:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[gatpot]
as
Select Ing.IngradientName,tbdis.Quntity,dgsi.SinglePlate,dgsi.DoublePalte,Ing.IngradientPrice,Ing.IngradientID
FROM DishsList as dis
inner JOIN dbo.BridgeTableINDish as tbdis ON dis.DishID = tbdis.DishID
inner JOIN IngradientsList as Ing ON tbdis.IngradentID =Ing.IngradientID
inner JOIN dbo.potsize as dgsi ON tbdis.potsizeid = dgsi.potsizeid
GO
select * from .[gatpot]
Benefits of views using:
View are used is nothing more than a saved SQL query. A view can also be considered as a virtual table. Its make performance of database is fast and smothers, accurate to application, view are used store data for temporary basis on database server these helpful for get fast data retrieve on application.
Innovation:
Use of any other features to enhance the usability of your database system:
Other features to enhancement are following:
1: use limit row selection in select query
2: use indexing in table for make fast
3: use minimum aggregate function because these need extra time on table
4: sure transaction no makes dead lock
5: make table resource in multiply uses
6: used trigger for maintain database changing
7: create a job for beck up data for any miss happening
8: use always parameters queries
For report on invoice on system:
create PROCEDURE [dbo].[Sp_InvoiceSelect]
@Action VARCHAR(10)
,@InvoiceId INT = NULL
,@DishName NCHAR(100) = NULL
,@PlatePrice INT = NULL
,@TotalPrice INT = NULL
,@InvoiceDate date =null
,@potId INT = NULL
,@fromTotal INT= null,
@toTotal Int = null
,@fromDate nvarchar (50) = null,
@toDate nvarchar (50) = null
,@fromPrice nvarchar (50) = null,
@toPrice nvarchar (50) = null
,@fromQuntity nvarchar (50) = null
,@toQuntity nvarchar (50) = null
AS
BEGIN
SET NOCOUNT ON;
IF (@Action='All')
begin
Select InvoiceId, DishName,PlatePrice,NumberOfPlate,TotalPrice,InvoiceDate
FROM Invoice
order by InvoiceDate DESC
end
if (@Action='Ind')
begin
Select InvoiceId,DishName,PlatePrice,NumberOfPlate,TotalPrice,InvoiceDate
FROM Invoice
where
(DishName = @DishName OR @DishName IS NULL)AND
(PlatePrice between @fromPrice and @toPrice OR (@fromPrice IS NULL and @toPrice IS NULL)) And
(NumberOfPlate between @fromQuntity and @toQuntity OR (@fromQuntity IS NULL and @toQuntity IS NULL)) And
(TotalPrice between @fromTotal and @toTotal OR (@fromTotal IS NULL and @toTotal IS NULL)) And
(InvoiceDate between @fromDate and @toDate OR (@fromDate IS NULL and @toDate IS NULL))
ORDER BY InvoiceDate DESC
end
end
Urgenthomework helped me with finance homework problems and taught math portion of my course as well. Initially, I used a tutor that taught me math course I felt that as if I was not getting the help I needed. With the help of Urgenthomework, I got precisely where I was weak: Sheryl. Read More
Follow Us