SQL Pizza Company Database (CET 321 Course Work)

  

 SQL  Database 

(CET 321 Course Work)

The following article is about how to create a Pizza Company Database.

Note: This is my CET 321 homework.

create database AlierenkayhanCET321BonusHomework

use AlierenkayhanCET321BonusHomework

create table tblLogin(

luser_id int primary key,

user_password nvarchar(100) not null

)

create table tblAccount(

user_id int primary key,

luser_id int,

constraint tblAccount_tblLogin foreign key (luser_id) references tblLogin(luser_id), ---------- I give my foreign key reference to my primary key

user_name nvarchar(100),

user_surname nvarchar(100),

user_adress nvarchar(100) null,

user_phone nvarchar(100) null,

user_email nvarchar(100) null,

user_state nvarchar(10) not null,

)

create table tblSupplier(

supplier_id int primary key,

supplier_name nvarchar(100) null,

product_supplier_state nvarchar(100) null,

supplier_address nvarchar(100) null,

supplier_phone nvarchar(100) null,

supplier_email nvarchar(100) null,

supplier_price nvarchar(100)  null,

)

create table tblProductFeatures(

pfeatures_id int primary key,

size int null,

color nvarchar(100) null,

year date null,

product_description nvarchar(100) null,

price nvarchar(100)  null,

)

create table tblProduct(

p_id int primary key,

pname nvarchar(100) not null,

product_state nvarchar(100) not null,

supplier_id int,

constraint tblProduct_tblSupplier foreign key (supplier_id) references tblSupplier(supplier_id), ---------- I give my foreign key reference to my primary key

pfeatures_id int,

constraint tblProduct_tblProductFeatures foreign key (pfeatures_id) references tblProductFeatures(pfeatures_id), ---------- I give my foreign key reference to my primary key

)

create table tblComment(

comment_id int primary key,

comment_title nvarchar(100) null,

comment_description nvarchar(100) null,

user_id int,

constraint tblComment_tblAccount foreign key (user_id) references tblAccount(user_id), ---------- I give my foreign key reference to my primary key

products_starts nvarchar(5) null,

p_id int,

constraint tblComment_tblProduct foreign key (p_id) references tblProduct(p_id), ---------- I give my foreign key reference to my primary key

)

create table tblCategory(

cid int primary key,

cname nvarchar(100) not null,

)

create table tblSubCategory(

cid int,

constraint tblSubCategory_tblCategory foreign key (cid) references tblCategory(cid), ---------- I give my foreign key reference to my primary key

scid int,

sctype nvarchar(100) null,

p_id int

constraint tblSubCategory_tblProduct foreign key (p_id) references tblProduct(p_id), ---------- I give my foreign key reference to my primary key

scname nvarchar(100) not null,

brand nvarchar(100) null,

)

create table tblDeliveryman(

deliveryman_id int primary key,

deliveryman_name nvarchar(100) not null,

deliveryman_surname nvarchar(100) null,

)

create table tblDelivered(

delivered_id int primary key,

deliveryman_id int,

constraint tblDelivered_tblDeliveryman foreign key (deliveryman_id) references tblDeliveryman(deliveryman_id), ---------- I give my foreign key reference to my primary key

delivered_time date  null,

)

create table tblDiscount(

p_id int,

constraint tblDiscount_tblProduct foreign key (p_id) references tblProduct(p_id), ---------- I give my foreign key reference to my primary key

discount_id int primary key,

discount_name nvarchar(100) not null,

discount_deadline date null,

discount_check nvarchar(2) null,

discount_start_time date null,

discount_number int,

)

create table tblPayment(

discount_id int,

constraint tblPayment_tblDiscount foreign key (discount_id) references tblDiscount(discount_id), ---------- I give my foreign key reference to my primary key

total_price int null,

payment_type nvarchar(100) null,

)

create table tblOrder(

user_id int,

constraint tblOrder_tblAccount foreign key (user_id) references tblAccount(user_id), ---------- I give my foreign key reference to my primary key

p_id int,

constraint tblOrder_tblProduct foreign key (p_id) references tblProduct(p_id), ---------- I give my foreign key reference to my primary key

delivered_id int,

constraint tblOrder_tblDelivered foreign key (delivered_id) references tblDelivered(delivered_id), ---------- I give my foreign key reference to my primary key

order_id int primary key

)

insert into tblLogin values (1,1)

insert into tblLogin values (2,12)

insert into tblAccount(user_id,luser_id,user_name,user_surname,user_state) values (10,1,'Aperson','Surname','Active')

insert into tblAccount(user_id,luser_id,user_name,user_surname,user_state)  values (11,2,'Iperson','Surname','Active')

insert into tblSupplier(supplier_id,supplier_name) values (20001,'Dell')

insert into tblSupplier(supplier_id,supplier_name) values (20002,'MSI')

insert into tblSupplier(supplier_id,supplier_name) values (20003,'Apple')

insert into tblProductFeatures(pfeatures_id,year) values (1,'2022-01-20')

insert into tblProductFeatures(pfeatures_id,year) values (2,'2022-01-19')

insert into tblProductFeatures(pfeatures_id,year) values (3,'2022-01-18')

insert into tblProduct values (1,'Alienware','Available',20001,1)

insert into tblProduct values (2,'Katana','Available',20002,2)

insert into tblProduct values (3,'iPhone 12','Available',20003,3)

insert into tblComment(comment_id,comment_description,user_id,p_id) values (100001,'Good product',10,1)

insert into tblComment(comment_id,comment_description,user_id,p_id) values (100002,'Good product',11,2)

insert into tblCategory values (1,'Laptop')

insert into tblCategory values (2,'Phone')

insert into tblSubCategory values (1,1,'Gaming Laptop',1,'Gaming Laptop','Dell')

insert into tblSubCategory values (1,1,'Gaming Laptop',2,'Gaming Laptop','MSI')

insert into tblSubCategory values (2,2,'Phone',3,'Phone','Apple')

insert into tblDeliveryman(deliveryman_id,deliveryman_name) values (1,'Yperson')

insert into tblDeliveryman(deliveryman_id,deliveryman_name) values (2,'Xperson')

insert into tblDelivered(delivered_id,deliveryman_id) values (1,1)

insert into tblDelivered(delivered_id,deliveryman_id) values (2,2)

insert into tblDiscount(p_id,discount_id,discount_name,discount_number) values (1,1,'winter holiday discount',10)

insert into tblDiscount(p_id,discount_id,discount_name,discount_number) values (2,1,'winter holiday discount',10)

 

insert into tblPayment(discount_id,payment_type) values (1,'credits cards')

insert into tblOrder values (10,1,1,1)

insert into tblOrder values (11,3,2,2)

create view [Wholecategory] AS 

SELECT c.cid,c.cname,sc.scid,sc.scname,sc.sctype,p.pname,p.product_state

FROM tblCategory as c full outer join tblSubCategory as sc on c.cid = sc.cid full outer join tblProduct as p on p.p_id=sc.p_id

select * from [Wholecategory]

create view [ProductInfo] AS 

SELECT p.p_id,p.pname,p.product_state,p.supplier_id,pf.pfeatures_id,pf.year,c.comment_id,c.user_id,c.comment_description

FROM tblProduct as p full outer join tblProductFeatures as pf on p.pfeatures_id = pf.pfeatures_id full outer join tblComment as c on p.p_id=c.p_id

select * from [ProductInfo]

select * from tblAccount

select * from tblCategory

select * from tblComment

select * from tblDelivered

select * from tblDeliveryman

select * from tblDiscount

select * from tblLogin

select * from tblOrder

select * from tblPayment

select * from tblProduct

select * from tblProductFeatures

select * from tblSubCategory

select * from tblSupplier