›
›

Luận văn Tìm hiểu khai thác SQL Server và sử dụng nó để giải quyết bài toán quản lý dân cư

pdf 85 trang phuongnguyen 3900
Download
Bạn đang xem 20 trang mẫu của tài liệu "Luận văn Tìm hiểu khai thác SQL Server và sử dụng nó để giải quyết bài toán quản lý dân cư", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfluan_van_tim_hieu_khai_thac_sql_server_va_su_dung_no_de_giai.pdf

Nội dung text: Luận văn Tìm hiểu khai thác SQL Server và sử dụng nó để giải quyết bài toán quản lý dân cư

  1. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Đặt vấn đề ắ Cơ sở dữ liệu và quản trị cơ sở dữ liệu là một trong những vấn đề cốt lõi của tin học. Xét cho cùng khi ứng dụng tin học để giải quyết các bài toán thì ng−ời ta phải giải quyết vấn đề xây dựng cơ sở dữ liệu và xây dựng thuật toán xử lý chúng. Khi cơ sở dữ liệu càng lớn nhu cầu xử lý phức tạp, yêu cầu bảo mật càng cao thì vấn đề quản trị cơ sở dữ liệu càng phức tạp. Vì vậy các mục tiêu của đề án này là tìm hiểu khai thác SQL Server và sử dụng nó để giải quyết bài toán quản lý dân c−. Từ mục tiêu của đề án chúng ta thấy rằng nội dung của đề án gồm có hai phần cơ bản: 1. Tìm hiểu khai thác SQL Server. 2. Giải quyết bài toán quản lý dân c− trên SQL Server. ắ Lý do chọn Microsoft SQL Server: • Xuất phát từ đặc điểm bài toán kích th−ớc lớn, phân tán, đa ng−ời sử dụng. • MS SQL Server là hệ quản trị cơ sở dữ liệu hỗ trợ tốt với l−ợng dữ liệu lớn, cho phép ng−ời sử dụng theo mô hình Client/Server. • MS SQL Server tiện dụng trong việc phân tán tra cứu dữ liệu nhanh. • MS SQL Server hỗ trợ mạnh với dữ liệu phân tán. ắ MS SQL Server chạy trên môi tr−ờng Win NT (Server) và Win 9.X, Win 2000 (Client), ắ SQL Server là một trong những hệ phần mềm tiện lợi và hiệu quả trong việc phát triển các ứng dụng cơ sở dữ liệu lớn, phân tán thích hợp cho cơ quan, tổ chức, địa ph−ơng, ắ MS SQL Server hỗ trợ tốt trong quản lý xử lý đồng nhất, bảo mật dữ liêu theo mô hình Client/Server trên mạng. ắ Với máy chủ có MS SQL Server có thể quản trị nhiều Server với tên khác nhau (các Server là hệ quản trị các cơ sở dữ liệu riêng của mình), và nhóm các Server (Server group). - 1 -
  2. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Trên mỗi Server thông th−ờng có nhiều cơ sở dữ liệu (Databases). Mỗi cơ sở dữ liệu chứa một số đối t−ợng cơ sở dữ liệu là các bảng, các khung nhìn (view), hay các thủ tục truy vấn (query). Mỗi cơ sở dữ liệu sẽ chứa danh sách những ng−ời sử dụng cơ sở dữ liệu đó, họ đ−ợc trao một số quyền nhất định để truy nhập đến từng đối t−ợng. Ng−ời sử dụng có quyền cao nhất với một cơ sở dữ liệu chính là ng−ời tạo ra cơ sở dữ liệu đó (Owner). ắ Chủ nhân cơ sở dữ liệu (Database Owner) là ng−ời sử dụng tạo nên cơ sở dữ liệu, mỗi cơ sở dữ liệu có một chủ nhân, chủ nhân cơ sở dữ liệu có đầy đủ đặc quyền bên trong cơ sở dữ liệu và xác định cung cấp khả năng truy cập tới ng−ời khác. Trong cơ sở dữ liệu của mình ng−ời sử dụng đ−ợc giới thiệu nh− là DBO trong cơ sở dữ liệu khác, Chủ nhân cơ sở dữ liệu là đ−ợc biết đến bởi tên sử dụng cơ sở dữ liệu của họ. ắ Chủ nhân của các đối t−ợng cơ sở dữ liệu (Database Object Owner) là ng−ời sử dụng tạo ra cơ sở dữ liệu (các bảng, các chỉ số, các khung nhìn, mặc định, các trigger, các quy tắc, và các thủ tục ). Mỗi cơ sở dữ liệu có duy nhất một ng−ời tạo ra. Chủ nhân của các đối t−ợng cơ sở dữ liệu là tự động gán quyền cho phép toàn bộ trên đối t−ợng cơ sở dữ liệu. Chủ nhân của đối t−ợng cơ sở dữ liệu có thể trao quyền cho phép tới ng−ời sử dụng khác, tới đối t−ợng sử dụng. ắ SQL Server l−u trữ cơ sở dữ liệu trên các thiết bị khác nhau, mỗi thiết bị có thể nằm trên đĩa cứng, mềm, băng từ, có thể nằm trên nhiều đĩa. ắ Cơ sở dữ liệu có thể đ−ợc l−u trữ trên một hay nhiều thiết bị. Cũng có thể mở rộng kích th−ớc thiết bị và thiết bị l−u trữ một cơ sở dữ liệu. ắ SQL Server cho phép quản trị với tệp dữ liệu lớn tới 32 TB (Tetabyte). ắ SQL Server đã kế thừa cùng Windows NT tạo nên một hệ thống bảo mật tốt quản trị user, Server, và những tiện ích của Windows NT. - 2 -
  3. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng I Cấu trúc MS SQL Server I. Nhóm Server và Server I.1 Tạo nhóm Server ắ Bạn có thể tạo nhóm Server với SQL Server Enterprise Manager và đặt Server của bạn trong nhóm Server. Nhóm Server cung cấp h−ớng thích hợp để tổ chức số l−ợng lớn Server vào trong một nhóm. ắ Các b−ớc tạo một nhóm Server mới (Enterprise manager) 1. Trên menu Tools, chọn Register SQL Server. 2. Trong hộp thoại Register SQL Server Properties, trong hộp Server, hãy nhấp browse( ) để truy cập vào một danh sách các Server trên mạng. 3. Trong hộp Server Group, nhấp browse( ). 4. Trong hộp name, nhập vào tên một nhóm Server. 5. Trong hộp level, hãy nhấp: • Top level group để tạo một nhóm Server mới ở mức cao nhất. • Sub-group of, rồi nhấp một nhóm Server để lập danh sách nhóm Server mới trong một nhóm Server đang có. - 3 -
  4. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT I.2 Cách đăng ký một Server 1. Trên menu Tools, chọn Register SQL Server. 2. Trong hộp thoại Register SQL Server Properties, trong hộp Server, hãy nhập tên của SQL Server để đăng ký, hoặc nhấp browse( ) để truy cập vào một danh sách các Server trên mạng. 3. Trong hộp Connect, hãy nhấp: y Use SQL Server Authentication để nỗi vào SQL Server với Microsoft Windows NT login ID password của bạn. y Use Windows NT Server Authentication, rồi nhấp một tên login và một password mà Server có thể nhận biết. để bổ xung một lớp của mức an toàn hãy chọn Always promt for login name password. 4. Trong danh sách Server Group, hãy nhấp tên của SQL Server mà ở đó bạn muốn đ−a vào Server đã đ−ợc đăng ký, hoặc nhấp browse( ) để tạo một nhóm Server mới. II. Các thiết bị và cơ sở dữ liệu hệ thống ắ Cơ sở dữ liệu là tập hợp dữ liệu, bảng dữ liệu và các đối t−ợng cơ sở dữ liệu khác có trật tự và đ−ợc giới thiệu để đáp ứng một mục đích rõ ràng, nh− là điều kiện thuận lợi của việc tìm kiếm xắp xếp và tổ chức lại dữ liệu. Cơ sở dữ liệu đ−ợc l−u trữ trên các thiết bị. - 4 -
  5. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Thiết bị (Device) là file hệ điều hành trong cơ sở dữ liệu đ−ợc l−u trữ. Một cơ sở dữ liệu có thể l−u trữ trên một vài thiết bị. SQL Server có hai loại thiết bị: Thiết bị cơ sở dữ liệu nó l−u trữ cơ sở dữ liệu và thiết bị sao l−u, l−u trữ khôi phục cơ sở dữ liệu. ắ Khi SQL Server đ−ợc cài đặt ch−ơng trình cài đặt tạo ra MASTER, MSDBDATA, và thiết bị cơ sở dữ liệu MSDBLOG. Nó cũng tạo ra cơ sở dữ liệu Master, Model, Tempdb, Pubs và đặt chúng trên thiết bị cơ sở dữ liệu MASTER, hơn nữa nó cũng tạo ra cơ sở dữ liệu Msdb và l−u trữ trên thiết bị cơ sở dữ liệu MSDBDATA và nơi thực hiện Msdb log on thiết bị MSDBLOG. II.1 Cơ sở dữ liệu chính (Master database) ắ Khi SQL Server đ−ợc cài đặt, ch−ơng trình cài đặt tạo ra thiết bị cơ sở dữ liệu MASTER rồi tạo ra cơ sở dữ liệu chính và đặt nó trên thiết bị ( device ). Ng−ời điều khiển sử dụng cơ sở dữ liệu chính và thao tác toàn bộ SQL Server. Nó kiểm tra và giữ lại sự tính toán của ng−ời sử dụng, ng−ời sử dụng từ xa. Server phục vụ từ xa có thể t−ơng tác với tiến trình đang diễn ra, có khả năng định cấu hình biến môi tr−ờng, hệ thống thông báo lỗi cơ sở dữ liệu trên SQL Server, phân phát không gian l−u trữ cho mỗi cơ sở dữ liệu, băng và đĩa sẵn có trên hệ thống và các khoá hiện hành. ắ SQL Server cũng có thể thêm đối t−ợng ng−ời sử dụng tới cơ sở dữ liệu chính nh−ng nó không đ−ợc tạo ra đối t−ợng trong cơ sở dữ liệu chính dùng cho toàn bộ hệ thống quản trị. SQL Server thiết lập và cho phép trong toàn bộ cơ sở dữ liệu chính mà phần lớn ng−ời sử dụng không thể tạo ra đối t−ợng. Bạn có thể ngăn cản ng−ời sử dụng từ đối t−ợng tạo ra trong cơ sở dữ liệu chính bằng cách thay đổi ngầm định của ng−ời sử dụng cơ sở dữ liệu. Tuy nhiên ng−ời quản trị hệ thống ngầm định cơ sở dữ liệu nên giữ nguyên cơ sở dữ liệu chính. - 5 -
  6. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II.2 Mô hình cơ sở dữ liệu (Model Database) Mô hình cơ sở dữ liệu chứa đựng bảng hệ thống, yêu cầu cho mỗi ng−ời sử dụng cơ sở dữ liệu. Nó cũng có thể đ−ợc sửa đổi theo h−ớng tuỳ biến cấu trúc mới tạo ra cơ sở dữ liệu cũng nh− mọi thay đổi bạn tạo ra mô hình đ−ợc phản ánh trong mỗi cơ sở dữ liệu mới. Có một vài thay đổi chung tạo ra mô hình: • Thêm vào loại dữ liệu ng−ời sử dụng, các ràng buộc, các quy tắc hoặc ngầm định. • Thêm vào ng−ời sử dụng nào là đ−ợc truy cập toàn bộ cơ sở dữ liệu trên SQL Server. • Lựa chọn cấu hình cơ sở dữ liệu ( nh− là select into/bulkcopy), thiết lập trong mô hình cơ sở dữ liệu. II.3 Cơ sở dữ liệu Msdb (Msdb Database) Cơ sở dữ liệu Msdb hỗ trợ thực hiện SQL và cung cấp vùng l−u trữ các thông tin lập biểu trong thời gian cài đặt phần mền Server. Ch−ơng trình cài đặt tự động tạo ra hai device ( 2MB & 1MB ) trên ổ đĩa t−ơng tự nh− là cơ sở dữ liệu chính rồi đặt cơ sở dữ liệu Msdb trên 2MB device ( MSDBDATA ) và thực hiện nhập vào device 1MB ( MSDBLOG ). II.4 Cơ sở dữ liệu Tempdb (Tempdb Database) Cơ sở dữ liệu Tempdb là chia sẻ không gian làm việc sử dụng bởi cơ sở dữ liệu trên SQL Server. Các bảng tạm thời của ng−ời sử dụng là mất đi từ Tempdb khi ng−ời sử dụng hiện hành thoát khỏi SQL Server hoặc trong thời gian truy lại từ sự dừng lại của hệ thống. Thủ tục l−u trữ các bảng tạm thời là mất đi khi thủ tục thoát ra. Bảng tạm thời cũng có thể mất đi tr−ớc khi kết thúc phiên. Kích th−ớc ngầm định của Tempdb là 2MB. Hoạt động nào đó theo sau có thể cần thiết tạo nên để tăng kích th−ớc của Tempdb: y Bảng tạm thời lớn. y Hoạt động có ý nghĩa trên bảng tạm thời. y Sắp xếp hoặc nhiều sắp xếp lớn sảy ra trong bảng cùng một lúc. y Truy vấn con và tập hợp lại với GROUP BY. y Số l−ợng lớn của Open cursor. - 6 -
  7. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II.5 Cơ sở dữ liệu Pubs (Pubs Database) Cơ sở dữ liệu Pubs là cơ sở dữ liệu lấy mẫu cung cấp nh− là công cụ nghiên cứu. Cơ sở dữ liệu pubs là cơ sở của phần lớn các ví dụ trong t− liệu của Microsoft SQL Server. Cơ sở dữ liệu mẫu đ−ợc miêu tả trong Microsoft SQL Server Transact-SQL Referrence. Nếu cơ sở dữ liệu Pubs không yêu cầu cho mục đích nghiên cứu, bạn có thể không cần nó. II.6 Các bảng hệ thống (System Tables) ắ Bảng là một tập hợp các hàng (Record) mà có liên quan đến các cột ( Fields). Cơ sở dữ liệu chính và ng−ời sử dụng cơ sở dữ liệu gồm có các bảng hệ thống thông tin về toàn bộ SQL Server và mỗi ng−ời sử dụng cơ sở dữ liệu. ắ Toàn bộ SQL Server cung cấp bảng trong cơ sở dữ liệu chính đ−ợc xem xét trong bảng hệ thống. Mỗi ng−ời sử dụng cơ sở dữ liệu tạo ra bảng hệ thống. ắ Cơ sở dữ liệu chính và bảng hệ thống đ−ợc tạo ra khi cài đặt SQL Server. Trong bảng hệ thống ng−ời sử dụng cơ sở dữ liệu tự động tạo ra kho cơ sở dữ liệu. Tên của phần lớn các bảng hệ thống đều bắt đầu với SYS. ắ Sự cho phép bảng hệ thống (Permission for System Table) Sự cho phép là giấy phép đảm bảo cho ng−ời sử dụng thực hiện hoạt động nào đó trên đối t−ợng cơ sở dữ liệu nào đó hoặc sử dụng câu lệnh nào đó. Cho phép sử dụng bảng hệ thống là đ−ợc điều khiển bởi cơ sở dữ liệu của chính mình (owner). SQL Server cài đặt ch−ơng trình thiết lập cho phép toàn bộ ng−ời sử dụng có thể đọc hệ thống bảng ngoại trừ một vài tr−ờng. ắ Truy vấn bảng hệ thống (Querying the System Table) Bảng hệ thống có thể yêu cầu đến các bảng khác, ví dụ nh− câu lệnh sau cho trở lại tên toàn bộ bảng hệ thống trong cơ sở dữ liệu. SELECT Name FROM SysObject WHERE Type = ‘S’ SQL Server có thủ tục l−u trữ hệ thống cung cấp lối tắt cho truy vấn hệ thống bảng. - 7 -
  8. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II.7 Thủ tục l−u trữ hệ thống (System Store Procedure) ắ Thủ tục l−u trữ hệ thống là tập hợp tr−ớc khi biên dịch câu lệnh Transact-SQL. Nhiều thủ tục l−u trữ hệ thống có sẵn cho việc quản trị SQL Server và hiển thị thông tin về cơ sở dữ liệu và ng−ời sử dụng. ắ Phần lớn tên thủ tục l−u trữ hệ thống bắt đầu với SP_. Thủ tục l−u trữ hệ thống xác định vị trí trong cơ sở dữ liệu chính và chính bởi ng−ời quản trị hệ thống. Nh−ng nhiều thủ tục l−u trữ hệ thống có khả năng chạy từ bất kỳ cơ sở dữ liệu nào. Nếu thủ tục l−u trữ hệ thống thực hiện trong cơ sở dữ liệu khác với cơ sở dữ liệu chính thì nó đang làm việc trên hệ thống bảng trong cơ sở dữ liệu từ bảng hệ thống đ−ợc thực hiện. ắ Thêm vào thủ tục l−u trữ hệ thống và thủ tục l−u trữ mở rộng đ−ợc cài đặt với SQL Server. Thủ tục l−u trữ mở rộng cung cấp thêm chức năng cho SQL Server, cung cấp bằng cách nạp động và thực hiện chức năng với th− viện liên kết động (DLL=Data Library Link) gắn liền với chức năng mở rộng SQL Server. Hoạt động bên ngoài SQL Server có thể dễ dàng kích hoạt và thông tin mở rộng trở lại SQL Server. Mã trạng thái trở lại & tham số đầu ra là cũng đ−ợc hỗ trợ. ắ SQL Server bao gồm hệ thống l−u trữ thủ tục có thể thêm vào và xoá đi thủ tục l−u trữ mở rộng, cung cấp thông tin về thủ tục l−u trữ mở rộng. ắ Hơn thế SQL Server cung cấp số l−ợng thủ tục l−u trữ hệ thống, thủ tục l−u trữ hệ thống khác có thể tạo ra bởi ng−ời lập trình sử dụng dịch vụ dữ liệu mở của Microsoft. II.8 Thiết lập kí tự và trật tự sắp xếp (Character Sets & Sort Order) ắ Thiết lập kí tự xác định loại kí tự mà SQL Server chấp nhận trong cơ sở dữ liệu. Thiết lập kí tự là thiết lập 256 chữ cái, chữ số và các biểu t−ợng đặc tr−ng cho một quốc gia hay một ngôn ngữ. Có thể in ra 128 kí tự đầu tiên giống nh− đại diện cho toàn bộ thiết lập kí tự. 128 kí tự sau đôi khi đ−ợc nhắc đến nh− là kí tự mở rộng. Bạn nên sử dụng thiết lập kí tự cho cả Client & Server hoặc kết quả của bạn có thể thay đổi. Tuy nhiên nếu cơ sở dữ liệu của bạn sử dụng 128 kí tự ban đầu của tập hợp kí tự, nó không tạo nên sự khác biệt nào với thiết lập kí tự bạn sử dụng bởi vì 128 kí tự đầu tiên phải giống nh− toàn bộ tập kí tự. ắ Trật tự sắp xếp thiết lập các quy tắc xác định SQL Server so sánh và giới thiệu dữ liệu trong sự h−ởng ứng tới cơ sở dữ liệu truy vấn nh− thế nào ?. Trật tự sắp xếp xác định trật tự dữ liệu là giới thiệu trả lời câu lệnh SQL Server gồm: GROUP BY, ORDER BY & DISTINT. Trật tự sắp xếp - 8 -
  9. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT cũng định rõ truy vấn nào đó là đ−ợc giải quyết nh− là truy vấn: WHERE & DISTINT. ắ Thiết lập kí tự và trật tự sắp xếp cung cấp cho toàn bộ Server. Bạn không thể có cơ sở dữ liệu khác nhau với thiết lập kí tự khác nhau hoặc trật tự sắp xếp trên Server. III. Các tiện ích, dịch vụ của SQL Server III.1 Quản lý các công cụ và các tiện ích (Administrative Tools & Utility) Công cụ đồ hoạ Mô tả (Graphical Tools) (Descrition) Sử dụng cấu hình Server, bạn có thể sử dụng SQL setup ch−ơng trình cài đặt để thay đổi các tuỳ chọn hỗ trợ mạng. Thêm vào các ngôn ngữ, xây dựng lại cơ sở dữ liệu chính, thay đổi thiết lập kí tự và trật tự sắp xếp, thiết lập các tuỳ chọn an toàn và gỡ bỏ SQL Server. SQL Server Sử dụng câu lệnh Start, Pause, Continue & Stop Manager SQL Server và SQL Execute. ISQL/w Cho phép bạn nhập lệnh Transact-SQL và thủ tục l−u trữ hệ thống trong giao diện truy vấn đồ hoạ. ISQL/w cũng cung cấp khả năng cho phân tích truy vấn đồ hoạ. SQL Security Cho phép quản lý user account với SQL Server mà Manager sử dụng an toàn thống nhất với Windows NT. SQL Enterprise Cung cấp dễ dàng, quản lý xí nghiệp rộng từ Manager Server hoặc Workstation. Nó cho phép bạn thực hiện nhiệm vụ quản trị hệ thống sử dụng giao diện đồ hoạ. Bạn có thể đặt cấu hình Server, quản trị cơ sở dữ liệu và đối t−ợng cơ sở dữ liệu, lập thời biểu cho các biến cố (event), định cấu hình và quản lý sự tái tạo và có thể làm đ−ợc hơn thế nữa. SQL Transact Cung cấp một cách dễ dàng, đồ hoạ theo cách Manager truyền các đối t−ợng và dữ liệu từ Server tới nơi khác. SQL Performance Tích hợp với Windows NT thực hiện điều khiển Monitor với SQL Server, SQL Server Book Cung cấp trực tuyến tới Microsoft SQL Server, - 9 -
  10. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Online thiết lập t− liệu bao gồm sức mạnh và khả năng tìm kiếm dễ dàng đối với ng−ời sử dụng. Microsoft ODBC Là một file trợ giúp trực tuyến cung cấp thông tin SQL Server driver về Microsoft ODBC SQL Server driver. SQL Distribute Là một file trợ giúp trực tuyến hiển thị SQL Manager Object Distribute Manager Object. SQL Client Thiết lập mặc định Net-Library & Server nối tới Configuration thông tin trên Client. Nó cũng có thể hiển thị BD- Utility Library và kiểm tra cho nhiều bản sao của DB- Library & Net-Library trong đ−ờng dẫn của bạn. III.2 SQL Server Book Online Trong thời gian cài đặt Server software hoặc Client software 32 bit hoặc 16 bit máy tính dựa vào nền Windows. Ch−ơng trình cài đặt đ−a ra cho bạn các cài đặt tự chọn SQL Server Book Online. SQL Server Book Online cung cấp truy cập trên màn hình tới toàn bộ thiết lập t− liệu SQL Server. Đáng chú ý về sức mạnh đặc biệt là nó có khả năng tìm kiếm đầy đủ văn bản mà cho phép tìm nhanh các từ hoặc đoạn văn, bạn cũng có thể tìm kiếm toàn bộ văn bản hoặc thông qua lựa chọn sách hoặc các chủ đề. III.3 Dịch vụ của SQL Server ắ SQL Server Service Manager sử dụng để bắt đầu (start), tạm dừng (pause) và dừng (stop) các thành phần Microsoft SQL Server trên Server. Các thành phần chạy nh− là dịch vụ trên Microsoft Windows NT và có thể thực hiện riêng biệt trên Microsoft Windows 95/98: Dịch vụ MSSQLServer. Dịch vụ SQLServerAgent. Dịch vụ MSDTC (Windows NT). ắ Sử dụng SQL Server Service Manager: y Cách khởi động SQL Server - 10 -
  11. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT 1. Từ nhóm ch−ơng trình Microsoft SQL Server chọn SQL Server Service Manager. 2. Trong hộp Service chọn MSSQLServer hoặc MSServerAgent. 3. Kích vào nút lệnh Start. y Cách ng−ng SQL Server 1. Từ nhóm ch−ơng trình Microsoft SQL Server chọn SQL Service Manager. 2. Từ hộp Service chọn MSSQL Server hoặc MSServerAgent. 3. Nếu bạn tạm dừng SQL Server, kích vào nút Pause để dừng dịch vụ. 4. Kích vào nút Stop để ngừng hoạt động. • Hoặc có thể dùng SQL Server Enterprise Manager. - 11 -
  12. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng II các đối t−ợng cơ sở dữ liệu I. Đối t−ợng cơ sở dữ liệu (SLQ-DMO) ắ Đối t−ợng cơ sở dữ liệu giới thiệu thuộc tính đơn của Microsoft SQL Server. ắ Lý do trình bày cơ sở dữ liệu SQL Server, đối t−ợng cơ sở dữ liệu là thành phần chính của đối t−ợng cây SLQ-DMO. Đối t−ợng cơ sở dữ liệu chứa đựng tập hợp xác định các bảng, các thủ tục l−u trữ, loại dữ liệu, và ng−ời sử dụng cơ sở dữ liệu. Ph−ơng thức của đối t−ợng cơ sở dữ liệu cho phép bạn thực hiện trình diễn bản chất chức năng duy trì cơ sở dữ liệu, nh− là khôi phục. ắ Với đối t−ợng cơ sở dữ liệu, bạn có thể: 1. Tạo cơ sở dữ liệu SQL Server. 2. Thêm cơ sở dữ liệu roles, rules, stored procedures, tables, user- defined data types, user, và view cho cơ sở dữ liệu hiện tại SQL Server. 3. Gỡ bỏ hoặc xoá bỏ đối t−ợng cơ sở dữ liệu (tables, views, ) từ cơ sở dữ liệu hiện tại SQL Server. - 12 -
  13. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT 4. Thay đổi đĩa nguồn sử dụng bởi cơ sở dữ liệu l−u trữ. 5. Khôi phục hoặc l−u trữ cơ sở dữ liệu hiện hành. 6. Điều khiển bảo mật cơ sở dữ liệu SQL Server bởi thêm users và gán quyền, từ chối hoặc t−ớc quyền truy cập tới cơ sở dữ liệu. 7. Kiểm tra tính toàn vẹn cơ sở dữ liệu. 8. Kiểm tra h−ớng sử dụng trong cơ sở dữ liệu, riêng biệt, kiểm tra trạng thái của khoá cung cấp dựa vào cơ sở dữ liệu nguồn. ắ Tạo cơ sở dữ liệu SQL Server 1. Tạo đối t−ợng Database. 2. Thiết lập thuộc tính Name của đối t−ợng Database. 3. Tạo ra đối t−ợng DBFile. 4. Thiết lập thuộc tính Name của đối t−ợng DBFile. 5. Thiết lập thuộc tính PhysicalName của đối t−ợng DBFile. 6. Thiết lập thuộc tính đối t−ợng DBFile tuỳ chọn cho cơ sở dữ liệu mới nh− kích th−ớc. 7. Thêm vào đối t−ợng DBFile cho đối t−ợng Database mới, tên đối t−ợng FileGroup cơ bản. 8. Thêm vào đối t−ợng Database cho tập hợp Database của kết nối đối t−ợng SQL Server. ắ Chỉ định file ghi 1. Tạo đối t−ợng LogFile. 2. Thiết lập thuộc tính Name. 3. Thiết lập thuộc tính PhysicalName. 4. Thiết lập thuộc tính LogFile Size. 5. Thêm vào đối t−ợng LogFile cho tập hợp LogFile của đối t−ợng Transation, đối t−ợng Database mới. ắ Tạo giản đồ cơ sở dữ liệu Giản đồ cơ sở dữ liệu nên hỗ trợ yêu cầu giao dịch là vì các yêu cầu query-driven đặc tr−ng của một thiết kế cơ sở dữ liệu OLTP. Ví dụ nh− đ−a ra giản đồ cơ sở dữ liệu từ hàng đ−a vào hệ thống: - 13 -
  14. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Xác định sự kiện và kích th−ớc bảng. y Thiết kế các bảng thực. y Thiết kế kích th−ớc bảng. I.1 Tập hợp DatabaseRoles ắ Tập hợp DatabaseRoles chứa đối t−ợng DatabaseRole đ−a ra vai trò đặc quyền bảo mật cơ sở dữ liệu trong SQL Server. ắ Cơ sở dữ liệu Roles trong SQL Server có thể chứa một hoặc nhiều thành viên (database user). Một thuộc tính xác định ng−ời sử dụng có thể tạo ra Databaseroles, thêm vào hoặc gỡ bỏ chúng từ Databaseroles, gán quyền hoặc phủ nhận cơ sở dữ liệu đặc quyền tới roles cho đặc quyền quản lý một hoặc nhiều trật tự logic ng−ời sử dụng. - 14 -
  15. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Với tập hợp DatabaseRoles, bạn có thể: • Tạo ra cơ sở dữ liệu roles SQL Server. • Gỡ bỏ cơ sở dữ liệu roles SQL Server. I.2 Tập hợp các mặc định (Defaults) ắ Tập hợp Defaults chứa đối t−ợng Default tham chiếu tới các mặc định SQL Server. Với tập hợp các Defaults, bạn có thể: Tạo ra các Defaults. Gỡ bỏ các Defaults. ắ Quá trình gọi liên kết cho phép một mặc định SQL Server. Mặc định có thể giới hạn một hoặc nhiều cột hoặc loại dữ liệu ng−ời sử dụng xác định. Một giới hạn mặc định không thể gỡ bỏ. ắ Các mặc định (defaults) • Trong SQL Server mỗi cột trong bản ghi phải chứa đựng một vài giá trị thậm chí nếu giá trị đó là NULL. Bạn xác định cột nào có thể chấp nhận giá trị NULL bởi kiểu dữ liệu, mặc định, hoặc các ràng buộc. • Mặc định chỉ định giá trị mà SQL Server sẽ chèn vào khi ng−ời sử dụng không đ−a vào giá trị (trong cả hai cột NULL hoặc NOT NULL) • Một cách dễ dàng nhất để chỉ định các mặc định là xác định ràng buộc DEFAULT khi bạn tao nên bảng. • Bạn cũng có thể tạo ra giá trị mặc định và ràng buộc chúng vào cột để ng−ời sử dụng xác định loại dữ liệu. I.3 Tập hợp FileGroup ắ Tập hợp FileGroups chứa đựng đối t−ợng FileGroup tham chiếu FileGroups của cơ sở dữ liệu Microsoft SQL Server. FileGroups Collection (SQL-DMO) Với tập hợp các FileGroups, bạn có thể: Tạo một SQL Server FileGroups mới. Huỷ bỏ SQL Server FileGroups. ắ SQL Server FileGroups có thể đ−ợc sử dụng kết hợp với file hệ điều hành sử dụng để duy trì cơ sở dữ liệu. FileGroups có thể đơn giản nhiệm - 15 -
  16. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT vụ quản trị nh− là thao tác l−u trữ và khôi phục. Theo mặc định, cơ sở dữ liệu SQL Server đ−ợc tạo ra trên đúng một FileGroups gọi là PRIMARY. ắ Khi sử dụng Item hoặc Remove, tập hợp FileGroups hỗ trợ xác minh thành viên sử dụng một tên hoặc thứ tự tham chiếu cú pháp. I.4 Tập hợp FullTextCatalogs ắ Tập hợp FullTextCatalogs chứa đối t−ợng tham chiếu FullTextCatalogs. Microsoft SQL Server tìm kiếm liên tục dữ liệu trong FullTextCatalogs. Với tập hợp FullTextCatalogs, bạn có thể: Tạo một danh mục chỉ số mới cho Microsoft SQL Server. Xoá bỏ một danh mục chỉ số của Microsoft SQL Server. I.5 Tập hợp các quy tắc (Rules Collection) ắ Tập hợp Rules chứa các đối t−ợng Rule tham chiếu Microsoft SQL Server, thực thi ràng buộc tính toàn vẹn dữ liệu nh− là các đối t−ợng cơ sở dữ liệu gọi là các quy tắc. Với các tập quy tắc, bạn có thể: Tạo thực thi ràng buộc tính toàn vẹn SQL Server nh− là các quy tắc. Xoá bỏ một quy tắc xác định từ SQL Server. ắ Các quy tắc là các đối t−ợng cơ sở dữ liệu mà nó có thể xác định các giá trị có thể đ−ợc chèn vào một cột riêng biệt. Chú ý rằng sử dụng các ràng buộc là đề cập cách giới hạn các cột dữ liệu bởi vì nhiều ràng buộc có thể xác định trên một cột hoặc nhiều cột. Ví dụ nh− trong một bảng với cột gọi là phone_number, bạn có thể xác định quy tắc mà xác định cột chấp nhận duy nhất 10 con số đ−a vào. I.6 Tập hợp các thủ tục l−u trữ (StoredProcedures Collection) ắ Tập hợp StoredProcedures chứa đối t−ợng StoredProcedure tham chiếu hệ thống và ng−ời sử dụng xác định thủ tục l−u trữ của cơ sở dữ liệu Microsoft SQL Server. - 16 -
  17. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Với các StoredProcedure, bạn có thể: Tạo thủ tục l−u trữ. Huỷ thủ tục l−u trữ. ắ Sử dụng thủ tục l−u trữ (using stored procedures) Thủ tục l−u trữ có khả năng mở rộng, hiệu quả và có tính mềm dẻo của SQL Server, và đột ngột cải tiến thực hiện câu lệnh SQL và file bat. Thủ tục l−u trữ có thể: y Nhận tham số. y Gọi các thủ tục khác. y Trở lại giá trị trạng thái để gọi thủ tục hoặc file bat tới chỉ định thành công hoặc thất bại. y Trở lại giá trị của tham số gọi thủ tục hoặc file bat. Thủ tục l−u trữ có thể dùng kỹ thuật bảo mật, bởi vì ng−ời sử dụng có thể đ−ợc gán quyền để thực hiện thủ tục l−u trữ thậm chí họ không thể thực hiện trên bảng hoặc khung nhìn tham chiếu tới nó. ắ Tạo ra thủ tục l−u trữ Bạn có thể tạo ra thủ tục l−u trữ sử dụng câu lệnh CREATE PROCEDURE Transact-SQL. Tr−ớc khi tạo thủ tục l−u trữ, cần l−u ý đến: y Câu lệnh CREATE PROCEDURE không thể kết hợp với câu lệnh Transact-SQL khác trong file bat đơn. y Cho phép thực hiện tạo câu lệnh CREATE PROCEDURE mặc định tới cơ sở dữ liệu của ng−ời tạo ra nó và có thể chuyển giao nó tới ng−ời sử dụng khác. y Thủ tục l−u trữ là cơ sở dữ liệu đối t−ợng và mỗi tên phải tuân theo quy tắc chỉ định. y Bạn có thể tạo ra thủ tục l−u trữ duy nhất trong cơ sở dữ liệu hiện hành. ắ Khi bạn tạo ra thủ tục l−u trữ, bạn nên chỉ định: y Bất kỳ tham số đầu vào và tham số đầu ra theo h−ớng gọi thủ tục hoặc tệp bat. y Các câu lệnh ch−ơng trình thực hiện thao tác trong cơ sở dữ liệu, bao gồm h−ớng tới thủ tục khác. - 17 -
  18. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Trạng thái giá trị trở lại theo h−ớng thủ tục hoặc tệp bat cho biết thành công hoặc thất bại. ắ Tạo ra thủ tục l−u trữ: y Từ menu trong SQL Server Enterprise Manager, chọn cơ sở dữ liệu muốn tạo thủ tục l−u trữ, rồi kích chuột phải vào Stored procedure chọn New stored procedure y Sử dụng câu lệnh CREATE PROCEDURE. ắ Quy tắc ch−ơng trình thủ tục l−u trữ gồm có: y Xác định câu lệnh CREATE PROCEDURE có thể gồm bất kỳ số và kiểu câu lệnh SQL loại trừ câu lệnh CREATE, mà không thể sử dụng bất cứ chỗ nào trong thủ tục l−u trữ: CREATE DEFAULT CREATE TRIGGER CREATE PROCEDURE CREATE VIEW CREATE RULE y Các đối t−ợng cơ sở dữ liệu khác có thể tạo trong thủ tục l−u trữ. Bạn có thể tham chiếu đối t−ợng tạo ra trong một thủ tục l−u trữ chỉ cần đ−ợc tạo ra tr−ớc khi nó đ−ợc tham chiếu. y Bạn có thể tham chiếu các bảng tạm trong thủ tục l−u trữ. y Số l−ợng lớn nhất của tham số trong thủ tục là 1024. y Số l−ợng lớn nhất của biến cục bộ trong thủ tục l−u trữ là giới hạn bởi bộ nhớ có sẵn. y Tuỳ thuộc vào bộ nhớ, kích th−ớc cực đại của thủ tục l−u trữ là 128 MB. ắ Xác định tên bên trong thủ tục l−u trữ • Bên trong thủ tục l−u trữ, tên đối t−ợng sử dụng với các câu lệnh (ví dụ nh− SELECT hoặc INSERT) mà không xác định ng−ời sử dụng mặc định cho thủ tục l−u trữ của chính mình. Nếu ng−ời sử dụng mà tạo ra thủ tục l−u trữ không xác định tên của bảng tham chiếu trong câu lệnh SELECT, INSERT, UPDATE, hoặc DELETE trong thủ tục l−u trữ, truy cập bảng này thông qua thủ tục l−u trữ là giới hạn bởi mặc định để tạo ra thủ tục. • Tên đối t−ợng sử dụng với câu lệnh ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS, và DBCC phải đủ khả - 18 -
  19. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT năng cùng với sự hiện diện tên của chính đối t−ợng nếu ng−ời sử dụng khác là sử dụng thủ tục l−u trữ. ắ Đổi tên thủ tục l−u trữ 1. Mở rộng một sever group, rồi mở rộng server. 2. Mở rộng Database, mở rộng cơ sở dữ liệu mà thủ tục thuộc về nó rồi kích Stores procedure. 3. Trong detail pane, kích phải stored procedure, chọn rename. 4. Gõ tên mới của thủ tục. Thực hiện thủ tục l−u trữ hệ thống ps_rename. ắ Xoá một thủ tục l−u trữ. 1. Mở rộng một sever group, rồi mở rộng server. 2. Mở rộng Database, mở rộng cơ sở dữ liệu mà thủ tục thuộc về nó rồi kích Stores procedure. 3. Trong detail pane, kích phải stored procedure, chọn delete. 4. Để xem cách xoá bỏ thủ tục l−u trữ sẽ ảnh h−ởng nh− thế nào tới cơ sở dữ liệu, nhấp show dependencies. 5. Nhấp Drop all. Sử dụng câu lệnh DROP PROCEDURE. I.7 Tập hợp kiểu dữ liệu hệ thống (SystemDatatypes Collection) ắ Tập hợp SystemDatatypes chứa đối t−ợng SystemDatatype liệt kê kiểu dữ liệu cơ bản của cài đặt Microsoft SQL Server. ắ SQL Server xác định số l−ợng cố định kiểu dữ liệu cơ bản. Bởi vì số l−ợng là cố định, tập hợp SystemDatatypes, miêu tả kiểu dữ liệu này, cố định các thành viên và không hỗ trợ ph−ơng thức Add hoặc Remove. I.8 Tập hợp các bảng (Tables Collection) ắ Tâp hợp các bảng chứa đối t−ợng bảng tham chiếu hệ thống và xác định các bảng của ng−ời sử dụng của cơ sở dữ liệu Microsoft SQL Server. - 19 -
  20. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Với tập hợp các bảng, bạn có thể: Tạo bảng. Xoá bảng. ắ Sự lập kế hoạch tạo bảng: 1. Quyết định kiểu nào của bảng dữ liệu sẽ chứa đựng. 2. Quyết định cột nào bạn cần trong bảng, loại dữ liệu nào cho mỗi cột. 3. Quyết định cột nào nên chấp nhận giá trị null. 4. Quyết định sử dụng ràng buộc có mặc định hoặc không quy tắc. 5. Quyết định kiểu chỉ số nào bạn cần, bạn cần chỉ số để làm gì và cột nào nên là khoá chính và khoá ngoài. ắ Để tạo bảng 1. Tạo loại dữ liệu do ng−ời sử dụng xác định sau đó bạn xác định bảng nào bạn muốn sử dụng chúng. 2. Tạo bảng với các ràng buộc, các mặc định sử dụng bởi câu lệnh CREATE TABLE. 3. Tạo triggers với câu lệnh CREATE TRIGGER. 4. Tạo các khung nhìn với câu lệnh CREATE VIEW. 5. Tạo bảng y Trong SQL Server Enterprise Manager, chọn cơ sở dữ liệu bạn muốn tạo bảng, chọn bảng hoặc đối t−ợng, kích chuột phải rồi chọn New table. y Sử dụng câu lệnh CREATE TABLE. y Sử dụng tuỳ chọn INTO của câu lệnh SELECT. Bạn có thể xác định lên tới 250 cột mỗi bảng. Bảng và tên cột phải tuân theo các quy tắc cho đồng nhất hoá, chúng phải duy nhất với bảng. Nh−ng bạn có thể sử dụng tên cột t−ơng tự trong bảng khác, trong cơ sở dữ liệu t−ơng tự, Bạn phải xác định loại dữ liệu cho mỗi cột. - 20 -
  21. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Xoá bảng Khi bạn không cần bảng, bạn có thể gỡ bỏ nó từ cơ sở dữ liệu. 1. Trong cửa sổ SQL Server Enterprise Manager, chọn bảng bạn muốn xoá, kích chuột phải, chọn delete, rồi chọn Drop all. 2. Sử dụng câu lệnh DROP TABLE. ắ Đổi tên bảng 1. Trong cửa sổ SQL Server Enterprise Manager, chọn bảng bạn muốn đổi tên, kích chuột phải, chọn Rename. 2. Thực hiện thủ tục l−u trữ hệ thống sp_rename. Ví dụ: sp_rename title, books Thay đổi tên của bảng titles thành bảng books. ắ Thay đổi bảng Sau khi bạn tạo bảng, bạn muốn thay đổi cấu trúc bảng bởi cộng thêm vào cột, thêm vào khoá bản ghi, hoặc thay đổi các ràng buộc. Xác định bảng cần thay đổi 1. Trong SQL Server Enterprise Manager, kích đúp vào bảng thích hợp, rồi tạo nên sự thay đổi. 2. Sử dụng câu lệnh ALTER TABLE. I.9 Tập hợp kiểu dữ liệu do ng−ời dùng xác định. ắTập hợp UserDefineDatatypes chứa các đối t−ợng UserDefineDatatypes tham chiếu cơ chế tính toàn vẹn dữ liệu SQL Server gọi là kiểu dữ liệu do ng−ời sử dụng xác định. Với kiểu dữ liệu do ng−ời dùng xác định, bạn có thể: Tạo một kiểu dữ liệu mới do ng−ời dùng xác định. Xoá bỏ kiểu dữ liệu do ng−ời sử dụng xác định. ắ Tạo nên kiểu dữ liệu do ng−ời dùng xác định (create user-defined datatype) 1. Mở rộng một server group, rồi mở rộng server - 21 -
  22. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT 2. Mở rộng Database, rồi mở rộng cơ sở dữ liệu mà qua đó bạn tạo kiểu dữ liệu do ng−ời dùng xác định. 3. Nhấp phải chuột vào Defined Data Type, rồi nhấp New User Defined Data Type Hoặc từ menu SQL Server Enterprice Manager, chọn Action\ New\ user Defined data type 1. Nhập tên của kiểu dữ liệu mới. 2. Trong danh sách Data type, hãy chọn kiểu dữ liệu. 3. Nếu length đ−ợc kích hoạt, nó chứa các kiểu dữ liệu:binary, char, nchar, nvarchar, varbinary và varchar. 4. Để cho phép dữ liệu chấp nhận các giá trị trống, hãy chọn Allow NULLs. 5. Tuỳ ý, trong danh sách Rule và Default, hãy chọ một quy tắc hoặc mặc định để buộc vào kiểu dữ liệu đã đ−ợc ng−ời dùng xác định. ắ Xoá dữ liệu do ng−ời dùng xác định 1. Mở rộng một server group, rồi mở rộng server. 2. Mở rộng Database, rồi mở rộng dữ liệu, rồi nhấp User Defined Data Types. 3. Trong detail pane, hãy nhấp kiểu dữ liệu xoá bỏ rồi nhấp Delete. - 22 -
  23. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT 4. Để xem cách xoá bỏ kiểu dữ liệu này sẽ ảnh h−ởng nh− thế nào đến cơ sở dữ liệu, bạn hãy nhấp show Dependencies 5. trong hộp thoại Drop Object, hãy nhấp Drop All. I.10 Tập hợp ng−ời sử dụng (Users Collection) ắ Tập hợp Users chứa đối t−ợng phản chiếu định nghĩa ng−ời sử dụng cơ sở dữ liệu của SQL Server. Với tập hợp Users, bạn có thể: Tạo ra cơ sở dữ liệu ng−ời sử dụng SQL Server. Huỷ bỏ cơ sở dữ liệu ng−ời sử dụng SQL Server. I.11 Tập hợp các khung nhìn (Views Collection) ắ Tập hợp các Views chứa đối t−ợng View tham chiếu đến khung nhìn xác định trong cơ sở dữ liệu Microsoft SQL Server. Với tập hợp các Views, bạn có thể: Tạo ra một bảng view. Huỷ bỏ một bảng view. ắ Sử dụng khung nhìn để tối −u hoá dữ liệu Các khung nhìn cho phép ng−ời sử dụng khác nhau xem dữ liệu bằng cách khác nhau, ngay cả khi họ đang sử dụng cơ sở dữ liệu t−ơng tự cùng một lúc. Sự thuận lợi này là đặc biệt quan trong khi ng−ời sử dụng với nhiều quan tâm khác nhau và trình độ kỹ năng chia sẻ giống nh− cơ sở dữ liệu. ắ Sử dụng khung nhìn để xuất dữ liệu Sử dụng khung nhìn, bạn có thể xuất dữ liệu tới các ứng dụng khác. Ví dụ giả sử bạn muốn sử dụng bảng stores và sales để phân tích dữ liệu bán hàng đ−ợc l−u trữ trong Microsoft Excel. Để làm điều này, bạn có thể tạo nên khung nhìn dựa trên bảng stores và sales. Rồi bạn có thể xuất dữ liệu xác định bởi sử dụng ch−ơng trình tiện ích bulk copy (bcp). ắ Sử dụng khung nhìn cho sự bảo mật Sử dụng câu lệnh CREATE VIEW với WITH CHECK OPTION, bạn có thể đảm bảo rằng ng−ời sử dụng truy vấn và sửa chữa duy nhất dữ liệu mà họ nhìn thấy. Tuỳ chọn này bắt toàn bộ câu lệnh sửa chữa dữ liệu thực - 23 -
  24. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT hiện dựa vào khung nhìn tham gia thiết lập tiêu chuẩn với câu lệnh SELECT xác định khung nhìn. Sử dụng câu lệnh GRANT và REVOKE, bạn có thể giới hạn ng−ời sử dụng cơ sở dữ liệu truy cập để xác định đối t−ợng cơ sở dữ liệu bao gồm các khung nhìn. Nếu khung nhìn và toàn bộ bảng thực hiện gán quyền tới đối t−ợng khác sử dụng khung nhìn và chủ nhân có thể từ chối truy cập tới khung nhìn ở d−ới bảng. ắ Tạo các khung nhìn Bạn có thể tạo views với SQL Server Enterprise Manager hoặc sử dụng câu lệnh CREATE TABLE. 1. Trong cửa sổ SQL Server Enterprise Manager, chọn view thích hợp, rồi kích chuột phải, chọn new view 2. Sử dụng câu lệnh CREATE TABLE. ắ Đổi tên view. Bạn có thể đổi tên view, chủ đề theo h−ớng dẫn sau: y View khi đổi tên phải trong cơ sở dữ liệu hiện tại. y Tên mới phải theo sau các quy tắc cho đồng nhất hoá. y Bạn có thể đổi tên duy nhất view của bạn. y Chủ nhân cơ sở dữ liệu có thể thay đổi tên của bất kỳ view của ng−ời sử dụng. Thực hiện thủ tục hệ thống sp_rename. Ví dụ: sp_rename account, owing II. Đặc tả dung l−ợng cực đại của SQL Server Bảng sau chỉ định kích th−ớc cực đại và các số l−ợng khác nhau xác định đối t−ợng trong cơ sở dữ liệu Microsoft SQL Server hoặc tham chiếu trong câu lệnh Transact-SQL. Object (đối t−ợng) Maximum sizes/numbers SQL Server 6.5 SLQ Server 7.0 65,536* Kích th−ớc Kích th−ớc file bat (Batch size) 128 KB gói tin mạng (Network Packet Size) Số byte cho mỗi chuỗi kí tự cột 255 8000 (Bytes per short string column) - 24 -
  25. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Số byte cho mỗi cột text, ntext hoặc image 2 GB-2 2 GB-2 (Bytes per text, ntext, or image column) Số byte cho mỗi GROUP BY, ORDER BY 900 8060 (Bytes per GROUP BY, ORDER BY) Số byte cho mỗi chỉ số 900 900 (Bytes per index) Số byte cho mỗi khoá ngoài 900 900 (Bytes per foreign key) Số byte cho mỗi khoá cơ bản 900 900 (Bytes per primary key) Số byte cho mỗi hàng 1962 8060 (Bytes per row) Số byte trong nguồn text của thủ Lesser of batch size or tục l−u trữ Bytes in source text of a 65025 ( 250 MB stored procedure) Nhóm chỉ số cho mỗi bảng 1 1 (Clustered indexes per table) Hạn chế tốt nhất bởi Các cột trong GROUP BY, số l−ợng byte ORDER BY (Columns in GROUP 16 (Limited only by BY, ORDER BY) number of bytes) Các cột hoặc biểu thức trong câu lệnh GROUP BY WITH CUBE 10 10 hoặc WITH ROLLUP Các cột cho mỗi chỉ số 16 16 (Columns per index) Các cột cho mỗi khoá ngoài 16 16 (Columns per foreign key) Các cột cho mỗi khoá cơ bản 16 16 (Columns per primary key) Các cột cho mỗi bảng cơ bản 250 1024 (Columns per base table) Các cột cho mỗi câu lệnh SELECT 4096 4096 Các cột cho mỗi câu lệnh INSERT 250 1024 Max. value of Kết nối cho mỗi client Giá trị định cấu hình configured (Connections per client) kết nối connections - 25 -
  26. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Kích th−ớc cơ sở dữ liệu 1 TB 1,048,516 TB (Database size) Cơ sở dữ liệu cho mỗi server 32,767 32,767 (Databases per server) Nhóm file cho mỗi cơ sở dữ liệu N/A 256 (Filegroups per database) Các file cho mỗi cơ sở dữ liệu 32 32,767 (Files per database) Kích th−ớc file (dữ liệu) 32 GB 32 TB {File size (data)} Kích th−ớc file (log) 32 GB 4 TB {File size (log)} Khoá ngoài bảng tham chiếu cho mỗi bảng. 16 253 (Foreign key table references per table) định danh độ dài (trong kí tự) 30 128 {Identifier length (in characters)} Các khoá cho mỗi Các khoá cho mỗi kết nối Max. locks per server (Max. locks per (Locks per connection) server server) 2,147,483,647 (static) Các khoá cho mỗi server 2,147,483,647 40% of SQL Server (Locks per server) memory (dynamic) Mức lồng nhau của thủ tục l−u trữ 16 32 (Nested stored procedure levels) Các truy vấn lồng nhau 16 32 (Nested subqueries) Mức lồng nhau của trigger 16 32 (Nested trigger levels) Chỉ số không nhóm cho mỗi bảng 249 249 (Nonclustered indexes per table) Các đối t−ợng mở đồng thời trong server* 2 billion 2,147,483,647 (Objects concurrently open in a server*) Đối t−ợng trong cơ sở dữ liệu* 2 billion 2,147,483,647 (Objects in a database*) Tham số cho mỗi thủ tục l−u trữ 255 1024 (Parameters per stored procedure) - 26 -
  27. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Tham chiếu cho mỗi bảng 31 63 (REFERENCES per table) Giới hạn bởi l−u trữ Các hàng cho mỗi bảng Limited by có sẵn (Rows per table) available storage (Limited by available storage) Độ dài chuỗi kí tự SLQ 128 KB 128* TDS packet size {SLQ string length (batch size)} Giới hạn bởi số l−ợng Số bảng cho mỗi cơ sở dữ liệu 2 billion đối t−ợng trong cơ sở (Tables per database) dữ liệu Số bảng cho mỗi câu lệnh SELECT 16 256 (Tables per SELECT statement) Giới hạn bởi số l−ợng Triggers cho mỗi bảng 3 đối t−ợng trong cơ sở (Triggers per table) dữ liệu 249 không nhóm và 1 Chỉ số duy nhất hoặc các ràng nhóm (249 buộc cho mỗi bảng (UNIQUE 249 nonclustered and 1 indexes, constraints per table) clustered) * Các đối t−ợng cơ sở dữ liệu bao gồm toàn bộ các bảng, các thủ tục l−u trữ, các thủ tục mở rộng, các triggers, các defaults, và các constraints. Tổng số l−ợng của toàn bộ các đối t−ợng trong cơ sở dữ liệu không thể v−ợt quá 2,147,483,647. - 27 -
  28. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng III Làm việc với dữ liệu I. Dữ liệu ắ Sau khi bạn tạo ra các bảng, bạn sẽ muốn nhập dữ liệu vào các bảng và làm việc với dữ liệu. Bạn có thể thay đổi dữ liệu, hủy bỏ dữ liệu hoặc thêm vào dữ liệu, bạn cũng có thể xuất, nhập dữ liệu tới từ các ứng dụng khác nhau. ắ Trong SQL Server bạn có thể làm việc với dữ liệu bởi câu lệnh thay đổi dữ liệu (modification data), bạn có thể thêm dữ liệu với lệnh INSERT, thay đổi dữ liệu với câu lệnh UPDATE, WRITETEXT hoặc UPDATETEXT và huỷ bỏ dữ liệu với câu lệnh DELETE hoặc TRUNCATE TABLE. I.1 Thêm vào kế hoạch hoặc thay đổi dữ liệu Để làm việc với dữ liệu trong cơ sở dữ liệu, bạn cần hoạch định cho việc thêm vào hoặc thay đổi. Bạn cần quan tâm ai nên thay đổi dữ liệu, họ cần thực hiện thay đổi nó nh− thế nào và đảm bảo tính toàn vẹn của dữ liệu ra sao. ắ Cho phép gán quyền y Câu lệnh sửa chữa dữ liệu là không cần thiết có sẵn cho mọi ng−ời. Cơ sở dữ liệu chính chủ và chủ nhân của các đối t−ợng cơ sở dữ liệu có thể sử dụng câu lệnh GRANT và REVOKE để xác định ai có thể sử dụng câu lệnh sửa chữa dữ liệu. y Sự cho phép có thể gán quyền tới ng−ời sử dụng riêng lẻ, hay một nhóm ng−ời sử dụng hoặc tới một tổ chức xã hội. ắ Đảm bảo tính toàn vẹn của dữ liệu Để đảm bảo tính toàn vẹn của cơ sở dữ liệu, bạn có thể hạn chế toàn bộ dữ liệu vào trong bảng của cơ sở dữ liệu. Ví dụ bạn có thể yêu cầu dữ liệu integer (nguyên) trong một cột của bảng và dữ liệu character (kí tự) trong cột khác. Để giới hạn toàn bộ dữ liệu, bạn có thể sử dụng loại dữ liệu hệ thống, kiểu dữ liệu do ng−ời dùng xác định, các mặc định, các ràng buộc, hoặc đặc tính IDENTITY. - 28 -
  29. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT I.2 Thêm vào, thay đổi hoặc gỡ bỏ dữ liệu Trong SQL Server, bạn có thể thêm vào, xoá bỏ, hoặc thay đổi dữ liệu bởi sử dụng câu lênh sửa chữa dữ liệu INSERT, DELETE, TRUNCATE TABLE, UPDATE, UPDATETEXT và WRITETEXT. 1. INSERT thêm vào một hàng mới trong bảng. Ví dụ: giả sử có bảng Congdan có 4 tr−ờng là [Stt], [Họ và tên], [Tuổi], [Giới tính], [Trình độ]. Stt Họ và tên Tuổi Giới tính Trình độ 1 Vũ Hùng C−ờng 23 Nam Đại Học 2 Lê Huyền Thanh 20 Nữ Cao Đẳng 3 Nguyễn Thị Mơ 18 Nữ Trung Học INSERT INTO Congdan([Stt], [Họ và tên], [Tuổi], [Giới tính], [Trình độ]) VALUE( 3, ‘Nguyễn Thị Mơ’,18,‘Nữ’,‘Trung Học’) Sau khi thực hiện song câu lênh INSERT thì trong bảng Congdan có thêm bản nghi mới (in nghiêng). 2. DELETE xoá một hoặc nhiều hàng, TRUNCATE TABLE xoá bỏ toàn bộ hàng trong bảng. Ví dụ: DELETE Congdan WHERE Tuổi=20 Sau khi thực hiện câu lênh thì bản ghi số 2 sẽ bị xoá. 3. UPDATE thay đổi các hàng. Ví dụ: UPDATE Congdan SET [Trung Học]= ‘Đại Học’ WHERE [Trình độ]= ‘Trung Học’ Cập nhật ng−ời có trình độ trung học lên Đại Học. 4. UPDATETEXT và WRITETEXT thay đổi text và image. I.3 Nhập, xuất dữ liệu ắ Nhập dữ liệu, xử lí khôi phục dữ liệu từ nguồn bên ngoài tới Microsoft SQL Server, ví dụ một file văn bản ASCII, chèn dữ liệu vào trong bảng SQL Server. ắ Nhập dữ liệu từ nguồn dữ liệu bên ngoài vào trong SQL Server giống nh− b−ớc đầu tiên bạn thực hiện sau khi cài đặt cơ sở dữ liệu của bạn. Sau - 29 -
  30. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT khi dữ liệu vừa nhập vào trong cơ sở dữ liệu SQL Server, bạn có thể bắt đầu làm việc với cơ sở dữ liệu. ắ Xuất dữ liệu là xử lý trích dữ liệu từ SQL Server tới một vài khuôn thức chỉ định ng−ời sử dụng, ví dụ nh− copy nội dung của bảng SQL Server tới cơ sở dữ liệu Microsoft Access. ắ Xuất dữ liệu th−ờng xuất hiện ít. SQL Server cung cấp công cụ đa dạng và tính năng mà cho phép các ứng dụng, nh− là Access hoặc Microsoft Excel, đ−ợc kết nối và thao tác trực tiếp với dữ liệu. ắ SQL Server cung cấp các công cụ nhập, xuất dữ liệu và từ sự đa dạng của dữ liệu bao gồm các file text, nguồn dữ liệu ODBC (nh− là cơ sở dữ liệu Oracle), nguồn dữ liệu OLE DB (nh− là các server khác chạy SQL Server), các file văn bản ASCII, và các bảng tính Excel. ắ Ngoài ra, mô hình SQL Server cho phép dữ liệu phân bố thông qua các tổ chức, copy dữ liệu giữa các vị trí và tự động thay đổi đồng bộ giữa dữ liệu copy khác. I.4 Xử lí thực hiện trong khối ắ Bạn có thể đ−a ra câu lệnh SQL tới SQL Server cùng một lúc, đ−a vào câu lệnh và nhận kết quả đầu ra, hoặc bạn có thể đ−a ra nhiều câu lệnh SQL nh− là một khối (batches), hoặc trong một file. Khối lệnh của SQL là xác định bởi tín hiệu end-of-batch (cuối khối) mà chỉ dẫn SQL Server đi tới đầu và thực hiện câu lệnh. ắ File batch là tập hợp của một hoặc nhiều câu lệnh SQL gửi trong một khối thống nhất về phía máy trạm (Client). Mỗi file batch là đ−ợc biên dịch trong sơ đồ thực hiện đơn. Nếu file batch chứa nhiều câu lệnh SQL, toàn bộ các b−ớc tối −u cần thực hiện toàn bộ câu lệnh đ−ợc xây thành kế hoạch thực hiện đơn. ắ Có vài cách chỉ định file batch. 1. Toàn bộ câu lệnh SQL gửi trong một thực hiện đơn từ một ứng dụng bao gồm khối đơn và tạo ra kế hoạch thực hiện đơn. 2. Toàn bộ câu lệnh trong thủ tục l−u trữ hoặc trigger bao gồm khối đơn (single batch). Mỗi thủ tục l−u trữ hoặc trigger biên dịch thành kế hoạch thực hiện đơn. 3. Thực hiện chuỗi bởi câu lệnh EXECUTE là biên dịch khối (batch) thành kế hoạch thực hiện đơn. 4. Thực hiện chuỗi bởi thủ tục l−u trữ hệ thống sp_executesql là biên dịch khối thành kế hoạch thực hiện đơn. - 30 -
  31. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ví dụ nh− batch chứa bốn câu lệnh: Câu lệnh EXECUTE thực hiện thủ tục l−u trữ. Gọi sp_execuresql thực hiện chuỗi kí tự. Câu lệnh EXECUTE thực hiện chuỗi kí tự. Câu lệnh UPDATE chuyển đến bảng mà có cập nhật trigger. ắ Transact-SQL cung cấp các từ đặc biệt gọi là trình điều khiển dòng ngôn ngữ (control-of-flow language) mà điều khiển l−u l−ợng của sự thực hiện câu lệnh Transact-SQL, khối lệnh và các thủ tục l−u trữ. Các từ có thể sử dụng trong câu lệnh Transact-SQL, trong khối, trong thủ tục l−u trữ. Bảng từ khoá trình điều khiển dòng ngôn ngữ Transact-SQL: Từ khoá (keyword) Mô tả (Description) BEGIN END Định nghĩa khối lệnh. BREAK Thoát khỏi vòng lặp WHILE. CONTINUE Bắt đầu lại vòng lặp WHILE. Tiếp tục xử lí câu lệnh sau label nh− là xác định bởi GOTO label label. Xác định điều kiện và tuỳ chọn, thực hiện luân IF ELSE phiên khi điều kiện là FALSE. RETURN Thoát khỏi vô điều kiện. WAITFOR Thiết lập thực hiện câu lệnh delay. WHILE Lặp lại câu lệnh trong khi điều kiện là TRUE. - 31 -
  32. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II. Truy Tìm dữ liệu với các truy vấn Bạn có thể truy tìm dữ liệu từ các bảng (table) và các khung nhìn (view) nhanh chóng và dễ dàng bởi sử dụng các truy vấn (queries). Bạn có thể sử dụng truy vấn cho rất nhiều các lý do: trả lời nhanh câu hỏi, thiết lập thông tin báo cáo hoặc tìm kiếm bất kỳ tâp hợp con có liên quan đến dữ liệu của bạn. SQL Server cung cấp công cụ bạn cần để nhận đ−ợc dữ liệu bạn cần tìm kiếm. II.1 Tạo ra các truy vấn cơ bản ắ Bạn có thể sử dụng câu lệnh SELECT để chọn các hàng và các cột từ bảng, bạn có thể sử dụng nó cho tập hợp (truy tìm tập hợp con của các hàng trong một hoặc nhiều bảng), dự thảo {Projections} (truy tìm tập hợp con của các cột trong một hoặc nhiều bảng), liên kết {Joint}(liên kết hàng trong hai hoặc nhiều bảng để truy tìm dữ liệu bảng chéo). ắ Từ định danh (identifiers) Mỗi một đối t−ợng trong cơ sở dữ liệu có một tên, để làm việc với đối t−ợng bảng, bạn phải xác định (định danh) nó bởi tên. Trong Transact- SQL, bạn phải định danh để xác định tên của đối t−ợng bạn muốn làm việc. Từ định danh có từ 1 đến 30 kí tự. Kí tự đầu tiên có thể là kí tự bảng chữ cái hoặc biểu t−ợng @, _, #, ắ Chọn cơ sở dữ liệu Toàn bộ các đối t−ợng trong Microsoft SQL Server l−u trữ trong cơ sở dữ liệu. Toàn bộ tham chiếu tới đối t−ợng SQL Server có đ−ợc giải quyết để xác định cơ sở dữ liệu trong đó mà nó th−ờng trú. ắ Cú pháp của câu lệnh SELECT Cú pháp đầy đủ của câu lệnh SELECT là phức tạp, nh−ng mệnh đề chính có thể tổng kết: SELECT select_list [INTO new_table_name] FROM table_list [WHERE search_conditions] [GROUP BY group_by_list] [HAVING search_conditions] [ORDER BY order_list [ASC | DESC] ] select_list - 32 -
  33. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Mô tả các cột của tập kết quả, mỗi tr−ờng (field) có dấu phảy ngăn cách danh sách của biểu thức. Mỗi biểu thức xác định cả hai định dạng (kích th−ớc và kiểu dữ liệu) và nguồn của dữ liệu cho tập kết quả cột. Mỗi một lựa chọn danh sách biểu thức th−ờng tham chiếu tới cột trong bảng nguồn. Transact-SQL sử dụng biểu thức * trong lựa chọn danh sách xác định toàn bộ cột trong bảng nguồn. Ví dụ: SELECT * FROM Congdan 0INTO new_table_name Chỉ định tập kết quả sử dụng để tạo bảng mới, new_table_name chỉ định tên của bảng mới. Ví dụ: SELECT * INTO Congdan1 FROM Congdan FROM table_list Chứa đựng danh sách của bảng từ đó kết quả tập dữ liệu là đ−ợc truy vấn. Nguồn này có thể là: • Các bảng cơ bản trong Server cục bộ chạy Microsoft SQL Server. • Các khung nhìn trong SQL Server cục bộ. • Liên kết bảng trong dữ liệu nguồn OLE DB tạo ra có thể gần với SQL Server. Ví dụ: SELECT [Họ và tên], [Tuổi], [Trình độ] FROM Congdan WHERE search_conditions Trong thành phần WHERE các dòng phải đ−ợc cung cấp cho kết quả cuối cùng đ−ợc xác định trong điều kiện. Ví dụ: SELECT [Họ và tên], [Tuổi], [Trình độ] FROM Congdan WHERE [Trình độ] = ‘Đại Học’ Các điều kiện đơn: y So sánh đơn. y Điều kiện kép với AND, OR, NOT. y Toán tử BETWEEN. y Toán tử IN. y Toán tử LIKE. y Toán tử NULL. - 33 -
  34. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Toán tử IN với truy vấn phụ. y Toán tử so sánh với truy vấn phụ. y Toán tử ANY và ALL. y Toán tử EXIST. GROUP BY group_by_list Thành phần GRUOP BY đ−ợc sử dụng để nhóm các dòng của một kết quả tạm. HAVING search_conditions Thành phần HAVING lọc những nhóm không thoả mãn điều kiện truy vấn dữ liệu. ORDER BY order_list [ ASC | DESC ] Mệnh đề ORDER BY xác định trật tự hàng trong tập kết quả có trật tự. Order_list xác định cột kết quả tạo nên danh sách sắp xếp. Từ khoá ASC và DESC là sử dụng để xác định hàng là sắp xếp tăng hoặc giảm ắ Giả sử rằng chúng ta có bảng Nhansu và Tienluong dùng để áp dụng trong truy vấn dữ liệu theo cú pháp câu lênh SELECT FROM: - 34 -
  35. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Sử dụng thao tác so sánh đơn để tìm những ng−ời có độ tuổi từ 22 đến 25 tuổi trong bảng Nhansu. ™ Dùng điều kiện kép với AND, OR, tìm ng−ời có tuổi lớn hơn 20 và có giới tính là nam. - 35 -
  36. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Toán tử BETWEEN có thể sử dụng với kiểu dữ liệu ký tự, kiểu chuỗi, bit hoặc kiểu datetime giống nh− đối với kiểu dữ liêu số học. Tìm những ng−ời có độ tuổi từ 23 đến 30 tuổi. ™ Sử dụng toán tử IN để tìm kiếm những ng−ời thoả mãn một trong độ tuổi là 23, 30, 20. Câu lệnh SELECT FROM WHERE IN (23, 30, 20) t−ơng đ−ơng với SELECT FROM WHERE Tuoi = 23 OR Tuoi = 30 OR Tuoi = 20 - 36 -
  37. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Toán tử LIKE có thể dùng các ký hiệu % thay cho một chuỗi ký tự, thay cho một ký tự, khi đó ng−ời ta gọi là ‘mặt nạ’. ™ Toán tử NULL tìm tất cả những hàng mà giá trị tại một cột quy định nào đó là NULL. - 37 -
  38. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Toán tử IN trong truy vấn con giống với các toán tử ANY và ALL. ™ So sánh với truy vấn con. - 38 -
  39. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Toán tử EXISTS ™ Thành phần GROUP BY nhóm các dòng, trong bảng kết quả truy vấn, tất cả những hàng mà có giá trị ở cột lập nhóm giống nhau sẽ đ−ợc gom chung lại thành một nhóm. - 39 -
  40. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ™ Mệnh đề HAVING quy định lọc để đặt thêm một hạn chế nữa đối với bảng kết quả vấn tin do mệnh đề GROUP BY tạo ra. ™ Mệnh đề ORDER BY để hiển thị bảng kết quả truy vấn theo thứ tự tăng dần hoặc giảm dần. - 40 -
  41. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II.2 Sử dụng liên kết để tạo nhiều truy vấn Các thao tác liên kết cho phép bạn truy lục dữ liệu từ hai hay nhiều bảng hoặc khung nhìn trong cơ sở dữ liệu hoặc trong cơ sở dữ liệu khác về thao tác đơn. ắ Thao tác liên kết Liên kết hai hoặc nhiều bảng xử lí so sánh dữ liệu xác định cột và sử dụng so sánh kết quả từ bảng mới tới các hàng chỉ định (that qualify). Câu lệnh liên kết: y Xác định cột cho mỗi bảng. y So sánh giá trị trong cột này bởi từng hàng. y Tổ hợp hàng với định tính chất giá trị vào trong hàng mới. ắ Lựa chọn danh sách trong liên kết Câu lệnh liên kết nh− câu lệnh lựa chọn khác, bắt đầu với từ khoá SELECT. Tên các cột sau từ khoá SELECT là các cột bao gồm trong kết quả truy vấn, trong trật tự mong muốn. ắ Mệnh đề FROM trong liên kết Mệnh đề FROM của câu lệnh liên kết tên toàn bộ các bảng hoặc các view bao gồm tại liên kết. FROM là mệnh đề mà thực sự chỉ ra tới SQL Server mà liên kết mong muốn. Trật tự danh sách bảng hoặc khung nhìn (view) trong mệnh đề FROM ảnh h−ởng tới hiển thị kết quả khi bạn sử dụng * trong lựa chọn danh sách. ắ Mệnh đề WHERE trong liên kết Mệnh đề WHERE xác định tập hợp giữa tên bảng hoặc view trong mệnh đề FROM và giới hạn các hàng đ−ợc tính đến trong kết quả. Nó đ−a ra tên của các cột đ−ợc liên kết và thao tác liên kết giống nh− là tính ngang bằng. ắ Mô hình quan hệ và các liên kết Thao tác liên kết xác nhận tiêu chuẩn mô hình quan hệ của hệ quản trị cơ sở dữ liệu. Trong cấu trúc cơ sở dữ liệu quản trị hệ thống (th−ờng biết đến nh− là mạng hoặc hệ thống có cấp bậc) mối quan hệ giữa dữ liệu là xác định tr−ớc. Một cơ sở dữ liệu vừa đ−ợc cài đặt, nó khó khăn thực hiện truy vấn về mối quan hệ không dự kiến tr−ớc giữa dữ liệu. ắ Liên kết đ−ợc xử lí nh− thế nào ? - 41 -
  42. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Điều kiện liên kết có thể xác định trong mệnh đề FROM hoặc WHERE, chỉ định chúng trong mệnh đề FROM là đ−ợc giới thiệu. Mệnh đề WHERE và HAVING có thể cũng chứa đựng điều kiện kiểm tra cho việc lựa chọn lọc các hàng bởi điều kiện liên kết. ắ Hợp có thể phân loại sau: y Hợp trong {Inner Joins} (thao tác liên kết đặc tr−ng mà sử dụng một vài thao tác so sánh nh− = hoặc <>). Điều này bao gồm liên kết ngang bằng và liên kết tự nhiên. Inner joins sử dụng thao tác so sánh các hàng thoả mãn từ hai bảng dựa trên giá trị cột chung từ mỗi bảng. y Hợp ngoài {Outer joins}có thể có hợp trái (left), phải (right), và hợp toàn phần. y Hợp chéo {Cross joins} y Một hợp chéo ghép nối toàn bộ các hàng của bảng thứ nhất với bảng thứ hai. Hợp này còn gọi là tích Cartesian. ™ Ví dụ về sử dụng liên kết trong truy vấn. II.3 Sử dụng nhóm các truy vấn ắ Bạn có thể nhóm dữ liệu bởi mệnh đề GROUP BY hoặc HAVING. GROUP BY tổ chức dữ liệu thành nhóm, HAVING thiết lập điều kiện - 42 -
  43. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT trên nhóm gồm có trong kết quả. Mệnh đề này th−ờng đ−ợc sử dụng với nhau. HAVING sử dụng không có GROUP BY có thể đ−a ra kết quả khó hiểu. ắ Bạn có thể sử dụng chức năng tổng quát với mệnh đề GROUP BY Chức năng tổng quát Kết quả (Result) (Aggregate function) SUM([ALL| DISTINCT] Tổng của giá trị trong biểu thức số. expression) AVG([ALL| DISTINCT] Giá trị trung bình trong biểu thức số. expression) COUNT([ALL | DISTINCT] Xác định giá trị xuất hiện trong cột hay expression) số dòng trong bảng. COUNT(*) Số các giá trị xuất hiện trong hàng. MAX(expression) Cho giá trị lớn nhất của biểu thức. MIN(expression) Cho giá trị nhỏ nhất của biểu thức. ™ Ví dụ sau cho ta thấy chức năng của một số hàm nh− MAX, MIN, - 43 -
  44. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y SUM, AVG, COUNT, MAX, và MIN bỏ qua giá trị Null, COUNT(*) không bỏ qua giá trị Null. y Từ khoá tự chọn DISTINCT có thể đ−ợc sử dụng với SUM, AVG, và COUNT để loại trừ giá trị đúp sau khi tập hợp chức năng áp dụng. y SUM và AVG có thể đ−ợc sử dụng duy nhất với cột số, ví dụ nh− loại int, smallint, tinyint, decimal, numeric, float, real, money, và smallmoney. y MIN and MAX không thể sử dụng với kiểu dữ liệu bits. Tập hợp chức năng khác nh− COUNT(*) không thể sử dụng với kiểu dữ liệu text and image. II.4 Sử dụng truy vấn con (subquery) ắ Truy vấn con là câu lệnh SELECT lồng trong câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc trong truy vấn con khác. Câu lệnh gồm có thao tác truy vấn con trên các hàng dựa vào đánh giá của câu lệnh truy vấn con SELECT. Câu lệnh truy vấn con có thể chuyển đến bảng khác bên ngoài truy vấn hoặc tới bảng khác. ắ Cú pháp truy vấn con Truy vấn con lồng trong câu lệnh SELECT có cú pháp: (SELECT [ALL|DISTINCT] subquery_select_list [ FROM {table_name | view_name}{optimizer_hints} [[,{table_name2 | view_name2}{optimizer_hints} [ ,{table_name16 | view_name16}{optimizer_hints}]]] [WHERE clause] [GROUP BY clause] [HAVING clause] Câu lệnh SELECT của truy vấn con luôn luôn chứa trong ngoặc đơn. Nó không thể có mệnh đề ODER BY, COMPUTE hoặc BROWSE. Truy vấn con có thể lồng trong mệnh đề WHERE hoặc HAVING của bên ngoài câu lệnh SELECT, INSERT, UPDATE hoặc DELETE. Không có sự giới hạn trên cấp độ lặp. Một truy vấn con có thể xuất hiện ở bất kỳ biểu thức có thể đ−ợc sử dụng với điều kiện là nó trở về giá trị đơn. Bạn không thể sử dụng truy vấn con trong danh sách ORDER BY. ắHiển thị toàn bộ với GROUP BY hoặc COMPUTE - 44 -
  45. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Bạn có thể hiển thị toàn bộ bởi tập hợp chức năng và mệnh đề GROUP BY hoặc hàng tập hợp chức năng và mệnh đề COMPUTE. Tập hợp chức năng SUM, AVG, COUNT, MAX và MIN tổng kết giá trị xuất hiện nh− cột mới trong kết quả truy vấn. Mệnh đề GROUP BY, phần của câu lệnh SELECT, chia bảng thành nhóm. GROUP BY và tập hợp chức năng th−ờng sử dụng với nhau, và tổng giá trị là tính toán cho mỗi nhóm. ™ Ví dụ sử dụng truy vấn con. II.5 Sử dụng chức năng lập sẵn (using built-in functions) SQL Server cung cấp đủ loại các hàm chức năng lập sẵn giúp bạn thực hiện thao tác nào đó nhanh chóng và dễ dàng. Chức năng đ−ợc chia thành các loại sau: y Chức năng hệ thống. y Chức năng chuỗi cho thao tác giá trị char, varchar, binary và varbinary. y Chức năng text và image cho thao tác giá trị text và image. y Chức năng số học. y Chức năng date (ngày tháng) cho vận dụng giá trị datetime và smalldatetime. - 45 -
  46. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Chức năng chuyển đổi (convert), cho phép chuyển đổi biểu thức từ một kiểu dữ liệu tới dạng khác và định dạng date. ắ Chức năng hệ thống Chức năng hệ thống cho phép bạn truy cập thông tin từ bảng hệ thống Microsoft SQL Server mà không truy cập bảng hệ thống trực tiếp. Nhóm này gồm năm cặp chức năng hệ thống cho Database (cơ sở dữ liệu), Server (máy chủ), Object (đối t−ợng), Login (đăng nhập) và User (ng−ời sử dụng) trả về tên khi dựa vào ID (chỉ danh) và khai báo ID dựa vào tên: y DB_ID and DB_NAME y HOST_ID and HOST_NAME y OBJECT_ID and OBJECT_NAME y SUSER_ID and SUSER_NAME (or SUSER_SID and SUSER_SNAME) y USER_ID and USER_NAME Chức năng này cung cấp dễ dàng cách chuyển đổi tên tới một ID hoặc một ID tới tên. ắ Chức năng chuỗi kí tự Chức năng chuỗi kí tự sử dụng cho hoạt động khác nhau trên kí tự, chuỗi nhị phân chúng trả lại giá trị chung nhất cần cho thao tác trên kí tự dữ liệu. Phần lớn chức năng chuỗi có thể sử dụng trên kiểu dữ liệu char, nchar, nvarchar hoặc kiểu dữ liệu mà hoàn toàn chuyển đổi chúng. Một vài chức năng chuỗi có thể cũng sử dụng dữ liệu binary hoặc varbinary. Bạn có thể sử dụng chức năng để: y Truy vấn một phần của chuỗi. y Tìm kiếm đồng dạng trong chuỗi kí tự. y Tìm kiếm bắt đầu vị trí chuỗi đặc biệt trong cột hoặc biểu thức. y Ràng buộc vào nhau hoặc tổ hợp chuỗi thành một chuỗi. Ví dụ nh− tên, họ, và đệm thành tên đầy đủ. y Chuyển đổi giá trị không phải là chuỗi thành giá trị chuỗi. y Chèn thêm chuỗi vào trong chuỗi hiện hành. Ví dụ nh− chèn xâu “Once” vào trong xâu “upon a time” thành sâu kết quả “ Once upon a time”. - 46 -
  47. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Chức năng văn bản và hình ảnh (text & image) Chức năng văn bản (text) sử dụng thao tác trên dữ liệu văn bản và hình ảnh. Quá trình chuyển đổi sử dụng chức năng CONVERT hỗ trợ từ text tới varchar và từ image tới varbinary hoặc binary, nh−ng text và image rút gọn tới 255 bytes. ắ Chức năng số học Chức năng số học thực hiện thao tác số học trên biểu thức số và trở lại kết quả của thao tác. Chức năng số học thao tác trên hệ thống Microsoft SQL Server cung cấp dữ liệu số (decimal, integer, float, real, money, smallmoney, smallint, and tinyint). Độ chính xác xây dựng thao tác trên loại dữ liệu động là đặt sáu vị trí thập phân bởi mặc định. Sau đây là một số hàm toán học: ABS, LOG10, ACOS, PI, ASIN, POWER, ATAN, RADIANS, ATN2, RAND, CEILING, ROUND, COS, SIGN, COT, SIN, DEGREES, SQUARE, EXP, SQRT, FLOOR,TAN, LOG. ắ Chức năng ngày tháng (Date Functions) Chức năng ngày tháng hiển thị thông tin về ngày, tháng và giờ. Chức năng sử dụng giá trị datetime và smalldatetime, thực hiện thao tác số học trên đó. Chức năng ngày tháng có thể đ−ợc sử dụng ở bất kỳ biểu thức nào có thể sử dụng. SQL Server thừa nhận độ rộng khác nhau của dữ liệu datetime đ−a vào định dạng. Bạn có thể sử dụng câu lệnh SET DATEFORMAT để thiết lập trật tự của phần ngày tháng để đ−a vào dữ liệu datetime hoặc smalldatetime. ắ Chức năng chuyển đổi Chức năng chuyển đổi CAST và CONVERT để chuyển đổi biểu thức của một loại dữ liệu tới kiểu dữ liệu khác bất cứ lúc nào biểu thức này cũng không thực hiện tự động bởi Microsoft SQL Server. Chức năng chuyển đổi cũng sử dụng để đạt đ−ợc trạng thái khác nhau của định dạng dữ liệu đặc biệt. Chức năng chuyển đổi cũng có thể đ−ợc sử dụng trong danh sách lựa chọn, trong mệnh đề WHERE và bất cứ chỗ nào biểu thức cho phép. Khi sử dụng cả hai phần CAST hoặc CONVERT của thông tin yêu cầu: y Kiểu thức chuyển đổi (ví dụ, báo cáo bán hàng yêu cầu dữ liệu bán hàng đ−ợc chuyển đổi từ dữ liệu tiền tệ thành dữ liệu ký tự) y Kiểu dữ liệu chuyển đổi cũng dựa vào biểu thức. - 47 -
  48. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT - 48 -
  49. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng IV bảo mật trong SQL Server I. Các loại bảo mật I.1 Bảo mật Bạn có thể giới hạn truy cập tới dữ liệu sử dụng bởi dịch vụ Microsoft SQL Server OLAP qua tính năng bảo mật. Sử dụng tính năng bảo mật, bạn có thể điều khiển ng−ời nào đ−ợc truy cập dữ liệu và kiểu thao tác mà họ có thể thực hiện với dữ liệu. OLAP Services hỗ trợ tích hợp hệ thống bảo mật Microsoft Windows NT và cho phép bạn thực hiện truy cập cơ sở dữ liệu và mức luỹ thừa 3 (cube level). I.2 Bảo mật dữ liệu Một trong những chức năng của cơ sở dữ liệu là bảo vệ dữ liệu bằng cách ngăn cản ng−ời sử dụng nào đó xem xét hoặc thay đổi dữ liệu nhạy cảm cao và ngăn cản toàn bộ ng−ời sử dụng tạo ra lỗi. Hệ thống bảo mật trong Microsoft SQL Server điều khiển ng−ời sử dụng có thể làm việc với dữ liệu và ng−ời sử dụng đó có thể thực hiện hoạt động trong cơ sở dữ liệu. I.3 Quản lý bảo mật Để đảm bảo dữ liệu và đối t−ợng l−u trong Microsoft SQL Server là đ−ợc truy cập bởi uỷ quyền của ng−ời sử dụng, bảo mật phải thiết lập thích hợp. Sự hiểu biết về bảo mật thích hợp giúp bạn đơn giản trong quản lý. Yếu tố bảo mật mà có thể có thiết lập gồm có chế độ xác thực, đăng nhập, ng−ời sử dụng, gán quyền, t−ớc quyền và bảo mật. I.4 Cấu trúc bảo mật (Security Architecture) Cấu trúc của hệ thống bảo mật dựa trên ng−ời dùng và nhóm ng−ời dùng, đ−ợc nhắc đến nh− là bảo mật chính (Security Principals). Sự minh hoạ này chỉ ra ng−ời dùng và nhóm ng−ời sử dụng cục bộ, toàn cục trong Microsoft Windows NT có thể ánh xạ sự tính toán bảo mật trong Microsoft SQL Server và SQL Server có thể quản lý sự tính toán bảo mật không phụ thuộc vào sự tính toán trong Windows NT. SQL Server cũng cung cấp bảo mật với mức ứng dụng qua sự sử dụng riêng biệt cơ sở dữ liệu ứng dụng roles. - 49 -
  50. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT I.5 Quản lý bảo mật có cấp bậc (Hierarchical Security Management) Môi tr−ờng bảo mật trong Microsoft Windows NT và Microsoft SQL Server đ−ợc l−u trữ, quản lý và có hiệu lực qua hệ thống có cấp bậc của ng−ời sử dụng. Để đơn giản hoá quản trị nhiều ng−ời sử dụng, Windows NT và SQL Server sử dụng các nhóm (groups). Cài đặt bảo mật xác định cho một nhóm đ−ợc cung cấp cho toàn bộ thành viên của nhóm. Hệ thống có cấp bậc của nhóm bảo mật đơn giản hoá quản lý thiết lập bảo mật. Nó cho phép thiết lập bảo mật tới ứng dụng chung cho toàn bộ nhóm thành viên, không xác định d− thừa không cần thiết cho mỗi ng−ời. Mô hình có cấp bậc cũng cung cấp thiết lập bảo mật áp dụng tốt nhất tới ng−ời sử dụng đơn. I.6 Bảo mật cho server từ xa Thiết lập bảo mật thực hiện lời gọi thủ tục l−u trữ từ xa (Remote Procedure Call) dựa vào Server từ xa gồm thiết lập biểu đồ phản xạ trong Server từ xa thực hiện Microsoft SQL Server và có thể trên Server cục bộ (local). ắ Thiết lập Server từ xa (Setting Up the Remote Server) ánh xạ đăng nhập từ xa cần đ−ợc cài đặt dựa trên Server từ xa. Sử dụng ánh xạ đó, Server từ xa đăng nhập bởi kết nối lời gọi thủ tục từ xa căn cứ vào Server để đăng nhập cục bộ (local). ánh xạ đăng nhập từ xa có thể đ−ợc cài đặt sử dụng thủ tục sp_addremotelogin l−u trên Server từ xa. ắ Thiết lập Server cục bộ (Setting Up the Local Server) Trong SQL Server 7.0, tên ng−ời sử dụng đăng nhập từ bên ngoài tới Microsoft Windows NT xác thực đăng nhập khác nhau từ giải phóng tr−ớc đó. Vì thế các b−ớc sau đây cần đ−ợc thực hiện tạo ra sự kết nối Server từ xa với đăng nhập xác thực cho Windows NT. Sự sắp xếp đăng nhập cục bộ có thể thiết lập dựa trên Server cục bộ mà xác định đăng nhập và mật khẩu nào sẽ đ−ợc sử dụng bởi SQL Server khi nó tạo ra một kết nối lời gọi thủ tục từ xa tới Server từ xa. Để đăng nhập xác thực Windows NT, nó không cần thiết tạo ra bất kỳ ánh xạ cục bộ cho việc thực hiện lời gọi thủ tục l−u trữ dựa trên Server từ xa. Sử dụng thủ tục l−u trữ sp_addlinkedsrvlogin tạo ra ánh xạ đăng nhập cục bộ. I.7 SQL Server thực hiện bảo mật Ng−ời dùng v−ợt qua 2 giai đoạn của bảo mật khi làm việc trên SQL Server: Sự xác nhận là đúng và sự cho phép hiệu lực. Xác thực giai đoạn nhận biết mà ng−ời dùng sử dụng kê khai đăng nhập và xác nhận khả - 50 -
  51. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT năng để kết nối với SQL server. Nếu sự xác nhận là đúng là thành công thì ng−ời dùng có thể kết nối với SQL Server. Ng−ời dùng cũng cần sự cho phép để truy cập cơ sở dữ liệu trên Server, nó thực hiện bằng việc sử dụng giá trị trên mỗi cơ sở dữ liệu, ánh xạ tới đăng nhập của ng−ời sử dụng. Giai đoạn cho phép hiệu lực điều khiển hoạt động ng−ời dùng phải tuân theo để thực hiện trong cơ sở dữ liệu SQL Server. II. Sự tích hợp với Windows NT II.1 Thiết lập chế độ bảo mật của Windows NT (Enterprise Manager) 1. Mở rộng Server group. 2. Nhấp phải Server, rồi chọn Properties. 3. Nhấp Security. 4. Chọn Windows NT only. 5. Trong Audit level, chọn mức mà ng−ời sử dụng truy cập Microsoft SQL Server. - 51 -
  52. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT II.2 Sự xác thực (Authentication) Microsoft SQL Server có thể thao tác một trong hai chế độ bảo mật sau: 1. Chế độ xác thực Windows NT (Windows NT Authentication). 2. Chế độ hỗn hợp (Windows NT Authentication và SQL Server Authentication). Chế độ hỗn hợp cho phép ng−ời sử dụng kết nối sử dụng Windows NT Authentication hoặc SQL Server Authentication. Ng−ời sử dụng kết nối qua Microsoft Windows NT sử dụng tính toán có thể tạo ra sử dụng kết nối tin cậy (sự kết nối có hiệu lực bởi Windows NT) trong chế độ hỗn hợp và chế độ xác thực Windows NT. Sau khi kết nối thành công tới SQL Server, kỹ thuật bảo mật giống nh− cả hai chế độ trên. Hệ thống bảo mật dựa trên đăng nhập và mật khẩu SQL Server (SQL Server Authentication) có thể dễ dàng quản lý hơn hệ thống bảo mật dựa trên Windows NT, đặc biệt cho cơ sở dữ liệu mà không cần nhiệm vụ tới hạn, các ứng dụng không nhạy cảm và thông tin mật. ắ Xác thực Windows NT • Khi ng−ời sử dụng kết nối qua Windows NT, SQL Server xác nhận rằng tên account và passwod là có hiệu lực khi ng−ời sử dụng nhập vào hệ Windows NT hoặc Microsoft Windows 95/98. y SQL Server thực hiện đăng nhập (login) tích hợp bảo mật với Windows NT bởi sử dụng thuộc tính bảo mật của ng−ời sử dụng mạng kiểm soát truy cập đăng nhập. y Sự tích hợp bảo mật đăng nhập điều khiển trên giao thức mạng hỗ trợ xác thực kết nối giữa máy chạm (Client) và máy chủ (Server). Nh− là sự kết nối dựa vào kết nối thực và hỗ trợ bởi SQL Server sử dụng th− viện mạng có sẵn. y Xác thực Windows NT có chứa lợi ích trên SQL Server tr−ớc hết nó tích hợp với hệ thống bảo mật Windows NT. Hệ thống bảo mật Windows NT cung cấp nhiều đặc điểm, nh− đảm bảo hiệu lực, mã hoá mật khẩu, bởi vì nhóm sử dụng Windows NT duy trì bởi Windows NT, SQL Server đọc các thông tin về thành viên trong nhóm khi ng−ời sử dụng kết nối. Nếu thay đổi này tạo ra quyền truy cập của kết nối sử dụng, thay đổi trở nên hiệu lực ở lần kết nối tiếp theo tới SQL Server hoặc nhập vào hệ Windows NT. - 52 -
  53. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Sự uỷ quyền bảo mật account (Security Account Delegation) Sự uỷ quyền bảo mật account cho phép máy trạm (Client) kết nối tới một Server sử dụng Windows NT Authentication để truy cập Server khác sử dụng lời gọi thủ tục từ xa (RPC) với uỷ quyền bảo mật của họ, đúng hơn sự uỷ quyền bảo mật của Server lúc đầu hoặc xác định thiết lập uỷ quyền bảo mật trên Server đầu tiên. ắ SQL Server Authentication y Khi ng−ời sử dụng kết nối với tên đăng nhập xác định mật khẩu từ sự kết nối không tin cậy, SQL Server thực hiện chính sự nhận thức bởi kiểm tra xem xét nếu SQL Server đăng nhập account vừa cài đặt và nếu thoả mật khẩu xác định một bản ghi (record) tr−ớc đây. y SQL Server Authentication cung cấp tính t−ơng hợp lùi lại bởi vì ứng dụng viết cho phiên bản gần đây của SQL Server có thể yêu cầu sử dụng mật khẩu và đăng nhập SQL Server. Hơn thế SQL Server Authentication yêu cầu khi SQL Server chạy trên Windows 95/98 bởi vì chế độ xác thực Windows NT là không hỗ trợ trên Windows 95/98. Vì thế SQL Server sử dụng chế độ hỗn hợp khi chạy trên Windows 95/98 (nh−ng hỗ trợ duy nhất với SQL Server Authentication). y Phát triển ứng dụng và cơ sở dữ liệu ng−ời sử dụng có thể đ−a ra SQL Server bởi vì chúng quen thuộc với chức năng mật khẩu và đăng nhập. SQL Server Authentication cũng yêu cầu cho sự kết nối với internet và máy trạm (Client). - 53 -
  54. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT - 54 -
  55. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng V khả năng liên kết với các ngôn ngữ lập trình I. Liên kết với C, C++, Visual C++ 1. Ch−ơng trình nhúng SQL cho ngôn ngữ lâp trình C. Microsoft nhúng SQL cho ngôn ngữ lâp trình C (ESQL/C) đề nghị ng−ời lập trình thay phiên nhau viết ứng dụng máy trạm Microsoft SQL Server với th− viện cơ sở dữ liệu (DB-Library) cho ngôn ngữ lâp trình C hoặc hệ thống kết nối cơ sở dữ liệu mở (ODBC) giao diện ch−ơng trình ứng dụng. ESQL/C có khả năng giúp bạn kết hợp chặt trẽ câu lênh Transact-SQL vào trong ch−ơng trình ngôn ngữ C của bạn. ESQL/C chủ yếu sử dụng chuyển mang ứng dụng hiện tại của bạn từ cơ sở dữ liệu khác tới SQL Server. 2. Xử lý câu lệnh nhúng SQL(Processing Embedded SQL Statements) Nhúng SQL cho ngôn ngữ lập trình C hỗ trợ hoàn toàn phần mở rộng Transact-SQL, gồm có thủ tục l−u trữ, biến cục bộ, trình điều khiển dòng ngôn ngữ. Nhờ có cú pháp xung đột với nhúng SQL dành riêng các từ khoá, bởi vì câu lệnh SQL biên dịch vào trong thủ tục l−u trữ bởi tiền biên dịch, hạn chế thứ yếu cung cấp phần mở rộng Transact-SQL là đ−ợc thực thi. Có các hạn chế sau: y Câu lệnh Transact-SQL EXECUTE nên viết tắt nh− EXEC để tránh xung đột với nhúng SQL EXECUTE. y Nhãn câu lênh Transact-SQL không nên sử dụng trong câu lệnh SQL tĩnh bởi vì chúng xung đột với cú pháp biến chính. Tuy nhiên bạn có thể sử dụng nhãn trong câu lệnh Transact-SQL động. y Bởi vì nsqlrep chuyển đổi toàn bộ câu lệnh SQL tĩnh vào thủ tục l−u trữ, toàn bộ giới hạn cho Transact-SQL cung cấp thủ tục l−u tới câu lệnh SQL tĩnh. Câu lệnh SQL tĩnh chứa câu lệnh quản lý giao dich (nh− COMMIT TRANSACTION hoặc SAVEPOINT) là không biên dich vào trong thủ tục l−u trữ. 3. Ch−ơng trình th− viện cơ sở dữ liệu cho ngôn ngữ C (DB-library) Microsoft SQL Server là cấu trúc mạnh của ngôn ngữ truy vấn cơ sở dữ liệu Server. Với phần mềm mạng cục bộ (LAN), SQL Server cho phép - 55 -
  56. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Client chạy Microsoft Windows NT, các hệ điều hành Microsoft Windows hoặc Microsoft MS-DOS truy cập tới dịch vụ. Ng−ời phát triển sẽ viết các ứng dụng cho SQL Server bằng ch−ơng trình với DB-library cho ngôn ngữ lâp trình C, phiên bản ngôn ngữ C thông báo th− viện cho SQL Server. DB-Library cho ngôn ngữ C giao diện ch−ơng tình ứng dụng (API) gồm có chức năng C và macros mà cho phép một ứng dụng giao tiếp với SQL Server. Chức năng gồm có gửi câu lệnh Transact-SQL tới SQL Server và chức năng xử lý kết quả của câu lệnh đó. Các chức năng khác về điều khiển lỗi và chuyển đổi dữ liệu. DB-Library C có chức năng sau: y Hệ thống kết nối mở. y Tạo khuôn thức truy vấn. y Gửi khối truy vấn tới Server và nhận lại kết quả. y Điều khiển hai phần cam kết thao tác giữa một vài thao tác SQL Server. y Sử dụng con trỏ có thể cuộn. y Bulk-copying dữ liệu từ các file hoặc biến ch−ơng trình tới Server. y Thực hiện thủ tục l−u trữ và thủ tục l−u trữ từ xa. DB-Library hỗ trợ môi tr−ờng đa nhiệm, lập trình viên C có thể chọn ph−ơng thức sau: • Nhiều kỹ thuật luồng th− viện liên kết động (DLL) cho hệ điều hành Windows NT. • Mô hình vừa và lớn với th− viện liên kết tĩnh cho MS-DOS, cho cả ch−ơng trình biên dịch Microsoft và Borland. 4. Yêu cầu hệ thống cho việc nhúng SQL trong C Sử dụng ESQL/C, bạn có thể biên dịch và chạy các ứng dụng trên các hệ điều hành khác nhau. ắ Window NT Trong Microsoft SQL Server phiên bản 7.0, ESQL/C đ−ợc hỗ trợ bởi cả Intel@ và Alpha đặt trên nền chuẩn Microsoft Windows NT. Những đặc tr−ng của hệ thống đòi hỏi : y Microsolft Windows NT Workstation phiên bản 3.51 trở lên hoặc Microsoft Windows NT Server phiên bản 3.5 về sau. - 56 -
  57. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Microsoft Visual C++ phát triển trên hệ thống 32 bit, phiên bản 2.0 hoặc phiên bản về sau (phiên bản 5.0 là đ−ợc giới thiệu) hoặc 100% t−ơng thích với ch−ơng trình biên dịch và liên kết. y Microsoft SQL Server 6.0 hoặc phiên bản sau này, file Ntwdblib.dll cần thiết ở phiên bản 6.0 hoặc phiên bản sau. Th− viện này đ−ợc cài đặt trong hệ thống của bạn d−ới \Devtools\lib. ắ Windows 95/98 • Microsoft Windows 95/98. y Microsoft Visual C++ phát triển trên hệ thống 32 bit, phiên bản 2.0 hoặc phiên bản về sau (phiên bản 5.0 là đ−ợc giới thiệu) hoặc 100% t−ơng thích với ch−ơng trình biên dịch và liên kết. y Microsoft SQL Server 6.0 hoặc phiên bản sau này, file Ntwdblib.dll cần thiết ở phiên bản 6.0 hoặc phiên bản sau. ắ Windows y Ch−ơng trình biên dịch và th− viện ESQL/C 16-bit có sẵn cho sử dụng với SQL Server 7.0 nh−ng không đ−ợc hỗ trợ. Ch−ơng trình biên dịch và th− viện có thể copy từ đĩa compact SQL Server. Hệ thống yêu cầu chạy ESQL/C d−ới Windows 16-bit là: y Microsoft Windows phiên bản 3.1 hoặc phiên bản sau, hoặc Microsoft Windows for Workgroups phiên bản 3.11 hoặc phiên bản sau. y Microsoft Visual C++ phát triển trên hệ thống 32 bit, phiên bản 5.0 (phiên bản nhỏ nhất là 2.0), hoặc 100% t−ơng thích với ch−ơng trình biên dịch và liên kết. y Microsoft SQL Server 6.0 hoặc phiên bản sau này, file Ntwdblib.dll cần thiết ở phiên bản 6.0 hoặc phiên bản sau. ắ MS-DOS y Ch−ơng trình biên dịch và th− viện ESQL/C 16-bit có sẵn sử dụng với SQL Server 7.0 nh−ng không đ−ợc hỗ trợ. Ch−ơng trình biên dịch và th− viện có thể copy từ đĩa compact SQL Server. Hệ thống yêu cầu chạy ESQL/C d−ới Microsoft MS-DOS 16-bit là: y Microsoft MS-DOS phiên bản 6.22 hoặc phiên bản sau. y Microsoft Visual C++ phát triển trên hệ thống 16 bit, phiên bản 1.52 hoặc phiên bản sau, hoặc 100% t−ơng thích với ch−ơng trình biên dịch và liên kết. - 57 -
  58. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT y Microsoft SQL Server 6.0 hoặc phiên bản sau. II. Liên kết với Visual Basic Với Visual Basic cũng có khả năng nhúng SQL t−ơng tự đối với ngôn ngữ lập trình C hoặc Visual C. - 58 -
  59. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Ch−ơng VI Xây dựng thiết kế ch−ơng trình I. Mô hình Client/Server I.1 Mô hình Client/Server lý thuyết Ngày nay mô hình Client/Server đ−ợc sử dụng rất rộng rãi vì cấu trúc của mô hình này rất phù hợp với cấu trúc của nhiều ngành nghề có thể kể ra nh− ngân hàng, b−u điện, năng l−ợng, ắ Kiến trúc nguyên lý gồm các hệ thống: • Máy chủ (Server), phần mềm máy chủ. • Máy khách (Client), phần mềm trên máy khách. • Hệ thống mạng LAN, WAN, và các phần mềm quản trị mạng. ắ Hoạt động của ứng dụng Client/Server nh− sau: Phần mềm trên Server nhận những yêu cầu xử lý dữ liệu từ máy Client thông qua một kết nối giữa máy Server và Client kể trên. Máy Server nhận đ−ợc yêu cầu trên của Client sẽ tiến hành sử lý dữ liệu dựa trên hệ thống cơ sở dữ liệu trên Server này và trả về cho máy Client kết quả xử lý và các lỗi nếu có. Client Server Procesed SQL Request Application Logic Application Logic Presentation Logic Processed DBMS Result Applications logic: giao diện với ng−ời sử dụng. Presentations Logic: xử lý t−ơng tác của ng−ời dùng. DBMS: Hệ quản trị cơ sở dữ liệu đặt trên Server. - 59 -
  60. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Processed SQL request: tiến trình đ−a ra yêu cầu xử lý dữ liệu theo dạng lệnh SQL Server. Processed result: tiến trình trả về dữ liệu cho Client từ Server. ắ Mô hình này mang lại nhiều lợi ích: • Tiện lợi, linh hoạt: khi phải nâng cấp phần mềm cho Client hoặc Server thì ít ảnh h−ởng đến phần còn lại. • Bảo mật, an toàn dữ liệu. • Xây dựng phân tán nh−ng điều khiển tập trung. • Tiết kiệm hơn so với các ứng dụng dựa trên Mainframe. • Tính mở cao. I.2 áp dụng lý thuyết vào ch−ơng trình quản lý dân c− Dựa trên những cơ sở lý thuyết đã trình bày ở trên ta áp dụng vào quản lý dân c−. Hệ thống đ−ợc cài đặt theo mô hình Client/Server bao gồm: ắ Máy Client • Hệ điều hành Windows 95/98 hoặc Windows 2000. • Công cụ giao diện ng−ời sử dụng và kết nối tới Server: SQL Server. • Kết nối thông qua hệ thống chuẩn ODBC của hãng Microsoft. ắ Máy Server • Hệ điều hành Windows NT Server 4.0 hoặc phiên bản cao hơn. • Hệ quản trị cơ sở dữ liệu: Microsoft SQL Server 7.0 ắMạng: hệ thống mạng LAN,WAN, II. Một số vấn đề về chuẩn hoá dữ liệu II.1 Định nghĩa phụ thuộc hàm ắ Khi tiến hành xây dựng thiết kế một cơ sở dữ liệu cho bài toán có các vấn đề nảy sinh: • D− thừa dữ liệu (Redundancy): một thông tin đ−ợc l−u trữ ở nhiều nơi trong cơ sở dữ liệu. - 60 -
  61. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Không nhất quán (Inconsistency): là hệ quả của việc d− thừa dữ liệu, là do khi cập nhật dữ liệu đã không đề cập nhập hết các dữ liệu d− thừa. • Dị th−ờng khi thêm bộ (Insertion anomalies): xảy ra khi thêm một bộ mà các giá trị t−ơng ứng với các thuộc tính không đủ. • Dị th−ờng khi xoá bộ (Deletion anomalies): xảy ra khi xoá một bộ vào một quan hệ. Các vấn đề trên cho thấy cần có một giải pháp để khắc phục đ−ợc những nh−ợc điểm trên. đó là ph−ơng pháp chuẩn hoá các bảng dữ liệu. ắ Để tìm hiểu nội dung của ph−ơng pháp chuẩn hoá chúng ta xem xét một số khái niệm sau đây về phụ thuộc hàm: Định nghĩa: Cho R(U) là một l−ợc đồ quan hệ với U là tập các thuộc tính. X,Y là tập con của U. Ta nói rằng X -> Y (đọc là X xác định hàm Y hoặc Y phụ thuộc hàm vào X). Nếu r là một quan hệ xác định trên R(U) sao cho bất kỳ hai bộ T1, T1∈ r mà T1[X] = T2[X] thì T1[Y] = T2[Y] Cần chú ý rằng chỉ xét các phụ thuộc hàm thoả mãn mọi quan hệ trên l−ợc đồ quan hệ t−ơng ứng của nó chứ không xét phụ thuộc hàm thoả mãn một quan hệ đặc biệt nào. II.2 Các dạng chuẩn Việc chuẩn hoá các quan hệ cũng nh− các sơ đồ quan hệ đóng vai trò cực kỳ quan trong trong việc thiết kế các hệ quản trị cơ sở dữ liệu trên mô hình dữ liệu. Nhờ có các chuẩn hoá các quan hệ và các sơ đồ quan hệ chúng ta tránh đ−ợc việc d− thừa dữ liệu và tăng tốc độ của các phép toán xử lí quan hệ. ắ Dạng chuẩn 1 (1NF) Định nghĩa 1NF: một l−ợc đồ quan hệ R đ−ợc gọi là ở dạng chuẩn một (1NF) nếu và chỉ nếu toàn bộ các miền có mặt trong R đều chỉ chứa giá trị nguyên tố. ắ Dạng chuẩn 2 (2NF) Tr−ớc khi đ−a ra định nghĩa cần xem xét tới khái niệm về phụ thuộc hàm đầy đủ: Cho l−ợc đồ quan hệ R(U) trên tập thuộc tính U = {A1, Ak}. X, Y là hai tập thuộc tính khác nhau nh−ng đều là tập con của U. Y là phụ - 61 -
  62. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT thuộc hàm đầy đủ vào X nếu Y là phụ thuộc hàm vào X nh−ng không phụ thuộc hàm vào bất kỳ một tập hợp con thực sự nào của X. Định nghĩa 2NF: L−ợc đồ quan hệ R ở dạng chuẩn thứ hai nếu nó ở dạng chuẩn thứ nhất và mỗi thuộc tính không khoá của R là phụ thuộc hàm đầy đủ vào khoá chính. ắ Dạng chuẩn 3 (3NF) Định nghĩa 3NF: l−ợc đồ quan hệ R ở dạng chuẩn thứ ba nếu nó ở dạng chuẩn thứ hai và nếu mỗi thuộc tính không khoá của R là không phụ thuộc hàm bắc cầu vào khoá chính. Trên đây là ba dạng chuẩn điển hình, ngoài ra còn có một số chuẩn khác nh− không nêu ra ở đây. Các bảng trong cơ sở dữ liệu ch−a chuẩn hoá thì ta có thể tiến hành phân rã các bảng này thành các bảng ở dạng chuẩn hoá. Trong thực tế không phải lúc nào dữ liệu d− thừa cũng không tốt, đôi khi ng−ời ta chấp nhận sự d− thừa này để tránh phải trả giá cho việc thêm phép kết nối từ xa tốn kém hơn nhiều. III. Thiết kế dữ liệu III.1 Thiết kế dữ liệu đầu vào Dữ liệu đầu vào của hệ thống quản lý dân c− bao gồm các thông tin sau: 1. Mã số công dân * 2. Tên khai sinh 3. Tên th−ờng gọi 4. Giới tính 5. Ngày sinh 6. Quốc tịch 7. Dân tộc 8. Tôn giáo 9. Quê quán 10. Số chứng minh th− 11. Nơi đăng ký hộ khẩu th−ờng trú 12. Số hộ khẩu 13. Loại hộ khẩu 14. Họ tên chủ hộ - 62 -
  63. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT 15. Quan hệ với chủ hộ 16. Ngày nhập khẩu 17. Lý do nhập khẩu 18. Chỗ ở hiện nay 19. Diện tích bình quân nhà ở 20. Các mã số đăng ký kết hôn 21. Các mã số giấy ly hôn 22. Các mã số khai tử 23. Loại hộ khẩu tai nơi ở hiện nay 24. Trình độ học vấn 25. Trình độ chuyên môn đào tạo 26. Ngành nghề đ−ợc đào tạo 27. Ngành nghề đang làm 28. Tình trạng việc làm (th−ờng xuyên, thất th−ờng, thất nghiêp) 29. Hình thức việc làm (tự do, biên chế, hợp đồng) 30. Có nhu cầu học nghề 31. Chế độ −u đãi đang h−ởng 32. Mã số các bản án 33. Các tội danh 34. Các loại tội phạm 35. Các hình phạt 36. Ngày kê khai/ cập nhật III.2 Dữ liệu đầu ra gồm - Hiển thị thông tin về dân c−. - Cập nhật, sửa chữa, - Tìm kiếm và tra cứu dữ liệu về dân c−. III.3 Cấu trúc các bảng ắ Bảng Congdan dùng l−u trữ hồ sơ công dân - 63 -
  64. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Stt Tên tr−ờng Kiểu dữ liệu Độ rộng 1 Mã số công dân * Char 10 2 Tên khai sinh Char 30 3 Tên th−ờng gọi Char 15 4 Giới tính Char 3 5 Ngày sinh Datetime 8 6 Quốc tịch Char 15 7 Dân tộc Char 15 8 Tôn giáo Char 10 9 Quê quán Varchar 50 10 Số chứng minh th− Numeric 9 11 Số hộ khẩu Numeric 9 12 Loại hộ khẩu Char 15 13 Nơi đăng ký hộ khẩu t. trú Varchar 50 14 Họ tên chủ hộ Char 32 15 Quan hệ với chủ hộ Char 25 16 Ngày nhập khẩu Datetime 8 17 Lý do nhập khẩu Char 35 18 Chỗ ở hiện nay Varchar 50 19 Diện tích bình quân nhà ở Int 3 20 Mã số đăng ký kết hôn Numeric 9 21 Mã số giấy ly hôn Numeric 9 22 Mã số giấy khai tử Numeric 9 23 Ngày kê khai/ cập nhật Datetime 8 ắ Cấu trúc bảng Trinhdo. Bảng này dùng để l−u trữ về trình độ và nghề nghiệp của mỗi công dân. - 64 -
  65. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Stt Tên tr−ờng Kiểu dữ liệu Độ rộng 1 Mã số công dân * Char 10 2 Trình độ học vấn Char 35 3 Trình độ chuyên môn đào tạo Char 30 4 Ngành nghề đ−ợc đào tạo Char 55 5 Ngành nghề đang làm Char 40 Tình trạng việc làm (th−ờng 6 Char 25 xuyên, thất th−ờng, thất nghiêp) Hình thức việc làm 7 Char 30 (tự do, biên chế, hợp đồng) 8 Có nhu cầu học nghề Char 15 9 Chế độ −u đãi đang h−ởng Varchar 50 10 Ngày kê khai/ cập nhật Datetime 8 ắ Cấu trúc bảng Toidanh. Stt Tên tr−ờng Kiểu dữ liệu Độ rộng 1 Mã số công dân * Char 10 2 Mã số các bản án Numeric 9 3 Các tội danh Varchar 50 4 Các loại tội phạm Varchar 50 5 Các hình phạt Varchar 50 6 Ngày kê khai/ cập nhật Datetime 8 ắ Thiết kế mã Các bảng Congdan, Trinhdo, Toi danh, đều dùng mã số công dân làm khoá cơ bản nên chỉ cần thiết kế mã số công dân. Chỉ cần biết đ−ợc mã công dân là có thể tìm ra các thông tin có liên quan nh− là họ tên, ngày sinh, quê quán, trình độ học vấn, - 65 -
  66. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Tên hệ thống: Thiết kế mã cho bảng Congdan Ngày Thiết kế: 25/5/2000 Tên mã: Ph−ơng pháp mã: Số cột: 23 Mã số công dân Mã tuần tự Số l−ợng sử dụng: Chu kỳ sử dụng: Đ−ợc sử dụng trong: Hiện nay khoảng: 5-10 năm Xác định thông tin về dân 250000-500000 c− trong quận. T−ơng lai khoảng: Thống kê, báo cáo, cập 600000-999999 nhật, tìm kiếm, tra cứu, Đối t−ợng mã: Mã số công dân Cấu trúc: tuần tự Cấu trúc: A 01 000001: mã quận, mã ph−ờng/xã, số thứ tự Dạng mã quận: A ẻ Z Dạng mã ph−ờng/xã: 01 ẻ 99 Dạng số thứ tự: 000001 ẻ 999999 IV. Sử dụng SQL Server vào việc quản lý IV.1 Giới thiệu về màn hình giao diện SQL Server ắ Microsoft Management Console (MMC) là một công cụ mà bạn có thể dùng để tạo, l−u, và mở các công cụ quản lý (đ−ợc gọi là MMS consoles) nhằm quản lý phần cứng, phần mền, và các thành phần của hệ Windows NT. (Bạn cũng có thể chạy MMS trên các hệ điều hành Windows 95 và Windows 98). MMS cung cấp các thuận lợi sau đây: • Giao diện chung (Common interface): MMS cung cấp một giao diện ng−ời dùng đơn để từ đó có thể chạy tất cả các công cụ quản lý. • Tính nguyên vẹn (Integration): MMS tích hợp tất cả các công cụ đ−ợc yêu cầu để thực hiện tác vụ quản lý. • Có thể định cấu hình (Configurable): MMS cấu hình các công cụ để hoạt động trên các thành phần hệ thống đặc biệt. - 66 -
  67. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Có thể tạo lại (Reproducible): MMS l−u các công cụ và các cấu hình kết hợp để bạn có thể mở và sử dụng trên các máy tính khác nhau. MMC không thực hiện chức năng quản lý, nh−ng lại điều khiển các ch−ơng trình, đ−ợc gọi là snap-ins để thực hiện điều đó. Bạn có thể bổ sung snap-ins vào bất cứ MMS consoles và cấu hình chung để quản lý một thành phần của hệ thống. Có hai cách chính mà bạn có thể sử dụng MMS: trong chế độ user, làm việc với các MMS consoles đang có để quản lý một hệ thống, hoặc trong chế độ author, tạo ra một consoles mới hoặc chỉnh sửa MMS đang có. ắ SQL Enterprise Manager là một công cụ đồ hoạ cho phép bạn dễ dàng đinh cấu hình và quản lý các đối t−ợng Microsoft SQL Server và SQL Server. SQL Enterprise Manager hỗ trợ một số khả năng sau: • Khả năng cảnh báo dành cho ng−ời quản lý. • Một công cụ lập thời biểu (scheduling engine) • Giao diện quản lý lặp lại đ−ợc tạo sẵn. • Tạo các script. • Quản lý các thiết bị và cơ sở dữ liệu. • Sao chép dự phòng các cơ sở dữ liệu và transaction log. • Quản lý các Table, các View, các Trigger, các index, - 67 -
  68. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Cơ sở dữ liệu (Databases) Một cơ sở dữ liệu trong Microsoft SQL Server bao gồm tập hợp của các bảng với dữ liệu và các đối t−ợng khác nh− các views, các indexes, các stored procedures, các triggers mà xác định để hỗ trợ hoạt động thực hiện với dữ liệu. Tr−ớc các đối t−ợng trong cơ sở dữ liệu có thể đ−ợc tạo ra, bạn phải tạo cơ sở dữ liệu và hiểu về cách thay đổi cài đặt và định cấu hình cơ sở dữ liệu nh− thế nào. Thao tác này bao gồm nh− là mở rộng hoặc rút gọn cơ sở dữ liệu, hoặc chỉ định các file sử dụng tạo ra cơ sở dữ liệu. ắ Sử dụng Data Transformation Services (DTS) Bạn có thể nhập xuất dữ liệu giữa nhiều nguồn khác nhau sử dụng cấu trúc dựa trên OLE DB. Bạn có thể biến đổi dữ liệu theo h−ớng xây dựng kho dữ liệu từ xử lý biến đổi trực tuyến (Online Transaction Processing = OLTP) hệ thống trong quá trình này. Hơn thế nữa DTS cho phép bạn chuyển giao đối t−ợng cơ sở dữ liệu nh− là các chỉ số và các thủ tục l−u trữ giữa các máy chạy SQL Server 7.0 Sử dụng DTS, có thể thực hiện: • Xây dựng kho dữ liệu và dữ liệu trung tâm trong Microsoft SQL Server bởi nhập vào và chuyển giao dữ liệu từ nhiều nguồn khác nhau t−ơng tác hoặc nền tảng lập tr−ớc theo chu kỳ tự động. • Tạo tuỳ chỉnh biến đổi các đối t−ợng mà có thể tích hợp vào ba phần (third-party) sản phẩm. • Truy cập ứng dụng sử dụng third-party OLE DB providers. Điều này cho phép các ứng dụng, mà nhà cung cấp OLE DB hiện có, đ−ợc sử dụng nh− là nguồn và đích của dữ liệu. DTS cho phép bạn di chuyển và biến đổi dữ liệu theo h−ớng và xuất phát từ: • Nhà cung cấp OLE DB nh− là SQL Server, Microsoft Excel, và Microsoft Access. • Nguồn dữ liệu ODBC nh− là Oracle và DB2 sử dụng Microsoft OLE DB provider cho ODBC. Các thành phần DTS gồm có DTS Import Wizard, DTS Export Wizard, và DTS Designer, mà dùng sẵn qua SQL Server Enterprise Manager. DTS cũng bao gồm ch−ơng trình giao tiếp COM bạn có thể sử dụng để tạo ra tuỳ biến nhập xuất và chuyển đổi các ứng dụng. - 68 -
  69. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Quản lý Server Việc quản lý Microsoft SQL Server gồm một loạt các tác vụ quản lý sau: • Đăng ký Server và gán nhận passwords. • Định lại cấu hình kết nối mạng. • Cấu hình Server liên kết, cho phép bạn thực hiện các truy vấn (queries) phân bố và các mục chuyển giao phân bố về nguồn dữ liệu OLE DB thông qua Enterprise. • Định cấu hình Server từ xa, mà cho phép bạn kết nối tới sử dụng SQL Server để thực hiện thủ tục l−u trữ th−ờng trú trên SQL Server khác. • Thiết lập cấu hình tuỳ chọn Server. • Quản lý thông tin SQL Server. • Cài đặt khoảng kiểm xoát vòng (polling interval). Trong phần lớn các tr−ờng hợp, bạn không cần định lại cấu hình Server. Mặc định cài đặt cho các thành phần Server, cấu hình trong khi cài đặt SQL Server, cho phép bạn chạy ngay sau khi cài đặt. Tuy nhiên, sự quản lý Server cần thiết trong tình huống khi bạn muốn thêm vào Server mới, cài đặt riêng biệt cấu hình Server, thay đổi kết nối mạng, hoặc thiết lập cấu hình tuỳ chon cải thiện thực thi SQL Server. ắ Quản lý bảo mật (Managing Security) Để đảm bảo rằng dữ liệu và đối t−ợng l−u trữ trong Microsoft SQL Server là truy cập duy nhất bởi uỷ quyền của ng−ời sử dụng (user), sự bảo mật phải đ−ợc thiết lập đúng. Sự hiểu biết về thiết lập đúng có thể giúp nhà quản lý những việc sảy ra đơn giản. Yếu tố bảo mật mà có thể có thiết lập gồm có cách thức xác nhận, logins, users, roles, gán quyền, huỷ quyền, và từ chối cho phép trên câu lệnh Transact-SQL và các đối t−ợng, sự mật hoá dữ liệu. - 69 -
  70. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT IV.2 Biểu đồ cơ sở dữ liệu và khung nhìn. ắ Biểu đồ cơ sở dữ liệu cho ta biết đ−ợc mối quan hệ giữa các bảng với nhau nh− bảng Congdan, Trinhdo, Toidanh. ắ Bằng cách tạo, chỉnh sửa và sử dụng thủ tục l−u trữ chúng ta có thể đơn giản hoá các trình ứng dụng giao dịch và cải tiến trình ứng dụng và thực hiện cơ sở dữ liệu. - 70 -
  71. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT ắ Bằng cách sử dụng các view để tối −u hoá, bạn có thể tăng tốc độ truy cập nhanh hơn. - 71 -
  72. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT IV.3 áp dụng SQL Server vào việc quản lý và truy vấn ắ Sau khi nhập dữ liệu vào trong cơ sở dữ liệu chúng ta bắt đầu sử dụng câu lệnh SQL Server để truy vấn tìm các thông tin cần thiết về một cá nhân nào đó chỉ cần biết một số đặc điểm cơ bản nh−: mã số công dân, ngày sinh, giới tính, quê quán, trình độ, ắ Màn hình truy vấn trong SQL Server Enterprise có dạng nh− hình sau trong đó có: Show/Hide Diagram Pane, Show/Hide Grid Pane, Show/Hide SQL Pane, Show/Hide Results Pane. • Show/Hide Diagram Pane giới thiệu trình bày minh hoạ của dữ liệu đầu ra (các bảng, hoặc các truy vấn khác) bạn có sự lựa chọn từ dữ liệu có liên quan. Trong Diagram Pane bạn có thể: - Thêm vào hoặc gỡ bỏ nguồn dữ liệu vào và chỉ định các cột cho đầu ra. - Chỉ định các cột cho trật tự truy vấn. - Chỉ định mà bạn muốn nhóm các hàng trong tập kết quả. - 72 -
  73. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Show/Hide Grid Pane cho phép bạn chỉ định tuỳ chọn query – nh− là dữ liệu cột đ−ợc hiển thị nh− thế nào, thứ tự kết quả ra sao và các hàng nào là lựa chọn trong truy vấn, với Grid Pane bạn có thể: - Các cột hiển thị và bí danh tên cột. - Bảng có cột thuộc về nó. - Biểu thức tính toán cho các cột. - Trật tự sắp xếp cho các truy vấn. - Tìm kiếm có điều kiện. - Kết hợp tiêu chí, gồm chức năng tổng hợp sử dụng cho báo cáo tổng kết. - Giá trị mới cho cập nhật hoặc chèn các giá trị truy vấn. - Tên cột đích dựa vào truy vấn. • Show/Hide SQL Pane hiển thị câu lệnh SQL cho truy vấn hiện hành. Với SQL Pane bạn có thể: - Tạo ra truy vấn mới bởi đ−a và các câu lệnh SQL. - Thay đổi câu lệnh SQL tao ra bởi ng−ời thiết kế truy vấn dựa trên cài đặt tạo ra Diargam và Grid pane. - Đ−a vào các câu lệnh mà đem lại sự thuận lợi của tính năng xác định tới cơ sở dữ liệu bạn đang sử dụng. • Show/Hide Results Pane đ−a ra các kết quả của phần lớn truy vấn lựa chọn thực hiện trên SQL pane. (các kết quả của truy vấn khác hiển thị trong hộp thông báo). Trong Results pane bạn có thể: - Xem tập kết quả truy vấn trong bảng Results pane. - Hiệu chỉnh các giá trị trong các cột riêng trong tập kết quả, thêm vào hàng mới, xoá bỏ những hàng không cần thiết. ắ Sử dụng câu lệnh SQL để kiết xuất, xem xét các thông tin có liên quan đến công dân, thống kê, báo cáo, tổng hợp, Dữ liệu thử trong cơ sở dữ liệu là 131 bản ghi trong đó gồm nhiều tr−ờng khác nhau nh− Mã số công dân, Tên Khai sinh, Ngày sinh, - 73 -
  74. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Tìm tổng số dân trong quận: • Trong tổng số dân của một quận có bao nhiêu nam, bao nhiêu nữ. - 74 -
  75. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Tổng số trẻ em đến tuổi tới tr−ờng năm học 2000 - 2001 là bao nhiêu em ? • Xem có bao nhiêu em ch−a đến tuổi đi học, bao nhiêu em học tiểu học, bao nhiêu em học phổ thông cơ sở, bao nhiêu em học trung học. - 75 -
  76. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Thống kê một quận có bao nhiêu ng−ời có trình độ đại học, cao học. • Trong tổng số dân hãy tìm ng−ời có ngày tháng năm sinh cao tuổi nhất và ng−ời trẻ tuổi nhất. - 76 -
  77. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Từ bảng Congdan và Trinhdo tìm những ng−ời có trình độ cao học đ−ợc sắp xếp theo ngày sinh. • Khi biết đ−ợc mã số đăng ký kết hôn hoặc ly hôn thì ta có thể tìm đ−ợc thông tin về tình trạng hôn nhân của ng−ời đó. - 77 -
  78. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Khi biết đ−ợc mã số các bản án thì ta có thể tìm ra mã số công dân, tên tuổi, ngày sinh, giới tính, • Ta có thể dùng câu lệnh Update, Delete để thay đổi dữ liệu hoặc xoá. Trên đây là một số truy vấn giúp ta hiểu thêm về cách mà SQL Server thực hiện xử lý các thao tác mà chúng ta có thể dùng trong thực tế. - 78 -
  79. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT V. Phân định công việc giữa Client/Server trong quản lý V.1 Chức năng của Client và Server Chức năng của Client và Server trong bài toán quản lý dân c−: ắ Chức năng của Client là nơi các thông tin đ−ợc nhập vào và nơi thông tin cuối cùng đến ng−ời sử dụng đ−ợc kiết xuất ra màn hình giao tiếp. Nh− vậy nhiệm vụ chính của Client là vào ra thông tin, còn nhiệm vụ xử lý thông tin là thứ yếu. ắ Chức năng của Server là nơi tạo và l−u trữ dữ liệu của hệ thống. Server là nơi xử lý dữ liệu nh− thực hiện các lời gọi thủ tục l−u trữ (store procedure), thực hiện các câu lệnh SQL Server do Client gửi tới. ắ Hệ thống đ−ợc xây dựng bởi: • Tại Client: giao diện ng−ời dùng đ−ợc sử dụng bằng phần mềm Microsoft SQL Server 7.0. • Tại Server: sử dụng hệ quản trị cơ sở dữ liệu Microsoft SQL Server 7.0 • Hệ điều hành mạng: Windows NT Server 4.0 hoặc cao hơn. Việc liên lạc giữa Client và Server thông qua chuẩn ODBC của Microsoft, bằng việc gọi tới các thủ tục l−u trữ (store procedure), các lệnh SQL gửi lên từ Client. ắ Đánh giá −u điểm: • Dữ liệu tập trung trên Server, dễ dàng trong quản lý, tăng c−ờng tính thống nhất và an toàn dữ liệu, tránh đ−ợc những thay đổi ngoài ý muốn của ng−ời sử dụng. • Tận dụng đ−ợc tiềm năng sức mạnh về sử lý của Server. • Giảm đ−ợc các xử lý tại Client, tăng tốc độ nhập xuất dữ liệu. • Tận dụng đ−ợc −u điểm của cơ chế gọi thủ tục l−u trữ. đây là một trong những đặc tính −u việt nhất của hệ thống xử lý các ứng dụng phân tán theo mô hình Client/Server. ắ Đánh giá nh−ợc điểm: • Xử lý dữ liệu trên Server là chính nên nếu Server cấu hình không mạnh hoặc chạy song song SQL Server với nhiều ứng dụng khác hoặc có nhiều Client gọi tới Server thì tốc độ đem lại sẽ không nh− - 79 -
  80. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT mong muốn. Là vì có nhiều lời gọi thủ tục, lệnh SQL đ−ợc gửi tới cùng lúc, thì dòng xếp hàng sẽ dài ra. • Sẽ có quá nhiều luồng thông tin đi trên mạng, làm giảm tốc độ mạng. V.2 Công việc thực hiện tại Client ắ Chức năng xuất nhập dữ liệu: • Client có một hệ thống giao diện hết sức thân thiện, giúp cho ng−ời sử dụng dễ dàng, thuận tiện cho ng−ời sử dụng trong việc theo dõi, quản lý các dữ liệu vào ra hệ thống. Có thể liệt kê một số điểm nh−: việc thiết lập các khuôn dạng thống nhất cho các tr−ờng dữ liệu, việc thống nhất sử dụng các hệ thống phím điều khiển. • Khi Client goi thủ tục l−u trữ, một lệnh SQL thì kết quả truy vấn đ−ợc Server gửi lại Clinet thông qua một cursor, mọi thông tin kiết xuất ra màn hình đều dựa vào cursor này. ắ Chức năng xử lý • Kiểm tra tính hợp lệ của dữ liệu Mục đích loại trừ phần lớn các tr−ờng hợp dữ liệu nhập vào không hợp lệ, thực hiện ngay tại Client và trả lại ngay kết quả cho ng−ời sử dụng, làm giảm quá trình kiểm tra trên Server. • Xử lý dữ liệu gửi đi Tính toán ngay ra kết quả tại Client, chỉ cần cập nhật kết quả lên trên Server làm giảm bớt thời gian tính toán trên Server. Thực tế cho thấy đây là một biện pháp rất hữu hiệu, các kết quả tính toán ra nếu có vấn đề nào đó (tính hợp lệ, ) sẽ đ−ợc thông báo và sửa đổi ngay tại Client và tính toán tiếp. Quá trình này đảm bảo cho việc cập nhật trên Server một lần duy nhất với kết quả đúng. • Thực hiện lời gọi thủ tục l−u trữ SP Đối với quá trình xử lý, chẳng hạn khi phải cập nhật vào nhiều bảng, việc thực hiện gọi tới các thủ tục l−u trữ đ−ợc tối −u hoá bằng cách thực hiện một lần duy nhất mà vẫn đảm bảo toàn bộ các thao tác dữ liệu bên trong nhằm mục đính tránh gọi hàm nhiều lần trong một quy trình xử lý, làm giảm tới mức thấp nhất các thông tin l−u thông trên mạng. • Xử lý dữ liệu gửi về - 80 -
  81. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT Kết quả của việc thực hiện lời gọi tới thủ tục l−u, hoặc lệnh SQL trên Server sẽ trả về Client, kết quả đó có thể là một tập hợp dữ liệu, hoặc có thể là các lỗi trong khi thực hiện trên Server, tham số trả về sẽ cho biết lỗi này. V.3 Công việc thực hiện tại Server ắ Server là nơi tạo và l−u trữ dữ liệu cho toàn bộ hệ thống để tạo một cơ sở dữ liệu, ta có thể tạo trực tiếp trên Server hoặc tạo trên các công cụ hỗ trợ khác nh− SQL Server, Microsoft Access, ắ Server là nơi xử lý chính các dữ liệu trên hệ thống Công việc xử lý đ−ợc thực hiện bởi các thủ tục l−u trữ. Thủ tục l−u trữ là một tập các lệnh QSL th−ờng dùng và có mục đích sử lý dữ liệu và xắp xếp có hệ thống tạo thành một thủ tục l−u trữ. Thủ tục này sau khi biên dịch đ−ợc l−u thẳng vào cơ sở dữ liệu, do đó đ−ợc gọi là các thủ tục. Cấu trúc để tạo thủ tục l−u trữ trong SQL Server 7.0 nh− sau: === /* Add an object to the dtproperties table */ create procedure dbo.dt_adduserobject as set nocount on /* Create the user object if it does not exist already */ begin transaction insert dbo.dtproperties (property) VALUES 'DtgSchemaOBJECT') update dbo.dtproperties set objectid=@@identity where id=@@identity and roperty='DtgSchemaOBJECT' commit return @@identity === ắ Tác dụng của việc thực hiện thủ tục l−u trữ Thủ tục l−u trữ là một trong các −u điểm rất mạnh của hệ quản trị cơ sở dữ liệu xử lý các ứng dụng phân tán theo mô hình Client/Server nói chung và của SQL Server nói riêng. Lợi ích của thủ tục l−u trữ đem lại rất nhiều, nh−ng phải kể đến: - 81 -
  82. Luận văn tốt nghiệp Vũ Hùng C−ờng B2 - CNTT • Làm giảm bớt các luồng thông tin trên mạng. Thay vì việc mỗi khi thực hiện một loạt các giao dịch, bằng cách gửi một loạt các câu lệnh SQL từ Client tới Server, giờ đây ta có thể nhóm tất cả các câu lệnh đó vào chung một thủ tục và l−u tại Server và việc thực hiện bây giờ chỉ đơn giản ta gửi một lời gọi từ Client sang Server. • Một lời gọi có thể thực hiện một tập hợp các câu lệnh SQL, điều này rất quan trong cho việc đảm bảo tính an toàn và thống nhất dữ liệu cho hệ thống. • Thực hiện các câu lệnh SQL mà không cần dịch lại, làm tăng tốc độ xử lý dữ liệu. • Câu lệnh SQL l−u trên Server đ−ợc sử dụng trên nhiều trạm làm việc, làm giảm bớt thời gian lập trình. • Quản lý tập trung các lệnh xử lý dữ liệu trên Server, tạo thuận lợi cho ng−ời sử dụng trong việc kiểm soát các thao tác dữ liệu, tối −u hoá các lệnh sử lý bằng SQL. V.4 Vấn đề liên lạc giữa Client và Server Vấn đề liên lạc giữa Client với Server đ−ợc thực hiện thông qua chuẩn ODBC (Open Database Connectivities: hệ thống kết nối cơ sở dữ liệu mở) thông qua việc thực hiện thủ tục l−u (hay gọi thực hiện các lệnh SQL). Sử dụng chuẩn ODBC có nhiều −u điểm nh−: • Sử dụng ODBC rất tiện lợi đối với ng−ời lập trình, họ hoàn toàn bị trong xuốt với các xử lý hệ thống nh− việc gửi các câu lệnh SQL đi, nhận kết quả trở về, thông báo lỗi, đều do ODBC đảm trách. • ODBC hết sức mềm dẻo, điều đó có nghĩa ODBC hỗ trợ cho ng−ời sử dụng một khả năng to lớn về việc sử dụng Database Server, và việc chuyên đổi giữa các Database Server là dễ dàng. • Về tốc độ xử lý ODBC hiện tại không phải là giải pháp tối −u cho tốc độ, tuy nhiên hãng Microsoft rất khuyến khích việc sử dụng ODBC và hứa hẹn trong t−ơng lai sẽ có kế hoach tối −u hoá. • Việc lập trình tại Client bằng ngôn ngữ lập trình để kết nối lên SQL Server theo chuẩn ODBC hết sức dễ dàng, lập trình viên sẽ không có cảm giác khác biệt giữa lập trình bằng ODBC và lập trình trực tiếp với cơ sở dữ liệu. - 82 -