tailieunhanh - Lecture Fundamentals of database systems: Chapter 9 - Emasri, Navathe

After completing this chapter, students will be able to understand: Specification of more general constraints via assertions, SQL facilities for defining views (virtual tables), Various techniques for accessing and manipulating a database via programs in general purpose languages. | General Constraints as Assertions Views in SQL Database Programming Embedded SQL Functions Calls SQL CLI Stored Procedures SQL PSM Summary Specification of more general constraints via assertions SQL facilities for defining views virtual tables Various techniques for accessing and manipulating a database via programs in generalpurpose languages . Java C etc. Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 9- 3 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 9-4 General 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 condition Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 9- 5 The salary of an employee must not be greater 1 Specify a query that violates the condition include inside a NOT EXISTS clause Query result must be empty if the query result is not empty the assertion has been violated Triggers are expressed in a syntax similar to assertions and include the following Event Such as an insert deleted or update operation Condition Action To be taken when the condition is satisfied Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 9 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 9- 8 A trigger to compare an employee s salary to his her supervisor during insert or update operations CREATE TRIGGER INFORM_SUPERVISOR BEFORE INSERT OR UPDATE OF SALARY SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN SELECT SALARY FROM EMPLOYEE WHERE SSN INFORM_SUPERVISOR A view is a virtual table that is derived from other tables Allows for limited update operations Since the table may not physically be stored Allows full query operations A convenience for expressing certain operations Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 9- 9 SQL command CREATE VIEW Copyright 2007 Ramez Elmasri and Shamkant B.