Bài giảng Hệ quản trị cơ sở dữ liệu

Bài giảng Hệ quản trị CSDL

1

MỤC LỤC

M Ụ C L Ụ C .............................................................................................................................1

Ch ươ ng 1: T Ổ NG QUAN V Ề H Ệ QU Ả N TR Ị CSDL..........................................................3

1.1. Ð ị nh ngh ĩ a: .................................................................................................................3

1.2. Các kh ả n ă ng c ủ a h ệ qu ả n tr ị CSDL...........................................................................3

1.3. Đặ c đ i ể m c ủ a m ộ t h ệ qu ả n tr ị CSD L ..........................................................................4

1.3.1. S ự tr ừ u t ượ ng hoá d ữ li ệ u :...................................................................................4

1.3.2. Ngôn ng ữ c ơ s ở d ữ li ệ u........................................................................................5

1.3.3. X ử lý câu h ỏ i.......................................................................................................6

1.3.4. Qu ả n tr ị giao d ị ch ................................................................................................6

1.3.5. Qu ả n lý l ư u tr ữ .....................................................................................................7

1.4. Ki ế n trúc c ủ a m ộ t h ệ qu ả n tr ị CSDL ..........................................................................7

1.5. Các ch ứ c n ă ng c ủ a h ệ qu ả n tr ị CSDL quan h ệ ...........................................................9

1.5.1. Các khái ni ệ m trong mô hình d ữ li ệ u quan h ệ ....................................................9

1.5.2. Các ch ứ c n ă ng c ủ a h ệ qu ả n tr ị CSDL quan h ệ ..................................................1 1

Ch ươ ng 2: CÁC CÂU L Ệ NH SQL C Ơ B Ả N .....................................................................1 4

2.1. CÁC CÂU L Ệ NH ĐỊ NH NGH Ĩ A D Ữ LI Ệ U ...........................................................1 4

2.1.1. L ệ nh CREATE...................................................................................................1 4

2.1.2. L ệ nh thay th ế s ử a đổ i ALTE R ...........................................................................1 5

2.1.3. Xoá c ấ u trúc DROP ...........................................................................................1 6

2.2. CÁC CÂU L Ệ NH C Ậ P NH Ậ T D Ữ LI Ệ U ...............................................................1 6

2.2.1. L ệ nh Inser t .........................................................................................................1 6

2.2.2. L ệ nh Update.......................................................................................................1 6

2.2.2. L ệ nh Delete........................................................................................................17

2.3. KI Ể M SOÁT D Ữ LI Ệ U............................................................................................1 7

2.3.1. Trao quy ề n GRANT ..........................................................................................1 7

2.3.2. Thu h ồ i quy ề n REVOTE ...................................................................................1 7

2.4. TRUY V Ấ N D Ữ LI Ệ U.............................................................................................1 8

2.4.1. Tìm ki ế m theo câu h ỏ i đơ n gi ả n ........................................................................1 8

2.4.2. S ử d ụ ng các hàm th ư vi ệ n..................................................................................1 9

2.4.3. Tìm ki ế m nh ờ các m ệ nh đề ................................................................................2 0

2.4.4. Câu h ỏ i ph ứ c t ạ p ................................................................................................2 1

Ch ươ ng 3: H Ệ QU Ả N TR Ị C Ơ S Ở D Ữ LI Ệ U SQL SERVER ...........................................2 4

3.1. T Ổ NG QUAN V Ề H Ệ QU Ả N TR Ị SQL SERVER .................................................2 4

3.1.1. Gi ớ i thi ệ u h ệ qu ả n tr ị SQL Serve r .....................................................................2 4

3.1.2.Các thành ph ầ n c ủ a SQL Serve r .........................................................................2 4

3.1.2.1. Các thành ph ầ n c ủ a SQL Server 200 0 ............................................................2 4

3.1.2.2. Các thành ph ầ n c ủ a SQL Server 200 5 ............................................................2 8

3.1.3. Qu ả n lý các d ị ch v ụ c ủ a SQL Server.................................................................3 2

3.1.3.1. Qu ả n lý các d ị ch v ụ c ủ a SQL Server 200 0 ....................................................3 2

3.1.3.2. Qu ả n lý các d ị ch v ụ c ủ a SQL Server 200 5 ....................................................3 6

3.2. LÀM VI Ệ C V Ớ I CÁC ĐỐ I T ƯỢ NG TRONG SQL SERVE R ...............................4 4

3.2.1. C ơ s ở d ữ li ệ u - Database....................................................................................4 5

3.2.2.B ả ng - Table........................................................................................................59

3.2.3. View...................................................................................................................6 7

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

2

3.2.4. Ch ỉ m ụ c - Index .................................................................................................80

3.2.5. L ượ c đồ - Diagram s ...........................................................................................9 2

3.3. B Ả O ĐẢ M D Ữ LI Ệ U TRONG SQL SERVE R .....................................................1 10

3.3.1. Phân quy ề n và b ả o m ậ t trong SQL Server.......................................................1 10

3.3.2. Sao l ư u - ph ụ c h ồ i CSDL.................................................................................1 38

Ch ươ ng 4. L Ậ P TRÌNH TRÊN SQL SERVER ................................................................1 52

4.1. Gi ớ i thi ệ u ngôn ng ữ T-SQL....................................................................................1 52

4.1.1. Khái ni ệ m.........................................................................................................1 52

4.1.2. Phát bi ể u truy v ấ n d ữ li ệ u nâng cao.................................................................1 52

4.1.3. L ậ p trình c ấ u trúc trong SQL Server ...............................................................1 60

4.2. Các store procedure – Các th ủ t ụ c ..........................................................................1 79

4.2.1. Khái ni ệ m.........................................................................................................1 79

4.2.2. T ạ o store procedure .........................................................................................1 79

4.2.3.Thay đổ i, xóa, xem n ộ i dung store procedure...................................................1 85

4.3. Các store function – Các hà m .................................................................................187

4.3.1. Các khái ni ệ m ..................................................................................................1 87

4.3.2. T ạ o các hà m .....................................................................................................187

4.3.3. Các ví d ụ t ạ o các hà m ......................................................................................189

4.3.4.Thay đổ i, xóa, xem n ộ i dung store function .....................................................1 92

4.4. Trigger ....................................................................................................................193

4.4.1. Khái ni ệ m.........................................................................................................1 93

4.4.2. T ạ o trigger........................................................................................................1 95

4.4.3. Các thao tác qu ả n lý trigger .............................................................................2 04

Ch ươ ng 5. SQL SERVER VÀ L Ậ P TRÌNH Ứ NG D Ụ NG...............................................2 08

5.1. Mô hình k ế t n ố i ứ ng d ụ ng đế n SQL server.............................................................2 08

5.1.1. Mô hình ADO ..................................................................................................2 08

5.1.2. Mô hình ADO.NET .........................................................................................2 10

5.1.3. Đ i ể m khác nhau gi ữ a ADO và ADO.NET ......................................................2 15

5.2. Các l ớ p SqlClient trong mô hình ADO.NET..........................................................2 15

5.2.1. Class SqlConnectio n ........................................................................................2 16

5.2.2. Class SqlCommand..........................................................................................2 19

5.2.3. Class SqlDataAdapte r ......................................................................................2 24

5.2.4. Class DataSe t ...................................................................................................230

5.2.5. DataView .........................................................................................................2 31

5.3. Ví d ụ minh h ọ a .......................................................................................................2 34

5.3.1. CSDL trong ví d ụ minh h ọ a.............................................................................2 35

5.3.2. Xây d ự ng Form nh ậ p DSSinhVie n ..................................................................2 36

5.3.3. Xây d ự ng Form nh ậ p DSLop...........................................................................2 44

5.3.4. Xây d ự ng Form hi ể n th ị danh sách sinh viên. .................................................2 46

5.3.5. Xây d ự ng báo cáo dùng Report. ......................................................................2 52

5.3.6. Xây d ự ng report dùng Crystal Report..............................................................266

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

3

Chương 1: TỔNG QUAN VỀ HỆ QUẢN TRỊ CSDL

1.1. Ðịnh nghĩa:

- Hệ quản trị cơ sở dữ liệu (Database Management System - DBMS): Là

một hệ thống phần mềm cho phép tạo lập cơ sở dữ liệu và điều khiển mọi truy

nhập đối với cơ sở dữ liệu đó.

Trên thị trường phần mềm hiện nay ở Việt Nam đã xuất hiện khá nhiều

phần mềm hệ quản trị cơ sở dữ liệu như: Microsoft Access, Foxpro, DB2,

SQL Server, Oracle,.v.v…

- Hệ quản trị cơ sở dữ liệu quan hệ (Relation Database Management

System - RDBMS) là một hệ quản trị cơ sở dữ liệu theo mô hình quan hệ.

1.2. Các khả năng của hệ quản trị CSDL

Có hai khả năng chính cho phép phân biệt các hệ quản trị cơ sở dữ liệu

với các kiểu hệ thống lập trình khác:

i.

Khả năng quản lý dữ liệu tồn tại lâu dài: đặc điểm này chỉ ra rằng

có một cơ sở dữ liệu tồn tại trong một thời gian dài, nội dung của

cơ sở dữ liệu này là các dữ liệu mà hệ quản trị CSDL truy nhập và

quản lý.

ii.

Khả năng truy nhập các khối lượng dữ liệu lớn một cách hiệu quả.

Ngoài hai khả năng cơ bản trên, hệ quản trị CSDL còn có các khả năng

khác mà có thể thấy trong hầu hết các hệ quản trị CSDL đó là:

iii.

iv.

v.

Hỗ trợ ít nhất một mô hình dữ liệu hay một sự trừu tượng toán

học mà qua đó người sử dụng có thể quan sát dữ liệu.

Ðảm bảo tính độc lập dữ liệu hay sự bất biến của chương trình

ứng dụng đối với các thay đổi về cấu trúc trong mô hình dữ liệu.

Hỗ trợ các ngôn ngữ cao cấp nhất định cho phép người sử dụng

định nghĩa cấu trúc dữ liệu, truy nhập dữ liệu và thao tác dữ liệu.

vi.

Quản lý giao dịch, có nghĩa là khả năng cung cấp các truy nhập

đồng thời, đúng đắn đối với CSDL từ nhiều người sử dụng tại

cùng một thời điểm.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

4

vii. Ðiều khiển truy nhập, có nghĩa là khả năng hạn chế truy nhập đến

các dữ liệu bởi những người sử dụng không được cấp phép và khả

năng kiểm tra tính đúng đắn của CSDL.

viii. Phục hồi dữ liệu, có nghĩa là có khả năng phục hồi dữ liệu, không

làm mất mát dữ liệu với các lỗi hệ thống.

1.3. Đặc điểm của một hệ quản trị CSDL

1.3.1. Sự trừu tượng hoá dữ liệu:

Ðể cho hệ thống có thể sử dụng được, hệ quản trị CSDL phải tra cứu

hay tìm kiếm dữ liệu một cách có hiệu quả. Ðiều này dẫn đến việc thiết kế các

cấu trúc dữ liệu phức tạp để biểu diễn dữ liệu trong CSDL này. Người phát

triển che dấu tính phức tạp này thông qua một số mức trừu tượng để đơn giản

hoá các tương tác của người sử dụng đối với hệ thống.

Khung nhìn 1

Khung nhìn n

… Mức khung nhìn …

Mức logic

Mức vật lý

Hình 1.1. Ba mức trừu tượng dữ liệu

-

Mức vật lý: Mức thấp nhất của sự trừu tượng mô tả dữ liệu được

lưu trữ một cách thực sự như thế nào. Tại mức vật lý, các cấu trúc

dữ liệu mức thấp phức tạp được mô tả chi tiết.

Mức logic: Mức cao tiếp theo của sự trừu tượng hoá mô tả những

dữ liệu nào được lưu trữ và các mối quan hệ nào tồn tại giữa các

dữ liệu này. Mức logic của sự trừu tượng được xác định người

quản trị CSDL, cụ thể phải quyết định những thông tin gì được

lưu trữ trong CSDL.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

5

-

Mức khung nhìn: Mức cao nhất của sự trừu tượng mô tả chỉ một

phần của toàn bộ CSDL. Mặc dù sử dụng các cấu trúc đơn giản

mức logic, một số phức tập vẫn còn tồn tại do kích thước lớn của

CSDL. Thực chất những người sử dụng chỉ cần truy nhập đến một

phần CSDL, do vậy sự tương tác của họ với hệ thống này là đơn

giản hoá và mức khung nhìn của sự trừu tượng được xác định. Hệ

thống có thể được cung cấp nhiều khung nhìn đối với cùng một cơ

sở dữ liệu.

1.3.2. Ngôn ngữ cơ sở dữ liệu

Một hệ quản trị cơ sở dữ liệu thường cung cấp hai kiểu ngôn ngữ khác

nhau đó là: ngôn ngữ mô tả sơ đồ cơ sở dữ liệu và ngôn ngữ biểu diễn các

truy vấn và các cập nhật cơ sở dữ liệu.

-

Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL)

+ Một sơ đồ CSDL đặc tả bởi một tập các định nghĩa được

biểu diễn bởi một ngôn ngữ đặc biệt được gọi là ngôn ngữ

định nghĩa dữ liệu. Kết quả của việc dịch các ngôn ngữ

này là một tập các bảng được lưu trữ trong một tệp đặc

biệt được gọi là từ điển dữ liệu hay thư mục dữ liệu.

+ Một từ điển dữ liệu là một tệp chứa các siêu dữ liệu có

nghĩa là các dữ liệu về dữ liệu. Tệp này được tra cứu

trước khi dữ liệu thực sự được đọc hay được sửa đổi trong

hệ CSDL.

+ Cấu trúc và các phương pháp truy nhập được sử dụng bởi

hệ CSDL được đặc tả bởi một tập các định nghĩa trong

một kiểu đặc biệt của DDL là ngôn ngữ định nghĩa và lưu

trữ dữ liệu.

-

Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML):

+ Các yêu cầu về thao tác dữ liệu bao gồm:

• Tìm kiếm thông tin được lưu trữ trong CSDL.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

6

• Thêm thông tin mới vào CSDL.

• Xoá thông tin từ CSDL.

• Thay đổi thông tin được lưu trữ trong CSDL.

+ Một ngôn ngữ thao tác dữ liệu (DML) là một ngôn ngữ

cho phép người sử dụng truy nhập hay thao tác dữ liệu

được tổ chức bởi mô hình dữ liệu thích hợp. Có hai kiểu

ngôn ngữ thao tác dữ liệu cơ bản:

• Các DML thủ tục đòi hỏi người sử dụng phải đặc

tả dữ liệu nào cần tìm kiếm và tìm kiếm những dữ

liệu này như thế nào.

• Các DML phi thủ tục đòi hỏi người sử dụng đặc tả

dữ liệu nào cần tìm kiếm mà không phải đặc tả tìm

kiếm những dữ liệu này như thế nào.

1.3.3. Xử lý câu hỏi

Công việc của bộ xử lý câu hỏi là biến đổi một truy vấn hay một thao tác

CSDL có thể được biểu diễn ở các mức cao thành một dãy các yêu cầu đối

với các dữ liệu lưu trữ trong CSDL.

Thường phần khó nhất của nhiệm vụ xử lý câu hỏi là tối ưu hoá câu hỏi,

có nghĩa là lựa chọn một kế hoạch tốt nhất đối với hệ thống lưu trữ để trả lời

truy vấn này nhanh nhất.

1.3.4. Quản trị giao dịch

Thông thường một số thao tác trên CSDL hình thành một đơn vị logic

công việc. Ðiều này có nghĩa là hoặc tất cả các thao tác được thực hiện hoặc

không thao tác nào được thực hiện. Hơn nữa sự thực hiện các thao tác này

phải đảm bảo tính nhất quán của CSDL.

Một giao dịch là một tập hợp các thao tác mà xử lý như một đơn vị không

chia cắt được. Các hệ quản trị CSDL điển hình cho phép người sử dụng một

hay nhiều nhóm thao tác tra cứu hay thay đổi CSDL thành một giao dịch.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

7

1.3.5. Quản lý lưu trữ

Các CSDL thường đòi hỏi một khối lượng lớn không gian lưu trữ. Do bộ

nhớ chính của máy tính không thể lưu trữ nhiều thông tin như vậy, các thông

tin này được lưu trữ ở các thiết bị nhớ ngoài như đĩa cứng, đĩa mềm,.v.v…

Khi xử lý, dữ liệu cần phải được di chuyển từ đĩa từ vào bộ nhớ chính;

sự di chuyển này là khá chậm so với tốc độ xử lý của bộ nhớ trung tâm, do

vậy các hệ CSDL phải tổ dữ liệu vật lý sao cho tốt, tối thiểu hoá số yêu cầu

chuyển dữ liệu giữa đĩa từ vào bộ nhớ chính.

1.4. Kiến trúc của một hệ quản trị CSDL

Chúng ta sẽ phác thảo kiến trúc và thấy cách thức của một hệ quản trị

CSDL điển hình. Ta có sơ đồ kiến trúc hình 1.2:

Hình 1.2. Các thành phần chính của hệ quản trị CSDL

- Dữ liệu, siêu dữ liệu: Ðáy kiết trúc là thiết bị nhớ ngoài lưu trữ dữ liệu

và siêu dữ liệu. Trong phần này không chỉ chứa dữ liệu được trữ trong CSDL

mà chứa cả các siêu dữ liệu, tức là thông tin cấu trúc của CSDL. Ví dụ: Trong

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

8

hệ quản trị cơ sở dữ liệu quan hệ, các siêu dữ liệu bao gồm các tên của các

quan hệ, tên các thuộc tính của các quan hệ, và các kiểu dữ liệu đối với các

thuộc tính này.

- Bộ quản lý lưu trữ: Nhiệm vụ của bộ quản lý lưu trữ là lấy ra các

thông tin được yêu cầu từ những thiết bị lưu trữ dữ liệu và thay đổi những

thông tin này khi được yêu cầu bởi các mức trên nó của hệ thống.

- Bộ xử lý câu hỏi: Bộ xử lý câu hỏi điều khiển không chỉ các câu hỏi mà

cả các yêu cầu thay đổi dữ liệu hay siêu dữ liệu. Nhiệm vụ của nó là tìm ra

cách tốt nhất một thao tác được yêu cầu và phát ra lệnh đối với bộ quản lý lưu

trữ và thực thi thao tác đó.

- Bộ quản trị giao dịch: Bộ quản trị giao dịch có trách nhiệm đảm bảo

tính toàn vẹn của hệ thống. Nó phải đảm bảo rằng một số thao tác thực hiện

đồng thời không cản trở mỗi thao tác khác và hệ thống không mất dữ liệu

thậm chí cả khi lỗi hệ thống xảy ra.

+ Nó tương tác với bộ xử lý câu hỏi, do vậy nó phải biết dữ liệu nào

được thao tác bởi các thao tác hiện thời để tránh sự đụng độ giữa

các thao tác và cần thiết nó có thể làm trễ một số truy vấn nhất

định hay một số thao tác cập nhật để đụng độ không thể xảy ra.

+ Nó tương tác với bộ quản lý lưu trữ bởi vì các sơ đồ đối với việc

bảo vệ dữ liệu thường kéo theo việc lưu trữ một nhật ký các thay

đổi đối với dữ liệu. Hơn nữa, việc sắp thứ tự các thao tác một

cách thực sự được nhật ký này sẽ chứa trong một bản ghi đối với

mỗi thay đổi khi gặp lỗi hệ thống, các thay đổi chưa được ghi vào

đĩa có thể được thực hiện lại.

- Các kiểu thao tác đối với hệ quản trị CSDL: Tại đỉnh kiến trúc, ta thấy

có 3 kiểu thao tác:

+ Các truy vấn: Ðây là các thao tác hỏi đáp về dữ liệu được lưu trữ

trong CSDL. Chúng được sinh ra theo hai cách sau:

Thông qua giao diện truy vấn chung. Ví dụ: Hệ quản trị

CSDL quan hệ cho phép người sử dụng nhập các câu

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

9

lệnh truy vấn SQL mà nó được chuyển qua bộ xử lý câu

hỏi và được trả lời.

Thông qua các giao diện chương trình ứng dụng: Một hệ

quản trị CSDL điển hình cho phép người lập trình viết

các chương trình ứng dụng gọi đến hệ quản trị CSDL

này và truy vấn CSDL.

+ Các cập nhật dữ liệu: Ðây là các thao tác thay đổi dữ liệu như xoá,

sửa dữ liệu trong CSDL. Giống như các truy vấn, chúng có thể được

phát ra thông qua giao diện chung hoặc thông qua giao diện của

chương trình.

+ Các thay đổi sơ đồ: Các lệnh này thường được phát bởi một người sử

dụng được cấp phép, thường là những người quản trị CSDL mới được

phép thay đổi sơ đồ của CSDL hay tạo lập một CSDL mới.

1.5. Các chức năng của hệ quản trị CSDL quan hệ

1.5.1. Các khái niệm trong mô hình dữ liệu quan hệ

- Miền (domain): là một tập các giá trị hoặc các đối tượng.

- Thực thể: Thực thể là một đối tượng cụ thể hay trừu tượng trong thế

giới thực mà nó tồn tại và có thể phân biệt được với các đối tượng khác.

Ví dụ: Bạn Nguyễn Văn A là một thực thể cụ thể. Hay Sinh viên cũng là

một thực thể, thực thể trừu tượng.

- Thuộc tính (Attribute): Là tính chất của thực thể.

+ Các thực thể có các đặc tính, được gọi là các thuộc tính. Nó kết

hợp với một thực thể trong tập thực thể từ miền giá trị của thuộc

tính. Thông thường, miền giá trị của một thuộc tính là một tập

các số nguyên, các số thực, hay các xâu ký tự.

+ Một thuộc tính hay một tập thuộc tính mà giá trị của nó xác

định duy nhất mỗi thực thể trong tập các thực thể được gọi là

khoá đối với tập thực thể này.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

10

+ Mỗi một thuộc tính nhận tập số các giá trị nhất định được gọi là

domain của thuộc tính đó.

- Một quan hệ (Relation): Định nghĩa một cách đơn giản, một quan hệ là

một bảng dữ liệu có các cột là các thuộc tính và các hàng là các bộ dữ liệu cụ

thể của quan hệ.

- Các liên kết: Một liên kết là một sự kết hợp giữa một số thực thể (hay

quan hệ). Ví dụ: Mối liên kết giữa phòng ban và nhân viên thể hiện: Một nhân

viên A sẽ thuộc một phòng ban B nào đó.

+ Các liên kết một – một: đây là dạng liên kết đơn giản, liên kết trên

hai thực thể là một – một, có nghĩa là mỗi thực thể trong tập thực thể

này có nhiều nhất một thực thể trong tập thực thể kia kết hợp với nó

và ngược lại.

+ Các liên kết một – nhiều: Trong một liên kết một – nhiều, một thực

thể trong tập thực thể A được kết hợp với không hay nhiều thực thể

trong tập thực thể B. Nhưng mỗi thực thể trong tập thực thể B được

kết hợp với nhiều nhất một thực thể trong tập thực thể A.

+ Các liên kết nhiều – nhiều: Ðây là dạng liên kết mà mỗi thực thể

trong tập thực thể này có thể liên kết với không hay nhiều thực thể

trong tập thực thể kia và ngược lại.

Ví dụ 1.1. Các mối liên kết giữa các thực thể:

LOP(MaLop, TenLop, Khoa),

SINHVIEN(MaSV, Hoten, NgSinh, MaLop),

MONHOC(MaMon, TenM, SDVHT) và

KETQUA (MaSV, MaMon, Diem)

Ta có mối quan hệ giữa các thực thể đó là:

LOP

MaLop

TenLop

Khoa

SINHVIEN

KETQUA

MONHOC

MaSV

Hoten

NgSinh

MaLop

MaSV

MaMon

Diem

MaMon

TenM

SDVHT

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

11

- Mô hình dữ liệu quan hệ: Làm việc trên bảng hay trên quan hệ trong

đó: Mỗi cột là một thuộc tính, mỗi dòng là một bộ (một bản ghi).

+ Các ưu điểm của mô hình dữ liệu quan hệ

o Cấu trúc dữ liệu dễ dùng, không cần hiểu biết sâu về kỹ thuật

cài đặt.

o Cải thiện tính độc lập dữ liệu và chương trình.

o Cung cấp ngôn ngữ thao tác phi thủ tục.

o Tối ưu hoá cách truy xuất dữ liệu.

o Tăng tính bảo mật và toàn vẹn dữ liệu.

o Cung cấp các phương pháp thiết kế có hệ thống. Và mở ra cho

nhiều loại ứng dụng (lớn và nhỏ).

+ Khoá của quan hệ:

o Khoá của quan hệ (key): Là tập các thuộc tính dùng để phân biệt

hai bộ bất kỳ trong quan hệ.

o Khoá ngoại của quan hệ (Foreign Key): Một thuộc tính được gọi

là khoá ngoại của quan hệ nếu nó là thuộc tính không khoá của

quan hệ này nhưng là thuộc tính khoá của quan hệ khác.

1.5.2. Các chức năng của hệ quản trị CSDL quan hệ

Các chức năng của hệ quản trị CSDL quan hệ có thể được phân thành

các tầng chức năng như hình 1.3:

- Tầng giao diện (Interface layer): Quản lý giao diện với các ứng dụng.

Các chương trình ứng dụng CSDL được thực hiện trên các khung nhìn (view)

của CSDL. Ðối với một ứng dụng, khung nhìn rất có ích cho việc biểu diễn

một hình ảnh cụ thể về CSDL (được dùng chung bởi nhiều ứng dụng).

Khung nhìn quan hệ là một quan hệ ảo, được dẫn xuất từ các quan hệ cơ

sở (base relation) bằng cách áp dụng các phép toán đại số quan hệ.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

12

Quản lý khung nhìn bao gồm việc phiên dịch câu vấn tin người dùng trên

dữ liệu ngoài thành dữ liệu khái niệm. Nếu câu vấn tin của người dùng được

diễn tả bằng các phép toán quan hệ, câu vấn tin được áp dụng cho dữ liệu khái

niệm vẫn giữ nguyên dạng này.

- Tầng điều khiển (Control Layer): chịu trách nhiệm điều khiển câu vấn

tin bằng cách đưa thêm các vị từ toàn vẹn ngữ nghĩa và các vị từ cấp quyền.

- Tầng xử lý vấn tin (Query processing layer): chịu trách nhiệm ánh xạ

câu vấn tin thành chuỗi thao tác đã được tối ưu ở mức thấp hơn.

Tầng này liên quan đến vấn đề hiệu năng. Nó phân rã câu vấn tin thành

một cây biểu thị các phép toán đại số quan hệ và thử tìm ra một thứ tự “tối

ưu” cho các phép toán này. Kết xuất của tầng này là câu vấn tin được diễn tả

bằng đại số quan hệ hoặc một dạng mã ở mức thấp.

- Tầng thực thi (Execution layer): Có trách nhiệm hướng dẫn việc thực

hiện các hoạch định truy xuất, bao gồm việc quản lý giao dịch (uỷ thác, tái

khởi động) và động bộ hoá các phép đại số quan hệ. Nó thông dịch các phép

toán đại số quan hệ bằng cách gọi tầng truy xuất dữ liệu qua các yêu cầu truy

xuất và cập nhật.

- Tầng truy xuất dữ liệu (data access layer): Quản lý các cấu trúc dữ liệu

dùng để cài đặt các quan hệ (tập tin, chỉ mục). Nó quản lý các vùng đệm bằng

cách lưu tạm các dữ liệu thường được truy xuất đến nhiều nhất. Sử dụng tầng

này làm giảm thiểu việc truy xuất đến đĩa.

- Tầng duy trì nhất quán (Consistency layer): chịu trách nhiệm điều

khiển các hoạt động đồng thời và việc ghi vào nhật ký các yêu cầu cật nhật.

Tầng này cũng cho phép khôi phục lại giao dịch, hệ thống và thiết bị sau khi

bị sự cố.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

13

Hình 1.3. Các chức năng của hệ quản trị CSDL quan hệ

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

14

Chương 2: CÁC CÂU LỆNH SQL CƠ BẢN

Ngôn ngữ SQL (Structured Query Language) là ngôn ngữ truy vấn có

cấu trúc, dùng để thao tác với dữ liệu trong cơ sở dữ liệu cũng như tạo và thay

đổi cấu trúc của các cơ sở dữ liệu. Trong chương này ta sẽ trình bày một số

câu lệnh SQL cơ bản.

2.1. CÁC CÂU LỆNH ĐỊNH NGHĨA DỮ LIỆU

2.1.1. Lệnh CREATE

- Ý nghĩa: Lệnh CREATE dùng để tạo các đối tượng cơ sở dữ liệu như

các bảng, các view, các tệp chỉ số .v.v…

- Cú pháp:

+ CREATE TABLE <Tên bảng>(<Danh sách: Tên_cột

Kiểu_cột> <Điều_kiện_kiểm_soát_dl >)

+ CREATE VIEW <Tên View>(<Danh sách: Tên_cột

Kiểu_cột> <Điều_kiện_kiểm_soát_dl >) AS Q; với Q là một

khối câu lệnh SELECT định nghĩa khung nhìn (view).

+ CREATE [UNIQUE] INDEX <tên chỉ số> ON <Ten

bảng>(Tên cột [ASC|DESC])

- Một số kiểu dữ liệu: Integer - số nguyên; float- dấu phảy động; char -

ký tự, datetime- ngày tháng, boolean,…

Ví dụ 2.1. Sử dụng câu lệnh CREATE.

+ CREATE TABLE S (S# Integer NOT NULL, SNAME

Char(30), STATUS Integer, CITY Char(50)) PRIMARY

KEY (S#);

+ CREATE VIEW vieS (S# Integer NOT NULL, SNAME

Char(30)) AS SELECT S#, SNAME FROM S;

+ CREATE TABLE PHONGBAN (MaPB Char(5) NOT

NULL, TenPB Char(30)) PRIMARY KEY (MaPB);

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

15

+ CREATE TABLE NHANVIEN (MaNV Char(5) NOT

NULL, TenNV Char(30), Ngaysinh date, MaPB Char(5))

PRIMARY KEY (MaNV) FOREIGN KEY (MaPB)

REFERENCES TO PHONGBAN;

+ CREATE INDEX index1 ON SP(S# ASC, P# DESC)

2.1.2. Lệnh thay thế sửa đổi ALTER

- Ý nghĩa: Dùng để thay đổi cấu trúc lược đồ của các đối tượng CSDL.

- Cú pháp:

+ ALTER TABLE <Tên bảng> <Thực hiện các lệnh trên cột>

Các lệnh trên cột có thể là:

• Xóa một cột: Delete <tên cột>

• Thêm một cột: Add <Tên cột>

• Thay đổi tên cột: Change column <Tên cột>To<Tên cột>

• Xóa khóa chính: Drop PRIMARY KEY

• Xóa khóa ngoại: Drop FOREIGN KEY

• Thiết lập khóa chính: PRIMARY KEY (Tên cột)

• Thiết lập khóa ngoại:

FOREIGN KEY (Tên cột) REFERENCES TO <tên bảng ngoài>

+ ALTER VIEW <Tên View>(<Danh sách: Tên_cột Kiểu_cột>

<Điều_kiện_kiểm_soát_dl >) AS Q; với Q là một khối câu lệnh

SELECT định nghĩa khung nhìn (view).

Ví dụ 2.2. Thay đổi cấu trúc của bảng NHANVIEN

ALTER TABLE NHANVIEN Add Quequan char(50);

ALTER TABLE NHANVIEN Delete Ngaysinh;

Ví dụ 2.3. Thay đổi khung nhìn vieS

CREATE VIEW vieS AS SELECT S#, SNAME, CITY FROM S

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

16

2.1.3. Xoá cấu trúc DROP

- Ý nghĩa: Dùng để xóa các đối tượng cơ sở dữ liệu như Table, View,

Index, .v.v…

- Cú pháp:

DROP TABLE <Tên bảng>

DROP VIEW <Tên view>

DROP INDEX <Tên index>

2.2. CÁC CÂU LỆNH CẬP NHẬT DỮ LIỆU

2.2.1. Lệnh Insert

- Ý nghĩa: Dùng để chèn một hàng hoặc một số hàng cho bảng.

- Cú pháp:

+ INSERT INTO <Tên bảng> (Danh sách các cột) VALUES

(Danh sách các giá trị) hoặc

+ INSERT INTO <Tên bảng> (Danh sách các cột) (Các câu hỏi

con);

Ví dụ 2.4. Chèn dữ liệu vào bảng S.

INSERT INTO S (S#, SNAME, STATUS)

VALUES (s1, Smith, 20, Paris);

INSERT INTO S

SELECT * FROM W WHERE CITY="Paris";

2.2.2. Lệnh Update

- Ý nghĩa: Dùng để sửa đổi dữ liệu.

- Cú pháp:

UPDATE <Tên bảng>

SET <Tên_cột_1=Biểu_thức_1, Tên_cột_2=Biểu_thức_2,… >

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

17

[WHERE <điều kiện>]

Ví dụ 2.5. Sử dụng lệnh Update

UPDATE SINHVIEN SET TenSV="Nguyễn Thị Hạnh"

Where MaSV="20042390"

UPDATE HangHoa SET Dongia=Dongia*1.1

Where TenHH IS LIKE 'Bia %' and Dongia<3500

2.2.2. Lệnh Delete

- Ý nghĩa: Xoá một số hàng trong bảng.

- Cú pháp:

DELETE FROM <Tên bảng> WHERE <Điều kiện>

Ví dụ 2.6. Xoá tất cả các hàng trong bảng KETQUA có trường Diem<5

DELETE FROM KETQUA WHERE Diem<5;

2.3. KIỂM SOÁT DỮ LIỆU

2.3.1. Trao quyền GRANT

- Ý nghĩa: Dùng để trao quyền cho một acount nào đó.

- Cú pháp:

GRANT <Quyền> ON <Tên bảng/ Tên View> TO <user>

[WITH GRANT OPITION]

Các quyền có thể trao là: All, Select, update, delete, insert, index, alter,

read, write,…

User có thể là: Public, tên một user cụ thể,…

- Chú ý: Nếu được trao quyền với chỉ định WITH GRANT OPITION thì

anh ta có thể trao lại quyền ấy cho người khác.

Ví dụ 2.7. Trao quyền Select cho acount Lannt

GRANT Select ON SINHVIEN TO Lannt WITH GRANT OPITION

2.3.2. Thu hồi quyền REVOTE

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

18

- Ý nghĩa: Dùng để thu hồi quyền của một acount nào đó.

- Cú pháp:

REVOTE <Quyền> ON <Tên bảng/ Tên View> FROM <user>

Ví dụ 2.8. Thu hồi quyền Select của acount Lannt

REVOTE Select ON SINHVIEN FROM Lannt;

2.4. TRUY VẤN DỮ LIỆU

Khối câu lệnh phổ dụng: SELECT - FROM – WHERE. Ta có thể sử

dụng theo cú pháp chung như sau:

SELECT [*| DISTINCT] <Danh sách các cột [AS <Bí danh>]>

FROM <Danh sách Tên bảng/Tên View>

[WHERE <Biểu thức điều kiện>]

[GROUP BY <Danh sách cột>]

[HAVING <Điều kiện>]

[ORDER BY <Tên cột/ Số thứ tự cột/Biểu thức> [ASC/DESC]]

2.4.1. Tìm kiếm theo câu hỏi đơn giản

- Tìm kiếm đơn giản:

+ Nếu xuất hiện giá trị * nghĩa là xem toàn bộ các cột của bảng.

Select * From SINHVIEN;

+ Nếu sử dụng DISTINCT thì sẽ lấy giá trị đại diện.

Select Distinct S#, P# From SP;

- Xử lý xâu: dùng toán tử [NOT] LIKE <Mẫu so sánh>

+ Dùng dấu gạch dưới để thay cho một ký tự.

+ Dùng dấu % để thay cho một dãy các ký tự tuỳ ý.

Ví dụ 2.9. Cho bảng hồ sơ sinh viên HOSOSV(MaSV, Hodem, TenSV,

Ngaysinh, MaLop). Hãy cho biết mã và họ tên sinh viên có hai chữ đầu là 'Ba'

Select MaSV, Hodem+TenSV as Hoten

From HOSOSV

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

19

Where TenSV like 'Ba%'

Ví dụ 2.10. Cho bảng hồ sơ sinh viên HOSOSV(MaSV, Hodem, TenSV,

Ngaysinh, MaLop). Hãy cho biết mã và họ tên sinh viên có không có hai chữ

đầu là 'Ba'

Select MaSV, Hodem+TenSV as Hoten

From HOSOSV

Where TenSV like 'Ba%'

- Sử dụng Between và IN để xác định phạm vi:

Ví dụ 2.11. Cho bảng thông tin sách mượn SACHMUON(MaBD,

MaSach, NgayMuon, NgayTra). Hãy cho biết mã các bạn đọc mượn sách của

thư viện trong khoảng ngày {1/1/2008} và {31/3/2008}

Select MaBanĐoc

From SACHMUON

Where NgayMuon Between {1/1/2000} and {31/3/2000}

2.4.2. Sử dụng các hàm thư viện

Các hàm thư viện thực hiện các thao tác như thống kê dữ liệu, tính toán

dữ liệu có sẵn như:

•

Count(): Dùng để đếm các bảng ghi,

Max(): Trả về giá trị lớn nhất của một tập hợp các giá trị,

Min(): Trả về giá trị lớn nhất của một tập hợp các giá trị,

Sum(): Trả về tổng giá trị của một tập hợp các giá trị,

Avg():Trả về giá trị trung bình của một tập hợp các giá trị,

Ví dụ 2.12.: Cho bảng DIEM(MaSV, MaMH, DiemL1, DiemL2). Hãy

xem sinh viên có mã SV061001 đã tham gia thi bao nhiêu môn:

Select Count(MaMH) AS Tongso

From DIEM

where MaSV='SV061001';

Ví dụ 2.13: Cho biết điểm thi cao nhất lần 1 của môn có mã '03AB'

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị CSDL

20

Select Max(DiemL1) as DiemCN

From DIEM

Where MaMH='03AB';

Ví dụ 2.14. Cho biết chênh lệnh giữa điểm thi cao nhất và thấp nhất của

môn có mã môn học là '03AB'

Select (Max(DiemL1) - Min (DiemL1)) As Chenh_Lenh

From DIEM

where MaMT='03AB';

2.4.3. Tìm kiếm nhờ các mệnh đề

- Sử dụng phân nhóm GROUP BY: Mệnh đề GROUP BY được sử dụng

để tạo hiệu quả sắp xếp và tính toán theo từng phân nhóm.

Ví dụ 2.15. Cho biết tình hình thi của từng sinh viên:

Select MaSV, MaMH, DiemL1, DiemL2

From DIEM

Group By MaSV

- Sử dụng HAVING: Mệnh đề HAVING dùng để đặt điều kiện lọc cho

các phân nhóm con.

Ví dụ 2.16. Cho bảng mặt hàng đã được cung cấp SP(S#, P#, QTY). Tìm

mã những nhà cung cấp cung cấp ít nhất 2 mặt hàng:

Select S# From SP

Group By S#

Having Count(Distinct P#)>=2;

Ví dụ 2 .17. Tìm mã các sinh viên không có môn thi nào dưới 5

Select MaSV From DIEM

Group By MaSV

Having Min(Diem)>=5;

Chú ý: Having đi sau Group By để đặt điều kiện chọn lọc ra những phân

nhóm thoả mãn điều kiện sau Having. Nếu không có từ khoá Group By thì

Having sẽ tác động trên toàn bảng coi như một phân nhóm duy nhất.

Biên soạn: Chu Thị Hường Bộ môn: Các hệ thống thông tin Khoa Công nghệ Thông tin

Bài giảng Hệ quản trị cơ sở dữ liệu - Chu Thị Hường