Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Relational Model Advantages Expand / Collapse
Author
Message
Posted Thursday, November 26, 2009 9:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 429, Visits: 3,083
In an earlier thread ("SQL Futures") it was suggested that more explanation is needed of the advantages of the Relational Model alternatives to SQL. Here are some examples of the benefits I would expect. I am making an assumption that any future RDBMS would at least meet the requirements of a “D”-compliant system - i.e. it would have features including relation variables, relational closure and so forth.

1. More cost effective OLAP. Many decision support systems make use of non-SQL data stores for their data even where that data is sourced from a SQL-based Data Warehouse, i.e. they use a HOLAP / MOLAP architecture. A principal reason for this is that SQL doesn’t provide the multi-dimensional data structures needed for business analytics. SQL returns its results as essentially “flat” two-dimensional tables - a 2D array or collection being the only data structures supported by SQL query interfaces.

The need to support multi-dimensional data stores separately from SQL is an expensive burden. For Oracle’s OLAP option for example the licence cost is around $20K per processor. Quite apart from the software cost there is the expense of maintaining and managing data and storage in two places and keeping them consistent.

The relational model is a n-dimensional data model. Relational queries preserve keys in their results, in other words they preserve the dimensional structure of the data which SQL does not. Therefore it is possible to use an RDBMS as a multi-dimensional data store to support analytical queries without a separate data store. Given that OLAP is a multi-billion dollar market I think the savings here could be very considerable indeed.

2. Better query optimization and storage strategies. SQL and RDBMS are both logical representations of data but relational databases have certain potential advantages when it comes to optimization. By freeing the database engine of the burden of supporting duplicates in tables and queries we would get the benefit of much smarter query optimisation and processing. The final benefit is of course hard to quantify but there is undoubtedly a large, well-documented body of research into relational database optimization which the industry is currently unable to take advantage of in SQL DBMSs. This ought to represent a very significant gain for RDBMS over SQL DBMS.

3. Developer productivity. SQL is a seriously deficient language for the 21st century when compared to other languages in the object-oriented world (C++, Java or C#). SQL’s 1980’s style type support, lack of type-inheritence and lack of relation types, relation variables or relation assignment are serious omissions that certainly cost development time and effort on practically every project. Incomplete support for set-based queries and the consequent need to rely on row-by-row processing are another feature of SQL. These defects are why abstraction layers that hide SQL complexity and limitations are so popular today.

Take a few examples. The need to eliminate duplicates from queries or from tables without keys are very common requests in forums that deal with SQL problems. Assignment and comparison of tables or sets of rows are two other very frequent SQL problems. Since SQL doesn’t have any straightforward syntax for table assignment or comparison the code has to be written again and again for each new project. RDBMS doesn’t suffer from any of these problems.

Inability to support anything other than a few basic types also causes big challenges for developers who are forced to write or duplicate code to emulate native or user-defined types in other languages.

As a very conservative estimate I think it’s not unreasonable to assume a full-time developer writing complex SQL might save 1-2 days per month by using a more full-featured relational language instead. In other words it could be a 5-10% saving on development costs.


In the above I've tried to concentrate on the benefits which I think are easily demonstrable and relatively easy to estimate. There are many other advantages too but these are pretty well covered in the work of Codd, Chris Date and others so I don't think there's much need to repeat them here.


David
Post #825279
Posted Sunday, December 06, 2009 2:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
David Portas (11/26/2009)
Relational queries preserve keys in their results, ...

I've been trying figure this out for a week now, and I just can't see how this is true. How can a relational query "preserve keys" across relational operations like PROJECT and JOIN? It just doesn't work as far as I can tell, even SELECT is questionable. Granted, there may be some subtlety that I am missing that allows it for JOIN (if so please let me know). But PROJECT seem plainly impossible, given that one or more of the key columns could be removed and that you cannot validly derive keys merely from data inferencing.




-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #829556
Posted Sunday, December 06, 2009 3:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 429, Visits: 3,083
RBarryYoung (12/6/2009)How can a relational query "preserve keys" across relational operations like PROJECT and JOIN?

Some examples:

P = PROJECT(a,b) R

If a is a key of R then a is a key of P
If b is a key of R then b is a key of P
Otherwise (a,b) is a key of P

Q = S JOIN T

The union of the key attributes of S and T is a superkey of Q (possibly a candidate key).

Key inference rules exist for all the relational operations. It's just a natural consquence of the closure property, ie: every result is a relation and every relation has at least one key.


David
Post #829563
Posted Sunday, December 06, 2009 3:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
David Portas (12/6/2009)
RBarryYoung (12/6/2009)How can a relational query "preserve keys" across relational operations like PROJECT and JOIN?

Some examples:

P = PROJECT(a,b) R

If a is a key of R then a is a key of P
If b is a key of R then b is a key of P
Otherwise (a,b) is a key of P
...

But this is a trivial example that dodges the concern that I raised. Specifically, what about:

P = PROJECT(a,c,d) of R(a,b: c,d) ?

Removing one of the columns of a multi-column candidate key leaves you with an ambiguous situation wherein it is impossible to "preserve keys" in any meaningful way. Sure you could declare the whole columnset (a,c,d) as a superkey, but that's not a relational key and ultimately meaningless since you can do that with ANY relation, relvar, relational rowset, or indeed with any SQL rowset so long as you use DISTINCT with your final SELECT. There's nothing actually being preserved here.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #829564
Posted Sunday, December 06, 2009 4:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 429, Visits: 3,083
All keys are superkeys. Identifying a candidate key of a relvar is purely a matter of interpretation because it depends on what the intended meaning of the relvar is - except in the special case where the candidate key is the empty set, which is obviously irreducible. Except in the case of the empty set, key constraints in a database are effectively superkey constraints (this incidentally being one criticism of SQL's daft "PRIMARY KEY" constraint - it's really a misleadingly named superkey constraint!).

Of course I agree that you can't necessarily derive candidate keys. Candidate keys must still exist though because every superkey implicitly contains a candidate key. So I don't see what you mean by "not a relational key".

In SQL you can only identify a superkey when there are no nullable columns and when DISTINCT is used. SQL query processors can't and don't assume that. They just don't bother to derive keys at all. Even if they did then the best they could do in many cases would be to assume the whole set of columns is a superkey. The advantage of the relational model is that you can identify smaller keys and more of them, which is much more useful. In decision support queries you normally want to distinguish the dimension attributes from the measures for example. You don't want measure columns to be considered as part of a key.


David
Post #829574
Posted Sunday, December 06, 2009 9:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 22,480, Visits: 30,163
All of this is well and interesting, unfortunately I have to work with what we have and make it perform as best as I can. I don't have the time or energy to engage in an academic discourse on relational theory, which I did learn while in college. It is a interesting topic, I must say, but until the industry can truely build a RDBMS based completely on relational theory, I will continue to work with SQL and the various databases that exist. At this moment, that happens to MS SQL Server, and it currently meets the needs of the organization for which I work.

Perhaps the academic community should strive to work more closely with the leaders in our industry to work at developing such an RDBMS as you envision. When that occurs, I'll be more than happy to learn some new and work with it.

Until then, I really don't see what I can do to change things. I am not in a real position to influence what Microsoft, Oracle, IBM, or any of the other SQL DBMS developers are doing. Yes, you could argue that my one voice added to many others could have an impact, but I need to work in the world of today, not the dreams of tomorrow and what it may provide.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #829634
Posted Sunday, December 06, 2009 10:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
Please don't bring the nightmare of inheritence anywhere near an RDBMS. And I don't care if SQL isn't up to speed when compared to GUI languages... they're simply not built for the same thing and I tire of the people that claim they are.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #829644
Posted Sunday, December 06, 2009 10:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 35,951, Visits: 30,236
SQL Server and all relational databases aren't much more than glorified file handlers. If you accept them as that, then you'll see the falacy of trying to do things like inheritence and the wad of other stuff people think it should do. SQL is not a full fledged language nor should it be. It's a simple file handling language. Tables are really nothing more than files with lines (rows) and fields (columns). It's a very simple thing... why do people keep trying to make it more complex?

And the automatic elimination of duplicates from queries won't fix anything... the engine will still have to remove them if someone writes stupid code that generates the duplicates either due to bad table design or, like I said, stupid code. Learn how to use the language and stop looking for it to make up for a lack of knowledge.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #829646
Posted Monday, December 07, 2009 12:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 429, Visits: 3,083
Lynn Pettis (12/6/2009)

Until then, I really don't see what I can do to change things. I am not in a real position to influence what Microsoft, Oracle, IBM, or any of the other SQL DBMS developers are doing.


Lynn,

All Microsoft, Oracle and IBM's customers are in a position to influence the features and future direction of their products. Those companies spend a lot of time listening to what customers want. All of them are constantly at work on the next version of their products. So discussing future features is not dreaming - it's something that is happening or can happen now.

Which brings me back to the main reason why I began this thread. Which group of customers is having the most influence on the future direction of database systems right now? Answer: application developers. Just look at the number of new DBMS models that have sprung up in the last 2 years or so. Google, Amazon and the NoSQL open source movement are responding to what developers need and they are changing the face of the DBMS market. The big three are responding to this and it's a trend that will influence the future direction of their products for better or worse.

This is a very hot topic right now for application developers, IT executives and strategists. However, as I've said before, what I've noticed is that SQL database professionals are almost silent on the topic. They don't seem to be part of the conversation right now and I don't think that's a healthy position to be in. As a community database professionals need to recognise the limitations and criticisms of SQL and propose a way forward. Because if they don't then someone else certainly will.



David
Post #829673
Posted Monday, December 07, 2009 5:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 22,480, Visits: 30,163
David,

I continually read to keep up on the changes that occur in technology. Unfortunately I don't see what you see. Most of the NoSQL DBMS's at this time appear to be niche systems answering very specific needs, not the needs in general when it comes to mainstream computing.

Also, until general LOB commercial applications begin using these DBMS's successfully, I don't see many business moving towards them either. Until that happens I will continue to support my organization the best way I can, which is making the best use of the tools I have at hand.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #829752
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse