Slide 1Chapter 9 SQL: Assertions, Views, and Programming TechniquesChapter OutlineChapter ObjectivesConstraints as AssertionsAssertions: An ExampleUsing General AssertionsSQL TriggersSQL Triggers: An ExampleViews in SQLSpecification of ViewsSQL Views: An ExampleUsing a Virtual TableEfficient View ImplementationSlide 15View UpdateUn-updatable ViewsDatabase ProgrammingDatabase Programming ApproachesImpedance MismatchSteps in Database ProgrammingEmbedded SQLExample: Variable Declaration in Language CSQL Commands for Connecting to a DatabaseEmbedded SQL in C Programming ExamplesSlide 26Dynamic SQLDynamic SQL: An ExampleEmbedded SQL in JavaJava Database ConnectivitySteps in JDBC Database AccessSteps in JDBC Database Access (continued)Embedded SQL in Java: An ExampleMultiple Tuples in SQLJDatabase Programming with Functional CallsSQL Call Level InterfaceComponents of SQL/CLISteps in C and SQL/CLI ProgrammingSteps in C and SQL/CLI Programming (continued)Database Stored ProceduresStored Procedure ConstructsSQL Persistent Stored ModulesSQL/PSM: An ExampleSummarySummary (continued)Chapter 9 SQL: Assertions, Views, and Programming TechniquesCopyright © 2004 Pearson Education, Inc.Chapter 9-3Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter Outline9.1 General Constraints as Assertions9.2 Views in SQL9.3 Database Programming9.4 Embedded SQL9.5 Functions Calls, SQL/CLI9.6 Stored Procedures, SQL/PSM9.7 SummaryChapter 9-4Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter ObjectivesSpecification of more general constraints via assertionsSQL facilities for defining views (virtual tables)Various techniques for accessing and manipulating a database via programs in general-purpose languages (e.g., Java)Chapter 9-5Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Constraints as AssertionsGeneral constraints: constraints that do not fit in the basic SQL categories (presented in chapter 8)Mechanism: CREAT ASSERTION–components include: a constraint name, followed by CHECK, followed by a conditionChapter 9-6Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Assertions: An Example“The salary of an employee must not be greater than the salary of the manager of the department that the employee works for’’CREAT ASSERTION SALARY_CONSTRAINTCHECK (NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.SALARY > M.SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))Chapter 9-7Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Using General AssertionsSpecify a query that violates the condition; include inside a NOT EXISTS clauseQuery result must be empty–if the query result is not empty, the assertion has been violatedChapter 9-8Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition SQL TriggersObjective: to monitor a database and take action when a condition occursTriggers are expressed in a syntax similar to assertions and include the following:–event (e.g., an update operation)–condition–action (to be taken when the condition is satisfied)Chapter 9-9Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition SQL Triggers: An ExampleA trigger to compare an employee’s salary to his/her supervisor during insert or update operations:CREATE TRIGGER INFORM_SUPERVISORBEFORE INSERT OR UPDATE OFSALARY, SUPERVISOR_SSN ON EMPLOYEEFOR EACH ROWWHEN(NEW.SALARY> (SELECT SALARY FROM EMPLOYEE WHERE SSN=NEW.SUPERVISOR_SSN))INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN;Chapter 9-10Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Views in SQLA view is a “virtual” table that is derived from other tablesAllows for limited update operations (since the table may not physically be stored)Allows full query operationsA convenience for expressing certain operationsChapter 9-11Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Specification of Views SQL command: CREATE VIEW–a table (view) name–a possible list of attribute names (for example, when arithmetic operations are specified or when we want the names to be different from the attributes in the base relations)–a query to specify the table contentsChapter 9-12Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition SQL Views: An ExampleSpecify a different WORKS_ON tableCREATE TABLE WORKS_ON_NEW ASSELECT FNAME, LNAME, PNAME, HOURSFROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBERGROUP BY PNAME;Chapter 9-13Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Using a Virtual TableWe can specify SQL queries on a newly create table (view):SELECT FNAME, LNAME FROM WORKS_ON_NEWWHERE PNAME=‘Seena’;When no longer needed, a view can be dropped:DROP WORKS_ON_NEW;Chapter 9-14Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Efficient View ImplementationQuery modification: present the view query in terms of a query on the underlying base tables–disadvantage: inefficient for views defined via complex queries (especially if additional queries are to be applied to the view within a short time period)Chapter 9-15Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Efficient View ImplementationView materialization: involves physically creating and keeping a temporary table–assumption: other queries on the view will follow–concerns: maintaining correspondence between the base table and the view when the base table is updated–strategy: incremental updateChapter 9-16Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition View UpdateUpdate on a single view without aggregate operations: update may map to an update on the underlying base tableViews
View Full Document
Unlocking...