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

1. Finance management
2. Record management
3. Report generation
4. Stock managements
5. 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:

1. 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

2. 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:

1. Number of Pots on shop in running condition:
2. Cost of different kg pots :
3. Renmaining plates in Pot

Balance Management:

1. Sale
2. Receipt
3. Payment

Report:

1. Sale by
   1. number of plates
   2. date
   3. price
   4. single/double/large
   5. 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:

1. If Pot is empty then make no sale from that Pot.
2. If stock is less than 5 mark alert.
3. If admin no add new pot on shop then no sale is accour
4. Pot sale by single ,medium, large plate type with different price
5. Sale man have own account for login
6. 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:

1. Pot and Dishlist:

Why:

Use for get dish name and pictures in pot by primary key and foreign key Referential integrity

2. 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

3. 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,

4. Pot Size and Ingredients table:

Why:

Use for numbers ingredients in different kg pots.

5. email unique index,

why:

use to store only unique emails of admin and users,

6. Client and sale

Why:

Use Primary key unique data in table

7. 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.

1. 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

1. Use of GROUP BY with HAVING

• Use of SQL functions

SELECT COUNT (DISTINCT [potId])as TotalInpot

FROM pot where potStatus =1

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;

1. 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