--- 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
|
