{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

07-sql

# 07-sql - Announcements(Thu Sep 22 Homework#2 assigned today...

This preview shows pages 1–3. Sign up to view the full content.

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? 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? • Natural join doesn’t work!

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

View Full Document
2 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 10 Unfortunate consequences ± SELECT AVG(GPA) FROM Student; SELECT SUM(GPA)/COUNT(*) FROM Student; ² Not equivalent ² Although AVG(GPA) = SUM(GPA)/COUNT(GPA) still ± SELECT * FROM Student; SELECT * FROM Student WHERE GPA = GPA; ² Not equivalent ) Be careful: NULL breaks many equivalences 11 Another problem ± Example: Who has NULL GPA values?
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 6

07-sql - Announcements(Thu Sep 22 Homework#2 assigned today...

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

View Full Document
Ask a homework question - tutors are online