Relational Database | Part 2

Relational Database | Part 2

Theory of relational databases

The foundations of the theory of relational database were laid by Edgar F. Codd in the 1960s and 1970s and in his work A Relational Model of Data for Large Shared Data Banks [described one]. Theoretically, all operations are based on the relational algebra.

Understanding the relational algebra

The relational algebra is an algebraic model that describes how data can be stored, queried and manipulated. The main operations from which all other are derived, are the following:

  • Projection (English projection)
  • Selection (English selection)
  • Cross product or Cartesian Product (Data Sheet cross product, cross join or cartesian product)
  • Changes (English rename)
  • Association (English union)
  • Difference (English set difference or minus)

All requests that are made using SQL on a relational database, the database management system shown on these operators, that is translated. In practice, there are other operators, such as the join operator, but is also only a combination of cross product, selection and projection.

Restrictions on the relational algebra

The relational algebra does not provide support for calculation of recursive queries (Transitive Closure). This means, for instance, that it is not possible in a query all ancestors of a person to calculate where they are stored in a relation person and connected by a relationship. The ancestors can only be determined through a series of questions.

With the introduction of SQL-99 but also an extended Relational Algebra was introduced, which allows an operation to calculate the transitive closure.

Database schema and Modeling

An important part of relational database is its schema. The schema defines what data is stored in the database and how these data are related to each other. The process for creating a schema is called data modeling.

For modeling of relational databases and the Entity-Relationship model is used. It is used to design a conceptual schema, which is using a database management system (DBMS) can be implemented. This step is called the logical design or data model mapping and, as a result of a database schema in the implementation of the DBMS data model.

An important step in the modeling process is normalization. This will reduce redundancies and prevent anomalies in order to simplify the maintenance of a database, and ensure the consistency of the data. , Edgar F. Codd has proposed along with four normal forms, thereafter in the relational database design used and were supplemented by another.

Criticism of the relational database model:

Segmentation

In the relational representation is the storage of an object is segmented in many different relationships. The application objects are usually complex, there are so themselves back from objects, or lists of objects. Since the relational model only consisting of values, must use complex objects in a query by the DBMS are restored by means of numerous joins from the individual relations.

This can lead to confusing queries that need to be reviewed each structural modification of the application object needs to adjust down. The use of joins, which are supported by each well-matching database indexes must, makes the object access expensive than, for example in an object database, both the resource requirements as well as the development effort.

Artificial key attributes

To uniquely identify tuples in some cases, artificial keys are used. This, for example, helps to reduce the size of the key, if it is to be used as foreign keys, or to one-to-implement relations. There are therefore included in the relation attributes that have nothing to do with the abstract description of an application object nothing but “just” management information.

External API

Since in many relational databases only data manipulation languages are available with reduced thickness, is usually necessary to powerful programming interfaces. This link leads to an unfavorable handling if necessary, for example, if the quantity-SQL to process in the satzorientierten C + +, refer to impedance mismatch.

However, there are also relational database with powerful languages such PL / SQL on Oracle or PL / pgSQL PostgreSQL, the latter turn enables the server programming with other languages such as PHP, Tcl or Python.

Object properties and behaviors are often not mapped

In the relational database application type and the behavior of an object are not described. This description may therefore only take place outside the database in a software application. When multiple applications use the same data can lead to a redundant implementation.

Relational Design Theory

The relational design theory focuses on the basis of formal methods with the conceptual design of the schemas of relational databases. The relational design theory thus provides a theoretical basis for the design of a “good” relation schemas.

Basis for the design of a schema make it functional dependencies – more informal – are a generalization of the key concept, which aims to help make certain stored data clearly identified. Furthermore, the canonical cover of functional dependencies and multi-valued dependencies under investigation, the latter in turn are a generalization of functional dependencies.

Using the functional dependencies are defined normal forms for relational schemas and are, in a “quality criterion is” is assessed on the basis of which the quality of relation schemes, quality is a collective term for properties such as manageability, comprehensibility, clarity, etc. try the relational design theory, this helps to formalize using the normal forms.

A “good” relational schema is characterized for example by the fact that information is not stored implicitly, that information is not redundant and that there are no inconsistencies, induced by modification, deletion and addition. These undesirable properties of relation schemas are often caused by mixing, cutting, or repeated storing the identified information needs analysis in the entities.

A distinction in the relational design theory, two approaches:

  • The normalization of relations will help to improve a given design: are not satisfied for a certain normal form relational schema, we can break this by using the appropriate normalization algorithms to several schemes that meet the desired normal form.
  • The synthesis of relations aims to design an optimal Relational schemas.

To be continued.

Study: From Wikipedia, the free encyclopedia. The text is available under the Creative Commons.

Sharing

Leave your comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.