Cause of indeterminacy in sql, PL-SQL Programming

Cause of Indeterminacy in SQL

One root cause of indeterminacy in SQL lies in its implementation of comparison for equality. For certain system-defined types it is possible for distinct values to compare equal (note the contradiction). One such type is CHARACTER. Like COBOL, SQL ignores trailing "pad characters" when comparing character strings. The pad character is normally the space obtained by depressing the space bar on a keyboard. Thus, for example, the comparison 'SQL' = 'SQL ' evaluates to TRUE, even though CHAR_LENGTH('SQL') = CHAR_LENGTH('SQL '), comparing the lengths of those two strings, 3 and 6, evaluates to FALSE. Now consider the relational projection of ENROLMENT over just its Name attribute: ENROLMENT{Name} has a counterpart of projection, but suppose the two rows for student S1 in the ENROLMENT table had 'Anne' and 'Anne ' for S1's name.

If both of those values were to appear in the result, that would be inconsistent with the fact that they compare equal in SQL. If just one of them appears, then which one? The SQL standard declares such an expression to be possibly non-deterministic and permits a conforming implementation to give any value that compares equal to 'Anne'-possibly one that doesn't even appear in the table-and does not require it to give the same value every time the expression is evaluated. As a consequence, there are several SQL operators whose use on character strings is not permitted to appear in constraint declarations. The SQL standard lists a multitude of conditions that cause an expression to be defined as possibly non-deterministic. Perhaps the most alarming is the assumption that equals comparison of values of user-defined types is assumed to suffer from the same problem as I have described for character strings: it is assumed that distinct values can compare equal, even if the type definition is such that this cannot possibly be the case.

Posted Date: 1/18/2013 1:54:40 AM | Location : United States







Related Discussions:- Cause of indeterminacy in sql, Assignment Help, Ask Question on Cause of indeterminacy in sql, Get Answer, Expert's Help, Cause of indeterminacy in sql Discussions

Write discussion on Cause of indeterminacy in sql
Your posts are moderated
Related Questions
Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

What are 3 good practices of modeling and/or implementing data warehouses?

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Restriction in SQL Syntax: SELECT DISTINCT StudentId FROM IS_CALLED WHERE Name = 'Boris' The WHERE clause operates on the result of the FROM clause in analogo

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative