Cơ sở dữ liệu - Chapter 10: Application development with views

Chapter 10Application Development with ViewsOutline BackgroundCreating views and using viewsProcessing queries that reference viewsUpdatable viewsData requirements for hierarchical formsData requirements for reportsWhat is a View?Derived tableBehaves like a base table (virtual) with some restrictions for view usage in modification statementsStored queryView AdvantagesReduce impact of database definition changesSimplify database usageUnit of database securityCan be a performance penalty on complex views Three Schema Architecture View Definition ExampleExample 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 Column RenamingExample 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 Using ViewsExample 3 SELECT OfferNo, CourseNo, FacFirstName, FacLastName, OffTime, OffDays FROM MS_View WHERE OffTerm = 'SPRING' AND OffYear = 2003Example 4 SELECT OfferNo, Instructor, NumStudents, CrsUnits FROM Enrollment_View, Course WHERE Enrollment_View.CourseNo = Course.CourseNo AND NumStudents 100000Multiple Table Updatable ViewsNo industry standardOnly recently supportedMore complex rules than single table updatable viewsAccess supports flexible view updates for multi-table viewsOracle rules in Appendix 10.B1-M Updatable QueriesAssociated with 1-M relationshipsJoin column of the parent table: primary key or uniqueDetermine updatable tablesChild table updatablePrimary key Foreign key: must include in the query resultRequired columns of the child tableInclude primary key and required columns to support insert operations on the parent tableUse join operator style 1-M Updatable Query ExampleExample 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 Usage of a 1-M Updatable QueryExample 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' )Extensions to Multiple TablesApply rules to each 1-M relationshipFK of each child table in the query resultUsually only the lowest level child table is updatableExamplesCourse-Offering, Faculty-OfferingOffering-Enrollment, Faculty-OfferingHierarchical FormsFormatted window for data entry and displayMain formSubformProvide attractive interface for a 1-M relationshipSpecification of data requirements is important Revised University DatabaseExample Hierarchical FormAnalysis of Data RequirementsIdentify the 1-M relationshipIdentify the linking fieldsDetermine other tables in the main form and the subformDetermine updatable tablesWrite queries for the main form and subformRegistration Form Requirements1-M relationship: Registration-EnrollmentLinking fields: Registration.RegNo, Enrollment.RegNoOther tablesMain form: StudentSubform: Offering, Course, FacultyUpdatable tables: Registration, EnrollmentRegistration Main Form QuerySELECT RegNo, RegTerm, RegYear, RegDate, Registration.StdSSN, Registration.StdSSN, RegStatus, StdFirstName, StdLastName, StdClass, StdCity, StdState FROM Registration INNER JOIN Student ON Registration.StdSSN = Student.StdSSN Registration Subform QuerySELECT 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 Faculty Assignment FormFaculty Assignment RequirementsStep 1: Faculty (parent table), Offering (child table)Step 2: Faculty.FacSSN, Offering.FacSSNStep 3: Course table in the subform Step 4: update Offering.FacSSN Faculty Assignment QueriesMain formSELECT FacSSN, FacFirstName, FacLastName, FacDept FROM FacultySubformSELECT OfferNo, Offering.CourseNo, FacSSN, OffTime, OffDays, OffLocation, CrsUnits FROM Offering INNER JOIN COURSE ON Offering.CourseNo = Course.CourseNoHierarchical ReportsStylized presentation of data appropriate to a selected audienceUse nesting (or indentation) to provide a visually appealing layoutVocabularyGroup: sorted field; usually indentedDetail line: innermost field Example Hierarchical ReportSummary Data in Detail LinesQuery Formulation SkillsLess difficult than skills for formsInspect reportMatch fields in the report to database columnsDetermine the needed tablesIdentify the join conditionsDetermine the level of detailRow data versus summary dataQuery should provide data for the detail linesFaculty Work Load QuerySELECT 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 SummarySignificant benefits with a modest performance penalty Foundation of application data requirementsUpdatable views important for hierarchical formsCarefully analyze data requirements before developing forms and reports