Fresher training program relational database management system
Download
1 / 50

Fresher Training Program Relational Database Management System - PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on

Fresher Training Program Relational Database Management System. Nguyen Minh Tien. Contents. DCL statements Aggregate functions View Trigger Store-procedure. Learning approach. The following are strongly suggested for a better learning and understanding of this course:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Fresher Training Program Relational Database Management System' - gunnar


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Fresher training program relational database management system

Fresher Training ProgramRelational Database Management System

Nguyen Minh Tien


Contents
Contents

  • DCL statements

  • Aggregate functions

  • View

  • Trigger

  • Store-procedure


Learning approach
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



Security
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


Grant revoke
GRANT & REVOKE

  • GRANT ….. TO …

  • REVOKE ….. FROM ...


Grant database
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]


Grant tables or views
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]


Grant system privileges
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]


Grant to
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;


Grant to1
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 ]


Grant to2
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


All public arguments
ALL & PUBLIC arguments

  • GRANT ALL PRIVILEGES ON Customer to User2

  • GRANT ALL ON Cusomer to PUBLIC;

  • GRANT SELECT ON Customer to PUBLIC;


Granting with grant option
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;


Taking priviliges away
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}]


Examples of revoke
Examples of REVOKE

REVOKE INSERT

ON Customer

FROM User2;

REVOKE SELECT, INSERT

ON Customer

FROM User2, User3;



Concurrency
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


Locking
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


Deadlock
Deadlock

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




What is a view
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


What is a view to the dbms
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


Create a view
Create a VIEW

CREATE VIEW dssv AS

SELECT masv,h

FROM sinhvien,lop

WHERE sinhvien.malop=lop.malop;


Alter a view
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'


Update data in view
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


Dropping views
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;


Advantages of views
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.


Disadvantages of views
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



What s trigger
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


Dml trigger
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.


Create a trigger
CREATE a TRIGGER

  • Syntax:

    CREATE TRIGGER Trigger_name

    ON table | view

    [WITH ENCRYPTION]

    { FOR | AFTER | INSTEAD OF } {[DELETE] [,] [INSERT] [,] [UPDATE] }

    AS Sql_statement


Types of trigger
Types of Trigger

  • INSERT Trigger

  • UPDATE Trigger

  • DELETE Trigger


Insert trigger
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


Update trigger
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


Delete trigger
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


Instead of triggers
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.


Instead of trigger
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)


Alter a trigger
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 ] }


Delete a trigger
DELETE a Trigger

  • DROP TRIGGER:

    DROP TRIGGER { trigger_name }



What s a store procedure
What’s a Store-procedure?

  • What’s is store-procedure?

    A store-procedure is one or set of statements implement number of works.


Create store procedure
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


Execute sp
Execute SP

  • Execute SP:

    EXECUTE tên_thủ_tục [danh_sách_các_đối_số]


Variables in sp
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 [email protected]

PRINT @tenlop1+' nhap hoc nam '+str(@namnhaphoc1)


Return in sp
Return in SP

CREATE PROCEDURE sp_Conghaiso(

@a INT,

@b INT,

@c INT OUTPUT)

AS

SELECT @[email protected][email protected]

DECLARE @tong INT

SELECT @tong=0

EXECUTE sp_Conghaiso 100,200,@tong OUTPUT

SELECT @tong


Params with default values
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 [email protected]

ELSE

SELECT hodem,ten

FROM sinhvien INNER JOIN lop

ON sinhvien.malop=lop.malop

WHERE [email protected] AND

[email protected]

END



ad