Question

Assume that we also have a table for suppliers: Supplier (supplierNo, partNo, price) and a view SupplierParts, which contains the distinct part numbers that are supplied by at least one supplier: CREATE VIEW SupplierParts (partNo) AS SELECT DISTINCT partNo FROM Supplier s, Part p WHERE s.partNo = p.partNo; Discuss how you would maintain this as a materialized view and under what circumstances you would be able to maintain the view without having to access the underlying base tables Part and Supplier.

   Assume that we also have a table for suppliers:
Supplier (supplierNo, partNo, price)
and a view SupplierParts, which contains the distinct part numbers that are supplied by at least one supplier:
CREATE VIEW SupplierParts (partNo)
AS SELECT DISTINCT partNo
FROM Supplier s, Part p
WHERE s.partNo = p.partNo;
Discuss how you would maintain this as a materialized view and under what circumstances you would be able to maintain the view without having to access the underlying base tables Part and Supplier.

Database Systems: A Practical Approach to Design, Implementation and Management

Thomas M. Connolly,… 4th Edition

Chapter 6, Problem 19

Instant Answer

Step 1

One way to do this is to schedule regular updates for the materialized view. This can be done using a job scheduler or a database trigger. The update process would involve querying the base tables Part and Supplier to retrieve the distinct part numbers and then Show more…

Show all steps

Thanks for your feedback!

Assume that we also have a table for suppliers: Supplier (supplierNo, partNo, price) and a view SupplierParts, which contains the distinct part numbers that are supplied by at least one supplier: CREATE VIEW SupplierParts (partNo) AS SELECT DISTINCT partNo FROM Supplier s, Part p WHERE s.partNo = p.partNo; Discuss how you would maintain this as a materialized view and under what circumstances you would be able to maintain the view without having to access the underlying base tables Part and Supplier.

Play audio

Feedback

Ace is your personal tutor. It breaks down any question with clear steps so you can learn.

Start Using Ace

Continue solving this problem

Create a free account to:

View full step-by-step solution
Ask follow-up questions with Ace AI
Save progress and study later

Please give Ace some feedback