Answer to Question #164024 in Databases | SQL | Oracle | MS Access for kahan

Question

Consider the global relations EMP(ENAME, TITLE, CITY) and ASG(ENAME, PNAME, CITY, DUR). City in ASG is the location of the project of name PNAME (i.e., PNAME functionally determines CITY). Consider the local relations EMP1(ENAME, TITLE, CITY), EMP2(ENAME, TITLE, CITY), PROJ1(PNAME, CITY), PROJ2(PNAME, CITY) and ASG1(ENAME, PNAME,DUR). Consider query Q which selects the names of the employees assigned to a project in Rio de Janeiro for more than 6 months and the duration of their assignment.

(a) Assuming the GAV approach, perform query rewriting.

(b) Assuming the LAV approach, perform query rewriting using the bucket algorithm.

Answer 1

A GAV mapping is a set of queries on local sources one for each element of the global relations. This can exactly tell us how the element is computed from the local relations.

A LAV mapping is a set of queries on the global schema one for each local source. In LAV views express how much sources contribute to the global relations.

Queries:

(a) CREATE VIEW GloRel AS

SELECT EMP.ENAME AS ENAME, EMP.TITLE AS TITLE, EMP.CITY AS CITY

FROM EMP

UNION

SELECT ASG.ENAME AS ENAME , ASG.PNAME AS PNAME , ASG.CITY AS CITY , ASG.DUR AS DUR

FROM ASG;

SELECT GloRel.ENAME

FROM GloRel

WHERE CITY IS “Rio de Janeiro” AND DUR > 6;
(b)  CREATE VIEW EMP1(ENAME, TITLE, CITY) AS

SELECT GloRel.ENAME AS EMP1.ENAME, GloRel.TITLE AS EMP1.TITLE, GloRel.CITY AS EMP1.CITY

FROM GloRel;

CREATE VIEW EMP2(ENAME, TITLE, CITY) AS

SELECT GloRel.ENAME AS EMP2.ENAME, GloRel.TITLE AS EMP2.TITLE, GloRel.CITY AS EMP2.CITY

FROM GloRel;

CREATE VIEW PROJ1(PNAME,CITY) AS

SELECT GloRel.PNAME AS PROJ1.PNAME, GloRel.CITY AS PROJ1.CITY

FROM GloRel;

CREATE VIEW PROJ2(PNAME,CITY) AS

SELECT GloRel.PNAME AS PROJ2.PNAME, GloRel.CITY AS PROJ2.CITY

FROM GloRel;

CREATE VIEW ASG1(ENAME, PNAME,DUR) AS

SELECT GloRel.ENAME AS ASG1.ENAME, GloRel.PNAME AS ASG1.PNAME, GloRel.DUR AS ASG1.DUR

FROM GloRel;

SELECT GloRel.ENAME

FROM GloRel

WHERE CITY IS “Rio de Janeiro” AND DUR > 6;