计算机专业毕业设计外文翻译资料--sql查询中的语意错误(编辑修改稿)

计算机专业毕业设计外文翻译资料--sql查询中的语意错误(编辑修改稿)内容摘要：

lways the same result as this one, except when EMP =  ； SELECT DISTINCT DNAME FROM DEPT Therefore, we will require the equivalence onlyfor states in which all relations are nonempty. It might even be possible to assume that all columns contain at least two different values. (3) Some types of errors produce many duplicates. More powerful query simplifications can be used if these duplicates are not considered as important for the equivalence (at least if the simpler query Q、 produces less duplicates than the more difficult query Q). . in the above example, we would want to delete the unused tuple variable even if DISTINCT were not specified. Duplicates are further considered in Section 5. Now we give a list of all cases in which a query can be obviouslysimplified under this slightlyweakened notion of equivalence. In each of these cases, a warning should be given to the user. . Entire query unnecessary Error 1. Inconsistent condition 毕业设计 (专业外文翻 译 ) 7 Nobody would pose a query if he or she knew beforehand that the query result is empty, no matter what the database state is. An example was given in the introduction. Note that it is also possible that the WHEREclause itself is consistent, but it contradicts a constraint in the database schema. For instance, since SQL has no enumeration types, one typcially restricts columns of type ‘‘CHAR’’ with constraints like CHECK(SEX = ‘ M’ OR SEX = ‘ F’ ) Now the condition SEX = ‘ W’ as part of a large conjunction in the WHEREclause would immediately make the query inconsistent. Yet, no DBMS we are aware of would give a warning. As mentioned before, the problem is in general undecidable (Abiteboul et al., 1994). But . for SQL queries that contain only one level of subqueries and no aggregations, it is decidable (see, ., Brass and Goldberg, 2020). In general, one could apply heuristic assumptions (., that a certain number of tuples per relation suffices) to generate warnings that are not always accurate, but still useful. Thereisa large bodyof literature about satisfiability tests and model construction, alsoin thecontextof databases(Bry andManthey, 1986。 Bry et al., 1988。 Klug, 1988。 Neufeld et al., 1993。 Guo et al., 1996。 Halevy et al., 2020。 Zhang et al., 2020。 Minock, 2020). In general, one could also construct other queries that have a constant result for all database states (under the assumption that relations are not empty). But this is at least very unmon (it did not occur in the analyzed exams). . SELECT clause Error 2. Unnecessary DISTINCT One should use an explicit duplicate elimination only if necessary. It sometimes can be proven that a query cannot return duplicates, . SELECT DISTINCT EMPNO, ENAME, JOB FROM EMP Because EMPNO isa key of EMP, the query could not return duplicates, even without DISTINCT. Then DISTINCT should not be used, because the query then will run slower (many optimizers of current DBMS do not remove the unnecessary duplicate 毕业设计 (专业外文翻 译 ) 8 elimination). Theoretitions sometimes remend to write always ‘‘ DISTINCT’’, but that shadows possible errors:Whena query does produce duplicates, it is often helpful to understand why. Algorithms for this error are discussed in Section 8. Error 3. Constant output column An output column is unnecessary if it contains a single value that is constant and can be derivedfrom the query without any knowledge about the database state. This was already illustrated at the beginning of this section. Of course, if a constant (datatype literal) is written as a term in the SELECTlist, this is obviously intended, and no warning should be given. Also SELECT * might yield a constant column, yet is shorter than listing the remaining columns explicitly, and again, no warning should be printed. However, at least the followingtypical case should be caught: The conjunctive normal form of the WHEREclause contains A = c with an attribute A and a constant c as one part of the conjunction, and A also appears as a term in the SELECTlist. Of course, if A = B appears in addition to A = c, also the value of B is obvious, and using B in the SELECTlist should generate a warning. And so on. With a higher warning level, one should run a model generator, and for each output column A run the model generator again with the additional condition A c, where c is the value of A in the first model. This would ensure that every output column can generate at least two different values (in possibly different database states). Error 4. Duplicate output column An output column is also unnecessary if it is always identical to another output column. . FROM clause: unnecessary tuple variables The next three errors are cases where tuple variables are declared under FROM that are not really necessary. Error 5. Unused tuple variable 毕业设计 (专业外文翻 译 ) 9 It is a simple syntax check to ensure that all tuple variables declared under FROM are really accessed somewhere in the query. If this condition is violated, one would typically also get Error 27 (missing join condition), but since ‘‘forgetting’’ a declared tuple variable happens quite often (it is Number 5 on our Top 10 list), a more specific error message might be preferable. (See also the discussion about equivalence at the beginning of this section.) Error 6. Unnecessary join If only the key attributes of a tuple variable X are accessed, and thiskeyis equated with the foreignkey of another tuple variable Y, X is not needed. Since joins are an expensive operation, there is a large body of literature about join elimination in query optimization (see, ., Aho et al., 1979。 Popa et al., 2020). However, at least one widely used mercial system does not seem to do a join elimination. Furthermore, one could argue that this is not the purpose of the quer。