1 / 12

What is a View?

What is a View?. Derived table Behaves like a base table (virtual) Stored query. View Advantages. Reduce impact of database definition changes Simplify database usage Unit of database security Can be a performance penalty on complex views. Three Schema Architecture.

agnes
Download Presentation

What is a View?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What is a View? • Derived table • Behaves like a base table (virtual) • Stored query

  2. View Advantages • Reduce impact of database definition changes • Simplify database usage • Unit of database security • Can be a performance penalty on complex views

  3. Three Schema Architecture

  4. View Definition Example Example 1: Create a view consisting of offerings taught by faculty in the MS department. CREATE VIEW MS_View AS SELECT OfferNo, Course.CourseNo, CrsUnits, OffTerm, OffYear, Offering.FacSSN, FacFirstName, FacLastName, OffTime, OffDays FROM Faculty, Course, Offering WHERE FacDept = 'MS' AND Faculty.FacSSN = Offering.FacSSN AND Offering.CourseNo = Course.CourseNo

  5. Column Renaming Example 2: create a view containing offering data and the number of enrolled students. CREATE VIEW Enrollment_View ( OfferNo, CourseNo, Term, Year, Instructor, NumStudents ) AS SELECT Offering.OfferNo, CourseNo, OffTerm, OffYear, FacLastName, COUNT(*) FROM Offering, Faculty, Enrollment WHERE Offering.FacSSN = Faculty.FacSSN AND Offering.OfferNo = Enrollment.OfferNo GROUP BY Offering.OfferNo, CourseNo, OffTerm, OffYear, FacFirstName, FacLastName

  6. Using Views Example 3 SELECT OfferNo, CourseNo, FacFirstName, FacLastName, OffTime, OffDays FROM MS_View WHERE OffTerm = 'SPRING' AND OffYear = 2003 Example 4 SELECT OfferNo, Instructor, NumStudents, CrsUnits FROM Enrollment_View, Course WHERE Enrollment_View.CourseNo = Course.CourseNo AND NumStudents < 5

  7. Processing View Queries • Materialization • Execute two queries • Large overhead • Preferred in static environments • Modification • Substitute view definition for view references • Execute one query • Incurs little overhead • Not possible for all view queries

  8. Modification Example Example 5: Query using a view SELECT OfferNo, CourseNo, FacLastName FROM MS_View WHERE OffYear = 2003 Example 6: Modified query SELECT OfferNo, Course.CourseNo, FacLastName FROM Faculty, Course, Offering WHERE FacDept = 'MS' AND OffYear = 2003 AND Faculty.FacSSN = Offering.FacSSN AND Offering.CourseNo = Course.CourseNo

  9. Single Table Updatable Views • Support modification statements • Rules for single table updatable views • 1-1 correspondence between view rows and base table rows • View includes PK of base table • View includes all required columns • View definition does not have GROUP BY or DISTINCT

  10. Updatable View Examples Example 7: Single table updatable view CREATE VIEW Fac_View1 AS SELECT FacSSN, FacFirstName, FacLastName, FacRank, FacSalary, FacDept, FacCity, FacState, FacZipCode FROM Faculty WHERE FacDept = 'MS' Example 8: View update UPDATE Fac_View1 SET FacSalary = FacSalary * 1.1 WHERE FacRank = 'ASST'

  11. View Update with Side Effects • Modify column used in the WHERE condition of a view definition • Use WITH CHECK OPTION clause to prevent • Example 9: Change the department of rows in the Fac_View1 • UPDATE Fac_View1 • SET FacDept = 'FIN' • WHERE FacSalary > 100000

  12. Multiple Table Updatable Views • No industry standard • Only recently supported • More complex rules than single table updatable views • Access supports flexible view updates for multi-table views • Oracle rules in Appendix 10.B

More Related