Giáo trình Hệ quản trị cơ sở dữ liệu - Chương 1: Tổng quan về Hệ quản trị Cơ sở dữ liệu - Lê Thị Minh Nguyên

8/25/2017 1 Chương 1. Tổng quan về Hệ quản trị Cơ sở dữ liệu GV: Lê Thị Minh Nguyện Email: nguyenltm@huflit.edu.vn Nội dung 1. Nhắc lại một số khái niệm 2. Quá trình phát triển của Hệ quản trị Cơ sở dữ liệu (HQT CSDL) (Database Management System - DBMS) 3. Định nghĩa HQT CSDL 4. Các mức biểu diễn một CSDL 5. Các thành phần chính của HQTCSDL 6. Một số HQT CSDL 7. Tổng quan về HQT CSDL SQL Server 8. Transact – SQL Hệ quản trị Cơ sở dữ liệu 2 1. Nhắc lại một số khái niệm 1.1. Dữ liệu 1.2. Thông tin 1.3. Cơ sở dữ liệu 1.4. Siêu dữ liệu (Meta data) Hệ quản trị Cơ sở dữ liệu 3 1.1. Dữ liệu • Dữ liệu là những giá trị ban đầu mà chưa có nghĩa nhiều với người dùng mà máy tính có thể tiếp nhậ và xử lý Hệ quản trị Cơ sở dữ liệu 4 8/25/2017 2 1.2. Thông tin • Thông tin được xử lý từ dữ liệu ra và hoàn toàn có nghĩa với người dùng Hệ quản trị Cơ sở dữ liệu 5 1.3. Cơ sở dữ liệu • Phần dữ liệu được lưu giữ trong máy tính theo một quy định hay cấu trúc nào đó gọi là cơ sở dữ liệu Hệ quản trị Cơ sở dữ liệu 6 1.4. Siêu dữ liệu (Meta data) • Siêu dữ liệu tức là thông tin mô tả về dữ liệu Hệ quản trị Cơ sở dữ liệu 7 2. Quá trình phát triển của HQT CSDL (Database Management System) 2.1. Hệ thống tập tin cổ điển (file systems) 2.2. Cơ sở dữ liệu (Database) Hệ quản trị Cơ sở dữ liệu 8 8/25/2017 3 2.1. Hệ thống tập tin cổ điển (file systems) Hệ quản trị Cơ sở dữ liệu 9 Sales Files PropertyForRent(propertyNo, street, city, postcode, type, rooms, rent, ownerNo) PrivateOwner(ownerNo, fName, lName, address, telNo) Client(clientNo, fName, lName, address, telNo, preType, maxRent) Contracts Files Lease(leaseNo, propertyNo, clientNo, rent, paymentMethod, deposit, paid, rentStart, rentFinish) PropertyForRent(propertyNo, street, city, postcode, rent) Client(clientNo, fName, lName, address, telNo) 2.1. Hệ thống tập tin cổ điển (file systems) (tt) • Ưu điểm: • Gọn nhẹ, phù hợp thực tiễn. Ít tốn thời gian, chi phí thấp • Khả năng đáp ứng khai thác nhanh chóng và kịp thời Hệ quản trị Cơ sở dữ liệu 10 • Nhược điểm: • Thông tin lưu nhiều nơi, dư thừa, không nhất quán • Lãng phí thời gian cập nhật dữ liệu và lưu trữ • Phối hợp tổ chức và khai thác là khó khăn • Thiếu sự chia sẽ thông tin giữa các đơn vị và bộ phận. • Khó khi nâng cấp ứng dụng. • Không có người quản trị dữ liệu, mọi người có quyền sử dụng thêm, xóa, sửa  không an tòan, không bảo mật thông tin 1.2. Cơ sở dữ liệu (Database) PropertyForRent(propertyNo, street, city, postcode, type, rooms, rent, ownerNo) PrivateOwner(ownerNo, fName, lName, address, telNo) Client(clientNo, fName, lName, address, telNo, preType, maxRent) Lease(leaseNo, propertyNo, clientNo, paymentMethod, deposit, paid, rentStart, rentFinish) Hệ quản trị Cơ sở dữ liệu 11 1.2. Cơ sở dữ liệu (Database) (tt) • Ưu điểm nổi bật của CSDL là: • Giảm sự trùng lặp thông tin xuống mức thấp nhất và do đó bảo đảm đượ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 Hệ quản trị Cơ sở dữ liệu 12 8/25/2017 4 1.2. Cơ sở dữ liệu (Database) (tt) Hệ quản trị Cơ sở dữ liệu 13 • Các vấn đề cần giải quyết • Tính chủ quyền bị vi phạm • Tính nhất quán CSDL • Vấn đề bảo mật • Tính an tòan dữ liệu • Vấn đề tranh chấp dữ liệu • Chia sẽ thông tin cho nhiều người sử dụng một cách đồng thời • Đảm bảo dữ liệu truy xuất đồng thời theo nhiều cách khác nhau • Tính độc lập giữa dữ liệu và chương trình / hệ thống ứng dụng 1.2. Cơ sở dữ liệu (Database) (tt) • Là HTTT có cấu trúc, được lưu trữ trên các thiết bị mang tin từ tính, phục vụ việc khai thác thông tin của nhiều người sử dụng một cách đồng thời với nhiều mục đích khác nhau. Hệ quản trị Cơ sở dữ liệu 14 3. Định nghĩa HQT CSDL • Là một hệ thống phần mềm cung cấp các công cụ để xây dựng và quản lý CSDL • Định nghĩa cấu trúc dữ liệu (DDL) • Cung cấp khả năng thao tác trên CSDL (MDL) • Hỗ trợ lưu trữ dữ liệu • Điều khiển truy xuất dữ liệu giữa nhiều người dùng Hệ quản trị Cơ sở dữ liệu 15 4. Các mức biểu diễn CSDL Hệ quản trị Cơ sở dữ liệu 16 Physical Level Logical Level View 1 View 2 View n View Level 8/25/2017 5 4. Các mức biểu diễn CSDL • Mức vật lý • Đây là mức lưu trữ CSDL. Tại mức này, vấn đề cần giải quyết là dữ liệu gì và được lưu trữ như thế nào? ở đâu (đĩa từ, băng từ, track, sector ... nào)? Cần các chỉ mục gì? Việc truy xuất là tuần tự (Sequential Access) hay ngẫu nhiên (RandomAccess) đối với từng loại dữ liệu. • Những người hiểu và làm việc với CSDL tại mức này là người quản trị CSDL (Administrator), những người sử dụng (NSD) chuyên môn. Hệ quản trị Cơ sở dữ liệu 17 4. Các mức biểu diễn CSDL • Mức quan niệm: • Tại mức này sẽ giải quyết cho câu hỏi CSDL cần phải lưu giữ bao nhiêu loại dữ liệu? đó là những dữ liệu gì? Mối quan hệ giữa các loại dữ liệu này như thế nào? • Từ thế giới thực (Real Universe) các chuyên viên tin học qua quá trình khảo sát và phân tích, cùng với những người sẽ đảm nhận vai trò quản trị CSDL, sẽ xác định được những loại thông tin gì được cho là cần thiết phải đưa vào CSDL, đồng thời mô tả rõ mối liên hệ giữa các thông tin này. Hệ quản trị Cơ sở dữ liệu 18 4. Các mức biểu diễn CSDL • Mức ngoài. • Đó là mức của người sử dụng và các chương trình ứng dụng. • Làm việc tại mức này có các nhà chuyên môn, các kỹ sư tin học và những người sử dụng không chuyên. • Mỗi người sử dụng hay mỗi chương trình ứng dụng có thể được "nhìn" (View) CSDL theo một góc độ khác nhau. Có thể "nhìn" thấy toàn bộ hay chỉ một phần hoặc chỉ là các thông tin tổng hợp từ CSDL hiện có. Hệ quản trị Cơ sở dữ liệu 19 5. Các thành phần chính của DBMS Hệ quản trị Cơ sở dữ liệu 20 Quản lý khôi phục Xử lý truy vấn Ngôn ngữ giao tiếp Quản lý giao tác Quản lý lưu trữ 8/25/2017 6 5.1. Ngôn ngữ giao tiếp • DBMS cung cấp giao diện lập trình dể sử dụng với một ngôn ngữ lập trình CSDL • SQL Server: Transaction-SQL (T-SQL) • Oracle: PL/SQL • Ngôn ngữ bao gồm • Định nghĩa dữ liệu • Thao tác dữ liệu Hệ quản trị Cơ sở dữ liệu 21 5.2. Xử lý truy vấn • Biểu diễn câu truy vấn ở dạng ngôn ngữ cấp cao (SQL) và thực hiện câu truy vấn có hiệu quả • Query compiler – biên dịch • Query parser • Xây dựng cấu trúc hình cây từ câu truy vấn • Query preprocessor • Kiểm tra ngữ nghĩa của câu truy vấn • Chuyển đổi cấu trúc cây sang ngôn ngữ đại số quan hệ • Query optimizer • Sắp xếp các phép toán nhằm mục đích tối ưu hóa câu truy vấn Hệ quản trị Cơ sở dữ liệu 22 5.3. Quản lý giao tác • Thành phần quản lý các giao tác có ảnh hưởng đến CSDL • Giao tác là một nhóm các hành động mà nếu thực hiện được thì phải thực hiện hết tất cả các hành động trong giao tác đó, ngược lại xem như không thực hiện hành động nào Hệ quản trị Cơ sở dữ liệu 23 transaction 1 2 3 4 5 7 8 5.3. Quản lý giao tác (tt) • Nhật ký • Để CSDL được bền vững (durable), mọi thay đổi lên CSDL phải được ghi nhận lại • Log manager – ghi chép nhật ký • Đảm bảo CSDL vẫn nguyên vẹn khi có sự cố xãy ra • Recovery manager – khôi phục • Dựa vào nhật ký để phục hồi lại CSDL về trạng thái nhất quán trước đó • Trạng thái thỏa tất cả các RBTV của CSDL đó Hệ quản trị Cơ sở dữ liệu 24 8/25/2017 7 5.3. Quản lý giao tác (tt) Hệ quản trị Cơ sở dữ liệu 25 • Điều khiển đồng thời • Bộ lập lịch (scheduler) - có nhiệm vụ lập 1 lịch thực hiện từ n giao tác được kích hoạt đồng thời • Cơ chế khóa (lock) - ngăn 2 giao tác cùng thao tác lên 1 đơn vị dữ liệu tại 1 điểm DBMS ServerDB Client ClientClient Scheduler Equivalent to serial result 5.3. Quản lý giao tác (tt) Hệ quản trị Cơ sở dữ liệu 26 • Giải quyết deadlock • Vì sử dụng cơ chế khóa nên các giao tác sẽ phải tranh giành tài nguyên • Tình huống “không một giao tác nào có thể thực hiện được công việc của mình” • Các giao tác chờ đợi lẫn nhau để được cấp phát tài nguyên • Thành phần quản lý giao tác sẽ phải can thiệp vào • Rollback • Abort 5.4. Quản lý lưu trữ • Thành phần có nhiệm vụ điểu khiển việc đọc/ghi dữ liệu qua lại giữa bộ nhớ và thiết bị lưu trữ • Làm việc với khác khái niệm • Tập tin dữ liệu • Từ điển dữ liệu • Lưu trữ các metadata về cấu trúc của CSDL, đặc biệt là lược đồ của CSDL • Chỉ mục Hệ quản trị Cơ sở dữ liệu 27 6. Một số HQT CSDL • DB2: IBM • InterBase: Borland • MySQL : MySQL LAB • Microsoft Office Access • Microsoft SQL Server • Oracle • .. Hệ quản trị Cơ sở dữ liệu 28 8/25/2017 8 6. Một số HQT CSDL (tt) Hệ quản trị Cơ sở dữ liệu 29 • Giá bản quyền phần mềm • Độ phức tạp thấp(dễ dàng quản trị) • Giá đầu tư phần cứng, thiết bị kèm theo thấp SQL SERVER  Định hướng phát triển tổ chức, công ty.  Độ phức tạp cao(khó quản trị)  Giá đầu tư cao Oracle  Sử dụng ngôn ngữ chuẩn SQL 7. Tổng quan về HQT CSDL SQL Server 7.1. Giới thiệu về Microsoft SQL Server 7.2. Các phiên bản của SQL Server 7.3. Các thành phần của SQL Server 7.4. Các CSDL hệ thống của SQL Server 7.5. Các đối tượng của CSDL Hệ quản trị Cơ sở dữ liệu 30 7.1. Giới thiệu về Microsoft SQL Server • SQL Server là hệ quản trị CSDL Client/Server • Định nghĩa, chỉnh sửa CSDL, lược đồ, quan hệ giữa các lược đồ • Thêm, sửa, xóa thông tin lưu trong các lược đồ • Hỗ trợ các tính năng bảo mật, sao lưu phục hồi, cấp quyền truy nhập Hệ quản trị Cơ sở dữ liệu 31 7.1. Giới thiệu về Microsoft SQL Server (tt) Hệ quản trị Cơ sở dữ liệu 32 32 • Hệ thống khách/chủ gồm 3 phần • Hệ thống phía Server: xử lý yêu cầu và phục vụ • Hệ thống phía Client: nơi yêu cầu và nhận dữ liệu • Hệ thống giao tiếp giữa Client và Server(Network) 8/25/2017 9 7.1. Giới thiệu về Microsoft SQL Server (tt) Hệ quản trị Cơ sở dữ liệu 33 RDBMS(Relational Database Management System) SQL ServerClient Results Client Application OLAP OLTPQuery OLTP: Online Transaction Process OLAP: Online Analysis Process 7.1. Giới thiệu về Microsoft SQL Server (tt) Hệ quản trị Cơ sở dữ liệu 34 Client Application Client Net-Library Client SQL Server Relational Engine Storage Engine Server Local Database Database API (OLE DB, ODBC, DB-Library) Processor Memory Open Data Services Server Net-Libraries 7.1. Giới thiệu về Microsoft SQL Server (tt) Hệ quản trị Cơ sở dữ liệu 35 Local Database Client Application Client Net-Library Client SQL Server Relational Engine Storage Engine Server Database API (OLE DB, ODBC, DB-Library) Processor Memory Open Data Services Server Net-Libraries Query Result Set Result Set Query 1 2 3 4 5 7.2. Các phiên bản của SQL Server Hệ quản trị Cơ sở dữ liệu 36 • Enterprise Edition • Không hạn chế kích thước cơ sở dữ liệu • Hỗ trợ Xử lý giao dịch trực tuyến (OLTP) • Khã dụng và khã cỡ cao • Standard Edition • Đủ cho các công ty vừa và nhỏ • Gồm các tính năng cơ bản như: thương mại điện tử, nhà kho dữ liệu, giải pháp ứng dụng doanh nghiệp • Developer Edition • Có các chức năng để xây dựng và kiểm thử ứng dụng trên phiên bản SQL Server Expression • Express Edition • Phiên bản nhỏ gọn có thể download từ Internet • Chỉ có phần dịch vụ cơ sở dữ liệu, không hỗ trợ những công cụ quản lý Workgroup Edition Dòng sản phẩm cho nhóm làm việc Cho các ứng dụng và hệ thống của các tổ chức nhỏ Không hạn chế kích thước cơ sở dữ liệu và số lượng người dùng 8/25/2017 10 7.3. Các thành phần của SQL Server Hệ quản trị Cơ sở dữ liệu 37 7.3. Các thành phần của SQL Server (tt) • Database Engine (lõi của SQL Server) • Ðây là một engine có khả năng chứa data ở các quy mô khác nhau dưới dạng table và support tất cả các kiểu kết nối: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). • Replication (Cơ chế tạo bản sao) • Integration Services (DTS) • Di chuyển, sao chép và chuyển đổi dữ liệu • Analysis Services Hệ quản trị Cơ sở dữ liệu 38 7.3. Các thành phần của SQL Server (tt) • Notification Services • Dịch vụ thông báo Notification Services là nền tảng cho sự phát triển và triển khai các ứng dụng tạo và gửi thông báo. • Reporting Services • Xây dựng các ứng dụng báo cáo • Full Text Search Service • Truy vấn cho dữ liệu văn bản không cấu trúc được lưu trữ trong các CSDL SQL Server • Service Broker • Service Broker giao tiếp qua giao thức TCP/IP và cho phép các component khác nhau có thể được đồng bộ cùng nhau theo hướng trao đổi các message. Hệ quản trị Cơ sở dữ liệu 39 7.4. Các CSDL hệ thống của SQL Server Hệ quản trị Cơ sở dữ liệu 40 • SQL Server hỗ trợ ba loại cơ sở dữ liệu: Cơ sở dữ liệu hệ thống Cơ sở dữ liệu người dùng định nghĩa Cơ sở dữ liệu ví dụ  Được tạo ra bởi SQL Server. Các cơ sở dữ liệu này được sử dụng để lưu thông tin về SQL Server..  Hơn nữa, các cơ sở dữ liệu này còn được sử dụng để quản lý các cơ sở dữ liệu người dùng .  Người sử dụng SQL Server có thể tạo các cơ sở dữ liệu người dùng định nghĩa  Mục đích của các cơ sở dữ liệu này là để lưu dữ liệu của người sử dụng  SQL Server cung cấp các cơ sở dữ liệu mẫu để người sử dụng làm việc với SQL Server  Trong SQL Server là AdventureWorks 8/25/2017 11 7.4. Các CSDL hệ thống của SQL Server Hệ quản trị Cơ sở dữ liệu 41 Các cơ sở dữ liệu hệ thống Cơ sở dữ liệu Mô tả master Cơ sở dữ liệu này lưu trữ các tất cả các thông tin về hệ thống của SQL Server. msdb Cơ sở dữ liệu này được sử dụng bởi dịch vụ SQL Server Agent để nhắc nhở các thực hiện công việc theo lịch biểu và các công việc khác. model Cơ sở dữ liệu này được sử dụng như là cơ sở dữ liệu mẫu cho tất cả các cơ sở dữ liệu được người dung tạo ra sau này trong SQL Server. tempdb Các kết quả tạm thời, đối tượng tạm thời được tạo ra trong quá trình xử lý tính toán được lưu trong cơ sở dữ liệu này 7.4. Các CSDL hệ thống của SQL Server (tt) Hệ quản trị Cơ sở dữ liệu 42 7.5. Các đối tượng CSDL Hệ quản trị Cơ sở dữ liệu 43 8. Transact – SQL 8.1. Giới thiệu 8.2. Khai báo và sử dụng biến 8.3. Các lệnh điều khiển Hệ quản trị Cơ sở dữ liệu 44 8/25/2017 12 8.1. Giới thiệu • Transact-SQL (T-SQL) là ngôn ngữ phổ biến nhất được sử dụng trong thế giới CSDL. • T-SQL là ngôn ngữ khá mạnh có đề cập đến kiểu dữ liệu, đối tượng tạm thời, các thủ tục hệ thống và các thủ tục mở rộng. • T-SQL còn có khả năng xử lý trên mẫu tin, xử lý có điều kiện, điều khiển giao tác, xử lý lỗi và biệt lệ. Hệ quản trị Cơ sở dữ liệu 45 8.2. Khai báo và sử dụng biến • Biến cục bộ DECLARE @Tên_biến kiểu_dữ_liệu[,] • Ví dụ: DECLARE @ten_ncc varchar(50), @ngayxh DATETIME • Gán giá trị cho biến: Dùng SET hoặc SELECT cùng với phép gán (= ) Hệ quản trị Cơ sở dữ liệu 46 8.2. Khai báo và sử dụng biến (tt) Hệ quản trị Cơ sở dữ liệu 47 DECLARE @TongSLDat int SELECT @TongSLDat = SUM(SLDAT) FROM CTDH 8.2. Khai báo và sử dụng biến (tt) • Biến hệ thống • Các biến hệ thống trong SQL Server luôn bắt đầu bằng 2 chữ @@. • Giá trị mà chúng ta đang lưu trữ do hệ thống SQL cung cấp. • Người lập trình không can thiệp trực tiếp để gán giá trị vào các biến hệ thống. • Ví dụ: Hệ quản trị Cơ sở dữ liệu 48 SELECT * FROM NHACC SELECT @@ROWCOUNT 8/25/2017 13 8.2. Khai báo và sử dụng biến (tt) Hệ quản trị Cơ sở dữ liệu 49 Tên biến kiểu trả về Dùng để trả về connections số nguyên Tổng số các kết nối vào SQL Server từ khi nó được khởi động Error số nguyên số mã lỗi của câu lệnh thực hiện gần nhất. Khi một lệnh thực hiện thành công thì biến này có giá trị là 0 Language chuỗi Tên ngôn ngữ mà hệ thống SQL đag sử dụng. Mặc định là US_English RowCount số nguyên Tổng số mẩu tin được tác động vào câu lệnh truy vấn gần nhất ServerName chuỗi Tên của máy tính cục bộ được cài đặt trong SQL Server ServiceName chuỗi Tên dịch vụ kèm theo bên dưới SQL Server Fetch_Status số nguyên Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế dòng mẩu tin (cursor). Khi dữ liệu đọc mẩu tin thành công thì biến này có giá trị là 0 Version chuỗi Phiên bản, ngày của phẩm SQL Server và loại CPU 8.3. Các lệnh điều khiển • Cấu trúc rẽ nhánh IFELSE Hệ quản trị Cơ sở dữ liệu 50 IF Biểu_thức_luận_lý Câu_lệnh1|khối_lệnh1 ELSE Câu_lệnh2|khối_lệnh2 8.3. Các lệnh điều khiển (tt) Hệ quản trị Cơ sở dữ liệu 51 IF (SELECT COUNT(*) FROM CTXUAT WHERE SLXUAT>4)>0 BEGIN Print “Danh sách các hàng hoá bán ra với số lượng lớn hơn 4” SELECT CTXUAT.MaMH, TenMH, SLXuat FROM CTXUAT, MATHANG WHERE CTXUAT.MaMH = MATHANG.MaMH AND SLXuat>4 END ELSE Print “chưa bán hàng nào với số lượng lớn hơn 4” 8.3. Các lệnh điều khiển (tt) Hệ quản trị Cơ sở dữ liệu 52 CASE WHEN THEN WHEN THEN [ELSE ] END • Cấu trúc CASE SELECT HONV, TENNV FROM NHANVIEN WHERE YEAR(GETDATE()) – YEAR(NGSINH) >= ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END ) 8/25/2017 14 8.3. Các lệnh điều khiển (tt) • Cấu trúc lặp WHILE Hệ quản trị Cơ sở dữ liệu 53 WHILE Biểu_thức_luận_lý BEGIN Các_lệnh_lặp END DECLARE @Songuyen INT SET @Songuyen = 100 WHILE (@Songuyen < 110) BEGIN Print “Số nguyên: ” + convert(char(3), @songuyen) SET @Songuyen = @Songuyen +1 END Hệ quản trị Cơ sở dữ liệu 54