Báo cáo Đồ Án Hệ Quản Trị Cơ Sở Dữ Liệu Hệ Thống || Quản Lý Sách Thư Viện || SQL

Dưới Đây là 1 số mấu bài tập mà chúng nhóm mình làm xem rồi tham khảo , bài tập khác đầy đủ gồm hơn 50 bài mỗi thành viên gầm 5 bài : Bài 1 : Lớp có sinh viên mượn ít sách nhấtBài 2 : Tính tổng sinh viên của từng lớp đã mượn sáchBài 3 : Chỉnh sửa cơ sở dữ liệu để có thể in ra phiếu của người mượn (số lượng mượn, tên sách, tác giá, thời gian …)Bài 4 : khởi tạo trigger Update Số Lượng sách trong bảng Sophieu và sachBài 5 : Tạo procedure cho câu INSERT để thêm sinh viên vào bảng lớpBài 1 : Tác Giả có nhiều sách được mượn nhấtBài 2 : Tính tổng số sách mượn của từng sinh viên theo lớpBài 3 : Tạo thêm cột trong bảng để tính toán số ngày đã mượn sách của từng sinh viênBài 4 : Tạo procedure để xem thông tin sinh viên đã mượn sách trong bảng số phiếuBài 5 : Tạo Trigger cập nhật thông tin sinh viên có soluongmuon trên bảng số phiếuBài 1 : Tác Giả có nhiều sách được mượn nhấtBài 2 : Tính tổng số sách mượn của từng sinh viên theo lớpBài 3 : Tạo thêm cột trong bảng để tính toán số ngày đã mượn sách của từng sinh viênBài 4 : Tạo procedure để xem thông tin sinh viên đã mượn sách trong bảng số phiếuBài 5 : Tạo Trigger cập nhật thông tin sinh viên có soluongmuon trên bảng số phiếuBài 1 : Sinh viên mượn sách dưới 20 tuổiBài 2 : Update số lượng sách sau khi sinh viên mượn thêm sáchBài 3 : Thêm cột tính tổng số sinh viên trên bảng lớp. Tính tổng số sinh viên các lớp đã mượn sáchBài 4 : Từ bảng số phiếu hiển thị thông tin mã sinh viên, mã phiếu, ngày mượn, ngày trả của những sinh viên mượn 2 quyển sáchBài 5 : Tạo 1 trigger để xóa số lượng sách mượnBài 1 : Lớp có sinh viên mượn nhiều sách nhấtBài 2 : Tổng lượng sách đã mượn theo nhà xuất bảnBài 3 : Tạo lập quyền hạn mượn sách của từng sinh viên (2 – 5 quyểntháng). Tính toán số lượng sinh viên đã mượn quá số lượng quy định

Trang 1

TRƯỜNG ĐẠI HỌC ĐIỆN LỰC

KHOA CÔNG NGHỆ THÔNG TIN

1 Nguyễn Quốc Tuấn Tạo database và tạo table

2 Nguyễn Thị Thu Nhập dữ liệu vào bảng sach

3 Nguyễn Trí Tuệ Nhập dữ liệu vào bảng lop

4 Lê Thanh Tùng Nhập dữ liệu vào bảng sinhvien

5 Đinh Doãn Việt Nhập dữ liệu vào bảng sophieu, làm báo cáo

6 Nguyễn Quang Vinh In báo cáo

7 Phạm Xuân Tùng Chỉnh sửa báo cáo

8 Đặng Ngọc Tú Kiểm tra và chỉnh sửa dữ liệu bảng sach , lop

9 Nguyễn Văn Thùy Tạo kết nối diagram

10 Phùng Quốc Vương Chuẩn hóa database

11 Nguyễn Xuân Tuấn Kiểm tra chỉnh sửa dữ liệu bảng sinhvien,sophieu

Hà Nội, tháng 03 năm 2019

Trang 2

5 Đinh Doãn Việt

Trang 3

-Phần

1 -I Mô tả bài toán

1 Sự cần thiết của bài toán

a Sự cần thiết của bài toán

Đối với thư viện , CSDL cho phép ta kiểm soát lượng dữ liệu sách, người đọc theo thời gian

mà không bị nguy cơ mất mát dữ liệu Trong thư viện, có rất nhiều loại sách với nhiều thể loại khác nhau, nhiều tác giả và có số lượng còn lại trong kho khác nhau Lượng sinh viên cũng vậy,

có nhiều sinh viên với nhiều mã sinh viên khác nhau, lớp và các ngành học khác nhau Cho nên chương trình này cho phép kiểm soát thông tin của thư viện để quản lý và phục vụ cho nhu cầu đọc mượn của người đọc

b Yêu cầu về CSDLYC01 - Cho phép lưu , cập nhật thông tin sinhvien với các thông tin chi tiết

YC02 - Cho phép lưu và xuất thông tin về các loại sách trong thư viện

YC03 - Cho phép tính tổng số lượng sách mượn nhiều nhất theo lớp

YC04 - Cho phép hiển thị sinh viên có lượng sách mượn nhiều nhất

YC05 - Cho phép hiển thị ra sinh viên có số tuổi lớn (nhỏ) hơn

YC06 – Cho phép cập nhật số lượng sách khi có update, delete, insert

YC07 - Cho hiển thị ra lớp có sinh viên mượn nhiều (ít) sách nhất

YC08 - Cho xuất ra thông tin chi tiết của phiếu mượn

YC09 - Hiển thị ra sinh viên mượn nhiều (ít) sách nhất

YC10 - Hiển thị ra lượng sách đã mượn theo tên sinh viên , thời gian …

YC11 - Hiển thị tác giả có lượng sách được mượn nhiều (ít) nhất

YC12 - Tạo quyền hạn mượn cho sinh viên

YC13 - kiểm tra số lượng sách mượn trên bảng sophieu có vượt quá giới hạn mỗi khi update dữ liệu

YC14 - Cho phép hiển thị những phiếu mượn có thời gian nhiều hơn N ngày

YC15 - Cập nhật thêm sinh viên , nếu có sinh viên mới vào lớp

Trang 4

- Bảng sach (masach, tensach, tacgia, namxb, nhaxb, theloai, soluong)

- Bảng lop (malop, makhoa, tenlop)

- Bảng sinhvien (masv, tensv, namsinh, sdt, malop, tuoi)

- Bảng sophieu (maphieu, masv, masach, soluong, ngaym, ngayt)

o Bảng sach (masach, tensach, tacgia, namxb, nhaxb, theloai, soluong) là bảng lưu thông tin của các loại sách có trong thư viện Bảng có các thuộc tinh sau :

 masach là mã nhận diện của từng sách

 tensach là tên của từng quyển sách

 tacgia là tác giả của quyển sách

 namxb là năm xuất bản của sách

 nhaxb là nhà xuất bản của sách

 theloai là thể loại của sách

 soluong là số lượng sách có trong thư viện Khóa chinh – Primary Key là masach

Ứng với YC02

o Bảng lop (malop, makhoa, tenlop) là bảng lưu thông tin các lớp, khoa Bảng có các thuộctinh sau:

 malop là mã nhận diện của từng lớp

 makhoa là mã nhận diện của từng khoa

 tenlop là tên của từng lớpKhóa chinh – Primary Key là malop

o Bảng sinhvien (masv, tensv, namsinh, sdt, malop) là bảng lưu thông tin sinh viên của toàn trường

Bảng có các thuộc tinh sau :

 masv là mã nhận diện của từng sinh viên

 tensv là họ và tên của từng sinh viên

 namsinh là ngày tháng năm sinh của từng sinh viên

 sdt là số điện thoại liên lạc của từng sinh viên

 malop là mã nhận diện lớp của sinh viên đóKhóa chinh – Primary Key là masv

Khóa ngoại – Foreign key là malop Ứng với YC01, YC05

o Bảng sophieu (maphieu, masv, masach, soluongmuon, ngaym, ngayt) là bảng lưu thông tin về việc mượn sách của các sinh viên trong trường và thông tin về thời hạn mượn Bảng có các thuộc tinh sau :

 maphieu là mã nhận diện của từng phiều đối với mỗi lần mượn

 masv là mã nhận diện của sinh viên mượn sách

Trang 5

 masach là mã nhận diện của sách được mượn

 soluongmuon là số lượng sách được sinh viên mượn

 ngaym là ngày tháng năm sách được sinh viên mượn

 ngayt là thời hạn sinh viên phải trả sách

Khóa chinh – Primary key là masv, masachKhóa ngoại – Foreign Key là masv, masachỨng với YC03, YC04,YC06,YC07,YC08,YC09,YC10

 Sơ đồ Diagram :

Trang 6

DROP DATABASE thuvien

CREATE TABLE sach

(

masach NVARCHAR ( 50 ) NOT NULL,

tensach NVARCHAR ( 50 ) NOT NULL,

tacgia NVARCHAR ( 50 ) NOT NULL,

DROP TABLE sach

CREATE TABLE lop

DROP TABLE lop

CREATE TABLE sinhvien

CONSTRAINT pk_masv PRIMARY KEY ( masv ),

CONSTRAINT fk_sinhvien_malop FOREIGN KEY ( malop ) REFERENCES lop ( malop ) ON DELETE CASCADE ON UPDATE

CASCADE

);

DROP TABLE sinhvien

CREATE TABLE sophieu

Trang 7

CONSTRAINT fk_sophieu_masv FOREIGN KEY ( masv ) REFERENCES sinhvien ( masv ) ON DELETE CASCADE ON

-SACH -INSERT INTO sach ( masach , tensach , tacgia , namxb , nhaxb , theloai , soluong )

VALUES ('ta1','tieng anh 1','dh su pham','2016','kim dong','giao trinh','10')

INSERT INTO sach ( masach , tensach , tacgia , namxb , nhaxb , theloai , soluong )

VALUES ('ta2','tieng anh 2','dh su pham','2016','quoc gia','giao trinh','9')

VALUES ('ta3','tieng anh 3','dh bach khoa','2017','dh su pham','giao trinh','11')

VALUES ('c++','ngon ngu lap trinh c','dh su pham','2017','phuong nam','giao trinh','12')

VALUES ('vatly','vat ly dai cuong','dh quoc gia','2018','dh su pham','giao trinh','11')

VALUES ('pldc','phap lat dai cuong','dh luat ha noi','2014','dh su pham','giao trinh','10')

VALUES ('tcc1','toan cao cap 1','nxb thanh nien','1999','dh su pham','giao trinh','5')

VALUES ('dhmt','do hoa may tinh','dh dien luc','2009','dh dien luc','giao trinh','6')

VALUES ('mmt','mang may tinh','thanh nien','2007','thanh nien','giao trinh','7')

VALUES ('KTMT','KIEN TRUC MAY TINH','DH BACH KHOA','2016','PHUONG NAM','GIAO TRINH','8')

-LOP -INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('tc-01','1782','the chat')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('ta-02','ms13','tieng anh')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('cntt','ac14','cong nghe thong tin')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('cnpm','df15','cong nghe phan mem')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('cot','af72','co dien tu')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('h2','ck13','dien')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('qtdn','qt3','kinh te')

INSERT INTO lop ( malop , tenkhoa , tenlop ) VALUES ('cota','ck14t','co ban')

-SINHVIEN -INSERT INTO sinhvien ( masv , tensv , namsinh , sdt , malop )

VALUES ('1781310183','dinh doan viet','1998/01/02','0965062715','tc-01')

INSERT INTO sinhvien ( masv , tensv , namsinh , sdt , malop )

VALUES ('1781310184','nguyen thi thu','1997/01/03','0965062716','ta-02')

VALUES ('1781310185','nguyen quang vinh','1996/01/04','0965062717','cntt')

VALUES ('1781310186','nguyen quoc tuan','1999/01/05','0965062718','cnpm')

VALUES ('1781310187','nguyen tri tue','2000/01/06','0965062719','cot')

VALUES ('1781310188','nguyen xuan tuan','2000/01/07','0965062710','h2')

VALUES ('1781310189','pham xuan tung','1999/01/09','0965062711','qtdn')

VALUES ('1781310190','le thanh tung','1999/01/08','0965062712','cota')

VALUES ('1781310191','nguyen quoc ha','1998/01/10','0965062713','cnpm')

Trang 8

VALUES ('1781310192','nguyen xuan duc','1999/01/11','0965062714','cnpm')

1.Nguyễn Quốc Tuấn :

Bài 1 : Lớp có sinh viên mượn ít sách nhất

Bài 2 : Tính tổng sinh viên của từng lớp đã mượn sách

Bài 3 : Chỉnh sửa cơ sở dữ liệu để có thể in ra phiếu của người mượn (số lượng mượn, tên sách, tác giá, thời gian …)

Bài 4 : khởi tạo trigger Update Số Lượng sách trong bảng Sophieu và sach

Bài 5 : Tạo procedure cho câu INSERT để thêm sinh viên vào bảng lớp

-Bai Tap 1 : Hiển thị danh sách lớp có sinh viển mượn ít sách

Viển' , sophieu soluongmuon AS N'Số Lượng Mượn'

FROM lop , sinhvien , sophieu

FROM sophieu

-Bai tap 2: Tính tống sinh viển cua từng lớp đã mượn sách -Chưa xong

sophieu masv ) AS N'Số SV Mượn Sách' , SUM ( soluongmuon ) AS N'Số Lượng Mượn'

FROM lop , sinhvien , sophieu

Trang 9

-BÀi 3 : Chỉnh sưa cơ sơ dữ liệu để có thể in ra phiểu cua người mượn (số lượng mượn, tển sách, tác giá, thời gian …)

AS

RETURN(

SELECT

sinhvien tensv , sophieu maphieu , sach tensach , sach theloai , sach tacgia , sophieu soluongmuo

n sophieu ngayt , sophieu ngaym

FROM sinhvien , sophieu , sach

AND sinhvien masv = sophieu masv AND sophieu masach = sach masach

sinhvien tensv , sophieu maphieu , sach tensach , sach theloai , sach tacgia , sophieu soluongmuo

n sophieu ngayt , sophieu ngaym

)

-Bài 4 : khơi tạo trigger Update Số Lượng sách trong bang Sophieu và sach

BEGIN

UPDATE sach SET soluong = sach soluong - (SELECT soluongmuon FROM inserted WHERE masach = sach masach )

+ (SELECT soluongmuon FROM deleted WHERE masach = sach masach )

from sach

join deleted on sach masach = deleted masach

END

UPDATE sophieu SET soluongmuon = 3 WHERE maphieu = 157

- Bai 5 : Tạo procedure cho cấu INSERT để thểm sinh viển vào bang lớp

Bang Sinh viển trước khi tạo Procedure

procedure cho cấu INSERT

VALUES( @masv , @tensv , @namsinh , @sdt , @malop )

END

-Gọi procedure thểm sinh viển

EXEC udsThemSinhVien '126802' , N'Nguyể_ n Văn AAA' , '1998' , '0396854725' , 'CNPM2'

Bang Sinh viển sau khi tạo

2.Nguyễn Thị Thu :

Bài 1 : Tác Giả có nhiều sách được mượn nhất

Bài 2 : Tính tổng số sách mượn của từng sinh viên theo lớp

Trang 10

Bài 3 : Tạo thêm cột trong bảng để tính toán số ngày đã mượn sách của từng sinh viên

Bài 4 : Tạo procedure để xem thông tin sinh viên đã mượn sách trong bảng số phiếu

Bài 5 : Tạo Trigger cập nhật thông tin sinh viên có soluongmuon trên bảng số phiếu

Bài 1: Tác giả có nhiều sách được mượn nhất

SELECT TOP 1 tacgia , sum(soluongmuon)

AS ‘ tongsoluongmuon ’

FROM sach , sophieu

WHERE sach.masach = sophieu.masach

GROUP BY tacgia

ORDER BY sum(soluongmuon) DESC

Bài 2 : Tính tổng số sách mượn của từng sinh viên theo lớp

SELECT tensv , sum(soluongmuon), malop

AS ‘ tongsoluongmuon ‘

FROM sinhvien , sophieu

WHERE sinhvien.masv = sophieu.masv

GROUP BY malop, tensv

Bài 3 : Tạo thêm cột trong bảng để tính toán số ngày đã mượn sách của từng sinh viên

ALTER TABLE sophieu

ADD songaym INT

sau khi them 1 cot ta update du lieu vao cot

UPDATE sophieu

SET songaym = DATEDIFF (DAY,ngayt, ngaym)

Trang 11

SELECT masv , tensv, malop

FROM sinhvien, lop

WHERE sinhvien.malop = lop.malop)

SELECT * FROM thongtin_nguoimuon

CREATE PROCEDURE thongtin_SV

SELECT * FROM sinhvien

WHERE masv = @masv

END

EXEC thongtin_SV '1781310183'

Trang 12

WHERE sophieu.masach = sach.masach GROUP BY tacgia)

END

SELECT * FROM sophieu

DROP TRIGGER capnhat_SV

3.Nguyễn Trí Tuệ :

Bài 1 : Sinh viên mượn sách dưới 20 tuổi Bài 2 : Update số lượng sách sau khi sinh viên mượn thêm sách Bài 3 : Thêm cột tính tổng số sinh viên trên bảng lớp Tính tổng số sinh viên các lớp đã mượn sách

Bài 4 : Từ bảng số phiếu hiển thị thông tin mã sinh viên, mã phiếu, ngày mượn, ngày trả của những sinh viên mượn 2 quyển sách

Bài 5 : Tạo 1 trigger để xóa số lượng sách mượn

Bài1: Sinh viên mượn sách dưới 20 tuổi

Code minh họa

SELECT SINHVIEN.MASV,TENSV,MALOP

FROM SINHVIEN,SOPHIEU

WHERE SINHVIEN.MASV = SOPHIEU.MASV

AND ( YEAR ( GETDATE() )- YEAR (NAMSINH) <20 );

Trang 13

Bài2: Update số lượng sách sau khi sinh vien mượn thêm sách

Code minh họa

ALTER TABLE SOPHIEU ADD MUONTHEM int;

cap nhap thong tin

-update so luong sach sau khi muon

UPDATE AS

BEGIN

UPDATE sach SET soluong=sach.soluong - (SELECT MUONTHEM FROM inserted WHERE masach = sach.masach)

Trang 14

BANG TRUOC CAP NHAP

-BANG SAU CAP NHAP

Trang 15

Bài3: Thêm cột tính tổng số sinh viên trên bảng lớp Tính tổng số sinh viên các lớp đã mượn sách

Code minh họa

ALTER TABLE LOP ADD TONGSOSV int;

cap nhap thong tin

TONGSOSV -INSERT INTO LOP VALUES ( 'TC-01','1782','THE CHAT','50' )

INSERT INTO LOP VALUES ( 'TA-02','MS13','TIENG ANH','46' ) INSERT INTO LOP VALUES ( 'CNTT','AC14','CONG NGHE THONG TIN','50' )

MEM','36' )

INSERT INTO LOP VALUES ('COT','AF72','CO DIEN TU','24')

INSERT INTO LOP VALUES ( 'H2','CK13','DIEN','56' )

INSERT INTO LOP VALUES ( 'QTDN','QT3','KINH TE','40' )

INSERT INTO LOP VALUES ( 'COTA','CK14T','CO BAN','26' )

tong sinhvien cac lop da muon sach

-SELECT SINHVIEN.MALOP AS N'Mã Lớp' , LOP.TENLOP

AS N'Tên Lớp' , count (DISTINCT SOPHIEU.MASV)

AS N'Số SV Mượn Sách' , SUM (SOLUONG+MUONTHEM)

Trang 16

Bài4: Từ bảng số phiếu hiển thị thông tin mã sinh viên, mã phiếu, ngày mượn, ngày trả của những

sinh viên mượn 2 quyển sách

Code minh họa:

CREATE VIEW VIEW_sophieu

AS (

SELECT MASV,MAPHIEU,NGAYT,NGAYM,SOLUONG

FROM SOPHIEU

Bài5: Tạo 1 trigger để xóa số lượng sách mượn

Code minh họa:

FOR DELETE AS

BEGIN

UPDATE sach SET soluong = soluong - (

Trang 17

4.Lê Thanh Tùng :

Bài 1 : Lớp có sinh viên mượn nhiều sách nhấtBài 2 : Tổng lượng sách đã mượn theo nhà xuất bảnBài 3 :Tạo lập quyền hạn mượn sách của từng sinh viên (2 – 5 quyển/tháng) Tính toán số lượng sinh viên đã mượn quá số lượng quy định

Bài 4 :Từ bảng sach hiển thị ra masach,tensach,soluong có Năm xuất bản vào năm 2016Bài 5 : Tạo 1 trigger kiểm tra số lượng sách mượn trên bảng sophieu có vượt quá giới hạn mỗi khi update dữ liệu

BÀI 1 : HIỂN THỊ LỚP CÓ SINH VIÊN MƯỢN NHIỀU SÁCH NHẤT

SELECT sinhvien.masv,tensv,lop.malop,tenlop,sum(soluongmuon)as'soluongmuon'

FROM sophieu,sinhvien,lop

WHERE sophieu.masv=sinhvien.masv and lop.malop=sinhvien.malop

and lop.malop in(SELECT malop

FROM sinhvien,sophieu

WHERE sinhvien.masv=sophieu.masv GROUPBY malop

HAVINGSUM(soluongmuon)>=all(SELECTsum(soluongmuon)

FROM sophieu,sinhvien,LOP WHERE sophieu.masv=sinhvien.masv

and lop.MALOP=SINHVIEN.MALOP GROUPBY lop.malop))

GROUPBY sinhvien.masv,tensv,lop.malop,tenlop ;

BÀI 2 : Tổng lượng sách đã mượn theo nhà xuất bản

SELECT nhaxb,sum(soluongmuon)as'tongmuon'

FROM sach, sophieu

WHERE sach.masach = sophieu.masach

GROUPBY nhaxb

ORDERBY [tongmuon] DESC;

BÀI 3: Tạo lập quyền hạn mượn sách của từng sinh viên (2 – 5 quyển/tháng)

Tính toán số lượng sinh viên đã mượn quá số lượng quy định

CREATETABLE themuon(

CONSTRAINT pk_mathe PRIMARYKEY(mathe,masv),

CONSTRAINT fk_themuon_masv FOREIGNKEY (masv)REFERENCES sinhvien(masv)ONDELETECASCADE

ONUPDATECASCADE)

Trang 18

INSERTINTO themuon VALUES ('1','1781310183','157','5','2018/1/2','2018/1/2')

drop table themuon

SELECT sinhvien.masv,tensv,count(soluongmuon)AS'soluongthua'

FROM sinhvien,sophieu,themuon

WHERE sinhvien.masv = sophieu.masv and sinhvien.masv = themuon.masv

and soluongmuon > gioihanmuon

GROUPBY sinhvien.masv,tensv ;

Bài 4 : Từ bảng sach hiển thị ra masach,tensach,soluong có Năm xuất bản vào năm 2016

CREATEVIEW v_sach AS (

SELECT masach,tensach,soluong,namxb

FROM sach

GROUPBY masach,tensach,soluong,namxb )

DECLARE @namxb INT

SET @namxb = 2016

SELECT*

FROM v_sach

WHEREYEAR(namxb)= @namxb

ORDERBY [namxb] DESC

Bài 5 : Tạo 1 trigger kiểm tra số lượng sách mượn trên bảng sophieu có vượt quá giới hạn mỗi khi update dữ liệu

CREATEVIEW v_sophieu AS (

SELECTDistinct sophieu.masv, sinhvien.tensv, soluongmuon

FROM sophieu, sinhvien

WHERE sophieu.masv = sinhvien.masv

GROUPBY sophieu.masv, sinhvien.tensv,soluongmuon)

DROP VIEW v_sophieu

SELECT*FROM v_sophieu

SELECT*FROM themuon,sinhvien where sinhvien.masv=themuon.masv

TRIGGER CREATETRIGGER nhap_slmuon ON sophieu

Định dạng
Số trang	37
Dung lượng	2,15 MB