Page 61 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Impact on SQL Constructs

This unit deals with the impact of NULL values on the SQL constructs. To understand this impact,
consider a simple student table.

Student Relation

Student_id Std_name Course_id Class Group
1 A 101 2 B
2 B 102 3 NULL
3 C 101 2 A
4 D 103 4 NULL
5 E 104 5 A
6 F 102 3 B
7 G 105 6 NULL

A Simple Query

Query: List all the names of students who belongs to group ‘B’.
Solution:
SELECT *

FROM Student S
WHERE S.group = ‘B’
This solution will result in the set of tuples that satisfies the ‘WHERE’ condition and all other
tuples that does not satisfy this condition are ignored in addition to these tuples. Tuples with
NULL values are also ignored because for them the condition evaluates to false or unknown.
This elimination of rows that resulted unknown, makes the queries that involves EXISTS and/
or UNIQUE much more simple, easy to understand and makes the evaluation of these queries
(nested queries specially) much more easier.
We know that the comparison of any two fields with null values for equality is an unknown
value. But when it comes to (=) equality operator, the two null value attributes are treated as
equal. If a field contains two null values then that is considered as duplicate values. Two tuples
are said to be duplicates if they hold the same value or if they hold the null values. So, the
comparison of NULL values with the “=” operator always results in TRUE.
The result of all the arithmetic operators (+, -,%,/,*) results in an unknown value (NULL) if any
one of the argument is a NULL value. Similarly with all the aggregate operators the result is
NULL if these operators are applied to a NULL value. Aggregate functions simply delete the
NULL values and then returns the result of aggregate operator i.e., SUM, AVG, MIN, MAX,
COUNT(DISTINCT) simply delete/ignore the NULL values and returns the result of other NOT
NULL tuples. Only exception in aggregate operator is count(*) which does not ignore/ delete.
The NULL values, it counts them and then return the number of tuples in the table.

Disallowing NULL Values

The fields can take on NULL values if they are not declared as NOT NULL, we can restrict the
insertion of null values for the field by declaring that field as NOT NULL. This means that the
field cannot take null values. For the primary key constraint i.e., the field which is declared as
primary key is also declared as NOT NULL. This declaration is implicit declaration done by
DBMS.

54 LOVELY PROFESSIONAL UNIVERSITY