This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
This paper will discuss the development of Structured Query Language or commonly pronounced SQL is a database computer language used in a wide range of applications for accessing and manipulate the data stored in relational databases. Manipulation activities in SQL are such as execute queries, retrieve data, insert new records, delete records and update records in a database. SQL is a simple, English-like language that is relatively easy to learn and use by any level of database user. SQL has gone through several major changes throughout its development since it was introduced in 1970. It is now an ANSI (American National Standards Institute) standard. SQL has been widely used by the database vendors and each vendor implemented it differently. Although they have their own proprietary SQL extensions to the standard ones, their implementation is still complying with the basic ANSI standards in major commands such as SELECT, UPDATE, DELETE, INSERT, WHERE. Implementation of SQL in programming world cannot be separated. Many programmers will use SQL to retrieve data but without proper planning in the programming coding, SQL statements can be seen as a security threat. Nevertheless, if it is written correctly the security threat is not an issue at all.
Keywords: Structured Query Language, SQL, American National Standards Institute, Relational Database Management System, Database Management System.
Introduction to SQL
Standard Query Language or commonly called as SQL (pronounce "ess que ell") or some called it sequel. It is a structured query language being used widely in database world. Relational database management system and non-relational database management system support SQL as the method for accessing data without much effort from the developers or programmers. SQL standard has been successfully implemented by various database vendors like ORACLE, IBM and MICROSOFT.
History of SQL
History of SQL start of from a paper presentation by Dr. E. F. Codd, a researcher for IBM in the year 1970. This paper was published in Association of Computer Machinery (ACM) journal, Communications of the ACM. This became the foundation for the relational database system and the model is now accepted as the definitive model for relational database management systems (RDBMS). This article has generated a great deal of interest in both the feasibility and practical commercial application of database system. While Dr. E. F. Codd presented his theory and idea into research paper, his colleagues, Donald D. Chamberlin and Raymond F.Boyse had been developing a query language known as SQUARE (Specifying Queries as Relational Expressions). They have used set theory and predicate mathematics to select data from the database. Although this language had a complex and unfriendly mathematical syntax, but it became the proving ground for concepts which are important to database manipulation. In 1974, Donald D. Chamberlin and Raymond F.Boyse have started an implementation of an IBM prototype named SEQUEL-XRM in the year 1974-75. This prototype is under a project named System/R project. In this project they developed SEQUEL or Structured English Query Language. In 1976-1977, it was rewritten to include features like multi-table and multiuser and was called "SEQUEL/2". Later IBM changed it to SQL due to the copyright issues relating to "SEQUEL" name. It was a trade mark registered by the Hawker Siddeley an aircraft company; hence by dropping the vowel, the name SQL is produced.
In the year 1978, IBM commenced a testing at customer site. IBM demonstrated the usefulness and practicality of the system and it was a success story for IBM. As the result from it, IBM started to developed commercial products that implemented SQL based on the System/R prototype. This includes SQL/D, which was introduced in 1981 and later DB2 in 1983.
There are other vendors that have accepted the rise of this relational model and announced their SQL-based products in market. Other IBM, another well known database vendor, ORACLE, release first commercial RDBMS, Oracle V2, also SYBASE and INGRES (based on the University of California's Berkeley Ingres project) in 1979. Other than these players, Microsoft has its first entry enterprise level database market in 1989 named SQL Server 1.0. This product is a combination 3 big name (Microsoft, Sybase and Ashton-Tate). As of today, the leading products in RDMS;
The world's second-largest software company successfully developed a success of relational data management SQL-based product through flagship database servers. It is also involves its enterprise applications which the SQL-based product.
IBM as one of the largest computer system company. Early founder of SQL usage. IBM starts off with DB2 Product line as common foundation in IBM product line. Later, as commitment to SQL, IBM bought over Informix's SQL DBMS.
Microsoft as the world's largest software company which has been using SQL Server as a critical part of its strategy to conquer and penetrate the computing system market.
Other names are MySql, Postgress, SAP
What Is SQL?
By definition SQL is a standard language used to communicate with a database and some define it as database sublanguage for querying and modifying relational databases. The SQL language has two distinct sets of commands: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is the subset of SQL used to define and modify various data structures. While DML is the subset of SQL to access and manipulate data that is contained within the data structures. This data structures was previously defined via DDL.
Data Definition Language (DDL)
DDL is to perform table and index structure management. DDL has numerous commands for handling such tasks as creating tables, indexes, views, and constraints. The command are CREATE, ALTER, RENAME, DROP and TRUNCATE statements.
CREATE command is to creates a table or a view in the database system. Sample of the command is as follow:
CREATE TABLE employees (
Id INTEGER PRIMARY KEY,
first_name CHAR(50) NULL,
last_name CHAR(75) NOT NULL,
dateofbirth DATE NULL,
ic_number INTEGER NOT NULL
DROP command is to deletes an object in the database, usually irretrievably
Sample of the command is as follow:
DROP TABLE employees;
ALTER is the command to modify the structure of an existing object in the database by various ways. For example command to add a column to an existing table. Sample of the command is as follow:
ALTER TABLE employess ADD contact_num INTEGER;
ALTER TABLE employess DROP COLUMN ic_number;
DDL term was first introduced in relation to the Codasyl (Conference on Data Systems Languages) database model. In this model, the schema of the database was written in a DDL describing the records and fields to make up the user Data Model. Initially it only referred to a subset of SQL, but now it is formally used to refer to describe data or information structure.
Data Manipulation Language (DML)
DML is a language used by database users to access and manipulate data contained within the data structures previously defined via DDL. DML modify stored data only, not the schema or database objects which are under DDL responsibility. DML is comprised of five statements:
SELECT - Retrieves data from a database. Sample of the command is as follow:
WHERE ic_number = 750208045164
INSERT - Add in data into tables in database. Sample of the command is as follow:
INSERT INTO employees
(id, first_name, last_name, dateofbirth, ic_number,contact_num)
(1, 'Ali', 'Ahmad', '8-8-1979',790808015351,87401593);
UPDATE - Update data in tables in the database. Sample of the command is as follow:
SET contact_num = 87401595
WHERE id = 1
DELETE - Remove data from a database. Sample of the command is as follow:
DELETE FROM employees
WHERE id = 1
MERGE - Add and/or modifies data in tables in the database. This statement is the new one that added to be part of ANSI SQL Standard in year 2003.
What Is ANSI SQL?
ANSI SQL is the American National Standards Institute standardized Structured Query Language. ANSI is an organization that approves standards for many industries. As for SQL particularly, it has been the standard language in relational database communication. It was first approved in 1986 based on IBM's implementation.
The development of SQL has gone through quite a number of changes. In 1987, ANSI SQL standard was internationally accepted by the International Standards Organization (ISO). This standard was revised again in 1992 and called SQL-92 which was referred as SQL 3. As to date, the latest version revised is SQL: 2008. Based on entry published in Wikipedia, the free encyclopedia, SQL (Wikipedia, 2001), the developments of SQL standards are shown in the next diagram.
Benefits of SQL Implementation
The main reason why SQL is very successful is because SQL is a high-level language that provides a bigger abstraction than normal procedural languages such as 3rd generation languages (3GLs), such as COBOL and C, and also 4th generation languages (4GLs). The normal procedural languages will require the programmer navigate the data structures and it is difficult when they need to encode the information in high level language. They will have difficulties in changing it after it has been programmed and time consuming. Contrast with SQL, it is designed to allow the programmer to specify what data is needed. It does not specify how to retrieve it due to SQL is coded. Database Management System (DBMS) analyzes the SQL and formulates data-navigational instructions "silently behind the scenes" and these are called access paths. The DBMS will determine the optimal access path to the data and there is no need for the programmer to know how to retrieve it. Plus, the DBMS have a better understanding of the state of the data stored, and therefore can produce a more efficient and dynamic access path to the data. Hence, if SQL is properly used and implemented, it can provide quicker application development and prototyping environment than is available with corresponding high-level languages.
By using SQL, it will be the same language used to query data with the one used to define data structures, control access to the data, and insert, modify, and delete occurrences of the data. By having a common language, communication between development team such as DBAs, system and application programmers, system analyst, designers and end-users will be easier. This enable reducing the development time of the project and surely one of the benefits of adopting it since all team members are speaking the same language, the SQL. Besides the same language used, SQL basic syntax is an English-like language which makes it easier to understand by both developer and end-users.
Another benefit of adopting SQL based is that a single request can be formulated in many different ways. With a single statement or single line of code, SQL is able to operate on sets of data to modify, retrieved or removed.
Risk of SQL Implementation
Every implementation of technology has its own risk. SQL does not make exception. Although when implementing SQL means it is indirectly implementing DBMS/RDMS as well which the risk of SQL implementation is fall back to the database vendors on how it implements. Anyhow, in the industry there is one common problem that almost all database vendors is facing is the "SQL injection" technique. SQL injection is a technique that exploits web sites by modify or change backend SQL statement. SQL injection is very 'popular' among online or internet attacker. They are taking advantage on the vulnerability of web application. This happens when the web application that has online form accepts users input. The input then being posted over to another processing server side page. In this server side page it has a set of coding that has select statement in it. This is where the attacker enters it directly into SQL statement. If there is no proper filtering being done for any suspicious character, it then, allows an attacker to pump in another set of SQL statement in it. The statement can be MODIFY or DELETE statement. Effect of this can be very severe if they have access to Stored and Extended Procedures (database server functions which may possible to compromise the entire machine. They commonly insert single quotes into into a forms input field to test for SQL Injection and also through a URL's query string. Sample of how SQL injection can be seen in the sample below:
A statement that received an input (user_Name) from a web form. "SELECT * FROM userdetails WHERE name = '" + user_Name + "';"
The user input userName = 'cloe'. So, sql statement render it by the parent language as "SELECT * FROM userdetails WHERE name = 'cloe' OR 't'='t';"
Most SQL server implementations allow multiple statements to be executed with a single statement, the multi-select statement. Means the statement can be altered as; "SELECT * FROM userdetails WHERE name = 'cloe';DROP TABLE userdetails; SELECT * FROM user_info WHERE 't' = 't';"
Nevertheless, to avoid this, the developer must properly plan their development technique which can reduce the risk of being injected by unnecessary statement.
Other than security flaws, SQL statement (the basic ones) limits the functions to perform loop or step through multiple rows one at a time on certain tasks. It is impossible to accomplish using solely through SQL statement. Nevertheless, more functionality is being added from time to time but this is depending on the database vendor's implementation to handle this need or requirement.
As mentioned above, there are few database vendors that provide additional and enhanced features. Each vendor's implementation is unique in both features and nature in producing their database server as product. Nevertheless, they will comply with the current ANSI and ISO standard for portability and user convenience but of course as mentioned above they will have their own uniqueness. This is to ensure their user or rather their customer will not easily switch database product. It would be rather discouraging for the database users to learn another language to maintain functionality with the new system.
What Is a Database?
By definition, database is a collection of data that is organized. The contents of the database can easily be accessed, managed, and updated. As mentioned in the earlier topic of this paper, it can be maintained through a sublanguage called SQL using an access path provided by database management system. A database can be classified according to types of content such as bibliographic, full-text, numeric, images and multimedia files or blob files. The diagram below shows the relationship between SQL and the Database Management System.
What is Database Management System?
By definition, Database Management System or commonly called DBMS is a system or software designed to manage a database for creation and maintenance of data. It is also known as integrated collection of logically related records or files consolidated into a common pool that provides data for one or more applications.
What is Relational Database Management System?
By implementing SQL, it is more likely it will implement RDBMS with it. RDBMS is more secure and are able to perform normalization of data (reducing redundancy, repetition, and concurrency). Additional condition that the RDBMS supports a tabular structure for the data, with enforced relationships between the tables.
Future of SQL
There's no doubt that SQL based product's development is still growing. Many of the database vendors are persistently producing more features and functionality of their RDBMS product. If their new features and functions been a common among the vendors, these features or functionality will become a standard in the ISO. At to this point, SQL is still growing and latest functionality added are CASE statements, outer joins and nested tables' expression. These functionalities gave the users increment in the number of tasks when using SQL alone.
Other way forward for the database vendors are seriously looking into is the development of SQL based on fuzzy logic discipline. By simple definition in SQL point of view fuzzy logic is well suited to expressing the intent of a database query when the semantics of the query are rather vague. Fuzzy logic SQL statements helps to use more English-like statement which will enable the users to do more with a single SQL statement. For example of
Lots of vendors have adopted fuzzy logic as one of their uniqueness of their products. Research and development are being done constantly to improve be more like human English language.
Emerging technology of XML has made most of all the vendors adopting it. The ISO/IEC 9075-14:2006 standard defines ways for each database vendor to follow on how SQL can be used in with XML. When there are more and more data are represented as XML documents, the need of persistent storage systems has increased rapidly. The key benefits of XML in the table is the retrieval speed and lesser in-depth of data tree. This has make XML becoming more popular among vendors and all practitioners. Hence, all the big names of database vendors such as Oracle, IBM, Microsoft, MySQL and many more has implemented it and each vendors has their own uniqueness as added value in implementing XML.
Almost all major packaged enterprise applications such as Enterprise Resource Planning (ERP), Human Resource Development Management (HRM), Sales Invoicing Management, Electronic Customer Relationship Management (eCRM) and many more are developed on SQL-based databases. In the information management market today SQL-based relational databases product is one of the most important foundation technologies. More than two decades ago, the first commercial implementation, SQL has grown and becoming the standard database language for all. Looking in past history in the first decade, the research in IBM laboratories, the blessing of standards bodies (ANSI and ISO), and the enthusiastic support of DBMS vendors has made SQL as the only standard for data management. In the second decade of SQL presents, the SQL has extended to personal computer and workgroup environments and to database-driven market segments like the data warehousing. At current of its early third decade, SQL has become un-doubly as the standard database for Internet-based computing and solution which has emerge as a standard for any specialized databases in applications such as data warehousing to mobile laptop databases. Other than that are the embedded applications in industries such as telecommunication as well as data communications networks. Hence, the future of SQL is indeed bright and competition among the vendors is becoming more stiff and challenging. Nevertheless, it's healthy in terms of competition and definitely gave us as the user more option to choose the best.