Đang chuẩn bị liên kết để tải về tài liệu:
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; i.e. 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 s.branchNo, s.staffNo, p.propertyNo FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo; 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 i.e. 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; i.e. do not know primary keys View Updatability Lets change definition of view and replace count with actual .