Fresher Training Program Relational Database Management System

1. Fresher Training ProgramRelational Database Management System Nguyen Minh Tien

2. Contents • DCL statements • Aggregate functions • View • Trigger • Store-procedure

3. Learning approach • The following are strongly suggested for a better learning and understanding of this course: • Noting down the key concepts in the class • Analyze all the examples / code snippets provided • Study and understand the self study topics • Completion and submission of all the assignments, on time • Completion of the self review questions in the lab guide • Study and understand all the artifacts including the reference materials / e-learning / supplementary materials specified • Completion of the project (if application for this course) on time inclusive of individual and group activities • Taking part in the self assessment activities • Participation in the doubt clearing sessions

4. Data Control Language - DCL

5. Security • Protection of data against unauthorized disclosure, alteration or destruction. • Access allowed to only authorized users • User identification - Authorized users connect to the database using user id and password. • Views, Synonyms,Roles • Access Privileges

6. GRANT & REVOKE • GRANT ….. TO … • REVOKE ….. FROM ...

7. GRANT …. database GRANT { [DBADM[, ]] - Database administrator authority [DBCTRL[,]] - Database control authority [DBMAINT[, ]] - Database maintenance authority [CREATETAB[,]] - Privilege to create table [DROP[, ]] - Privilege to DROP/ALTER [STARTDB[, ]] - Start database [STOPDB[, ]] } - Stop database ON DATABASE database-name[,...] TO [AuthID][,...] [PUBLIC] [WITH GRANT OPTION]

8. GRANT …. Tables or views GRANT { [ALTER[, ]] [DELETE[, ]] [INDEX[, ]] [INSERT[, ]] [SELECT[, ]] [UPDATE [(column-name[,...])][, ]] [REFERENCES[, ]] | ALL [PRIVILEGES] } ON [TABLE] {table-name[,...] | view-name[,...]} TO [AuthID][,...] [PUBLIC [AT ALL LOCATIONS]] [WITH GRANT OPTION]

9. GRANT .. System privileges GRANT { [CREATEALIAS[, ]] - create alias [CREATEDBA[, ]] - create DB to get DBADM authority [CREATEDBC[, ]] - create DB to get DBCTRL authority [CREATESG[, ]] - to create new storage group [SYSADM[, ]] - to provide system ADM authority [SYSCTRL[, ]] - to provide system control authority } TO [AuthID][,...] [PUBLIC] [WITH GRANT OPTION]

10. GRANT …. TO …. • Used to grant access to new users; • Permission can be granted for all DML commands; • Permission is granted on a database/table/view; • Permission for further grant. • Example: • User1 is an owner of Customer table. • User1 wants User2 perform queries on it. • User1 issues following command: GRANT SELECT ON Customer to User2;

11. GRANT .. TO • Syntax: GRANT ALL [PRIVILEGES]| các_quyền_cấp_phát [(danh_sách_cột)] ON tên_bảng | tên_khung_nhìn |ON tên_bảng | tên_khung_nhìn [(danh_sách_cột)] |ON tên_thủ_tục |ON tên_hàm TO danh_sách_người_dùng | nhóm_người_dùng [WITH GRANT OPTION ]

12. GRANT .. TO • Assign to users with name of thuchanh right to implement SELECT, INSERT, UPDATE on LOP table. GRANT SELECT,INSERT,UPDATE ON lop TO thuchanh • Assign to users with name of thuchanh right of view hodem, ten and ngaysinh fileds on SINHVIEN table. GRANT SELECT (hodem,ten,ngaysinh) ON sinhvien TO thuchanh

13. ALL & PUBLIC arguments • GRANT ALL PRIVILEGES ON Customer to User2 • GRANT ALL ON Cusomer to PUBLIC; • GRANT SELECT ON Customer to PUBLIC;

14. Granting with GRANT option • GRANT SELECT ON Customer To User2 WITH GRANT OPTION • GRANT SELECT ON User1.Customer To User3; • GRANT SELECT ON User1.Customer To user3 WITH GRANT OPTION;

15. Taking PRIVILIGES away • The syntax of REVOKE command is patterned after GRANT, but with a reverse meaning. REVOKE{ [ALTER[, ]] [DELETE[, ]] [INDEX[, ]] [INSERT[, ]] [SELECT[, ]] [UPDATE [(column-name[,...])][, ]] | ALL [PRIVILEGES] } ON [TABLE] {table-name[,...] | view-name [,...]} FROM AuthID[,...][PUBLIC [AT ALL LOCATIONS]] [BY {AuthID[,...] | ALL}]

16. Examples of REVOKE REVOKE INSERT ON Customer FROM User2; REVOKE SELECT, INSERT ON Customer FROM User2, User3;

17. Locking example

18. Concurrency • Two or more users access a database concurrently • DBMS ensures serializability • Problems associated with concurrent execution: • Lost update • Dirty read • Non repeatable read • Phantom records • Concurrency techniques: • Locking • Time stamping

19. Locking • A lock is a variable associated with each data item in a database. • When updated by a transaction, DBMS locks the data item • Serializability could be maintained by this. • Lock could be Shared or Exclusive

20. Deadlock • Occurs when two or more separate processes compete for resources held by one another.

21. Deadlock

22. VIEW

23. What is a view? • A view is a kind of “virtual table” • Contents are defined by a query like: Select Empno, Name, age from Employee Where designation=‘developer’; • As shown in the figure

24. What is a view to the DBMS? • We can use views in select statements like Select * from view_employees where age > 23; • DBMS translates the request to an equivalent request to the source table

25. Create a VIEW CREATE VIEW dssv AS SELECT masv,h FROM sinhvien,lop WHERE sinhvien.malop=lop.malop;

26. Alter a view • A view can be change structure CREATE VIEW viewlop AS SELECT malop,tenlop,tenkhoa FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa WHERE tenkhoa='Khoa Vật lý’ ALTER VIEW view_lop AS SELECT malop,tenlop,hedaotao FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa WHERE tenkhoa='Khoa Công nghệ thông tin'

27. Update data in VIEW CREATE VIEW nv1 AS SELECT manv,hoten,madv FROM nhanvien INSERT INTO nv1 VALUES('NV04','Le Thi D',1) • A new record will be added to nhanvien table • In order to update data through a view, in SELECT statement define a view, do not use DISTINCT, TOP, GROUP BY, UNION

28. Dropping Views • Views are dropped similar to the way in which the tables are dropped. However, you must own a view in order to drop it. DROP VIEW <view name>; DROP VIEW NV1;

29. Advantages of views • Security: only a limited set of rows/ columns are viewable by certain users • Query simplicity: A view can derive data from many tables. So, subsequently we can use queries on the view as single table queries rather than writing queries against the source tables as multi-table queries • Structural simplicity: views can show only a portion of the table which is relevant to the user there by keeping it simple.

30. Disadvantages of views • Performance: views based on joins are merely virtaul tables. Every time a query is placed against the view, the query representing creation of the view has to be executed . So, complex joins may have to be performed every time a query is placed against the view. • Restrictions: Not all views are updateable

31. TRIGGER

32. What’s TRIGGER? • Trigger is a special store-prcedure type implemented automatically when an event happen on DB. • Trigger has two types: • DML triggers (Standart triggers) raising whenever user change data on table or view (INSERT, UPDATE, DELETE) trên bảng hoặc view. DML triggers are used to ensure business constraint or data integrity • DDL triggers implemented whenever changing structure view, table, … (CREATE, ALTER, DROP). DDL trigger is new from SQL2005 version

33. DML Trigger • DML Trigger includes: • AFTER Trigger: raising after changing data implemented successfully. AFTER is default and cannot use for view. • INSTEAD OF Trigger: implemented instead of SQL statements cause the trigger. INSTEAD OF trigger use for table and view.

34. CREATE a TRIGGER • Syntax: CREATE TRIGGER Trigger_name ON table | view [WITH ENCRYPTION] { FOR | AFTER | INSTEAD OF } {[DELETE] [,] [INSERT] [,] [UPDATE] } AS Sql_statement

35. Types of Trigger • INSERT Trigger • UPDATE Trigger • DELETE Trigger

36. INSERT TRIGGER • INSERT Trigger: Create trigger TongLuong1 On NhanVien For Insert As If ( (select MasoDV from Inserted) is Not Null) Begin Update DonVi Set TongLuong = TongLuong + (select luong from Inserted) Where MasoDV= (select MasoDV from Inserted ); Print 'Da cap nhat tong luong cua don vi. Tong luong1' End

37. UPDATE TRIGGER • UPDATE Trigger: CREATE TRIGGER UpdateMaNV ON NhanVien FOR UPDATE AS IF UPDATE (MaNV) BEGIN PRINT ‘Khong the thay doi gia tri cua MaNV’ ROLLBACK TRANSACTION END

38. DELETE TRIGGER • DELETE Trigger: Create trigger XoaDV On DonVi For Delete As Begin Delete from NhanVien Where MasoDV=(select MasoDV from deleted); Delete from DuAn Where MasoDV=(select MasoDV from deleted); End

39. INSTEAD OF Triggers • INSTEAD OF Trigger used to replace SQL statements interact with data. • INSTEAD OF trigger is very useful when changing data on view that cannot implement in common way.

40. INSTEAD OF Trigger • Create Emp_pub view: CREATE VIEW Emp_pub AS SELECT emp_id, lname, job_id, pub_name FROM employee e, publishers p WHERE e.pub_id = p.pub_id • Create INSTEAD OF del_emp trigger CREATE TRIGGER del_emp ON Emp_pub INSTEAD OF DELETE AS DELETE employee WHERE emp_id IN (SELECT emp_id FROM DELETED)

41. ALTER a TRIGGER • ALTER TRIGGER: ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] }

42. DELETE a Trigger • DROP TRIGGER: DROP TRIGGER { trigger_name }

43. STORE - PROCEDURE

44. What’s a Store-procedure? • What’s is store-procedure? A store-procedure is one or set of statements implement number of works.

45. Create Store procedure • Create SP: CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] AS Các_câu_lệnh_của_thủ_tục

46. Execute SP • Execute SP: EXECUTE tên_thủ_tục [danh_sách_các_đối_số]

47. Variables in SP CREATE PROCEDURE sp_Vidu( @malop1 NVARCHAR(10), @malop2 NVARCHAR(10)) AS DECLARE @tenlop1 NVARCHAR(30) DECLARE @namnhaphoc1 INT SELECT @tenlop1=tenlop, @namnhaphoc1=namnhaphoc FROM lop WHERE malop=@malop1 PRINT @tenlop1+' nhap hoc nam '+str(@namnhaphoc1)

48. Return in SP CREATE PROCEDURE sp_Conghaiso( @a INT, @b INT, @c INT OUTPUT) AS SELECT @c=@a+@b DECLARE @tong INT SELECT @tong=0 EXECUTE sp_Conghaiso 100,200,@tong OUTPUT SELECT @tong

49. Params with default values CREATE PROC sp_TestDefault( @tenlop NVARCHAR(30)=NULL, @noisinh NVARCHAR(100)='Huế') AS BEGIN IF @tenlop IS NULL SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh=@noisinh ELSE SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh=@noisinh AND tenlop=@tenlop END

50. Q & A