SLIDES_RDBMS_6.pdf - Education and Research We enable you to leverage knowledge anytime anywhere RDBMS \u2013 Part 6 o f In ER\/CORP\/CRS\/DB92 Ver No 0.1 s y

SLIDES_RDBMS_6.pdf - Education and Research We enable you...

This preview shows page 1 out of 36 pages.

Unformatted text preview: Education and Research We enable you to leverage knowledge anytime, anywhere! RDBMS – Part 6 o f In ER/CORP/CRS/DB92 Ver. No.: 0.1 s y s Confidential Copyright © 2008, Infosys Technologies Ltd. General Guideline © (2008) Infosys Technologies Ltd. This document contains valuable confidential and proprietary information of Infosys. Such confidential and proprietary information includes, amongst others, proprietary intellectual property which can be legally protected and commercialized. Such information is furnished herein for training purposes only. Except with the express prior written permission of Infosys, this document and the information contained herein may not be published, disclosed, or used for any other purpose. o f In Copyright © 2008, Infosys Technologies Ltd. 2 s y s Confidential Confidential Information   This Document is confidential to Infosys Technologies Limited. This document contains information and data that Infosys considers confidential and proprietary (“Confidential Information”). Confidential Information includes, but is not limited to, the following: s y s  Corporate and Infrastructure information about Infosys  Infosys’ project management and quality processes  Project experiences provided included as illustrative case studies     Any disclosure of Confidential Information to, or use of it by a third party, will be damaging to Infosys. Ownership of all Infosys Confidential Information, no matter in what media it resides, remains with Infosys. Confidential information in this document shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. This document also contains third party confidential and proprietary information. Such third party information has been included by Infosys after receiving due written permissions and authorizations from the party/ies. Such third party confidential and proprietary information shall not be disclosed, duplicated or used – in whole or in part – for any purpose other than reading without specific written permission of an authorized representative of Infosys. o f In Copyright © 2008, Infosys Technologies Ltd. 3 Confidential Recap     Sub Query Independent Sub Query Correlated Query Exists and Not Exists o f In Copyright © 2008, Infosys Technologies Ltd. 4 s y s Confidential 4 Session Plan      Views Types of views Updating table through views Types of Foreign key Constraints DCL o f In Copyright © 2008, Infosys Technologies Ltd. 5 s y s Confidential 5 Education and Research We enable you to leverage knowledge anytime, anywhere! VIEWS o f In Copyright © 2008, Infosys Technologies Ltd. 6 s y s Confidential What is a view?  A view is a kind of “virtual table” s y s  Contents are defined by a query like: Select Empno, Name, age from Employee Where designation=‘developer’; As shown in the figure o f In Copyright © 2008, Infosys Technologies Ltd. 7 Confidential View: A view is a customized representation of data from one or more tables. The tables that the view is referencing are known as base tables. A view can be considered as a stored query or a virtual table. Only the query is stored in the Oracle data dictionary; the actual data is not copied any-where. This means that creating views does not take any storage space, other than the space in dictionary. 7 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 o f In Copyright © 2008, Infosys Technologies Ltd. 8 s y s Confidential For simple queries the results are generated row by row on the fly. For more complex views the DBMS must generate a temporary table representing the view and later discard it. 8 Create a view s y s CREATE VIEW ViewCustomerDetails AS SELECT * FROM Customer_Details; o f In Copyright © 2008, Infosys Technologies Ltd. 9 Confidential You must have permission on the table referred in the view to successfully create the view Can assign a new name to each column chosen in the view Only names can be different. The data type etc remain the same as the source table because, the values are after all going to be derived from that table If no names are specified for columns, the same name as used in the source table is used 9 Assigning names to columns CREATE VIEW vwCustDetails (CustCode,CustLname,CustFName CustCode,CustLname,CustFName) CustCode,CustLname,CustFName AS SELECT Cust_Id,Cust_Last_Name,Cust_First_Name FROM Customer_details; o f In Copyright © 2008, Infosys Technologies Ltd. 10 s y s Confidential Note: We can assign names for the various columns in the view. This may be entirely different from what has been used in the source table. If you use an asterisk (*) to select all columns from a table in the query to create a view, and you later modify the table to add columns, you should re-create the view to reflect the new columns. When * is used, Oracle expands it to the column list and stores the definition in the database. 10 Types of views      Horizontal views Vertical views Row/column subset views Grouped views Joined views o f In Copyright © 2008, Infosys Technologies Ltd. 11 s y s Confidential Based on how the view is created. We will discuss them in detail in the following slides 11 Horizontal views Horizontal view restricts a user’s access to only selected rows of a table. s y s CREATE VIEW view_cust AS SELECT * FROM Customer_Details where Cust_ID in (101,102,103); o f In Copyright © 2008, Infosys Technologies Ltd. 12 Confidential Here a horizontal subset of the source table is taken to create the view. Very useful when data belonging to different categories are present in the table. A private( virtual) table for each category can be created and given access to the person concerned 12 Vertical views  A view which selects only few columns of a table:  Vertical view restricts a user’s access to only certain columns of a table s y s CREATE VIEW view_cust AS o f In SELECT Cust_ID, Account_No, Account_Type FROM Customer_Details Copyright © 2008, Infosys Technologies Ltd. 13 Confidential Helps restricting access to sensitive information like salary, grade etc. 13 Row/column subset views CREATE VIEW View_Cust_VertHor AS SELECT Cust_Id,Account_No,Account_Type FROM Customer_Details WHERE CUST_ID IN (101,102,103); o f In Copyright © 2008, Infosys Technologies Ltd. 14 s y s Confidential SQL standard doesn’t define any notion of horizontal or vertical views . It is for our own understanding that we have given names for views which select only selected rows/columns from the source table as horizontal/vertical views. There could be a combination of these two concepts where a view selects a subset of rows and columns 14 Views with Group By clause  The query contains a group by clause CREATE VIEW View_GroupBY(Dept,NoofEmp) s y s AS SELECT Department, COUNT(Employee_ID) FROM Employee_Manager GROUP BY Department; o f In Copyright © 2008, Infosys Technologies Ltd. 15 Confidential Grouped views are created based on a grouped query. They group rows of data and produce one row in the result corresponding to each group So, the rows in the view don’t have a one to one correspondence with the rows of the source table For this reason , grouped views cannot be updated. You can update, insert, and delete rows through a view, but with some restrictions. You can perform DML statements on a view only if the view definition does not have the following: •Distinct clause •Group by clause •ROWNUM clause •Set operators (UNION, UNION ALL, INTERSECT, or MINUS) •Sub-query in the SELECT clause •More than one table (join view) All DML operations on the view are performed on the base tables. 15 Views with Joins  Created by specifying a two-table or three-table query in the view creation command CREATE VIEW View_Cust_Join AS SELECT s y s a.Cust_Id,b.Cust_First_Name,b.Cust_Last_Name, o f In Amount_in_dollars FROM Customer_loan a, customer_details b WHERE a.cust_id = b.cust_id; Copyright © 2008, Infosys Technologies Ltd. 16 Confidential Using Join Views: A join view is a view with more than one base table in the top-level FROM clause. An updatable join view ( or modifiable join view) is a view that can be used to update the base tables through the view. 16 Updating View A View can be modified by the DML command. CREATE VIEW View_Cust AS SELECT * FROM Customer_Details WHERE CUST_ID IN (101,102,103); --Insert Statement INSERT INTO view_cust VALUES (103,'Langer','G.','Justin',3421,'Savings',' Global Commerce Bank','[email protected]'); o f In --Delete Statement DELETE view_cust s y s WHERE cust_id = 103; --Update Statament UPDATE view_cust SET Cust_last_name = 'Smyth' WHERE cust_id = 101; Copyright © 2008, Infosys Technologies Ltd. 17 Confidential Any kind of update through view that violates the constraints of the underlying table is not allowed 17 Updating View A view can be updated if the query that defines the view meets all of these restrictions: DISTINCT must not be specified; that is, duplicate rows must not be eliminated from the query results s y s The FROM clause must specify only one updateable table; the view must have a single underlying source table o f In The SELECT list cannot contain expressions, calculated columns, or column functions The WHERE clause must not include a sub query; only simple row-by-row search conditions may appear Copyright © 2008, Infosys Technologies Ltd. 18 Confidential Depending on the commercial implementation being used, views may or may not be updateable. In some cases, all views are not updateable. In some others a view is updateable if it is from one table, else it is not. In still others, a view is updateable if it is the result of a simple query; if it is defined by some GROUP BY statements, for example, the view is not updateable. According to ANSI/ISO standards, A view is updatable if The DBMS is able to trace back the rows and columns of the view to the corresponding rows and columns of the source table. So a view is updatable if : DISTINCT is not specified in the query used to create the view The FROM clause specifies only one source table The select list doesn’t contain expressions/calculated columns The WHERE clause doesn’t include a subquery The query doesn’t include a GROUP BY or HAVING 18 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 s y s <view name>; DROP VIEW View_Cust; o f In Copyright © 2008, Infosys Technologies Ltd. 19 Confidential if some other views depend on this view, then if you say DROP VIEW ViewSupplier CASCADE then this view plus all other views that are based on this view are deleted. If you specify DROP VIEW ViewSupplier RESTRICT then this view cannot be deleted if other views depend on it. 19 Checking View Updates :– Check Option Solution Solution o f In s y s CREATE VIEW view_customer AS SELECT Cust_ID, Cust_Last_Name,Account_No, Account_Type, Bank_Branch FROM Customer_Details Where Bank_Branch = ‘Downtown’ Downtown’ With CHECK OPTION; Copyright © 2008, Infosys Technologies Ltd. 20 Confidential Advantages of views  Security  Query simplicity  Structural simplicity o f In Copyright © 2008, Infosys Technologies Ltd. 21 s y s Confidential 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. 21 Disadvantages of views  Performance  Restrictions o f In Copyright © 2008, Infosys Technologies Ltd. 22 s y s Confidential Performance: views based on joins are merely virtual 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. 22 Education and Research We enable you to leverage knowledge anytime, anywhere! Types of Foreign Key Constraints o f In ER/CORP/CRS/DB92 Ver. No.: 0.1 s y s Confidential Copyright © 2008, Infosys Technologies Ltd. Types of Foreign Key Constraints  On Delete Set Null  On Delete Cascade  On Delete Set Default  On Delete Restrict o f In Copyright © 2008, Infosys Technologies Ltd. 24 s y s Confidential Types of Foreign Key Constraints  Implementation of Primary Key and Foreign Key Constraints CREATE TABLE PROJECT( Project_ID Number(3) CONSTRAINT Pkey1 PRIMARY KEY, Project_Name VarChar2(25), Project_Location VarChar2(35), ); o f In CREATE TABLE EMPLOYEE( Employee_ID KEY, Employee_First_Name Employee_Email Grade Project_NO REFERENCES Delete Cascade ); s y s Number(6) CONSTRAINT Pkey2 PRIMARY VarChar2(25), VarChar2(35), Number(2), Number(3) CONSTRAINT Fkey1 PROJECT(Project_ID) On Copyright © 2008, Infosys Technologies Ltd. 25 Confidential Types of Foreign Key Constraints  Implementation of Primary Key and Foreign Key Constraints CREATE TABLE PROJECT( Project_ID Number(3) CONSTRAINT Pkey1 PRIMARY KEY, Project_Name VarChar2(25), Project_Location VarChar2(35), ); o f In CREATE TABLE EMPLOYEE( Employee_ID KEY, Employee_First_Name Employee_Email Grade Project_NO REFERENCES Delete Set null ); s y s Number(6) CONSTRAINT Pkey2 PRIMARY VarChar2(25), VarChar2(35), Number(2), Number(3) CONSTRAINT Fkey1 PROJECT(Project_ID) On Copyright © 2008, Infosys Technologies Ltd. 26 Confidential Education and Research We enable you to leverage knowledge anytime, anywhere! s y s DATA CONTROL LANGUAGE (DCL) o f In Copyright © 2008, Infosys Technologies Ltd. 27 Confidential GRANT …. Tables or views GRANT { [ALTER[, ]] s y s [DELETE[, ]] [INDEX[, ]] [INSERT[, ]] o f In [SELECT[, ]] [UPDATE [(column-name[,...])][, ]] | ALL [PRIVILEGES]] } ON [TABLE] {table-name[,...] | viewname[,...]} TO [AuthID][,...] [WITH GRANT OPTION] Copyright © 2008, Infosys Technologies Ltd. 28 Confidential Privileges on a specific table or a view created based on a table. Granting a privilege to the special user PUBLIC implicitly grants that privilege to any user who connects to the database. 28 GRANT GRANT SELECT, INSERT ON Customer_Details s y s TO Edwin ; GRANT ALL PRIVILEGES o f In ON Customer_Loan TO JACK ; GRANT ALL ON Customer_Loan TO PUBLIC ; Copyright © 2008, Infosys Technologies Ltd. 29 Confidential Grant  With Grant Option GRANT SELECT ON Customer_Loan TO EDWIN WITH GRANT OPTION; o f In Copyright © 2008, Infosys Technologies Ltd. 30 s y s Confidential Object privileges can be granted WITH GRANT OPTION, which gives the grantee permission to grant those privileges to any other user, or to PUBLIC. 30 Taking PRIVILIGES away The syntax of REVOKE command is patterned after GRANT, but with a reverse meaning. REVOKE{ [ALTER[, ]] s y s [DELETE[, ]] [INDEX[, ]] [INSERT[, ]] o f In [SELECT[, ]] [UPDATE [(column -name[,...])][, ]] [(column| ALL [PRIVILEGES] } ON [TABLE] {table{table-name[,...] | viewview-name [,...]} FROM AuthID[,...] Copyright © 2008, Infosys Technologies Ltd. 31 Confidential 31 Revoke REVOKE SELECT, INSERT ON Customer_Details FROM Edwin ; REVOKE ALL PRIVILEGES ON Customer_Loan FROM JACK ; o f In REVOKE ALL ON Customer_Loan FROM PUBLIC ; Copyright © 2008, Infosys Technologies Ltd. 32 s y s Confidential Revoke o f In Copyright © 2008, Infosys Technologies Ltd. 33 s y s Confidential Learning approach  The following are strongly suggested for a better learning and understanding of this course:  Noting down the key concepts in the class, explained by the educator  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 applicable for this course) on time inclusive of individual and group activities  Taking part in the self assessment activities  Participation in the doubt clearing sessions o f In Copyright © 2008, Infosys Technologies Ltd. 34 s y s Confidential Summary  A view is a kind of “virtual table”  View can be used to modify the content of table with some restriction  Grant and revoke command can be used to add or remove privileges from users o f In Copyright © 2008, Infosys Technologies Ltd. 35 s y s Confidential 35 o f In Thank You s y s “The contents of this document are proprietary and confidential to Infosys Technologies Ltd. and may not be disclosed in whole or in part at any time, to any third party without the prior written consent of Infosys Technologies Ltd.” “© 2008 Infosys Technologies Ltd. All rights reserved. Copyright in the whole and any part of this document belongs to Infosys Technologies Ltd. This work may not be used, sold, transferred, adapted, abridged, copied or reproduced in whole or in part, in any manner or form, or in any media, without the prior written consent of Infosys Technologies Ltd.” Copyright © 2008, Infosys Technologies Ltd. 36 Confidential ...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture