## 3 Semantic Expression of Relational Data Provenance
### Representation of Provenance Transformation
#### 3.4.3 Representing Provenance Transformation in Stored Procedures with Meta-Transformation  
Database-stored procedures are similar to SQL-like languages or other scripting languages, such as Python and Perl. In this paper, we only focus on SQL-like procedural languages, such as T-SQL for SQL Server, PL/SQL for Oracle, and PL/pgSQL for PostgreSQL. The stored procedure covers two kinds of flow structure: data flow (assignment, SQL execution) and control flow (conditional control, loop control). Only the data flow structure addresses the copy transformation relation of the data, while the control flow structure controls the switch branch of transformation. Here, we introduce how to use basic operations to represent each language feature. 1. Constant. In procedural language, when statement blocks do not reference any relational table in the database, the data output by this program can be regarded as coming from a constant relational table. Therefore, any query referencing a constant is treated as an ordinary import and expressed as \(ONull(\bot)\). 2. Assignment. The assignment operation assigns the calculation result of an expression to a variable. If values of an output tuple on some fields come from the variable, then a projection operation is used to project the input variable to the output tuple on these fields. Suppose that \(t[A]=e(u[B]),u\in Q(I),B\subseteq Q\), when \(A\) covers all the fields of t, then the transformation expression is \(OProj(\mathit{expr}_{Q})\). Otherwise, tuple \(t\) must contain values of other field set \(A^{\prime}\) other than \(A\), and the values are the result of other projection expressions. If there is \(t[A^{\prime}]=e^{\prime}(v[C]),v\in Q^{\prime}(I)\), \(C\subseteq Q^{\prime}\), then the transformation expression is \(OProj(OJoin(\mathit{expr}_{Q},\mathit{expr}_{Q^{\prime}}))\). 3. The SQL or dynamic SQL in stored procedures is equivalent to a nested subquery, and we convert it into a subexpression. 4. Conditional control statement block. A conditional control structure has multiple switch sub-statement blocks that cannot simultaneously execute, so the tuple can only be from one of the sub-statement blocks. Similar to a union operation, the transformation expressions of conditional control structure are \(OUnion(subExpr_{1},subExpr_{2},\ldots,\)  
Figure 4: The query \(Q_{3}\) and its output. \(subExpr_{n}\)) or \(subExpr_{i},i=1,2,\ldots,n\). \(OUnion\) is a requisite only when the present transformation is a subexpression of \(OAggr\). Therefore, a conditional control operation is equivalent to a union operation. 5. Loop control. Assume \(t\) is a variable in the body of the loop statement. Before entering the loop statement, \(PET(Q,t)=expr_{1}\), and the transformation expression of \(t\) in the loop body is \(expr_{2}\). After the execution of the loop statement, we have four cases: (a) if the judgment condition of the loop does not meet at the beginning, the loop body statement will not execute the following: \(PET(Q^{\prime},t)=expr_{1}\); (b) the loop body statement is executed only once as the following: \(PET(Q^{\prime},t)=expr_{2}\); (c) the loop body statement is executed multiple times, but \(expr_{2}\) does not reference the variable \(t\). Therefore, the expression \(t=expr_{2}\) is non-recursive, and the value of \(t\) is always the result of the last loop, namely \(PET(Q^{\prime},t)=expr_{2}\); and (d) the statement of the loop body is executed multiple times, and \(expr_{2}\) is recursive. Then, we have \(PET(Q^{\prime},t)=expr_{2}\)*, which shows that loop control is equivalent to one CTE. **Example 4**. : _Suppose function \(fn(x,y)\) returns the project number and the discount amount, where \(x\) and \(y\) are inputs representing the discount and the minimum quantity for the discount.