Untitled

create database quanly
go
use quanly
go
create table customers (
    customerID int identity(1,1) not null,
    address nvarchar(50),
    country nvarchar(20),
	email varchar(25),
	phone char(10),
	city nvarchar(20),
	first_name nvarchar(10),
	last_name nvarchar(10),
	date_of_birth DATE,
)
alter table customers add constraint pk_cus primary key(customerID)

go

create table category (
	category_name nvarchar(20),
	categoryID int identity(1,1) not null,
)

alter table category add constraint pk_cate primary key(categoryID)

go 
create table products (
	productID int identity(1,1) not null,
	cost DECIMAL(11, 3),
	discount DECIMAL(3, 2),
	product_name nvarchar(20),
	product_desc nvarchar(50),
	categoryID int not null,
)

alter table products add constraint pk_pro primary key(productID)
alter table products add constraint fo_products_cate foreign key (categoryID) references category(categoryID)

go

create table orders (
	customerID int not null,
	orderID int identity(1,1) not null,
	order_date DATE
)

alter table orders add constraint pk_order primary key(orderID)
alter table orders add constraint fo_cus_order foreign key (customerID) references customers(customerID)

go
create table orders_products (
	orderID int not null,
	productID int not null,
)

alter table orders_products add constraint fo_o_p_o foreign key (orderID) references orders(orderID)
alter table orders_products add constraint fo_o_p_p foreign key (productID) references products(productID)
alter table orders_products add quantity SMALLINT
alter table orders_products add constraint pk_or_pro primary key(orderID, productID)
go

create table bill_details (
	cost_total DECIMAL(13,2),
	bill_detailID int identity(1,1) not null,
	orderID int not null
)

alter table bill_details add constraint pk_bill primary key(bill_detailID)
alter table bill_details add constraint fo_or_bill foreign key (orderID) references orders(orderID)