L4 Multi-table queries

Question 1

What null values are used for in the relational model?

Answer 1

• value unknown:
  
  • e.g. perhaps we know that a pub sells a certain beer but we don’t know the price.
• value inapplicable:
  
  • e.g. the value for a spouse attribute of an unmarried person could be NULL.
• value witheld:
  
  • e.g. perhaps we don’t wish to reveal where a person lives.

Question 2

Describe three situations in which the use of null values either complicates the syntax of SQL or makes query processing more involved.

Answer 2

• aggregation: Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values.
  
  • The exception to this is COUNT(*), which counts rows and not individual column values.
• In SQL, the NULL value is never true in comparison to any other value, even NULL.
  
  • To search for column values that are NULL, you cannot use an expr = NULL test because expr = NULL is never true for any expression
• For some data types, MySQL handles NULL values specially: If you insert NULL into a TIMESTAMP column, the current date and time is inserted

Question 3

What is three-valued logic?

Answer 3

a logic systems in which there are three truth values indicating true, false and some indeterminate third value ( NULL in case of sql)

Question 4

What is a left outer join?

Answer 4

includes all tuples from the left relation, padded with NULL values, where no matching tuples exist in the right relation.

Question 5

What is a Natural join operation?

Answer 5

• equates all pairs of attributes from the two relations having a common name
• projects out one of each pair of equated attributes