Bài giảng Cơ sở dữ liệu - Nguyễn Hồng Phương

)))((())(( EE XYFXFX   NHP 19 Các phép biến đổi tương đương biểu thức ĐSQH (tiếp) 6. Quy tắc đối với phép chọn và phép tích Đề các • Ta ký hiệu: – E1(U1) có nghĩa là biểu thức E1 xác định trên tập thuộc tính U1 – F1(U1) có nghĩa là điều kiện chọn F1 xác định trên tập thuộc tính U1 – Quy tắc biến đổi liên quan đến phép chọn và tích Đề các được phát biểu như sau: • tương đương với: – trong trường hợp F = F1(U1) – trong trường hợp F = F1(U1) F2(U2) – trong trường hợp F = F1(U1) F2(U1U2) ))()(( 2211 UEUEF  211 )( EEF  )()( 2211 EE FF   ))(( 2112 EEFF  NHP 20 Các phép biến đổi tương đương biểu thức ĐSQH (tiếp) 7. Quy tắc đối với phép chọn và phép hợp: 8. Quy tắc đối với phép chọn và phép trừ: )()()( 2121 EEEE FFF   )()()( 2121 EEEE FFF   10/11/2019 6 NHP 21 Các phép biến đổi tương đương biểu thức ĐSQH (tiếp) 9. Quy tắc đối với phép chiếu và tích Đề các: 10.Quy tắc đối với phép chiếu và phép hợp: 21 212211 ,, )()())()(( UZUYYZX EEUEUE ZYX   )()()( 2121 EEEE XXX   NHP 22 Ví dụ • Tìm tên hãng cung ứng ít nhất 1 mặt hàng màu đỏ hoặc màu xanh SELECT sname FROM S, P, SP WHERE S.sid = SP.sid AND P.pid = SP.pid AND (colour = ‘Red’ OR colour = ‘Green’); • Biểu thức đại số quan hệ tương đương với câu truy vấn trên là: ))(( )'''Re'(.... PSPSGreencolourdcolourpidSPpidPsidSPsidSsname   NHP 23 NHP 24 Lời hay ý đẹp "Phẩm cách chân chính của con người là ở trong cách họ sống chứ không phải ở cái họ có" Blackie 10/11/2019 1 1 An toàn và toàn vẹn dữ liệu Nguyễn Hồng Phương phuongnh@soict.hust.edu.vn Bộ môn Hệ thống thông tin Viện Công nghệ thông tin và Truyền thông Đại học Bách Khoa Hà Nội 2 Nội dung 1. Đặt vấn đề 2. An toàn dữ liệu 3. Toàn vẹn dữ liệu 4. Điều khiển tương tranh 1. Đặt vấn đề • Một số yêu cầu đối với thiết kế, cài đặt và quản trị CSDL: –Đảm bảo tính an toàn của dữ liệu • Tránh truy nhập không hợp lệ từ phía người dùng: phân quyền, xác minh và kiểm tra quyền hạn người sử dụng. –Đảm bảo tính đúng đắn của dữ liệu • Tránh sai sót khi cập nhật dữ liệu: định nghĩa và kiểm tra các ràng buộc dữ liệu. • Tránh sai sót trong quá trình thao tác với dữ liệu: kiểm tra tính toàn vẹn của các thao tác với dữ liệu. 3 4 2. An toàn dữ liệu • Định nghĩa: Tính an toàn dữ liệu là sự bảo vệ dữ liệu trong CSDL chống lại những truy nhập, sửa đổi hay phá hủy bất hợp pháp. • Người sử dụng hợp pháp là những người sử dụng được cấp phép, ủy quyền. • Để đảm bảo tính an toàn cho CSDL, cần có một cơ chế để quản lý người dùng hợp lý. • Những nhóm người dùng khác nhau trong hệ CSDL có quyền sử dụng khác nhau đối với các đối tượng dữ liệu trong CSDL. 10/11/2019 2 Trục tam giác 5 Người dùng, nhóm người dùng Các quyền Các đối tượng dữ liệu trong CSDL 6 2.1. Các quyền truy nhập của người sử dụng • Đối với người khai thác CSDL: –Quyền đọc dữ liệu: được phép đọc một phần hay toàn bộ dữ liệu trong CSDL. –Quyền cập nhật dữ liệu: được phép sửa đổi một số giá trị nhưng không được xóa dữ liệu trong CSDL. –Quyền xóa dữ liệu: được phép xóa dữ liệu trong CSDL. –Quyền bổ sung dữ liệu: được phép thêm dữ liệu mới vào trong CSDL nhưng không được phép thay đổi dữ liệu 2.1. Các quyền truy nhập của người sử dụng • Đối với người quản trị CSDL: –Quyền tạo chỉ dẫn trên các quan hệ trong CSDL –Quyền thay đổi sơ đồ cơ sở dữ liệu: thêm hay xóa các thuộc tính của các quan hệ trong CSDL –Quyền loại bỏ quan hệ trong CSDL –Quyền quản lý tài nguyên: được phép thêm các quan hệ mới vào CSDL 7 2.2. Người dùng • Một người dùng cụ thể (user) – ví dụ: phuongnh, postgres, • Một nhóm người dùng (group) – nhóm có các thành viên (member) – ví dụ: group_h3t có phuongnh, lamdb, trinhvt,. • Một số hệ quản trị không phân biệt 2 khái niệm trên, mà gọi chung là vai trò (role) – ví dụ: trong PostGreSQL, role có thể là user hoặc group (nếu có member) 8 10/11/2019 3 2.3. Các đối tượng dữ liệu • Tables • Views 9 10 Trách nhiệm của người quản trị hệ thống • Để có thể phân biệt được người sử dụng trong hệ CSDL, người quản trị hệ thống phải có trách nhiệm: – Xác định các quyền cụ thể mà mỗi người sử dụng hay một nhóm người sử dụng được phép thực hiện, xác định vai trò và trách nhiệm của mỗi người sử dụng. Điều này được gọi chung là Phân quyền người sử dụng. – Cung cấp một phương tiện cho người sử dụng để hệ thống có thể nhận biết được người sử dụng đó hay còn gọi là Xác minh người sử dụng. 11 Xác minh người sử dụng • Để xác minh được người sử dụng, người ta có thể dùng các kỹ thuật sau: – Kỹ thuật dùng tài khoản có tên và mật khẩu, mật khẩu cũng được bảo vệ bởi hệ thống. – Kỹ thuật sử dụng các hàm kiểm tra người sử dụng: Hệ thống đưa cho người sử dụng một số ngẫu nhiên x, người sử dụng dùng một hàm F tính nhẩm kết quả và đưa kết quả y = F(x) vào hệ thống. Trong lúc đó, hệ thống cũng tính toán và so sánh kết quả với y. Người sử dụng hợp pháp là người biết hàm biến đổi F và đưa vào giá trị y đúng. – Kỹ thuật dùng thẻ điện tử, thẻ thông minh. – Kỹ thuật sử dụng nhận dạng tiếng nói, vân tay v..v. 12 Kiểm tra quyền truy nhập của người sử dụng • Mỗi người sử dụng sẽ có một bộ hồ sơ do người quản trị thiết lập và được hệ thống quản lý, trong hồ sơ đó sẽ có chi tiết về các thao tác người sử dụng được phép thực hiện: – Phân quyền người sử dụng: Người quản trị hệ thống phải có trách nhiệm xác định khung nhìn để kiểm soát xem mỗi người sử dụng chỉ được truy nhập phần dữ liệu nào trong CSDL và có được các quyền nào trong số các quyền đọc, thêm, xóa, sửa đổi. – Xác định và kiểm soát sự lưu chuyển dữ liệu: Hệ thống phải bảo trì danh sách các quyền một cách chặt chẽ vì người sử dụng có thể được quyền lan truyền các quyền cho người sử dụng khác. 10/11/2019 4 13 Các câu lệnh an toàn dữ liệu trong SQL • Câu lệnh tạo khung nhìn • Câu lệnh phân quyền cho người sử dụng • Câu lệnh thu hồi quyền của người sử dụng 14 Câu lệnh tạo khung nhìn • CREATE VIEW [(d/s cột)] AS • Danh sách các cột trong khung nhìn là phần không bắt buộc. Trong trường hợp người sử dụng muốn đặt tên khác cho các cột xuất hiện trong khung nhìn thì người sử dụng có thể chỉ ra tên các cột, dữ liệu trên cột thì tương ứng với các cột trong mệnh đề Select của câu truy vấn. 15 Ví dụ câu lệnh tạo khung nhìn • Cho cơ sở dữ liệu gồm 2 quan hệ: Nhânviên(Id,Họtên,ĐC,Lương,NămBD,Đánhgiá,PhòngCT) Phòng(PId, Tên, ĐC, Điệnthoại, Trưởngphòng) • Câu lệnh tạo khung nhìn cho một nhân viên của phòng Khoa Học có thể được định nghĩa như sau: CREATE VIEW NVKH(HọtênNhânviên, Địachỉliênlạc) AS SELECT Họtên, Địachỉ FROM Nhânviên WHERE PhòngCT IN (SELECT PId FROM Phòng WHERE Tên ='Khoa Học') 16 Câu lệnh phân quyền cho NSD • GRANT ON TO [WITH GRANT OPTION] • : có thể bao gồm 1 hay nhiều thao tác được liệt kê dưới đây: – Insert: chèn dữ liệu vào trong CSDL có sẵn nhưng không được thay đổi bất kỳ mục dữ liệu nào trong CSDL – Update: sửa đổi dữ liệu nhưng không được xóa dữ liệu – Delete: xóa dữ liệu trong CSDL – Select : tìm kiếm – Create: tạo lập các quan hệ mới – Alter: Thay đổi cấu trúc của quan hệ – Drop: Loại bỏ quan hệ – Read/Write: Đọc và Ghi 10/11/2019 5 17 Câu lệnh phân quyền cho NSD (tiếp) • : bảng hoặc khung nhìn • : Một người hay một nhóm hay một danh sách người sử dụng. Từ khóa public được dùng thay thế cho mọi người sử dụng • [With Grant Option] Nếu dùng từ khóa này trong câu lệnh phân quyền thì người dùng xuất hiện trong có quyền được lan truyền các quyền vừa được tuyên bố cho những người dùng khác 18 Ví dụ câu lệnh phân quyền cho NSD • Trao quyền đọc, ghi, tìm kiếm, sửa đổi dữ liệu cho nhân viên tên Hoa của phòng Khoa học trên khung nhìn vừa tạo lập trong phần trước GRANT read, write, select, update ON NVKH TO Hoa; • Trao quyền cho trưởng phòng Khoa học – ông HungNC GRANT read, write, select, update, delete ON NVKH TO HungNC WITH GRANT OPTION; 19 Câu lệnh thu hồi quyền của NSD • REVOKE ON FROM [RESTRICT/CASCADE] • , , <D/s người dùng> giống như đối với câu lệnh GRANT. • Phần [RESTRICT/CASCADE] là chỉ ra cơ chế thu hồi với các quyền đã được người dùng trong lan truyền 20 Câu lệnh thu hồi quyền của NSD (tiếp) • Nếu Restrict thì có nghĩa là chỉ hủy bỏ quyền của những người có trong danh sách, quyền đã được lan truyền cho người khác không bị thu hồi. • Nếu dùng Cascade thì hủy bỏ quyền của người trong , đồng thời kéo theo hủy bỏ quyền mà người dùng đó đã luân chuyển cho những người khác. • Ví dụ: REVOKE update, delete ON NVKH FROM HungNC CASCADE 10/11/2019 6 21 3. Toàn vẹn dữ liệu • Định nghĩa: Tính toàn vẹn dữ liệu là sự bảo vệ dữ liệu trong CSDL chống lại những sự sửa đổi, phá hủy vô căn cứ để đảm bảo tính đúng đắn và chính xác của dữ liệu. • Các thao tác có thể ảnh hưởng đến tính đúng đắn của CSDL là thêm, xóa, sửa đổi. • Để đảm bảo tính toàn vẹn dữ liệu, cần phải chỉ ra và duy trì những ràng buộc toàn vẹn liên kết với mỗi quan hệ. Các ràng buộc toàn vẹn cung cấp 1 phương tiện để đảm bảo rằng các thao tác được thực hiện bởi những người sử dụng hợp pháp không làm mất đi tính đúng đắn của CSDL. • Trong hệ thống đa người dùng, để đảm bảo được toàn vẹn dữ liệu, hệ thống còn phải có được một trình điều khiển tương tranh để tránh đụng độ giữa các thao tác được đưa ra bởi những người sử dụng khác nhau tại cùng một thời điểm 22 23 Các ràng buộc toàn vẹn trong SQL • Các ràng buộc về khóa chính, khóa ngoài, kiểm tra miền giá trị sử dụng Check đã được đề cập đến khi nói về câu lệnh tạo bảng trong CSDL. • Các khẳng định (assertion) • Các kích hoạt (trigger) Các khẳng định • Là một vị từ biểu thị một điều kiện mà CSDL phải luôn luôn thỏa mãn. • Các khẳng định được tạo ra bằng câu lệnh: CREATE ASSERTION CHECK 24 10/11/2019 7 25 Ví dụ về khẳng định • Số lượng mặt hàng được cung cấp bởi các hãng có số nhân viên < 50 phải nhỏ hơn 100: CREATE ASSERTION KĐSốlượng CHECK NOT EXISTS (SELECT * FROM S WHERE numofemps < 50 AND sid IN (SELECT sid FROM SP WHERE quantity >= 100)) Ví dụ về khẳng định (tiếp) • Lương của nhân viên không được cao hơn lương người quản lý phòng ban của nhân viên đó. CREATE ASSERTION Salary_Constraint CHECK (NOT EXISTS (SELECT * FROM Employee E, Employee M, Department D WHERE E.Salary>M.Salary AND E.Dno=D.Number AND D.MgrSSN=M.SSN)) 26 27 Các kích hoạt (trigger) • Là một thủ tục lưu trữ hệ thống (stored procedure) đặc biệt, được thực thi một cách tự động khi có sự kiện gây biến đổi dữ liệu như Update, Insert hay Delete • Được dùng để đảm bảo toàn vẹn dữ liệu hay thực hiện các quy tắc nghiệp vụ nào đó. • Khi nào sử dụng trigger? – khi các biện pháp đảm bảo toàn vẹn dữ liệu khác như Constraint không thể thỏa mãn yêu cầu của ứng dụng Các kích hoạt (trigger) • Constraint thuộc loại toàn vẹn dữ liệu khai báo: kiểm tra dữ liệu trước khi cho phép nhận vào bảng • Trigger thuộc loại toàn vẹn dữ liệu thủ tục nên việc Insert, Update, Delete xảy ra rồi mới kích hoạt trigger. –Đôi khi, do nhu cầu thay đổi dây chuyền, có thể sử dụng trigger • Đặc điểm của trigger –một trigger có thể làm nhiều công việc, có thể được kích hoạt bởi nhiều sự kiện 28 10/11/2019 8 Các kích hoạt (trigger) – trigger không thể được tạo ra trên bảng tạm hoặc bảng hệ thống – trigger chỉ có thể được kích hoạt tự động bởi các sự kiện mà không thể chạy thủ công được. –có thể áp dụng trigger cho view –khi trigger được kích hoạt • dữ liệu mới được insert sẽ được chứa trong bảng "inserted" • dữ liệu mới được delete sẽ được chứa trong bảng "deleted" • đây là hai bảng tạm nằm trên bộ nhớ, và chỉ có giá trị bên trong trigger 29 30 Ví dụ về trigger • Nhânviên(ID,Họtên,Lương,Địachỉ,Ngư ờiquảnlý) • Một nhân viên bao giờ cũng có lương ít hơn lương người trưởng phòng, điều kiện này phải được kiểm tra khi thêm bộ dữ liệu. CREATE TRIGGER ThemNV ON INSERT Nhânviên IF Nhânviên.Lương > (SELECT E.Lương FROM Nhânviên AS E WHERE E.ID = Nhânviên.Ngườiquảnlý) THEN ABORT; 31 4. Điều khiển tương tranh • Trong hệ CSDL đa người dùng, hệ thống cần đưa ra giải pháp chống đụng độ giữa các giao dịch (một dãy các thao tác) được đưa ra bởi những người dùng khác nhau để tránh việc một đối tượng dữ liệu nào đó bị làm mất tính đúng đắn trong quá trình cập nhật. 4.1. Giao dịch • Một giao dịch hình thành nên 1 đơn vị công việc trong 1 DBMS đ/v 1 CSDL, được coi là cố kết và tin cậy độc lập với các giao dịch khác. • Giao dịch có nhiều bước, và phải được thực hiện một cách trọn vẹn. • Trạng thái trung gian giữa các bước là ẩn đ/v các giao dịch khác. • Nếu có sự cố mà giao dịch không thể hoàn thành, thì tất cả các bước không ảnh hưởng lên CSDL. 32 10/11/2019 9 Ví dụ về giao dịch • Một CSDL ngân hàng chứa thông tin tài khoản của các khách hàng và tài khoản quỹ của các chi nhánh. • Giả sử, thực hiện giao dịch chuyển 100 đô-la từ tài khoản của Alice sang tài khoản của Bob: 33 UPDATE accounts SET balance = balance - 100.00 WHERE name = ’Alice’; UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Alice’); UPDATE accounts SET balance = balance + 100.00 WHERE name = ’Bob’; UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Bob’); 4.2. Các tính chất của một giao dịch • Tính nguyên tố (Atomicy) – Hoặc là tất cả các thao tác được thực hiện hoặc là không thao tác nào được thực hiện – Đảm bảo tính không thể chia cắt, không thể rút gọn • Tính nhất quán (Consistency) – Dữ liệu nhất quán sau khi giao dịch thực hiện. • Tính cách ly (Isolation) – xác định cách mà những thay đổi bởi một thao tác là ẩn với các thao tác đồng thời khác. 34 4.2.Các tính chất của một giao dịch • Tính bền vững (Durability) –Đảm bảo giao dịch đã được xác nhận sẽ tồn tại vĩnh cửu. –ví dụ, một vị trí chỗ ngồi trên máy bay đã được đặt chỗ thì vị trí đó sẽ vẫn trong trạng thái đã đặt chỗ cho dù hệ thống có vấn đề. • Tất cả 4 tính chất trên, gọi tắt là ACID 35 • Các thao tác hình thành nên một giao dịch có thể được nhúng trong một chương trình ứng dụng hoặc được xác định bởi ngôn ngữ cấp cao như SQL. • Để biết một giao dịch diễn ra, cần xác định tường minh câu lệnh bắt đầu (begin transaction) và kết thúc (end transaction) của giao dịch trong chương trình ứng dụng. 36 10/11/2019 10 • Giao dịch chỉ đọc (read-only) –không cập nhật dữ liệu, mà chỉ lấy ra dữ liệu. • Để đơn giản, giả sử các thao tác truy cập CSDL trong một giao dịch gồm có: – read_item(X): đọc 1 khoản mục CSDL tên là X vào một biến trong chương trình cũng tên là X. –write_item(X): ghi giá trị của biến X trong chương trình vào khoản mục CSDL cũng tên là X. 37 • Đơn vị cơ bản của việc chuyển dữ liệu từ đĩa vào bộ nhớ chính là khối (block). • Thực hiện lệnh read_item(X) bao gồm các bước sau: –Tìm địa chỉ của khối trên đĩa mà chứa khoản mục X. –Sao chép khối đó vào bộ đệm trên bộ nhớ chính (nếu trên bộ đệm chưa có). –Sao chép khoản mục X từ bộ đệm vào biến chương trình X. 38 • Thực hiện lệnh write_item(X) bao gồm các bước sau: – Tìm địa chỉ khối trên đĩa mà nó chứa khoản mục X. – Sao chép khối đó vào bộ đệm trong bộ nhớ chính (nếu bộ đệm chưa có). – Sao chép khoản mục X từ biến chương trình tên là X vào vị trí chính xác trên bộ đệm. – Lưu trữ khối đã cập nhật này từ bộ đệm lên đĩa (có thể ngay tức thì hoặc lưu trữ sau). 39 4.3. Tại sao lại phải điều khiển tương tranh? • Các vấn đề –The Lost Update –The Temporary Update (Dirty Read) –The Incorrect Summary –The Unrepeatable Read 40 10/11/2019 11 The lost update • Hiện tượng này xuất hiện khi hai giao dịch truy cập vào cùng các khoản mục dữ liệu nhưng thao tác của hai giao dịch lại xen kẽ, làm cho giá trị của các khoản mục dữ liệu không còn đúng nữa. 41 read_item (X); X:=X-N; write_item (X); read_item (Y); Y:=Y+N; write_item (Y); read_item (X); X:=X+M; write_item (X); T2T1 Time The Temporary Update • Xuất hiện khi 1 giao dịch cập nhật 1 khoản mục dữ liệu, nhưng sau đó không hoàn thành các bước tiếp theo => giao dịch không trọn vẹn nên phải roll-back giá trị đã cập nhật về giá trị cũ. • Khoản mục dữ liệu đã được cập nhật kia lại được sử dụng bởi 1 giao dịch khác trước khi nó được roll- back về giá trị cũ 42 read_item (X); X:=X-N; write_item (X); read_item (Y); read_item (X); X:=X+M; write_item (X); T2T1 Time Giao dịch T1 fails và phải thay đổi giá trị của X về giá trị cũ của nó; trong khi đó T2 lại đọc giá trị không đúng tạm thời của X. The Incorrect Summary • Một giao dịch tính hàm tích lũy trên các bản ghi đang bị cập nhật bởi 1 giao dịch khác =>hàm tích lũy này có thể tính toán dựa trên một số giá trị đã cập nhật và một số giá trị chưa cập nhật. 43 read_item(X); X:=X-N; write_item(X); read_item(Y); Y:=Y+N; write_item(Y); sum:=0; read_item(A); sum:=sum+A; read_item(X); sum:=sum+X; read_item(Y); sum:=sum+Y; T1 T3 T3 đọc X sau khi X đã trừ N và đọc Y trước khi Y cộng N Time The Unrepeatable Read • Một giao dịch T đọc một khoản mục dữ liệu 2 lần. Khoản mục dữ liệu này bị một giao dịch khác thay đổi giữa 2 lần đọc đó. • Do đó, T nhận các giá trị khác nhau cho 2 lần đọc cùng một khoản mục. 44 10/11/2019 12 45 4.4. Các kỹ thuật điều khiển tương tranh • Kỹ thuật dùng khóa: Khi một giao dịch cần dữ liệu nào thì xin hệ điều hành một khóa trên phần dữ liệu đó, các giao dịch khác phải đợi đến khi giải phóng khóa mới được sử dụng phần dữ liệu đó. Có thể người ta sử dụng các loại khóa khác nhau ví dụ như khóa đọc – cho phép nhiều giao dịch đọc cùng 1 lúc, khóa ghi – chỉ 1 giao dịch có được tại một thời điểm. • Kỹ thuật gán nhãn thời gian: Mỗi giao dịch được gán một nhãn T theo thời gian, giao dịch nào cần được ưu tiên thì được gán nhãn thời gian nhỏ hơn và được thực hiện trước. Kỹ thuật này giúp đưa yêu cầu đồng thời về thực hiện tuần tự. 46 47 Lời hay ý đẹp "Khi nói sự thật bạn sẽ không phải nhớ mình vừa nói gì, mà bạn cũng không bao giờ quên những gì mình vừa nói" S.Raybum 10/11/2019 1 1 Tổ chức dữ liệu vật lý Nguyễn Hồng Phương phuongnh@soict.hust.edu.vn Bộ môn Hệ thống thông tin Viện Công nghệ thông tin và Truyền thông Đại học Bách Khoa Hà Nội 2 Nội dung • 1. Mô hình tổ chức bộ nhớ • 2. Tổ chức tệp đống • 3. Tổ chức tệp băm • 4. Tổ chức tệp chỉ dẫn • 5. Cây cân bằng 1. Mô hình tổ chức bộ nhớ • Phương tiện nhớ của máy tính hình thành một phân cấp bộ nhớ bao gồm 2 loại chính: – Bộ nhớ sơ cấp: • Bao gồm các thiết bị nhớ mà CPU của máy tính có thể thao tác trực tiếp trên đó, như bộ nhớ chính, bộ nhớ đệm cache. • Cung cấp cơ thế truy cập dữ liệu nhanh nhưng lại bị giới hạn về dung lượng. – Bộ nhớ thứ cấp: • Bao gồm các đĩa từ, đĩa quang, băng từ. • Dung lượng lớn hơn, chi phí rẻ hơn. • Cung cấp cơ chế truy cập dữ liệu chậm hơn. • CPU không xử lý dữ liệu trực tiếp trên bộ nhớ thứ cấp mà dữ liệu được sao chép sang bộ nhớ sơ cấp để CPU xử lý. 3 4 Bộ nhớ ngoài • Bộ nhớ ngoài (bộ nhớ thứ cấp): đĩa từ, băng từ,... • Đĩa được chia thành các khối vật lý (sector) - 512 byte đến 4096 byte được đánh địa chỉ khối gọi là địa chỉ tuyệt đối • Mỗi tệp dữ liệu chiếm 1 hoặc nhiều khối • Mỗi khối chứa 1 hoặc nhiều bản ghi 10/11/2019 2 5 1. Mô hình tổ chức bộ nhớ (tiếp) • Thao tác với dữ liệu của tệp thông qua địa chỉ tuyệt đối của các khối. • Các bản ghi đều có địa chỉ: –địa chỉ tuyệt đối của byte đầu tiên –địa chỉ khối và số byte tính từ đầu khối đến vị trí đầu bản ghi • Địa chỉ của các bản ghi/khối được lưu ở 1 tệp => sử dụng con trỏ (pointer) để truy cập dữ liệu của tệp. 6 2. Tổ chức tệp đống (Heap file) • Tổ chức dữ liệu –Bản ghi lưu trữ kế tiếp trong các khối, không tuân theo một thứ tự đặc biệt nào. • Các thao tác –Tìm kiếm một bản ghi: tìm kiếm một bản ghi có giá trị khóa cho trước => quét toàn bộ tệp. –Thêm một bản ghi: thêm bản ghi mới vào sau bản ghi cuối cùng k1 k2 k3  k4 k5 k6  k7 k8  7 2. Tổ chức tệp đống (Heap file) • Các thao tác (tiếp) –Xóa một bản ghi: thao tác xóa bao hàm thao tác tìm kiếm. Nếu có bản ghi cần xóa thì nó sẽ được đánh dấu là xóa => hệ thống cần tổ chức lại đĩa định kỳ. –Sửa một bản ghi: tìm bản ghi rồi sửa một hay nhiều trường. 8 2. Tổ chức tệp đống (Heap file) • Ví dụ: Thêm bản ghi có giá trị khóa là 32 Xóa bản ghi có giá trị khóa là 64 10/11/2019 3 9 3. Tổ chức tệp băm (Hashed files) • Hàm băm: h(x) nhận một giá trị trong đoạn [0,k], ví dụ: h(x)=x mod k • Tổ chức tệp dữ liệu –Phân chia các bản ghi vào các cụm. –Mỗi cụm gồm một hoặc nhiều khối. –Mỗi khối chứa số lượng bản ghi cố định. –Tổ chức lữu trữ dữ liệu trong mỗi cụm áp dụng theo tổ chức đống • Tiêu chí chọn hàm băm: phân bố các bản ghi tương đối đồng đều theo các cụm. 10 3. Tổ chức tệp băm (Hashed files) 11 3. Tổ chức tệp băm (Hashed files) 1 2 4 3 Store hash 1 1 2 2 3 3 4 4 h(x) = x mod 5 0 12 3. Tổ chức tệp băm (Hashed files) 12 10 17 18 Store hash 1 1 2 2 3 3 4 4 h(x) = x mod 5 0 12 10 17 18 10/11/2019 4 13 3. Tổ chức tệp băm (Hashed files) • Các thao tác –Tìm kiếm một bản ghi: để tìm bản ghi có khóa x, tính h(x) sẽ được cụm chứa bản ghi, sau đó tìm kiếm theo tổ chức đống. –Thêm một bản ghi: thêm 1 bản ghi có giá trị khóa là x. • nếu trong tệp đã có một bản ghi có trùng khóa x =>bản ghi mới sai (vì khóa là duy nhất!) • nếu không có bản ghi trùng khóa, bản ghi được thêm vào khối còn chỗ trống đầu tiên trong cụm, nếu hết chỗ thì tạo khối mới. 14 3. Tổ chức tệp băm (Hashed files) –Xóa một bản ghi: tìm kiếm bản ghi rồi xóa –Sửa đổi một bản ghi: • nếu trường cần sửa có tham gia vào trong khóa thì việc sửa sẽ là loại bỏ bản ghi này và thêm mới 1 bản ghi (bản ghi có thể thuộc vào 1 cụm khác) • nếu trường cần sửa không thuộc khóa: tìm kiếm rồi sửa. Nếu bản ghi không tồn tại thì xem như có lỗi. 15 4. Tổ chức tệp chỉ dẫn(Indexed Files) • Giả sử giá trị các khóa của các bản ghi được sắp xếp tăng dần. • Tệp chỉ dẫn được tạo bằng cách chọn các giá trị khóa trong các bản ghi • Tệp chỉ dẫn bao gồm các cặp (k,d), trong đó k là giá trị khoá của bản ghi đầu tiên, d là địa chỉ của khối (hay con trỏ khối). 16 4. Tổ chức tệp chỉ dẫn(Indexed Files) • Tìm kiếm trên tệp chỉ dẫn –Cho một giá trị khóa ki, tìm một bản ghi (km,d) trong tệp chỉ dẫn sao cho km<=ki và: • hoặc (km,d) là bản ghi cuối cùng trong tệp chỉ dẫn • hoặc bản ghi tiếp theo (km+1,d') thỏa mãn ki<km+1 –Khi đó, chúng ta nói km phủ ki –Tìm kiếm này có thể là: • tuần tự • nhị phân 10/11/2019 5 17 4. Tổ chức tệp chỉ dẫn(Indexed Files) • Các thao tác – Tìm kiếm một bản ghi – Thêm một bản ghi: xác định khối i sẽ chứa bản ghi đó • nếu trong khối i còn chỗ thì đặt bản ghi này vào đúng chỗ theo thứ tự sắp xếp của khóa, dồn toa các bản ghi đằng sau nó. • nếu khối i hết chỗ thì việc thêm này sẽ đẩy bản ghi cuối cùng trong khối sang làm bản ghi đầu tiên của khối tiếp theo i+1 => sửa bản ghi chỉ dẫn tương ứng • nếu bản ghi mới này có giá trị khóa lớn hơn tất cả mọi khóa trong tệp dữ liệu chính và không còn chỗ thì tạo thêm một khối mới. 18 4. Tổ chức tệp chỉ dẫn(Indexed Files) –Xóa một bản ghi: giống như thêm một bản ghi, nếu xóa mà tạo thành 1 khối rỗng, khi đó có thể loại bỏ cả khối đó. –Sửa một bản ghi: • Sử dụng thủ tục tìm kiếm để xác định bản ghi cần sửa • nếu các trường cần sửa không phải là khóa thì sửa bình thường • nếu các trường cần sửa tham gia vào khóa thì quá trình sửa sẽ là quá trình thêm và xóa 1 bản ghi. 19 5. Cây cân bằng(Balanced-trees) • B-tree được tổ chức theo cấp m, có các tính chất sau đây: –Gốc của cây hoặc là một nút lá hoặc ít nhất có hai con. –Mỗi nút (trừ nút gốc và nút lá) có từ [m/2] đến m con. –Mỗi đường đi từ nút gốc đến bất kỳ nút lá nào đều có độ dài như nhau. 20 5. Cây cân bằng(Balanced-trees) • Cấu trúc của mỗi nút trong B-cây có dạng (p0, k1, p1, k2,...,kn, pn) với pi (i=1..n) là con trỏ trỏ tới khối i của nút có ki là khoá đầu tiên của khối đó. Các khoá k trong một nút được sắp xếp theo thứ tự tăng dần. 10/11/2019 6 21 5. Cây cân bằng(Balanced-trees) • Mọi khoá trong cây con, trỏ bởi con trỏ p0 đều nhỏ hơn k1; • Mọi khoá trong cây con, trỏ bởi con trỏ pi đều nhỏ hơn ki+1. • Mọi khoá trong cây con, trỏ bởi con trỏ pn đều lớn hơn kn. 22 5. Cây cân bằng(Balanced-trees) • Các thao tác –Tìm kiếm một bản ghi: xác định đường dẫn từ nút gốc tới nút lá chứa bản ghi này –Thêm một bản ghi: • Xác định vị trí nút lá sẽ chứa bản ghi này (như tìm kiếm) • Nếu còn chỗ thì thêm bình thường • Nếu hết chỗ thì phải tạo thêm nút lá mới, chuyển nửa dữ liệu cuối của nút lá hiện tại sang nút mới, sau đó thêm bản ghi mới này vào vị trí phù hợp nút lá hiện tại hoặc nút mới tạo • Rất có khả năng “động chạm” đến nút cha,.nút gốc. 23 5. Cây cân bằng(Balanced-trees) –Loại bỏ 1 bản ghi: • Dùng thủ tục tìm kiếm một bản ghi để xác định nút L có thể chứa bản ghi đó. • Rất có khả năng “động chạm” đến nút cha,,nút gốc. 24 Kết luận • Tổ chức tệp chỉ dẫn: – được áp dụng phổ biến – Với các ứng dụng yêu cầu cả xử lý tuần tự và truy nhập trực tiếp đến các bản ghi – Hiệu năng sẽ giảm khi kích thước tệp tăng =>chỉ dẫn B-cây • Tổ chức băm: – Dựa trên 1 hàm băm, cho phép tìm thấy địa chỉ khoản mục dữ liệu một cách trực tiếp – Hàm băm tốt? Phân bố các bản ghi đồng đều trong các cụm 10/11/2019 7 25 26 Lời hay ý đẹp Bản chất của tình bạn chân thật là khoan dung với những lỗi nhỏ của bạn David Storey 10/11/2019 1 1 Thủ tục lưu trữ hệ thống và kích hoạt Stored Procedure & Trigger Nguyễn Hồng Phương Email: phuong.nguyenhong@hust.edu.vn Site: Bộ môn Hệ thống thông tin Viện Công nghệ thông tin và Truyền thông Đại học Bách Khoa Hà Nội Nội dung 1. Stored Procedure 1.1. Khái niệm 1.2. Cú pháp 2. Trigger 2.1. Khái niệm 2.2. Cú pháp https://freetuts.net/hoc-sql-server/sql- server-nang-cao https://quantrimang.com/procedure-thu-tuc- trong-sql-server-159768 2 1. Stored Procedure 1.1. Khái niệm 1.2. Cú pháp 3 1.1. Khái niệm stored procedure  Thủ tục lưu trữ (stored procedure - SP) là một tập hợp các phát biểu T-SQL mà SQL Server biên dịch thành một kế hoạch thực thi, được lưu trữ trong CSDL của SQL Server.  Khi chạy một SP lần đầu, mô hình truy vấn của SP được đặt vào trong bộ nhớ.  Các SP là các tập lệnh chạy nhanh. 4 10/11/2019 2 1.1. Khái niệm stored procedure (tiếp)  Có 3 loại SP:  SP hệ thống: do SQL Server cung cấp, có tên bắt đầu bằng tiền tố "sp_", được dùng để quản lý SQL Server và hiển thị các thông tin về CSDL và người dùng  SP mở rộng: là những thư viện liên kết động (DLL), được viết bằng các ngôn ngữ như C, C++,..., mà SQL Server có thể nạp và thực thi  SP bên ngoài: có tên bắt đầu bằng "xp_"  SP người dùng định nghĩa 5 1.1. Khái niệm stored procedure (tiếp)  Lợi ích của việc sử dụng SP:  Sau khi được thực hiện, lược đồ của thủ tục được lưu trữ trong vùng đệm của thủ tục => trong cùng phiên làm việc, nếu sử dụng nhiều lần thủ tục này thì hiệu quả sẽ cao hơn.  Đóng gói các quy tắc nghiệp vụ. Sau khi chúng được đóng gói, nhiều ứng dụng có thể sử dụng quy tắc này. Nếu chức năng thay đổi, ta chỉ việc thay đổi ở một nơi.  Có thể truyền đối số vào và nhận dữ liệu trả về. 6 1.1. Khái niệm stored procedure (tiếp)  Có thể được thiết lập chạy tự động khi SQL Server khởi động.  Được gọi một cách rõ ràng. Không giống như trigger, các SP phải được gọi bởi ứng dụng, kịch bản, batch, tác vụ của bạn.  Hữu dụng trong quản trị và bảo trì CSDL.  Có thể gán quyền cho một người sử dụng chạy một SP cho dù người đó không có quyền trên các bảng cơ sở. 7 1.2. Cú pháp tạo SP  Có thể dùng câu lệnh T-SQL, Enterprise Manager hoặc wizard để tạo SP.  Cú pháp trong SQL Server: CREATE PROC[EDURE] procedure_name {;number} [{@parameter data_type}[=default | NULL][VARYING][OUT PUT]] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement 8 10/11/2019 3 Ví dụ  Chạy thủ tục này:  EXEC pAuthors 9 USE MyDB GO IF EXISTS(SELECT name FROM sysobjects WHERE name='pAuthors' AND type='P') DROP PROCEDURE pAuthors GO CREATE PROCEDURE pAuthors AS SELECT au_fname, au_lname FROM authors ORDER BY au_fname DESC GO  Xem nội dung SP  EXEC sp_helptext pAuthors  Xóa thủ tục:  DROP PROCEDURE procedure_name 10 Tạo một nhóm các thủ tục  Để nhận được danh sách tất cả các thành phố mà các tác giả sống, sử dụng câu lệnh sau:  EXEC group_sp;3 11 CREATE PROC group_sp AS SELECT * FROM authors GO CREATE PROC group_sp AS SELECT au_lname FROM authors GO CREATE PROC group_sp AS SELECT DISTINCT city FROM authors GO Các tham số  @parameter data_type [=default | NULL] [VARYING] [OUTPUT]  @parameter: tên của tham số bên trong thủ tục, có thể khai báo đến 1024 tham số bên trong một SP.  data_type: bất kỳ kiểu dữ liệu nào do hệ thống định nghĩa hoặc do người dùng định nghĩa, ngoại trừ kiểu dữ liệu hình ảnh.  Default: chỉ rõ giá trị mặc định cho tham số.  VARYING: áp dụng cho recordset được trả về.  OUTPUT: xác định đây là một tham số trả về. 12 10/11/2019 4 Ví dụ:  Viết một thủ tục lưu trữ nhận 5 thông số, tính giá trị trung bình và kết xuất ra: 13 CREATE PROCEDURE scores @score1 smallint, @score2 smallint, @score3 smallint, @score4 smallint, @score5 smallint, @myAvg smallint OUTPUT AS SELECT @myAvg = (@score1 + @score2 + @score3 + @score4 + @score5) / 5 Truyền/nhận giá trị cho/từ tham số  Truyền đúng theo trật tự vị trí 14 DECLARE @AvgScore smallint EXEC scores 10, 9, 8, 8, 10, @AvgScore OUTPUT SELECT 'The Average Score is: ',@AvgScore Go  Truyền không theo trật tự DECLARE @AvgScore smallint EXEC scores @score1=10, @score3=9, @score2=8, @score4=8, @score5=10, @myAvg = @AvgScore OUTPUT SELECT 'The Average Score is: ',@AvgScore Go Truyền/nhận giá trị cho/từ tham số (tiếp)  Từ khóa RETURN 15 CREATE PROC MyReturn @t1 smallint, @t2 smallint, @retval smallint AS SELECT @retval = @t1 + @t2 RETURN @retval DECLARE @myReturnValue smallint EXEC @myReturnValue = MyReturn 9, 9, 0 SELECT 'The return value is: ',@myReturnValue  Chạy thủ tục trên:  Tùy chọn WITH RECOMPILE  trong phát biểu CREATE PROCEDURE: Toàn bộ thủ tục được biên dịch lại mỗi khi nó chạy, thủ tục có thể được tối ưu cho các tham số mới.  trong phát biểu EXEC PROCEDURE: Biên dịch thủ tục lưu trữ riêng cho lần thực thi đó và lưu trữ kế hoạch mới trong vùng đệm của thủ tục cho các lệnh EXEC PROCEDURE sau này  Nếu một SP được tạo với tùy chọn ENCRYPTION =>không xem được nội dung của nó 16 10/11/2019 5 2. Trigger 2.1. Khái niệm 2.2. Cú pháp 17 2.1 Khái niệm  Là một thủ tục lưu trữ hệ thống (stored procedure) đặc biệt, được thực thi một cách tự động khi có sự kiện gây biến đổi dữ liệu như Update, Insert hay Delete  Được dùng để đảm bảo toàn vẹn dữ liệu hay thực hiện các quy tắc nghiệp vụ nào đó.  Khi nào sử dụng trigger?  khi các biện pháp đảm bảo toàn vẹn dữ liệu khác như Constraint không thể thỏa mãn yêu cầu của ứng dụng 18 2.1 Khái niệm (tiếp)  Constraint thuộc loại toàn vẹn dữ liệu khai báo: kiểm tra dữ liệu trước khi cho phép nhận vào bảng  Trigger thuộc loại toàn vẹn dữ liệu thủ tục nên việc Insert, Update, Delete xảy ra rồi mới kích hoạt trigger.  Đôi khi, do nhu cầu thay đổi dây chuyền, có thể sử dụng trigger  Đặc điểm của trigger  một trigger có thể làm nhiều công việc, có thể được kích hoạt bởi nhiều sự kiện 19 2.1 Khái niệm (tiếp)  trigger không thể được tạo ra trên bảng tạm hoặc bảng hệ thống  trigger chỉ có thể được kích hoạt tự động bởi các sự kiện mà không thể chạy thủ công được.  có thể áp dụng trigger cho view  khi trigger được kích hoạt  dữ liệu mới được insert sẽ được chứa trong bảng "inserted"  dữ liệu mới được delete sẽ được chứa trong bảng "deleted"  đây là hai bảng tạm nằm trên bộ nhớ, và chỉ có giá trị bên trong trigger 20 10/11/2019 6 2.2. Cú pháp  Có thể dùng T-SQL hoặc Enterprise Manager để tạo trigger  Không được dùng các phát biểu sau trong định nghĩa trigger: ALTER DATABASE, CREATE DATABASE, DISK INIT, DISK RESIZE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, RESTORE LOG 21  Bảng tạm: deleted và inserted  được tham khảo như bảng thật nhưng được lưu trong bộ nhớ trong chứ không phải trên đĩa.  giá trị trong bảng này chỉ được truy xuất trong trigger. Ngay khi trigger hoàn tất thì các bảng này cũng không thể truy xuất được nữa. 22 Ví dụ:  Tạo trigger In_ThemNCC trên bảng NHA_CC: in thông báo bất cứ khi nào dữ liệu được thêm vào bảng 23 USE MyDB GO IF EXISTS(SELECT name FROM sysobjects WHERE name='In_ThemNCC' AND Type='TR') DROP TRIGGER In_ThemNCC GO CREATE TRIGGER In_ThemNCC ON NHA_CC FOR INSERT AS PRINT 'BANG NHA_CC da duoc them du lieu' GO Tạo deleted trigger  Tạo bảng tbl_MatHangXoa để lưu mặt hàng bị xóa khỏi bảng MatHang (đã có) 24 CREATE TABLE tbl_MatHangXoa( MaHang Int NOT NULL, TenHang Nvarchar(50) NOT NULL, MoTa Nvarchar(100), SoLuong SmallIn Default 0 )  Tạo deleted trigger trên bảng MatHang cho sự kiện delete CREATE TRIGGER tg_MatHangXoa ON MatHang FOR DELETE AS INSERT INTO tbl_MatHangXoa SELECT * FROM deleted 10/11/2019 7 Tạo inserted trigger  Tạo inserted trigger cho bảng BanHang. Mỗi khi có một dòng mới được thêm vào (hàng được bán) thì trigger sẽ làm nhiệm vụ trừ đi số lượng trong bảng MatHang 25 CREATE TRIGGER tg_CapNhatSoLuong ON BanHang FOR INSERT AS SELECT * FROM inserted UPDATE MatHang SET MatHang.SoLuong = MatHang.SoLuong – inserted.SoLuong WHERE MatHang.MaHang = inserted.MaHang Tạo update trigger 26 CREATE TRIGGER tg_KiemTraCapNhatGia ON MatHang FOR UPDATE AS DECLARE @gia_cu smallmoney, @gia_moi smallmoney SELECT @gia_cu = DonGia FROM deleted PRINT 'Gia cu =' PRINT CONVERT(varchar(6), @gia_cu) SELECT @gia_moi = DonGia FROM inserted PRINT 'Gia moi =' PRINT CONVERT(varchar(6), @gia_moi) IF(@gia_moi > (@gia_cu*1.10)) BEGIN PRINT 'Gia moi tang qua 10%, khong cap nhat' ROLLBACK END ELSE PRINT 'Gia moi chap nhan duoc' 27 CREATE TRIGGER tg_KT ON DeTai FOR UPDATE AS DECLARE @kp_cu int, @kp_moi int SELECT @kp_cu = KinhPhi FROM deleted PRINT 'Kinh phi cu =' PRINT CONVERT(varchar(6),@kp_cu) SELECT @kp_moi = KinhPhi FROM inserted PRINT 'Kinh phi moi =' PRINT CONVERT(varchar(6),@kp_moi) IF(@kp_moi > (@kp_cu * 1.10)) BEGIN PRINT 'Tang qua 10%, khong chap nhan' ROLLBACK END ELSE PRINT 'Chap nhan. Cap nhat' ------ UPDATE DeTai SET KinhPhi = KinhPhi * 1.05 WHERE DTID = 'DT01' 28 10/11/2019 8 Lời hay ý đẹp 29 "Cho nhiều hơn nhận, ngay kẻ tham cũng phải mến bạn. Có đủ cơ trí ở trong lòng, ngay ở lúc bất ngờ bạn cũng không bị dồn đến bước đường cùng" Vegetable Roots 10/11/2019 1 1 Index NGUYEN HongPhuong Email: phuongnh@soict.hust.edu.vn Site: Face: https://www.facebook.com/phuongnhbk Hanoi University of Science and Technology Contents  Introduction  Types of index  Some kinds of indexes in PostGreSQL  B-Tree Data structure  INDEX in SQL  Which cases should not we use index? 2 Introduction  The technical purpose of the database index is to limit as much as possible disk IO while executing a query.  Users can not see the indexes, they are just used to speed up searches/queries  Updating a table with indexes takes more time than a table without indexes  So, only create indexes on columns that will be frequently searched against 3 Introduction  An index is a separate data structure managed by the database, which can be used while executing a query, in order to avoid reading the entire data for a query that only requires a small part of it.  Different implementations of an index will improve query performance for different type of operators. 4 10/11/2019 2 Types of index  A table/view can contain the following types of indexes:  Clustered  Non clustered 5 Clustered  Clustered indexes sort and store the data rows in the table/view based on their key values  There should be only one clustered index per a table, because the data rows themselves can be stored in only one order. 6 Non-clustered  Non-clustered indexes have a structure separate from the data rows  The pointer from an index row in a non- clustered index to a data row is called a row locator  You can add non-key columns to the leaf level of the non-clustered index to by-pass existing index key limits, and execute fully covered, indexed, queries. 7 Some kinds of indexes in PostGreSQL  PostgreSQL comes with many implementations by default for the index data structure  B-Tree Index - very useful for single value search or to scan a range, but also for pattern matching.  Hash Index - very efficient when querying for equality.  Generalized Inverted Index (GIN) - useful for indexing array values and testing the presence of an item. 8 10/11/2019 3 Some kinds of indexes in PostGreSQL  Generalized Search Tree (GiST) - a more complex index structure useful for more exotic searches such as nearest-neighbor or pattern matching.  Space Partitioned GiST (SP-GiST) - similar with GiST, this index implementation supports space partitioned trees such as quadtrees, k-d trees, and radix trees.  Block Range Index (BRIN) - this type of index stores summary information for each table block range  B-Tree indexes are the default option when creating an index without specifying the type. 9 B-Tree Data structure  B-Tree is a self-balanced search tree in which every node contains multiple keys and has more than two children.  B-Tree of Order m has the following properties...  Property #1 - All leaf nodes must be at same level.  Property #2 - All nodes except root must have at least [m/2]-1 keys and maximum of m-1 keys. 10 B-Tree Data structure  Property #3 - All non leaf nodes except root (i.e. all internal nodes) must have at least m/2 children.  Property #4 - If the root node is a non leaf node, then it must have atleast 2 children.  Property #5 - A non leaf node with n-1 keys must have n number of children.  Property #6 - All the key values in a node must be in Ascending Order. 11 B-Tree Data structure  For example, B-Tree of Order 4 contains a maximum of 3 key values in a node and maximum of 4 children for a node. 12 10/11/2019 4 INDEX in SQL  Syntax for create index  CREATE INDEX index_name ON table_name;  Single-Column Index  CREATE INDEX index_name ON table_name (column_name);  Unique index  CREATE UNIQUE INDEX index_name ON table_name (column_name); 13 INDEX in SQL  Composite Index  CREATE INDEX index_name ON table_name (column_name1, column_name2);  Drop index  DROP INDEX table_name.index_name;  DROP INDEX index_name ON table_name; 14 Which cases should not we use index?  Small tables  Tables are often updated and inserted  Not be applied on columns which have a large number of NULL value.  Not be applied on columns which are often updated. 15 16 10/11/2019 1 1 Transaction NGUYEN HongPhuong Email: phuongnh@soict.hust.edu.vn Site: Face: https://www.facebook.com/phuongnhbk Hanoi University of Science and Technology Contents  Introduction  Properties of a transaction  Processing a transaction  Some examples 2 Introduction  Transactions in SQL are a group of SQL statements. If a transaction is made successfully, all data changes made in the transaction are saved to the database. If a transaction fails and is rolled back, all data modifications will be deleted (data is restored to the state before the transaction was executed). 3 Properties of a transaction  The transaction has 4 standard properties, referenced by ACID  Atomicity: ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state. 4 10/11/2019 2 Properties of a transaction  Consistency: ensures that the database properly changes states upon a successfully committed transaction.  Isolation: enables transactions to operate independently of and transparent to each other.  Durability: ensures that the result or effect of a committed transaction persists in case of a system failure. 5 Transaction States and Additional Operations  A transaction is an atomic unit of work that is either completed in its entirety or not done at all.  For recovery purposes, the system needs to keep track of when the transaction starts, terminates, and commits or aborts.  Hence, the recovery manager keeps track of the following operations: 6 Processing a transaction  The following commands are used to process transactions.  COMMIT: to save the changes.  ROLLBACK: to return to the previous state before changing.  SAVEPOINT: create points within the transaction group to ROLLBACK, i.e. to return to that status point.  SET TRANSACTION: give a name to a transaction.  These commands are only used with DML: INSERT, UPDATE and DELETE. 7 A state transition diagram 8 ACTIVE PARTIALLY COMMITTED COMMITTED TERMINATEDFAILED BEGIN TRANSACTION END TRANSACTION COMMIT ABORT READ, WRITE ABORT 10/11/2019 3 COMMIT command  Used to save the changes invoked by a transaction to the database.  Stores all transactions in the Database since the last COMMIT or ROLLBACK command.  The basic syntax of a COMMIT command is as follows: 9 COMMIT; ROLLBACK command  Used to return transactions to a state before changes have not been saved to the database.  Can only be used to undo transactions from the last COMMIT or ROLLBACK command.  The basic syntax: 10 ROLLBACK; ROLLBACK TO SavePointName; SAVEPOINT  A SAVEPOINT is a point in a transaction when you can undo the transaction to a specific point without having to roll it back to the first state before that change.  The basic syntax of the SAVEPOINT is as follows: 11 SAVEPOINT SAVEPOINT_NAME; RELEASE SAVEPOINT command  Used to delete a SAVEPOINT that you have created.  The basic syntax  Once a SAVEPOINT has been deleted, you can no longer use the ROLLBACK command to undo the transaction to that SAVEPOINT. 12 RELEASE SAVEPOINT SAVEPOINT_NAME; 10/11/2019 4 SET TRANSACTION command  Can be used to initiate a Database Transaction. This command is used to characterize the transaction.  For example, you can specify a transaction as read only or read write.  The basic syntax 13 SET TRANSACTION [READ WRITE | READ ONLY]; Transactions, read and write operation, DBMS buffer  The database operations that form a transaction can either be embedded within an application program or they can be specified interactively via a high-level query language such as SQL.  One way of specifying the transaction boundaries is by specifying explicit begin transaction and end transaction statements in an application program 14  A read-only transaction  do not update the database but only retrieve data  To simplify, the basic database access operations that a transaction can include are as follows:  read_item(X): Reads a database item named X into a program variable also named X.  write_item(X): Writes the value of program variable X into the database item named X 15  The basic unit of data transfer from disk to main memory is one block  Executing a read_item(X) command includes the following steps:  Find the address of the disk block that contains item X  Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer)  Copy item X from the buffer to the program variable named X 16 10/11/2019 5  Executing a write_item(X) command includes the following steps:  Find the address of the disk block that contains item X.  Copy that disk block into a buffer in main memory (if that disk block is not already in some main memory buffer).  Copy item X from the program variable named X into its correct location in the buffer.  Store the updated block from the buffer back to disk (either immediately or at some later point in time). 17  A transaction includes read_item and write_item operations to access and update the database.  The read-set of a transaction is the set of all items that the transaction reads  The write-set is the set of all items that the transaction writes. 18 19 read_item (X); X:=X-N; write_item (X); read_item (Y); Y:=Y+N; write_item (Y); read_item (X); X:=X+M; write_item (X); T2T1 Why Concurrency Control Is Needed  Problem  The Lost Update  The Temporary Update (Dirty Read)  The Incorrect Summary  The Unrepeatable Read 20 10/11/2019 6 The Lost Update Problem  Occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database items incorrect. 21 read_item (X); X:=X-N; write_item (X); read_item (Y); Y:=Y+N; write_item (Y); read_item (X); X:=X+M; write_item (X); T2T1 Time The Temporary Update Problem  Occurs when one transaction updates a database item and then the transaction fails for some reason.  The updated item is accessed by another transaction before it is changed back to its original value. 22 read_item (X); X:=X-N; write_item (X); read_item (Y); read_item (X); X:=X+M; write_item (X); T2T1 Time Transaction T1 fails and must change the value of X back to its old value; meanwhile T2 has read the “temporary” incorrect value of X. The Incorrect Summary Problem  If one transaction is calculating an aggregate summary function on a number of records while other transactions are updating some of these records, the aggregate function may calculate some values before they are updated and others after they are updated 23 read_item(X); X:=X-N; write_item(X); read_item(Y); Y:=Y+N; write_item(Y); sum:=0; read_item(A); sum:=sum+A; read_item(X); sum:=sum+X; read_item(Y); sum:=sum+Y; T1 T3 T3 reads X after N is subtracted and reads Y before N is added Time The Unrepeatable Read Problem  A transaction T reads an item twice and the item is changed by another transaction T' between the two reads  Hence, T receives different values for its two reads of the same item. 24 10/11/2019 7 Why Recovery Is Needed  The DBMS must not permit some operations of a transaction T to be applied to the database while other operations of T are not. This may happen if a transaction fails after executing some of its operations but before executing all of them.  There are several possible reasons for a transaction to fail in the middle of execution:  A computer failure (system crash)  A transaction or system error  Local errors or exception conditions detected by the transaction  Concurrency control enforcement  Disk failure  Physical problems and catastrophes 25 Some examples  Using an explicit transaction 26 CREATE TABLE ValueTable (id int); INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); BEGIN TRANSACTION; DELETE FROM ValueTable WHERE id = 2; COMMIT;  Rolling back a transaction BEGIN TRANSACTION; INSERT INTO ValueTable VALUES(3); INSERT INTO ValueTable VALUES(4); ROLLBACK; Some examples (2)  Naming a transaction 27 DECLARE @TranName VARCHAR(20); SELECT @TranName = 'MyTransaction'; BEGIN TRANSACTION @TranName; DELETE FROM ValueTable WHERE id = 1; COMMIT TRANSACTION @TranName; Some examples (3)  Marking a transaction 28 BEGIN TRANSACTION Del WITH MARK N'Deleting a row'; DELETE FROM ValueTable WHERE id = 1; COMMIT TRANSACTION Del; 10/11/2019 8 29 10/11/2019 1 1 View NGUYEN HongPhuong Email: phuongnh@soict.hust.edu.vn Site: Face: https://www.facebook.com/phuongnhbk Hanoi University of Science and Technology Contents  Introduction  Creating a view  A view from two tables  Dropping a view  Updating a table from a view 2 Introduction  In SQL, a view is a virtual table based on the result-set of an SQL statement.  A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.  You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. 3 Creating a view  Syntax 4 CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; 10/11/2019 2 Creating a view  Examples 5 CREATE VIEW vCompany1 AS SELECT Name, Address, Telephone FROM Company CREATE VIEW vCompany2 AS SELECT Name, Address, Telephone FROM Company WHERE Address LIKE '%Japan%' CREATE VIEW vComSupPro1(ComName, ProdName, Qty) AS SELECT Company.Name, Product.Name, Quantity FROM Company INNER JOIN Supply ON Company.CompanyID = Supply.CompanyID INNER JOIN Product ON Supply.ProductID = Product.ProductID Querying on a view 6 SELECT * FROM vCompany1 WHERE Address LIKE'%Germany%'; SELECT * FROM vCompany2; Deleting a view  Syntax 7 DROP VIEW view_name;  Example DROP VIEW vCompany1; DROP VIEW vCompany2; DROP VIEW vComSupPro1; 8