Xem mẫu
- 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í.
- Mục Lục
I- Tạo các bảng (table) ............................................................................................. 3
II- Phân người, nhóm người tác động lên cơ sở dữ liệu. ......................................... 6
III- TRIGGER .......................................................................................................... 8
Qltv_sach VS qltv_nhaxb ........................................................................................ 8
Qltv_sach VS qltv_tacgia......................................................................................... 9
Qltv_sach VS qltv_theloaisach ................................................................................ 9
2. Bảng qltv_nhaxb ................................................................................................ 11
3. Bảng qltv_theloaisach ........................................................................................ 12
4. Bảng qltv_tacgia ................................................................................................ 14
5. Bảng qltv_docgia ............................................................................................... 15
6. Bảng loại độc giả............................................................................................... 17
7. Bảng phiếu mượn sách ....................................................................................... 18
8. Bảng thẻ thư viện .............................................................................................. 20
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()))
- 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))
- 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))
nguon tai.lieu . vn
