Question

mvLPscreen(model, speed, ram, size, cost)

A materialized view is not supported in MySQL. However, it is possible to create a substitute with the same behavior by first creating a relation with one or more queries and then creating triggers to maintain the table when inserts, updates, and deletions occur.

For example, if we want a "materialized view" of laptops with screens at least 17 inches, we start by creating a new relation:

CREATE TABLE mvLPscreen AS SELECT * FROM laptop WHERE screen >= 17;

This creates a new relation and populates it with the results of our query. The problem in new relation is that it is static. If laptop relation changes the fake materialized view will not change. We can use triggers, one to handle inserts, one for updates, one for deletes. The triggers should make the corresponding change to the fake materialized view relation.

Demonstrate the correctness of your fake materialized view with a combination of select, insert, update, and delete queries on the laptop relation.

Answer 1

1. DELETE FROM laptop WHERE screen=18;

SELECT * FROM mvLPscreen WHERE screen=18; This query would return a set of rows.

SELECT * FROM laptop WHERE screen=18; This query returns NULL. This shows the inconsistency in our view.

2. UPDATE laptop SET cost=0 WHERE screen=19;

SELECT cost FROM mvLPscreen WHERE screen=19; This would return values other than zero.

SELECT cost FROM laptop WHERE screen=19; This would return all rows value as 0.

IF you want the triggers and then the queries to show correctness of triggers, Please mention in the comment and I would edit the anwer.