IT420: Database Management and Organization

1. IT420: Database Management and Organization SQL Views, Triggers and Stored Procedures 17 February 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. Last time • SQL Views Kroenke, Database Processing

3. Today • Updates on views • Triggers • Stored procedures Kroenke, Database Processing

4. SQL Views • SQL view is a virtual table that is constructed from other tables or views • It has no data of its own, but obtains data from tables or other views • It only has a definition Kroenke, Database Processing

5. CREATE VIEW Command • CREATE VIEW command: CREATE VIEW view_name AS select_statement • Use the view: • In SELECT statements • Sometimes in INSERT statements • Sometimes in UPDATE statements • Sometimes in DELETE statements Kroenke, Database Processing

6. Uses for SQL Views • Security: hide columns and rows • Display results of computations • Hide complicated SQL syntax • Provide a level of isolation between actual data and the user’s view of data • three-tier architecture • Assign different processing permissions to different views on same table • Assign different triggers to different views on same table Kroenke, Database Processing

7. Using Views Customers • Customer(CustID, CustName, Address, Phone) • CREATE VIEW CustomerV AS SELECT * FROM Customers • SELECT * FROM CustomerV SELECT * FROM CustomerV query result: Kroenke, Database Processing

8. Using Views Customers • CREATE VIEW CustomerV AS SELECT * FROM Customers • SELECT * FROM CustomerV WHERE Address LIKE ‘%Annapolis%’ SELECT query result: Kroenke, Database Processing

9. UPDATE on Views Customers table before update: • CREATE VIEW CustomerV AS SELECT * FROM Customers • UPDATE CustomerV SET Phone = ‘410-123-1234’ WHERE CustID = 01 Customers table after update: UPDATE impacts the Customers table Kroenke, Database Processing

10. INSERT on Views Customers table • CREATE VIEW CustomerV AS SELECT * FROM Customers • INSERT INTO CustomerV VALUES(‘08’,’Scott White’,’DC’,’401-456-3415’) Customers table after insert: INSERT impacts the Customers table Kroenke, Database Processing

11. DELETE on Views Customers table • CREATE VIEW CustomerV AS SELECT * FROM Customers • DELETE FROM CustomerV WHERE Address LIKE ‘%Annapolis%’ Customers table after delete: DELETE impacts the Customers table Kroenke, Database Processing

12. Using Views – Case 2 Customers • Customer(CustID, CustName, Address, Phone) • CREATE VIEW CustomerV2 AS SELECT CustID, CustName, Phone FROM Customers • SELECT * FROM CustomerV2 SELECT * FROM CustomerV2 query result: Kroenke, Database Processing

13. INSERT on Views - Case 2 Customers table • CREATE VIEW CustomerV2 AS SELECT CustID, CustName, Phone FROM Customers • INSERT INTO CustomerV2 VALUES(‘08’,’Scott White’,’401-456-3415’) Customers table after insert: Address NOT NULL, INSERT fails Kroenke, Database Processing

14. Views – Case 3 Rental • Rental(RentalID, CustID, PlaneID, NbHours, HRate) • CREATE VIEW RentalView AS SELECT RentalID, CustID, PlaneID, NbHours*HRate AS Charge FROM Rental • SELECT * FROM RentalView SELECT * FROM RentalView query result: Kroenke, Database Processing

15. INSERT on Views – Case 3 Rental • CREATE VIEW RentalView AS SELECT RentalID, CustID, PlaneID, NbHours*HRate AS Charge FROM Rental • INSERT INTO RentalView VALUES (03,113,01,1250) INSERT fails! UPDATE Charge fails! SELECT * FROM RentalView query result: Kroenke, Database Processing

16. Updateable Views • Views based on a single table • No computed columns • All non-null columns present in view • Views with INSTEAD OF triggers defined on them • Views based on a single table, primary key in view, some non-null columns missing from view • Updates for non-computed columns ok • Deletes ok • Inserts not ok Kroenke, Database Processing

17. Triggers • Trigger: stored program that is executed by the DBMS whenever a specified event occurs • Associated with a table or view • Three trigger types: BEFORE, INSTEAD OF, and AFTER • Each type can be declared for INSERT, UPDATE, and DELETE • Resulting in a total of nine trigger types Kroenke, Database Processing

18. Programming Languages for Triggers • Depends on DBMS • Java or PL/SQL for Oracle • T-SQL for SQL Server • C++, C#, Visual Basic .NET for SQL Server 2005 Kroenke, Database Processing

19. Firing Triggers • When a trigger is fired, the DBMS supplies: • Old and new values for the update • New values for inserts • Old values for deletions • The way the values are supplied depends on the DBMS product • Trigger applications: • Provide default values • Enforce data constraints • Update views • Perform referential integrity actions Kroenke, Database Processing

20. Create trigger • CREATE TRIGGER trigger_name ON table_or_view_name AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE AS trigger_code Kroenke, Database Processing

21. Trigger for Complex Default Value • Trans(TransactionID, WorkID, AcquisitionPrice, AskingPrice) • ArtistWorkNet(SaleID, WorkID, NetPrice) • AskingPrice = max of • 2*AcquisitionPrice • AcquisitionPrice+AVG(Past_NetPrice), if WorkID already in table Kroenke, Database Processing

22. Create trigger Declare variables Built-in function Kroenke, Database Processing

23. Kroenke, Database Processing

24. Trigger for Referential Integrity Actions – generic code Kroenke, Database Processing

25. Class Exercise • Students(Alpha, LName, FName, GPA) • Enroll(Alpha, CourseID, Semester, Grade) • GradeValues(LetterGrade, PointValue) • Define a trigger to update the GPA every time the student gets a new grade, or a grade changes Kroenke, Database Processing

26. Stored Procedures • A stored procedure is a program that is stored within the database and is compiled when used • In Oracle, it can be written in PL/SQL or Java • In SQL Server, it can be written in TRANSACT-SQL • Stored procedures can receive input parameters and they can return results • Stored procedures can be called from: • Programs written in standard languages, e.g., Java, C# • Scripting languages, e.g., JavaScript, VBScript • SQL command prompt, e.g., SQL*Plus, Query Analyzer Kroenke, Database Processing

27. Stored Procedure Advantages • Greater security as store procedures are always stored on the database server • SQL can be optimized by the DBMS compiler • Code sharing resulting in: • Less work • Standardized processing • Specialization among developers Kroenke, Database Processing

28. Kroenke, Database Processing

29. Triggers vs. Stored Procedures Kroenke, Database Processing

30. Project 1 – Due March 3, 2006 • National College Learning Center Organization (NCLCA) www.nclca.org • Membership • Conferences organized • Have: Partial user requirements (forms) • Tasks: • Design the ER model • Transform ER model to tables • Verify tables are normalized • Write SQL to create the tables in SQL Server • Write SQL to answer typical user queries • Write-up explaining your work Kroenke, Database Processing