Welcome to Edukum.com

Theoretical Concepts

The relational model for database management is database model based on first-order predicate logic, first formulated & proposed in 1969 by Edgar F. Codd. In relational model of a database, all data are represented in terms of the tuples, grouped into relations. A database organized in terms of the relational model is called relational database.

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.

  1. Entity Integrity
    Entity integrity constraint states “no primary key value can be a null value”, as primary key value is used to identify individual tuples in the relation. Having null value for primary key states that we cannot identify some tuples. This also specifies that any duplicate entries in the primary key column keyword may not exist.
  2. Referential Integrity
    A referential integrity constraint is specified in between two relations & is used to maintain consistency between tuples in two relations. Informally, the referential integrity constraint states “a tuple in the one relation which refers to another relation, must refer to an existing tuple in that relation”. It is a rule which maintains consistency among rows of two relations.
  3. Domain Integrity
    The domain integrity states “every element from relation should respect type & restrictions of its corresponding attribute”. A type can have a variable length that needs to be respected. Restrictions could be a range of values which element can have, default value if none is provided, & if the element can be NULL.
  4. User-Defined Integrity
    A business rule is a statement which defines or constrains some aspect of a business. It is intended to assert business structure /to control/influence behavior of a business. E.g.: Age>=20 && Age<=70.

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:

  • Clauses
  • Expressions
  • Queries
  • semicolon (";") statement terminator
  • Insignificant whitespace

Operator:

Figure

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:

CASE WHEN n > 0
THEN 'positive'
WHEN n < 0
THEN 'negative'
ELSE 'zero'
END

When conditions are tested in order that they appear in the source. If no ELSE expression is specified, it defaults to ELSE NULL.

Queries
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:

  • FROM clause which indicates table(s) from which the data is retrieved.
  • WHERE clause includes comparison predicate, which restricts rows returned by a query.
  • HAVING clause includes the predicate used to filter rows resulting from GROUP BY clause.
  • ORDER BY clause identifies which columns are used to sort resulting data, & in which direction they should be sorted i.e ascending/descending.

Example:
SELECT * FROM Book WHERE price > 100.00 ORDER BY title;

Data Manipulation 
DML is a subset of SQL and used to add, update and delete data.

Transaction Controls 
Transactions, if available, wrap DML operations

Data Definition 
Data Definition Language (DDL) manages table & index structure. Basic items of DDL are CREATE, ALTER, DROP, RENAME and TRUNCATE statements.

Data Control 
Data Control Language (DCL) authorizes users to access & manipulate data. Its two main statements are GRANT and REVOKE.

Query Optimization
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.

Query Optimization

  • In Non-procedural DMLs (eg. SQL), a user specifies what data is required rather than how it is to be retrieved.
  • Relieves the user of knowing what constitutes good execution strategy.
  • Gives DBMS more control over the system performance.
  • Two main techniques for query optimization:
    • Heuristic rules that order the operations in the query.
    • Comparing many strategies based on the relative costs, selecting one which minimizes resource usage.
  • Disk access tends to be the dominant cost in query processing for centralized DBMS.

© 2019 EDUKUM.COM ALL RIGHTS RESERVED


#Things To Remember



1088 Views