1SQL: Part IICPS 116Introduction to Database Systems2Announcements (September 20) Homework #2 assigned today Due in 9 days (September 29) Homework #1 sample solution available Hardcopies only Project Milestone #1 due in 23 days Come to my office hours if you want to chat about project ideas3Incomplete 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?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 careful• 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) Complicates schema and queries• SELECT AVG(GPA) FROM StudentWHERE GPA_is_valid;6SQL’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 i37Computing 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)8Three-valued logic TRUE = 1, FALSE = 0, UNKOWN = 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 enough9Unfortunate 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 equivalences410Another 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;11Outerjoin 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; 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 NULL12Outerjoin 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’s513Outerjoin examplesSID CID142 CPS196142 CPS114123 CPS196857 CPS196857 CPS130456 CPS114CID titleCPS199 Independent StudyCPS130 Analysis of AlgorithmsCPS114 Computer NetworksCourseEnrollCourse EnrollCID title SIDCPS199 Independent Study NULLCPS130 Analysis of Algorithms 857CPS114 Computer Networks 142CPS114 Computer Networks 456Course EnrollCID title SIDCPS196 NULL 142CPS114 Computer Networks 142CPS196 NULL 123CPS196 NULL 857CPS130 Analysis of Algorithms 857CPS114 Computer Networks 456CID title SIDCPS199 Independent Study NULLCPS130 Analysis of Algorithms 857CPS114 Computer Networks 142CPS114 Computer Networks 456CPS196 NULL 142CPS196 NULL 123CPS196 NULL 857Course Enroll14Outerjoin 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 EnrollON Course.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;15Summary of SQL features covered so far SELECT-FROM-WHERE statements Set and bag operations Table expressions, subqueries Aggregation and grouping Ordering NULL’s and outerjoins) Next: data modification statements, constraints616INSERT Insert one row INSERT INTO Enroll VALUES (456, ’CPS116’);• Student 456 takes CPS116Insert 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 CPS11617DELETE Delete everything DELETE FROM Enroll; Delete according to a WHERE conditionExample: Student 456 drops CPS116 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%’;18UPDATE Example: Student 142 changes name to “Barney” UPDATE StudentSET name = ’Barney’WHERE SID = 142; 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 table719Constraints 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)20Types of SQL constraints NOT NULL Key Referential integrity (foreign key) General assertion Tuple- and attribute-based CHECK’s21NOT NULL constraint examples CREATE TABLE Student(SID INTEGER NOT NULL,name VARCHAR(30) NOT NULL,email VARCHAR(30),age INTEGER,GPA FLOAT); CREATE TABLE Course(CID CHAR(10) NOT NULL,title VARCHAR(100) NOT NULL); CREATE TABLE Enroll(SID INTEGER NOT NULL,CID CHAR(10) NOT NULL);822Key declaration At most one PRIMARY KEY per table Typically implies a primary index
View Full Document