Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 3
− Quản lý vùng đệm.
Quản lý giao dịch (Transaction management)
Thông thường, một số thao tác trên cơ sở dữ liệu tạo thành một đơn vị logic công việc.
Ta hãy xét ví dụ chuyển khoản, trong đó một số tiền x được chuyển từ tài khoản A
(A:=A-x) sang một tài khoản B (B:=B+x). Một yếu tố cần thiết là cả hai thao tác này hoặc
cùng xảy ra hoặc không hoạt động nào xảy ra cả. Việc chuyển khoản phải xảy ra trong
tính toàn thể của nó hoặc không. Yêu cầu toàn thể-hoặc-không này được gọi là tính
nguyên tố (atomicity). Một yếu tố cần thiết khác là sự thực hiện việc chuyển khoản bảo
toàn tính nhất quán của cơ sở dữ liệu: giá trị của tổng A + B phải được bảo toàn. Yêu cầu
về tính chính xác này được gọi là tính nhất quán (consistency). Cuối cùng, sau khi thực
hiện thành công hoạt động chuyển khoản, các giá trị của các tài khoản A và B phải bền
vững cho dù có thể có sự cố hệ thống. Yêu cầu về tính bền vững này được gọi là tính lâu
bền (durability).
Một giao dịch là một tập các hoạt động thực hiện chỉ một chức năng logic trong một
ứng dụng cơ sở dữ liệu. Mỗi giao dịch là một đơn vị mang cả tính nguyên tố lẫn tính nhất
quán. Như vậy, các giao dịch phải không được vi phạm bất kỳ ràng buộc nhất quán nào:
Nếu cơ sở dữ liệu là nhất quán khi một giao dịch khởi động thì nó cũng phải là nhất
quán khi giao dịch kết thúc thành công. Tuy nhiên, trong khi đang thực hiện giao dịch,
phải cho phép sự không nhất quán tạm thời. Sự không nhất quán tạm thời này tuy là cần
thiết nhưng lại có thể dẫn đến các khó khăn nếu xảy ra sự cố.
Trách nhiệm của người lập trình là xác định đúng đắn các giao dịch sao cho bảo toàn
tính nhất quán của cơ sở dữ liệu.
Đảm bảo tính nguyên tố và tính lâu bền là trách nhiệm của hệ cơ sở dữ liệu nói chung
và của thành phần quản trị giao dịch (transaction-management component ) nói riêng.
Nếu không có sự cố, tất cả giao dịch hoàn tất thành công và tính nguyên tố được hoàn
thành dễ dàng. Tuy nhiên, do sự hiện diện của các sự cố, một giao dịch có thể không hoàn
tất thành công sự thực hiện của nó. Nếu tính nguyên tố được đảm bảo, một giao dịch thất
bại không gây ảnh hưởng đến trạng thái của cơ sở dữ liệu. Như vậy, cơ sở dữ liệu phải
được hoàn lại trạng thái của nó trước khi giao dịch bắt đầu. Hệ quản trị cơ sở dữ liệu phải
có trách nhiệm phát hiện sự cố hệ thống và trả lại cơ sở dữ liệu về trạng thái trước khi xảy
ra sự cố.
Khi một số giao dịch tương tranh cập nhật cơ sở dữ liệu, tính nhất quán của dữ liệu có
thể không được bảo toàn, ngay cả khi mỗi giao dịch là chính xác. Bộ quản trị điều khiển
tương tranh (concurency-control manager) có trách nhiệm điều khiển các tương tác giữa
các giao dịch đồng thời để đảm bảo tính thống nhất của CSDL.
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 4
Thành phần Kiểm tra chứng thực và toàn vẹn (Authorization and Integrity Manager)
Kiểm tra ràng buộc toàn vẹn và quyền truy cập dữ liệu của người dùng cơ sở dữ liệu.
Thành phần quản lý giao dịch (Transaction manager)
Thành phần này đảm bảo rằng cơ sở dữ liệu luôn ở trạng thái nhất quán. Nó quản lý việc
thực thi các yêu cầu thao tác dữ liệu và đảm bảo các truy cập dữ liệu đồng thời không dẫn
đến mâu thuẫn.
Thành phần quản lý file (File manager): quản lý việc cấp phát không gian trên đĩa. Các
file được dùng để chứa tập các dữ liệu tương tự nhau. Hệ quản lý file quản lý các file độc
lập, giúp đỡ nhập và lấy các mẩu tin. Thành phần quản lý file thiết lập và duy trì danh
sách các cấu trúc và chỉ mục được định nghĩa trong lược đồ trong. Thành phần quản lý
file có thể:
o Tạo file.
o Xóa file.
o Cập nhật mẩu tin trong file.
o Lấy một mẩu tin từ một file.
Thành phần quản lý vùng đệm (Buffer Manager): có trách nhiệm chuyển dữ liệu từ đĩa
lưu trữ vào bộ nhớ chính theo yêu cầu của chương trình.
2. Bộ xử lý truy vấn (Query Processor)
Thực hiện câu truy vấn nhận được từ người dùng qua các giai đoạn phân tích (parser), tối
ưu hóa câu hỏi (query optimizer), lập kế hoạch thực hiện (plan executor) và thực hiện tính
toán (operator evaluator).
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 5
Chương 2
XÂY DỰNG, QUẢN LÝ VÀ KHAI THÁC CƠ SỞ DỮ LIỆU
I. Khái niệm cơ sở dữ liệu
• Ở mức logic, một cơ sở dữ liệu (CSDL) gồm:
− Các bảng (tables) chứa dữ liệu có cấu trúc và các ràng buộc (constraint) định nghĩa
trên các bảng.
− Các khung nhìn (view).
− Các thủ tục/ hàm.
− Các vai trò (role) và người dùng (user).
− …
• Ở mức lưu trữ vật lý, một database của SQL Server được lưu trữ bởi 3 loại tập tin:
− Tập tin dữ liệu (data file) gồm có:
1 tập tin dữ liệu chính (primary data file), thường có phần mở rộng “mdf”: chứa
các dữ liệu khởi đầu của database.
0-n tập tin dữ liệu thứ cấp (secondary data file), thường có phần mở rộng
“ndf”: chứa các dữ liệu không lưu trữ hết trong tập tin dữ liệu chính.
− Tập tin nhật ký giao tác (transaction log file) gồm có 1-n tập tin nhật ký, thường có
phần mở rộng “ldf”: chứa các thông tin về nhật ký giao tác, dùng để phục hồi
database sau khi xảy ra sự cố.
II. Tạo cơ sở dữ liệu
1. Cú pháp lệnh tạo CSDL
Create Database database_name
[ On [Primary]
{ file_spec [,…n] }
]
[ Log on
{ file_spec [,…n] }
]
với
file_spec :: = ( Name = logical_file_name,
Filename = 'os_file_name '
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 6
[ , Size = size ]
[ , Maxsize = { max_size | Unlimited } ]
[ , Filegrowth = growth_increment ] )
Mặc định, các tập tin dữ liệu và log được lưu trong thư mục MSSQL\ Data của thư mục
cài đặt SQL Server.
Ví dụ
• Ví dụ 1: tạo CSDL QLSinhVien theo các quy định mặc định của SQL Server
Create Database QLSinhVien
• Ví dụ 2: tạo CSDL QLSinhVien với khai báo tên file logic, thư mục lưu tập tin dữ liệu
chính, kích thước, …
Create Database QLSinhVien
On
( Name = QLSV_Data
Filename = ‘C:\ \ QLSV_Data.mdf ’,
Size = 1,
Filegrowth = 10% )
•
Ví dụ 3
Create Database QLSinhVien
On
( Name = QLSV_Data1,
Filename = ‘C:\ \ QLSV_Data.mdf ’,
Size = 1,
Maxsize = 10 MB,
Filegrowth = 1 MB ) ,
( Name = QLSV_Data2 ,
Filename = ‘C:\ \QLSV_Data1.ndf’ )
Log on
( Name = QLSV_Log,
Filename = ‘D:\ \QLSV_Log.ldf’ )
2. Xoá một CSDL đã tồn tại
Drop Database database_name
3. Thay đổi một CSDL
Alter Database database_name ….
Dùng để:
– Thêm/xoá/thay đổi các tập tin.
– Thay đổi các tùy chọn cho CSDL.
III. Kiểu dữ liệu
SQL Server cung cấp các kiểu dữ liệu:
1. Số
– Số nguyên: bit, tinyint, smallint, int, bigint.
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 7
– Số thực
Floating point:
o float(n)
o real = float(24)
Fixed point
o Decimal(p,s)
o Numeric(p,s)
2. Chuỗi
char(n): chuỗi có độ dài cố định.
nchar(n): chuỗi (theo mã Unicode) có độ dài cố định.
varchar(n): chuỗi có độ dài thay đổi.
nvarchar(n): chuỗi (theo mã Unicode) có độ dài thay đổi.
text: kiểu dữ liệu cho phép chứa chuỗi có kích thước hơn 8KB.
ntext: kiểu dữ liệu cho phép chứa chuỗi (theo mã Unicode) có kích thước hơn
8KB.
3. Ngày giờ
Datetime.
Smalldatetime
4. Kiểu người dùng tự định nghĩa
a. Định nghĩa một kiểu dữ liệu:
sp_addtype type_name, system_type [, ‘null_type’][, ‘owner’]
Ví dụ: định nghĩa kiểu dữ liệu Code là kiểu chuỗi gồm 10 ký tự cho phép để trống
Exec sp_addtype Code, char(10), ‘NULL’
b. Xóa một kiểu dữ liệu người dùng định nghĩa:
sp_droptype ‘type_name’
IV. Tạo và quản lý bảng
1. Tạo bảng
– Xác định các cột (các thuộc tính) của bảng.
– Xác định khóa chính.
– Xác định các thuộc tính null/ not null.
– Xác định thuộc tính identity (nếu có) (phải là kiểu số nguyên).
Lưu ý:
– Luôn tạo khóa chính cho một bảng.
– Ràng buộc khóa ngoại nên được tạo sau khi đã tạo xong tất cả các bảng liên quan.
a. Cú pháp lệnh tạo bảng
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 8
Create table Table_name
(
{ Column_name Data_type [null | not null]
[default default_value ]
[identity [( seed, increment)] ]
} [,…n]
[, constraint constraint_name primary key ( Column_name [,…n] ) ]
)
Ví dụ
: Tạo bảng học sinh có khóa chính là (STT, Lop)
Create table HOCSINH
( STT tinyint not null,
Lop char(5) not null default ‘11A1’ ,
HoTen nvarchar(30) not null,
NgaySinh datetime not null,
DiaChi nvarchar(100),
constraint pk_HS primary key (STT, Lop)
)
b. Thay đổi cấu trúc bảng / xóa bảng
Thay đổi cấu trúc bảng là thực hiện:
–
Thêm/ xoá/ cập nhật kiểu dữ liệu của một cột (column).
–
Thêm/ xoá/ kiểm tra/ không kiểm tra ràng buộc (constraint).
–
Cho phép/ không cho phép trigger hoạt động.
Cú pháp: Alter table <tên_bảng>
…
Xóa bảng: xoá dữ liệu và cấu trúc của bảng
Cú pháp: Drop table <tên_bảng>
Ví dụ
− Thêm thuộc tính DanToc vào bảng HOCSINH:
Alter table HOCSINH
Add DanToc nvarchar(20) null default ‘Kinh’
− Sửa kiểu dữ liệu của thuộc tính NgaySinh thành kiểu SmallDatetime:
Alter table HOCSINH
Alter column NgaySinh SmallDatetime not null
2. Quản lý bảng
− Các tên bảng, tên ràng buộc không được trùng nhau trong cùng một database.
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 9
− Tên các cột trong cùng một bảng không được trùng nhau.
− Thông tin về các bảng, các ràng buộc được lưu trong bảng hệ thống sysobjects
Ví dụ: đọc thông tin về các bảng trong database hiện hành:
Select * from sysobjects where type = ‘U’
− Một số thủ tục SQL Server cung cấp để quản lý bảng và cấu trúc bảng:
o sp_databases
o sp_tables [‘table_name’] [, ‘owner’][,‘database_name’][, “ ‘type’ ”]
Ví dụ: Exec sp_tables null, null, null, “ ‘TABLE’ ”
o sp_help [object_name]
sp_help cho biết các thông tin về đối tượng bất kỳ trong database (đối tượng
có chứa trong sysobjects).
Ví dụ: Exec sp_help HOCSINH
o sp_columns object [, owner] [, database] [,column]
o sp_helpconstraint ‘table_name’
o …
3. Cài đặt ràng buộc toàn vẹn đơn giản
SQL Server cung cấp sẵn cơ chế để kiểm tra các loại ràng buộc toàn vẹn (RBTV) sau:
o Khóa chính (primary key constraint).
o Khóa ngoại (foreign key constraint).
o Giá trị duy nhất (unique constraint).
o Check constraint (Kiểm tra ràng buộc miền giá trị).
Có thể khai báo ràng buộc trong lúc tạo bảng hoặc khi bảng đã tồn tại. Thông thường
nên khai báo ràng buộc toàn vẹn trước khi nhập dữ liệu.
a. Khai báo ràng buộc trong lúc tạo bảng
Cú pháp
:
Create table Table_name
( …
[, constraint Constraint_name
{ primary key (Column_name [,…n])
| unique ( Column_name [,…n])
| check ( logical_expression ) }
] […n]
)
Ví dụ
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 10
Create table SinhVien
(
MaSV char(10) not null,
HoTen nvarchar(30) not null,
Nam tinyint,
CMND char(10),
Khoa char(5),
constraint pk_SV primary key (MaSV),
constraint u_CMND unique (CMND),
constraint chk_Nam check (Nam > 0 and Nam <= 4)
)
b. Khai báo ràng buộc trên bảng đã tồn tại
Cú pháp:
Alter table table_name
[with check| with nocheck] Add
{ constraint constraint_name
{ primary key ( column_name [,…n] )
| unique ( column_name [,…n] )
| check ( logical_expression )
| foreign key ( column_name [,…n] )
references ref_table ( ref_column [,…n] )
[ on delete {cascade| no action} ]
[ on update {cascade| no action} ]
} [,…n]
Ví dụ
/* giả sử đã tồn tại bảng KHOA( MaKhoa
, …) */
Alter table SINHVIEN
with check add
constraint u_CMND unique (CMND),
constraint chk_Nam check (Nam in (1, 2, 3, 4) ),
constraint fk_SV_maKhoa foreign key (Khoa),
references KHOA(MaKhoa)
c. Kiểm tra / không kiểm tra ràng buộc
Cú pháp:
Alter table Table_name
{Check| Nocheck} constraint { All | constraint_name [,…n] }
Ví dụ:
alter table SINHVIEN
nocheck constraint u_CMND, chk_Nam
d. Xoá ràng buộc
Cú pháp:
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 11
Alter table table_name
Drop { [constraint ] constraint_name } [,…n]
Ví dụ:
Alter table SINHVIEN
drop constraint u_CMND, chk_Nam
e. Rule
− Rule là một qui định chung được tạo ra trong một CSDL.
− Một rule có thể được áp dụng cho nhiều thuộc tính của nhiều bảng khác nhau, hoặc
cho các kiểu dữ liệu người dùng định nghĩa trong database.
Tạo rule
Cú pháp:
Create rule rule_name
as logical_expression
(trong đó “logical_expression” phải chứa một biến. Biến này tương ứng với đối tượng sẽ
được áp dụng rule).
Ví dụ:
create rule r_SoDuong
as @value >0
Kết buộc/ gỡ kết buộc rule
Kết buộc rule: dùng thủ tục:
sp_bindrule ‘rule_name’, ‘object’, [ ‘futureonly’ ]
trong đó:
Tùy chọn futureonly chỉ dùng khi kết buộc rule với kiểu dữ liệu người dùng
định nghĩa, có nghĩa các cột thuộc kiểu dữ liệu này trước đó không bị ảnh
hưởng bởi rule.
Ví dụ: sp_bindrule ‘r_SoDuong’, ‘SinhVien.Nam’
Rule mới kết buộc sẽ ngầm gỡ rule cũ trên đối tượng.
Gỡ kết buộc
sp_unbindrule ‘object’, [ ‘futureonly’ ]
Ví dụ: sp_unbindrule ‘SinhVien.Nam’
Xoá rule
Cú pháp: Drop rule {rule_name} [,…n]
Lưu ý: Chỉ xóa được rule khi nó không còn kết buộc với đối tượng nào.
V. Các thao tác trên dữ liệu
Chú ý khi thêm/ xóa/ cập nhật dữ liệu:
Bài giảng tóm tắt Hệ quản trị cơ sở dữ liệu 12
– Dữ liệu nhập phải phù hợp với kiểu dữ liệu.
– Đảm bảo các ràng buộc toàn vẹn.
– Định dạng giá trị kiểu chuỗi unicode, kiểu datetime.
– Nhập giá trị rỗng (Null).
1. Các dạng lệnh insert
− Thêm từng dòng dữ liệu vào bảng
Insert [into] Table_name[ (column_name[,…n] )]
values ( value [,…n] )
− Thêm 0-n dòng dữ liệu từ bảng khác/ từ kết quả của một câu truy vấn
Insert [into] Table_name
Select_statement
Lưu ý:
trong câu select, ta có thể đọc dữ liệu từ các bảng trong database khác.
Khi đó, tên bảng được viết đầy đủ như sau:
Database_name.Owner.Table_name
Ví dụ: select * from QLSinhVien.dbo.SinhVien
2. Lệnh cập nhật dữ liệu
update table_name
set column_name_1= value1,…, column_name_m= value_m
[
where conditional_expression]
3. Lệnh xoá dữ liệu
delete [from] table_name
[
where conditional_expression]
VI. Truy vấn dữ liệu
1. Câu truy vấn tổng quát
Cú pháp tổng quát của câu truy vấn dữ liệu:
SELECT [tính chất] <danh sách các thuộc tính_1>
FROM <danh sách các table hoặc query/view [as alias] >
[WHERE <điều kiện_1>]
[GROUP BY <danh sách các thuộc tính_2>]
[HAVING <điều kiện_2>]
[ORDER BY <danh sách các thuộc tính_3> [ASC | DESC]]
trong đó:
− Tính chất là một trong các từ khóa: ALL (chọn ra tất cả các dòng trong bảng),
DISTINCT (loại bỏ các dòng trùng lắp thông tin), TOP <n> (chọn n dòng đầu
tiên thỏa mãn điều kiện).
Không có nhận xét nào:
Đăng nhận xét