Đề tài Xây dựng cơ sở dữ liệu chạy trên máy chủ SQL Sever 2000 cho thư viện sách, báo, tạp chí

Tên đề tài: Xây dựng cơ sở dữ liệu chạy trên máy chủ  SQL Sever 2000 cho thư viện sách, báo, tạp chí. Bài làm: Cơ sở dữ  liệu được ứng dụng trong rất nhiều hệ  thống, từ các phần mềm quản lý, phần mềm kế  toán, các website,… Với nhiệm vụ lớn lao đó các nhà sản xuất đã xây dựng ra rất nhiều các hệ quản trị cơ sở dữ liệu khác nhau, mỗi hệ quản trị CSDL có một đặc điểm khác nhau (orale, Ms SQL Sever, My SQL,…), tùi theo từng nhu cầu công việc mà các nhà sử dụng, sử dụng các cơ sở dữ liệu khác nhau. Thư viện sách là nơi quản lý đầu sách, quản lý độc giả, quản lý các thông tin về mượn và trả sách. Để đáp ứng được nhu cầu của việc phát triển công nghệ thông tin như hiện nay và để ứng dụng công nghệ thông tin vào cuộc sống, để thay đổi việc quản lý củ như ngày xưa, quản lý bằng giấy tờ, việc quản lý bằng máy vi tính tiết kiệm được rất nhiều tiền của của thư viện đó, bằng việc đi tìm từng quyển sách trong list danh sách đầu sách, chúng ta chỉ việc ngồi trước máy vi tính và gõ bất kỳ thông tin gì của đầu sách đó thì chúng ta sẽ tìm ra quyển sách của mình cần. Với việc ứng dụng như thế nên chúng em chọn đề tài quản lý thư viện để làm đề tài bài tập lớn môn hệ quản trị CSDL, vì kiến thức còn kém cỏi, chưa đi sâu vào thực tế, nên chắc chắn bài tập lớn của bọn em còn nhiều sai sót trong mọi mặt. Chân thành cám ơn thầy giáo Trịnh Viết Cường đã là người chỉ dẫn cho chúng em trong môn học này, kính chúc thầy mạnh khỏe và thành công trong cuộc sống.      I- Tạo các bảng (table) Một CSDL có thể  có một bảng hoặc nhiều bảng, nếu một CSDL có nhiều bảng thì nó được liên kết với nhau thông qua các khóa, với việc liên kết đó thì sẽ rất dễ tạo ra sự thừa hoặc thiếu dữ liệu, đi cụ thể với cơ sở dữ liệu của chúng em như một nhà xuất bản thì có thể xuất bản ra nhiều quyển sách, và một quyển sách chưa bao giờ được sản xuất bởi nhiều nhà xuất bản trong một lần “In”, (có thể các lần tái bản khác thì sẽ do nhà xuất bản khác in), một quyển sách có thể do nhiều tác giả viết, và một tác giả thì có thể viết ra nhiều quyên sách,… Quay lại với việc đi xây dựng cơ sở dữ liệu của chúng em, chúng em đi xây dựng 8 bảng để chứa các trường thông tin khác nhau phục vụ cho việc quản lý thư viện. Và tất nhiên khi đi xây dựng các bảng chúng ta phải đi xây dựng nơi chứa các cơ sở dữ liệu đó, ta có thể gọi đó là Database, tạo database bằng câu lệnh:  Create database qltv on primary(name=qltv_data, filename=’ --Đường dẫn cơ  sở dữ liệu\qltv_data.mdf’, size=4mb, maxsize=20mb, filegrowth=2mb) Log On (name=qltv_log,filename=’--Đường dẫn cơ sở dữ  liệu\qltv_log.ldf’ Size=2mb, Maxsize=10mb, Filegrouwth=1mb) Go  Các bảng của cơ  sở dữ liệu: Qltv_sach: Create table qltv_sach (masach int primary key, Tensach nchar(50) not null, Matheloai tinyint not null, Matacgia tinyint not null, Manxb tinyint not null, Namxuatban datetime , Sotrang int, Giatien char(10),--Số  tiền bằng không nếu sách đó là  sách được  tặng Soquyentrongkho tinyint not null, Ngonngu char, Ngaynhap char)  Qltv_nhaxb: Create table qltv_nhaxb (manxb tinyint primarykey, Tennxb nchar(50) not null, Mota nvarchar(100));  Qltv_docgia: Create table qltv_docgia (Madocgia int primarykey, Hovaten nchar(50) not null, Maloaidocgia tinyint not null, Diachi nvarchar(80), Ngaysinh datetime not null, Gioitinh nchar(5) not null, Noicongtac nvarchar(80), Hinhanh text)  Qltv_loaidocgia: Create table qltv_loaidocgia (Maloaidocgia tinyint primary key, Tenloaidocgia nchar(50) not null, Mota nvarchar(100))  Qltv_phieumuonsach: Create table qltv_phieudangky (maphieu int primary key, Mathemuon int not null, Ngaymuon datetime not null, Ngaytra datetime not null, Masach int not null)   Qltv_theloaisach: Create table qltv_theloaisach (Matheloai tinyint primarykey, Tentheloai nchar(50) not null, Mota nchar(50))  Qltv_thethuvien: Create table qltv_thethuvien (Mathemuon int primary key, Madocgia int not null, Ngaylapthe datetime not null, Ngayhethan datetime not null)  Qltv_tacgia: Create table qltv_tacgia (matacgia tinyint primary key, Tentacgia nchar(50) not null, Mota nvarchar(80))  II- Phân người, nhóm người tác động lên cơ sở dữ liệu. Một cơ sở dữ  liệu muốn được an toàn, đảm bảo tính toàn vẹn của CSDL, thì phải phân ra các người, nhóm các người dùng, người nào được làm gì trong CSDL, không phải tất cả mọi người đều được ghi, xóa, sửa chữa trong CSDL đó, nên chúng ta đi phân ra người dùng và nhóm người dùng trong CSDL đó:   1. Qltv_root: có mọi quyền trên csdl đó và ta đặt bằng thuộc tính role db_owner bây giờ tài khoản này sẽ có mọi quyền trên csdl này, bao gồm quyền phát biểu: create table, create view, create SP, create default  , create rule,… Và các quyền về đối tượng. Exec sp_addrolemember ‘db_owner’,’qltv_root’ Ta có thể tạo quyền bằng chế độ 1 người dùng, ta có thể  dùng quyền Administrator đăng nhập vào windows để đăng nhập vào CSDL, vì người này là người cài ra hệ quản trị CSDL thì người đó sẽ có mọi quyền trong CSDL đó.  2. Qltv_admin: tài khoản này ta tạo ra nhằm làm tài khoản của người quản lý thư viện sách, nó có tất cả các quyền về đối tượng như xem, xóa, sửa trên tất cả các bảng của CSDL qltv, nhưng nó không có một số quyền như backup, restore,… CSDL đó. Grant all on qltv_loaidocgia to qltv_admin Grant all on qltv_nhaxb to qltv_admin Grant all on qltv_phieumuonsach to qltv_admin Grant all on qltv_sach to qltv_admin Grant all on qltv_sachmuon to qltv_admin Grant all on qltv_tacgia to qltv_admin Grant all on qltv_theloaisach to qltv_admin Grant all on qltv_thethuvien to qltv_admin Tất nhiên ta cũng có  thể cấp các quyền này bằng cách dung giao diện  đồ họa bằng cách bấm chuột phải vào bảng cần cấp quyền rồi chọn properties > permissions rồi cấp quyền các đối tượng tưng ứng.  Tùi theo nhu cầu công việc ta có thể tạo một nhóm người dùng chứ không phải duy nhất một người dùng có các quyền qltv_admin, và gán cho những người quản lý thư viện đó là  thành viên của nhóm đó. 3. Qltv_mem : Những người dùng trong nhóm người dùng này có các quyền xem một số column ở một số bảng, nhóm người dùng này như một độc giả bình thường lên để tra xem có quyển sách này hay không, nên nhóm độc giả này chỉ có quyền xem một số bảng như bảng sách, tác giả, nhà xuất bản, và ở bảng sách thì không được phép xem giá tiền của bảng đó. Ta có thể cấp quyền cho người dùng qltv_mem này dễ dàng bằng giao diện đồ họa hoặc bằng câu lệnh. Grant select(masach ,tensach, matheloai, matacgia, manxb, namxuatban, sotrang, tinhtrang, ngonngu) on qltv_sach to qltv_mem Grant select on qltv_nhaxb to qltv_mem Grant select on qltv_tacgia to qltv_mem Grant select on qltv_theloaisach to qltv_mem  Tất nhiên sẽ có  nhiều cách để đến kết quả này ví dụ như cấp qua role, cấp qua views,…  III- TRIGGER Để đảm bảo toàn vẹn cơ sở dữ liệu này, ví dụ như ta không thể nhập một quyển sách nếu mã tác giả của quyển sách đó không tồn tại trong bảng mã tác giả,… Ta không thể cho mượn một quyển sách mà quyển sách đó không tồn tại trong bảng sách,… Bây giờ ta đi cụ thể đến từng trigger. 1. Bảng qltv_sach  Ta tạo 1 trigger có  nhiệm vụ kiểm soát việc nhập sách vào kho, nếu nhập sách vào kho mà chưa tồn tại nhà xuất bản, tác giả, thể loại sách trong các bảng qltv_nhaxb, qltv_tacgia, qltv_theloaisach thì trigger sẽ làm nhiệm vụ xóa cái dòng vừa nhập đi bằng câu lệnh Rollback transaction, và in ra dòng chử tương ứng ở câu lệnh print.  Câu lệnh tổng quát. Qltv_sach VS qltv_nhaxb create trigger insert_sach_nhaxb -- Tên của trigger on qltv_sach-- tác động lên bản qltv_sach for insert-- Với thuộc tính insert as if(select count(*) from inserted,qltv_nhaxb where inserted.manxb=qltv_nhaxb.manxb)=0-- Nếu manxb ở bảng inserted( bảng inserted là bảng mà khi ta chèn thuộc tính ở bảng qltv_sach tạo ra) mà không bằng manxb ở bảng qltv_nhaxb thì sẽ làm câu lệnh bên dưới. begin print 'Khong ton tai nha xuat ban nay' -- In ra dòng thong báo rollback transaction -- Xóa những dòng vừa insert vào. End Qltv_sach VS qltv_tacgia create trigger insert_sach_tacgia on qltv_sach for insert as if(select count(*) from inserted,qltv_tacgia where inserted.matacgia=qltv_qltv_tacgia.matacgia)=0 begin print 'Khong ton tai tac gia nay' rollback transaction . End Qltv_sach VS qltv_theloaisach create trigger insert_sach_theloaisach on dbo.qltv_sach for insert as if(select count(*) from inserted,qltv_theloaisach where inserted.matheloai=qltv_theloaisach.matheloai)=0 begin print 'Khong ton tai the loai sach nay' rollback transaction end  Tạo trigger có nhiệm vụ không thể nhập được năm xuất bản của quyển sách lớn hơn năm hiện tại. Nếu nhập năm xuất bản lớn hơn năm hiện tại thì sẽ xóa cái dòng vừa nhập vào. Câu lệnh như sau:  create trigger insert_sach_namxuatban on qltv_sach for insert as declare @nxb datetime select @nxb=namxuatban from inserted if(year(@nxb)>=year(getdate())) begin print 'Nam xuat ban bat buoc phai nho hon hoac bang nam hien tai' rollback transaction end  Ta để chế độ mặc  định cho ngày nhập ở bảng sách bằng cách lấy ngày giờ hệ thống Câu lệnh như sau: CREATE DEFAULT ngaynhap AS getdate() Bây giờ chế  độ mặc định nằm ở qltv>defaults Nhấp vào cái default ta vừa tạo chọn properties> bind columns rồi add cột qltv_sach.ngaynhap  vào. Vậy khi ta nhập sách vào kho, ở column ta không cần nhập mà nó sẽ lấy ngày giờ của hệ thống.   2. Bảng qltv_nhaxb  Tạo 1 trigger có nhiệm vụ xóa tất cả các thuộc tính trong bảng qlvt_sach nếu mà có manxb trùng với manxb của bảng qltv_nhaxb nếu ta xóa trong bảng qltv_nhaxb.  Câu lênh như sau: create trigger delete_manhaxb on qltv_nhaxb for delete as begin delete qltv_sach from deleted where qltv_sach.manxb=deleted.manxb print 'Da xoa tuong ung cac thuoc tinh trong bang sach ma co ma nha xuat ban tuong ung' end  Tạo trigger có tác dụng nếu thay đổi thuộc tính mã nhà xuất bản ở bảng qltv_nhaxb thì mã nhà xuất bản tương ứng ở bảng qltv_sach cũng được thay đổi theo. Câu lệnh: CREATE TRIGGER update_manhaxuatban ON qltv_nhaxb FOR UPDATE AS IF(@@ROWCOUNT=0) BEGIN       PRINT 'Bang nha xuat ban khong có du lieu’       RETURN END IF UPDATE(manxb) BEGIN       UPDATE t1 SET t1.manxb=t3.manxb       FROM qltv_sach t1, DELETED t2, INSERTED t3       WHERE t1.manxb=t2.manxb       PRINT 'Dong thoi sua doi o bang qltv_sach'  END  3. Bảng qltv_theloaisach Tạo 1 trigger có nhiệm vụ xóa tất cả các thuộc tính trong bảng qlvt_sach nếu mà có matheloai trùng với matheloai của bảng qltv_theloaisach nếu ta xóa trong bảng qltv_theloaisach.  create trigger delete_matheloai on qltv_theloasach for delete as begin delete qltv_sach from deleted where qltv_sach.matheloai=deleted.matheloai print 'Da xoa tuong ung cac thuoc tinh trong bang sach ma co ma nha the loai tuong ung' end Tạo trigger có tác dụng nếu thay đổi thuộc tính mã thể loại ở bảng qltv_theloaisach thì mã thể loại sách tương ứng ở bảng qltv_sach cũng được thay đổi theo. Câu lệnh: CREATE TRIGGER update_matheloaisach ON qltv_theloaisach FOR UPDATE AS IF(@@ROWCOUNT=0) BEGIN       PRINT 'Bang ma the loai sach khong có du lieu’       RETURN END IF UPDATE(matheloai) BEGIN       UPDATE t1 SET t1.matheloai=t3.matheloai       FROM qltv_sach t1, DELETED t2, INSERTED t3       WHERE t1.matheloai=t2.matheloai       PRINT 'Dong thoi sua doi o bang qltv_sach'  END  4. Bảng qltv_tacgia  Tạo 1 trigger có nhiệm vụ xóa tất cả các thuộc tính trong bảng qlvt_sach nếu mà có matacgia trùng với matacgia của bảng qltv_tacgia nếu ta xóa trong bảng qltv_tacgia. create trigger delete_matacgia on qltv_tacgia for delete as begin delete qltv_sach from deleted where qltv_sach.matacgia=deleted.matacgia print 'Da xoa tuong ung cac thuoc tinh trong bang sach ma co ma tac gia tuong ung' end  Tạo trigger có tác dụng nếu thay đổi thuộc tính mã tác giả ở  bảng qltv_tacgia thì mã thể loại sách tương ứng ở bảng qltv_sach cũng được thay đổi theo. Câu lệnh: CREATE TRIGGER update_matacgia ON qltv_tacgia FOR UPDATE AS IF(@@ROWCOUNT=0) BEGIN       PRINT 'Bang tac gia khong co du lieu’       RETURN END IF UPDATE(matacgia) BEGIN       UPDATE t1 SET t1.matacgia=t3.matacgia       FROM qltv_sach t1, DELETED t2, INSERTED t3       WHERE t1.matacgia=t2.matacgia       PRINT 'Dong thoi sua doi o bang qltv_sach'  END      5. Bảng qltv_docgia Tạo trigger có nhiệm vụ kiểm soát việc nhập dữ liệu vào bảng qltv_docgia nếu nhập độc giả mà loại độc giả  không tồn tại trong bảng qltv_loaidocgia thì sẽ  in ra câu thông báo và xóa những gì bạn vừa nhập Câu lệnh như sau: create trigger insert_docgia_maloaidocgia on qltv_docgia for insert as if(select count(*) from inserted,qltv_loaidocgia where inserted.maloaidocgia=qltv_loaidocgia.maloaidocgia)=0 begin print 'Khong ton tai loai doc gia o bang qltv_maloaidocgia' rollback transaction end  Một người phải từ  5 tuổi trở lên mới được làm thẻ thư  viện nên ta tạo một trigger bảo đảm người đó có  số tuổi lớn hơn 5. Nên bảng ngày sinh phải có  số tuổi là ngày/tháng/năm  hiện tại trừ  đi ngày/tháng/năm sinh của người đó phải lớn hơn 5 thì  ta mới cho đủ tuổi làm thẻ thư viện. Giả sử rằng 1 năm có 365 ngày. Câu lệnh như sau: CREATE trigger insert_docgia_ngaysinh on qltv_docgia for insert as declare @ns datetime select @ns=ngaysinh from inserted if(datediff(dd,@ns,getdate()))<5*365 begin print 'Oh! Chua du tuoi lam the thu vien dau' rollback transaction end  6. Bảng loại  độc giả Tạo 1 trigger có nhiệm vụ xóa tất cả các thuộc tính trong bảng qlvt_docgia nếu mà có maloaidocgia trùng với maloaidocgia của bảng qltv_docgia nếu ta xóa trong bảng qltv_loaidocgia. create trigger delete_maloaidocgia on qltv_loaidocgia for delete as begin delete qltv_docgia from deleted where qltv_docgia.maloaidocgia=deleted.maloaidocgia print 'Da xoa tuong ung cac thuoc tinh trong bang doc gia ma co ma loai doc gia tuong ung’ end Tạo trigger có tác dụng nếu thay đổi thuộc tính mã loại độc giả ở bảng qltv_maloaidocgia thì mã loại độc giả tương ứng ở bảng độc giả cũng được thay đổi theo. Câu lệnh: CREATE TRIGGER update_maloaidocgia ON qltv_loaidocgia FOR UPDATE AS IF(@@ROWCOUNT=0) BEGIN       PRINT 'Bang loai doc gia khong co du lieu'       RETURN END IF UPDATE(maloaidocgia) BEGIN       UPDATE t1 SET t1.maloaidocgia=t3.maloaidocgia       FROM qltv_docgia t1, DELETED t2, INSERTED t3       WHERE t1.maloaidocgia=t2.maloaidocgia       PRINT 'Dong thoi sua doi o bang qltv_docgia'  END  7. Bảng phiếu mượn sách Tạo trigger có nhiệm vụ kiểm soát nếu ngày mượn nhập vào lớn hơn ngày trả thì sẽ in ra thông báo và xóa dòng vừa mới nhập đi  CREATE trigger insert_phieumuonsach_ngaymuon_ngaytra on qltv_phieumuonsach for insert as declare @nm datetime declare @nt datetime select @nm=ngaymuon,@nt=ngaytra from inserted if(datediff(dd,@nm,@nt))<0 begin print 'Ngay muon sach phai nho hon ngay tra sach' rollback transaction end  Tạo trigger kiểm tra nếu nhập một quyển sách ở bảng phiếu mượn sách mà quyển sách đó không tồn tại trong bảng sách thì sẽ in ra thông báo và xóa dòng vừa nhập đi Câu lệnh : CREATE trigger insert_phieumuonsach_ngaymuon_ngaytra on qltv_phieumuonsach for insert as declare @nm datetime declare @nt datetime select @nm as ngaymuon,@nt as ngaytra create trigger insert_phieumuonsach_masach on qltv_phieumuonsach for insert as if(count(*) from qltv_sach,inserted where qltv_sach.masach=inserted.masach)=0 begin print 'Khong ton tai quyen sach nay o bang qltv_sach' rollback transaction end  8. Bảng thẻ  thư viện Ta tạo một trigger có  nhiệm vụ khi nhập vào bảng qltv_thethuvien thì sẽ  kiểm tra điều kiện nếu ngày/tháng/năm lập thẻ mà  lớn hơn ngày/tháng/năm của ngày hết hạn thẻ thì  sẽ xóa cột vừa nhập vào và in ra dòng thông báo  Câu lệnh : CREATE trigger insert_themuon_ngaylapthe_hansudung on qltv_thethuvien for insert as declare @nlt datetime declare @nhh datetime select @nlt=ngaylapthe,@nhh=ngayketthuc from inserted if(datediff(dd,@nlt,@nhh))<0 begin print 'Nhap ngay het han phai lon hon ngay lap the' rollback transaction end  Taoj trigger có nhiệm vụ kiểm tra sự nhập vào của bảng  qltv_thethuvien xem nếu nhập vào 1 độc giả mà độc giả  đó không tồn tại trong bảng qltv_docgia thì sẽ  đưa ra thông báo và rollback transaction. Câu lệnh: create trigger insert_thethuvien_docgia on qltv_thethuvien for insert as if(select count(*) from inserted,qltv_docgia where qltv_docgia.madocgia=inserted.madocgia)=0 begin print 'Khong ton tai doc gia nay trong bang qltv_docgia' rollback transaction end