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

-- Tao CSDL
create database BT1810
go

-- Kich hoat CSDL
use BT1810
go

-- Tao tables
create table Student (
	rollno int primary key identity(1,1),
	fullname nvarchar(50) not null,
	email nvarchar(150),
	phone_number nvarchar(20)
)
go

create table Teacher (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	email nvarchar(150),
	phone_number nvarchar(20)
)
go

create table Subject (
	id int primary key identity(1,1),
	name nvarchar(50),
	session_count int,
	constraint ck_session_count check (session_count > 0)
)
go

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

create table ClassMember (
	rollno int references Student (rollno),
	class_id int references Class (id),
	joined_date date,
	outed_date date
)
go

create table Schedule (
	id int primary key identity(1,1),
	teacher_id int references Teacher (id),
	subject_id int references Subject (id),
	class_id int references Class (id),
	start_date date,
	end_date date
)
go

create table Attendence (
	id int primary key identity(1,1),
	schedule_id int references Schedule (id),
	rollno int references Student (rollno),
	status_1 nvarchar(5), -- P | PA | A
	date_1 datetime,
	status_2 nvarchar(5),
	date_2 datetime,
	note nvarchar(250)
)
go

-- Insert Data
insert into Student (fullname, email, phone_number)
values
('Sinh Vien A', 'a@gmail.com', '123213'),
('Sinh Vien B', 'b@gmail.com', '123213'),
('Sinh Vien C', 'c@gmail.com', '123213')
go

insert into Class (name)
values
('Lop Hoc A'),
('Lop Hoc B')
go

insert into ClassMember(class_id, rollno)
values
(1, 1),
(1, 2),
(2, 3)
go

insert into Teacher (fullname, email, phone_number)
values
('Giao Vien A', 'gva@gmail.com', '123213'),
('Giao Vien B', 'gvb@gmail.com', '123213'),
('Giao Vien C', 'gvc@gmail.com', '123213')
go

insert into Subject (name, session_count)
values
('Lap Trinh C', 40),
('SQL Server', 36)
go

insert into Schedule (class_id, subject_id, teacher_id, start_date, end_date)
values
(1, 1, 1, '2022-02-18', '2022-03-18'),
(2, 2, 1, '2022-03-22', '2022-04-22')
go

insert into Attendence (schedule_id, rollno, status_1, date_1, status_2, date_2, note)
values
(1, 1, 'P', '2022-02-20 13:45', 'P', '2022-02-20 17:00', 'OK'),
(1, 2, 'P', '2022-02-20 13:45', 'A', '2022-02-20 17:00', 'Chon Hoc')
go

-- proc
create proc proc_view_student_list
	@classId int
as
begin
	select Student.rollno, Student.fullname, Class.name 'Class Name'
	from Student, Class, ClassMember
	where Student.rollno = ClassMember.rollno
		and Class.id = ClassMember.class_id
		and Class.id = @classId
end

exec proc_view_student_list 1
exec proc_view_student_list 2

-- Thong tin hien thi: rollno (Student), student name (Student), class name (Class),  Teacher name (Teacher), Subject Name (Subject), Status 1 (Attendence), Status 2 (Attendence)
create proc proc_find_attendence
	@classId int,
	@subjectId int
as
begin
	select Student.rollno, Student.fullname, Class.name 'Class Name', Teacher.fullname 'Teacher Name', Subject.name 'Subject Name', Attendence.status_1, Attendence.status_2
	from Student, Attendence, Class, Schedule, Teacher, Subject
	where Student.rollno = Attendence.rollno
		and Attendence.schedule_id = Schedule.id
		and Schedule.subject_id = Subject.id
		and Schedule.teacher_id = Teacher.id
		and Schedule.class_id = Class.id
		and Schedule.class_id = @classId
		and Schedule.subject_id = @subjectId
end

exec proc_find_attendence 1, 1
exec proc_find_attendence 1, 2

-- trigger
create trigger trigger_instead_of_student on Student
instead of delete
as
begin
	-- Foreign: Class Member
	delete from ClassMember where rollno in (select rollno from deleted)

	-- Foreign: Attendence
	delete from Attendence where rollno in (select rollno from deleted)

	-- Primary key
	delete from Student where rollno in (select rollno from deleted)
end

select * from Student
select * from ClassMember
select * from Attendence

delete from Student where rollno = 1