I. Đề Tài
Đề số 9:

Một trường PTCS cần xây dựng hệ thống quản lý học sinh. Trường có 4 khối lớp
từ lớp 6 đến lớp 9. Mỗi khối có các lớp được đặt tên gồm khối và thứ tự để phân
biệt các lớp trong cùng khối. Chẳng hạn khối 7 có 5 lớp có các tên lớp 7/1,
7/2,...,7/5. Thông tin cần lưu cho học sinh là mã học sinh, tên học sinh, ngày
sinh, phái. Mỗi học sinh học lớp nào cần lưu thêm thông tin về năm học. Giả sử
trong một năm học không cho phép học sinh chuyển đổi lớp. Mỗi khối lớp có
các môn học quy định. Một năm học có học kỳ 1 và học kỳ 2. Mỗi môn trong
một học kỳ, một học sinh có các điểm kiểm tra như sau: không hoặc tối đa 3 lần
kiểm tra miệng, 3 lần kiểm tra 15 phút, 2 lần kiểm tra 1 tiết và 1 lần thi học kỳ.
Để có thêm thông tin ghi vào học bạ, hệ thống cần lưu thông tin giáo viên chủ
nhiệm và giáo viên dạy môn mà sinh viên học trong mỗi năm học của mỗi lớp
(Trong một năm học không có sự thay đổi giáo viên dạy và giáo viên chủ
nhiệm). Mỗi giáo viên có thể dạy nhiều môn. Trong một năm học một giáo viên
có thể không chủ nhiệm hoặc chỉ chủ nhiệm một lớp.

II. Code
1. Tạo Database
create database QLHS
on primary
(name=QLHS_data1,filename='D:\nhom9\QLHS_data1.mdf',size=10MB,maxsize=40
MB filegrowth=1MB)
log on
(name=QLHS_log,filename='D:\nhom9\QLHS_log.ldf',size=6MB,maxsize=8MB,file
growth=1MB)

2. Tạo Table
--Tao bang GIAOVIEN
create table GIAOVIEN

(MAGV nvarchar(10)not null,HOVATEN nvarchar(50),GIOITINH numeric,NGAYSINH
datetime ,NOISINH nvarchar(50),QUEQUAN nvarchar(50),DIACHI nvarchar(50),SDT
numeric ,CHUCVU nvarchar(30))
alter table GIAOVIEN add constraint pk_GV primary key(MAGV)
GO
--Tao bang HOCSINH
create table HOCSINH
(MSHS nvarchar(10)not null,HOVATEN nvarchar(50),GIOITINH numeric,NGAYSINH
datetime,NOISINH nvarchar(50),DIACHI nvarchar(50),QUEQUAN
nvarchar(50),DANTOC nvarchar(50),TONGIAO nvarchar(10))
alter table HOCSINH add constraint pkV_HS primary key(MSHS)
GO
--Tao bang HOCKY
create table HOCKY
1


(MAHK nvarchar(10)not null,NAMHOC numeric,GHICHU nvarchar(50))
alter table HOCKY add constraint pk_HK primary key(MAHK)
GO
--Tao bang MONHOC
create table MONHOC
(MAMH nvarchar(10)not null,TENMONHOC nvarchar(50),THACHANH numeric,
LYTHUYET numeric,MAHK nvarchar(10)not null)
alter table MONHOC add constraint pk_MH primary key (MAMH)
alter table MONHOC add constraint pk_MA foreign key(MAHK) references HOCKY
(MAHK)
GO
--Tao bang LOP
create table LOP

(MALOP nvarchar(10)not null,TENLOP nvarchar(10),SISO numeric)
alter table LOP add constraint pk_L primary key(MALOP)
GO
--Tao bang LICHGIANGDAY
create table LICHGIANGDAY
(MAGV nvarchar(10) not null,MAMH nvarchar(10)not null,MALOP nvarchar(10)not
null,NGAY datetime,TENLOP nvarchar(10),
TUTIET nvarchar(10),DENTIET nvarchar(10))
alter table LICHGIANGDAY add constraint pk_LGD primary key(MAGV,MAMH)
alter table LICHGIANGDAY add constraint pk_Ma_LDG foreign key(MAGV) references
GIAOVIEN (MAGV)
alter table LICHGIANGDAY add constraint pk_Ma_MH foreign key(MAMH) references
MONHOC (MAMH)
alter table LICHGIANGDAY add constraint pk_Ma_B foreign key(MALOP) references
LOP (MALOP)
GO
--Tao bang BANGDIEM
create table BANGDIEM
(MSHS nvarchar(10)not null,MAMH nvarchar(10)not null,KTRAMIENG numeric,
KTRA15 numeric,KTRA1TIET numeric,KTRAHOCKY numeric)
alter table BANGDIEM add constraint pk_BD primary key(MSHS,MAMH)
alter table BANGDIEM add constraint pk_Ms foreign key(MSHS) references HOCSINH
(MSHS)
alter table BANGDIEM add constraint pk_JBD foreign key(MAMH) references
MONHOC (MAMH)

3. Nhập Liệu
--nhap bang GIAOVIEN
insert into GIAOVIEN values ('gv001', 'vo kim thong',0,convert(datetime,
'12/02/1979',103), 'dak lak','dak lak','tp hcm',0988333222,'giao vien')

insert into GIAOVIEN values ('gv002', 'ngo quang liem',0, convert(datetime,
'12/12/1979',103), 'ninh thuan','ninh thuan','tp hcm',0988333223,'giao vien')
2


insert into GIAOVIEN values ('gv003', 'nguyen thi an',1, convert(datetime,
'10/02/1979',103), 'binh thuan','binh thuan','tp hcm',0988333224,'giao vien')
insert into GIAOVIEN values ('gv004', 'tran thanh thinh',0, convert(datetime,
'09/02/1979',103), 'thai binh','thai binh ','tp hcm',0988333225,'hieu pho')
insert into GIAOVIEN values ('gv005', 'lam hong diem',1, convert(datetime,
'05/07/1979',103), 'phu yen','ca mau','tp hcm',0988333226,'hieu truong')
go
-- nhap bang HOCSINH
insert into HOCSINH values ('hs001', 'nguyen lan anh',1, convert(datetime,
'05/08/2000',103), 'binh thuan','tp hcm','binh thuan','kinh','khong')
insert into HOCSINH values ('hs002', 'tran van an',0, convert(datetime, '04/02/1999',103),
'ca mau','tp hcm','ca mau','dao','thien chua')
insert into HOCSINH values ('hs003', 'lam thi teo',1, convert(datetime, '09/03/1998',103),
'binh thuan','tp hcm','ca mau','ede','tin lanh')
insert into HOCSINH values ('hs004', 'nguyen quoc long',0, convert(datetime,
'06/07/1997',103), 'thai nguyen','tp hcm','thai nguyen','bana','nho giao')
insert into HOCSINH values ('hs005', 'lam hong diem',1, convert(datetime,
'05/08/1997',103), 'binh thuan','tp hcm','ca mau','tay','phat giao')
go
--nhap bang HOCKY
insert into HOCKY values (01,2010,null)
insert into HOCKY values (02,2010,null)
insert into HOCKY values (03,2011,null)
insert into HOCKY values (04,2011,null)
insert into HOCKY values (05,2012,null)

go

--nhap bang MONHOC
insert into MONHOC values ('mh01','toan',30,40,02)
insert into MONHOC values ('mh02','van',10,20,05)
insert into MONHOC values ('mh03','ly',25,30,01)
insert into MONHOC values ('mh04','hoa',30,35,03)
insert into MONHOC values ('mh05','tin',20,25,04)
go
--nhap bang BANGDIEM
insert into BANGDIEM values ('hs001','mh01',6,8,9,8)
insert into BANGDIEM values ('hs002','mh02',7,9,5,7)
insert into BANGDIEM values ('hs003','mh03',2,9,7,6)
insert into BANGDIEM values ('hs004','mh04',7,9,4,7)
insert into BANGDIEM values ('hs005','mh05',3,8,9,5)
go
--nhap bang LOP
insert into LOP values (01,'lop6',50)
insert into LOP values (02,'lop7',40)
insert into LOP values (03,'lop8',45)
insert into LOP values (04,'lop9',42)
insert into LOP values (05,'lop9A',48)
go
--nhap bang LICHGIANGDAY
3


insert
into
LICHGIANGDAY

'02/09/2011',103),'6',1,5)
insert
into
LICHGIANGDAY
'02/09/2011',103),'6',6,10)
insert
into
LICHGIANGDAY
'03/09/2011',103),'7',6,10)
insert
into
LICHGIANGDAY
'04/09/2011',103),'8',2,4)
insert
into
LICHGIANGDAY
'05/09/2011',103),'9a',7,9)
insert
into
LICHGIANGDAY
'06/09/2011',103),'9b',1,3)

values

('gv001','mh01',01,convert(datetime,

values

('gv001','mh02',01,convert(datetime,


values

('gv002','mh02',02,convert(datetime,

values

('gv003','mh03',03,convert(datetime,

values

('gv004','mh04',04,convert(datetime,

values

('gv005','mh05',05,convert(datetime,

III. Mô Hình Quan Hệ

4


IV. View
1. Hien danh sach cac hoc sinh co noi sinh o Binh Thuan
create view cau1
as
select mshs,hovaten,ngaysinh,dantoc,tongiao,diachi,noisinh
from hocsinh
where noisinh like 'binh thuan'
select*from cau1select *from cau1


2. Xuat ra nhung nguoi lam chuc vu la giao vien
create view cau2
as
select magv,hovaten,gioitinh,chucvu,diachi,sdt
from giaovien
where chucvu like 'giao vien'
select*from cau2

5


3. Lay ra hoc sinh co diem thi hoc ki gioi
create view cau3
as
select hocsinh.mshs,hovaten,KTRAHOCKY
from hocsinh,bangdiem
where hocsinh.mshs=bangdiem.mshs and ktrahocky >=8 or ktrahocky >=10
select*from cau3

4. In ra nhung giao vien day lop 9
create view cau4
as
select lichgiangday.magv,hovaten,ngay,tenlop
from lichgiangday,giaovien
where lichgiangday.magv=giaovien.magv and tenlop like '9%'
select*from cau4

5. Xem chi tiet
create view cau5
as

select lichgiangday.tenlop,siso,namhoc,tenmonhoc
from lop,hocky,monhoc,lichgiangday
where hocky.mahk=monhoc.mahk and lop.malop=lichgiangday.malop
and lichgiangday.mamh= monhoc.mamh
select*from cau5

V.

PROCEDURE

1. Tinh tong diem cua cac hoc sinh
create procedure tongdiem
as
select
hs.MSHS,HOVATEN,MAMH,SUM((KTRAMIENG+KTRA15+KTRA1TIET*2+
KTRAHOCKy*3)/7) AS TONG_diem
from bangdiem bd,hocsinh hs
where bd.mshs = hs.mshs
group by hs.MSHS,HOVATEN,MAMH
exec tongdiem

2. Lay ra danh sach cua hoa sinh co tong diem cao nhat
create procedure tongdiem1
as
select
hs.MSHS,HOVATEN,MAMH,SUM((KTRAMIENG+KTRA15+KTRA1TIET*2+
KTRAHOCKy*3)/7) AS TONG_DIEM
from bangdiem bd,hocsinh hs
where bd.mshs = hs.mshs
group by hs.MSHS,HOVATEN,MAMH

6


having
SUM((KTRAMIENG+KTRA15+KTRA1TIET*2+KTRAHOCKy*3)/7)>=all
(select SUM((KTRAMIENG+KTRA15+KTRA1TIET*2+KTRAHOCKy*3)/7)
from bangdiem bd,hocsinh hs
where bd.mshs = hs.mshs
group by hs.MSHS,HOVATEN,MAMH)
exec tongdiem1

3. Lay ra danh sach giao vien co ngay thang la tham so truyen vao
create procedure dsthang
@thang datetime
as
select MAGV,HOVATEN,NGAYSINH,SDT
from giaovien gv
where month(ngaysinh) = @thang
exec dsthang 02

4. Lay ra danh sach hoc sinh voi dantoc la tham so truyen vao
create procedure dstg
@tg nvarchar(10)
as
select MSHS,HOVATEN,NGAYSINH,DANTOC,TONGIAO,(case GIOITINH
when 1 then 'Nu' when 0 then 'Nam' end) AS GIOITINH
from hocsinh
where dantoc = @tg
exec dstg 'kinh'


5. Lay ra danh sach hoc sinh voi gioitinh la tham so truyen vao
create procedure dsgt
@gt nvarchar(10)
as
select MSHS,HOVATEN,NGAYSINH,DANTOC,TONGIAO,(case GIOITINH
when 1 then 'Nu' when 0 then 'Nam' end) as GIOITINH
from hocsinh
where gioitinh = @gt
exec dsgt 1

VI.

TRIGGER

1. Tạo trigger để không cho phép xoá cùng lúc nhiều học sinh
create trigger t1
on hocsinh
for delete
as
if (select count(*) from deleted)>1
begin
7


raiserror('khong the xoa cung luc nhieu hoc sinh',16,1)
rollback transaction
End
delete from hocsinh where mshs = 'hs001' and mshs = 'hs002'

2. Tao trigger khong cho phep 2 tenlop trung ten

create trigger t2
on lop
for insert
as
declare @tenlop nvarchar(10)
select @tenlop = tenlop from inserted
if (select count (*) from lop where tenlop=@tenlop)>1
begin
print'trung ten lop!!!'
rollback
end
insert into LOP values (06,'lop8',45)

3. Tao trigger khong cho nhap diem ktramieng >10
create trigger t3
on bangdiem
for insert
as
declare @ktramieng numeric
select @ktramieng = ktramieng from inserted
if (@ktramieng>10)
begin
print'nhap lon hon 10'
rollback transaction
end
insert into BANGDIEM values ('hs002','mh01',11,8,9,8)

4. Tao trigger nhap ten giao vien phai lon hon 1 ki tu
create trigger t4
on giaovien

for insert
as
declare @cdten int
select @cdten = len(inserted.hovaten)
from inserted
if @cdten<=1
begin
print'Ten khong hop le,moi nhap lai!!!'
rollback transaction
8


end
insert into GIAOVIEN values ('gv006', 'A',0, convert(datetime, '12/12/1979',103), 'ninh
thuan','ninh thuan','tp hcm',0988333223,'giao vien')

5. Tao trigger khong cho phep xoa hoc sinh da co diem
create trigger t5
on hocsinh
for delete
as
declare @mshs nvarchar(10),@sl int
select @mshs = mshs from deleted
select @sl=count(mshs) from bangdiem where @mshs=Mshs
if ( @sl>1)
begin
print 'Khong xoa duoc vi co du lieu'
rollback transaction
end
delete from hocsinh where mshs = 'hs001'


6. Tao tu dong thay doi theo lich gian giang day
create trigger t6
on lichgiangday
after insert,delete,update
as
begin
update sisohs
set siso=(select count(lichgiangday.malop) from lichgiangday inner join lop
on lichgiangday.malop=lop.malop
where soluonghs.malop=lichgiangday.malop)
end

7. Ten lop khong duoc it hon hai ki tu
create trigger t7 on lop
for insert, update
as
begin
declare @siso int
declare @lop nvarchar(10)
select @lop=malop from inserted
select @siso= count(tenlop)
from lop where @lop= malop
if @siso>2
ROLLBACK TRANSACTION
End

9



8. Them 1 giao vien co tuoi <=25
create trigger t8 on giaovien
for insert
as
if exists (select * from inserted as a
where YEAR(GETDATE()) - YEAR(ngaysinh) <= 25)
begin
raiserror('Giao vien phai <=25 tuoi lam viec ',15,1);
rollback tran;
end;

9. Ma sinh vien phai tuan theo quy tac sau: 1 ky tu dau là ‘h’, thu 2 la ‘s’ ,3 ky tu
sau bieu the so thu tu
create trigger t9 on hocsinh
for insert, update
as
if update(Mshs) or not exists (select * from deleted)
if not exists(select * from inserted
where patindex('[h][s][0-9][0-9][0-9]',mshs) > 0)
begin
raiserror('Mã sinh viên phai tuan theo quy tac sau: 1 ký tu dau là ‘h’, thu 2
la ‘s’ ,3 ky tu sau bieu the so thu tu',15,1);
rollback
end;

10.Them mot hoc sinh phai co <=12 tuoi
create trigger t10 on hocsinh
for insert
as
if exists (select * from inserted as a

where YEAR(GETDATE()) - YEAR(ngaysinh) <= 12)
begin
raiserror('Hoc sinh phai co <=12',15,1);
rollback tran;
end;

10