1 / 37

Chapter 10

Chapter 10. Application Development with Views. Outline. Background Creating views and using views Processing queries that reference views Updatable views Data requirements for hierarchical forms Data requirements for reports. What is a View?. Derived table

olive
Download Presentation

Chapter 10

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. Chapter 10 Application Development with Views

  2. Outline • Background • Creating views and using views • Processing queries that reference views • Updatable views • Data requirements for hierarchical forms • Data requirements for reports

  3. What is a View? • Derived table • Behaves like a base table (virtual) with some restrictions for view usage in modification statements • Stored query

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

  5. Three Schema Architecture

  6. 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

  7. 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

  8. 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

  9. Processing View Queries • Materialization • Execute two queries • Large overhead • Preferred in static environments such as data warehouses (Chapter 16) • Modification • Substitute view definition for view references • Execute one query • Incurs little overhead • Not possible for all view queries

  10. View Modification

  11. View Materialization

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

  13. 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

  14. 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'

  15. 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

  16. 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

  17. 1-M Updatable Queries • Associated with 1-M relationships • Join column of the parent table: primary key or unique • Determine updatable tables • Child table updatable • Primary key • Foreign key: must include in the query result • Required columns of the child table • Include primary key and required columns to support insert operations on the parent table • Use join operator style

  18. 1-M Updatable Query Example Example 10: Save as Course_Offering_View1 SELECT Course.CourseNo, CrsDesc, CrsUnits, Offering.OfferNo, OffTerm, OffYear, Offering.CourseNo, OffLocation, OffTime, FacSSN, OffDays FROM Course INNER JOIN Offering ON Course.CourseNo = Offering.CourseNo

  19. Usage of a 1-M Updatable Query Example 11: Insert a row into the Course_Offering_View1. INSERT INTO Course_Offering_View1 ( OfferNo, Offering.CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays ) VALUES ( 7799, 'IS480', 'Spring', 2003, 'BLM201', '1:30PM', '098-76-5432', 'MW' )

  20. Extensions to Multiple Tables • Apply rules to each 1-M relationship • FK of each child table in the query result • Usually only the lowest level child table is updatable • Examples • Course-Offering, Faculty-Offering • Offering-Enrollment, Faculty-Offering

  21. Hierarchical Forms • Formatted window for data entry and display • Main form • Subform • Provide attractive interface for a 1-M relationship • Specification of data requirements is important

  22. Revised University Database

  23. Example Hierarchical Form

  24. Analysis of Data Requirements • Identify the 1-M relationship • Identify the linking fields • Determine other tables in the main form and the subform • Determine updatable tables • Write queries for the main form and subform

  25. Registration Form Requirements • 1-M relationship: Registration-Enrollment • Linking fields: Registration.RegNo, Enrollment.RegNo • Other tables • Main form: Student • Subform: Offering, Course, Faculty • Updatable tables: Registration, Enrollment

  26. Registration Main Form Query SELECT RegNo, RegTerm, RegYear, RegDate, Registration.StdSSN, Registration.StdSSN, RegStatus, StdFirstName, StdLastName, StdClass, StdCity, StdState FROM Registration INNER JOIN Student ON Registration.StdSSN = Student.StdSSN

  27. Registration Subform Query SELECT RegNo, Enrollment.OfferNo, OffTime, Offering.CourseNo, OffLocation, OffTerm, OffYear, Offering.FacSSN, FacFirstName, FacLastName, CrsDesc, CrsUnits FROM ( ( Enrollment INNER JOIN Offering ON Enrollment.OfferNo = Offering.OfferNo ) INNER JOIN Course ON Offering.CourseNo = Course.CourseNo ) LEFT JOIN Faculty ON Faculty.FacSSN = Offering.FacSSN

  28. Faculty Assignment Form

  29. Faculty Assignment Requirements • Step 1: Faculty (parent table), Offering (child table) • Step 2: Faculty.FacSSN, Offering.FacSSN • Step 3: Course table in the subform • Step 4: update Offering.FacSSN

  30. Faculty Assignment Queries • Main form SELECT FacSSN, FacFirstName, FacLastName, FacDept FROM Faculty • Subform SELECT OfferNo, Offering.CourseNo, FacSSN, OffTime, OffDays, OffLocation, CrsUnits FROM Offering INNER JOIN COURSE ON Offering.CourseNo = Course.CourseNo

  31. Hierarchical Reports • Stylized presentation of data appropriate to a selected audience • Use nesting (or indentation) to provide a visually appealing layout • Vocabulary • Group: sorted field; usually indented • Detail line: innermost field

  32. Example Hierarchical Report

  33. Summary Data in Detail Lines

  34. Query Formulation Skills • Less difficult than skills for forms • Inspect report • Match fields in the report to database columns • Determine the needed tables • Identify the join conditions • Determine the level of detail • Row data versus summary data • Query should provide data for the detail lines

  35. Faculty Work Load Query SELECT Offering.OfferNo, FacFirstName, FacLastName, FacDept, OffTerm, CrsUnits, OffLimit, Count(Enrollment.RegNo) AS NumStds, NumStds/Offlimit AS PercentFull, (NumStds/Offlimit) < 0.25 AS LowEnrollment FROM Faculty, Offering, Course, Enrollment WHERE Faculty.FacSSN = Offering.FacSSN AND Course.CourseNo = Offering.CourseNo AND Offering.OfferNo = Enrollment.OfferNo AND ( ( Offering.OffTerm = 'Fall' AND Offering.OffYear = 2005 ) OR ( Offering.OffTerm = 'Winter' AND Offering.OffYear = 2006 ) OR ( Offering.OffTerm = 'Spring' AND Offering.OffYear = 2006 ) ) GROUP BY Offering.OfferNo, FacFirstName, FacLastName, FacDept, OffTerm, CrsUnits, OffLimit

  36. Summary • Significant benefits with a modest performance penalty • Foundation of application data requirements • Updatable views important for hierarchical forms • Carefully analyze data requirements before developing forms and reports

  37. Questions & Discussion

More Related