Introduction to Database Management Systems

Introduction to Database Management Systems Dr. Adam P. Anthony

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

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

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:

Executing an Outer Join • coursenatural left outer joinprereq

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

Full Outer Join • coursenatural full outer joinprereq

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.

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!

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

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

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

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));

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

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

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

Introduction to Database Management Systems