Understanding DBMS: Functions and Duties in Database Systems

CSCE-608 Database Systems Spring 2025 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 17: On subqueries and views

lock table DDL language DDL complier database administrator concurrency control file logging & recovery manager transaction manager index/file manager buffer manager query execution engine database programmer DML (query) language DML complier main memory buffers secondary storage (disks) DBMS

SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; In principle, 1. Make a cross product of the relations from FROM; 2. Apply the select based on the condition given in WHERE; 3. Apply the projection given in SELECT. SELECT c FROM S(a,b), T(b,c) WHERE S.b = T.b AND a>4; <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c c T(b,c) <b-primary> <comp-pred> S.b=T.b AND a>4 <b-primary> <co-op> <exp> <exp> <comp-pred> <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> S(a,b) T(b,c) <term> <term> > T.b S.b <integer> <column-name> a 4

What Does DBMS Do? Prepare a collection A of efficient algorithms for operations in relational algebra; For a given database program P: 1. understand the program P; 2. translate the program P into an expression E in relational algebra; 3. convert E into an algorithm using algorithms in the collection A; take care of issues in optimization, consistency, and security.

What Does DBMS Do? An input database program P understand the program P parse tree translate the program P into an expression E in relational algebra logic query plan expression tree convert E into an algorithm using algorithms in the collection C Machine executable code

DDL Statements CREATE TABLE <name> (<list of elements>); CREATE VIEW <name> AS <table construction>; CREATE ASSERTION <name> CHECK (<condition>); CREATE TRIGGER <name> CREATE INDEX <name> ON <table>(<attribute-list>); DROP TABLE <name>; ALTER TABLE <name> ADD <attribute declaration>; ALTER TABLE <name> DROP <attribute>; DML Statements SELECT select-list FROM from-list WHERE conditions GROUP BY group-list HAVING conditions; (subquery) UNION (subquery); (subquery) INTERSECT (subquery); (subquery) EXCEPT (subquery); R CROSS JOIN S; R NATURAL JOIN S; R JOIN S ON <condition>; INSERT INTO <relation> VALUES (<list of values>); DELETE FROM <relation> WHERE <condition>; UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;

Sample (real) SQL Programs

Sample (real) SQL Programs SELECT sub.* FROM ( SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE day_of_week = 'Friday' ) sub WHERE sub.resolution = 'NONE' Subqueries may appear in the FROM clause

Sample (real) SQL Programs SELECT sub.* FROM ( SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE day_of_week = 'Friday' ) sub WHERE sub.resolution = 'NONE' Subqueries may appear in the FROM clause SELECT * FROM ReplA A WHERE A.ID IN (SELECT sum(B.ID2) OVER() FROM ReplB B WHERE A.ID2 = B.ID); Subqueries may appear in the WHERE clause

Sample (real) SQL Programs SELECT sub.* FROM ( SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE day_of_week = 'Friday' ) sub WHERE sub.resolution = 'NONE' Subqueries may appear in the FROM clause SELECT * FROM ReplA A WHERE A.ID IN (SELECT sum(B.ID2) OVER() FROM ReplB B WHERE A.ID2 = B.ID); Subqueries may appear in the WHERE clause SELECT DISTINCT LastName, FirstName, e.EmployeeKey FROM DimEmployee e JOIN FactResellerSales s ON e.EmployeeKey = s.EmployeeKey WHERE ProductKey IN (SELECT ProductKey FROM DimProduct WHERE ProductSubcategoryKey IN (SELECT ProductSubcategoryKey FROM DimProductSubcategory WHERE EnglishProductSubcategoryName LIKE '%Bikes')) ORDER BY LastName Subqueries may appear in both FROM/WHERE clauses, recursively

SQL-92 Grammar <from clause> ::= FROM <table list> <table list> ::= <table reference> | <table reference>, <table list> <table reference> ::= <table name> | <derived table> | <joined table> <derived table> ::= <table subquery> <where clause> ::= WHERE <search condition> <search condition> ::= <boolean term> | <boolean term> ::= <boolean factor> | <boolean factor> ::= [ NOT ] <boolean test> <boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] <boolean primary> ::= <predicate> | <predicate> ::= <comparison predicate> | <in predicate> | <exists predicate> | <in predicate> ::= <row value constructor> [ NOT ] IN <in predicate value> <in predicate value> ::= <table subquery> | <exists predicate> ::= EXISTS <table subquery>

SQL-92 Grammar <from clause> ::= FROM <table list> <table list> ::= <table reference> | <table reference>, <table list> <table reference> ::= <table name> | <derived table> | <joined table> <derived table> ::= <table subquery> FROM clause allows subqueries. <where clause> ::= WHERE <search condition> <search condition> ::= <boolean term> | <boolean term> ::= <boolean factor> | <boolean factor> ::= [ NOT ] <boolean test> <boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] <boolean primary> ::= <predicate> | <predicate> ::= <comparison predicate> | <in predicate> | <exists predicate> | <in predicate> ::= <row value constructor> [ NOT ] IN <in predicate value> <in predicate value> ::= <table subquery> | <exists predicate> ::= EXISTS <table subquery>

SQL-92 Grammar <from clause> ::= FROM <table list> <table list> ::= <table reference> | <table reference>, <table list> <table reference> ::= <table name> | <derived table> | <joined table> <derived table> ::= <table subquery> FROM clause allows subqueries. <where clause> ::= WHERE <search condition> <search condition> ::= <boolean term> | <boolean term> ::= <boolean factor> | <boolean factor> ::= [ NOT ] <boolean test> <boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] <boolean primary> ::= <predicate> | <predicate> ::= <comparison predicate> | <in predicate> | <in predicate> ::= <row value constructor> [ NOT ] IN <in predicate value> <in predicate value> ::= <table subquery> | WHERE clause allows subqueries.

If only simple relations and conditions are involved. \\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN SELECT c FROM S(a,b), T(b,c) WHERE S.b = T.b AND a>4; <statement> <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c T(b,c) <b-primary> <comp-pred> <b-primary> c <co-op> <exp> <exp> <comp-pred> S.b=T.b AND a>4 <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> <term> <term> > T.b S.b <integer> <column-name> S(a,b) T(b,c) a 4

If only simple relations and conditions are involved. How do we handle complicated situations ? <statement> \\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN SELECT c FROM S(a,b), T(b,c) WHERE S.b = T.b AND a>4; <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c T(b,c) <b-primary> <comp-pred> <b-primary> c <co-op> <exp> <exp> <comp-pred> S.b=T.b AND a>4 <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> <term> <term> > T.b S.b <integer> <column-name> S(a,b) T(b,c) a 4

subqueries in the FROM clause SELECT c FROM S(a,b), (SELECT * FROM T(b,c) WHERE c = 10) t WHERE S.b = t.b AND a>4;

subqueries in the FROM clause SELECT c FROM S(a,b), (SELECT * FROM T(b,c) WHERE c = 10) t WHERE S.b = t.b AND a>4; <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c <b-primary> <comp-pred> <b-primary> <co-op> <exp> <exp> <comp-pred> <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> <term> <term> > S.b T.b <integer> <column-name> a 4 parse tree for (SELECT * FROM T(b,c) WHERE c = 10) t

subqueries in the FROM clause SELECT c FROM S(a,b), (SELECT * FROM T(b,c) WHERE c = 10) t WHERE S.b = t.b AND a>4; <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c <b-primary> c <comp-pred> <b-primary> S.b=t.b AND a>4 <co-op> <exp> <exp> <comp-pred> <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> <term> <term> > S(a,b) t S.b T.b <integer> <column-name> a 4 parse tree for (SELECT * FROM T(b,c) WHERE c = 10) t logic plan for (SELECT * FROM T(b,c) WHERE c = 10) t

\\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN subqueries in the FROM clause SELECT c FROM S(a,b), (SELECT * FROM T(b,c) WHERE c = 10) t WHERE S.b = t.b AND a>4; <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> <select-sublist> <tbl-name> <tbl-list> , <b-term> <column-name> <b-term> and <b-facor> <tbl-name> S(a,b) <b-facor> c <b-primary> c <comp-pred> <b-primary> S.b=t.b AND a>4 <co-op> <exp> <exp> <comp-pred> <term> <term> = <co-op> <exp> <exp> <column-name> <column-name> <term> <term> > S(a,b) t S.b T.b <integer> <column-name> a 4 parse tree for (SELECT * FROM T(b,c) WHERE c = 10) t logic plan for (SELECT * FROM T(b,c) WHERE c = 10) t

subqueries in the WHERE clause

subqueries in the WHERE clause SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ;

subqueries in the WHERE clause SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ;

subqueries in the WHERE clause SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; \\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN Can we make a logic query plan like this? b S.a > ANY (SELECT c FROM T(c,d) WHERE d= 10) S(a,b)

subqueries in the WHERE clause SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; \\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN Can we make a logic query plan like this? b S.a > ANY (SELECT c FROM T(c,d) WHERE d= 10) S(a,b) NO! Conditions in involved in subqueries can cause significant inefficiency and/or even logic inconsistency

subqueries in the WHERE clause SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; \\ Parse tree to Exp tree: for SFW 1. create a node; 2. collect the attributes in child-2 and attach them to the node; 3. IF child-5 NULL THEN create a node and make it the child of the node; get the conditions in child-6 and attach it to the node; 4. create a node and make it the child of the node; 5. make the tables in child-4 the children of the node; 6. IF child-7 NULL THEN Can we make a logic query plan like this? b S.a > ANY (SELECT c FROM T(c,d) WHERE d= 10) S(a,b) Then what will we do?

subqueries in the WHERE clause EXIST R s IN R s > ALL R s > ANY R SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ;

subqueries in the WHERE clause EXIST R s IN R s > ALL R s > ANY R SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; Each operation has a different conversion procedure. In general, 1.Convert the subquery into a logic plan; 2.Collect the attributes for the condition; 3.Make a cross product with the main relations; 4.Apply the conditions in the cross product.

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ;

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> <column-name> <b-facor> S(a,b) b <b-primary> <comp-pred> <exp> <co-op> <exp> > <term> any <column-name> S.a Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> <column-name> <b-facor> S(a,b) b <b-primary> <comp-pred> <exp> <co-op> <exp> > <term> any <column-name> S.a logic plan for SELECT c FROM T(c,d) WHERE d = 10 Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> <column-name> <b-facor> S(a,b) b <b-primary> c <comp-pred> <exp> <co-op> <exp> d = 10 > <term> any T(c,d) <column-name> S.a logic plan for SELECT c FROM T(c,d) WHERE d = 10 Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> <column-name> <b-facor> S(a,b) b <b-primary> c <comp-pred> S(a,b) <exp> <co-op> <exp> d = 10 > <term> any T(c,d) <column-name> S.a logic plan for SELECT c FROM T(c,d) WHERE d = 10 Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> b select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> S.a > U.c <column-name> <b-facor> S(a,b) b <b-primary> c <comp-pred> S(a,b) <exp> <co-op> <exp> d = 10 > <term> any T(c,d) <column-name> S.a logic plan for SELECT c FROM T(c,d) WHERE d = 10 Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> b select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> S.a > U.c <column-name> <b-facor> S(a,b) b <b-primary> c <comp-pred> S(a,b) <exp> <co-op> <exp> d = 10 > <term> any T(c,d) <column-name> S.a Parse tree for SELECT c FROM T(c,d) WHERE d = 10

subqueries in the WHERE clause 1. Convert the subquery into a logic plan; 2. Collect the attributes for the condition; 3. Make a cross product with the main relations; 4. Apply the conditions in the cross product. SELECT b FROM S(a,b) WHERE S.a > ANY (SELECT c FROM T(c,d) WHERE d = 10) ; <statement> <select-statement> b select <select-list>from<tbl-list> where <search-condition> <select-sublist> <tbl-name> <b-term> S.a > U.c <column-name> <b-facor> S(a,b) b <b-primary> <comp-pred> S(a,b) <exp> <co-op> <exp> c > <term> any d = 10 <column-name> T(c,d) S.a Parse tree for SELECT c FROM T(c,d) WHERE d = 10

What Does DBMS Do? An input database program P understand the program P parse tree translate the program P into an expression E in relational algebra logic query plan convert E into an algorithm using algorithms in the collection C Machine executable code

What Does DBMS Do? An input database program P understand the program P parse tree View processing, Semantic checking preprocessing parse tree translate the program P into an expression E in relational algebra logic query plan convert E into an algorithm using algorithms in the collection C Machine executable code

View Processing

View Processing CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h

View Processing <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> , <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <column-name> <b-primary> U(h,b) b <comp-pred> <exp> <co-op> <exp> <term> > <term> <column-name> <column-name> U.h T.g CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h

View Processing <statement> <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <column-name> <b-primary> U(h,b) b <comp-pred> <exp> <co-op> <exp> <term> > <term> <column-name> <column-name> U.h T.g CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <column-name> <b-primary> U(h,b) b <comp-pred> <exp> <co-op> <exp> <term> > <term> <column-name> <column-name> U.h T.g CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <statement> <column-name> <b-primary> <select-statement> U(h,b) b <comp-pred> <tbl-list>where , select <select-list>from <exp> <co-op> <exp> <search-condition> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-term> <term> > <term> a <tbl-name> V(a,b) <column-name> <b-facor> <column-name> <column-name> S(c,d) d U.h T.g <b-primary> <comp-pred> CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h <exp> <co-op> <exp> <term> <term> = <column-name> S.c V.b

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <statement> <column-name> <b-primary> <select-statement> U(h,b) b <comp-pred> <tbl-list>where , select <select-list>from <exp> <co-op> <exp> <search-condition> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-term> <term> > <term> a <tbl-name> V(a,b) <column-name> <b-facor> <column-name> <column-name> S(c,d) d U.h T.g <b-primary> <comp-pred> CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h <exp> <co-op> <exp> <term> <term> = <column-name> S.c V.b

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <statement> <column-name> <b-primary> <select-statement> U(h,b) b <comp-pred> <tbl-list>where , select <select-list>from <exp> <co-op> <exp> <search-condition> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-term> <term> > <term> a <tbl-name> V(a,b) <column-name> <b-facor> <column-name> <column-name> S(c,d) d U.h T.g <b-primary> <comp-pred> CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h <exp> <co-op> <exp> replace it by its parse tree <term> <term> = <column-name> S.c V.b

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <statement> <column-name> <b-primary> <select-statement> U(h,b) b <comp-pred> <tbl-list>where , select <select-list>from <exp> <co-op> <exp> <search-condition> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-term> <term> > <term> a <tbl-name> V(a,b) <column-name> <b-facor> <column-name> <column-name> S(c,d) <statement> d U.h T.g <b-primary> <select-statement> <comp-pred> select <select-list>from<tbl-list>where <search-condition> , CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h <exp> <co-op> <exp> <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <term> <term> <column-name> <tbl-name> = <b-facor> a T(a,g) <column-name> <column-name> <b-primary> U(h,b) b S.c V.b <comp-pred> <exp> <co-op> <exp> <term> > <term> <column-name> <column-name> U.h T.g

View Processing <statement> V is a view <select-statement> select <select-list>from<tbl-list>where <search-condition> , SELECT a, d FROM V(a,b), S(c,d) WHERE V.b = S.c <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-facor> a <tbl-name> T(a,g) <statement> <column-name> <b-primary> <select-statement> U(h,b) b <comp-pred> <tbl-list>where , select <select-list>from <exp> <co-op> <exp> <search-condition> , <column-name> <tbl-list> <tbl-name> <select-sublist> <b-term> <term> > <term> a <tbl-name> V(a,b) <column-name> <b-facor> <column-name> <column-name> S(c,d) <statement> d U.h T.g <b-primary> <select-statement> <comp-pred> select <select-list>from<tbl-list>where <search-condition> , CREATE VIEW V AS SELECT a, b FROM T(a,g), U(h,b) WHERE T.g > U.h <exp> <co-op> <exp> <b-term> , <column-name> <tbl-list> <tbl-name> <select-sublist> <term> <term> <column-name> <tbl-name> = <b-facor> a T(a,g) <column-name> <column-name> <b-primary> U(h,b) b S.c V.b <comp-pred> Therefore, for each CREATE VIEW statement, construct its parse tree. In the parse tree for a statement that uses the view, replace the view name with the parse tree for the view. <exp> <co-op> <exp> <term> > <term> <column-name> <column-name> U.h T.g

What Does DBMS Do? An input database program P understand the program P parse tree View processing, Semantic checking preprocessing parse tree translate the program P into an expression E in relational algebra logic query plan convert E into an algorithm using algorithms in the collection C Machine executable code

What Does DBMS Do? An input database program P understand the program P parse tree View processing, Semantic checking preprocessing parse tree translate the program P into an expression E in relational algebra logic query plan convert E into an algorithm using algorithms in the collection C Machine executable code

What Does DBMS Do? An input database program P View processing, Semantic checking parser parse tree preprocessing parse tree parse tree-lqp convertor logic query plan Lqp-pqp convertor physical query plan Machine executable code