--untuk membuat tabel
create table MsCashier
(
    CashierID char(6) primary key not null,
    CashierName varchar(45),
    CashierPhone varchar(14),
    CashierAddress varchar(50),
    constraint cons1 check( len(CashierID) = 6 ),
    constraint cons2 check( CashierID like 'KK[0-9][0-9][0-9][0-9]' ),   
)

create table MsCustomer
(
    CustomerID char(6) primary key not null,
    CustomerName varchar(50),
    CustomerAddress varchar(50),
    CustomerPhone varchar(14),
    CustomerEmail varchar(35),
    constraint cons3 check ( len(CustomerID) = 6 ),
    constraint cons4 check ( CustomerID like 'KKN[0-9][0-9][0-9]' ),   
)

create table MsProductCategory
(
    ProductCategoryID char(6) primary key not null,
    ProductCategory varchar(45),
    constraint cons7 check ( len(ProductCategoryID) = 6 ),
    constraint cons8 check ( ProductCategoryID like 'JPR[0-9][0-9][0-9]' ),   
)

create table MsProduct
(
    ProductID char(6) primary key not null,
    ProductName varchar(40),
    ProductPrice numeric,
    ProductCategoryID char(6),
    constraint cons5 check ( len(ProductID) = 6 ),
    constraint cons6 check ( ProductID like 'PRO[0-9][0-9][0-9]' ),
    Foreign key(ProductCategoryID) references MsProductCategory on update cascade on delete cascade
)

create table TrHeaderSell
(
    TransactionID char(6) primary key not null,
    TransactionDate datetime not null,
    CustomerID char(6) not null,
    CashierID char(6) not null,   
    constraint cons9 check ( len(TransactionID) = 6 ),
    constraint cons10 check ( TransactionID like 'TR[0-9][0-9][0-9][0-9]'),  
    Foreign key(CustomerID) references MsCustomer on update cascade on delete cascade,
    Foreign key(CashierID) references MsCashier on update cascade on delete cascade
)


create table TrDetailSell
(   
    TransactionID char(6) not null,
    ProductID char(6) not null,
    Qty numeric,
    primary key (TransactionID, ProductID),
    Foreign key(TransactionID) references TrHeaderSell on update cascade on delete cascade,
    Foreign key(ProductID) references MsProduct on update cascade on delete cascade
)


--menghapus tabel
--master
drop table MsCashier
drop table MsCustomer
drop table MsProduct
drop table MsProductCategory

--transaction
drop table TrHeaderSell
drop table TrDetailSell


--menampilkan semua tabel
sp_tables

--menampilkan isi table

select *  from MsCashier
select *  from MsCustomer
select *  from MsProduct
select *  from MsProductCategory
select *  from TrHeaderSell
select *  from TrDetailSell



--MsCashier

insert into MsCashier values('KK0001','Niko Setiono','021-56057557','Jl. Tmn Teratai 3 E6/11, Tangerang')
insert into MsCashier values('KK0002','Christine','021-56776699','Jl. H Ungar Lr. Mursala No. 69, Tanjungpinang')
insert into MsCashier values('KK0003','Albertus Agung','021-56928153','THB A1 No.2')
insert into MsCashier values('KK0004','Monika Leslivania','021-56537784','Jl. Jend Sudirman No. 96, Medan')
insert into MsCashier values('KK0005','Novi Trisjaniar','021-56606693','Jl. A.Yani No. 12, Sanggau')
insert into MsCashier values('KK0006','Devina Purwito','021-56551736','Jl. Ade Irma Suryani No. 8, Pontianak')

--MsCustomer
insert into MsCustomer values('KKN001','Fransisca Theresia','Apart. Gading Mediterania CC / 31A / P','08159145050','invisible_kid@hotmail.com')
insert into MsCustomer values('KKN002','Fendy Willyam','Jl. Kebon Jeruk Raya no. 9 Kemanggisan Jakbar','081808215249','ee_uze@yahoo.com')
insert into MsCustomer values('KKN003','Garry Bernardy','Jl. Studio No.41 Kemanggisan ','08126586154','lius_hunt@hotmail.com')
insert into MsCustomer values('KKN004','Hardi Chandra','Lembang Baru II A / 3 Ciledug - Tangerang','08128268897','med_inez@yahoo.com')
insert into MsCustomer values('KKN005','Hendra Kurniawan','Tmn. Aries E 20 / 35 Jakbar','081316210220','nmbee_09@yahoo.com')
insert into MsCustomer values('KKN006','Heru Prasetia','Kebon Jeruk Raya no.9','08170692570','arcadia_boyz@yahoo.com')
insert into MsCustomer values('KKN007','Mariana','Jl. Keluarga no.30 B','08567745789','mr.henry@programmer.net')
insert into MsCustomer values('KKN008','Henry Chong','Gg. Harun 2 Kemanggisan Pulo','08568742515','leonheart@qita.net')
insert into MsCustomer values('KKN009','Fredy','Jl. Pluit Karang Elok J5 no 3 muara Karang Jakut ','08561772223','fredy_fe@yahoo.com')

--MsProductCategory

insert into MsProductCategory values('JPR001','Motherboard')
insert into MsProductCategory values('JPR002','Harddisk External')
insert into MsProductCategory values('JPR003','Monitor')


--MsProduct

insert into MsProduct values('PRO001','INTEL CORE 2 DUO 2.33 GHz (E6550) Tray',1593330,'JPR001')
insert into MsProduct values('PRO002','INTEL QUADCORE 2.4 GHz(Q6600) BOX',2560380,'JPR001')
insert into MsProduct values('PRO003','INTEL QUADCORE 2.66 GHz(Q6700) BOX',5111550,'JPR001')
insert into MsProduct values('PRO004','250 GB SEAGATE IDE RESMI',746010,'JPR002')
insert into MsProduct values('PRO005','320 GB SEAGATE SATA RESMI',939420,'JPR002')
insert into MsProduct values('PRO006','500 GB SEAGATE SATA RESMI',1630170,'JPR002')
insert into MsProduct values('PRO007','MONITOR SAMSUNG 15" 591S',773640,'JPR003')
insert into MsProduct values('PRO008','MONITOR SAMSUNG 17" 793 BLACK',828900,'JPR003')
insert into MsProduct values('PRO009','MONITOR SAMSUNG 17" FLAT 793MG',1031520,'JPR003')


--TrHeaderSell

insert into TrHeaderSell values('TR0001','2/1/2010','KKN004','KK0004')
insert into TrHeaderSell values('TR0002','2/1/2010','KKN001','KK0004')
insert into TrHeaderSell values('TR0003','2/3/2010','KKN003','KK0001')
insert into TrHeaderSell values('TR0004','2/3/2010','KKN002','KK0001')
insert into TrHeaderSell values('TR0005','2/3/2010','KKN006','KK0003')
insert into TrHeaderSell values('TR0006','2/3/2010','KKN002','KK0005')
insert into TrHeaderSell values('TR0007','2/3/2010','KKN009','KK0002')
insert into TrHeaderSell values('TR0008','2/3/2010','KKN003','KK0003')
insert into TrHeaderSell values('TR0009','2/20/2010','KKN005','KK0001')
insert into TrHeaderSell values('TR0010','2/20/2010','KKN002','KK0001')
insert into TrHeaderSell values('TR0011','2/20/2010','KKN003','KK0004')
insert into TrHeaderSell values('TR0012','2/20/2010','KKN007','KK0001')
insert into TrHeaderSell values('TR0013','2/20/2010','KKN004','KK0003')
insert into TrHeaderSell values('TR0014','2/20/2010','KKN002','KK0006')
insert into TrHeaderSell values('TR0015','2/20/2010','KKN001','KK0004')
insert into TrHeaderSell values('TR0016','2/23/2010','KKN008','KK0004')
insert into TrHeaderSell values('TR0017','5/1/2010','KKN008','KK0004')
insert into TrHeaderSell values('TR0018','5/1/2010','KKN002','KK0005')
insert into TrHeaderSell values('TR0019','5/1/2010','KKN003','KK0003')
insert into TrHeaderSell values('TR0020','5/1/2010','KKN001','KK0001')

--TrDetailSell

insert into TrDetailSell values('TR0001','PRO008',79)
insert into TrDetailSell values('TR0002','PRO008',84)
insert into TrDetailSell values('TR0003','PRO006',13)
insert into TrDetailSell values('TR0004','PRO009',96)
insert into TrDetailSell values('TR0005','PRO002',94)
insert into TrDetailSell values('TR0006','PRO003',32)
insert into TrDetailSell values('TR0007','PRO002',1)
insert into TrDetailSell values('TR0008','PRO002',7)
insert into TrDetailSell values('TR0009','PRO007',76)
insert into TrDetailSell values('TR0010','PRO002',30)
insert into TrDetailSell values('TR0011','PRO006',87)
insert into TrDetailSell values('TR0012','PRO008',43)
insert into TrDetailSell values('TR0013','PRO008',47)
insert into TrDetailSell values('TR0014','PRO008',3)
insert into TrDetailSell values('TR0015','PRO003',85)
insert into TrDetailSell values('TR0016','PRO001',15)
insert into TrDetailSell values('TR0017','PRO006',95)
insert into TrDetailSell values('TR0018','PRO006',3)
insert into TrDetailSell values('TR0019','PRO009',77)
insert into TrDetailSell values('TR0020','PRO006',24)


delete  from MsCashier
delete  from MsCustomer
delete  from MsProduct
delete  from MsProductCategory
delete  from TrHeaderSell
delete  from TrDetailSell

select *  from MsCashier
select *  from MsCustomer
select *  from MsProduct
select *  from MsProductCategory
select *  from TrHeaderSell
select *  from TrDetailSell