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 database BT1844
create database BT1844

-- Kich hoat database
use BT1844

-- Bạn được yêu cầu xây dựng database cho hệ thống đưa đón học sinh bằng xe bus cho một trường học có nghiệp vụ như sau
-- Bảng học viên gồm các trường : id tự tăng, Tên, địa chỉ, họ tên bố, me, sđt của bố, số điện thoại của mẹ, 
------ ngày sinh, giới tính, id địa điểm đón xe.
-- Thông tin xe bus : id tự tăng, biển số xe, loại xe, số ghế ngồi, id tài xế
-- Tài xế: id tự tăng, tên, sđt, giới tính, địa chỉ
-- Lộ trình xe đi : id xe bus, id địa điểm đón
-- Địa điểm đón xe : id tự tăng, địa chỉ

-- B1. Tao tat ca cac bang trong database
create table Student (
	id int primary key identity (1,1),
	fullname nvarchar(50) not null,
	birthday date,
	gender nvarchar(20),
	address nvarchar(200),
	father_name nvarchar(50),
	father_phone nvarchar(20),
	mother_name nvarchar(50),
	mother_phone nvarchar(20),
	position_id int
)

create table bus (
	id int primary key identity (1,1),
	bus_no nvarchar(20),
	type nvarchar(30),
	seats int,
	driver_id int
)

create table driver (
	id int primary key identity (1,1),
	fullname nvarchar(50),
	phone nvarchar(20),
	gender nvarchar(20),
	address nvarchar(200)
)

create table travel (
	bus_id int not null,
	position_id int not null,
	primary key (bus_id, position_id)
)

create table position (
	id int primary key identity (1,1),
	address nvarchar(200)
)

Tạo foreign key cho bảng trên.

-- Foreign key
alter table Student
add constraint fk_student_position foreign key (position_id) references position (id)

alter table bus
add constraint fk_bus_driver foreign key (driver_id) references driver (id)

alter table travel
add constraint fk_travel_bus foreign key (bus_id) references bus (id)

alter table travel
add constraint fk_travel_position foreign key (position_id) references position (id)

Them du lieu cho bang trong database

-- Them du lieu cho bang trong database
insert into driver (fullname, gender, phone, address)
values
('NGUYEN VAN A', 'NAM', '123123', 'HA NOI'),
('NGUYEN VAN B', 'NAM', '4324234', 'NAM DINH'),
('NGUYEN VAN C', 'NAM', '234545', 'HA NOI'),
('NGUYEN VAN D', 'NAM', '23423423', 'HA NAM'),
('NGUYEN VAN E', 'NAM', '23432454', 'NINH BINH')

insert into position (address)
values
('54 LE THANH NGHI'),
('285 DOI CAN'),
('NGO 20 TON THAT TUNG')

insert into Student (fullname, gender, birthday, address, father_name, father_phone, mother_name, mother_phone, position_id)
values
('TRAN VAN A', 'NAM', '2010-02-16', '56 LE THANH NGHI', 'FATHER A', '123123', 'MOTHER A', '213213', 1),
('TRAN VAN B', 'NAM', '2010-09-16', '560 LE THANH NGHI', 'FATHER B', '123123', 'MOTHER B', '213213', 1),
('TRAN VAN C', 'NAM', '2010-06-16', '126 LE THANH NGHI', 'FATHER C', '123123', 'MOTHER C', '213213', 1),
('TRAN VAN D', 'NAM', '2010-05-16', '200 DOI CAN', 'FATHER D', '123123', 'MOTHER D', '213213', 2),
('TRAN VAN E', 'NAM', '2010-10-16', '218 DOI CAN', 'FATHER E', '123123', 'MOTHER E', '213213', 2)

insert into bus (bus_no, seats, type, driver_id)
values
('R001', 16, 'VIP', 1),
('R002', 30, 'VIP', 2),
('R003', 36, 'VIP', 3)

insert into travel (bus_id, position_id)
values
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 3)

-- TEST
select * from driver
select * from position
select * from Student
select * from bus
select * from travel

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.

-- 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_travel
as
begin
	select driver.fullname 'driver name', bus.bus_no, position.address 'position address'
	from driver, bus, position, travel
	where driver.id = bus.driver_id
		and bus.id = travel.bus_id
		and travel.position_id = position.id
end

exec proc_view_travel

Tạo Proc xem thông tin sinh viên theo biển số xe => truyền tham số xe vào -> hiển thị danh sách sinh viên tương ứng

-- Tạo Proc xem thông tin sinh viên theo biển số xe => truyền tham số xe vào -> hiển thị danh sách sinh viên tương ứng
create proc proc_view_student_bus
	@busNo nvarchar(20)
as
begin
	select Student.*, bus.bus_no
	from Student, bus, position, travel
	where Student.position_id = position.id
		and position.id = travel.position_id
		and travel.bus_id = bus.id
		and bus.bus_no = @busNo
end

exec proc_view_student_bus 'R001'

Tao View xem thông tin sinh viên gồm : Tên SV, giới tính, địa chỉ đón

-- 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_position
as
select Student.fullname, Student.gender, position.address 'position address'
from Student, position
where Student.position_id = position.id

select * from view_student_position

-- Đá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 (position)
-- Phân tích: Khi xóa dữ liệu trên bảng position -> foreign trên bảng (travel & Student) -> gây ra lỗi -> không cho phép xóa trong bản position
---- Để xóa thành cống -> yêu cầu xóa dữ liệu trên bảng Student và travel trước -> sau đó quay sang xóa positon
-- TEST
select * from position
select * from Student
select * from travel

delete from position where id = 1

-- Luu y: Student -> du lieu quan trong -> khong the xoa du lieu sinh vien duoc -> Do vay giai phap -> set null cho position_id trong bang Student trong TH nay
create trigger trigger_delete_position on position
instead of delete
as
begin
	update Student set position_id = null where position_id in (select id from deleted)
	delete from travel where position_id in (select id from deleted)
	delete from position where id in (select id from deleted)
end

-- TEST
delete from position where id = 1

-- TEST
select * from position
select * from Student
select * from travel