Câu 35. Chọn phương án đúng: triết học ra đời từ? A. Sự vận động của ý muốn chủ quan của con người B. Sự sáng tạo của các ý niệm tuyệt đối C. Thực tiễn, do nhu cầu của thực tiễn D. Sự tư duy của con n PDF

Preview

Summary

ĐẠI HỌC THỦY LỢIBÁO CÁO BÀI TẬP LỚN MÔN HỌCCƠ SỞ DỮ LIỆUĐỀ TÀI: QUẢN LÝ KÝ TÚC XÁ SINH VIÊNThành viên: Trịnh Văn Lung - 2051063633 - Nhóm trưởng Nguyễn Như Minh - 2051063754 Phan Hoàng Lan - 2051063838 Lê Hoàng Long - 2051063632 Hoàng Thanh Thúy - 2051063739 Giảng viên hướng dẫn : Trần Hồng DiệpMục ...

Description

ĐẠI HỌC THỦY LỢI

BÁO CÁO BÀI TẬP LỚN MÔN HỌC CƠ SỞ DỮ LIỆU ĐỀ TÀI: QUẢN LÝ KÝ TÚC XÁ SINH VIÊN

Thành viên: 1. 2. 3. 4. 5.

Trịnh Văn Lung - 2051063633 - Nhóm trưởng Nguyễn Như Minh - 2051063754 Phan Hoàng Lan - 2051063838 Lê Hoàng Long - 2051063632 Hoàng Thanh Thúy - 2051063739

Giảng viên hướng dẫn : Trần Hồng Diệp

Mục lục A. Bảng phân chia công việc B. Nội dung Chương I. Chương tổng quan 1. Đặt vấn đề 2. Mô tả bài toán Chương II. Chương công việc 1. Khảo sát thực tế 2. Xây dựng cơ sở dữ liệu a. Mô hình thực thể liên kết b. Chuyển từ mô hình thực thể liên kết sang mô hình quan hệ c. Mô hình quan hệ d. Xác định phụ thuộc hàm e. Tìm khóa cho các quan hệ f. Chuẩn hóa lược đồ quan hệ thành dạng chuẩn 3NF 3. Khai thác dữ liệu a. Cài đặt mô hình quan hệ trong SQL Server b. Truy vấn dữ liệu C. Kết luận

2 3 3 3 3 4 4 5 5 5 6 6 7 7 8 8 9 12

1

A.

Bảng phân chia công việc

Công việc

Người thực hiện & trình bày

Chương tổng quan & Khảo sát thực tế

Hoàng Thanh Thúy

Xây dựng mô hình thực thể & mô hình quan hệ

Lê Hoàng Long

Xây dựng tập phụ thuộc hàm, tìm khóa

Nguyễn Như Minh

Chuẩn hóa lược đồ quan hệ thành dạng chuẩn 3NF

Trịnh Văn Lung

Cài đặt mô hình quan hệ trên SQL Server

Phan Hoàng Lan

Câu lệnh truy vấn

Cả nhóm

2

B.

Nội dung

CHƯƠNG I. CHƯƠNG TỔNG QUAN 1. Đặt vấn đề ● Trong thời đại hiện nay, ứng dụng tin học hoá vào các lĩnh vực quản lý của các hệ thống quản lý như quản lý ngân hàng, thư viện, kí túc xá...đã giảm thiểu được số lượng chi phí đầu tư cho nguồn nhân công, đảm bảo được tính bảo mật và có tính lưu trữ cao, đồng thời tăng hiệu quả về độ chính xác trong việc quản lý. ● Hiện nay các trường đại học với số lượng sinh viên đăng kí ở kí túc xá tương đối nhiều. Việc quản lí kí túc xá sinh viên của trường là rất khó khăn nếu không có ứng dụng của tin học. Đây là vấn đề rất bức thiết đối với nhà trường. ● Dựa vào mô hình cụ thể trong quản lý ký túc xá sinh viên của các trường đại học, chúng em đã vào tìm hiểu và đi sâu vào nghiên cứu đề tài: “QUẢN LÝ KÝ TÚC XÁ SINH VIÊN” dưới sự hướng dẫn của giảng viên Trần Hồng Diệp phụ trách bộ môn Cơ sở dữ liệu. 2. Mô tả bài toán ● Khi sinh viên có nhu cầu đăng ký tạm trú tại ký túc xá của trường sẽ phải thực hiện thủ tục với tổ công tác sinh viên, bộ phận quản lý hợp đồng của sinh viên. Nếu hồ sơ được đáp ứng thì hệ thống sẽ nhận hợp đồng. Trong hợp đồng gồm ID hợp đồng, ID phòng, thời hạn và ghi chú(bao gồm các điều khoản). ● Sinh viên khi đăng ký bên bộ phận quản lí kí túc xá thành công sẽ lưu trữ toàn bộ về thông tin sinh viên như mã sinh viên, tên sinh viên và số điện thoại trên hệ thống và khi nhận phòng sẽ cập nhập thêm ID phòng trên hệ thống. ● Phòng sẽ có ID phòng và được cập nhập mới thông tin khi có sinh viên mới. Sẽ được cập nhập lại thông tin về số sinh viên và một số thông tin khác. Cập nhập lại tình trạng nội thất sau mỗi tháng. ● Hoá đơn mỗi tháng sẽ gửi về cho sinh viên sẽ bao gồm tiền điện, tiền nước, tiền phòng. Sinh viên có thể nộp 2 hình thức, sau đó sẽ được cập nhập trên hệ thống và có 1 bản để sinh viên có thể xuất thành file pdf hay in.

3

CHƯƠNG II. CHƯƠNG CÔNG VIỆC 1. Chương khảo sát thực tế ● Yêu cầu về lưu trữ dữ liệu có trong thực tế : - Phòng thuê ( ID phòng, diện tích, sức chứa) - Hóa đơn (ID hóa đơn, ID phòng, ID sinh viên, tiền điện, tiền nước, tiền thuê phòng, thời gian xuất hóa đơn) - Hợp đồng (ID hợp đồng, ID phòng, thời hạn, ghi chú) - Sinh viên ( ID Sinh viên, ID hợp đồng, tên sinh viên, số điện thoại) Chứng từ :

● Thao tác công việc: - Sinh viên gửi thông tin đăng ký ở ký túc xá về cho bộ phận quản lý sinh viên. - Bộ phận quản lý kiểm tra thông tin đăng ký và kiểm tra tình trạng phòng. Nếu hết phòng hoặc sinh viên không đủ điều kiện đăng ký: từ chối cho sinh viên ở ký túc xá. Nếu đạt yêu cầu, sinh viên nộp tiền phòng và lập hợp đồng thuê phòng. - Khi nhận phòng, sinh viên kiểm tra tình trạng cơ sở vật chất, diện tích, sức chứa,... - Trong thời gian ở ký túc xá, hóa đơn tiền phòng hàng tháng sẽ được gửi cho sinh viên, sinh viên có trách nhiệm đóng tiền đúng thời hạn. - Khi sinh viên muốn ngừng ở ký túc xá thì bộ phận quản lý sinh viên xóa hồ sơ sinh viên và các thông tin liên quan. - Hết một niên khóa, nếu sinh viên muốn tiếp tục ở ký túc xá thì đăng ký thông tin lại từ đầu cho ban quản lý ký túc xá. 4

2. Xây dựng cơ sở dữ liệu a. Mô hình thực thể liên kết

b. Chuyển từ mô hình thực thể liên kết sang mô hình quan hệ - Liên kết 1-1 : Khóa của thực thể tham gia là toàn bộ khóa ngoại của thực thể còn lại → Khóa của Hợp đồng thành khóa ngoại của Sinh viên thuê. - Liên kết 1-N: Khóa của bên 1 thành khóa ngoại của bên N → Khóa của Sinh viên thuê thành khóa ngoại của Hóa đơn. → Khóa của Phòng thuê thành khóa ngoại của Hóa đơn. → Khóa của Phòng thuê thành khóa ngoại của Hợp đồng. c. Mô hình quan hệ:

5

Mỗi 1 sinh viên có một ID sinh viên và ID hợp đồng riêng biệt không trùng lặp, tên sinh viên, số điện thoại. ● ID sinh viên → ID hợp đồng, Tên sinh viên, Số điện thoại. Mỗi một hợp đồng sẽ có một ID hợp đồng và riêng biệt không trùng lặp, ID phòng, thời hạn và ghi chú. ● ID hợp đồng → ID phòng, Thời hạn, Ghi chú. . Mỗi phòng thuê có một ID phòng riêng biệt và không trùng lặp, diện tích và sức chứa. ● ID phòng → Diện tích, Sức chứa. Mỗi một hoá đơn có một ID hoá đơn riêng biệt và không trùng lặp, ID sinh viên, ID phòng , Tiền điện, Tiền nước, Tiện thuê phòng, Thời gian xuất hoá đơn. ● ID hoá đơn → ID sinh viên, ID phòng, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hoá đơn. e. Tìm khóa cho các quan hệ Quan hệ HDKTX (ID phòng, Diện tích, Sức chứa, ID hợp đồng, Thời hạn, Ghi chú, ID hóa đơn, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn, ID sinh viên, Tên sinh viên, Số điện thoại) F={

ID phòng → Diện tích, Sức chứa;

ID hợp đồng → ID phòng, Thời hạn, Ghi chú; ID hóa đơn →ID sinh viên, ID phòng, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn; ID sinh viên →Tên sinh viên, Số điện thoại, ID hợp đồng } Tập thuộc tính U = { (thuộc tính )ID phòng, Diện tích, Sức chứa, ID hợp đồng, Thời hạn, Ghi chú, ID hóa đơn, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn, ID sinh viên, Tên sinh viên, Số điện thoại}

Ta có giao của các khoá là: 6

X = U - {Diện tích, Sức chứa, ID phòng, Thời hạn, Ghi chú, ID sinh vien, ID phong, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn, Tên sinh viên, Số điện thoại, ID hợp đồng} = {ID hoá đơn} X+ = Bao đóng (ID hoá đơn)+ = tập thuộc tính U Quan hệ chỉ có 1 khóa duy nhất ID hoá đơn f. Chuẩn hóa lược đồ quan hệ thành dạng chuẩn 3NF Quan hệ HDKTX có: U = (ID phòng, Diện tích, Sức chứa, ID hợp đồng, Thời hạn, Ghi chú, ID hóa đơn, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn, ID sinh viên, Tên sinh viên, Số điện thoại) F={

ID phòng → Diện tích, Sức chứa;

ID hợp đồng → ID phong, Thời hạn, Ghi chú; ID hóa đơn →ID sinh vien, ID phong, Tiền điện, Tiền nước, Tiền thuê phòng, Thời gian xuất hóa đơn; ID sinh viên →Tên sinh viên, Số điện thoại, ID hợp đồng } Khoá là ID hoá đơn Đưa về đạt chuẩn 3NF. ● Bước 1: Kiểm tra đã đạt 1NF chưa: Quan hệ đã đạt chuẩn 1NF ● Bước 2: Kiểm tra đã đạt 2NF chưa: Quan hệ chưa đạt chuẩn 2NF do có thuộc tính không khoá không phụ thuộc đầy đủ vào khoá. Để đạt chuẩn 2NF ta tách thành 4 quan hệ: - Phòng (ID phòng, diện tích, sức chứa) - Hợp đồng (ID hợp đồng, ID phòng, thời hạn, ghi chú) - Hoá đơn (ID hoá đơn, ID sinh viên, ID phòng, tiền điện, tiền nước, tiền thuê phòng, thời gian xuất hoá đơn) - Sinh viên (ID sinh viên, tên sinh viên, số điện thoại, ID hợp đồng)

● Bước 3: Kiểm tra đã đạt 3NF chưa: 7

Quan hệ Phòng đã đạt 3NF vì không có thuộc tính không khóa nào phụ thuộc bắc cầu vào thuộc tính khóa Quan hệ Hợp đồng đã đạt 3NF vì không có thuộc tính không khóa nào phụ thuộc bắc cầu vào thuộc tính khóa Quan hệ Hoá đơn đã đạt 3NF vì không có thuộc tính không khóa nào phụ thuộc bắc cầu vào thuộc tính khóa Quan hệ Sinh viên đã đạt 3NF vì không có thuộc tính không khóa nào phụ thuộc bắc cầu vào thuộc tính khóa

3. Khai thác dữ liệu *Để ngắn gọn cho việc truy xuất, tên các thuộc tính sẽ được đổi sang tên viết tắt: - Phòng thuê: Room (R#(ID phòng), Area(diện tích),SC (sức chứa) - Hóa đơn: Bill (B# (ID hóa đơn),R#, Elec (tiền điện), Water(tiền nước), Rent (tiền thuê phòng), Time(thời gian xuất hóa đơn), ID sinh viên(MSV) - Hợp đồng: Contract (C# (ID hợp đồng), R#, Term(thời hạn), Note(ghi chú)) - Sinh viên: SV (Name(Tên sv), MSV, SDT, C#) a. Cài đặt mô hình quan hệ trong SQL Server - Tạo CSDL mới: CREATE DATABASE KTX#2; - Tạo bảng Room: CREATE TABLE Room( R# nchar(10) not null PRIMARY KEY, Area int , SC int) - Tạo bảng Contract: CREATE TABLE Contract( C# nchar(10) not null PRIMARY KEY, R# nchar(10) not null, Term date, Note nchar(100), FOREIGN KEY(R#) REFERENCES Room(R#)) - Tạo bảng SV: CREATE TABLE SV( MSV nchar(10) not null PRIMARY KEY, 8

Name nchar(30) not null, SDT nchar(15), C# nchar(10) not null, FOREIGN KEY(C#) REFERENCES Contract(C#)) - Tạo bảng Bill: CREATE TABLE Bill( B# nchar(10) not null PRIMARY KEY, R# nchar(10) not null, Elec int, Water int, Rent int, Time date, MSV nchar(10), FOREIGN KEY(R#) REFERENCES Room(R#), FOREIGN KEY(MSV) REFERENCES SV(MSV)) b. Truy vấn dữ liệu: 1. Cho biết tên tất cả sinh viên thuê phòng - Name (SV) - SELECT Name FROM SV 2. Cho biết tất cả các ID hợp đồng - C# (Contract) - SELECT C# FROM Contract 3. Cho biết thông tin tất cả sinh viên thuê phòng - (SV) - SELECT * FROM SV 4. Cho biết tên sinh viên có hóa đơn là B101 - Name ( σ B# = ‘B101’ ( σ SV.MSV=Bill.MSV ( SV x Bill ))) - SELECT Name FROM SV, Bill WHERE SV.MSV = Bill.MSV AND B# = 'B101'

9

5. Cho biết phòng có tiền phòng lớn hơn 200 - ( (Bill) ) - SELECT R# FROM Bill WHERE Rent>200 6. Cho biết tên sinh viên có mã hóa đơn là B101 và ID phòng là R100 -

((σB#=’B101’

Name

R#=’R100’

(σBill.MSV=SV.MSV(Bill x SV))))

SELECT Name FROM Bill, SV WHERE Bill.MSV=SV.MSV AND B#='B101' AND R#='R100'

7. Cho biết tên sinh viên có ID phòng là R101 và ID hợp đồng là C101 -

Name

( σ ( R# = R101 ⴷ C# = C101 )(σContract.C#=SV.C#(Contract x SV)))

SELECT Name FROM SV WHERE C# = 'C101' AND C# in (SELECT C# FROM Contract WHERE R#='R101')

8. Cho biết tên sinh viên có mã hóa đơn là B101 và tiền nước lớn hơn hoặc bằng 100 -

(σB#=’B101’

Name

(σBill.MSV=SV.MSV(Bill x SV)))

water >=100

SELECT Name FROM Bill, SV WHERE Bill.MSV= SV.MSV AND B#='B101' AND Water>= 100

9. Cho biết ID sinh viên có tiền phòng lớn hơn 100 và có thời gian xuất hóa đơn là ngày 1/12/2021 -

MSV(

σ(Rent>100

Time=’2021/12/01’)(Bill))

SELECT MSV FROM Bill WHERE Rent>100 AND Time = '2021/12/01'

10. Cho biết ID phòng có thời gian xuất hóa đơn vào ngày 1/12/2021 -

MSV(

σTime=’2021/12/01’(Bill))

SELECT B# FROM Bill WHERE Time = '2021/12/01' 10

11. Cho biết phòng thuê có thời hạn đến 15/01/2022 -

R#(

σTerm=’2022/01/15’(Bill))

SELECT R# FROM Contract WHERE Term = '2022/01/15'

12. Cho biết tên những sinh viên ở cùng phòng ‘R101’ - Name((SV) ) - SELECT Name FROM SV, Contract WHERE SV.C# = Contract.C# and R#='R101' 13. Cho biết tổng số tiền phải đóng của sinh viên có ID sinh viên là M1 - SELECT Elec+Water+Rent FROM Bill WHERE MSV='M1' 14. Cho biết tổng số tiền tất cả các sinh viên trong phòng R100 phải đóng - SELECT SUM(Elec+Water+Rent) FROM Bill WHERE R#='R100' 15. Cho biết tên sinh viên có tiền điện lớn nhất - SELECT Name FROM SV WHERE MSV IN (SELECT MSV FROM Bill WHERE Elec = (SELECT MAX(Elec) FROM Bill))

11

KẾT LUẬN Cơ hội làm bài tập lớn giúp cho bọn em được củng cố lại kiến thức, đem những kiến thức đã học áp dụng vào thực tế đồng thời cũng giúp bọn em cải thiện việc làm việc nhóm Vì thời gian làm có hạn, kiến thức của bọn em còn nhiều hạn chế và đây là lần đầu tiên tiếp xúc với một vấn đề lớn mà bọn em chưa có kinh nghiệm thực tế. Do vậy, nội dung của bài tập chưa thật sự sâu sắc, không tránh khỏi những sai sót và hạn chế trong bài làm. Bọn em hi vọng được thầy/cô chỉ bảo, góp ý thêm để bài làm của bọn em được hoàn thiện hơn.

12...