Missing Information - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Missing Information - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Missing Information

NULLs

• NULL is a placeholder for missing or

unknown value of an attribute. It is not

itself a value.

• Codd proposed to distinguish two kinds of

NULLs:

• A-marks: data Applicable but not known (for example, someone’s age)
• I-marks: data is Inapplicable (telephone number for someone who does not have a telephone, or spouse’s name for someone who is not married)

Theoretical solutions 1

• Use three-valued logic instead of classical

two-valued logic to evaluate conditions.

• When there are no NULLs around,

conditions evaluate to true or false, but if

a null is involved, a condition will evaluate

to the third value (‘undefined’, or

‘unknown’).

• This is the idea behind testing conditions in

WHERE clause of SQL SELECT: only tuples

where the condition evaluates to true are

returned.

3-valued logic

• If the condition involves a boolean

combination, we evaluate it as follows:

true true true true false true unknown unknown true false true false false true false un true un true un un un un un un un false false un un false true false true true false un false un true false false false false true

x y x AND y x OR y NOT x

Theoretical solutions 2

• Use variables instead of NULLs to represent unknown values.
• Different unknown values correspond to different variables
• When we apply operations such as selection to tables with variables, variables may acquire side conditions (constraints), for example x > 40 if x was unknown value of Mark and we include it in result of selection Mark > 40.
• This works out fine, but has high computational complexity and is not used in practice.
• More on conditional tables: Abiteboul, Hull, Vianu, Foundations of Databases.

SQL solution:NULLs in conditions

• Salary > 15,

evaluates to

‘unknown’ on the last

tuple – not included

SELECT *

FROM Employee Where Salary > 15,

Employee Name Salary John 25, Mark 15, Anne 20, Chris NULL

Name Salary

John 25,

Anne 20,

SQL solution: arithmetic

• Arithmetic operations

applied to NULLs

result in NULLs

SELECT

Salary1.1 AS NewSalary FROM Employee*

Employee Name Salary John 25, Mark 15, Anne 20, Chris NULL

NewSalary

NULL

SQL solution: aggregates

• Avg = 20,
• Num = 3
• Sum = 60,
• SELECT COUNT()...* gives a result of 4

SELECT

AVG(Salary) AS Avg, COUNT(Salary) AS Num, SUM(Salary) AS Sum FROM Employee

Employee Name Salary John 25, Mark 15, Anne 20, Chris NULL

Example: inner join

ID Name 123 John 124 Mary 125 Mark 126 Jane

ID Code 123 DBS 124 PRG 125 128 DBS

DBS

Mark 60 70 50 80

Student inner join Enrolment ID Name ID Code Mark 123 John 123 DBS 60 124 Mary 124 PRG (^70) 125 Mark 125 DBS 50

Student Enrolment

dangles

Example: full outer join

ID Name 123 John 124 Mary 125 Mark 126 Jane

ID Code 123 DBS 124 PRG 125 128 DBS

DBS

Mark 60 70 50 80

Student full outer join Enrolment ID Name ID Code Mark 123 John 123 DBS 60 124 Mary 124 PRG (^70) 125 Mark 125 DBS 50

Student Enrolment

dangles

126 Jane 128 DBS^80

null null null null null

Example: right outer join

ID Name 123 John 124 Mary 125 Mark 126 Jane

ID Code 123 DBS 124 PRG 125 128 DBS

DBS

Mark 60 70 50 80

Student right outer join Enrolment ID Name ID Code Mark 123 John 123 DBS 60 124 Mary 124 PRG (^70) 125 Mark 125 DBS 50

Student Enrolment

dangles

null null (^128) DBS 80

Outer Join Syntax in Oracle

SELECT

FROM OUTER JOIN

ON

Where is one of LEFT , RIGHT , or FULL

Example:

SELECT *

FROM Student FULL OUTER JOIN Enrolment

ON Student.ID = Enrolment.ID

Default Value Example

• Default values are
  • ??? for Name
  • -1 for Wgt and Qty
• -1 is used for Wgt and Qty as it is not sensible otherwise so won’t appear by accident, but what about

Parts

ID Name Wgt Qty

1 Nut 10 20 2 Bolt 15 - 3 Nail 3 100 4 Pin -1 30 5 ??? 20 20 6 Screw -1 - 7 Brace (^150 0) UPDATE Parts

SET Qty = Qty + 5

Problems With Default Values

• Since defaults are

real values

• They can be updated like any other value
• You need to use a value that won’t appear in any other circumstances
• They might not be interpreted properly - Also, within SQL

defaults must be of

the same type as the

column

• You can’t have have a string such as ‘unknown’ in a column of integers