Chapter 3Concise History of SQL

IFS180.81 Intro. to Data Management

History of DBMS

Hierarchical Database Structure Where structure is formed by data groups,

subgroups, and further subgroups Strength – capturing and storing transactional

data Major weakness – difficult for ad-hoc or on-

demand queries

History of DBMS

DEPT # Dept Name Reports To Manager Budget

Emp # Emp Name Dept # Sex Salary Grade

Job# Job Description

Job Date Title Child Name Age Sex

Salary Date Salary

System / Program had to navigate forward / backward thru branches

History of DBMS

Network Database Structure Developed to allow retrieval of specific

records Utilizes a system of ‘Pointers’ (RRN’s) No longer had to follow branches Major weakness – maintenance of Pointers

History of DBMS

1

2

3

4

5

Extremely Fast for Querying Data, Slow for TPS

History of DBMS

Relational DBMS – 1970 Codd & Date Based Upon relational algebra and Set

Theory from 1800’s. Sets of object are considered as a whole Relational algebra (simplest form) are truth

tables. And / Or / Nor Fast ad-hoc queries and TP

History of SQL

English like Query Language was developed to manipulate data in RDBMS (structured query language)

Relational Software (aka Oracle) Cal Berkley (INGRES) IBM (DB/2)

History of SQL

American National Standards Institute (ANSI) SQL Standard:

Year Name Alias Comments

1986 SQL-86 SQL-87 First published by ANSI. Ratified by ISO in 1987.

Lacked Referential Integrity

1989 SQL-89 Minor revision.

1992 SQL-92 SQL2 Major revision.Focusing on Standardization

1999 SQL:1999

SQL3 Added regular expression matching, recursive queries, triggers, non-scalar types and some object-oriented features. (The last two are somewhat controversial and not yet widely supported.)

2003 SQL:2003

  Introduced XML-related features, window functions, standardized sequences and columns with auto-generated values (including identity-columns). (See Eisenberg et al.: SQL:2003 Has Been Published.)

History of SQL

Even though SQL is a standard, all major vendors have their version

Oracle = PL/SQL IBM = SQL PL Microsoft = Transact SQL

History of SQL

Why so many versions of SQL Implementations?

Size / Complexity of ANSI Standard Missing components from STD (Indexing) Ambiguity of standard Backwards compatibility Need to be careful and know what vendor you

are working with