DOC PREVIEW
Duke CPS 116 - Introduction to Database Systems

This preview shows page 1-2-3-4 out of 11 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 11 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 11 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 11 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 11 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 11 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

1SQL: Part IICPS 116Introduction to Database Systems2Announcements (September 18) Homework #1 due today (11:59pm) Submit in class, slide underneath my office door Sample solution available Thursday Homework #2 assigned today Due next Thursday Project milestone #1 due in 3½ weeks3Incomplete information Example: Student (SID, name, age, GPA) Value unknownWe do not know Nelson’s ageWdoo owNsosg Value not applicable Nelson has not taken any classes yet; what is his GPA?24Solution 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 carefulS C A G(G A) O S d•SELECT AVG(GPA) FROM Student; Complicates applications• SELECT AVG(GPA) FROM StudentWHERE GPA <> -1; Remember the Y2K bug?• “00” was used as a missing or invalid year value5Solution 2 A valid-bit for every column Student (SID, name, name_is_valid,age, age_is_valid,GPA, GPA_is_valid)Cli h d iComplicates schema and queries• SELECT AVG(GPA) FROM StudentWHERE GPA_is_valid;6Solution 3? Decompose the table; missing row = missing value StudentName (SID, name)StudentAge (SID, age)StudentGPA (SID, GPA)Sd ID(SID)StudentID(SID) Conceptually the cleanest solution Still complicates schema and queries37SQL’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 i8Computing 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)9Three-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 enough410Unfortunate 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 equivalences11Another problem Example: Who has NULL GPA values? SELECT * FROM Student WHERE GPA = NULL; Introduced built-in predicates IS NULL and IS NOT NULL• SELECT * FROM Student WHERE GPA IS NULL;12Outerjoin motivation Example: a master class list SELECT c.CID, c.title, s.SID, s.nameFROM Course c, Enroll e, Student sWHERE c.CID = e.CID AND e.SID = s.SID;Wh if l i ?What if a class is empty? It may be reasonable for the master class list to include empty classes as well• For these classes, SID and name columns would be NULL513Outerjoin flavors and definitions A full outerjoin between R and S (denoted R & S) includes all rows in the result of R ! S, plus “Dangling” R rows (those that do not join with any Srows) padded with NULL’s for S’s columns “Dangling” S rows (those that do not join with any Rrows) padded with NULL’s for R’s columns A left outerjoin (R ' S) includes rows in R ! S plus dangling R rows padded with NULL’s A right outerjoin (R ( S) includes rows in R ! Splus dangling S rows padded with NULL’s14Outerjoin examplesCourseEllCourse ' EnrollCID titleCPS199 Independent StudyCPS130 Analysis of AlgorithmsCPS116 Intro. to Database SystemsCID title SIDCPS116 Intro. to Database Systems 142CPS114 NULL 142CPS114 NULL 123CID title SIDCPS199 Independent Study NULLCPS130 Analysis of Algorithms 857CPS116 Intro. to Database Systems 142CPS116 Intro. to Database Systems 857CPS116 Intro. to Database Systems 456EnrollCourse ( EnrollCourse & EnrollSID CID142 CPS116142 CPS114123 CPS114857 CPS116857 CPS130456 CPS116CPS116 Intro. to Database Systems 857CPS130 Analysis of Algorithms 857CPS116 Intro. to Database Systems 456CID title SIDCPS199 Independent Study NULLCPS116 Intro. to Database Systems 142CPS114 NULL 142CPS114 NULL 123CPS116 Intro. to Database Systems 857CPS130 Analysis of Algorithms 857CPS116 Intro. to Database Systems 45615Outerjoin syntax SELECT * FROM Course LEFT OUTER JOIN EnrollON Course.CID = Enroll.CID; SELECT * FROM Course RIGHT OUTER JOIN EnrollON Course.CID = Enroll.CID; SELECT * FROM Course FULL OUTER JOIN EnrollONC CID E ll CIDONCourse.CID = Enroll.CID; ) These are theta joins rather than natural joins Return all columns in Course and Enroll Equivalent to Course 'Course.CID = Enroll.CIDEnroll, Course (Course.CID = Enroll.CIDEnroll, and Course &Course.CID = Enroll.CIDEnroll) You can write regular (“inner”) joins using this syntax too:SELECT * FROM Course JOIN Enroll ON Course.CID = Enroll.CID;616Summary of SQL features covered so far SELECT-FROM-WHERE statements Set and bag operations Table expressions, subqueriesAggregation and groupingAggregation and grouping Ordering NULL’s and outerjoins) Next: data modification statements, constraints17INSERT Insert one row INSERT INTO Enroll VALUES (456, ’CPS116’);• Student 456 takes CPS116Insert the result of a queryInsert the result of a query INSERT INTO Enroll(SELECT SID, ’CPS116’ FROM StudentWHERE SID NOT IN (SELECT SID FROM EnrollWHERE CID = ’CPS116’));• Force everybody to take CPS11618DELETE Delete everything DELETE FROM Enroll; Delete according to a WHERE conditionExample: Student 456 drops CPS116DELETE FROM E llDELETE FROM EnrollWHERE SID = 456 AND CID = ’CPS116’;Example: Drop students from all CPS classes with GPA lower than 1.0 DELETE FROM EnrollWHERE SID IN (SELECT SID FROM StudentWHERE GPA < 1.0)AND CID LIKE ’CPS%’;719UPDATE Example: Student 142 changes name to “Barney” UPDATE StudentSET name = ’Barney’WHERE SID = 142;l’b“fi” Example: Let’s be “fair”? UPDATE StudentSET GPA = (SELECT AVG(GPA) FROM Student);• But update of every row causes average GPA to change!• Average GPA is computed over the old Student table20Constraints Restrictions on allowable data in a database In addition to the simple structure and type restrictions imposed by the table definitions Declared as part of the schema Enforced by the DBMS Why use constraints? Protect data integrity (catch errors) Tell the DBMS about the data (so it can optimize better)21Types of SQL constraints NOT


View Full Document

Duke CPS 116 - Introduction to Database Systems

Documents in this Course
Part I

Part I

8 pages

XSLT

XSLT

4 pages

XSLT

XSLT

8 pages

Part I

Part I

8 pages

XSLT

XSLT

8 pages

Load more
Download Introduction to Database Systems
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Introduction to Database Systems and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Introduction to Database Systems 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?