1. Introduction to Database Management Systems Dr. Adam P. Anthony

2. Lecture Overview • Joins • Views • Transactions • Data Integrity Constraints

3. Joins: The Full Story • Join: by itself, nothing more than a Cartesian product: • FROM student join takes == FROM student, takes • Can Add an ON keyword • student join takes ON student.ID = takes.ID • Very similar to natural join • If names equivalent, save time with using: • student join takes USING (ID) • If you will use ALL equivalent column names, natural saves most time: • student natural join takes

4. Outer Joins • A normal (INNER) join will skip any tuple that doesn’t have a matching pair between tables • List all courses with their pre-requisites:

5. Executing an Outer Join • coursenatural left outer joinprereq

6. Right Outer Join • Left/Right just says which table gets to include un-matched tuples: • coursenatural right outer joinprereq

7. Full Outer Join • coursenatural full outer joinprereq

8. Join Practice • Reverse-Lookup: List all courses paired with any course for which it is listed as a pre-requisite. Also include courses that are not a pre-requisite for anything.

9. Views: Dynamic Tables • View: a “table” in a database that is defined by a query • Simplifying large queries • Storing common subqueries • Restricting Access • Very easy to define: • CREATE VIEW <NAME> AS <QUERY> • At this point it behaves, programmatically, like a regular table!

10. View Example • Keep people from seeing instructor salary: • CREATE VIEW faculty ASSELECT ID, Name, dept_nameFROM instructor • To encapsulate a complex query: • CREATE VIEW enrollments ASSELECTcourse_id, sec_id, semester, year, count(*) as enrollmentFROM takesGROUP BYcourse_id, sec_id, semester, year • Select max(enrollment)from enrollments

11. View Concerns • Every time you use a view its query gets re-executed! • Some systems allow for materialized views, which must be updated whenever the underlying data changes • Some database systems allow for updating views • The changes actually affect the underlying tables • Can be more trouble than it is worth • Know that it can be done; read the book if you ever need to do it

12. Integrity Constraints • Best laid plans… • Humans Make mistakes! • Designers can build in rules that catch mistakes and keep them from becoming permanent • Not Null • Primary Key • Unique • Default value • Check( P ) where P is some predicate • Foreign Key Rules

13. Check constraint CREATE TABLE tracks( album_ID VARCHAR (25), vol_num NUMERIC (3) CHECK (vol_num > 0), track_num NUMERIC (3), playing_time NUMERIC (5), instrumental CHAR (1) CHECK (instrumental in (‘Y’,’N’)), vocal CHAR (1) CHECK (vocal in (‘Y’,’N’)), rating CHAR (5) DEFAULT ‘*’ CHECK (rating in (‘*’,’**’,’***’,’****’,’*****’)), track_name VARCHAR (80), rotation CHAR (1), PRIMARY KEY (album_ID,vol_num,track_num));

14. Foreign Keys CREATE TABLE tracks( album_ID VARCHAR (25), vol_num NUMERIC (3), track_num NUMERIC (3), playing_time NUMERIC (5), instrumental CHAR (1), vocal CHAR (1), rating CHAR (5), track_name VARCHAR (80), rotation CHAR (1), PRIMARY KEY (album_ID,vol_num,track_num) FOREIGN KEY (album_ID) REFERENCES ALBUMS ON DELETE CASCADE ON UPDATE CASCADE); • Foreign Key constraints: • System requires that the listed field(s) already exist in the referenced table • On Delete, Update Rules • Cascade • Set Null • Set Default

15. Foreign Keys in SQLITE • Relatively new: http://www.sqlite.org/foreignkeys.html • Referential Integrity off by default: • sqlite> PRAGMA foreign_keys = ON; • Useful Feature: Deferring Constraints • Add ‘DEFERRABLE INITIALLY DEFERRED’ to the foreign key definition

16. Deferred Foreign Keys and Transactions • All queries are atomic transactions • A sequence of queries can be made to execute in an all-or-nothing manner: • BEGIN TRANSACTION; insert…update…delete…END TRANSACTION; • If a foreign key in SQLITE is marked as deferred, then foreign key violations are allowed as long as they are fixed before you get to END TRANSACTION