A 12 of R B 34 of R C 246 of S D All of the above E None of the above 90 A B 1

A 12 of r b 34 of r c 246 of s d all of the above e

This preview shows page 90 - 99 out of 110 pages.

A. (1,2) of R B. (3,4) of R C. (2,4,6) of S D. All of the above E. None of the above 90 A B 1 2 3 4 5 6 B C D 2 4 6 4 6 8 4 7 9 R(A,B) S(B,C,D)
Image of page 90
Back to SQL So far …… We have seen simple Select From Where examples We have seen union intersect and except (Minus) examples We have nested queries We have seen aggregate functions being used in SQL Queries, as well as group by and having clauses We have seen nulls and how and what we mean by outer joins But How do we modify our relations? What are assertions and triggers? 91
Image of page 91
92 Database Manipulation Insertion redux Can insert a single tuple using: INSERT INTO S tudent VALUES (53688, ‘Smith’, ‘222 W.15 th ave’, 333-4444, MATH) or INSERT INTO S tudent (sid, name, address, phone, major) VALUES (53688, ‘Smith’, ‘222 W.15 th ave’, 333-4444, MATH) Add a tuple to student with null address and phone: INSERT INTO S tudent (sid, name, address, phone, major) VALUES (33388, ‘Chan’, null, null, CPSC)
Image of page 92
93 Database Manipulation Insertion redux (cont) Can add values selected from another table Enroll student 51135593 into every class taught by faculty 90873519 INSERT INTO Enrolled SELECT 51135593, name FROM Class WHERE fid = 90873519 The select-from-where statement is fully evaluated before any of its results are inserted So, queries like INSERT INTO table1 SELECT * FROM table1 are ok.
Image of page 93
94 Database Manipulation Deletion Note that only whole tuples are deleted. Can delete all tuples satisfying some condition (e.g., name = Smith): DELETE FROM Student WHERE name = ‘Smith’ Delete all students whose age is above the average student age: DELETE FROM Student WHERE age > (SELECT avg(age) FROM Student)
Image of page 94
95 Database Manipulation Updates Increase the age of all students by 2 (should not be more than 100) Need to write two updates: UPDATE Student SET age = 100 WHERE age >= 98 UPDATE Student SET age = age + 2 WHERE age < 98 Is the order important?
Image of page 95
96 Integrity Constraints (Review) An IC describes conditions that every legal instance of a relation must satisfy. Inserts/deletes/updates that violate IC’s are disallowed. Can ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200) Types of IC’s : domain constraints, primary key constraints, foreign key constraints, general constraints
Image of page 96
97 General Constraints: Check Created with a CHECK clause. Constraints can be named Can use subqueries to express constraint CREATE TABLE Student ( snum INTEGER, sname CHAR(32), major CHAR(32), standing CHAR(2) age REAL, PRIMARY KEY (snum), CHECK ( age >= 10 AND age < 100 ); CREATE TABLE Enrolled ( snum INTEGER, cname CHAR(32), PRIMARY KEY (snum, cname), CONSTRAINT noR15 CHECK (`R15’ <> ( SELECT c.room FROM class c WHERE c.name=cname))); Check constraints are checked when tuples are inserted or modified
Image of page 97
98 Constraints over Multiple Relations: Remember this one? We couldn’t express “every employee works in a department and every department has some employee in it”? Neither foreign-key nor not-null constraints in Works_In can do that.
Image of page 98
Image of page 99

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture