Null Values in a Relational Database

In a relational database, null values serve a specific purpose in denoting the absence of information or the inapplicability of a data element. The concept of null values, introduced by Dr. E.F. Codd in his seminal work on the relational model, plays a crucial role in managing data integrity, flexibility, and semantics within the database. The following points explain the purpose of null values in a relational database:

1. Representation of missing information: Null values allow for the representation of missing or unknown information in a database. When a specific attribute or data point is not available or has not been provided for a given tuple, a null value can be assigned to indicate that the information is currently unavailable. This facilitates the storage of partial information without resorting to artificial default values, which could lead to incorrect assumptions or conclusions.
2. Distinction from empty or zero values: Null values distinguish the absence of information from other valid data elements, such as empty strings, zeros, or other default values. By using null values, a relational database can differentiate between situations where the actual value is known to be zero or empty, and situations where the value is simply unknown or missing.
3. Support for optional attributes: In database design, some attributes may not be applicable to every tuple or may be optional. Null values enable the representation of such optional attributes, allowing for more flexible and adaptable schema design. This can be particularly useful in situations where data requirements evolve over time or where the data model needs to accommodate a wide range of scenarios.
4. Preservation of data integrity: By explicitly representing the absence of information through null values, relational databases can maintain data integrity when performing operations such as joins, selections, or projections. Queries and operations that involve null values can be designed to account for their specific semantics, ensuring that results accurately reflect the underlying data and its limitations.
5. Semantics in aggregate functions: In relational databases, aggregate functions such as COUNT, SUM, AVG, MIN, and MAX are used to summarize or calculate data across multiple tuples. The treatment of null values in these functions is defined such that they are generally ignored or excluded from the calculations, ensuring that the results are not skewed or distorted by the absence of information.

In conclusion, null values in a relational database serve the vital purpose of representing the absence of information or the inapplicability of a data element. Their proper use and handling ensure that the database can accurately model complex and evolving data requirements while maintaining data integrity and flexibility.

Every column in a table should contain a value, though there may be times when the value is unknown. For example, consider the following table, which stores data relating to Stories on CD suppliers.

To communicate with suppliers you will need their name, address, phone number, and fax. If you do not know one or more of those pieces of data, you will not know what to enter into its corresponding column.
When the value to enter into a field is unknown, it is called a null value. A null is different from a blank or a zero. A blank is the value used when a column contains no value.
For instance, if the supplier you met with does not have a fax number, you would enter a blank value into that column. A zero, by contrast, is an INT or DECIMAL value.
If a Stories on CD supplier gave the company a thousand free CDs to send to the next one thousand customers who placed an order, the RetailPrice column for that CD would contain a zero.

In many cases it appears that a blank and a null can be used interchangeably. However, the distinction is important to the RDBMS. If a value is truly unknown, enter a null into the column. You can represent a blank by typing a single space in the field. How you enter a null into a field will vary from RDBMS to RDBMS, but one common method is to skip the field entirely by pressing the Tab key. The columns that make up database tables fall into one of two general categories: key columns and descriptor columns.
The next lesson discusses the difference between them. Before you move on to the next lesson, click the link below to reinforce your understanding of the distinctions between nulls, zeros, and blanks.
Null Zero Blank Values

Rule 3 states that NULL values (distinct from an empty character string, a string of blank characters, or a numeric zero value) are supported in the RDBMS as a systematic representation of missing information, independent of the data type of the column containing the NULL value. This means that the database engine has to allow NULL values for any data type, as distinct and different from zeros, spaces, and N/A. This emphasizes the importance of the database supporting defined nullability (the ability to not have any value at all) and optionality (the ability for optional relationships to other data sets).