The purpose of a relational model is to provide a declarative method for specifying data & queries: users directly state what information database contains & what information they want from it, & let DBMS software take care of describing data structures for storing data & retrieval procedures for answering queries. Most relational databases use SQL data definition & query language. These systems implement can be regarded as an engineering approximation to a relational model. A table in SQL database schema corresponds to predicate variable; contents of the table in relation; key constraints, other constraints, & SQL queries corresponding to the predicates. However, SQL databases, including DB2, deviate from relational model in many details.
A relational database management system (RDBMS) is the database management system which is based on relational model. Many popular databases which are currently in use are based on a relational database model. RDBMSs have become a predominant choice for storage of information in new databases used for the manufacturing, financial records & logistical information, personnel data, & much more. Relational databases have often replaced hierarchical databases & network databases as they are easier to understand & use. However, relational databases have been challenged by ODBMS, which were introduced in an attempt to address object-relational impedance mismatch in the relational database and XML databases.
Relational model conformity and Integrity
Integrity constraints are used mostly to ensure accuracy & consistency of the data in relational database. Data integrity is handled in the relational database through a concept of the referential integrity. There are many types of the integrity constraints which play a vital role in referential integrity.
Advanced SQL programming
Structured Query Language (SQL) is special-purpose programming language designed for managing data held in the relational database management system.
SQL consists of data definition language (DDL) and a data manipulation language (DML). The scope of the SQL consists of data query, insert, update & delete, schema creation & modification, & data access control. SQL is a declarative language (4GL), also includes procedural elements.
SQL became standard of American National Standards Institute (ANSI) in 1986, & of International Organization for Standards (ISO) in 1987.
SQL language is subdivided into several language elements:
Conditional (CASE) expressions
SQL has a case/when/then/else/end expression, that was introduced in SQL-92. In its most general form, that is called "searched case" in SQL standard, it works as “else if” in other programming languages:
When conditions are tested in order that they appear in the source. If no ELSE expression is specified, it defaults to ELSE NULL.
The common operation in SQL is a query, which is performed with a declarative SELECT statement. SELECT retrieves the data from one or more tables or expressions. Standard SELECT statements have no persistent effects on a database.
Queries allow a user to describe the desired data, leaving a database management system responsible for planning, optimizing, & performing physical operations necessary to produce that result as it chooses.
SELECT is a most complex statement in SQL, with optional keywords & clauses that include:
SELECT * FROM Book WHERE price > 100.00 ORDER BY title;
DML is a subset of SQL and used to add, update and delete data.
Transactions, if available, wrap DML operations
Data Definition Language (DDL) manages table & index structure. Basic items of DDL are CREATE, ALTER, DROP, RENAME and TRUNCATE statements.
Data Control Language (DCL) authorizes users to access & manipulate data. Its two main statements are GRANT and REVOKE.
Query optimization is the function of many RDBMS. The query optimizer tries to determine a most efficient way to execute given query by considering possible query plans.
Generally, the query optimizer cannot be accessed directly by the users, once queries are submitted to the database server, & parsed by the parser, they are then passed to query optimizer where optimization occurs. However, some database engines allow guiding query optimizer with hints.
The main purpose of query optimization, that is an automated process, is to find a way to process a given query in minimum time amount. Query optimization typically tries to approximate optimum by comparing several common-sense alternatives to provide in the reasonable time.