9 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 - Relational Views, The System Catalog, and Embedded SQL Views and Other Features Relational Views  View Definition  View Deletion  Data Retrieval from Views  View Update System Catalog Embedded SQL9 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Relational Views  Derived relations from other relations (base relations).  Has no stored tuples.  Provide multiple user views  Support logical data independence.  Provide data security  Simplification of users’ perspective Base Relation 1 Base Relation 2 View 1 View 2 View N Does this look familiar? View Creation CREATE VIEW view-name [ ( attr [ , attr ] ... ) ] AS subquery; DROP VIEW view-name; Create a view containing the part number, Part name, Color and Weight for those which have weight greater than or equals 15. CREATE VIEW Qualified_Part AS select p#, pname, color, weight from Part where weight >= 15;9 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] More on Views A view for all suppliers who supply P3 parts: create view P3_Supplier ( S#, Name, Project_Name ) as select SUPPLIER.S#, SUPPLIER.Sname, PROJECT.Jname from SUPPLIER, SPJ, PROJECT where SUPPLIER.S# = SPJ.S# and SPJ.J# = PROJECT.J# and SPJ.P# = ‘P3';  Can assign a name to each column in view.  If list of column names is specified, it must have same number of items as number of columns produced by sub-query.  If omitted, each column takes name of corresponding column in sub-query.  A view may be derived from multiple base relations  Need SELECT privileges on all tables referenced in subselect Note: This SQL statement will not work…why? View Retrieval select Pname from Qualified_Part where weight > 15; select Pname from PART where weight > 12 and color = ‘Red’;  Queries on views are the same as that on base relations.  Queries on views are expanded into queries on their base relations. select Name, Project_Name from P3_Supplier where Name = Project_Name; ? What is this query asking in plain English?9 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] View Update  Update on a view actually changes its base relations  Some views are not updatable  A view may be updatable if it contains all primary keys of its base relations update Qualified_Part set weight = 20 where P# = ‘P4’; insert into Qualified_Part values (‘P7’, ‘Wheel’,’Brown’, 15 ) insert into Qualified_Part values (‘P10', ‘Spacer', ‘Yellow’,8 ) View Update... insert into P3_Supplier values ( 's10', ‘Mark’, ‘Design’ ); Result ?9 - 5 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Summary Advantages of Views Data independence Improved security Reduced complexity Currency Convenience Customization Data integrity Disadvantages of Views Update restriction Structure restriction Performance9 - 6 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] View Materialization View resolution mechanism may be slow, particularly if view is accessed frequently. View materialization stores view as temporary table when view is first queried. Thereafter, queries based on materialized view can be faster than recomputing view each time. Difficulty is maintaining the currency of view while base tables(s) are being updated. System Catalog Stores the meta-data information about relations and users ORACLE Example: The SYS.TAB relation tells about relation or views: SYS.TAB ( TName, TabType, ClusterID ) where TName is relation/view name TabType distinguishes a relation from a view ClusterID indicates which cluster stores the table. The SYSTEM.SYSCATALOG relation records other information about a relation. SYSTEM.SYSCATALOG( TName, Creator, TableType, ClusterID, LogBlk, ReqBlk, IXComp,Remarks ) The SYS.COL relation stores information about attributes. SYS.COL( CName, TName, Creator, ColNo, ColType, ... ) The SYS.ALL_USERS relation keeps user information. SYS.ALL_USERS ( UserID, UserName, TimeStamp, ConnectAuth, ... ) and more ...9 - 7 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Embedded SQL SQL can be embedded in high-level procedural language. In many cases, language is identical although SELECT statement differs. Two types of programmatic SQL: Embedded SQL statements. • SQL supports Ada, C, COBOL, FORTRAN, MUMPS, Pascal, and PL/1. Application program interface (API). Example - CREATE TABLE EXEC SQL CREATE TABLE Viewing ( propertyNo VARCHAR2(5) NOT NULL, clientNo VARCHAR2(5) NOT NULL, viewDate DATE NOT NULL, comment VARCHAR2(40)); if (sqlca.sqlcode >= 0) printf(“Creation successful\n”);9 - 8 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Embedded SQL  Embedded SQL starts with identifier, usually EXEC SQL.  Ends with terminator dependent on host language:  Ada, ‘C’, and PL/1: terminator is semicolon (;)  COBOL: terminator is END-EXEC  Fortran: ends when no more continuation lines.  Embedded SQL can appear anywhere an executable host language statement can appear. SQL Communications Area (SQLCA) Used to report runtime errors to the application program. Most important part is SQLCODE variable: 0 - statement executed successfully; < 0 - an error