Structured Query Language, SQL

Leave a comment

May 18, 2014 by Ozgur Ozden

Structured Query Language, SQL, can be considered as the main language of almost all relational database management systems (RDBMS). SQL and relational model is first introduced by Edgar F. Codd at 1970 in his paper named “A Relational Model of Data for Large Shared Data Banks”. American National Standards Institute (ANSI) standardized SQL as official standard in 1986 as and named as SQL-86. Starting from 1986 till 2011 SQL SQL’s structure revised many times. Wikipedia summarizes these changes nicely in a table as shown below:

YEAR RELEASED
GIVEN NAME
EXPLANATIONS
1986
SQL-86
ANSI released the first standards (ANSI X3.135-1986)
1989
SQL-89
integrity constraints were added by Federal Information Processing Standards in Publication FIPS 127-1
1992
SQL-92, SQL-2
FIPS introduced major changes in this version as FIPS 127-2.
1999
SQL:1999, SQL-3
Following features were added to the basic version: recursive queries, triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features. Support for embedding SQL in Java and vice-versa.
2003
SQL:2003
XML features were added, window functions, standardized sequences, and columns with auto-generated values added
2006
SQL:2006
adds the relation between the XML and SQL. Stores XML data in an SQL database. Edit the data in the database and publish it on the XML form.
2008
SQL:2008
Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement
2011
SQL:2011
important features introduced in this revision were: Window improvements, CALL improvements, Delete in the merge, Collection Type improvements.

After this brief introduction we can return back to SQL-99, SQL-3 and explore the new features introduced.
Development process of SQL-99 is an international joint work between ANSI and ISO. Many international committees were formed and worked on this version and released it.
Andrew (n.d) introduce these new features and divides them into two broad categories as “relational features” and “object oriented features”
Relational Features
New relational features introduced in SQL-99 can be list as: introducing new data types, increased security/privileges, active database, new predicates.
SQL-99 has introduced four new types of data as LARGE OBJECT (LOB), BOOLEAN, ARRAY ans ROW as composite data types and Distinct types.
Increased security/privileges
SQL-99 introduced the new security features as privileges and roles can be assigned to individuals. This system increased the security and made it easier to manage the database.
New predicates
SQL-99 introduced a new predicate called SIMILAR. Earlier versions of SQL has limited string search capabilities using >, < ,=< ,etc… With SIMILAR command this capability increased. Other new predicate is DISTINCT which is very similar to UNIQUE command. It is very helpful to filter the duplicate values in a columns or row.
Active Database
SQL-99 introduced another new feature called trigger. This can be defined as an SQL procedure creates an action every time an event triggered in the database such as INSERT, DELETE or UPDATE. Many triggers can be created in the database to control and monitor the data.
As I have tried to explain briefly here, SQL 99 introduced many new features from new predicates to security from triggers to object oriented features. With my limited but growing knowledge in this area I see the following features are really important Object Oriented, Active directory-triggers, and XML support. Object oriented feature is important because many programming languages are becoming object oriented these days and support to these programs is vital. Active directory-trigger is also important because it gives monitoring options to the database. XML support is another new but important addition because of web compatibility.
References:
1- Coronel, C., Morris, S. & Rob, P. (2011), Database Systems: Design, Implementation, and Management, 9th Ed, Boston, MA: Course Technology, Cengage Learning.
2- Codd, Edgar F (June 1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM (Association for Computing Machinery) (online) Available at: http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf [Accessed at: 20.9.2013].
3- FIPS PUB 127-2, Announcing the Standard for Database Language SQL (Online) Available at:
http://www.itl.nist.gov/fipspubs/fip127-2.htm [Accessed at: 20.9.2013]
4- SQL:1999, formerly known as SQL3”, Andrew Eisenberg and Jim Melton, SIGMOD Record, Vol. 28, No. 1, March 1999 (online) Available at http://users.dcc.uchile.cl/~cgutierr/cursos/BD/standards.pdf [Accessed at: 20.9.2013]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: