Use of table expressions - expressing constraint conditions, PL-SQL Programming

Assignment Help:

Use of Table Expressions - Expressing Constraint Conditions

With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar and thus involve invocations of relational operators or aggregate operators. Assuming support for CREATE ASSERTION, we can always derive SQL counterparts of these examples using table expressions and truth-valued operators, but when that assumption does not hold we need to look for alternative solutions using table constraints. In most cases these will entail the use of subqueries and even that technique is prohibited by many implementations. In some cases special syntactic constructs are available, as we shall see, but there are several for which no SQL solution is available unless the implementation supports CREATE ASSERTION or subqueries in table constraints.Now, the reason usually given for lack of support for subqueries in constraints is that in general such expressions can require the DBMS to examine the entire content of possibly very large tables.

If database updates are expected to occur frequently-and are perhaps required to occur very frequently indeed- then declaration of such constraints would give rise to an intolerable slowing down of the updating process. Of course this is an extremely valid concern and we have to admit that integrity might occasionally have to be compromised for performance reasons, but consider the user with a small database that is subject to comparatively infrequent updating but nevertheless has strong integrity requirements. Might not such a user feel unfairly treated by a system that prohibits the declaration of required constraints? Defenders of the status quo respond to this argument by holding that language constructs that can give rise to disappointment for performance reasons, to such an extent as to militate against their use in common practical situations, should be banned. But sometimes users resort to implementing constraints, as best they can, in application code when they wish to enforce a constraint that is not supported by the DBMS but nevertheless does not adversely impair performance. The DBMS could almost certainly enforce such constraints much more efficiently and much more reliably. We can also point to various other SQL constructs that might be subject to similar concerns but are supported nonetheless. For example, if tables T1, T2, and T3 each contain 100,000 rows, then SELECT * FROM T1, T2, T3, when evaluated, delivers a table containing a quadrillion rows.


Related Discussions:- Use of table expressions - expressing constraint conditions

Package specification in pl/sql , Package Specification The package sp...

Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to the

Relational shema.., Find the account numbers of all customers whose balance...

Find the account numbers of all customers whose balance is more than 10,000 $

Write an anonymous block that contains a pl/sql function, a. Write an anon...

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym

Adding table constraints, Adding Table Constraints ALTER TABLE ENROL...

Adding Table Constraints ALTER TABLE ENROLMENT ADD CONSTRAINT NameNotNull CHECK (Name IS NOT NULL) ; ALTER TABLE ENROLMENT ADD CONSTRAINT PK_StudentId_CourseId PRIM

Information hiding in pl/sql, Information Hiding   With the informatio...

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

%rowcount - implicit cursor attributes, %ROWCOUNT The %ROWCOUNT yields...

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Set operators - sql operators, Set Operators The Set operators combine...

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Cursors in pl/sql, Cursors The Oracle uses work areas to execute the SQ...

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

Structure of an object type in pl/sql, Structure of an Object Type: Si...

Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd