Consider the following classic case study: an employee table that records the salary of each employee working for the enterprise. Salaries can be unknown at a given point, so an employee row can contain a null to hold the place of the salary of an employee. When that row is inserted into the table, the salary value is missing information.
At a given instant, this employee table may look like this:
| emp_num | emp_name | dept_num | hire_date | salary |
|---|---|---|---|---|
| PK | ||||
| 214 | Smith | 32 | 09-12-1989 | 56150 |
| 447 | Lau | 15 | 05-30-1993 | ? |
| 103 | Hossein | 09 | 09-13-1984 | 29775 |
| 500 | Nakamura | 11 | 06-09-1997 | 84932 |
| 713 | Schroeder | 24 | 10-29-2001 | ? |
New hire Schroeder has not yet been assigned a salary and Lau was promoted but her new salary has not been determined.
The following "partial nothing" table lists only the primary key values known for each row.
| emp_num | emp_name | dept_num | hire_date | salary |
|---|---|---|---|---|
| PK | ||||
| 214 | ? | ? | ? | ? |
| 447 | ? | ? | ? | ? |
| 103 | ? | ? | ? | ? |
| 500 | ? | ? | ? | ? |
| 713 | ? | ? | ? | ? |
The problem with the "partial nothing" table is that its semantics are incorrectly perceived. The intended meaning of the nulls in the salary column derives from the true proposition that all employees receive a salary. The formal semantics of the table, however, assert only that a salary amount exists for each employee.
- Those rows with nulls that apply to all employees (“a salary amount exists for all employees”).
- Those rows that describe only those employees whose salaries are known (“all employees receive a salary”).
That is, multiple, inconsistent, informal predicates are being mapped (incorrectly) into one formal predicate. Rows containing nulls as place holders for the salary value apply to all employees, while rows containing values for employee salaries apply to a subset of that population. No single predicate can apply to both situations.