REQUIREMENTS
1. Create a database, ShopHere.
2. Create the tables as per the relationship diagram, ensuring minimum disk space utilization.
3. Perform validations on the tables as per the following guidelines.
4. Create appropriate relationships between the tables.
5. Create the appropriate indexes.
6. Simplify the following task:
− Calculation of the total cost for a particular order.
− Calculation of the total of all the orders placed by a particular employee in a
particular month.
1
CREATE DATABASE
Create a database, ShopHere.
CREATE DATABASE Shope Here
ON PRIMARY
NAME= ShopeHere _Data,
FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\ ShopeHere _Data.mdf',
MAXSIZE=50mb,SIZE=20mb,
FILEGROWTH=2mb
LOG ON
NAME= ShopeHere_Log,
FILENAME='C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\ ShopeHere _Log.ldf',
MAXSIZE=10mb,SIZE=4mb,
FILEGROWTH =2kb
2
CREATE SCHEMAS
CREATE SCHEMA Items
CREATE SCHEMA Supplier
CREATE SCHEMA HumanResources
CREATE SCHEMA Transactions
3
CREATE TABLES
TABLE [Link]
CREATE TABLE [Link]
(
ItemID INT PRIMARY KEY IDENTITY(1,1),
ItemName NVARCHAR (50) NOT NULL,
ItemDecription NVARCHAR (50) NOT NULL,
UnitPrice MONEY CHECK (UnitPrice>0),
QantityInHand INT CHECK (QantityInHand>0),
ReorderLevel NVARCHAR (50) CHECK (ReorderLevel>0),
ReorderQuantity INT CHECK (ReorderQuantity>0),
CategoryID INT ,
SupplierID INT
ALTER TABLE [Link]
ADD
CONSTRAINT [FK_Items.ItemDetails_Items.ProductCategory] FOREIGN KEY
[CategoryID]
) REFERENCES [Link]
ALTER TABLE [Link]
ADD
CONSTRAINT [FK_Items.ItemDetails_Supplier.SupplierDetails] FOREIGN KEY
SupplierID
) REFERENCES [Link]
4
TABLE [Link]
CREATE TABLE [Link]
Catego ryID INT PRIMARY KEY IDENTITY,
Catego ryName NVARCHAR (200) NOT NULL CHECK
(CategoryName IN ('HouseHold','Sports','Accessories','Clothing')),
Catego ryDescription NVARCHAR (1000) NOT NULL
TALBLE [Link]
CREATE TABLE [Link]
SupplierID INT PRIMARY KEY IDENTITY (001,1),
FirstName NVARCHAR (100) NOT NULL,
LastName NVARCHAR (100) NOT NULL,
Address NVARCHAR (1000) NOT NULL,
Phone VARCHAR(20) NOT NULL CHECK ([Phone]LIKE
('[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]')),
Country NVARCHAR (100) NOT NULL
TABLE [Link]
CREATE TABLE [Link]
EmployeeID INT PRIMARY KEY IDENTITY(01,1),
FirstName NVARCHAR (100) NOT NULL,
LastName NVARCHAR (100) NOT NULL,
City NVARCHAR (100) NOT NULL,
Phone VARCHAR(30) NOT NULL CHECK ([Phone] LIKE
('[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'))
5
)
TABLE [Link]
CREATE TABLE [Link]
PurchaseOrderID INT PRIMARY KEY IDENTITY (1,1),
EmployeeID INT NOT NULL,
OrderDate DATETIME CHECK ([OrderDate] < GETDATE())
DEFAULT GETDATE(),
RecievingDate DATETIME NULL ,
ItemsID INT,
QuantityOrder INT CHECK ([QuantityOrder]>0),
QuantityReceived INT NULL CHECK ([QuantityReceived]>0),
UnitPrice MONEY CHECK ([UnitPrice]>0),
ShipMethod NVARCHAR(200) ,
OrderStatus NVARCHAR(1000) CHECK (OrderStatus IN ('InTransit',
'Received','Cancelled'))
ALTER TABLE [Link]
ADD
CONSTRAINT [FK_Transactions.OrderDetails_Items.ItemDetails]
FOREIGN KEY
(ItemsID) REFERENCES [Link]
ALTER TABLE [Link]
ADD
CONSTRAINT [FK_Transactions.OrderDetails_HumanResources.Employee]
FOREIGN KEY
(EmployeeID) REFERENCES [Link]
6
PERFORM VALIDATION ON TABLES
TRIGGERS Quantity received can not greater than QuantityOrdered
CREATE TRIGGER TRG_Checkquatity
ON [Link]
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @QuantityOrder INT
DECLARE @QuantityReceived INT
SELECT @QuantityOrder=QuantityOrder,@QuantityReceived=QuantityReceived
FROM INSERTED
IF @QuantityReceived>=@QuantityOrder
BEGIN
PRINT 'QuantityReceived phai nho hon hoac bang QuantityOrder'
ROLLBACK TRAN
END
END
7
QuantityReceived should be added to QuantityInHand in the Items table
CREATE TRIGGER TRG_addquantity
ON [Link]
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @QuantityReceived INT
DECLARE @QantityInHand INT
DECLARE @add INT
DECLARE @ItemID INT
SELECT @QuantityReceived=QuantityReceived,
@QantityInHand=QantityInHand,
@ItemID=ItemID
FROM [Link] a JOIN inserted b ON [Link]=[Link]
SET @add=@QuantityReceived+@QantityInHand
UPDATE [Link]
SET QantityInHand=@add
WHERE ItemID=@ItemID
END
8
RecievingDate should be greater than OrderDate
CREATE TRIGGER TRG_checkdate
ON [Link]
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @RecievingDate DATETIME
DECLARE @OrderDate DATETIME
SELECT @RecievingDate=RecievingDate,
@OrderDate=OrderDate
FROM inserted
IF @RecievingDate<=@OrderDate
BEGIN
PRINT 'RecievingDate phai lon hon bang OrderDate'
ROLLBACK TRAN
END
END
9
DATABASE DIAGRAM
10
CREATE INDEX
1. Extract the Order Details for all the Purchase Orders in the current
month:
CREATE NONCLUSTERED INDEX Idx_OrderDate
ON [Link] (OrderDate)
2. Extract the details of all the Orders placed more than two years
back:
CREATE NONCLUSTERED INDEX idx_ReceivingDate
ON [Link](ReceivingDate)
3. Extract the details of the TOP FIVE Suppliers to whom the aximum
number of order have been placed in the current month:
CREATE NONCLUSTERED INDEX idx_QuantityOrders
ON [Link](QuantityOrdered)
11
TASK SIMPLIFYING
Calculation of the total cost for a particular order
CREATE PROC Totalcost @PurchaseOrderID int
AS
BEGIN
DECLARE @QuantityReceived INT
DECLARE @UnitPrice MONEY
DECLARE @Totalcost MONEY
SELECT@PurchaseOrderID=PurchaseOrderID,
@QuantityReceived=QuantityReceived,
@UnitPrice=UnitPrice
FROM [Link]
WHERE PurchaseOrderID=@PurchaseOrderID
SET @Totalcost=@UnitPrice*@QuantityReceived
PRINT @Totalcost
END
12
Calculation of the total of all the orders placed by a particular employee in
a particular month
CREATE PROC T OLTALORDER @EMPLOYEE ID INT,@MONTH INT
AS
BEGIN
SELECT @E MPLOYEE ID=EMPLOYEEID,@M ONTH=MONTH(ORDER DATE)
FROM T [Link] DETAILS
SELECT COUNT (QUANTITYORDER )
FROM TRANSACTIONS .ORDERDETAILS
WHERE E MPLOYEE ID=@E MPLOYEE ID AND MONTH (ORDERDATE )=@M ONTH
END
13