create database Tourdulich
go

create table hdv
(
mahdv nvarchar(50) not NULL,
tenhsv nvarchar(50),
ngaysinh date,
diachi nvarchar(50),
gioitinh nvarchar(50),
sdt nvarchar(50),
matour nvarchar(50),
primary key (mahdv)
)
create table diadiem
(
madd nvarchar(50) not NULL,
tendd nvarchar(50),
matour nvarchar(50) not NULL,
mota nvarchar(50),
primary key (madd)
)
create table dichvu
(
madv nvarchar (50) not NULL,
tendv nvarchar (50),
matour nvarchar (50),
giadv money,
mota nvarchar (50),
primary key (madv)
)
create table tour
(
matour nvarchar (50) not NULL,
tentour nvarchar (50),
mahdv nvarchar (50) not NULL,
ngaybd date,
ngaykt date,
trangthai nvarchar (50),
thoigian int,
giatour money,

primary key (matour),
)
create table hoadon
(
mahdon nvarchar (50) not NULL,
mahd nvarchar (50) unique not NULL,
tenkh nvarchar (50),
sotien money,
ngaythanhtoan date,

primary key (mahdon)
)
create table hopdong
(
mahd nvarchar (50) not NULL,
matour nvarchar (50) not NULL,
makh nvarchar (50) not NULL,
ngaylap date,
nguoilap nvarchar (50),
ngayhuy date,
phihuy money,
thanhtien money,

primary key (mahd)
)
create table khachhang
(
makh nvarchar (50) not NULL,
tenkh nvarchar (50),
quoctich nvarchar (50),
ngaysinh date,
hochieu nvarchar (50),
diachi nvarchar (50),

primary key (makh)
)
create table phieudanhgia
(
maphieu nvarchar (50)not NULL,
makh nvarchar (50),
mahd nvarchar (50),
danhgia nvarchar (50),

primary key (maphieu)
)
insert into hdv
values
('hdv01','cuong','2000/08/12','hanoi','nam','0123456','matour1'),
('hdv02','hung','1998/01/11','thaibinh','nam','0123567','matour2'),
('hdv03','quang','2000/04/03','haiduong','nam','0123678','matour3'),
('hdv04','hieu','2000/04/04','hanoi','nam','0123685','matour4'),
('hdv05','binh','2000/06/18','hanoi','nu','0123968','matour5')

insert into tour
values
('matour1','tour1','hdv01','2019/01/14','2019/02/04','hoanthanh','21','5000000'),
('matour2','tour2','hdv02','2019/05/07','2019/05/17','hoanthanh','7','7500000'),
('matour3','tour3','hdv03','2020/06/10','2020/06/25','chuahoanthanh','15','10000000'),
('matour4','tour4','hdv04','2019/011/02','2019/12/02','hoanthanh','30','20000000'),
('matour5','tour5','hdv05','2018/03/14','2018/03/15','hoanthanh','01','3000000')

insert into diadiem
values
('dd01','vinhhalong','matour1',''),
('dd02','hoian','matour2',''),
('dd03','hue','matour3',''),
('dd04','danang','matour4',''),
('dd05','cualo','matour5','')

insert into dichvu
values
('mdv01', 'Cafeom','matour1', '5000000',''),
('mdv02', 'leonui','matour2', '6000000',''),
('mdv03', 'duthuyen','matour3', '7000000',''),
('mdv04', 'nhaydu','matour4', '8000000',''),
('mdv05', 'luotvan','matour5', '9000000','')

insert into phieudanhgia
values
('maph01','makh01','mahd01',''),
('maph02','makh02','mahd02',''),
('maph03','makh03','mahd03',''),
('maph04','makh04','mahd04',''),
('maph05','makh05','mahd05','')

insert into khachhang
values
('makh01','kh01','vietnam','1960/02/21','hochieu01','hanoi'),
('makh02','kh02','vietnam','1973/06/11','hochieu02','bavi'),
('makh03','kh03','vietnam','1988/09/25','hochieu03','thaibinh'),
('makh04','kh04','campuchia','1999/03/16','hochieu04','phrompenh'),
('makh05','kh05','america','1996/06/13','hochieu05','newyork')

insert into hopdong
values
('mahd01','matour1','