Bài giảng Hệ quản trị Cơ sở dữ liệu SQL Server - Bài 1: Thiết kế cơ sở dữ liệu với Management Studio

12/8/2018 Microsoft SQL Server 1 Bài 1: Thiết kế Cơ sở dữ liệu với Management Studio 2/8/2018 Microsoft SQL Server 2 Cơ sở dữ liệu trong SQL Server n Lưu trữ dữ liệu: các bảng, bảng ảo, thủ tục nội tại, n Cấu trúc vật lý của một CSDL bao gồm tối thiểu 2 tệp tin: - Tệp tin lưu trữ dữ liệu (data file): .mdf (primary data file) - Tệp tin lưu vết giao tác (transaction log file): .ldf (log file) n Các thông số về kích thước tệp tin: Logical name, File Type, Filegroup, Initial Size, Autogrowth, Path, File Name 2/8/2018 Microsoft SQL Server 3 Một số CSDL hệ thống n Master: CSDL chính, chứa thông tin các bảng hệ thống n Model: CSDL dạng khuôn mẫu n Msdb: Dùng để quản lý SQL Server Agent - dịch vụ thực hiện các xử lý mà người quản trị đã lập lịch: sao lưu, đồng bộ dữ liệu n Tempdb: CSDL tạm dùng để lưu các bảng tạm, các kết quả trung gian trong quá trình xử lý dữ liệu 2/8/2018 Microsoft SQL Server 4 Tạo mới một CSDL n Bước 1: Trong cửa sổ Object Explorer, kích chuột phải vào Databases, chọn New Database 22/8/2018 Microsoft SQL Server 5 n Bước 2: Lựa chọn các thông tin cho CSDL được tạo: 1. Đặt tên cho CSDL 2. Chọn chủ sở hữu 3. Chọn thuộc tính cho các tệp CSDL 3.1 Chọn kích thước khởi tạo 3.2 Chế độ tăng kích thước tự động 3.3 Đường dẫn tới tệp 2/8/2018 Microsoft SQL Server 6 § Bước 2: (tiếp) Chọn chế độ tăng kích thước cho các tệp CSDL Cho phép tự động tăng kích thước Tăng theo tỉ lệ % Tăng theo lượng MB Giới hạn kích thước tối đa Không giới hạn kích thước tối đa 2/8/2018 Microsoft SQL Server 7 Sao lưu CSDL n B1: Kích chuột phải vào CSDL cần sao lưu, chọn Tasks/Backup 2/8/2018 Microsoft SQL Server 8 n Lựa chọn các thông số và chỉ ra file đích CSDL nguồn File đích 32/8/2018 Microsoft SQL Server 9 Phục hồi CSDL n Để phục hồi CSDL: Trong menu chuột phải của Databases, chọn Restore Database rồi tiếp tục chỉ ra tên CSDL phục hồi, file nguồn (backup), 2/8/2018 Microsoft SQL Server 10 Phục hồi CSDL (tiếp) CSDL đích Chọn file sao lưu 2/8/2018 Microsoft SQL Server 11 Bảng dữ liệu n Là một kiểu tổ chức dữ liệu sử dụng trong MS SQL Server n Gồm các cột (còn gọi là trường) biểu thị các thuộc tính của dữ liệu và các dòng (còn gọi là các bản ghi) biểu thị các đối tượng 2/8/2018 Microsoft SQL Server 12 Các kiểu dữ liệu cơ bản n Kiểu dữ liệu nguyên: - Bigint 8 bytes - Int 4bytes - Smallint 2bytes (giá trị nằm trong đoạn [-32768..32767] - Tinyint 1byte (giá trị nằm trong đoạn [0..255]) - Bit 1 bit, nhận giá trị 0,1 hoặc null n Kiểu dữ liệu thực: - Decimal 17bytes - Numeric 17bytes - Float 8bytes - Real 4bytes 42/8/2018 Microsoft SQL Server 13 Các kiểu dữ liệu cơ bản (tiếp) n Kiểu dữ liệu chuỗi có độ dài cố định: - Char Nbytes (0<=N<=8000) n Kiểu dữ liệu chuỗi có độ dài biến đổi: - Varchar Nbytes (1<=N<=8000) - Text Nbytes (1<=N<=2 tỷ) n Kiểu dữ liệu dạng chuỗi dùng Font Unicode: - nchar 2*Nbytes (1<=N<=4000) - nvarchar 2*Nbytes (1<=N<=4000) - ntext 2*Nbytes (1<=N<=1tỷ) 2/8/2018 Microsoft SQL Server 14 Các kiểu dữ liệu cơ bản (tiếp) n Kiểu dữ liệu tiền tệ: - Money 8bytes - Smallmoney 4bytes n Kiểu dữ liệu ngày, giờ: - Datetimeoffset 8-10bytes - Dateime2 6-8bytes - Datetime: 8bytes - Smalldatetime: 4bytes - Date 3bytes - Time 3-5bytes 2/8/2018 Microsoft SQL Server 15 Các kiểu dữ liệu cơ bản (tiếp) n Kiểu dữ liệu dạng chuỗi nhị phân: - Binary Nbytes (1<=N<=8000) - varBinary Nbytes (1<=N<=8000) - Image Nbytes (1<=N<=2^31) n Sql_variant: lưu dữ liệu chưa xác định kiểu, trừ kiểu text, ntext, image, timestamp và sql_variant n Timestamp: dùng 8 bytes để lưu dữ liệu nhị phân được tạo tự động duy nhất trong CSDL n Uniqueidentifier: dùng 16 bytes lưu dữ dữ liệu nhị phân được tạo tự động duy nhất trong CSDL n Xml: lưu trữ dữ liệu theo định dạng Xml 2/8/2018 Microsoft SQL Server 16 Các kiểu dữ liệu cơ bản (tiếp) * Nhóm dữ liệu User-Defined Data Type: người dùng tự định nghĩa 52/8/2018 Microsoft SQL Server 17 Tạo bảng trong CSDL n Bước 1: Kích chuột phải vào đối tượng Tables, chọn New Table 2/8/2018 Microsoft SQL Server 18 n Bước 2: Nhập tên các trường và lựa chọn các thuộc tính tương ứng: Tên trường Kiểu dữ liệu Chấp nhận giá trị Null Danh sách các kiểu dữ liệu Các thuộc tính Đặt giá trị ngầm định hoặc ràng buộc dữ liệu 2/8/2018 Microsoft SQL Server 19 n Bước 3: Đặt tên và lưu bảng: Nhấn Ctrl + S hoặc kích chuột vào nút Save, hoặc vào menu File/SaveTable_1. Ở cửa sổ Choose Name, nhập tên bảng rồi nhấn OK: Nhập tên bảng 2/8/2018 Microsoft SQL Server 20 Các đối tượng trong bảng n Cột n Khóa n Ràng buộc dữ liệu n Trigger n Chỉ mục n Thống kê 62/8/2018 Microsoft SQL Server 21 Các ràng buộc toàn vẹn dữ liệu n Yêu cầu toàn vẹn dữ liệu: Xuất phát từ yêu cầu thực tế. Ví dụ: Khi giao/nhận hàng: Số lượng đặt hàng>0 Ngày dự kiến nhận hàng phải sau ngày đặt hàng Các số hóa đơn giao hàng không được trùng nhau 2/8/2018 Microsoft SQL Server 22 Các ràng buộc toàn vẹn dữ liệu n Not null: Yêu cầu bắt buộc phải nhập dữ liệu cho bảng n Check: Giá trị nhập vào nằm trong miền xác định n Unique: Giá trị nhập vào là duy nhất n Primary Key: Ràng buộc khóa chính n Foreign Key: ràng buộc khóa ngoại 2/8/2018 Microsoft SQL Server 23 Ví dụ về ràng buộc Null và miền giá trị n Tạo ràng buộc cho Cột Phantram nằm trong bảng VATTU: - Bắt buộc phải nhập giá trị - Giá trị nằm trong đoạn [0,100] n Cách làm: - B1: Mở cửa sổ thiết kế bảng VATTU 2/8/2018 Microsoft SQL Server 24 - B2: Ở cột Phantram bỏ lựa chọn Allow Nulls - B3: Kích chuột phải vào cột Phantram chọn Check Constraints (hoặc kích chuột phải vào đối tượng Constraints của bảng VATTU, chọn New Constraint) Không cho phép để trống giá trị Kích vào đây để mở cửa sổ Check Constraints Có thể mở cửa sổ Check Constraints từ đây 72/8/2018 Microsoft SQL Server 25 - B4: Nhập biểu thức ràng buộc miền giá trị vào ô Expression rồi nhấn Close Biểu thức ràng buộc 2/8/2018 Microsoft SQL Server 26 Ràng buộc về tính duy nhất của dữ liệu n Yêu cầu: trong bảng NHACC, yêu cầu dữ liệu Tên nhà cung cấp là duy nhất cho mỗi bản ghi n Cách làm: - B1: Kích chuột phải vào dòng TenNCC, chọn Indexes/Keys - B2: Nhấn vào nút Add trong cửa sổ Indexes/Keys để thêm ràng buộc 2/8/2018 Microsoft SQL Server 27 - B3: Chọn các thông số cho ràng buộc 1. Thêm ràng buộc 2. Chọn trường 3. Loại ràng buộc 2/8/2018 Microsoft SQL Server 28 Thiết lập khóa chính cho bảng * Chú ý: Nếu khóa được tạo từ nhiều trường, cần chọn cùng lúc tất cả các trường đó rồi mới chọn Set Primary Key § Kích chuột phải vào trường được chọn làm khóa chính, chọn Set Primary Key. Ví dụ: chọn MaVTu làm khóa chính 82/8/2018 Microsoft SQL Server 29 Ví dụ về tạo khóa ngoại n Yêu cầu: Thêm khóa ngoại cho bảng CTDONDH: bảng tham chiếu là VATTU, trường liên kết là MaVTu n Cách làm: - Bước 1: Kích chuột phải vào Keys của bảng CTDONDH, chọn New Foreign Key 2/8/2018 Microsoft SQL Server 30 - Bước 2: trong cửa sổ Foreign Key Relationships, bấm chuột vào nút của mục Tables And Columns Specification và chọn các giá trị như trong hình rồi nhấn Ok Tên mối quan hệ Bảng khóa chính Bảng khóa ngoại Trường liên kết 2/8/2018 Microsoft SQL Server 31 - Bước 3: Lựa chọn các thuộc tính ở phần Identity, Table Designer sau đó nhấn Close để đóng cửa sổ thiết kế khóa. 2/8/2018 Microsoft SQL Server 32 Chỉnh sửa cấu trúc và nhập dữ liệu cho bảng - Chọn Modify để chỉnh sửa cấu trúc bảng - Chọn Open Table để nhập dữ liệu cho bảng n Kích chuột phải vào bảng cần chỉnh sửa/nhập dữ liệu: 92/8/2018 Microsoft SQL Server 33 Tạo mô hình quan hệ trong CSDL n Bước 1: Kích chuột phải vào đối tượng Database Diagrams, chọn New Database Diagram 2/8/2018 Microsoft SQL Server 34 § Bước 2: Chọn các bảng tham gia vào mô hình quan hệ 1. Chọn bảng 2. Kích vào nút Add để thêm bảng vào quan hệ 2/8/2018 Microsoft SQL Server 35 § Bước 3: Thiết lập các mối quan hệ giữa các bảng: Tên mối quan hệ Bảng khóa chính Bảng khóa ngoại Trường liên kết Kéo trường khóa chính của bảng thứ nhất thả trực tiếp vào trường khóa ngoại của bảng thứ 2, kiểm tra các lựa chọn rồi nhấn OK. Ví dụ: 2/8/2018 Microsoft SQL Server 36 § Bước 3: (tiếp) Nếu muốn tự động xóa/cập nhật dữ liệu ở bảng con khi có hành động xóa/cập nhật dữ liệu ở bảng cha thì chọn giá trị Cascade cho tùy chọn Delete/Update Rule tương ứng Quy tắc xóa Quy tắc cập nhật 10 2/8/2018 Microsoft SQL Server 37 § Bước 4: Lưu Mô hình quan hệ Nhấn Ctrl + S hoặc kích chuột vào nút Save hoặc vào Menu File/Save, nhập tên cho mô hình quan hệ rồi nhấn OK