Bài tập - Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server

-- Tao CSDL
create database SchoolBus

-- Kich hoat database
use SchoolBus

-- Tao tables
create table Student (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	birthday date,
	gender nvarchar(16),
	father_name nvarchar(50),
	father_phone nvarchar(20),
	mother_name nvarchar(50),
	mother_phone nvarchar(20),
	position_id int
)

alter table Student
drop column position_id

alter table Student
add address_id int

create table Bus (
	id int primary key identity(1,1),
	bus_no nvarchar(20) not null,
	type nvarchar(20),
	seats int,
	driver_id int
)

create table Driver (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	phone_number nvarchar(20),
	gender nvarchar(16),
	address nvarchar(200)
)

create table Schedule (
	bus_id int not null,
	address_id int not null,
	primary key (bus_id, address_id)
)

create table Addresses (
	id int primary key identity(1,1),
	name nvarchar(200) not null
)

-- Foreign key
alter table Bus
add constraint fk_bus_driver_id foreign key (driver_id) references Driver (id)

alter table Student
add constraint fk_student_address_id foreign key (address_id) references Addresses (id)

alter table Schedule
add constraint fk_schedule_bus_id foreign key (bus_id) references Bus (id)

alter table Schedule
add constraint fk_schedule_address_id foreign key (address_id) references Addresses (id)

-- Insert Data
insert into Addresses (name)
values
('54 Le Thanh Nghi'),
('285 Doi Can'),
('HH Linh Dam'),
('So 8 Ton That Tung'),
('So 5 Hoang Mai')

insert into Student (fullname, birthday, gender, father_name, father_phone, mother_name, mother_phone, address_id)
values
('A', '2005-06-11', 'Nam', 'A1', '12312', 'A2', '234234', 1),
('B', '2005-02-19', 'Nam', 'B1', '12312', 'B2', '234234', 1),
('C', '2005-08-16', 'Nam', 'C1', '12312', 'C2', '234234', 2),
('D', '2005-07-10', 'Nam', 'D1', '12312', 'D2', '234234', 1),
('E', '2005-09-22', 'Nam', 'E1', '12312', 'E2', '234234', 3),
('F', '2005-10-12', 'Nam', 'F1', '12312', 'F2', '234234', 4)

insert into Driver (fullname, gender, phone_number, address)
values
('Driver A', 'Nam', '45345345', 'Ha Noi'),
('Driver B', 'Nam', '45345345', 'Ha Noi'),
('Driver C', 'Nam', '45345345', 'Ha Noi'),
('Driver D', 'Nam', '45345345', 'Ha Noi'),
('Driver E', 'Nam', '45345345', 'Ha Noi')

insert into Bus (bus_no, type, seats, driver_id)
values
('B001', 'Yamaha', 30, 1),
('B002', 'Honda', 30, 2),
('B003', 'VINFAST', 30, 3),
('B004', 'Merch', 30, 4),
('B005', 'MOTOYOLA', 30, 5)


insert into Schedule (bus_id, address_id)
values
(1, 1),
(1, 2),
(2, 3),
(2, 4)

-- TEST
select * from Student
select * from Bus
select * from Addresses
select * from Driver
select * from Schedule

-- Tạo Proc xem thông tin lộ trình đi của xe bus : tài xế, biển số xe, địa chỉ đón.
create proc proc_view_schedule_bus
	@busId int
as
begin
	select Bus.bus_no, Bus.type, Bus.seats, Driver.fullname, Addresses.name
	from Bus, Driver, Addresses, Schedule
	where Schedule.address_id = Addresses.id
		and Schedule.bus_id = Bus.id
		and Bus.driver_id = Driver.id
		and Bus.id = @busId
end

exec proc_view_schedule_bus 1

-- Tạo Proc xem thông tin sinh viên theo biển số xe.
create proc proc_view_student_bus
	@fullname nvarchar(50)
as
begin
	select Student.fullname, Student.birthday, Student.gender, Student.father_name, Student.father_phone, Student.mother_name, Student.mother_phone, Bus.bus_no, Driver.fullname
	from Student, Addresses, Schedule, Bus, Driver
	where Student.address_id = Addresses.id
		and Addresses.id = Schedule.address_id
		and Schedule.bus_id = Bus.id
		and Bus.driver_id = Driver.id
		and Student.fullname like CONCAT('%', @fullname, '%')
end

exec proc_view_student_bus 'A'

-- Tao View xem thông tin sinh viên gồm : Tên SV, giới tính, địa chỉ đón
create view view_student_address
as
	select Student.fullname, Student.birthday, Student.gender, Student.father_name, Student.father_phone, Student.mother_name, Student.mother_phone, Addresses.name 'Address Name'
	from Student, Addresses
	where Student.address_id = Addresses.id

select * from view_student_address

-- Đánh index (nonclustered index) cho column họ tên bố trên bảng học viên
create nonclustered index index_father_name on Student (father_name)

-- Tạo trigger cho phép xoá địa chỉ đón trong bảng : Địa điểm đón xe
create trigger trigger_instead_of_address on Addresses
instead of delete
as
begin
	delete from Schedule where address_id in (select id from deleted)
	update Student set address_id = NULL where address_id in (select id from deleted)
	delete from Addresses where id in (select id from deleted)
end

delete from Addresses where id = 1