Slide 8.10: Table key (cont.)

Table Key (Cont.)

Every Table T Has at Least One Key
Given a table T with Head(T)=A₁…A_n, consider the attribute set S₁ with all these attributes:

Either S₁ is a key, or there is another set S₂, a proper subset of S₁, is also a superkey. Continuing in this fashion, we have S₁, S₂, ..., S_i, S_i+1, ...

S₁, S₂, ..., S_i, S_i+1, ... must come to an end with a final set S_k as a key.

Assume #S is the number of attributes in the set S. We have
```
   n = #S₁ > #S₂ > ... > #S_i > #S_i+1 > ... > 0
```
The final set S_k has no proper subset that is a superkey and therefore must be a key for the table.

NULL Values
A null value is no data entry at all. It is not the same thing as a zero or a blank. The null value should be interpreted as unknown or as not yet defined.

SQL> CREATE TABLE product (
  2>   pid      CHAR(5) PRIMARY KEY,
  3>   pname    VARCHAR(32) NOT NULL,
  4>   city     VARCHAR(32),
  5>   quantity INTEGER CHECK( quantity >= 0 ),
  6>   price    REAL NOT NULL CHECK( price >= 0.0 ) );
  ...   
SQL> INSERT INTO product VALUES( 'p07', 'stapler', null, null, 3.50 );
  ...
SQL> SELECT * FROM product WHERE city IS NULL;

⇓

pid	pname	city	quantity	price
p07	stapler	null	null	3.50

For example, when query the average quantity on products, the quantity for the stapler product is left out of the average.