Thiết kế CSDL & Nghiệp vụ quản lý tour du lịch - Khóa học SQL Server

-- Tao CSDL
create database BT2899
go

-- Kich Hoat CSDL
use BT2899
go

-- Tao tables
create table customer (
	id int not null identity(1,1),
	fullname nvarchar(50),
	birthday date,
	phone_number nvarchar(20),
	email nvarchar(150),
	points int default 0
)
go

create table places (
	id int not null identity(1,1),
	name nvarchar(50),
	address nvarchar(200)
)
go

create table tour (
	id int not null identity(1,1),
	place_id int,
	startdate datetime,
	enddate datetime,
	price float
)
go

create table booking (
	tour_id int not null,
	customer_id int not null,
	booking_date datetime
)

-- primary key
alter table customer
add constraint pk_customer primary key (id)
go

alter table places
add constraint pk_places primary key (id)
go

alter table tour
add constraint pk_tour primary key (id)
go

alter table booking
add constraint pk_booking primary key (customer_id, tour_id)
go

-- foreign key
alter table tour
add constraint fk_tour_places foreign key (place_id) references places (id)
go

alter table booking
add constraint fk_booking_customer foreign key (customer_id) references customer (id)
go

alter table booking
add constraint fk_booking_tour foreign key (tour_id) references tour (id)
go

-- insert data
insert into customer(fullname, email, phone_number, birthday, points)
values
('A', 'a@gmail.com', '123123213', '1999-02-12', 100),
('B', 'b@gmail.com', '345453454', '1998-06-12', 600),
('C', 'c@gmail.com', '657567657', '1989-04-12', 200),
('D', 'd@gmail.com', '678678678', '1988-08-12', 150),
('E', 'e@gmail.com', '678675456', '1995-03-12', 100)
go

insert into places (name, address)
values
('AA', 'Ha Noi'),
('AB', 'Ha Noi'),
('AC', 'Ha Noi'),
('AD', 'Ha Noi'),
('AE', 'Ha Noi')
go

insert into tour (place_id, price, startdate, enddate)
values
(1, 3000, '2022-01-02', '2022-01-05'),
(2, 12000, '2022-02-06', '2022-02-10'),
(1, 6000, '2022-01-12', '2022-01-18')
go

insert into booking (tour_id, customer_id, booking_date)
values
(1, 1, '2022-01-01'),
(1, 2, '2022-01-01'),
(1, 3, '2022-01-01'),
(2, 3, '2022-02-02'),
(2, 4, '2022-02-03')
go

-- Xem danh sách người đi theo 1 tour -> viết proc tìm theo tour_id
select customer.fullname, customer.phone_number, places.name 'place name', tour.price, tour.startdate, tour.enddate
from customer, places, tour, booking
where customer.id = booking.customer_id
	and booking.tour_id = tour.id
	and tour.place_id = places.id
go

create proc proc_view_customer_by_tour
	@tourId int
as
begin
	select customer.fullname, customer.phone_number, places.name 'place name', tour.price, tour.startdate, tour.enddate
	from customer, places, tour, booking
	where customer.id = booking.customer_id
		and booking.tour_id = tour.id
		and tour.place_id = places.id
		and tour.id = @tourId
end

exec proc_view_customer_by_tour 1
exec proc_view_customer_by_tour 2
exec proc_view_customer_by_tour 3

-- Thông kê tiền thu được cho từ Tour -> Viết view
select customer.fullname, customer.phone_number, places.name 'place name', tour.price, tour.startdate, tour.enddate
	from customer, places, tour, booking
	where customer.id = booking.customer_id
		and booking.tour_id = tour.id
		and tour.place_id = places.id

create view view_profit
as
select places.name 'place name', tour.startdate, tour.enddate, sum(tour.price) 'Doanh Thu'
	from places, tour, booking
	where booking.tour_id = tour.id
		and tour.place_id = places.id
	group by places.name, tour.startdate, tour.enddate
go

select * from view_profit
go

-- Viết trigger không cho phép xóa thông tin khách hàng
-- delete from customer where ... => FAIL
create trigger trigger_no_delete_customer on customer
for delete
as
begin
	print N'Khong dc xoa thong tin khach hang'
	rollback transaction
end

delete from customer where id = 5

-- Viết trigger không cho sửa giá tiền trong bảng Tour
select * from tour

create trigger trigger_no_update_price on tour
for update
as
begin
	if update (price)
	begin
		print N'Khong duoc thay doi gia trong bang tour'
		rollback transaction
	end
end

update tour set price = 1