tailieunhanh - Lecture Database Systems - Lecture 16
After completing this chapter, students will be able to: integrity enhancement feature, referential integrity, general constraint, data definition, create/alter/drop table, create/drop index, view updatability, advantages and disadvantages of views, view materialization. | CSC271 Database Systems Lecture # 16 Summary: Previous Lecture Integrity Enhancement Feature Referential integrity, general constraint Data definition CREATE/ALTER/DROP TABLE CREATE/DROP INDEX View CREATE/DROP VIEW View Resolution and restrictions View Updatability All updates to a base table are immediately reflected in all views that encompass that base table Similarly, we may expect that if a view is updated then the base table(s) will reflect that change View Updatability However, consider again the view StaffPropCnt, consider what would happen if we tried to insert a record that showed that at branch B003, staff member SG5 manages two properties, using the following insert statement: INSERT INTO StaffPropCnt VALUES (‘B003’, ‘SG5’, 2); Have to insert 2 records into PropertyForRent showing which properties SG5 manages However, do not know which properties they are; . do not know primary keys View Updatability Lets change definition of view and replace count with actual property numbers: CREATE VIEW StaffPropList (branchNo, staffNo, propertyNo) AS SELECT , , FROM Staff s, PropertyForRent p WHERE = ; And we try to insert the record: INSERT INTO StaffPropList VALUES (‘B003’, ‘SG5’, ‘PG19’); View Updatability Still problem, because in PropertyForRent all columns except postcode/staffNo are not allowed nulls However, have no way of inserting values for remaining non-null columns View Updatability ISO specifies that a view is updatable if and only if: DISTINCT is not specified; that is, duplicate rows must not be eliminated from the query results Every element in the SELECT list of the defining query is a column name (rather than a constant, expression, or aggregate function) and no column name appears more than once View Updatability The FROM clause specifies only one table . the view must have a single source table for which the user has the required privileges, If the source table is itself a view, then | CSC271 Database Systems Lecture # 16 Summary: Previous Lecture Integrity Enhancement Feature Referential integrity, general constraint Data definition CREATE/ALTER/DROP TABLE CREATE/DROP INDEX View CREATE/DROP VIEW View Resolution and restrictions View Updatability All updates to a base table are immediately reflected in all views that encompass that base table Similarly, we may expect that if a view is updated then the base table(s) will reflect that change View Updatability However, consider again the view StaffPropCnt, consider what would happen if we tried to insert a record that showed that at branch B003, staff member SG5 manages two properties, using the following insert statement: INSERT INTO StaffPropCnt VALUES (‘B003’, ‘SG5’, 2); Have to insert 2 records into PropertyForRent showing which properties SG5 manages However, do not know which properties they are; . do not know primary keys View Updatability Lets change definition of view and replace count with actual .
đang nạp các trang xem trước
