create database Tourdulich4
go
use Tourdulich4
go
create table huongdanvien(
mahuongdanvien nvarchar(50) not NULL,
hoten nvarchar(50),
ngaysinh date,
diachi nvarchar(50),
gioitinh nvarchar(50),
sodienthoai nvarchar(50),
primary key (mahuongdanvien)
)

go
create table diadiem(
madiadiem nvarchar(50) not NULL,
tendiadiem nvarchar(50),
tenthanhpho nvarchar(50),
quocgia nvarchar(50),
primary key (madiadiem))
go

create table tour(
matour nvarchar (50) not NULL,
tentour nvarchar (50),
mahuongdanvien nvarchar (50) not NULL,
madiadiem nvarchar(50) not null,
ngaybatdau date,
ngayketthuc date,
trangthai nvarchar (50),
thoigian datetime,
giatour money,
primary key (matour),
foreign key (mahuongdanvien) references huongdanvien(mahuongdanvien)
)
go


create table chitiettour(
matour nvarchar(50) not null,
madiadiem nvarchar(50) not null,
thoigianbatdau datetime,
thoigianketthuc datetime,
primary key(matour,madiadiem),
foreign key(matour) references tour(matour),
foreign key(madiadiem) references diadiem(madiadiem)
)
go

create table nhanvien(
manhanvien nvarchar(50) not NULL,
hoten nvarchar(50),
ngaysinh date,
diachi nvarchar(50),
gioitinh nvarchar(50),
sodienthoai nvarchar(50),
primary key (manhanvien)
)
go
create table dichvu(
madichvu nvarchar(50) not null,
tendichvu nvarchar(50),
giadichvu money,
primary key(madichvu)
)
go

create table khachhang(
makhachhang nvarchar(50) not NULL,
hoten nvarchar(50),
quoctich nvarchar(50),
ngaysinh date,
visa nvarchar(50),
diachi nvarchar(50),
gioitinh nvarchar(50),
sodienthoai nvarchar(50),
primary key (makhachhang)
)



go
create table hopdong(
mahopdong nvarchar(50) not null,
matour nvarchar(50) not null unique,
makhachhang nvarchar(50),
manhanvien nvarchar(50),
ngaylap datetime,
ngayhuy datetime,
phihuy money,
tiencoc money,
tinhtrangthanhtoan nvarchar(50),
thanhtien money,
primary key (mahopdong),
foreign key (matour) references tour(matour),
foreign key (makhachhang) references khachhang(makhachhang),
foreign key (manhanvien) references nhanvien(manhanvien)
)

go
create table chitietdichvu(
madichvu nvarchar(50) not null,
mahopdong nvarchar(50) not null,
primary key (madichvu,mahopdong),
foreign key (madichvu) references dichvu(madichvu),
foreign key (mahopdong) references hopdong(mahopdong)
)
go

create table hoadon(
mahoadon nvarchar(50) not null,
mahopdong nvarchar(50) unique,
makhachhang nvarchar(50),
ngaythanhtoan datetime,
giamgia float,
thanhtien money,
primary key (mahoadon),
foreign key (mahopdong) references hopdong(mahopdong),
foreign key (makhachhang) references khachhang(makhachhang)
)
go
create table phieudanhgia(
maphieu nvarchar(50) not null,
makhachhang nvarchar(50),
danhgia int,
ghichu nvarchar(3000),
primary key (maphieu),
foreign key (makhachhang) references khachhang(makhachhang)
)
go