Advanced Databases Sample Assignment
Advanced Databases
MSc Information Systems with Computing
System:
Online savour food point of sale
Scope:
Scope of the implemented database is
- Finance management
- Record management
- Report generation
- Stock managements
- Sale management’s
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:
- Expenses report
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
- Add new expenses:
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:
- Number of Pots on shop in running condition:
- Cost of different kg pots :
- Renmaining plates in Pot
Balance Management:
- Sale
- Receipt
- Payment
Report:
- Sale by
- number of plates
- date
- price
- single/double/large
- Numbers of dishes
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:
- If Pot is empty then make no sale from that Pot.
- If stock is less than 5 mark alert.
- If admin no add new pot on shop then no sale is accour
- Pot sale by single ,medium, large plate type with different price
- Sale man have own account for login
- Every invoice is store in database
Completed Database System:
Relational diagram with 3f normalization:
Five test table record every datable:
Referential Integrity Constraints:
Referential integrity used between tables:
- Pot and Dishlist:
Why:
Use for get dish name and pictures in pot by primary key and foreign key Referential integrity
- Stock and Ingredient:
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
- Dish’s and ingredients:
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,
- Pot Size and Ingredients table:
Why:
Use for numbers ingredients in different kg pots.
- email unique index,
why:
use to store only unique emails of admin and users,
- Client and sale
Why:
Use Primary key unique data in table
- Client and receipt
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.
- Use of JOIN between two or more tables as required
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
- Use of GROUP BY with HAVING
- Use of SQL functions
SELECT COUNT (DISTINCT [potId])as TotalInpot
FROM pot where potStatus =1
- Developing XML with appropriate elements using relation field
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;
- Modifying data in a field of XML data type.
Code:
UPDATE Stores
SET Survey_untyped.modify('delete(/StoreSurvey/Comments)[1]')
WHERE StoreID = 1;
- Searching data in a field of XML data type.
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
