Motivation: Find a few good references to make sense of SQL
Credit: Imagenation

Remarkably, given the pivotal role of SQL and data modeling, there is no "must have" reference books on SQL or data modeling. In the case of SQL there are some excellent books on individual databases that are of the highest quality but there are no books that integrate it all together at a high level of competence. So what we shall try to highlight in smaller reviews are some books of excellence.

Here is an excellent book on Information Modeling and RDBMS that takes a different and innovative approach to data modeling - this is the ORM - Object Role Modeling. ORM presents an extension to ER-Entity Relationship modeling and a very viable enhancement to UML's Use Case methods. In fact, Visio and then Microsoft incorporated ORM into their data modeling tools:Visio Modeler and Visual Studio Enterprise Architect respectively.

This book looks at SQL and relational databases in detail and posits the need for better modeling methods beyond ER and its variants such as IDEFIX and Information Engineering. Then it elaborates CSDP-Conceptual Schema Design Process which takes database development through a seven step process which refines a simple database design into a polished model. The steps of fact enumeration, uniqueness constraints and mandatory roles extend classic modeling methods - making it a book well worth reading.

Kevin and Daniel Kline's book SQL in a Nutshell first described the basic SQL syntax of four popular databases: two commercial databases- Oracle and Microsoft SQL Server 2000, and two Open Source databases - MySQL and PostgreSQL. The second book adds DB2 to the mix and over 200 more pages chock with examples of syntax and comparison to the SQL20o3 standard . The second edition is a monumental work that still manges to retain much of the referencing advanatges of the first editionThe book's pages manages to cram a lot of details about the DDL-Data definition Language, DML-Data Manipulation Language, and DCL-Data Control Language of each database's SQL implementation. Two facts become abundantly clear.

First, the Open Source databases are complete SQL implementations matching all of the basic ACID transaction requirements while delivering very good query capabilities. This confirms some of the recent benchmarks and reviews in the trade press. However, the commercial databases shine in some of their advanced capabilities for clustering, parallel processing, delivery alternatives and transcation processing backup and recovery. The second fact is unfortunate - all of the databases significantly differ in syntax among each other and also diverging from the ANSI SQL Standard- particularly in query functions, triggers, constraints, and stored procedure syntax. So if you regularly use one or more of these databases this is a irreplaceable and comprehensive first reference.

There are a number of highly competent books on Oracle including a number from Oracle Press. But here is the generally acknowledged best technical reference into Oracle by Oracle's own technical eveangelist Tom Kite. Expert One on One:Oracle certainly delivers on its promise.

The book tackles head on the key issues with any database - locking, concurrency control, transaction processing. The first 6 chapters delve into all these key DBMS issues. Then it carefully broadens scope taking in indexing, data loading and recovery, tuning and performance optimization, materialized views. In later chapters you have reviewed in constant clarity and depth C/Java APIs, access control, n-tier authentication, OLAP extensions. Until 1000 pages later, you realize that this is the crib sheet for getting the most out of your Oracle databases. There are so many nuggets of great information you soon lose count. An Oracle DBA's Bible.

There are over 120 books about SQL Server 2000 listed at Amazon.com. Of those the best quality books were clustered in the DBA arena or perfromance tuning where there 4 or 5 excellent books. Ken England's Microsoft SQL Server 2000 Performance Optimization stands out because the author attacks the very same core issues that Tom Kite does in his book on Oracle - locking, concurrency control, and transaction processing. And why not ? All the critical design decisions are driven by addressing these issues.

However, Ken does not stop here. In an almost conversational style he takes you through some of the key clustering, asynchronous processing and (for Microsoft especially)interoperability issues/tasks associated with SQL Server 2000. Again this is a SQL Server consultant's Bible.