Home > Computers And Internet > Web Development

SQL Databases - powering today's applications

by Parveen Aggarwal

SQL database is a type of database technology that is the most widely used in today's computing environment. Here the data is stored in a very structured format that provides high levels of functionality. SQL databases are generally more robust, secure and have better performance than other older database technologies. It provides for ‘SQL' access to the data. So it is important to understand the term SQL before we proceed further.

What is SQL?

SQL pronounced either as "sequel" or "seekel" is an acronym for Structured Query Language, a language developed by IBM Corporation for processing data contained in mainframe computer databases. The relational model from which SQL draws much of its conceptual core was formally defined in 1970 by Dr. E. F. Codd, a researcher for IBM in his paper titled "A Relational Model of Data for Large Shared Data Banks". System/R project began in 1974 and developed SEQUEL or Structured English Query Language. System/R was implemented on an IBM prototype called SEQUEL-XRM during 1974-75. Later it included multi-table and multi-user features revised as SEQUEL/2 and renamed as "SQL".

SQL is used to create, maintain & query relational databases and uses regular English words for many of its commands, which makes it easy to use. It is often embedded within other programming languages. A fundamental difference between SQL and standard programming languages is that SQL is declarative. You specify what kind of data you want from the database; the RDBMS is responsible for figuring out how to retrieve it.

SQL Standards

SQL, the most popular relational database language was first standardized in 1986 by the American National Standards Institute (ANSI). Since then, it has been formally adopted as an International Standard by the International Organization for Standardization (ISO) and the International Electrotechnical Commission (IEC). Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language.

Database Language SQL is under continual development by the above mentioned standardization bodies. At present there are two committees that are working to ensure SQL standards - an International committee (ISO/IEC JTC 1/SC 32/WG 3) & an American committee (ANSI TC NCITS H2).

After 1986, a revised standard known commonly as SQL-89 or SQL1 was published in 1989. Due to partially conflicting interests from commercial vendors, much of the SQL-89 standard was intentionally left incomplete, and many features were labeled implementer-defined. In order to strengthen the standard, the ANSI committee revised its previous work with the SQL-92 standard ratified in 1992 also called SQL2. This standard addressed several weaknesses in SQL-89 and set forth conceptual SQL features, which at the time exceeded the capabilities of any existing RDBMS implementation. In fact, SQL-92 standard was approximately six times the length of its predecessor. In 1999, the ANSI/ISO released the SQL-99 standard also called SQL3. This standard addresses some of the more advanced and previously ignored areas of modern SQL systems such as object-relational database concepts, call level interfaces, and integrity management. Basically, SQL-99 replaces the SQL-92 levels of compliance (Entry, Intermediate, and Full) with its own degrees of conformance - Core SQL-99 and Non-core (Enhanced) SQL-99. Recently, in 2003, ANSI/ISO released the SQL-2003 standard also called SQL-200n. The big SQL-2003 features are: more collection data types, cleaner object/relational specification, and references to new parts such as XML. The big missing SQL-2003 feature is the SQL-99 standard BIT data type.
From the above discussion, it is clear that the three editions that matter today are:
• SQL-92 the previous standard
• SQL-99 the current standard
• SQL-2003 the evolving standard
Technology standards are important for several reasons - number one being that consumers are assured of what the product is supposed to do before they buy it. There are also several benefits to having a standard - for one, third-party vendors are able to create tools and utilities that apply to an entire market rather than to a specific platform. Similarly, individuals can become certified in a standard, increasing the size of your available resource pool. Product certification can help ensure quality functionality, as opposed to the pseudo-implementation of standards requirements by compliant, but not certified, solutions.

Daffodil DB presently conforms to most of the Entry - Level SQL92 standard, as well as many of the Intermediate- and Full-level features.

Working of 'SQL' in SQL database

The basic structure of the relational model of an SQL database is a table, consisting of rows and columns. Data definition includes declaring the name of each table to be included in a database, the names and data types of all columns of each table, constraints on the values in and among columns, and the granting of table manipulation privileges to prospective users. Tables can be accessed by inserting new rows, deleting or updating existing rows, or selecting rows that satisfy a given search condition for output. Tables can be manipulated to produce new tables by Cartesian products, unions, intersections, joins on matching columns, or projections on given columns.

SQL data manipulation operations may be invoked through a cursor or through a general query specification. The language includes all arithmetic operations, predicates for comparison and string matching, universal and existential quantifiers, summary operations for max/min or count/sum, and GROUP BY and HAVING clause to partition tables by groups. Transaction management is achieved through COMMIT and ROLLBACK statements.

The standard provides language facilities for defining application specific views of the data. Each view is the specification of database operations that would produce a desired table. The viewed table is then materialized at application execution time.
The SQL standard provides a Module Language for interface to other languages. Each SQL statement may be packaged as a procedure that can be called and have parameters passed to it from an external language. A cursor mechanism provides row-at-a-time access from languages that can only handle one row of a table at one time.
Access control is provided by GRANT and REVOKE statements. Each prospective user must be explicitly granted the privilege to access a specific table or view using a specific statement.

The SQL Integrity Enhancement facility offers additional tools for referential integrity, CHECK constraint clauses, and DEFAULT clauses. Referential integrity allows specification of primary and foreign keys with the requirement that no foreign key row may be inserted or updated unless a matching primary key row exists. Check clauses allow specification of inter-column constraints to be maintained by the database system. Default clauses provide optional default values for missing data.

The Embedded SQL specification provides SQL interface to programming languages, specifically Ada, C, COBOL, FORTRAN, MUMPS, Pascal, and PL/I. Applications may thereby integrate program control structures with SQL data manipulation capabilities. The Embedded SQL syntax is just shorthand for an explicit SQL Module accessed from a standard conforming programming language.
SQL-92 significantly increases the size of the original 1986 standard to include a schema manipulation language for modifying or altering schemas, schema information tables to make schema definitions accessible to users, new facilities for dynamic creation of SQL statements, and new data types and domains. Other new SQL-92 features include outer join, cascade update and delete referential actions, set algebra on tables, transaction consistency levels, scrolled cursors ,deferred constraint checking, and greatly expanded exception reporting. SQL-92 also removes a number of restrictions in order to make the language more flexible and orthogonal. Daffodil DB does support all the above mentioned features. As regards SQL-92, Daffodil DB can be placed somewhere midway between Intermediate and Fully compliant levels.

The major features that are introduced in SQL-99 are regular expression matching, recursive queries, triggers, non-scalar types and some object-oriented features. Daffodil DB supports all the above listed features with the exception of object-oriented features.

Major features in SQL-2003 are: XML-related features, window functions, standardized sequences and columns with auto-generated values (including identity-columns). All the above features are included in the Road map of Daffodil DB.

Advantages of SQL databases

1. High speed - SQL queries are designed to retrieve large amounts of records from a database quickly and efficiently. With the help of simple SQL queries, you can retrieve even highly complicated combination of data from the database. SQL databases are also much more adept at handling very large volumes of data and processing it quickly when compared to non-SQL databases.

2. Security - With the SQL database storage, you get the security of all the data being in one place, which you can manage easier.

3. Well-defined standards exist - SQL databases use the long-established SQL (Structured Query Language) standard, which has been adopted by ANSI in 1986 and since then continually evolving by the efforts of ANSI & ISO, the latest being SQL-2003. On the contrary, non-SQL databases do not have clear standards to adhere.

4. Compatibility - This is the direct fallout of the previous point regarding standards. Since well-defined and established standards exist, and if the databases adhere to those, then portability from one SQL database to another is a trivial matter. Further, an SQL database conforming to set standards can be easily accessed by third party softwares and application tools. This will facilitate the development of quality applications and solutions around SQL databases.

5. No coding required - By using standard SQL it should be easier to move applications between different database systems without the need to rewrite a substantial amount of code.

6. Emergence of ORDBMS - Earlier SQL databases were synonymous with relational databases. With the emergence of ORDBMS (Object-Relational Database Management Systems), object storage capabilities are extended to relational systems. On the other hand, power of SQL can now be made use in OODBMS as well.


Disadvantages of SQL databases

1. Premature Codification - Also known as "fast setting concrete", this refers to the fact that in SQL, you must define your tables and fields and the relationships between them, pretty much before you can do anything else. This is fine with the initial version of your application, but for another major version, you typically want to change your tables and fields all around. At the same time, you also want to ensure that the new version is compatible with the old version to make your users happy. In SQL, you have to write a lot of extra code to achieve this. Dealing with intermediate versions is almost worse: adding small things for 'your version 1.1' often cause changes to cascade through your code. So it can be concluded that SQL databases may be great for mature applications. But for rapidly-developing new applications, it can be a real blocker to progress.

2. No Automatic Table-to-Object Mapping - If you code in an object-oriented style, you will find yourself developing a layer whose only function is to map SQL tables to the objects that your program really uses, and then back again. This is a real waste of time and effort, and one can easily leave with the impression that this process can almost be automated. The work-around would be 'SQL object persistence-on-demand', a tool that will automatically take an instance of your object and, on command, store into or retrieve from an SQL database.

3. Difficulty in interfacing - Since an SQL database supports many advanced features, interfacing to an SQL database is more difficult than adding a few lines of code.

4. More features implemented in a Proprietary way - Although SQL databases conform to the SQL standards adopted by ANSI & ISO, some databases go for proprietary extensions to standard SQL so as to ensure vendor lock-in. Daffodil DB is an SQL database that almost conforms to SQL-99 specifications and does not have any proprietary extensions.

Major SQL databases

SQL databases can now be classified under two headings like SQL databases that are purely relational and a combination of both Object and Relational.

List of relational database management systems

Major RDBMSes

• Daffodil DB
• DB2
• Firebird
• Informix
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL


List of object-relational database management systems

Major ORDBMSes
• Oracle
• PostgreSQL

Minor ORDBMSes
• GigaBASE [1] (http://www.ispras.ru/~knizhnik/gigabase.html)
• UniSQL [2] (http://www.unisql.com)

Conclusion:

Hopefully this article has served to demystify SQL databases and how to start using them. We now know that a SQL database is just a handy and structured place to put all our data. This article has given us an introduction to the history of SQL databases, models of SQL and what role SQL play in the functioning of SQL databases.

We are now aware of what a SQL database is for, what its features, merits and demerits are and the available SQL database options in the market. This article also focuses on SQL99 and some of its key features.

Security and Compatibility are two most significant features of SQL databases. Storing data in a SQL database is more secure than ever before. SQL databases are compatible with almost all technologies which is probably the strongest reason of its wide acceptance. In SQL databases, the operations can be performed within the time constraints, as high speed query execution is another vital advantage of SQL databases. In this article, we have also pointed out the features of ORDBMS and the flavours it is available in.

The difference between advantages and disadvantages of SQL databases is narrowing day by day as developers are putting severe efforts in making SQL databases as user-friendly as possible. So we can expect some considerable improvements in the features of existing SQL databases in near future.