6 Data Manipulation Transparencies

Dr R. Baskaran

A database by itself, is not much use. The data is stored electronically on the computer’s disk in a format which we humans cannot read or understand directly. What we need is some way of accessing this data and converting it into a form which we do understand. This is the job of the database management system or DBMS for short. A DBMS is essentially a suite of programs that act as the interface between the human operator and the data held in the database.

 

Using the DBMS, it is possible to retrieve useful information, update or delete obsolete information and add new information to the database. As well as data entry and retrieval, the DBMS plays an important role in maintaining the overall integrity of the data in the database. The simplest example of is ensuring that the values entered into the database conform to the data types that are specified. For example, in the telephone book database, the DBMS might have to ensure that each phone number entered conforms to a set format of XXX-XXXXXXX where X represents an integer.

 

The Structured Query Language is a relational database language. By itself, SQL does not make a DBMS. It is just a medium which is used to as a means of communicating to the DBMS what you want it to do. SQL commands consist of english like statements which are used to query, insert, update and delete data. What we mean by `english like’, is that SQL commands resemble english language sentences in their construction and use. This does not mean that you can type in something like “Pull up the figures for last quarter’s sales” and expect SQL to understand your request. What it does mean is that SQL is a lot easier to learn and understand than most of the other computer languages.

 

SQL is sometimes referred to as a non-procedural database language. What this means is that when you issue an SQL command to retrieve data from a database, you do not have to explicitly tell SQL where to look for the data. It is enough just to tell SQL what data you want to be retrieved. The DBMS will take care of locating the information in the database. This is very useful because it means that users do not need to have any knowledge of where the data is and how to get at it. Procedural languages such as COBOL or Pascal and even older databases based on the network and hierarchical data models require that users specify what data to retrieve and also how to get at it.

 

Most large corporate databases are held on several different computers in different parts of the building or even at different geographic locations. In such situations, the non-procedural nature of SQL makes flexible, ad hoc querying and data retrieval possible. Users can construct and execute an SQL query, look at the data retrieved, and change the query if needed all in a spontaneous manner. To perform similar queries using a procedural language such as COBOL would mean that you would have to create, compile and run one computer programs for each query. Commercial database management systems allow SQL to be used in two distinct ways. First, SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately, and any result rows that are retrieved are displayed. This method of SQL processing is called interactive SQL. The second method is called programmatic SQL. Here, SQL statements are embedded in a host language such as COBOL or C. SQL needs a host language because SQL is not really a complete computer programming language as such. It has no statements or constructs that allow a program to branch or loop. The host language provides the necessary looping and branching structures and the interface with the user, while SQL provides the statements to communicate with the DBMS.

 

The origins of the SQL language date back to a research project conducted by IBM at their research laboratories in San Jose, California in the early 1970s. The aim of the project was to develop an experimental RDBMS which would eventually lead to a marketable product. At that time, there was a lot of interest in the relational model for databases at the academic level, in conferences and seminars. IBM, which already had a large share of the commercial database market with hierarchical and network model DBMSs, realised quite quickly that the relational model would figure prominently in future database products.

 

The project at IBM’s San Jose labs was started in 1974 and was named System R. A language called Sequel (for Structured English QUEry Language) was chosen as the relational database language for System R. In the project, Sequel was abbreviated to SQL. This is the reason why SQL is still generally pronounced as see-quel. In the first phase of the System R project, researchers concentrated on developing a basic version of the RDBMS. The main aim at this stage was to verify that the theories of the relational model could be translated in to a working, commercially viable product. This first phase was successfully completed by the end of 1975, and resulted in a rudimentary, single-user DBMS based on the relational model.

 

The subsequent phases of System R concent rated on further developing the DBMS from the first phase. Additional features were added, multi-user capability was implemented, and by 1978, a completed RDBMS was ready for user evaluation. The System R project was finally completed in 1979. During this time, the SQL language was modified and added to as the needs of the System R DBMS dictated.

 

The theoretical work of the System R project resulted in the development and release in 1981 of IBM’s first commercial relational database management system. The product was called SQL/DS and ran under the DOS/VSE operating system environment. Two years later, IBM announced a version of SQL/DS for the VM/CMS operating system. In 1983, IBM released a second SQL based RDB MS called DB2, which ran under the MVS operating system. DB2 quickly gained widespread popularity and even today, versions of DB2 form the basis of many database systems found in large corporate data-centres.

 

During the development of System R and SQL/DS, other companies were also at work creating their own relational database management systems. Some of them, Oracle being a prime example, even implemented SQL as the relational database language for their DBMSs concurrently with IBM.

 

Today, the SQL language has gained ANSI (American National Standards Institute) and ISO (International Standards Organization) certification. A version of SQL is available for almost any hardware platform from CRAY supercomputers to IBM PC microcomputers. In recent years, there has been a marked trend for software manufacturers to move away from proprietary database languages and settle on the SQL standard. The microcomputer platform especially has seen a proliferation of previously proprietary packages that have implemented SQL functionality. Even spreadsheet and word processing packages have added options which allow data to be sent to and retrieved from SQL based databases via a Local Area or a Wide Area network connection.

 

 SQL Standards

 

Oracle strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.

 

The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:

  • ANSI X3.135-1999, “Database Language SQL”, Parts 1 (“Framework”), 2 (“Foundation”), and 5 (“Bindings”)
  • ISO/IEC 9075:1999, “Database Language SQL”, Parts 1 (“Framework”), 2 (“Foundation”), and 5 (“Bindings”)

 

How SQL Works

 

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

  • It processes sets of data as groups rather than as individual units.
  • It provides automatic navigation to the data.
  • It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as “persistent stored modules” (PSM), and the PL/SQL extension to Oracle SQL is similar to PSM.

SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

 

SQL provides statements for a variety of tasks, including:

  • Querying data
  • Inserting, updating, and deleting rows in a table
  • Creating, replacing, altering, and dropping objects
  • Controlling access to the database and its objects
  • Guaranteeing database consistency and integrity

SQL unifies all of the preceding tasks in one consistent language.

 

Common Language for All Relational Databases

 

All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.

 

Recent Enhancements

 

The Oracle Database SQL engine is the underpinning of all Oracle Database applications. Oracle SQL continually evolves to meet the growing demands of database applications and to support emerging computing architectures, APIs, and network protocols.

 

In addition to traditional structured data, SQL is capable of storing, retrieving, and processing more complex data:

  • Object types, collection types, and REF types provide support for complex structured data. A number of standard-compliant multiset operators are now supported for the nested table collection type.
  • Large objects (LOBs) provide support for both character and binary unstructured data. A single LOB reach a size of 8 to 128 terabytes, depending on database block size.
  • The XMLType datatype provides support for semistructured XML data.

Native support of standards-based capabilities includes the following features:

  • Native regular expression support lets you perform pattern searches on and manipulate loosely formatted, free-form text within the database.
  • Native floating-point datatypes based on the IEEE754 standard improve the floating-point processing common in XML and Java standards and reduce the storage space required for  numeric data.
  • Built-in SQL aggregate and analytic functions facilitate access to and manipulation of data in data warehouses and data marts.

Ongoing enhancements in Oracle SQL will continue to provide comprehensive support for the development of versatile, scalable, high-performance database applications.

 

Nonstandard SQL

 

Oracle provides extensions to the standard SQL database language with integrity enhancement. The Federal Information Processing Standard for SQL (FIPS 127-2) requires vendors to supply a method for identifying SQL statements that use such extensions. You can identify or flag Oracle extensions in interactive SQL, the Oracle precompilers, or SQL*Module by using the FIPS flagger.

 

If you are concerned with the portability of your applications to other implementations of SQL, then use the FIPS flagger.

 

Recursive SQL

 

When you issue a data definition language (DDL) statement, Oracle Database implicitly issues recursive SQL statements that modify data dictionary information. Users need not be concerned with the recursive SQL internally performed by Oracle Database.

 

Lexical Conventions

 

The following lexical conventions for issuing SQL statements apply specifically to the Oracle Database implementation of SQL, but are generally acceptable in other SQL implementations.

 

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle Database evaluates the following two statements in the same manner:

 

SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)

FROM employees;

SELECT last_name,

salary * 12,

MONTHS_BETWEEN( hire_date, SYSDATE )

FROM employees;

 

Tools Support

 

Oracle provides a number of utilities to facilitate your SQL development process:

  • SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database server or client installation. It has a command-line user interface and a web-based user interface  called iSQL*Plus.
  • Oracle JDeveloper is a multiple-platform integrated development environment supporting the complete lifecycle of development for Java, Web services, and SQL. It provides a graphical interface for executing and tuning SQL statements and a visual schema diagrammer (database modeler). It also supports editing, compiling, and debugging PL/SQL applications.
  • Oracle HTML DB is a hosted environment for developing and deploying database-related Web applications. SQL Workshop is a component of Oracle HTML DB that lets you view and manage database objects from a Web browser. SQL Workshop offers quick access to a SQL command processor and a SQL script repository.

    What Is SQL?

 

SQL, Structured Query Language, is the standard language used to communicate with a relational database. The prototype was originally developed by IBM using Dr. E.F. Codd’s paper (“A Relational Model of Data for Large Shared Data Banks”) as a model. In 1979, not long after IBM’s prototype, the first SQL product, ORACLE, was released by Relational Software, Incorporated (it was later renamed Oracle Corporation). It is, today, one of the distinguished leaders in relational database technologies. SQL is pronounced either of two ways: as the letters S-Q-L, or as “sequel”; both pronunciations are acceptable. However, most experienced SQL users tend to use the latter pronunciation.

 

If you travel to a foreign country, you may be required to know that country’s language to get around. For example, you may have trouble ordering from a menu via your native tongue if the waiter speaks only his country’s language. Look at a database as a foreign land in which you seek information. SQL is the language you use to express your needs to the database. Just as you would order a meal from a menu in another country, you can request specific information from within a database in the form of a query using SQL.

 

What Is ANSI SQL?

 

The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQL has been deemed the standard language in relational database communication, originally approved in 1986 based on IBM’s implementation. In 1987, the ANSI SQL standard was accepted as the international standard by the International Standards Organization (ISO). The standard was revised again in 1992 and was called SQL-92. The newest standard is now called SQL-99; it’s also referred to as SQL3.

 

The New Standard: SQL-99

 

SQL-99 has five interrelated documents and other documents may be added in the near future. The five interrelated parts are as follows:

  • Part 1—SQL/Framework— Specifies the general requirements for conformance and defines the  fundamental concepts of SQL.
  • Part 2—SQL/Foundation— Defines the syntax and operations of SQL.
  • Part 3—SQL/Call-Level Interface— Defines the interface for application programming to SQL.
  • Part 4—SQL/Persistent Stored Modules— Defines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines.
  • Part 5—SQL/Host Language Bindings— Defines how to embed SQL statements in application programs that are written in a standard programming language.

With any standard comes numerous, obvious advantages, as well as some disadvantages. Foremost, a standard steers vendors in the appropriate industry direction for development. In the case of SQL, a standard provides a basic skeleton of necessary fundamentals, which as an end result, allows consistency between various implementations and better serves increased portability (not only for database programs, but databases in general and individuals who manage databases).

 

Some may argue that a standard is not so good, limiting the flexibility and possible capabilities of a particular implementation. However, most vendors who comply with the standard have added product-specific enhancements to standard SQL to fill in these gaps.

 

A standard is good, considering the advantages and disadvantages. The expected standard demands features that should be available in any complete SQL implementation and outlines basic concepts that not only force consistency between all competitive SQL implementations, but also increase the value of a SQL programmer.

 

What is a Database?

 

In very simple terms, a database is a collection of data. Some like to think of a database as an organized mechanism that has the capability of storing information, through which a user can retrieve stored information in an effective and efficient manner.

 

People use databases every day without realizing it. A phone book is a database. The data contained consists of individuals’ names, addresses, and telephone numbers. The listings are alphabetized or indexed, which allows the user to reference a particular local resident with ease. Ultimately, this data is stored in a database somewhere on a computer. After all, each page of a phone book is not manually typed each year a new edition is released.

 

The database has to be maintained. As people move to different cities or states, entries may have to be added or removed from the phone book.

 

A relational database is a database divided into logical units called tables, where tables are related to one another within the database. A relational database allows data to be broken down into logical, smaller, manageable units, allowing for easier maintenance and providing more optimal database performance according to the level of organization.