Giáo Trình SQL Bài 4

1
Làm việc với CSDL Quan Hệ
Relational Database :RDB
2
I. Thiết kế CSDL: 1. Khái niệm
Thiết kế CSDL là bước vô cùng quan trọng bấc nhất
Quá trình thiết kế CSDL độc lập với RDBMS (Relational Database Management System)
Một số lưu ý khi thiết kế CSDL:
Các Bảng và tên của bảng (thực thể)
Tên các Cột của Bảng (thuộc tính)
Các đặc tính của cột: giá trị duy nhất, null, kiểu...
Khóa chính (Primary key): mỗi bảng chỉ có một khóa chính, mặc dù không bắt buộc nhưng nên có
Các quan hệ (Relationship) giữa các bảng, những phụ thuộc trong các bảng gọi là quan hệ. Để định nghĩa 1 quan hệ ta dùng khòa ngoại (forein key) tham chiếu đến khóa chính của bảng khác.
3
2. Các kiểu quan hệ:
One-to-One: Quan hệ 1 dòng trong bảng có quan hệ đến chỉ 1 dòng trong bảng quan hệ
4
One-to-Many: Mỗi dòng trong bảng được liên quan đến một hoặc nhiều dòng trong bảng quan hệ.
5
Many-to-Many: Nhiều dòng trong bảng liên quan đến nhiều dòng trong bảng khác
VD1: 1 quyển sách có thể có nhiều tác giả, một tác giả có thể có nhiều quyển sách.
VD1: 1 sản phẩm có nhiều người mua, 1 người có thể mua nhiều sản phẩm

Ghi chú: QH Many-to-Many khi truy xuất dữ liệu không thể chính xác và đúng đắn theo yêu cầu. Do đó quan hệ Many-to-Many không thể tồn tại trong thiết kế CSDL, do đó ta phải phân tích QH Many-to-Many thành QH One-to-Many
6
Phân tích QH Many-to-Many thành QH One-to-Many bằng cách tạo một table thứ 3 (gọi là table chức năng). Các khóa chính của table 1 và 2 đưa sang table 3 làm khóa ngoại và thêm thuộc tính (cột) chức năng cho table 3
7
3. Các dạng chuẩn của RDB:
Trong thiết kế CSDL, việc tuân thủ ngặt nghèo những chuẩn là việc hết sức quan trọng, nó giúp cho việc quản trị dữ liệu có hiệu quả, khắc phục dư thừa, thông tin không trùng lắp, có tính nhất quán cao, thuận lợi trong quản trị dữ liệu lớn, hiệu quả với dữ liệu phức tạp
a. Dạng chuẩn 1: Tất cả các thuộc tính ở dạng giá trị đơn hoặc không ở dạng lặp lại (VD-GT-T.31)



Khắc phục bằng cách tạo ra một bảng lưu trữ danh sách tác giả của sách
8
b. Dạng chuẩn 2: Là dạng chuẩn 1 đồng thời tất cả các thuộc tính không khóa phụ thuộc đầy đủ vào khóa chính (VD-GT-T31)





Xem xét trong bảng trên ta thấy mỗi hàng phụ thuộc vào khóa Sach_ID và SinhVien_ID, nhưng thông tin HocLucSV không phụ thuộc vào Sach_ID, nên thông tin này cần chuyển sang bảng về thông tin của sinh viên
9
c. Dạng chuẩn 3: Tất cả các thuộc tính không khóa phụ thuộc đầy đủ vào khóa và không phụ thuộc bắc cầu hoặc Dạng chuẩn 3 là Dạng chuẩn 2 và không phụ thuộc bắc cầu (VD-GT-T31)


Bảng trên bạn thấy mỗi lần sinh viên mượn sách số lượng sách có mã Sach_ID mà sinh viên có mã SinhVien_ID sẽ tăng lên và tổng số là Số lượng đã mượn, thông tin này là thông tin tích lũy theo Sach_ID, SinhVien_ID, NgayMuon.
10
II. Cấu trúc CSDL của SQL Server:
Như đã trình bày ở các bài trước một trong những đặc điểm của SQL Server 2000 là Multiple-Instance nên khi nói đến một (SQL) Server nào đó là ta nói đến một Instance của SQL Server 2000, thông thường đó là Default Instance. Một Instance của SQL Server 2000 có 4 system databases và một hay nhiều user database. Các system databases bao gồm:
Master: Chứa tất cả những thông tin cấp hệ thống (system-level information) bao gồm thông tin về các database khác trong hệ thống như vị trí của các data files, các login account và các thiết đặt cấu hình hệ thống của SQL Server (system configuration settings).
11
Tempdb: Chứa tất cả những table hay stored procedure được tạm thời tạo ra trong quá trình làm việc bởi user hay do bản thân SQL Server engine. Các table hay stored procedure này sẽ biến mất khi khởi động lại SQL Server hay khi ta disconnect.
Model: Database này đóng vai trò như một khuôn mẫu (template) cho các database khác. Nghĩa là khi một user database được tạo ra thì SQL Server sẽ copy toàn bộ các system objects (tables, stored procedures...) từ Model database sang database mới vừa tạo.
Msdb: Database này được SQL Server Agent sử dụng để hoạch định các báo động và các công việc cần làm (schedule alerts and jobs).
12
II. Cấu trúc CSDL của SQL Server: (tt)
Cấu trúc Logic một CSDL của SQL Server là tập hợp các Bảng (table) dùng để chứa dữ liệu và các đối tượng khác như: View, Index, Store Procedure, Constrains và Trrigers...
Cấu trúc vật lý của CSDL SQL Server gồm các file: Data (*.mdf, or *.ndf) và file Log (*.ldf)
Theo ngầm định data file là: *.mdf (Primary files)có thể không hoặc có nhiều file *.ndf (secondary files)
Log file: Có ít nhất một log file chức thông tin phục hồi các giao tác (transaction) *.log
13
Transaction Log làm việc thế nào?
Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong database:
Đầu tiên khi có một sự thay đổi data như Insert, Update, Delete được yêu cầu từ các ứng dụng,
SQL Server sẽ tải (load) data page tương ứng lên memory (vùng bộ nhớ này gọi là data cache), Sau đó data trong data cache được thay đổi (những trang bị thay đổi còn gọi là dirty-page).
Tiếp theo mọi sự thay đổi đều được ghi vào Transaction log file (gọi là write-ahead log).
Cuối cùng thì một quá trình gọi là Check Point Process sẽ kiểm tra và viết tất cả những transaction đã được commited (hoàn tất) vào dĩa cứng (flushing the page).
14
15
16
III. Tạo, hiệu chỉnh CSDL trong SQL Server:
a. Vài điều xem xét trước khi tạo CSDL:
CSDL nên được thiết kế theo đúng dạng chuẩn 3
Quyền tạo trước hết phải là thành viên sysadmin hoặc user được cấp quyền tạo
User: người tạo ra CSDL trở thành chủ (Owner) CSDL đó
Tên CSDL phải tạo theo nguyên tắc định danh
b. Tạo CSDL:
Có ba cách tạo và hiệu chỉnh CSDL:
C1: Dùng Enterprise Manager (GT-T.35)
C2: Dùng Create Database Vizard (GT-T.36)
C3: Dùng câu lệnh Create DataBase (T-SQL)
17
Tạo CSDL bằng lệnh Create DataBase (T-SQL)
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = `os_file_name`
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
18
Tạo CSDL bằng lệnh Create DataBase (T-SQL)
CREATE DATABASE SalesDB
ON
( NAME = Sales_dat,
FILENAME = `d:datasale_data.mdf`,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = `Sales_log`,
FILENAME = `d:datasale_log.ldf`,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
19
Kiểm tra sự tồn tại
C1: Enterprise Manager kiểm tra sự tồn tại trong nhánh Database
C2: T-SQL: sp_helpDB
VD: sp_helpDB SalesDB

Xem, thay đổi thuộc tính
C1: Enterprise Manager:
C2: T-SQL
c. Thao tác trên CSDL của SQL Server: thường dùng Enterprise Manager hoặc T-SQL trong Query Analyzer
20
Systax:
ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}
< filespec > ::=
( NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = `os_file_name` ]
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] )
21
VD1:
ALTER DATABASE SalesDB
MODIFY FILE (NAME = `Sales_Log`, SIZE = 10MB)
GO

VD2:
ALTER DATABASE SalesDB
ADD FILE
( NAME = `Sales_Data2`,
FILENAME = `d:DataSales_Data.ndf `
SIZE = 10MB, MAXSIZE = 20MB)
GO

VD3:
ALTER DATABASE SalesDB
MODIFY NAME = Sales
GO
22
d. Xóa Database:

Có 2 cách tạo và hiệu chỉnh CSDL:
C1: Dùng Enterprise Manager (GT-T.39)
C2: Dùng T-SQL

Syntax: DROP DATABASE [, n …]

Tham số n chỉ ra là danh sách các database sẽ xóa

Vd1: DROP DATABASE SalseDB
Vd2: DROP DATABASE SalseDB, SinhVienDB
23
e. Đổi tên Database:

Dùng T-SQL với hàm sp_rename theo syntax:

sp_renamedb [ @dbname = ] `old_name` ,
    [ @newname = ] `new_name‘

Return Code Values 0 (success) or a nonzero number (failure)

Vd: EXEC sp_renamedb `accounting`, `financial`
24
IV. Tạo Script cho database và các Object:
(Xem giáo trình trang 39-42)