Ôn tập hệ quản trị csdl - .... - Tài liệu tham khảo | Đại học Hoa Sen

Ôn tập hệ quản trị csdl Tự luận 1. Lưu trữ
a. Liệt kê các thiết bị lưu trữ “bền vững” dữ liệu: tính chất, đặc điểm, giá thành.
Thiết bị nào được sử dụng phổ biến hiện nay.
b. Kỹ thuật truy xuất dữ liệu từ đĩa từ nhanh: RAID c. SQL server:
i. Database (physical): .mdf (1 file), .ndf (0-n files), .ldf (1- n files) ii. Kỹ thuật filegroups
iii. Cách tính độ lớn của database 2. Table, view, constraint a. Create table
i. Tập kiểu dữ liệu của SQL server b. Create view c. Constraint i. Primary key
ii. Foreign key: enforce, cascade, set null, set default iii. Check iv. Null v. Unique 3. Truy vấn a. INSERT, UPDATE, DELETE b. SELECT 4. Tối ưu truy vấn 5. Index
a. Cấu trúc index: b-tree, balance-tree, hash b. Cluster, non-cluster 6. T_SQL a. Khai báo biến i. Declare @a varchar(10) b. Gán giá trị cho biến i. Set @a = … ii. Select @a = Select @a = max (x) From A c. Khai báo khối Begin End d. Rẽ nhánh i. If (dk) Else e. Lặp i. While (dk dừng) f. Raiserror
i. Raiserror(“không tồn tại sv này”, 16, 1) g. Exception Procedure/ function 7. Transaction
a. 4 tính chất của transaction
b. Sơ đồ chuyển trạng thái c. Concurrency transaction i. LOCKING 1. DEADLOCK, LIVELOCK. ii. TIMESTAMPING iii. OPTIMISTED d. SQL-servers
i. Các lệnh T-SQL: begin tran, commit tran, rollback tran..
ii. Các mức lock: readcommited, readuncommited… 8. Trigger
a. Magic tables: inserted table, deleted table .
b. Before trigger, after trigger 9. Security a. SQL server
i. Window authentication, Mix mode
ii. Tạo account, mapping thành user trên database iii. Server role iv. Database role
v. Object permission, statement permission, implicit permission vi. Grant, revoke, deny 10. Backup, restore
a. Các loại backup: full, differential, files.. b. restore c. Recovery 11. Import, export a. Bcp b. Bulk-insert c. Dts Cho database company như sau Table Structure: Employee Field Name Data Type Null FName varchar(15) NOT NULL MInit varchar(1) NULL LName varchar(15) NOT NULL SSN char(9) NOT NULL BDate datetime NULL Address varchar(30) NULL Sex char(1) NULL Salary numeric(10, 2) NULL SuperSSN char(9) NULL DNo numeric(4, 0) NULL Department Field Name Data Type Null DName varchar(15) NOT NULL DNumber numeric(4, 0) NOT NULL Mgrssn char(9) NULL MgrStartdate datetime NULL Department Location Field Name Data Type Null DNumber numeric(4, 0) NOT NULL DLocation varchar(15) NOT NULL Project Field Name Data Type Null PName varchar(15) NOT NULL PNumber numeric(4, 0) NOT NULL PLocation varchar(15) NULL DNum numeric(4, 0) NOT NULL Works on Field Name Data Type Null ESSN char(9) NOT NULL PNo numeric(4, 0) NOT NULL Hours numeric(4, 1) NULL Dependent Field Name Data Type Null ESSN char(9) NOT NULL Dependent_Name varchar(15) NOT NULL Sex char(1) NULL BDate datetime NULL Relationship varchar(8) NULL Employee FName M LName SSN Total_hour Address Se Salar Supper_SSN DN in x y o it John B Smith 123456789 Houston, TX M 30000 333445555 5 Frankli T Wong 333445555 Houston, TX M 40000 888665555 5 n Joyce A English 453453453 Houston, TX F 25000 333445555 5 Ramesh K Narayan 666884444 Humble, TX M 38000 333445555 5 James E Borg 888665555 Houston, TX M 55000 NULL 1 Jennifer S Wallace 987654321 Bellaire, TX F 43000 888665555 4 Ahmad V Jabbar 987987987 Houston, TX M 25000 987654321 4 Alicia J Zelaya 999887777 Spring, TX F 25000 987654321 4 Department DName DNumber Mgrssn MgrStartdate Headquarters 1 888665555 06/19/1971 Administration 4 987654321 01/01/1985 Research 5 333445555 05/22/1978 Department Location DNumber DLocation 1 Houston 4 Stafford 5 Bellaire 5 Houston 5 Sugarland Project PName PNumber PLocation DNum NumofEmps ProductX 1 Bellaire 5 4 ProductY 2 Sugarland 5 6 ProductZ 3 Houston 5 7 Computerization 10 Stafford 4 3 Reorganization 20 Houston 1 5 Newbenefits 30 Stafford 4 6 Works on ESSN PNo Hours 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 333445555 20 10.0 453453453 1 20.0 453453453 2 20.0 666884444 3 40.0 888665555 20 NULL 987654321 20 15.0 987654321 30 20.0 987987987 10 35.0 987987987 30 23 999887777 10 10.0 999887777 30 30.0 123456797 30 21 Dependent ESSN Dependent_Name Sex BDate Relationship 123456789 Alice F 12/31/1978 Daughter 123456789 Elizabeth F 05/05/1957 Spouse 123456789 Michael M 01/01/1978 Son 333445555 Alice F 04/05/1976 Daughter 333445555 Joy F 05/03/1948 Spouse 333445555 Theodore M 10/25/1973 Son 987654321 Abner M 02/29/1932 Spouse 1. Truy vấn a)
Nhập 1 project mới có mã project là 40 các dữ liệu còn lại tùy ý. b)
Nhập vào database: tất cả nhân viên phòng 5 tham gia vào dự án có mã 40 với số giờ là 10 c)
Cập nhật lương của nhân viên tăng thêm 10% nếu nhân viên làm việc trên 40giờ/tuần d)
Xóa nhân viên làm việc cho dự án nếu dự án không đặt tại địa chỉ của nhân viên e)
Cập nhật lương của nhân viên tăng thêm 10% nếu nhân viên đang có lương < 30.000 f)
Tìm firstname, lastname của nhân viên có lương > 25000 và làm việc ở phòng có mã số = 5 g)
Tìm firstname, lastname của nhân viên NỮ có lương > 25000 hoặc nhân viên NAM có lương > 30000 h)
Tìm firstname, lastname của nhân viên làm việc cho project có tên là ‘ProductY’ i)
Tìm tên con gái của ‘John Smith’ j)
Tìm danh sách những người sinh vào tháng 12 (nhân viên và người phụ thuộc) k)
Tìm danh sách nhân viên làm việc cho dự án ProductY. l)
Tìm nhân viên không làm việc cho 1 phòng ban nhưng làm việc cho dự án mà phòng đó quản lý m)
Tìm tổng số giờ đã làm việc của từng nhân viên n)
Tìm nhân viên đã làm việc với tổng số giờ trên 40 giờ o)
Tìm nhân viên có lương cao nhất p)
Tìm danh sách nhân viên có địa chỉ ở Spring 2. Store procedure
a. Viết procedure để kiểm tra số lượng nhân viên của 1 project nào đó đã đủ chưa.
b. Viết procedure với tham số vào là mã project, xuất ra số nhân viên, tổng lương trả cho nhân viên của project
c. Viết procedure với tham số vào là mã nhân viên, xuất ra số project, tổng số giờ mà nhân viên đã làm
d. Viết procedure cập nhật tăng lương cho nhân viên có tổng giờ làm dự án >100 giờ.
Mức tăng = tổng giờ * 0.1 3. Trigger
a. Trigger để kiểm tra nếu nv tham gia vào dự án ‘30’ thì số giờ < 20.
b. Trigger để khi thêm mới 1 project ở ‘Sugarland’ thì tự động phân công cho những
nhân viên có địa chỉ ở ‘Sugarland’ tham gia vào project này với số giờ làm việc là 20.
c. Trigger để kiểm tra nhân viên không được làm việc quá 40 giờ. Bài giải CREATE PROC KTRASONV @PNO VARCHAR(3), @DUNV BIT OUT AS DECLARE @SONVYEUCAU INT DECLARE @SONVdangco INT
SET @SONVYEUCAU = ( SELECT NumofEmps FROM PROJECT WHERE Pnumber =@PNO )
SET @SONVdangco = ( SELECT COUNT(*) FROM Works on WHERE pno =@PNO )
IF @SONVdangco < @SONVYEUCAU SET @DUNV = 0 Else SET @DUNV =1 Declare @a bit Exec kiemtranv 30, @a out Print convert(varchar(1),@a) ----- CREATE TRIGGER T1 ON Works on FOR INSERT, UPDATE AS IF EXISTS (SELECT PNO FROM WORKS ON
WHERE HOURS >=20 AND PNO = ‘30 ) BEGIN ROLLBACK TRAN
RAISERROR (‘nv tham gia vào dự án ‘30’ thì số giờ >= 20’ ,16, 1) END