07-sql-notes

07-sql-notes - SQL: Part II CPS 116 Introduction to...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 SQL: Part II CPS 116 Introduction to Database Systems 2 Announcements (Thu. Sep. 22) ± Homework #2 assigned today ² Due on Tue. Oct. 4 (in less than 2 weeks) ± Midterm in class in 2 weeks ± Project milestone 1 due in 3 weeks 3 Incomplete information ± Example: Student ( SID , name , age , GPA ) ± Value unknown ² We do not know Nelson’s age ± Value not applicable ² Nelson has not taken any classes yet; what is his GPA?
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 4 Solution 1 ± A dedicated special value for each domain (type) ² GPA cannot be –1, so use –1 as a special value to indicate a missing or invalid GPA ² Leads to incorrect answers if not careful SELECT AVG(GPA) FROM Student; ² Complicates applications SELECT AVG(GPA) FROM Student WHERE GPA <> -1; ² Ever heard of the Y2K bug? • “00” was used as a missing or invalid year value 5 Solution 2 ± A valid-bit for every column ² Student ( SID , name , name_is_valid , age , age_is_valid , GPA , GPA_is_valid ) ² Complicates schema and queries SELECT AVG(GPA) FROM Student WHERE GPA_is_valid; 6 Solution 3? ± Decompose the table; missing row = missing value ² StudentName ( SID , name ) StudentAge ( SID , age ) StudentGPA ( SID , GPA ) ² StudentID ( SID ) ² Conceptually the cleanest solution ² Still complicates schema and queries • How to get all information about a student in a table?
Background image of page 2
3 7 SQL’s solution ± A special value NULL ² For every domain ² Special rules for dealing with NULL ’s ± Example: Student ( SID , name , age , GPA ) ² h 789, “Nelson”, NULL , NULL i 8 Computing with NULL ’s ± When we operate on a NULL and another value (including another NULL ) using +, –, etc., the result is NULL ± Aggregate functions ignore NULL , except COUNT(*) (since it counts rows) 9 Three-valued logic ± TRUE = 1, FALSE = 0, UNKNOWN = 0.5 ± x AND y = min( x , y ) ± x OR y = max( x , y ) ± NOT x = 1 – x ± When we compare a NULL with another value (including another NULL ) using =, >, etc., the result is UNKNOWN ± WHERE and HAVING clauses only select rows for output if the condition evaluates to TRUE ² UNKNOWN is not enough
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 10 Unfortunate consequences ± SELECT AVG(GPA) FROM Student; SELECT SUM(GPA)/COUNT(*) FROM Student; ± SELECT * FROM Student; SELECT * FROM Student WHERE GPA = GPA; ) Be careful: NULL breaks many equivalences 11 Another problem ± Example: Who has NULL GPA values?
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This document was uploaded on 01/17/2012.

Page1 / 11

07-sql-notes - SQL: Part II CPS 116 Introduction to...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online