Материал: Крючков Фундаменталс оф Нуцлеар Материалс Пхысицал Протецтион 2011

previous one. The purpose of normalization is primarily to speed up access to data and give data more integrity. These objectives may be mutually contradictory. A well normalized database is the necessary compromise between these trends. The first three normal forms are as follows.

First Normal Form (1NF) is expected to satisfy to the only requirement of atomicity: each data field should contain a single data element and no element should be duplicated.

Second Normal Form (2NF) should additionally satisfy to two more rules:

∙each table contains data of only one object;

∙each table contains a unique field or set of fields (termed the primary key) for unique identification of the entry.

Finally goes Third Normal Form (3NF) which requires all table fields, other than key-defined, to be mutually independent.

Occasionally, a designer may intentionally break the 3NF rules to improve performance of an application. This process is referred to as denormalization and is expected to conform to particular rules:

∙Validity of denormalization. The only purpose for which denormalization is practicable is to improve performance of an application.

∙Debugging and testing of the program code created to avoid problems from potential data corruption. For example, introducing a computed field requires a code to be written to accomplish this.

∙Complete documentation of the actions performed.

A relational database differentiates the following types of relations among tables:

∙One-to-one relation. In this relation each entry in the table relates to one entry in another table. Normally, if such relation exists, is makes sense to consolidate the tables. However, cases occur when this relation is realized.

∙One-to-many relation is used to relate an entry in one table to more than one entry in another table. This is the most commonly encountered relation type.

∙Many-to-many relation. Each entry in one table is related to more than one entry in another table and vice versa. Relational database rules require this relation to be presented in a dedicated table. As foreign keys, the additional table contains the primary keys of both tables. It has a one-to- many relation to the base tables, which realizes the many-to-many relation of the original table.

321

Integrity of data is a separate aspect to be ensured in the context of information security. Data integrity shall be understood as a correct and noncontradictory status of a database. This means that information in separate tables should make a whole and should not have contradictions. Relational database management systems help maintaining the integrity of data automatically without the need for a program code to be written to check the input. There are a number of procedures to keep data integral.

Thus, sharing an entity among tables and linking tables via primary and secondary keys helps avoid duplication of identical information and errors this may entail. DBMSs allow data types to be specified in particular table fields to hold down an attempted input of knowingly incorrect data with field values to be limited where possible.

Transaction support

Any change to the database in computerized NM A&C systems shall be made through transactions. Transaction is a set of database transformations which results in the database changing over from one noncontradictory state to another noncontradictory state. Transactions should meet four requirements known collectively as “ACID”: Atomicit y, Consistency, Isolation and Durability.

∙Atomicity means that a transaction can be performed only in full and not in part.

∙Consistency is the condition when no traces are left by a transaction. Aborted transactions should reset the system. This operation is called rollback.

∙Isolation is the notion meaning that transactions should not interact. If transactions try to handle the same data in the database, this data should be blocked for all other transactions before completion.

∙Durability of a transaction means that if the transaction has been completed and its goal achieved, it becomes completed even if something happens with the system.

DBMSs with transaction support should contain additional tables. Tables of independent values describe one object but at different time. These tables are used before the transaction is completed with data stored that may be required if there is a rollback.

Special transaction logs are needed to log all transactions in the database. Where in place, these logs, along with backup data copies, allow the transaction to be considered a data recovery unit. When data is

322

recovered, the DBMS reviews the transaction log data and consistently performs all transactions performed since the time of the latest data backup.

Structured query language (SQL)

Structured query language (SQL) is a high-level language for manipulation of data and objects in relational databases. Such language is the necessary condition for a database to be considered relational. Relational model was developed by IBM in the 1970s. In parallel, the original version of a structured query language appeared. The latest SQL standard was adopted in 2003.

DBMS manufacturers tend to extend the language standard by giving it extra capabilities. Normally, if successful, these changes are made part of the next language standard. Still, practically all SQL versions support base requests and functions of the standard. Following the standard helps create applications other than depending on a particular DBMS with standard requests employed to manipulate data. Dedicated mechanisms (ODBC or OLE DB) are exploited to communicate with databases, thus enabling conversion of requests to respective DBMS formats.

Use of SQL makes it unnecessary for programmers to write a great deal of routine sampling and data merger operations. This will also cut the load on the client computer as the operations done using SQL queries in the client/server architecture will be accomplished on the server.

Given below are the formats of the basic language queries enabling manipulation of data. Uses of SQL structures will be illustrated by examples.

The basic SQL command is SELECT which is a data sampling command. It has a rather complex syntax and makes it possible to compile queries for a great deal of operations to furnish the user with information from the database. Here are some examples to illustrate the language capabilities:

SELECT title, price FROM titles

WHERE рrice >= 5 AND рrice <=10

In this example, values in the titles and price fields are selected from the titles table for all entries in which the price field values lie in the interval of 5 to 10. The key words SELECT and FROM are necessary in the query because they specify the table sampled and those fields the values whereof

323

are to be supplied to the user. The key word WHERE defines the sampling condition. Where this word is absent, all table entries will be sampled from.

With the predicate in the WHERE sentence assuming the meaning of “truth”/”lie”, Boolean algebra operators can be use d to form it. In a Boolean expression, the only predicate may use any number of conditions which makes it possible to form exclusively powerful predicates. SQL may use the following comparison operators: equal to, greater than, less then, greater then or equal to, less than or equal to, not equal to. The last one looks as <>.

Apart from comparison operators, SQL distinguishes basic Boolean algebra operators which interlink a number of Boolean expressions, a Boolean expression being obtained again as the result. SQL distinguishes the following Boolean operators:

∙AND – logical AND. The operator takes two Boolean e xpressions (А AND B) and gives out “true” if both of them are tru e or “untrue” if otherwise;

∙OR – logical OR. The operator takes two Boolean expres sions (А OR В) and gives out “true” if at least one of these has the meaning “truth”;

∙NOT – logical negation. One expression (NOT А) is used as an argument. This operator inverts the value.

Using predicates with Boolean operators, one can give them a much greater selective power.

As stated in the discussion of the database relational model features, table fields should be independent. However, data is often needed, which can be obtained based on these fields with the aid of certain procedures: summation, averaging, etc. SQL performs these operations using aggregation functions.

∙COUNT – determines the quantity of rows or fields s elected by query and being other than NULL values.

∙SUM – computes the arithmetic sum of all selected v alues in the given field.

∙AVG – computes the average value of all selected va lues in the given field.

∙MAX – computes the greatest of all selected values in the given field.

∙MIN – computes the least of all selected values in this field.

Here is one example to illustrate how the aggregation function can be employed:

SELECT AVG(price) FROM titles

324