Lecture #2. Missing Data

Two types of values ​​are described in database management systems for detecting missing data: empty (or Empty-values) and undefined (or Null-values).

In some (mostly commercial) literature, Null values ​​are sometimes referred to as empty or null values, but this is incorrect. The meaning of the empty and indefinite meanings is fundamentally different, so it is necessary to carefully monitor the context of the use of a particular term.

1. Empty values ​​(Empty-values)

empty value is just one of many possible values ​​for some well-defined data type.

We list the most "natural", immediate empty values (i.e. empty values ​​that we could allocate on our own without having any additional information):

1) 0 (zero) - null value is empty for numeric data types;

2) false (wrong) - is an empty value for a boolean data type;

3) B'' - empty bit string for variable length strings;

4) "" - empty string for character strings of variable length.

In the cases above, you can determine whether a value is null or not by comparing the existing value with the null constant defined for each data type. But database management systems, due to the schemes implemented in them for long-term data storage, can only work with strings of constant length. Because of this, an empty string of bits can be called a string of binary zeros. Or a string consisting of spaces or any other control characters is an empty string of characters.

Here are some examples of constant length empty strings:

1) B'0';

2) B'000';

3) ' '.

How can you tell if a string is empty in these cases?

In database management systems, a logical function is used to test for emptiness, that is, the predicate IsEmpty(<expression>), which literally means "eat empty". This predicate is usually built into the database management system and can be applied to any type of expression. If there is no such predicate in database management systems, then you can write a logical function yourself and include it in the list of objects of the database being designed.

Consider another example where it is not so easy to determine whether we have an empty value. Date type data. Which value in this type should be considered an empty value if the date can vary in the range from 01.01.0100. before 31.12.9999/XNUMX/XNUMX? To do this, a special designation is introduced into the DBMS for empty date constants {...}, if the value of this type is written: {DD. MM. YY} or {YY. MM. DD}. With this value, a comparison occurs when checking the value for emptiness. It is considered to be a well-defined, "full" value of an expression of this type, and the smallest possible one.

When working with databases, null values ​​are often used as default values ​​or are used when expression values ​​are missing.

2. Null values

Word zero used to denote undefined values in databases.

To better understand what values ​​are understood as null, consider a table that is a fragment of a database:

So, undefined value or Null value - this:

1) unknown, but usual, i.e. applicable value. For example, Mr. Khairetdinov, who is number one in our database, undoubtedly has some passport data (like a person born in 1980 and a citizen of the country), but they are not known, therefore, they are not included in the database. Therefore, the Null value will be written to the corresponding column of the table;

2) not applicable value. Mr. Karamazov (No. 2 in our database) simply cannot have any passport data, because at the time of the creation of this database or the entry of data into it, he was a child;

3) the value of any cell of the table, if we cannot say whether it is applicable or not. For example, Mr. Kovalenko, who occupies the third position in our database, does not know the year of birth, so we cannot say with certainty whether he has or does not have passport data. And consequently, the values ​​of two cells in the line dedicated to Mr. Kovalenko will be Null-value (the first - as unknown in general, the second - as a value whose nature is unknown). Like any other data type, Null values ​​also have certain properties. We list the most significant of them:

1) over time, the understanding of the Null value may change. For example, for Mr. Karamazov (No. 2 in our database) in 2014, i.e., upon reaching the age of majority, the Null-value will change to some specific, well-defined value;

2) A null value can be assigned to a variable or constant of any type (numeric, string, boolean, date, time, etc.);

3) the result of any operations on expressions with Null-values ​​as operands is a Null-value;

4) an exception to the previous rule are the operations of conjunction and disjunction under the conditions of the laws of absorption (for more details on the laws of absorption, see paragraph 4 of lecture No. 2).

3. Null values ​​and the general rule for evaluating expressions

Let's talk more about actions on expressions containing Null values.

The general rule for dealing with Null values ​​(that the result of operations on Null values ​​is a Null value) applies to the following operations:

1) to arithmetic;

2) to bitwise negation, conjunction and disjunction operations (except for absorption laws);

3) to operations with strings (for example, concatenation - concatenation of strings);

4) to comparison operations (<, ≤, ≠, ≥, >).

Let's give examples. As a result of applying the following operations, Null values ​​will be obtained:

3 + Null, 1/ Null, (Ivanov' + '' + Null) ≔ Null

Here, instead of the usual equality, we use substitution operation "≔" due to the special nature of working with Null values. In the following, this character will also be used in similar situations, which means that the expression to the right of the wildcard character can replace any expression from the list to the left of the wildcard character.

The nature of Null values ​​often results in some expressions producing a Null value instead of the expected null, for example:

(x - x), y * (x - x), x * 0 ≔ Null when x = Null.

The thing is that when substituting, for example, the value x = Null into the expression (x - x), we get the expression (Null - Null), and the general rule for calculating the value of the expression containing Null values ​​comes into force, and information about the fact that here the Null value corresponds to the same variable is lost.

We can conclude that when calculating any operations other than logical ones, Null values ​​are interpreted as inapplicable, and so the result is also a Null value.

The use of Null values ​​in comparison operations leads to no less unexpected results. For example, the following expressions also produce Null values ​​instead of the expected Boolean True or False values:

(Null < Null); (Null ≤ null); (Null = Null); (Null ≠ Null);

(Null > Null); (Null ≥ Null) ≔ Null;

Thus, we conclude that it is impossible to say that a Null value is equal or not equal to itself. Each new occurrence of a Null value is treated as independent, and each time the Null values ​​are treated as different unknown values. In this, Null values ​​are fundamentally different from all other data types, because we know that it was safe to say about all the values ​​\uXNUMXb\uXNUMXbpassed earlier and their types that they are equal or not equal to each other.

So we see that Null values ​​are not the values ​​of variables in the usual sense of the word. Therefore, it becomes impossible to compare the values ​​of variables or expressions containing Null values, since as a result we will receive not the boolean True or False values, but Null values, as in the following examples:

(x < Null); (x ≤ null); (x=Null); (x ≠ Null); (x > Null);

(x ≥ Null) ≔ Null;

Therefore, by analogy with empty values, to check an expression for Null values, you must use a special predicate:

IsNull(<expression>), which literally means "is Null".

The Boolean function returns True if the expression contains Null or equals Null, and False otherwise, but never returns Null. The IsNull predicate can be applied to variables and expressions of any type. When applied to expressions of the empty type, the predicate will always return False.

For example:

So, indeed, we see that in the first case, when the IsNull predicate was taken from zero, the output turned out to be False. In all cases, including the second and third, when the arguments of the logical function turned out to be equal to the Null value, and in the fourth case, when the argument itself was initially equal to the Null value, the predicate returned True.

4. Null values ​​and logical operations

Typically, only three logical operations are directly supported in database management systems: negation ¬, conjunction &, and disjunction ∨. The operations of succession ⇒ and equivalence ⇔ are expressed in terms of them using substitutions:

(x ⇒ y) ≔ (¬x ∨ y);

(x ⇔ y) ≔ (x ⇒ y) & (y ⇒ x);

Note that these substitutions are fully preserved when using Null values.

Interestingly, using the negation operator "¬" any of the operations conjunction & or disjunction ∨ can be expressed one through the other as follows:

(x & y) ≔¬ (¬x ∨¬y);

(x ∨ y) ≔ ¬(¬x & ¬y);

These substitutions, as well as the previous ones, are not affected by Null-values.

And now we will give the truth tables of the logical operations of negation, conjunction and disjunction, but in addition to the usual True and False values, we also use the Null value as operands. For convenience, we introduce the following notation: instead of True, we will write t, instead of False - f, and instead of Null - n.

1. Denial xx.

It is worth noting the following interesting points regarding the negation operation using Null values:

1) ¬¬x ≔ x - the law of double negation;

2) ¬Null ≔ Null - The Null value is a fixed point.

2. Conjunction x & y.

This operation also has its own properties:

1) x & y ≔ y & x - commutativity;

2) x & x ≔ x - idempotence;

3) False & y ≔ False, here False is an absorbing element;

4) True & y ≔ y, here True is the neutral element.

3. Disjunction x ∨ y.

Features:

1) x ∨ y ≔ y ∨ x - commutativity;

2) x ∨ x ≔ x - idempotency;

3) False ∨ y ≔ y, here False is the neutral element;

4) True ∨ y ≔ True, here True is an absorbing element.

An exception to the general rule is the rules for calculating the logical operations conjunction & and disjunction ∨ under the conditions of action absorption laws:

(False & y) ≔ (x & False) ≔ False;

(True ∨ y) ≔ (x ∨ True) ≔ True;

These additional rules are formulated so that when replacing a Null value with False or True, the result would still not depend on this value.

As previously shown for other types of operations, using Null values ​​in Boolean operations can also result in unexpected values. For example, the logic at first glance is broken in the law of the exclusion of the third (x ∨ ¬x) and the law of reflexivity (x = x), since for x ≔ Null we have:

(x ∨ ¬x), (x = x) ≔ Null.

Laws are not enforced! This is explained in the same way as before: when a Null value is substituted into an expression, the information that this value is reported by the same variable is lost, and the general rule for working with Null values ​​comes into force.

Thus, we conclude: when performing logical operations with Null values ​​as an operand, these values ​​are determined by database management systems as applicable but unknown.

5. Null values ​​and condition checking

So, from the above, we can conclude that in the logic of database management systems there are not two logical values ​​(True and False), but three, because the Null value is also considered as one of the possible logical values. That is why it is often referred to as the unknown value, the Unknown value.

However, despite this, only two-valued logic is implemented in database management systems. Therefore, a condition with a Null value (an undefined condition) must be interpreted by the machine as either True or False.

By default, the DBMS language recognizes a condition with a Null value as False. We illustrate this with the following examples of the implementation of conditional If and While statements in database management systems:

If P then A else B;

This entry means: if P evaluates to True, then action A is performed, and if P evaluates to False or Null, then action B is performed.

Now we apply the negation operation to this operator, we get:

If ¬P then B else A;

In turn, this operator means the following: if ¬P evaluates to True, then action B is performed, and if ¬P evaluates to False or Null, then action A will be performed.

And again, as we can see, when a Null value appears, we encounter unexpected results. The point is that the two If statements in this example are not equivalent! Although one of them is obtained from the other by negating the condition and rearranging the branches, that is, by a standard operation. Such operators are generally equivalent! But in our example, we see that the null value of the condition P in the first case corresponds to the command B, and in the second - to A.

Now consider the action of the while conditional statement:

While P do A; B;

How does this operator work? As long as P is True, action A will be executed, and as soon as P is False or Null, action B will be executed.

But Null values ​​are not always interpreted as False. For example, in integrity constraints, undefined conditions are recognized as True (integrity constraints are conditions that are imposed on the input data and ensure their correctness). This is because in such constraints only deliberately false data should be rejected.

And again, in database management systems, there is a special substitution function IfNull(integrity constraints, True), with which Null values ​​and undefined conditions can be represented explicitly.

Let's rewrite the conditional If and While statements using this function:

1) If IfNull ( P, False) then A else B;

2) While IfNull( P, False) do A; B;

So, the substitution function IfNull(expression 1, expression 2) returns the value of the first expression if it does not contain a Null value, and the value of the second expression otherwise.

It should be noted that no restrictions are imposed on the type of the expression returned by the IfNull function. Therefore, using this function, you can explicitly override any rules for working with Null values.

<< Back: Introduction (Database management systems. Relational databases)

>> Forward: Relational Data Objects (Requirements for the tabular form of representing relationships. Domains and attributes. Relationship schemas. Named values ​​of tuples. Tuples. Types of tuples. Relations. Types of relations)

We recommend interesting articles Section Lecture notes, cheat sheets:

▪ Microbiology. Crib

▪ EU law. Crib

▪ Pharmacology. Crib

See other articles Section Lecture notes, cheat sheets.

Read and write useful comments on this article.

<< Back

Latest news of science and technology, new electronics:

The existence of an entropy rule for quantum entanglement has been proven 09.05.2024

Quantum mechanics continues to amaze us with its mysterious phenomena and unexpected discoveries. Recently, Bartosz Regula from the RIKEN Center for Quantum Computing and Ludovico Lamy from the University of Amsterdam presented a new discovery that concerns quantum entanglement and its relation to entropy. Quantum entanglement plays an important role in modern quantum information science and technology. However, the complexity of its structure makes understanding and managing it challenging. Regulus and Lamy's discovery shows that quantum entanglement follows an entropy rule similar to that for classical systems. This discovery opens new perspectives in the field of quantum information science and technology, deepening our understanding of quantum entanglement and its connection to thermodynamics. The results of the study indicate the possibility of reversibility of entanglement transformations, which could greatly simplify their use in various quantum technologies. Opening a new rule ... >>

Mini air conditioner Sony Reon Pocket 5 09.05.2024

Summer is a time for relaxation and travel, but often the heat can turn this time into an unbearable torment. Meet a new product from Sony - the Reon Pocket 5 mini-air conditioner, which promises to make summer more comfortable for its users. Sony has introduced a unique device - the Reon Pocket 5 mini-conditioner, which provides body cooling on hot days. With it, users can enjoy coolness anytime, anywhere by simply wearing it around their neck. This mini air conditioner is equipped with automatic adjustment of operating modes, as well as temperature and humidity sensors. Thanks to innovative technologies, Reon Pocket 5 adjusts its operation depending on the user's activity and environmental conditions. Users can easily adjust the temperature using a dedicated mobile app connected via Bluetooth. Additionally, specially designed T-shirts and shorts are available for convenience, to which a mini air conditioner can be attached. The device can oh ... >>

Energy from space for Starship 08.05.2024

Producing solar energy in space is becoming more feasible with the advent of new technologies and the development of space programs. The head of the startup Virtus Solis shared his vision of using SpaceX's Starship to create orbital power plants capable of powering the Earth. Startup Virtus Solis has unveiled an ambitious project to create orbital power plants using SpaceX's Starship. This idea could significantly change the field of solar energy production, making it more accessible and cheaper. The core of the startup's plan is to reduce the cost of launching satellites into space using Starship. This technological breakthrough is expected to make solar energy production in space more competitive with traditional energy sources. Virtual Solis plans to build large photovoltaic panels in orbit, using Starship to deliver the necessary equipment. However, one of the key challenges ... >>

Random news from the Archive AMD Radeon R9 290 09.11.2013 Following the October announcement of the flagship Radeon R9 290X accelerator and an additional week of delay, AMD has finally officially unveiled its second graphics accelerator, which is based on the same new 28nm Hawaii chip with 6,2 billion transistors. However, in order to reduce the cost of the solution for these video cards, chips were selected that operate at a lower frequency and have disabled computing units. As a result, the video card received 2560 stream processors instead of 2816 at a core frequency of up to 947 MHz (instead of 1000 MHz) and a peak computing performance of 4,9 teraflops. AMD is also positioning this accelerator on screens with 4K resolution (Ultra HD) - the number of raster operations units (ROP) has remained unchanged in relation to the flagship - 64 (the Radeon HD 7970 has only 32 of them). However, the number of texture units (TMU) has been slightly reduced - from 176 to 160. Like the Radeon R9 290X, AMD recommends testing the new accelerator in Ultra HD resolution - according to it, in 4K resolution you can find out what the solution is really capable of. Other characteristics of the R9 290 remain unchanged compared to the R9 290X: 512-bit interface provides a throughput of up to 320 GB / s, the amount of GDDR5 memory is 4 GB at 1250 MHz. AMD Radeon R9 290, like the flagship accelerator, scales well in CrossFire mode. Unlike past accelerators of the company, in the R9 290 series the accelerators interact without a special Crossfire bridge - all communication takes place through a PCIe slot, and this does not affect the quality of work and performance, but it allows you to place video cards more freely in the case. The Radeon R9 290 (along with the R9 290X and R7 260X) supports the new TrueAudio programmable audio technology, which is designed to take the sound environment in games to a whole new level. AMD compares TrueAudio in audio to programmable shaders in graphics. TrueAudio will be supported in many game audio engines. Early titles to support the technology include Eidos' Thief, Xaviant's Lichdom, Cloud Imperium Games' Star Citizen, and Square Enix/AirTight's Murdered Soul Suspect. The expected price of the Radeon R9 290 accelerator in the US market is expected to be around $450. In our area, the popularity of the solution will depend on how big the difference in retail price will be compared to a full-fledged Radeon R9 290X video card.

Other interesting news:

▪ Electronics will assess the tone of the computer user

▪ Creative people enjoy wasting time.

▪ New Model of the Expansion of the Universe

▪ coffee protects the heart

▪ Volkswagen cars with information projected onto the road

News feed of science and technology, new electronics

Interesting materials of the Free Technical Library:

▪ section of the site Aphorisms of famous people. Article selection

▪ article Artful Albion. Popular expression

▪ article Why is a mirror considered dangerous? Detailed answer

▪ Turner article. Standard instruction on labor protection

▪ article PSU fan speed controller. Encyclopedia of radio electronics and electrical engineering

▪ article Load power regulator controlled by voltage. Encyclopedia of radio electronics and electrical engineering

Leave your comment on this article:

All languages ​​of this page

Arabic	Hebrew	Polish
Bengali	Hindi	Portuguese
Chinese	Italian	Spanish
English	Japanese	Turkish
French	Korean	Ukrainian
German	Malay	Vietnamese

Home page | Library | Articles | Website map | Site Reviews

www.diagram.com.ua
2000-2024