Đề tài Xây dựng cơ sở dữ liệu quản lý thư viện
Bạn đang xem trước 20 trang mẫu tài liệu Đề tài Xây dựng cơ sở dữ liệu quản lý thư viện, để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên
TRƯỜNG ĐẠI HỌC GIAO THÔNG VẬN TẢI
KHOA CÔNG NGHỆ THÔNG TIN
MÔN HỌC CƠ SỞ DỮ LIỆU
---------o0o---------
BÁO CÁO BÀI TẬP LỚN
QUẢN LÝ THƯ VIỆN
Sinh viên thực hiện:
Trần Thanh Hải (MSV: 1203999)
Lớp: Công nghệ phần mềm K53
Giáo viên hướng dẫn :
Giảng viên : Đặng Thị Thu Hiền
Bộ môn: mạng máy tính và hệ thống thông tin
Hà Nội, ngày tháng năm
LỜI NÓI ĐẦU
Trong nhiều năm gần đây, thuật ngữ Cơ sở dữ liệu - Database đã trở nên quen thuộc trong nhiều lĩnh vực. Các ứng dụng tin học vào quản lý ngày càng nhiều và đa dạng, hầu hết các lĩnh vực kinh tế, xã hội… đều đã ứng dụng các thành tựu mới của tin học vào phục vụ công tác chuyên môn của mình. Chính vì lẽ đó mà ngày càng nhiều người quan tâm đến thiết kế, xây dựng và ứng dụng cơ sở dữ liệu (CSDL).
CSDL và công nghệ CSDL đã có những tác đông to lớn trong việc phát triển sử dụng máy tính. Có thể nói rằng CSDL ảnh hưởng đến tất cả các nơi có sử dụng máy tính:
Kinh doanh (thông tin về sản phẩm, khách hàng,…)
Giáo dục (thông tin về sinh viên, giáo viên, điểm,…)
Y tế (thông tin về bệnh nhân, thuốc,…)….
và rất rất nhiều lĩnh vực khác.
CSDL là một hệ thống các thông tin có cấu trúc được lưu trữ trên các thiết bị thông tin thứ cấp (như băng từ, đĩa từ…), để thỏa mãn yêu cầu khai thác thông tin đồng thời của nhiều người sử dụng / nhiều chương trình ứng dụng với những mục đích khác nhau.
Từ khái niệm trên chúng ta thấy rõ ưu điểm nổi bật của CSDL:
Giảm sự trùng lắp thông tin xuống mức thấp nhất và do đó đảm bảo được tính nhất quán và toàn vẹn dữ liệu.
Đảm bảo dữ liệu có thể được truy xuất theo nhiều cách khác nhau.
Khả năng chia sẻ thông tin cho nhiều người sử dụng và nhiều ứng dụng khác nhau…
Do vậy việc tìm hiểu về CSDL là rất cần thiết .
Để tìm hiểu các vấn đề trên, em đã chọn đề tài “xây dựng cơ sở dũ liệu quản lý thư viện”.
Nội dung báo cáo gồm các phần chính sau đây:
Chương I: Thiết kế cơ sở dữ liệu và mô hình thực thể liên kết
Chương II: Chuyển đổi sang mô hình dữ liệu quan hệ.
Chương III: Xác định khóa
Chương IV: Chuẩn hóa lược đồ quan hệ
Chương V: Câu lệnh SQL
Trong quá trình làm bài, em đã rất cố gắng đựa vào những kiến thức đã học và tìm hiểu thực tế của việc quản lý thư viện để hoàn thiện bài hơn song không thể tránh khỏi những sai sót. Do vậy em rất mong nhận được những ý kiến đóng góp để bài được hoàn thiện hơn.
MỤC LỤC
LỜI NÓI ĐẦU2
Chương I: Thiết kế CSDL và mô hình thực thể liên kết4
1.1 Giới thiệu sơ lược về CSDL quản lý thư viện4
1.2 Các thực thể và thuộc tính4
1.3 Xác định ràng buộc toàn vẹn6
1.4 Xác định phụ thuộc hàm9
1.5 Xây dựng mô hình thực thể liên kết10
Chương II: Chuyển từ mô hình thực thể liên kết sang mô hình quan hệ13
2.1 Cách chuyển đổi13
2.2 Xác định bảng và các thuộc tính của bảng13
2.3 Xây dựng kết nối giữa các bảng từ các lien kết14
2.4 Mô hình dữ liệu quan hệ quản lý thư viện16
2.5 Chuyển các thực thể và liên kết thực sang lược đồ quan hệ16
Chương III: Xác định khóa17
3.1 Thuật toán xác định khóa 17
3.2 Xác định khóa cho các quan hệ trong CSDL quản lý thư viện17
Chương IV: Chuẩn hóa lược đồ quan hệ19
4.1 Các dạng chuẩn và thuật toán chuẩn hóa 19
4.2 Cách thức chuẩn hóa thực tế 20
4.3 Chuẩn hóa CSDL quan hệ quản lý thư viện 21
Chương V: Các câu lệnh SQL23
5.1 Các câu lệnh tạo bảng 23
5.2 Các câu lệnh truy vấn dữ liệu SQL 24
Kết luận26
Tài liệu tham khảo
CHƯƠNG I: THIẾT KẾ CƠ SỞ DỮ LIỆU
VÀ MÔ HÌNH THỰC THỂ LIÊN KẾT
1.1 Giới thiệu sơ lược về cơ sở dữ liệu quản lý thư viện
Hiện nay, các thư viện phải quản lý một khối lượng rất lớn các sách, độc giả và nhac cung cấp sách. Đây là kho dữ liệu rất lớn, không thể lưu trữ và xử lý một cách thủ công như trước đây mà cần phải tin học hoá, cụ thể là xây dựng một chương trình tin học để quản lý thống nhất và toàn diện hoạt động cho mượn sách, nhập sách từ nhà cung cấp…
Do vậy nên em đã chọn đề tài quản lý thư viện. Em đã làm đề tài trên phần mềm Microsoft SQL sever 2005 1.2 Các thực thể và các thuộc tính của chúng
1.2.1 Các khái niệm cơ bản
Thực thể(Entity):là khái niệm mô tả một lớp các đối tượng có đặc trưng mà chúng ta cần quan tâm
Các thực thể là đối tượng cụ thể hoặc trừu tượng.
Trong sơ đồ thì thực thể thường được ký hiệu là hình chữ nhật.
Thuộc tính(Attribute): là các tính chất, đặc điểm chung của đối tượng. nó là một giá trị dùng để mô tả một đặc trưng nào đó của một thực thể.
Thuộc tính có thể là đơn trị, đa trị (lặp), hoặc phức hợp. Ký hiệu là hình thoi.
1.2.2 Các thực thể của cơ sở dữ liệu quản lý thư viện
a) Thực thể “Sách”
Tên sách
Mã sách
Lưu trữ các thông tin cơ bản về sách :mã sách, tên sách, tên tác giả, NXB, năm XB và số trang.
NXB
Tên tác giả
Sách
Năm xuất bản
Mã NCC
Số trang
Hình 1.1: thực thể “Sách”
b) Thực thể “Nhà cung cấp sách”
Mỗi nhà cung cấp sẽ có một mã riêng, tên nhà cung cấp, những sách sẽ cung cấp và số bản sách cung cấp.
Mã nhà cung cấp
Mã sách
NCC
Số bản
Ngày cung cấp
Tên nhà cung cấp
Hình 1.2: thực thể “nhà cung cấp sách”
c) Thực thể ”độc giả”
Mỗi độc giả sẽ có những thông tin nhất định như mã số, tên, ngày sinh, địa chỉ và đơn vị.
Tên độc giả
Ngày sinh
Mã độc giả
Độc giả
Địa chỉ
Đơn vị
Hình 1.3: thực thể “độc giả”
d) Thực thể “mượn sách”
Thực trạng sách cho biết tình trạng hiện tại của sách như độc giả mượn sách như thế nào? Ngày mượn, ngày hẹn trả và ngày trả như thế nào?
.Mã độc giả
Ngày mượn
Mã sách
Ngày hẹn trả
Ngày trả
Mượn sách
t
Số ngày quá hạn
Quá hạn trả
Hình 1.4: thực thể “tình trạng sách”
1.3 Xác định RBTV
1.3.1 Định nghĩa:
Ràng buộc toàn vẹn(RBTV) là một điều kiện bất biến không được vi phạm trong một CSDL.
Trong CSDL luôn tồn tại rất nhiều mối liên kết ảnh hưởng qua lại lẫn nhau giữa các thuộc tính, giữa các bộ giá trị trong một quan hệ và nhiều quan hệ.
Khi xác định một RBTV cần chỉ rõ:
điều kiện của RBTV, từ đó xác định cách biễu diễn
bối cảnh xảy ra RBTV trên một hay nhiều quan hệ
tầm ảnh hưởng của RBTV , khả năng tính toàn vẹn bị vi phạm
hành động cần phải có khi RBTV bị vi phạm.
Các loại RBTV
RBTV về miền gía trị của thuộc tính
RBTV liên thuộc tính
RBTV liên bộ, liên thuộc tính
RBTV về phụ thuộc tồn tại
RBTV tổng hợp (liên bộ - liên quan hệ)
1.3.2 Xác định RBTV
Gồm các điều kiện của RBTV và biểu diễ của chúng, đồng thời ta lập bảng tầm ảnh hưởng của mỗi RBTV.
Dấu (+): RBTV cần được kiểm tra nguy cơ dẫn tới vi phạm
Dấu (-): RBTV không có nguy cơ bị vi phạm.
Dấu (-(*)): RBTV không bị vi phạm vì không được phép sửa đổi
a) R1: Mỗi sách có 1 mã sách riêng không trùng với bất kì sách nào.
∀ masach1, masach2 ∈ sach, masach1≠masach2=>masach1.sach≠masach2.sach
Quan hệ
Thêm
Sửa
Xóa
masach
+(masach)
-(*)
+
b) R2: Mỗi độc giả có 1 mã riêng không trùng với độc giả khác.
∀madg1, madg2 ∈ docgia, madg1≠madg2=>madg1.docgia≠madg2.docgia
Quan hệ
Thêm
Sửa
Xóa
madg
+(madg)
-(*)
+
c) R3: Mỗi nhà cung cấp có 1 mã riêng không trùng với nhà cung cấp khác.
∀mancc1, mancc2 ∈ ncc, mancc1≠mancc2=>mancc1.ncc≠mancc2.ncc
Quan hệ
Thêm
Sửa
Xóa
mancc
+(mancc)
-(*)
+
d) R4: Tất cả ngày tháng trong csdl phải hợp lệ.
∀ ngaycc ∈ ncc =>1≤ ncc.ngaycc.date() ≤ 30 và 1 ≤ ncc.ngaycc.month() ≤30
∀ ngaymuon ∈ muonsach =>1≤ muonsach.ngaymuon.date() ≤ 30
và 1≤ muonsach.ngaymuon.month()≤30
∀ ngaytra ∈ muonsach =>1≤ muonsach.ngaytra.date() ≤ 30
và 1≤ muonsach.ngaytra.month()≤30
∀ ngayhentra ∈ muonsach =>1≤ muonsach.ngayhentra.date() ≤ 30
và 1≤ muonsach.ngayhentra.month()≤30
Quan hệ
Thêm
Sửa
Xóa
ncc
+
+
+
muonsach
+
+
+
e) R5: Mỗi độc giả không được mượn quá 5 cuốn sách.
∀ tinhtrang ∈ muonsach → 0 < tinhtrang.muonsach < 5
Quan hệ
Thêm
Sửa
Xóa
muonsach
+
-(*)
+
f) R6: Số ngày quán hạn không vượt quá 10 ngày.
∀ ngayquahan ∈ muonsach → 0 < ngayquahan.muonsach < 10
Quan hệ
Thêm
Sửa
Xóa
muonsach
+
-(*)
+
g) R7: Mỗi độc giả mượn sách phải ít nhất 10 tuổi trở lên( tính ở thời điểm hiện tại).
∀ madg ∈ docgia sao cho ((2014-docgia.ngaysinh)/365 ≥ 10)
Quan hệ
Thêm
Sửa
Xóa
docgia
+(ngaysinh)
-(*)
+
h) R8: Xét trên quan hệ muonsach:ngày mượn thì sẽ phải trước ngày trả.
∀ngaymuon, ngaytra ∈ muonsach /(muonsach.ngaymuon befor muonsach.ngaytra)
Quan hệ
Thêm
Sửa
Xóa
muonsach
+(ngaymuon, ngaytra)
+
+
i) R9: Mỗi sách phải do 1 nhà cung cấp cung cấp.
Quan hệ
Thêm
Sửa
Xóa
sach
+(masach)
+
+
ncc
-
-(*)
+
k) R10: Ngày cung cấp của sách phải trước ngày độc giả mượn sách.
∀masach ∈ ncc, ∀ masach ∈ muonsach sao cho
(ncc.ngaycc befor muonsach.ngaymuon)
Quan hệ
Thêm
Sửa
Xóa
ncc
+(ngaycc)
-(*)
+
muonsach
+(ngaymuon)
-(*)
+
l) R11: Một cuốn sách chỉ được mượn bởi một độc giả duy nhất.
∀ masach ∈ sach => ∃ ! muonsach.madg
Quan hệ
Thêm
Sửa
Xóa
sach
-
-(*)
+
muonsach
+(madg)
-(*)
+
1.4 Xác định phụ thuộc hàm.
a) Quan hệ sach (masach, tensach, tentg, namxb, sotrang, nxb, mancc).
ký hiệu: Q1(A, B, C, D, E, F, G)
f1: masach xác định tensach nên A →B
f2: masach xác định tentg nên A →C
f3: masach xác định namxb nên A →D
f4: masach xác định sotrang nên A →E
f5: masach xác định nxb nên A → F
f6 : masach xác định mancc nên A → G
=>Từ đó ta có tập PTH F1={ A →B, A →C, A →D, A →E, A → F, A→G}
b) Quan hệ ncc( mancc, masach, tenncc, soban, ngaycc)
ký hiệu: Q2(A, B, C, D, E)
f1: mancc,masach xác định tensach nên AB →C
f2: mancc,masach xác định soban nên AB →D
f3: mancc,masach xác định ngayCC nên AB →E
=>Từ đó ta có tập PTH F2={ AB →C, AB →D, AB →E}
c) Quan hệ docgia(madg, tendg, ngaysinh, diachi, donvi)
ký hiệu: Q3(A, B, C, D, E)
f1: madg xác định tenDG nên A →B
f2: madg xác định ngaysinh nên A →C
f3: madg xác định diachi nên A →D
f4: madg xác định donvi nên A →E
=>Từ đó ta có tập PTH F3={ A →B, A →C, A →D, A →E}
d) Quan hệ muonsach( madg, masach, ngaymuon, ngayhentra, ngaytra, quahan, ngayquahan, tinhtrang).
ký hiệu: Q1(A, B, C, D, E, F, G, H)
f1: madg, masach xác định ngaymuon nên AB →C
f2: madg, masach xác định ngayhentra nên AB →D
f3: madg, masach xác định ngaytra nên AB →E
f4: madg, masach xác định quahan nên AB →F
f5: madg, masach xác định ngayquahan nên AB →G
f6: madg, masach xác định tinhtrang nên AB → H
=>Từ đó ta có tập PTH F5={ AB →C, AB →D, AB →E, AB →F, AB →G, AB →H}
1.5 Xây dựng mô hình thực thể liên kết.
1.5.1 Các khái niệm:
Mối liên hệ (Entity Relationship): mối liên hệ giữa các thực thể thường được biểu diễn bằng hình thoi. Trong sơ đồ thực thể liên kết có các loại kiên kết sau:
Quan hệ 1-1: là mối quan hệ mà mỗi bản thể trong thực thể E1 chỉ có nhiều nhất một bản thể được liên kết trong thực thể E2.
Quan hệ 1-n: là mối quan hệ mà mỗi bản thể trong thực thể E1 có thể không liên kết hoặc liên kết với một hay nhiều bản thể trong thực thể E2.
Quan hệ n-n: là mối quan hệ mà mỗi bản thể trong thực thể có thể liên kết với nhiều bản thể trong thực thể khác và ngược lại.
Thuộc tính của mối liên hệ (Relationship Attribute): mỗi mối liên hệ cũng có thể có những thuộc tính riêng của chúng.
Chú ý: để đơn giản, đôi khi trình bày các thuộc tính nằm luôn trong các thực thể.
1.5.2 Xác đinh các quan hệ trong CSDL quản lý thư viện
mượn-trả
Sách
Độc giả
Hình 1.5: sơ đồ quan hệ giữa thực thể độc giả và sách.
cung cấp
Sách
Nhà cung cấp
Hình 1.6: sơ đồ quan hệ giữa thực thể nhà cung cấp và sách.
1.5.3 mô hình dữ liệu thực thể liên kết.
Ngày hẹn trả
Tên ĐG
Đơn vị
Mã ĐG
Tên sách
Số trang
Sách
Mã sách
Độc giả
Mượn
Ngày mượn
Ngày sinh
Địa chỉ
NXB
Tình trạng
Trả
Ngày trả
Năm XB
Số ngày quá hạn
Tên tác giả
Quá hạn trả sách
Số bản cung cấp
Mã NCC
Cung cấp
Nhà cung cấp
Tên NCC
Ngày cung cấp
Hình 1.7: Mô hình dữ liệu thực thể liên kết của CSDL quản lý thư viện.
CHƯƠNG II: CHUYỂN TỪ MÔ HÌNH THỰC THỂ LIÊN KẾT SANG MÔ HÌNH QUAN HỆ
2.1 Cách chuyển đổi
Các thực thể → các bảng .
Các thuộc tính của thực thể → các thuộc tính / cột của bảng.
Các loại liên kết:
Nếu là liên kết 1-1: khóa của bảng bên 1 bất kỳ trở thành một thuộc tính kết nối (khóa ngoại) của bảng bên kia.
Nếu là liên kết 1-n: khóa của bảng bên 1 trở thành một thuộc tính kết nối (khóa ngoại) của bảng bên nhiều
Nếu là n-n: thì phải thêm một bảng trung gian, bảng trung gian này sẽ chứa hai thuộc tính khóa của cảu hai bảng và cả hai thuộc tính khóa này sẽ là khóa chính của cho bảng trung gian, ngoài ra bảng trugn gian có thể them một số thuộc tính khác của mối liên kết.
2.2 Xác định các bảng và thuộc tính của bảng
Theo cách chuyển đổi thỉ ta xác định được các bảng và các cột của chúng như sau:
a) bảng sach ( masach, tensach, tentg, namxb,sotrang, nxb, ncc)
sach
masach
tensach
tentg
namxb
sotrang
nxb
mancc
b) bảng ncc( mancc, tenncc, soban, ngaycc)
ncc
mancc
masach
tenncc
soban
ngaycc
c) bảng docgia(madg, tendg, ngaysinh,diachi, donvi)
docgia
madg
tendg
ngaysinh
diachi
donvi
d) bảng muonsach (madg, masach, ngaymuon, ngayhentra, ngaytra, quahan, ngayquahan, tinhtrang)
muonsach
madg
masach
ngaymuon
ngayhentra
ngaytra
quahan
ngayquahan
tinhtrang
2.3 Xây dựng kết nối giữa các bảng từ các liên kết
Mối quan hệ giữa thực thể sach và thực thể ncc là quan hệ 1-n.
Thuộc tính mancc của bảng sach sẽ là khóa ngoại của bảng ncc.
Mối quan hệ giữa thực thể sach và thực thể muonsach là quan hệ 1-n.
Thuộc tính masach cuả bảng sach sẽ là khóa ngoại của bảng muonsach.
Mối quan hệ giữa thực thể docgia và thực thể muonsach là quan hệ 1-n.
Thuộc tính madg cuả bảng docgia sẽ là khóa ngoại của bảng muonsach.
Mối quan hệ giữa thực thể sach và thực thể docgia là quan hệ n-n.
2.4 Mô hình dữ liệu quan hệ quản lý thư viện
muonsach
madg
masach
ngaymuon
ngayhentra
ngaytra
quahan
ngayquahan
tinhtrang
docgia
madg
tendg
ngaysinh
diachi
donvi
sach
masach
tensach
tentg
namxb
sotrang
nxb
mancc
ncc
mancc
masach
tenncc
soban
ngaycc
Hình 2.1 : Lược đồ mô hình dữ liệu quan hệ quản lý thư viện.
2.5 Chuyển các thực thể và liên kết thực thể sang lược đồ quan hệ
a. Chuyển các thực thể
ĐỘC GIẢ
ĐỘCGIẢ(Mã ĐG, Tên ĐG, Ng.sinh,
Đơn vị, Địa chỉ)
1
SÁCH
SÁCH(Mã sách, Tên sách, Tên TG, NXB,
Năm XB, Số trang, )
2
NCC
NCC(Mã NCC, Tên NCC)
3
b. Chuyển các liên kết thực thể
MƯỢN
MƯỢN_S(Mã ĐG, Mã sách, Ng.mượn,
Ng.hẹn trả, )
4
TRẢ
TRẢ_S(Mã sách, Mã ĐG, Ng.trả,
Tình trạng)
5
C.CẤP
C.CẤP_S(Mã NCC, Mã sách, Ng.nhập, Số bản)
6
CHƯƠNG III: XÁC ĐỊNH KHÓA
3.1 Thuật toán xác định khóa của lược đồ quan hệ.
3.1.1) Định nghĩa về khóa
Khóa được định nghĩa lại bằng phụ thuộc hàm như sau:
R(U), U= {A1, A2, ….An}, F= {f1, f2, …., fm} xác định trên R, K U là khóa của R nếu thoả mãn hai điều kiện sau:
(i) K → U.
(ii) ! Ǝ K’ K mà K’ → U.
Biểu diễn lược đồ quan hệ R(U) bằng đồ thị có hướng như sau:
Mỗi nút của đồ thị là tên một thuộc tính của R.
Cung nối 2 thuộc tính A và B thể hiện phụ thuộc hàm A → B.
Thuộc tính mà chỉ có các mũi tên đi ra gọi là gốc.
Thuộc tính mà chỉ có các cung đi tới gọi là nút lá.
Như vậy khóa phải là bào phủ tập hợp các nút gốc, đồng thời không chứa bất kỳ nút lá nào của đồ thị.
3.1.2) Thuật toán xác định khóa của lược đồ quan hệ
xuất phát từ tập các nút gốc (X), dựa trên tập các phụ thuộc hàm F, tìm bao đóng X+.
nếu X+ = U thì X là khóa.
Ngược lại thì bổ sung thuộc tính khồng thuôc nút lá vào X rồi tìm bao đóng.
Cứ như thế cho tới khi tìm được bao đóng của X bằng U.
Cuối cùng khóa chính là X.
3.2 Xác định khóa cho các quan hệ trong CSDL quản lý thư viện.
a) quan hệ sach( masach, tensach, tentg, namxb, sotrang, nxb, mancc )
ký hiệu Q1(U) = Q1(A, B, C, D, E ,F,G)
ta có tập PTH F1={ A →B, A →C, A →D, A →E, A →F, A→G }
Xét: X = {A}
X+ ={A,B,C,D,E,D,G} = U
Vậy khóa của quan hệ sach là A (masach)
b) quan hệ ncc( mancc, masach, tenncc, soban, ngaycc )
ký hiệu Q2(U) = Q1(A, B, C, D, E )
ta có tập PTH F2={ AB →C, AB →D, AB →E }
Xét X = {AB}
X+ = {A,B,C,D,E} = U
Vậy khóa của quan hệ ncc là A(mancc), B(masach).
c) quan hệ docgia (madg, tendg, ngaysinh, diachi, donvi)
ký hiệu Q3(U) = Q3(A, B, C, D, E)
ta có tập PTH F3={ A →B, A →C, A →D, A →E }
Xét X= {A}
X+ = {A, B, C, D, E}=U
Vậy khóa của quan hệ docgia là A(madg).
d) quan hệ muonsach(madg, masach, ngaymuon, ngayhentra, ngaytra, quahan, ngayquahan, tinhtrang)
ký hiệu Q4(U) = Q4(A, B, C, D, E, F, G, H )
ta có tập PTH F4={ AB →C, AB →D, AB →E, AB →F, AB →G, AB →H }
Xét X= {AB}
X+ = { A, B, C, D, E, F, G, H }=U
Vậy khóa của quan hệ muonsach là A(madg) và B(masach).
CHƯƠNG IV: CHUẨN HÓA LƯỢC ĐỒ QUAN HỆ
THÀNH DẠNG CHUẨN 3NF HOẶC BCNF
4.1 Các dạng chuẩn và thuật toán chuẩn hóa.
4.1.1) Các dạng chuẩn.
Dạng chuẩn 1(1NF): lược đồ quan hệ Q ở dạng !NF nếu tất cả các thuộc tính của Q đều là thuộc tính đơn.
Dạng chuẩn 2 (2NF): lược đồ quan hệ Q ở dạng chuẩn 2NF nếu ở 1NF và tất cả các thuộc tính không khóa đều phụ thuộc đầy đủ vào khóa.
Dạng chuẩn 3 (3NF):
ĐN1: lược đồ quan hệ Q ở dạng chuẩn 3NF nếu ở 2NF và tất cả các thuộc tính không khóa đều không phụ thuộc bắc cầu vào khóa.
ĐN2: lược đồ quan hệ Q ở dạng chuẩn 3NF nếu ở 1NF và tất cả phụ thuộc hàm không hiển nhiên X→Y của F thỏa một trong hai điều kiên sau:
(i) X là một siêu khóa (X chứa một khóa nào đó)
(ii) mỗi thuộc tính trong tập (Y - X) nằm trong một khóa nào đó.
Dạng chuẩn Boyce – codd (BCNF): lược đồ quan hệ Q ở dạng chuẩn BCNF nếu ở 1NF và tất cả phụ thuộc hàm không hiển nhiên X→Y của F thì X là một siêu khóa (X chứa một khóa nào đó).
4.1.2) Thuật toán chuẩn hóa
Thuật toán phân rã
Dựa vào điều kiện phan rã bảo toàn thông tin Q thành Q1 và Q2 thỏa Q1 Q2 → Q1\ Q2 hay Q1 Q2→ Q2\ Q1.
Thuật toán phân rã thành các lược đồ ở dạng chuẩn BCNF như sau:
Cho Q và tập F xác định trên Q
Phân_rã :={Q} ;
done:= false;
Tính F+;
while (not done) do
if (có một Qi trong Phân_rã không ở dạng BCNF) then
Begin
X→Y là phụ thuộc hàm không hiển nhiên trên Qi thỏa:
X→Qi F+và X Y= thì
Phân_rã := (Phân_rã - Qi) U (XY) U (Qi-Y)
End
else done := true;
Kết quả ta được tập Phân_rã gồm các lược đồ ở dạng BCNF
Thuật toán tổng hợp
Thuật toán sau cho phân rã đạt tối thiểu ở dạng 3NF.
Cho Q và tập PTH F xác địnhtrên Q.
Tính Fc là một phủ tối tiểu của F+;
Xác định các khóa của Q;
i:= 0;
for (mỗi phụ thuộc hàm X→Y trong Fc) do
if (không có Qj, j=1,2,…I chứa XY) then
begin
i:= i+1;
Qi:= XY;
end;
if(không có Qj, j= 1, 2….i chứa khóa của Q) then
begin
i:= i+ 1;
Qi := bất kỳ khóa nào của Q;
end;
return(Q1, Q2,…Qi);
4.2 Cách thức chuẩn hóa thực tế
Trong thực thế khi chuẩn hóa lược đồ CSDL thường được thực hiện theo các bước:
Bước 1: kiểm tra xem quan hệ đã đạt dạng chuẩn 1NF chưa?. Nếu chưa ở 1NF có nghĩa là có các thuộc tính chưa nguyên tố / lặp. Tiến hành tách các thuộc tính đó.
Bước 2: kiểm tra xem chúng có ở dạng 2NF không ?. Nghĩa là kiểm tra xem các thuộc tính không khóa có phụ thuộc hoàn toàn vào khóa chính khôn g?. Tiến hành tách những PTH bộ phận đó thành các bảng con để giảm bớt sự trùng lặp thông tin.
Bước 3: kiểm tra xem chúng đã đạt dạng chuẩn 3NF chưa ?. Nghĩa là các thuộc tính không khóa thì phụ thuộc trực tiếp vào khóa chính. Tiến hành tách những PTH bắc cầu thành bảng con.
Bước 4: kiểm tra xem chúng đã đạt dạng chuẩn BCNF chưa ?.Nghĩa là tất cả các phụ thuộc hàm đều có vế trái là siêu khóa. Tiến hành tách PTH có vế trái chưa phải là siêu khóa.
4.3 Chuẩn hóa cơ sở dữ liệu quan hệ quản lý thư viện
CSDL quản lý thư viện có:
Quan hệ sach( masach, tensach, tentg, namxb, sotrang, nxb, mancc )
có tập PTH là F1 = {masach → tensach, masach → tentg, masach → namxb , masach → sotrang , masach → nxb, masach → mancc }
quan hệ ncc( mancc, masach, tenncc, soban, ngaycc )
có tập PTH là F2 = { (mancc,masach) → tenncc, (mancc,masach) → soban , (mancc,masach) → ngaycc}
quan hệ docgia(madg, tendg, ngaysinh, diachi, donvi)
có tập PTH là F3 = {madg → tendg, madg → ngaysinh, madg → diachi, madg →donvi}
quan hệ muonsach (madg, masach, ngaymuon, ngayhentra, ngaytra, quahan, ngayquahan, tinhtrang)
có tập PTH là F4 = { (madg,masach) → ngay muon, (madg,masach) → ngayhentra, (madg,masach) → ngaytra, (madg,masach) → quahan, (madg,masach) → ngayquahan, (madg,masach) → }
Bước 1: kiểm tra dạng chuẩn 1NF
Các thuộc tính của tất cả các quan hệ đều là nguyên tố.
CSDL đã đạt dạng chuẩn 1NF
Bước 2: kiểm tra dạng chuẩn 1NF
Xét các thuộc tính không khóa đều phụ thuộc hoàn toàn vào khóa chính.
CSDL đã đạt dạng chuẩn 2NF
Bước 3: kiểm tra dạng chuẩn 1NF
Các thuộc tính không khóa thì phụ thuộc trực tiếp vào khóa chính.
CSDL đã đạt dạng chuẩn 3NF
CHƯƠNG V: CÂU LỆNH SQL
5.1 Các câu lệnh tạo bảng.
a) tạo bảng sach( masach, tensach, tentg, namxb, sotrang, nxb, mancc )
CREATE TABLE [dbo].[sach](
[masach] [char](10) NOT NULL,
[tensach] [char](20) ,
[tentg] [char](20),
[namxb] [numeric](18, 0),
[sotrang] [numeric](18, 0),
[nxb] [char](30),
[mancc] [char](10) NOT NULL.
primary key (masach));
b) tạo bảng ncc(mancc, masach, tenncc, soban, ngaycc )
CREATE TABLE [dbo].[ncc](
[mancc] [char](10) NOT NULL,
[masach] [char](10) NOT NULL,
[tenncc] [char](30) NOT NULL,
[soban] [numeric](18, 0) NOT NULL,
[ngaycc] [datetime],
primary key (mancc)
foreign key (mancc)) ;
c) tạo bảng docgia(madg, tendg, ngaysinh, diachi, donvi)
CREATE TABLE [dbo].[doc gia](
[madg] [char](10) NOT NULL,
[tendg] [char](20),
[ngaysinh] [datetime] ,
[diachi] [char](30) ,
[donvi] [char](30);
primary key (madg));
d) tạo bảng muonsach(madg, masach, ngaymuon, ngayhentra, ngaytra, quahan, ngayquahan, tinhtang)
CREATE TABLE [dbo].[muon sach](
[madg] [char](10) NOT NULL,
[masach] [char](10) ,
[ngaymuon] [datetime] ,
[ngayhentra] [datetime] ,
[ngaytra] [datetime] ,
[quahan] [bit] ,
[ngayquahan] [numeric](18, 0) ,
[tinhtrang] [char](30) ,
primary key (masach,madg),
foreign key (madg, masach));
5.2 Các câu lệnh truy vấn dữ liệu SQL.
1. Đưa ra danh sách các sách của thư viện:
SELECT *
FROM sach;
2. Đưa ra danh sách độc giả:
SELECT *
FROM docgia;
3. Đưa ra danh sách nhà cung cấp sách:
SELECT *
FROM ncc;
4. Đưa ra thông tin độc giả có đơn vị là trường học:
SELECT *
FROM docgia
WHERE donvi= “trường học”;
5. Đưa ra tên sách được mượn trong ngày 2/2/2014:
SELECT sach *
FROM sach, muonsach
Where (date(ngaymuon)=2) and (month(ngaymuon)=2) and year(ngaymuon)=2014) and ( sach.masach=muonsach.masach);
6.Tính tổng số lượng độc giả mượn sách mỗi ngày:
SELECT ngaymuon, count ( madg) as soluongdocgia
FROM muonsach
GROUP BY ngaymuon;
7. Đưa ra số lần mượn sách của mỗi độc giả trong 2/2014 chỉ hiện thị những độc giả mượn sách nhiều hơn 3 lần:
SELECT madg count madg as solanmuon FROM muonsach
WHERE (month(ngaymuon)=2) and (year(ngaymuon)=2014)
GROUP BY madg HAVING count (madg) >3;
8. Tìm những độc giả mượn sách quá hạn:
SELECT docgia*
FROM muonsach, docgia
WHERE quahan = ”1”;
9. Đưa ra danh sách các sách của nhà xuất bản Kim Đồng:
SELECT tensach
FROM ncc
WHERE nxb=”Kim Đồng”;
10. Đưa ra danh sách nhà cung cấp cung cấp sách nhiều hơn 1000 bản:
SELECT tenncc
FROM ncc
WHERE soban > 1000;
11. Đưa ra danh sách độc giả có số ngày quá hạn mượn sách > 4 ngày:
SELECT docgia*
FROM docgia, muonsach
WHERE ngayquahan>4 and (docgia.madg=muonsach.madg);
12. Đưa ra danh sách các sách có số trang nhiều hơn 1000 trang được xuất bản trong năm 2013:
SELECT tensach
FROM sach
WHERE (sotrang > 1000) and (namxb=”2013”)
13. Đưa ra các mã sách chưa từng được mượn:
SELECT masach
FROM sach
WHERE masach Not in ( SELECT masach FROM muonsach);
14. Đưa ra thông tin độc giả mượn tối đa 5 cuốn sách:
SELECT docgia*
FROM muonsach, docgia
WHERE madg in ( SELECT madg FROM muonsach WHERE tinhtrang=”5”);
15. In ra thong tin sách có số bản được cung cấp nhiều nhất:
SELECT sach*
FROM ncc, sach
WHERE soban=( SELECT MAX(soban) FROM ncc)
and ( sach.macc=ncc.mancc)
KẾT LUẬN
Trong quá trình hoàn thành bài báo cáo em đã rút ra được nhiều kinh nghiệm và nâng cao kỹ năng chuyên môn, phát hiện ra những thiếu sót trong kiến thức của mình.
Mặc dù đã cố gắng để xây dựng và chuẩn hóa theo lý thuyết nhưng trên thực tế gặp rất nhiều khó khăn, trong phạm vi kiến thức được học chắc chắn báo cáo của e sẽ có những thiếu sót. Vì vậy em rất mong nhận được sự góp ý và đánh giá của cô để em có thể hoàn thiện hơn nữa. Em xin chân thành cảm ơn!
TÀI LIỆU THAM KHẢO
Đặng Thị Thu hiền, Bài giảng cơ sở dữ liệu, Đại học GTVT, 2004
Lê Tiến Vương. Nhập môn cơ sở dũ liệu quan hệ, NXB Thống Kê, 2000. Tái bản lần 5
Bài tập cơ sở dữFile đính kèm:
Bai tap lon CSDL.docx
