Transcript bai4

Fresher Training Program
Relational Database Management
System
Nguyen Minh Tien
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Contents





DCL statements
Aggregate functions
View
Trigger
Store-procedure
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Data Control Language - DCL
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
GRANT & REVOKE


GRANT ….. TO …
REVOKE ….. FROM ...
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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 ]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
ALL & PUBLIC arguments



GRANT ALL PRIVILEGES ON Customer to User2
GRANT ALL ON Cusomer to PUBLIC;
GRANT SELECT ON Customer to PUBLIC;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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}]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Examples of REVOKE
REVOKE INSERT
ON Customer
FROM User2;
REVOKE SELECT, INSERT
ON Customer
FROM User2, User3;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Locking example
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Deadlock

Occurs when two or more separate processes
compete for resources held by one another.
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Deadlock
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
VIEW
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Create a VIEW
CREATE VIEW dssv AS
SELECT masv,h
FROM sinhvien,lop
WHERE sinhvien.malop=lop.malop;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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'
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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;
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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.
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
TRIGGER
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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.
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
CREATE a TRIGGER

Syntax:
CREATE TRIGGER Trigger_name
ON table | view
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } {[DELETE] [,]
[INSERT] [,] [UPDATE] }
AS Sql_statement
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Types of Trigger



INSERT Trigger
UPDATE Trigger
DELETE Trigger
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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.

© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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)
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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 ] }
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
DELETE a Trigger

DROP TRIGGER:
DROP TRIGGER { trigger_name }
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
STORE - PROCEDURE
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
What’s a Store-procedure?

What’s is store-procedure?
A store-procedure is one or set of statements
implement number of works.
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Create Store procedure

Create SP:
CREATE PROCEDURE tên_thủ_tục
[(danh_sách_tham_số)]
[WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRY
PTION]
AS
Các_câu_lệnh_của_thủ_tục
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Execute SP

Execute SP:
EXECUTE tên_thủ_tục
[danh_sách_các_đối_số]
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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)
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
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
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4
Q&A
© FPT SOFTWARE – TRAINING MATERIAL – Internal use
04e-BM/NS/HDCV/FSOFT v2/4