By GokiSoft.com| 17:50 30/10/2021|
SQL Server/MySQL

[Video] Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL BT1816



--- Tao CSDL -> QuanLyDiemDanh
create database QuanLyDiemDanh
go

--- Active CSDL
use QuanLyDiemDanh
go

--- Phan I. Thiet ke tables
----- Tao bang Student
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	gender nvarchar(15),
	address nvarchar(150),
	birthday date
)
go

create table Teacher(
	id int primary key identity(1, 1),
	email nvarchar(150) unique,
	fullname nvarchar(150) not null,
	birthday date,
	gender nvarchar(15)
)
go

create table Subject (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	session int default 0
)
go

create table Class (
	id int primary key identity(1, 1),
	name nvarchar(50) not null,
	note nvarchar(200)
)
go

create table ClassMember (
	rollno nvarchar(20) not null,
	classno int not null,
	joined_date date,
	outed_date date,
	constraint pk_class_member primary key (rollno, classno)
)
go

alter table ClassMember
add constraint fk_class_member_rollno foreign key (rollno) references Student (rollno)

alter table ClassMember
add constraint fk_class_member_classno foreign key (classno) references Class (id)

create table Schedule (
	id int primary key identity(1, 1),
	teacherId int references Teacher(id),
	subjectNo int references Subject (id),
	classNo int references Class (id),
	startDate date,
	endDate date
)
go

------ attendence1, attendence2 : -1, 0, 1 : Vang, Nghi Co Phep, Di Hoc
------ attendence1, attendence2 : A, PA, P : Vang, Nghi Co Phep, Di Hoc
create table Attendences (
	id int primary key identity(1, 1),
	scheduleId int references Schedule(id),
	checkin datetime,
	attendence1 nvarchar(5) default 'P',
	attendence2 nvarchar(5) default 'P',
	note nvarchar(100)
)
go

------- TEST
select * from Student
select * from Class
select * from ClassMember
select * from Teacher
select * from Subject
select * from Schedule
select * from Attendences
-------- Phan II. Them du lieu
insert into Student (rollno, fullname, gender, birthday, address)
values
('R001', 'Tran Van A', 'Nam', '1999-03-06', 'Ha Noi'),
('R002', 'Tran Van B', 'Nam', '1997-05-19', 'Nam Dinh'),
('R003', 'Tran Van C', 'Nam', '1993-02-09', 'Ha Nam'),
('R004', 'Tran Van D', 'Nam', '1998-01-19', 'Thai Binh'),
('R005', 'Tran Van E', 'Nam', '1991-09-29', 'Hung Yen')
go

insert into Class (name)
values
('C1803L'),
('C1610I'),
('C1808G'),
('C2002L'),
('C1812L')
go

select * from Class

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 1, '2018-02-15', '2020-06-06'),
('R002', 1, '2018-02-15', '2020-06-06'),
('R003', 2, '2018-12-15', null),
('R004', 2, '2018-12-15', null),
('R005', 3, '2019-02-15', null)
go

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 4, '2018-02-15', null),
('R002', 4, '2018-02-15', null),
('R003', 4, '2018-12-15', null),
('R004', 5, '2018-12-15', null),
('R005', 5, '2019-02-15', null)
go

insert into Teacher (fullname, birthday, email, gender)
values
('Tran Van Diep', '1986-02-02', 'tranvandiep.it@gmail.com', 'Nam'),
('Tran Van OK', '1986-12-02', 'tranvanabc.ok@gmail.com', 'Nam')
go

insert into Subject(name, session)
values
('Lap Trinh C', 10),
('Bootstrap/jQuery', 6),
('HTML/CSS/JS', 10),
('SQL Server', 10),
('PHP/Laravel', 10)
go

select * from Schedule

insert into Schedule(teacherId, subjectNo, classNo, startDate, endDate)
values
(1, 4, 4, '2020-07-16', '2020-09-16'),
(1, 5, 5, '2020-07-10', '2020-09-10')
go

select * from Attendences

--- FIX : checkin => checkin1 & checkin2
--- rollno 
alter table Attendences
drop column checkin

alter table Attendences
add checkin1 datetime

alter table Attendences
add checkin2 datetime

alter table Attendences
add rollno nvarchar(20) references Student (rollno)

select * from Attendences

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(1, 'R001', '2020-08-20 19:05:00', 'A', '2020-08-20 20:50:00', 'P', ''),
(1, 'R002', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(1, 'R003', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'PA', '')

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(2, 'R004', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(2, 'R005', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', '')
go

--- - Tạo procedure để xem thông tin học viên trong một lớp học - đầu vào là tên lớp
------- ClassName, RollNo, Fullname, Gender, Birthday, Address
------- ClassName => Class, Other => Student, ClassMember
create proc proc_view_students_in_class
	@ClassName nvarchar(50)
as
begin
	select Class.name ClassName, Student.rollno, Student.fullname, Student.gender, Student.birthday, Student.address
	from Class left join ClassMember on Class.id = ClassMember.classno
		left join Student on ClassMember.rollno = Student.rollno
	where Class.name = @ClassName
end

exec proc_view_students_in_class N'C2002L'

---- Tạo procedure để xem danh sách điểm danh của lớp học, của môn môn học cụ thể. - đầu vào là mã lớp học và môn học
---- ClassName, SubjectName, Fullname, RollNo, checkin1, attendence1, checkin2, attendence2
------- Class, Subject, Student, Attendences, Schedule
select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
from Class, Subject, Student, Attendences, Schedule
where Class.id = Schedule.classNo
	and Subject.id = Schedule.subjectNo
	and Student.rollno = Attendences.rollno
	and Schedule.id = Attendences.scheduleId
	and Class.name = 'C2002L'
	and Subject.name = 'PHP/Laravel'

create proc proc_xem_diem_danh
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
end

create proc proc_xem_diem_danh_sinh_vien
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50),
	@RollNo nvarchar(20)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
		and Student.rollno = @RollNo
end

exec proc_xem_diem_danh N'C2002L', N'SQL Server'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R001'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R005'



Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL

Liên kết rút gọn:

https://gokisoft.com/1816

Bình luận

avatar
TRẦN VĂN ĐIỆP [Teacher]
2020-09-01 13:11:06

Trigger

avatar
TRẦN VĂN ĐIỆP [Teacher]
2020-09-01 12:49:41

Trigger


-- TEST
select * from Student

-- Trigger khong cho phep sua RollNo
create trigger trigger_no_update_rollno_student on Student
for update
as
begin
	if update(rollno)
	begin
		print N'Khong cho phep update rollno'
		rollback transaction
	end
end

insert into Student(rollno, fullname, gender, address, birthday)
values
('R006', 'ABC', 'Nam', 'Ha Noi', '1999-09-02')

update Student set rollno = 'R006' where rollno = 'R007'

-- Trigger check Gender
select * from Student
update Student set gender = 'Khac', address = 'Nam Dinh' where rollno = 'R007'
update Student set fullname = 'TRAN VAN X' where rollno = 'R007'
update Student set gender = '123' where rollno = 'R007'

create trigger trigger_check_update_gender_student on Student
for update
as
begin
	if (select count(gender) from inserted where gender not in ('Nam', 'Nu', 'Khac')) > 0
	begin
		print N'Yeu cau gioi tinh phai nhan 1 trong cac gia tri sau Nam, Nu, Khac'
		rollback transaction
	end
end

alter trigger trigger_check_update_gender_student on Student
for update
as
begin
	-- update Student set fullname = 'TRAN VAN X' where rollno = 'R007'
	-- gender => khong ton tai query tren => inserted
	if (select count(gender) from inserted where gender in ('Nam', 'Nu', 'Khac')) = 0
	begin
		print N'Yeu cau gioi tinh phai nhan 1 trong cac gia tri sau Nam, Nu, Khac'
		rollback transaction
	end
end

-- Trigger cho phép xoá sinh viên theo rollno
----- rollno trong bang Student => tao khoa ngoai (foreign key) tren nhung bang nao
-------- ClassMember, Attendance => Neu xoa ban nghi tren ClassMember & Attendance => thi co
----------------- bi error khong??? Khong co foreign key toi cac bang khac
create trigger InsteadOf_Delete_Rollno_Student on Student
instead of delete
as
begin
	delete from ClassMember where rollno in (select rollno from deleted)
	delete from Attendences where rollno in (select rollno from deleted)
	delete from Student where rollno in (select rollno from deleted)
end

select * from Student

delete from Student where rollno = 'R005'