Relational Theory and Programming PL/SQL

Database:

Feed Url:

http://vadimtropashko.wordpress.com/feed/

Nov 30, 2009 7:48pm
— ITNewsCast

After looking into formatting enhancements requests on various forums I can’t avoid the impression that the users and tools developers think along the following narrative:

*User*: “This particular syntax is not formatted as I like”

*Feature Implementer*: “OK, I’ll add a checkbox for that”

Mar 08, 2017 1:15am

Number of lines of code is well known to be poor metric for virtually any purpose. One thing is indisputable though: the total volume of code is growing. Today, developers are frequently challenged with mining huge codebases. Therefore, querying parse trees is not just an academic exercise, but something of ever increasing practical value.

Blog post, though, is hardly a proper venue to introduce a new language. Here are two references to supplement it:

Feb 10, 2017 11:15pm

Formatting is one of the software development features that always steers controversy. To put it bluntly, everybody wants to format the code their own way. In previous SQL Dev versions the answer to that challenge was having elaborate tree-table widget spiced with several dozens of various options.

Jan 03, 2017 10:15pm

In previous blog entry we have glimpsed into new SQL Developer 4.2 feature — parse trees. Again, this is something that development tools don’t readily expose with the most likely reason being the lack of foresight what users can do with this information. Blunt answer proposed here is: “query it”.

Oct 11, 2016 8:15pm

Parsing and internal parse structures — trees — are not something that development tools readily expose. It is assumed that most users are comfortable with numerous features that depend on parsing, such as formatting, code block collapsing/expanding, code advisors and so on, but aren’t really interested learning the formal code structure. In SQL Developer 4.2 we have attempted to encourage users to experiment with parse trees.

Sep 22, 2016 11:15pm

SQL Developer 4.2 early adopter release is out, but SQL performance analysis improvements somehow slipped from the list of additional features. **The enclosed manuscript ** describes three small but valuable improvements:

– Cancelling Long Running Queries (while extracting partial statistics)

– Object Hyperlinks

Sep 20, 2016 7:15pm

Two years ago we have ventured into mathematical foundation of relational theory. From algebraic geometry perspective relations were viewed as Finite Varieties.

Jan 04, 2016 10:15pm

Two years ago we have ventured into mathematical foundation of relational theory. From algebraic geometry perspective relations were viewed as Finite Varieties. In the followup exposition we were able to describe functional dependencies via explicit analytic formulas and provide intuitive interpretation of Heath’s theorem.

Dec 22, 2015 1:15am

A little over a year ago we have ventured into mathematical foundation of relational theory. From algebraic geometry perspective relations were viewed as Finite Varieties. In the followup exposition we were able to describe functional dependencies via explicit analytic formulas. In this article we shift the focus from varieties to dual algebraic object — [polynomial] *ideals*.

Jun 26, 2015 9:08pm

Database links allow clean solutions for many problems, which slowly began to recognize in oracle community. For example, oracle 12c features PDB instances communicating via database links. So, what is the status of SQLDeveloper database links in SQLDeveloper 4.1, a year since last announcement?

Apr 07, 2015 3:10pm

Recently, Litak & Mikulas & Hidders published the extended version of their earlier work. However, in a typical mathematical tradition following the famous saying “when fine building is revealed to the public eye, the scaffolding should be removed”, reading it requires some detective work. In particular, lets investigate the motivation behind their axiom system:

x ^ (y v z) = (x ^ (z v (R00 ^ y))) v (x ^ (y v (R00 ^ z))). % AxRH1

Aug 19, 2014 1:40pm

People invent really complicated ways to achieve simple tasks such as copying a table from one database to another. With database link (which is arguably RDBMS’s the most under appreciated feature) it is just one SQL command:

create table emp as select * from scott.emp@dblink1;

But who has time to create database links? In SQLDeveloper 4.0 you don’t have to. Assuming that you have the right connection the script

Apr 07, 2014 3:36pm

Relational Lattice is simplified “attribute-free” Relational Algebra. It allows to automatize Relational Algebra in the same spirit as Relation Algebra. Here is axiom system by Litak&Hidders&Mikulas:

Mar 28, 2014 8:38pm

Constraints have always been under appreciated in database practice. Today we’ll focus on *inclusion dependencies*, which in SQL community are better known as *foreign keys*. Let’s start with an example borrowed from David Spivak’s work on categorical foundation of databases — the familiar `Employees`

table

Employees = [empno mgr dept] 1 3 10 2 2 20 3 4 10 4 4 10 5 4 10

together with constraint

Mar 04, 2014 4:57pm

Here is really bizarre technique: one can perform union via join! Consider:

`(([p] 0 `

which outputs

`[q]`

a

b

c

(We assumed domain `p`

to include `{0,1}`

and `q`

to include `{a,b,c}`

, at least). You may object that the expression involves [domain dependent] union operation

, but it is applied for two relations with disjoint attributes, so it is nothing like classic union.

Feb 11, 2014 7:37pm

A recurring theme of this blog is that there is more to a relation than being merely a set of tuples. First, a relation is an object which obeys certain rules. The structure of this object is less important as long as it satisfies relational algebra axioms. Second, in previous post we have learned that relations are systems of constraints. The idea that a relation can be viewed as a set of constraints has been promoted in the 90s by Paris Kanellakis. Our intuition, however, is that by narrowing scope to equalities we can leverage powerful machinery of algebraic geometry.

Jan 03, 2014 11:21am

It is common wisdom that database field is firmly grounded in the two math disciplines: predicate logic and set theory. However, neither logic nor set theory are dominant math subjects. Just counting tags at mathoverflow.net can give rough idea of the importance of an individual math topic, and it is evident that *algebraic geometry* governs the math world. This field is so rich that it spawned new sub-fields, such as category theory.

Jan 02, 2014 2:52pm

The first Relational Algebra Tutorial video is here. This is part of undergraduate level introduction to databases course. One change prompted by awkward name confusion when trying to explain how “generalized/inner union” intersects attributes and unites tuples is introducing a better term: “seam”.

Sep 19, 2013 4:17pm

When it comes to teaching query languages within introductory database class, a system that supports relational algebra is a must. The first step is ASCII-matizing relational algebra syntax. One approach is to be faithful to RA notation by adopting LATEX syntax. Here is an example from popular Stanford class2go (registration required):

`\project_{pizza}`

\select_{20
(Eats \join Person);

Sep 03, 2013 4:41pm

Equations are ubiquitous in mathematics, physics, and computer science. In database field an optimizer rewriting a query to standard *restrict-project-join* leverages relational algebra identities. The craft of those reasoning abilities is culminated in Automated Theorem Proving systems.

Feb 01, 2013 6:10pm

There are many things in the world which existence bewilders rational mind. Humans are imaginative creatures, so one can’t restrain them from inventing silly things. This is why we are stuck with Daylight “saving” time, Letterbox Aspect Ratio, and “Climate Change”.

Dec 12, 2012 11:37am

Many people criticize SQL for bloated and inconsistent syntax. However, one must admit that some of its features give Relational Algebra run for the money. Consider recent stack overflow question “Relational Algebra equivalent of SQL “NOT IN””

Sep 24, 2012 2:24pm

Decomposition of a relation into join of projections serves as motivation for database normalization theory. In relational lattice terms relation `x`

projected into sets of attributes (that is empty relations) `s`

and `t`

:

`x = (x v s) ^ (x v t)`

Lets investigate dual perspective and switch the roles of *join* and *inner union*:

`x = (x ^ s) v (x ^ t)`

May 07, 2012 10:58am

Shadows of the Truth by Alexandre Borovik offers an interesting perspective upon human learning experience. The book is abundant with examples of concepts being challenging at the early age, but clarified later (sometimes, much later) in life. One of my stumbling blocks was the definition of implicit and explicit dependencies. Now, with modest help of relational thinking (that is, basic dependency theory) I can report some progress.

Feb 17, 2012 4:55pm

20th century has been highlighted by the two notable insights onto the nature of *information*. In 1948, Claude Shannon published classic paper “A Mathematical Theory of Communication” suggesting *quantitative* information measure — entropy — as average number of bits needed to store or communicate one symbol in a message. The paper has tremendous impact: it launched the whole new discipline — the *Information Theory*.

Jan 26, 2012 3:07pm

Code templates/snippets is one of modern IDE facilities accelerating programming. Some time ago I used to complain about cumbersome `main()`

function signature in Java which escaped my limited memorization abilities until learning that there is convenient code template offered by code assist, so the syntax doesn’t matter. Sometimes the answer is just *ctrl-space* away!

Oct 11, 2011 1:14pm

People imagination has no limits. Where does it lead SQL? Here is some bizarre syntax:

SELECT column1 FROM FINAL TABLE (INSERT INTO t1 VALUES(1,'John Smith') )

Wow, a whole semicolon has been spared — at the expense of syntax confusing relation with operator!

You would think DB2 designers would become laughing stock of SQL community? Wrong. Be prepared for some competition:

Sep 22, 2011 2:48pm

Here is curious identity:

**TABLE_DUM divided by a relation evaluates to the relation’s complement.**

Formally in QBQL:

`TABLE_DUM /= x = `

Aug 31, 2011 6:02pm

How weird is the language you are programming on? User survey on stackoverflow is the second most voted thread. Somewhere on page 2 we encounter the following SQL code snippet:

Jul 29, 2011 12:12pm

With ASR > 1M it is safe to assert that “SQL Design Patterns” book is effectively out of print. Since most of this site visitors are interested in chapter on tree encodings; here it is.

Jul 26, 2011 7:29pm

Oracle 11g introduced “syntax aware” code search facility — PL/Scope. One can look up identifier definitions, usages, exceptions, which is much more sophisticated and targeted search compared to the old way of finding all of the occurrences of a string in the `ALL_SOURCE`

. Certainly few developers are expected to use PL/Scope À la carte. IDEs, such as SQL Developer, accommodated it within its [object] search.

Jun 22, 2011 11:51am

Here is unconventional way to write a query in ANSI SQL join syntax SELECT 1 FROM DEPARTMENTS C JOIN EMPLOYEES A JOIN JOBS B ON C.DEPARTMENT_ID = A.DEPARTMENT_ID ON A.JOB_ID = B.JOB_ID It is valid syntax according to ANSI 92. Indeed, the fragment EMPLOYEES A JOIN JOBS B ON C.DEPARTMENT_ID = A.DEPARTMENT_ID is a qualified [...]

Mar 02, 2011 10:36am

Here is a solution to Iggy Fernandez The Second SQL challenge: WITH T(W2,OUT) AS ( SELECT WORD2, WORD1 || ' ' || WORD2 || ' ' || WORD3 FROM RIDDLE UNION ALL SELECT R.WORD2, T1.out || ' ' || R.WORD2 || ' ' || T2.out FROM T T1, RIDDLE R, T T2 WHERE T1.W2 = [...]

Feb 14, 2011 1:21pm

This is a legitimate program in QBQL: "for( int i = 0; i<10; i++ )" /= "sum += i"; which returns "for( int i = 0; i<10; i++ )"/="sum += i"=[sum] 45 ; Here is detailed article.

Nov 20, 2010 11:39am

SQL has many flaws, and the fact that its syntax poorly reflects duality of pivot and unpivot queries is probably somewhere at the bottom of the list. To be fair, relational algebra doesn’t do any better. What are pivot and unpivot queries in QBQL? Consider the following matrix example: This 2×2 matrix can be represented [...]

Oct 13, 2010 7:51pm

QBQL is, to put it bluntly, “Relational Algebra done right”; and this posting is about the recent improvements. 1. The operations from Date&Darwen’s “New Relational Algebra” are implemented as user-defined ones. Their definitions in relational lattice terms are in the udf.def file. 2. The include directive allows nesting QBQL programs inside each other. For example, [...]

Oct 07, 2010 5:51pm

The previous post about Boolean datatype sparkled a lively discussion at comp.databases.theory. Here, I’ll embark upon Tony Andrews comment that SQL DBMS world somehow abhors unary relations. This is very strange from theoretical perspective, as one can expect lower arity relations be prevalent according to Zipf law. This is one more artifact of infamous impedance [...]

Sep 30, 2010 8:54am

The question if Boolean is a legitimate SQL datatype is the source of long debates; witness the 10 years long discussion at Ask Tom, for example. Here is my perspective. To begin with, what is Boolean datatype, and why it is ubiquitous in each and every procedural programming language? Mathematically, depending how you look into [...]

Sep 16, 2010 12:57pm

Here is couple of queries which are easily expressed in SQL, but are much more challenging in Relational Algebra 1. Find a maximum in a set select max(x) from S This query is expressed in Relational Calculus as hence, it can be expressed in Relational Algebra as well. So, given unary relation S(x) and binary [...]

Jul 29, 2010 3:08pm

Relational Lattice theory originated as Relational Algebra converted into genuine mathematical shape. It gives a satisfactory foundation for ad-hock query transformation laws, such as push-selection-via-projection, which reduce to elementary algebraic laws of associativity, distributivity, absorption and so on.

Jul 13, 2010 6:14pm

Apparently, there is yet another bug in Matrix tree encoding ancestors query. Let’s trace derivation step-by-step to find out where the error crept in and fix it. A working example create table hierarchy ( name varchar2(10), a11 integer, a12 integer, a21 integer, a22 integer ); insert into hierarchy values('KING', 2, 1, 1, 0); insert into [...]

Jul 02, 2010 5:55pm

Given the two tables EMP and DEPT, what query could produce the following output? DNAME SUM(SAL) -------------- ---------------------- SALES 9400 SALES 12000 RESEARCH 10875 ACCOUNTING 8750 Hint: it requires putting couple extra rows into the standard EMP and DEPT tables.

Jun 24, 2010 4:41pm

Finding an axiomatic system with a minimal number of operations, or minimal number of axioms, or both is like a sport competition. It is challenge for players, and entertainment for spectators, but otherwise has little practical value. For Boolean algebra such a system is well known. In this posting we establish existence of a single [...]

May 14, 2010 2:11pm

This posting is an announcement of one more article about Relational Lattice. The later features an analysis of various [partial] order relationships, which may seems too abstract to untrained eye. Let’s assure the reader, that it has practical consequences. In database modeling area the “is-a” relationship is a partial order which coincides with the lattice [...]

Apr 27, 2010 4:42pm

New term for this month, borrowed from Algebra of Binary Relations: composition operation. Composition of two binary relations can be interpreted in Codd’s Relational Algebra (aka Algebra of Relations with Named Attributes) by taking their join, leading to a ternary relation, followed by a projection that removes the middle component. The less concise synonym for [...]

Jan 11, 2010 11:45am

Successful marketing is about introduction of new conspicuous terms. My experience is mixed: while, for example, materialized path and nested intervals were widely adopted, most of pattern terms introduced in “SQL Design Patterns” book are appeared to be dormant.

Today’s new term is comprehension index. The set theory axiom of comprehension allows one to define [...]

Dec 10, 2009 11:14am

Few code tips for SQL Developer 2.1 (EA is available on OTN).

Create query with few keystrokes

1. s then pause, or hit ctrl-space

in dropdown choose select * from

2. select * from then space

in dropdown choose multiple table names while holding the ctrl key. Suppose you have chosen dept and emp. The table/view names are [...]

Sep 28, 2009 3:02pm

There is a growing interest in Domain Specific Languages (DSL), and feeling that this technology would eventually render XML obsolete. Consider the following example of “2+2=4″ written in XML:

Aside from being insulting to reader’s intelligence, can you spot what is wrong with it? Where do these tree node annotations are coming from? Consider a grammar [...]

Mar 31, 2009 3:25pm

A tree structure is typically visualized as tree that “grows horizontally”, Windows file manager being a ubiquitous example. SQL execution plan is a tree which, until recently, has been rendered exclusively horizontal. Several client tools, however, broke up with this tradition and offered vertical display, while some articles cheer up this new option. Here is [...]

Mar 27, 2009 4:55pm

Iggy Fernandezs posted a challenge which looks like routine application of recursive query technique:

with njoins (face_value, probability, joins) as (

SELECT d1.face_value , d1.probability, 1

FROM die d1

union all

SELECT d1.face_value + d2.face_value AS SUM,

[...]

Mar 24, 2009 9:13pm

Contact | Privacy Statement | Company Info - © Copyright 2015