[Examination] Thiết kế CSDL khu vui chơi CityKids

-- Tao CSDL BT2907
create database BT2907
go

-- Kich hoat CSDL
use BT2907
go

-- Tao tables
create table zone (
	id int primary key identity(1,1),
	name nvarchar(50)
)
go

create table game (
	id int primary key identity(1,1),
	name nvarchar(50),
	zone_id int references zone (id),
	price float,
	type tinyint default 0,
	percents float
)
go

create table shop (
	id int primary key identity(1,1),
	name nvarchar(50),
	zone_id int references zone (id),
	company_name nvarchar(50)
)
go

create table product (
	id int primary key identity(1,1),
	title nvarchar(150),
	price float,
	shop_id int references shop (id)
)
go

create table ticket (
	id int primary key identity(1,1),
	price float,
	buy_date datetime,
	game_id int references game (id)
)
go

create table orders (
	id int primary key identity(1,1),
	product_id int references product (id),
	price float,
	num int,
	order_date datetime
)
go

-- Insert Data
insert into zone (name)
values
('Z001'),
('Z002'),
('Z003'),
('Z004'),
('Z005')
go

insert into game (zone_id, name, price, type, percents)
values
(1, 'G01', 1000, 0, 0),
(1, 'G02', 5000, 0, 0),
(2, 'G03', 3000, 1, 0.35),
(3, 'G04', 2000, 1, 0.35),
(4, 'G05', 8000, 0, 0),
(5, 'G06', 6000, 0, 0)
go

insert into shop (zone_id, name, company_name)
values
(1, 'S01', 'ABC'),
(2, 'S02', 'ABC'),
(3, 'S03', 'KKK'),
(4, 'S04', 'ABC'),
(5, 'S05', 'KKK')
go

insert into product (shop_id, title, price)
values
(1, 'P01', 2000),
(1, 'P02', 6000),
(2, 'P03', 3000),
(2, 'P04', 8000),
(2, 'P05', 3000),
(3, 'P06', 6000)
go

insert into orders (product_id, price, num, order_date)
values
(1, 2000, 2, '2022-03-26'),
(1, 2000, 3, '2022-03-26'),
(2, 6000, 1, '2022-03-27'),
(3, 3000, 5, '2022-03-27'),
(3, 3000, 2, '2022-03-25')
go

insert into ticket (game_id, price, buy_date)
values
(1, 1000, '2022-003-26'),
(1, 1000, '2022-003-26'),
(1, 1000, '2022-003-26'),
(2, 3000, '2022-003-27'),
(2, 3000, '2022-003-27')
go

insert into ticket (game_id, price, buy_date)
values
(3, 5000, '2022-003-26'),
(4, 6000, '2022-003-26')
go

--  Xem thông tin trờ chơi: tên zone, tên trờ chơi, giá, type, percent
select zone.name 'zone name', game.name 'game name', game.price, game.type, game.percents
from zone, game
where zone.id = game.zone_id
go

-- Tinh tiền thu được theo từng trờ chơi theo ngày cụ thể, tiền thu được của đối tác và của CityKids
select zone.name 'zone name', game.name 'game name', ticket.price, game.type, game.percents
from zone, game, ticket
where zone.id = game.zone_id
	and ticket.game_id = game.id
go

-- type: 0 -> CityKids, 1: Doi Tac
select zone.name 'zone name', game.name 'game name', ticket.price, game.type, game.percents, ticket.price * game.type * (1 - game.percents) '$ Doi Tac', (ticket.price - ticket.price * game.type * (1 - game.percents)) '$ CityKids'
from zone, game, ticket
where zone.id = game.zone_id
	and ticket.game_id = game.id
go

select sum(ticket.price * game.type * (1 - game.percents)) '$ Doi Tac', sum(ticket.price - ticket.price * game.type * (1 - game.percents)) '$ CityKids'
from zone, game, ticket
where zone.id = game.zone_id
	and ticket.game_id = game.id
go

-- Tính doanh thu đc của từng của hàng dịch vụ
select shop.name 'shop name', product.title 'product name', orders.price, orders.num, orders.price * orders.num
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
go

select shop.name 'shop name', sum(orders.price * orders.num) 'Tong Tien'
from shop, product, orders
where shop.id = product.shop_id
	and product.id = orders.product_id
group by shop.name
go

-- Tạo trigger cho phép xóa thông tin 1 đồ uống
select * from product
select * from orders

delete from product where id = 6
delete from product where id = 1

create trigger trigger_instead_of_delete on product
instead of delete
as
begin
	-- delete foreign key
	delete from orders where product_id in (select id from deleted)

	-- delete primary key
	delete from product where id in (select id from deleted)
end

delete from product where id = 1

-- Tạo trigger không cho sửa thông tin giá vé trong bản ticket
select * from ticket
go

create trigger trigger_no_update_price on ticket
for update
as
begin
	if update (price)
	begin
		print N'Khong dc phep sua gia ticket'
		rollback transaction
	end
end

update ticket set price = 1