GROUP MEMBERS:
WARIHA ASIM (65914)
AQDAS NADEEM (65905)
MADIHA SHAHID (65889)
DATABASE PROJECT DDL SCRIPT
create database hamarimehnat;
use hamarimehnat;
create table logininfo (
id int primary key identity(1,1),
email varchar(100) unique,
password varchar(50)
);
create table customerlogininfo (
id int primary key identity(1,1),
email varchar(100),
password varchar(50)
);
create table cart (
cart_id int primary key identity(1,1),
customer_id int foreign key references customerlogininfo(id),
product_id int foreign key references product(pid),
quantity int,
added_date datetime default getdate()
);
create table product (
pid int primary key identity(201,1),
pname varchar(50),
pprice int check(pprice>0),
pquantity int not null,
pcategory int foreign key references category(id)
);
create table category (
id int primary key identity(101,1),
categoryname varchar(50)
);
create table log (
status1 varchar(50),
operation varchar(50)
);
PROCEDURES:
create procedure categoryview
@i int
as
begin
select* from product where pcategory=@i
end
exec categoryview 101;
CREATE PROCEDURE UpdateCartItemQuantity
@customer_id INT,
@product_id INT,
@quantity INT
AS
BEGIN
UPDATE Cart
SET quantity = @quantity
WHERE customer_id = @customer_id AND product_id = @product_id;
END;
CREATE PROCEDURE AddToCart
@customer_id INT,
@product_id INT,
@quantity INT
AS
BEGIN
DECLARE @existingQuantity INT;
-- Check if the item already exists in the cart for the customer
SELECT @existingQuantity = quantity
FROM Cart
WHERE customer_id = @customer_id AND product_id = @product_id;
IF @existingQuantity IS NOT NULL
BEGIN
UPDATE Cart
SET quantity = @existingQuantity + @quantity
WHERE customer_id = @customer_id AND product_id = @product_id;
END
ELSE
BEGIN
-- If the item does not exist, insert a new row
INSERT INTO Cart (customer_id, product_id, quantity)
VALUES (@customer_id, @product_id, @quantity);
END
END;
CREATE PROCEDURE GetCartItems
@customer_id INT
AS
BEGIN
SELECT
p.pid ,
p.pname,
p.pprice,
c.quantity,
(p.pprice * c.quantity) AS total_price
FROM Cart c
JOIN product p ON c.product_id = p.pid
WHERE c.customer_id = @customer_id;
END;
CREATE PROCEDURE RemoveFromCart
@customer_id INT,
@product_id varchar(50)
AS
BEGIN
DELETE FROM Cart
WHERE customer_id = @customer_id AND product_id = @product_id;
END;
exec RemoveFromCart 1, 204;
CREATE PROCEDURE ClearCart
@customer_id INT
AS
BEGIN
DELETE FROM cart
WHERE customer_id = @customer_id;
END
TRIGGER
create trigger cartmauwali on cart
after insert
As
begin
insert into log values('Successfull','Insert');
end
