Traning CSDL Gi A Kì 2018 - 2019 PDF [PDF]

Zitiervorschau

1

BAN HỌC TẬP ĐOÀN KHOA CNPM

Chuỗi Training giữa học kì I năm học 2018 - 2019

Ban học tập

Our Phone

Email / Group

Khoa Công Nghệ Phần Mềm Trường ĐH Công Nghệ Thông Tin

0932 470 201 0936 645 393 01666 27 27 03

[email protected] fb.com/groups/bht.cnpm.uit/

Training giữa kì

Cơ Sở Dữ Liệu 10h ngày 25/10 Giảng đường 3 (A3) Vũ Tuấn Hải - KTPM2017 Nguyễn Trường Lâu - KHCL2017.1

2

CẤU TRÚC ĐỀ THI

CẤU TRÚC ĐỀ THI Phần 1: • Vẽ lược đồ ER (1,5 điểm) • Chuyển mô hình ER sang mô hình quan hệ(1 điểm) Phần 2: • Các câu lệnh DDL: Tạo bảng, thêm xoá khoá chính, thuộc tính, thay đổi kiểu dữ liệu thuộc tính(ngôn ngữ SQL) (0.75 điểm) • Các câu lệnh DML: Thêm xoá sửa dữ liệu (Ngôn ngữ SQL) (0.75 điểm) • Đại số quan hệ (6 điểm)

PHẦN 1

PHẦN 1.1: VẼ LƯỢC ĐỒ ER

MÔ HÌNH ER

MÔ HÌNH ER

MÔ HÌNH ER

BẢNG SỐ (MIN, MAX) Là số lượng nhỏ nhất và lớn nhất thực thể đó tham gia vào mối kết hợp. Có 4 loại: • 1,1 → Duy nhất một • 1, 𝑁 → Một hoặc nhiều • 0,1 → Không hoặc một • 0, 𝑁 → Không hoặc nhiều

CÁCH XÁC ĐỊNH BẢNG SỐ Ta xác định loại mối quan hệ là gì? Sau đó xác định bảng số VD: + Một nhân viên tham gia nhiều dự án (1, N) + Một nhân viên tham gia duy nhất một dự án (1, 1) + Một nhân viên tham gia nhiều nhất một dự án (0, 1)

CÁC BƯỚC VẼ MÔ HÌNH ER • B1: Xác định tập thực thể, thuộc tính, và khóa • B2: Xác định mối quan hệ • B3: Quyết định (min, max) cho mối quan hệ

ĐỀ THI GIỮA KÌ I 17 - 18

ĐỀ THI GIỮA KÌ I 17 - 18 Phần 1: 1. Vẽ mô hình ERD Mỗi trung tâm tin học cần tổ chức 1 CSDL để quản Lý với các thông tin như sau: • Mỗi phòng máy có mã phòng duy nhất để phân biệt với các phòng và tên người quản lý phòng. • Mỗi máy tính có một mã máy duy nhất. Ngoài ra, mỗi máy tính có cấu hình như: tốc độ CPU, dung lượng RAM, dung lượng ổ cứng. Các máy tính được bố trí trong các phòng máy. • Mỗi môn học có mã môn học duy nhất, có tên môn học và thời lượng môn học • Mỗi môn học có thể được thực hành ở các phòng máy khác nhau và một phòng máy có thể được đăng có cho nhiều môn học khác nhau, khi đăng ký phải biết ngày đăng ký. 2. Chuyển mô hình trên sang mô hình quan hệ

MÔ HÌNH ERD

Nguồn: kienthuc24h.vn

PHẦN 1.2: CHUYỂN ERD THÀNH QUAN HỆ

CÁC QUY TẮC BIẾN ĐỔI

CÁC LOẠI QUAN HỆ (MIN,MAX) -> MAX

PHẦN 2.1: NGÔN NGỮ DDL

NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU (DDL)

1. Lệnh tạo bảng (CREATE) 2. Lệnh sửa cấu trúc bảng (ALTER) 3. Lệnh xóa bảng (DROP)

PHẦN 2.1: NGÔN NGỮ DML

NGÔN NGỮ THAO TÁC DỮ LIỆU (DML)

1. Lệnh thêm dữ liệu (INSERT) 2. Lệnh xóa dữ liệu (DELETE) 3. Lệnh sửa dữ liệu (UPDATE)

CHUYỂN ERD THÀNH QUAN HỆ

ĐỀ THI GIỮA KÌ I 17 - 18 Phần II: (7.5 Điểm) Người quản trị của Công ty Cổ phần xe khách Phương Trang muốn quản lý các giao dịch mua vé xe khách trên các tuyến đường của hãng, đã thiết kế một CSDL Quản lý vé xe. Sau đây là một phần của lược đồ CSDL: XE(maxe, bienks, matuyen, sogheT1, sogheT2) Mô tả: Lược đồ quan hệ XE nhằm mô tả cho những chuyến xe đang được công ty quản lý. Mỗi xe được ghi nhận thông tin biển kiểm soát (bienks), mã tuyến mà xe đó đang hoạt động (matuyen), số ghế tầng 1 (sogheT1), số ghế tầng 2 (sogheT2) và được ấn định một mã số duy nhất (maxe) để theo dõi. TUYEN(matuyen, bendau, bencuoi, giatuyen, thoigiandk) Mô tả: Lược đồ quan hệ TUYEN nhằm mô tả thông tin tuyến xe. Mỗi thông tin tuyến xe sẽ bao gồm bến đầu (bendau), bến cuối (bencuoi), giá vé thông thường (giatuyen), thời gian dự kiến mỗi chuyến (thoigiandk) được tính bằng giờ và được đặt một mã số duy nhất (matuyen) để quản lý. KHACH(mahk, hoten, gioitinh, cmnd) Mô tả: Lược đồ quan hệ KHACH nhằm mô tả thông tin những hành khách. Thông tin được ghi nhận bao gồm: Mã hành khách (mahk), họ tên (hoten), giới tính (gioitinh) và số chứng minh nhân dân của hành khách đó (cmnd).

ĐỀ THI GIỮA KÌ I 17 - 18 VEXE(matuyen, mahk, ngaymua, giave) Mô tả: Lược đồ quan hệ VEXE nhằm mô tả thông tin vé xe của hành khách. Thông tin vé xe bao gồm: Mã tuyến (matuyen), mã hành khách (mahk), ngày mua vé (ngaymua) và giá vé mua tại thời điểm đó (giave). Một hành khách có thể mua nhiều vé của cùng một tuyến xe tại những thời điểm khác nhau. Lưu ý: thuộc tính gạch chân là khóa chính (thuộc tính) 1. Viết câu lệnh SQL: (1.5 Điểm) a. Dùng câu lệnh DDL định nghĩa bổ sung các vấn đề sau: (0.75 điểm) Thêm vào thuộc tính diachi có kiểu dữ liệu nvarchar(20) cho quan hệ KHACH. Sửa kiểu dữ liệu của thuộc tính diachi trong quan hệ KHACH thành nvarchar(50). Xóa thuộc tính diachi trong quan hệ KHACH. b. Giảm 10% giá vé được mua vào ngày ’20/10/2017′. (0.75 điểm)

GIỮA KÌ I 17 - 18 Phần II: 1. Viết lệnh SQL Câu a: ALTER TABLE KHACH ADD diachi nvarchar(20) ALTER TABLE KHACH ALTER COLUMN diachi nvarchar(50) ALTER TABLE KHACH DROP COLUMN diachi Câu b: UPDATE VEXE SET giave=giave*0.9 WHERE ngaymua=’20/ 10/2017′

PHẦN 2.3: ĐẠI SỐ QUAN HỆ

ĐẠI SỐ QUAN HỆ LÀ GÌ?

Là nền tảng toán học dùng để mô hình hóa CSDL quan hệ. INSERT, DROP, SELECT, …

Π, σ, ^, ˅, …

MỘT SỐ PHÉP TOÁN CƠ BẢN Phép chiếu π

Phép chọn σ

Phép giao ᴒ Phép kết

Phép trừ -

Phép chia ÷

Phép tích × Phép hội ᴗ

PHÉP CHỌN σ Là phép lấy các dòng (bản ghi) thỏa mãn điều kiện cho trước

VD: Liệt kê những sinh viên có giới tính nam và hơn 20 tuổi SINHVIEN (masv, hoten, gioitinh, chieucao) 𝜎

^ ′ ′ 𝑔𝑖𝑜𝑖𝑡𝑖𝑛ℎ= 𝑛𝑎𝑚

𝑡𝑢𝑜𝑖>20

(𝑆𝐼𝑁𝐻𝑉𝐼𝐸𝑁)

PHÉP CHỌN σ (16 – 17 HK1) Liệt kê những nhân viên vào làm từ năm 2015 NHANVIEN (manv, hoten, ngaysinh, ngayvaolam) 𝜎

𝑛𝑔𝑎𝑦𝑣𝑎𝑜𝑙𝑎𝑚>#1/1/2015#

𝜎𝑦𝑒𝑎𝑟

𝑛𝑔𝑎𝑦𝑣𝑎𝑜𝑙𝑎𝑚)≥2015

(𝑁𝐻𝐴𝑁𝑉𝐼𝐸𝑁) (𝑁𝐻𝐴𝑁𝑉𝐼𝐸𝑁)

PHÉP CHIẾU π Là phép trích chọn vài thuộc tính của bảng VD: Liệt kê họ tên những sinh viên có giới tính nam và cao 1m6 SINHVIEN (masv, hoten, gioitinh, chieucao) πℎ𝑜𝑡𝑒𝑛 𝜎

^ ′ ′ 𝑔𝑖𝑜𝑖𝑡𝑖𝑛ℎ= 𝑛𝑎𝑚

′ ′ 𝑐ℎ𝑖𝑒𝑢𝑐𝑎𝑜= 1𝑚6

(𝑆𝐼𝑁𝐻𝑉𝐼𝐸𝑁)

PHÉP TRỪ Là phép toán chỉ thực hiện được khi 2 quan hệ khả hợp (số thuộc tính bằng nhau + miền giá trị tương thích) VD: Liệt kê những sinh viên đạt giải tích nhưng không đạt đại số tuyến tính MASV 1

HOTEN A

2

B

3

C

Đạt GT

-

MASV 2

HOTEN B

Đạt ĐSTT

=

MASV 1

HOTEN A

3

C

PHÉP GIAO ᴒ Là phép toán chỉ thực hiện được khi 2 quan hệ khả hợp (số thuộc tính bằng nhau + miền giá trị tương thích) VD: Liệt kê những sinh viên đạt giải tích và đạt đại số tuyến tính MASV 1

HOTEN A

2

B

3

C

Đạt GT

ᴒ

MASV 3

HOTEN C

Đạt ĐSTT

=

MASV 3

HOTEN C

PHÉP HỘI ᴗ Là phép toán chỉ thực hiện được khi 2 quan hệ khả hợp (số thuộc tính bằng nhau + miền giá trị tương thích) VD: Liệt kê những sinh viên đạt giải tích hoặc đạt đại số tuyến tính MASV HOTEN 1 A

ᴗ

MASV HOTEN 4 D

MASV HOTEN 1 A

=

2

B

C

3

C

Đạt GT

4

D

2 3

B

Đạt ĐSTT

PHÉP TÍCH x Là phép dùng để liệt kê ra tất cả trường hợp giữa 2 quan hệ (bảng)

MASV HOTEN

1

A

2

B

2

MAMH

X

KTMT CSDL

2

=

MASV 1 2 1 2

HOTEN A B A B

4

MAMH KTMT KTMT CSDL CSDL

PHÉP KẾT Là phép tích kết hợp phép chọn Có 3 loại thường gặp

• Left outer join • Right outer join • Full outer join

RIGHT OUTER JOIN MASV HOTEN HV1 A HV2 B HV3 C HV4 D null null

MASV HV1 MASV HV2 MASV HV3 MASV null

MASV

MASV HV1 HV2 HV3 HV5

MAMH KTMT CSDL CSDL NMMMT

DIEM 6 7 4 9

HOTEN A HOTEN B HOTEN C HOTEN null

MASV HV1 MASV HV2 MASV HV3 MASV HV5

MAMH KTMT MAMH CSDL MAMH CSDL MAMH NMM MT

DIEM 6 DIEM 7 DIEM 4 DIEM 9

LEFT OUTER JOIN MASV HOTEN HV1 A HV2 B HV3 C HV4 D MASV

MASV HV1 HV2 HV3 HV1 null

MAMH KTMT CSDL CSDL NMMMT null

MASV HV1 HV1 MASV HV2 MASV HV3 MASV HV4 DIEM 6 7 4 9 null

HOTEN A A HOTEN B HOTEN C HOTEN D

MASV HV1 HV1 MASV HV2 MASV HV3 MASV null

MAMH KTMT NMM MAMH MT CSDL MAMH CSDL MAMH null

DIEM 6 9 DIEM 7 DIEM 4 DIEM null

FULL OUTER JOIN MASV HOTEN HV1 A HV2 B HV3 C HV4 D null null MASV

MASV HV1 HV2 HV3 HV5 null

MAMH KTMT CSDL CSDL NMMMT null

MASV HV1 MASV HV2 MASV HV3 MASV HV4 MASV null DIEM 6 7 4 9 null

HOTEN A HOTEN B HOTEN C HOTEN D HOTEN null

MASV HV1 MASV HV2 MASV HV3 MASV null MASV HV5

MAMH KTMT MAMH CSDL MAMH CSDL MAMH null MAMH NMM MT

DIEM 6 DIEM 7 DIEM 4 DIEM null DIEM 9

PHÉP CHIA ÷ Là phép ngược lại với phép nhân, B ÷ A - chọn ra những hàng (bản ghi) trong B thỏa toàn bộ A VD: Liệt kê những sinh viên học tất cả các môn đang được dạy MASV 1 2 1 3 2 1

MAMH CSDL NMMMT NMMMT KTMT CSDL KTMT

÷

MAMH CSDL NMMMT KTMT

=

MASV 1

PHÉP CHIA ÷ VD: Liệt kê những sinh viên học tất cả các môn đang được dạy MASV 1 2 1 3 2 4

MAMH CSDL NMMMT NMMMT KTMT CSDL KTMT

÷

MAMH CSDL NMMMT KTMT

=

MASV null

ĐỀ THI GIỮA KÌ I 17 - 18

GIỮA KÌ I 17 - 18 Phần 2: Người quản lý công ty CP xe khách Phương Trang muốn quản lý các giao dịch mua vé xe khách trên các tuyến đường của hãng, đã thiết kế CSDL Quản lý vé xe như sau: XE (maxe, bienks, matuyen, sogheT1, sogheT2)

TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) KHACH (mahk, hoten, gioitinh, cmnd)

VEXE (matuyen, mahk, ngaymua, giave)

GIỮA KÌ I 17 - 18 TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) a. Liệt kê matuyen, bencuoi của những xe có bến đầu là Đà Nẵng với giá tuyến > 300k

π𝑚𝑎𝑡𝑢𝑦𝑒𝑛,𝑏𝑒𝑛𝑐𝑢𝑜𝑖 𝜎 (𝑇𝑈𝑌𝐸𝑁)

^ ′ ′ 𝑏𝑒𝑛𝑑𝑎𝑢= Đà 𝑁ẵ𝑛𝑔

𝑔𝑖𝑎𝑡𝑢𝑦𝑒𝑛>300000

GIỮA KÌ I 17 - 18 TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) KHACH (mahk, hoten, gioitinh, cmnd) VEXE (matuyen, mahk, ngaymua, giave) b. Cho ra danh sách các tuyến (bendau, bencuoi) được khách hàng Nguyen Van A, số cmnd 012801156, mua vé vào ngày 02/01/2016.

𝐴 ← 𝜎(ℎ𝑜𝑡𝑒𝑛=′ 𝑁𝑔𝑢𝑦𝑒𝑛 𝑉𝑎𝑛 𝐴′ )^(𝑐𝑚𝑛𝑑=′ 012801156)^(𝑛𝑔𝑎𝑦𝑚𝑢𝑎=#2/1/2016#) (𝐾𝐻𝐴𝐶𝐻 𝑉𝐸𝑋𝐸) mahk

π𝑏𝑒𝑛𝑑𝑎𝑢,𝑏𝑒𝑛𝑐𝑢𝑜𝑖 (𝐴

𝑇𝑈𝑌𝐸𝑁)

matuyen

GIỮA KÌ I 17 - 18 TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) VEXE (matuyen, mahk, ngaymua, giave)

c. Khách hàng nào (makh) đã mua tất cả các tuyến có bendau là BX Miền Đông với giá tuyến > 100k. 𝐴 ← π𝑚𝑎𝑡𝑢𝑦𝑒𝑛 𝜎(𝑏𝑒𝑛𝑑𝑎𝑢=′ 𝐵𝑋 𝑀𝑖ề𝑛 Đô𝑛𝑔′ )^(𝑔𝑖𝑎𝑡𝑢𝑦𝑒𝑛>100000) (𝑇𝑈𝑌𝐸𝑁)

𝐵 ← 𝜋𝑚𝑎ℎ𝑘,𝑚𝑎𝑡𝑢𝑦𝑒𝑛 (𝑉𝐸𝑋𝐸) 𝐶 ←𝐵÷𝐴

GIỮA KÌ I 17 - 18 XE (maxe, bienks, matuyen, sogheT1, sogheT2) TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) d. Cho biết danh sách các tuyến xe (matuyen, bendau, bencuoi) chưa được phân xe π𝑚𝑎𝑡𝑢𝑦𝑒𝑛,𝑏𝑒𝑛𝑑𝑎𝑢,𝑏𝑒𝑛𝑐𝑢𝑜𝑖 (൫π𝑚𝑎𝑡𝑢𝑦𝑒𝑛 𝑇𝑈𝑌𝐸𝑁 matuyen

GIỮA KÌ I 17 - 18 TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) VEXE (matuyen, mahk, ngaymua, giave) e. Với mỗi mã tuyến, cho biết tổng số lượng hành khách đã mua vé năm 2016. 𝐴 ← 𝜋𝑚𝑎𝑡𝑢𝑦𝑒𝑛,𝑚𝑎ℎ𝑘 𝜎𝑦𝑒𝑎𝑟 𝐵(𝑚𝑎𝑡𝑢𝑦𝑒𝑛, 𝑠𝑙ℎ𝑘) ←

𝑛𝑔𝑎𝑦𝑚𝑢𝑎

(𝑇𝑈𝑌𝐸𝑁 =2016)

𝑚𝑎𝑡𝑢𝑦𝑒𝑛 𝜏 𝑐𝑜𝑢𝑛𝑡(𝑚𝑎ℎ𝑘)

𝑉𝐸𝑋𝐸)

matuyen

(𝐴)

GIỮA KÌ I 17 - 18 XE (maxe, bienks, matuyen, sogheT1, sogheT2) TUYEN (matuyen, bendau, bencuoi, giatuyen, thoigiandk) f. Cho biết thông tin tất cả các tuyến xe xuất phát từ bến đầu là TP. HCM và bến cuối là Đà Lạt và thông tin biển kiểm soát của các xe đã được phân công đi tuyến này (nếu có) 𝜎(𝑏𝑒𝑛𝑑𝑎𝑢=′ 𝑇𝑃.𝐻𝐶𝑀′ )^(𝑏𝑒𝑛𝑐𝑢𝑜𝑖=′ Đà 𝐿ạ𝑡 ′ ) (𝑇𝑈𝑌𝐸𝑁

matuyen

𝑋𝐸)

51

BAN HỌC TẬP ĐOÀN KHOA CNPM

Chuỗi Training giữa học kì I năm học 2018 - 2019

HẾT

Cảm ơn các bạn đã theo dõi Chúc các bạn có kết quả thi thật tốt !

Ban học tập

Our Phone

Email / Group

Khoa Công Nghệ Phần Mềm Trường ĐH Công Nghệ Thông Tin

0932 470 201 0936 645 393 01666 27 27 03

[email protected] fb.com/groups/bht.cnpm.uit/