Creating Views - PDF

Views  are SELECT statements that have been saved in the database. Keywords related to Views  CREATE VIEW  ALTER VIEW  DROP VIEW CREATE VIEW  is a keyword that enables users to create new views.  syntax: CREATE VIEW ViewName AS SELECT statement;...

Views  are SELECT statements that have been saved in the database. Keywords related to Views  CREATE VIEW  ALTER VIEW  DROP VIEW CREATE VIEW  is a keyword that enables users to create new views.  syntax: CREATE VIEW ViewName AS SELECT statement;  Example: tblEmployee EmployeeID EmpName 01-054 Jerry Mendoza 01-055 Dane Sevilla 01-556 Mylene Antonio tblLogRecords LogID EmployeeID LogDate 1 01-054 11-09-11 2 01-056 11-10-11 3 01-054 11-11-11 tblTimeRecords TimeRecID LogID TIn TOut 1 1 09:00 18:03 2 2 07:30 16:55 3 3 09:02 18:34 Statement A: SELECT tblEmployee.EmpName AS 'Name, tblLogRecords.LogDate AS 'Date', tblTimeRecords.TIn AS 'Time In', tblTimeRecords.TOut AS 'Time Out' FROM tblEmployee LEFT JOIN tblLogRecords ON tblEmployee.EmployeeID = tblLogRecods.EmployeeID LEFT JOIN tblTimeRecords ON tblLogRecords.LogID = tblTimeRecords.LogID; Statement B: CREATE VIEW EmployeeTimeSheetView AS SELECT tblEmployee.EmpName AS 'Name, tblLogRecords.LogDate AS 'Date', tblTimeRecords.TIn AS 'Time In', tblTimeRecords.TOut AS 'Time Out' FROM tblEmployee LEFT JOIN tblLogRecords ON tblEmployee.EmployeeID = tblLogRecods.EmployeeID LEFT JOIN tblTimeRecords ON tblLogRecords.LogID = tblTimeRecords.LogID; Referencing Views  Example: SELECT * FROM EmployeeTimeSheetView; OUTPUT: EmployeeTimeSheetView Name Date Time In Time Out Jerry Mendoza 11-09-11 09:00 18:03 Jerry Mendoza 11-11-11 09:02 18:34 Mylene Antonio Null Null Null Dane Sevilla 11-10-11 7:30 16:55 Views can:  reduce complexity  increase reusability  properly format data  create calculated columns  be used to rename column names  create subset of data  be used to enforce security restrictions ALTER VIEW  is the keyword used to modify a created view  syntax: ALTER VIEW ViewName AS SELECT statement;  Example: o Original Table tblStudentInfo: StudentNo FName MName LName 001 Jane Santos Aquino 002 Mark Guzman Perez 003 Leo Marquez Pacquiao Creating a View: CREATE VIEW StudentViews AS SELECT FName AS 'First Name', LName AS 'Last Name' FROM tblStudentInfo; StudentsView: First Name Last Name Jane Aquino Mark Perez Leo Pacquiao ALtering the views: ALTER VIEW StudentViews AS SELECT FName AS 'First Name', MName AS 'Middle Name', LName AS 'Last Name' FROM tblStudentInfo; Altered StudentsView: First Name Middle Name Last Name Jane Santos Aquino Mark Guzman Perez Leo Marquez Pacquiao DROP VIEW  is used to delete a view that was previously created  syntax: DROP VIEW ViewName;  Example: DROP VIEW StudentsView; TRANSACTIONS  Transactions What is a Transaction? a transaction is a logical unit of work. it is a set of SQL statements entered by a user and ended by specifying whether all changes are to be made permanent or undone. the DBMS provides different guarantees for a transaction, called ACID properties: Atomicity - either all (commit) or none (rollback) of the changes within a transaction are made permanent. Consistency - if a transaction executes on a consistent database, even when it terminates the database will still be consistent. Isolation - a transaction can execute on a database as if is the only transaction running. Durability - changes made by any committed transaction are permanent, even if system crashes and hardware failure occurs.  What is Concurrency? Concurrency is defined as simultaneous execution of operations. it has been a concern to all database administrations and developers. Starting a Transaction SQL starts a transaction automatically when a new statement is executed. a user may explicitly start a transaction using START TRANSACTION statement the syntax is: START TRANSACTION [}  COMMIT and ROLLBACK auto-commit us driven automatically after every SQL statement while manual commit is programmatically invoked by using the COMMIT commands COMMIT the commit statement is used to make changes to the database permanent or terminate a user-defined transaction. a ROLLBACK can be executed to prevent incorrect actions or explicitly invoked by the user using the ROLLBACK statement. ROLLBACK [WORK] [TO SAVEPOINT ]  Ending a Transaction a transaction can end either with a commit or rollback. the COMMIT statement attempts to commit all changes made from the beginning of the transaction. If problems are detected, COMMIT will signal an error, and the transaction is rolled back. Example: Consider the following SQL transaction pseudocode. The transaction executed the SQL statements MOD1. MOD2, MOD3, and MOD4. MOD1 START TRANSACTION MOD2 MOD3 MOD4 ROLLBACK  SAVEPOINT savepoints are named placeholders in the sequence of statements in a transaction if can be used to rollback a certain part of the transaction the syntax is: SAVEPOINT Example: ROLLBACK TO SAVEPOINT sp  Deadlock a deadlock is a phenomenon that can occur if many users access the database simultaneously entire table can also be locked by using the LOCK TABLE statement. The syntax is: LOCK TABLE table-name IN { SHARE | EXCLUSIVE } MODE SQL supports the following lock types: SHARE - this lock type ensures that an application can read the table while other applications are also allowed to read the table but the table is locked. EXCLUSIVE - this lock type ensures that the application can change the table while other applications cannot gain access to the table and can neither read it nor change it.  Isolation Levels the isolation level shows to what extent the users are isolated from each other. in SQL, there are four isolation levels: Serializable if the isolation level is serializable, the users are separated from each other. Repeatable read if the isolation level is repeatable read or read repeatability, the share locks are set on all data that a user reads, and exclusive locks are placed on data that is change. these locks exist as long as the transaction runs this means that if a user executes the same SELECT statement several times within the same transaction, the result will always be the same.  Isolation Levels Cursor stability or read commit the same locks are placed as for repeatable read, the difference is that share locks are released if the SELECT statement is processed. in other words, after the SELECT statement has been processed, but before the transaction ends, data becomes available for the other users. this does not apply to changes, an exclusive lock is set on data that has been changed and remains there until the end of the transaction. Dirty read or read uncommitted for reading data, dirty read is equal to cursor stability. however, with dirty read, a user can see the changes carried out by another user before that user has made his changes permanent with a COMMIT statement. the exclusive lock is release immediately after a change but before the transaction ends. this means, if the user work with dirty read, the locking mechanism does not meet the serializability criterion.  Different Isolation Levels Different Isolation Levels  the isolation level can be set using a specific SQL statement for Oracle, MS-SQL Server the syntax is: SET TRANSACTION ISOLATION LEVEL for Adaptive Server Anywhere, the syntax is: SET OPTION ISOLATION_LEVEL = example: Assume that you need to generate a report that could be run at midnight of the last day of every month to count the products and quantities on hand in Makati warehouse with a warehouse id of 5. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse. Using Adaptivee Server Anywhere syntax: SET OPTION ISLOLATION_LEVEL = 2 SELECT prouct_id, quantity_on_hand FROM inventories WHERE warehourse_id = 5;  SUMMARY: A transaction is a logical unit of work Transaction processing in databases allows many transactions to execute at the same time with confidence. Concurrency is defined as simultaneous execution of operations A user may explicitly start a transaction using the START TRANSACTION statement The COMMIT statement is used to make permanent changes to the database or to terminate a user-defined transaction, A rollback can be executed either by the DBMS to prevent incorrect actions or explicitly by the user using the ROLLBACK statement. Savepoints are named placeholders in the sequence of statements in a transactions A deadlock is a phenomenon that can occur if many users access the database simultaneously. The isolation level shows to what extent the user are isolated from each other.  CPE C307 Advanced Database Systems  Learning Objectives We are learning to..... – Create a complex view that contains group functions to display values from two tables – Retrieve data from a view – Write and execute a query that performs DML operations on a simple view – Name the conditionsthat restrict your ability to modify a view using DML operations – Write and execute a query using the WITH CHECK OPTION clause – Create and execute a SQL statement that removes a view – Create and execute a query using an inline view  VIEW like a table, is a database object. However, views are not "real" tables. They are logical representations of existing tables or of another view. Views contain no data of their own. They function as a window through which data from tables can be viewed or changed.  VIEW The tables on which a view is based are called "base" tables. The view is a query stored as a SELECT statement in the data dictionary.  WHY USE VIEWS? Views restrict access to base table data because the view can display selective columns from the table. Views can be used to reduce the complexity of executing queries based on more complicated SELECT statements. For example, the creator of the view can construct join statements that retrieve data from multiple tables. The user of the view neither sees the underlying code nor how to create it. The user, through the view, interacts with the database using simple queries.  WHY USE VIEWS? Views can be used to retrieve data from several tables, providing data independence for users. Users can view the same data in different ways. Views provide groups of users with access to data according to their particular permissions or criteria.  CREATING A VIEW To create a view, embed a subquery within the CREATE VIEW statement. The syntax of a view statement is as follows: CREATING A VIEW CREATING A VIEW CREATING A VIEW GUIDELINES FOR CREATING VIEW The subquery that defines the view can contain complex SELECT syntax. The subquery that defines the view should not contain an ORDER BY clause. The ORDER BY clause is specified when you retrieve data from the view. You can use the REPLACE option to change the definition of the view without having to drop it or re-grant object privileges previously granted on it. Aliases can be used for the column names in the subquery.  CREATE VIEW FEATURES Two classifications of views are used: simple and complex. The table summarizes the features of each view.  SIMPLE VIEW The subquery derives data from only one table and it does not contain a join function or any group functions. Because it is a simple view, INSERT, UPDATE, DELETE, and MERGE operations affecting the base table could possibly be performed through the view.  SIMPLE VIEW Column names in the SELECT statement can have aliases as shown below. Note that aliases can also be listed after the CREATE VIEW statement and before the SELECT subquery.  SIMPLE VIEW It is possible to create a view whether or not the base tables exist. Adding the word FORCE to the CREATE VIEW statement creates the view. As a DBA, this option could be useful during the development of a database, especially if you are waiting for the necessary privileges to the referenced object to be granted shortly. The FORCE option will create the view despite it being invalid. The NOFORCE option is the default when creating a view.  COMPLEX VIEW Complex views are views that can contain group functions and joins. The following example creates a view that derives data from two tables. COMPLEX VIEW  COMPLEX VIEW Group functions can also be added to complex-view statements.  Modifying a View To modify an existing view without having to drop then re-create it, use the OR REPLACE option in the CREATE VIEW statement. The old view is replaced by the new version. For example:  DML STATEMENTS AND VIEWS The DML operations INSERT, UPDATE, and DELETE can be performed on simple views. These operations can be used to change the data in the underlying base tables. If you create a view that allows users to view restricted information using the WHERE clause, users can still perform DML operations on all columns of the view.  DML STATEMENTS AND VIEWS For example, the view shown on the right was created for the managers of department 50 from the employees database. The intent of this view is to allow managers of department 50 to see information about their employees.  CONTROLLING VIEWS Using the view as stated, it is possible to INSERT, UPDATE, and DELETE information for all rows in the view, even if this results in a row no longer being part of the view. This may not be what the DBA intended when the view was created. To control data access, two options can be added to the CREATE VIEW statement: WITH CHECK OPTION WITH READ ONLY  VIEWS WITH CHECK OPTION The view is defined without the WITH CHECK OPTION. Using the view, employee_id 124 has his department changed to dept_id 90. The update succeeds, even though this employee is now not part of the view.  VIEWS WITH CHECK OPTION The WITH CHECK OPTION ensures that DML operations performed on the view stay within the domain of the view. Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint. Notice in the example below that the WITH CHECK OPTION CONSTRAINT was given the name view_dept50_check. VIEWS WITH CHECK OPTION Now, if we attempt to modify a row in the view that would take it outside the domain of the view, an error is returned. VIEWS WITH READ ONLY The WITH READ ONLY option ensures that no DML operations occur through the view. Any attempt to execute an INSERT, UPDATE, or DELETE statement will result in an Oracle server error.  DML RESTRICTIONS Simple views and complex views differ in their ability to allow DML operations through a view. For simple views, DML operations can be performed through the view. For complex views, DML operations are not always allowed.  DML RESTRICTIONS You cannot: – remove a row from an underlying base table if the view contains any of the following: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM Keyword – modify data through a view if the view contains: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions  DML RESTRICTIONS You cannot: – add data through a view if the view: includes group functions includes a GROUP BY clause includes the DISTINCT keyword includes the pseudocolumn ROWNUM keyword includes columns defined by expressions does not include NOT NULL columns in the base tables DELETING A VIEW Because a view contains no data of its own, removing it does not affect the data in the underlying tables. If the view was used to INSERT, UPDATE, or DELETE data in the past, those changes to the base tables remain. Deleting a view simply removes the view definition from the database. Remember, views are stored as SELECT statements in the data dictionary. Only the creator or users with the DROP ANY VIEW privilege can remove a view. The SQL syntax to remove a view is:  INLINE VIEWS Inline views are also referred to as subqueries in the FROM clause. You insert a subquery in the FROM clause just as if the subquery was a table name. Inline views are commonly used to simplify complex queries by removing join operations and condensing several queries into one.  INLINE VIEWS As shown in the example below, the FROM clause contains a SELECT statement that retrieves data much like any SELECT statement. The data returned by the subquery is given an alias (d), which is then used in conjunction with the main query to return selected columns from both query sources. It is company policy that only upper-level management be allowed access to individual employee salaries. The department managers, however, need to know the minimum, max-imum, and average salaries, grouped by department. Use the Oracle database to prepare a view that displays the needed information for department managers.  INLINE VIEWS the FROM clause contains a SELECT statement that retrieves data much like any SELECT statement. The data returned by the subquery is given an alias (d), which is then used in conjunction with the main query to return selected columns from both query sources. Granting and Revoking Privileges SQL Privileges Types Authorization to read data Authorization to insert new data Authorization to update data Authorization to delete data DCL Commands GRANT is a command used to provide access or privileges on the database objects to the users REVOKE removes user access rights or privileges to the database objects Authorizations SELECT is required to read rows in the table UPDATE if granted to a user, this allow the user to modify any row in a table INSERT i granted to a user, this allows the user to insert row/s in a specific table DELETE if granted to a user, this allows that user to delete row/s in a table. GRANT Command basic syntax: GRANT  ON TO where privilege_name is the access right or privilege granted to the user object_name is the name of a database object like TABLE, VIEW, STORED PROC and SEQUENCE user_name is the name of the user to whom an access right is being granted role_name or roles are a set of privileges grouped together example 1: GRANT SELECT ON StudentInfo TO Juanito example 2: GRANT UPDATE (StudentNo) ON StudentInfo TO Juanito, Juanita REVOKE Command basic syntax: REVOKE ON  TO where privilege_name is the access right or privilege revoked from the user object_name is the name of a database object like TABLE, VIEW, STORED PROC and SEQUENCE user_name is the name of the user to whom a right is being disallowed role_name or roles are a set of disallowed privileges grouped together example 1: REVOKE SELECT ON StudentInfo TO Juanito example 2: REVOKE UPDATE (StudentNo) ON StudentInfo TO Juanito, Juanita Views are SELECT statements that have been saved in the database. Keywords related to Views CREATE VIEW ALTER VIEW DROP VIEW Create View CREATE VIEW is a keyword that enables users to create new views. syntax: CREATE VIEW ViewName AS SELECT statement; Example: tblEmployee EmployeeID EmpName 01-054 Jerry Mendoza 01-055 Dane Sevilla 01-556 Mylene Antonio tblLogRecords LogID EmployeeID LogDate 1 01-054 11-09-11 2 01-056 11-10-11 3 01-054 11-11-11 tblTimeRecords TimeRecID LogID TIn TOut 1 1 09:00 18:03 2 2 07:30 16:55 3 3 09:02 18:34 Statement A: SELECT tblEmployee.EmpName AS 'Name, tblLogRecords.LogDate AS 'Date', tblTimeRecords.TIn AS 'Time In', tblTimeRecords.TOut AS 'Time Out' FROM tblEmployee LEFT JOIN tblLogRecords ON tblEmployee.EmployeeID = tblLogRecods.EmployeeID LEFT JOIN tblTimeRecords ON tblLogRecords.LogID = tblTimeRecords.LogID; Statement B: CREATE VIEW EmployeeTimeSheetView AS SELECT tblEmployee.EmpName AS 'Name, tblLogRecords.LogDate AS 'Date', tblTimeRecords.TIn AS 'Time In', tblTimeRecords.TOut AS 'Time Out' FROM tblEmployee LEFT JOIN tblLogRecords ON tblEmployee.EmployeeID = tblLogRecods.EmployeeID LEFT JOIN tblTimeRecords ON tblLogRecords.LogID = tblTimeRecords.LogID; Referencing Views Example: SELECT * FROM EmployeeTimeSheetView; OUTPUT: EmployeeTimeSheetView Name Date Time In Time Out Jerry Mendoza 11-09-11 09:00 18:03 Jerry Mendoza 11-11-11 09:02 18:34 Mylene Antonio Null Null Null Dane Sevilla 11-10-11 7:30 16:55 Benefits of View Views can: reduce complexity increase reusability properly format data create calculated columns be used to rename column names create subset of data be used to enforce security restrictions Altering Views ALTER VIEW is the keyword used to modify a created view syntax: ALTER VIEW ViewName AS  SELECT statement; Example: Original Table tblStudentInfo: StudentNo FName MName LName 001 Jane Santos Aquino 002 Mark Guzman Perez 003 Leo Marquez Pacquiao Creating a View: CREATE VIEW StudentViews AS SELECT FName AS 'First Name', LName AS 'Last Name' FROM tblStudentInfo; StudentsView: First Name Last Name Jane Aquino Mark Perez Leo Pacquiao ALtering the views:  ALTER VIEW StudentViews AS SELECT FName AS 'First Name', MName AS 'Middle Name', LName AS 'Last Name' FROM tblStudentInfo; Altered StudentsView: First Name Middle Name Last Name Jane Santos Aquino Mark Guzman Perez Leo Marquez Pacquiao Deleting Views DROP VIEW is used to delete a view that was previously created syntax: DROP VIEW ViewName; Example: DROP VIEW StudentsView;