Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 276

delta relation of the view from the delta relations of table Employee .
Write an algorithm to update the view from the delta relation.
7.2 Consider a relation Connected ( CityFrom , CityTo , Distance ), which indi-
cates pairs of cities that are directly connected and the distance between
them, and a view OneStop(CityFrom, CityTo) , which computes the pairs
of cities ( c1 , c2 ) such that c2 can be reached from c1 passing through
exactly one intermediate stop.
Answer the same questions as those of the previous exercise.
7.3 Consider the following tables:
Store(StoreID,City,State,Manager)
Order(OrderID,StoreID,Date)
OrderLine(OrderID, LineNo, ProductID, Quantity, Price)
Product(ProductID, ProductName, Category, Supplier)
Part( PartID , PartName, ProductID, Quantity)
and the following views:
￿ ParisManagers(Manager) that contains managers of stores located in
Paris.
￿ OrderProducts(OrderID, ProductCount) that contains the number of
products for each order.
￿ OrderSuppliers(OrderID, SupplierCount) that contains the number of
suppliers for each order.
￿ OrderAmount(OrderID, StoreID, Date, Amount) which adds to the
table Order an additional column that contains the total amount of
each order.
￿ StoreOrders(StoreID, OrderCount) that contains the number of orders
for each store.
￿ ProductPart(ProductID, ProductName, PartID, PartName) that is
obtained from the full outer join of tables Product and Part .
Define the above views in SQL. For each of these views, determine
whether the view is self-maintainable with respect to insertions and
deletions. Give examples illustrating the cases that are not self-
maintainable.
7.4 Consider the following tables
Professor(ProfNo, ProfName, Laboratory)
Supervision(ProfNo,StudNo)
PhDStudent(StudNo, StudName, Laboratory)
and a view ProfPhdStud(ProfNo, ProfName, StudNo, StudName) com-
puted from the outer joins of these three relations.
Determine whether the view is self-maintainable. Write the SQL
command for creating the view. Show a possible instance of the tables
and the corresponding view. Give a delta table composed of insertions
to and deletions from the table Supervision and show how the view is
computed from these delta tables.