Transcript Chapter 10 of Database Design, Application Development and

Chapter 10
Application Development with Views
McGraw-Hill/Irwin
Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
Outline






Background
Creating views and using views
Processing queries that reference views
Updatable views
Data requirements for hierarchical forms
Data requirements for reports
10-2
What is a View?
 Derived table
 Behaves like a base table (virtual) with
some restrictions for view usage in
modification statements
 Stored query
10-3
View Advantages
 Reduce impact of database definition
changes
 Simplify database usage
 Unit of database security
 Can be a performance penalty on complex
views
10-4
Three Schema Architecture
View 1
External to
Conceptual
Mappings
Conceptual
to Internal
Mappings
View 2
Conceptual
Schema
Internal
Schema
View n
External
Level
Conceptual
Level
Internal
Level
10-5
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
10-6
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
10-7
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
10-8
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-9
View Modification
QueryV
Results
QueryB
Modify
SQL Engine
DB
QueryV: query that references a view
QueryB: modification of QueryV such that references to the view are
replaced by references to base tables.
10-10
View Materialization
Query d
SQL Engine
DB
Query d: Query that defines a
view
Query v
SQL Engine
Result
View
Query v: Query that references
a view
10-11
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
10-12
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-13
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'
10-14
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
10-15
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
10-16
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
10-17
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
10-18
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' )
10-19
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
10-20
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
10-21
Revised University Database
10-22
Example Hierarchical Form
10-23
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
10-24
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
10-25
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
10-26
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
10-27
Faculty Assignment Form
10-28
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
10-29
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
10-30
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
10-31
Example Hierarchical Report
10-32
Summary Data in Detail Lines
10-33
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
10-34
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
10-35
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
10-36