Tom Kelliher, CS 325
Nov. 9, 2011
Sensitive data and inference in databases.
Examples: Oracle, Sybase, MS SQL Server, MySQL, PostgreSQL.
Potentially multiple distributed databases on heterogeneous platforms.
Database characteristics. Envision an airline reservations system.
View as a predicate -- a statement of truth. A set.
An ordered n-tuple.
Properties of tuples.
CREATE TABLE "student" ( "id" integer NOT NULL, "name" character(20) NOT NULL, "address" character(50), "status" character(10) DEFAULT 'Freshman', Constraint "stu_key" Primary Key ("id") ); CREATE TABLE "transcript" ( "stuid" integer, "crscode" character(6), "semester" character(6), "grade" character(1), CONSTRAINT "gradecheck" CHECK ( grade in ('A', 'B', 'C', 'D', 'F')), CONSTRAINT "stuidcheck" CHECK (stuid > 0 AND stuid < 1000000000) );
psql demo and sample queries:
-- Get name of student with particular Id #. select Name from Student where Id = '987654321'; -- Get Id and Name of all seniors. select Id, Name from Student where Status = 'Senior'; -- Get Name, Course, and Grade for all seniors. -- Must match tuples (join) between two relations. select Name, CrsCode, Grade from Student, Transcript where StuId = Id and Status = 'Senior';
Some integrity constraints for Student Registration System:
A transaction's ACID properties:
Transaction designer assumes database is initially consistent.
Two-phase commit and its importance.
Intra-relational. Key constraint. Name another ``key.'' Static.
Static constraints define legal instances.
Inter-relational. Foreign key constraint. Static.
Dynamic constraints define transitions between legal instances.
Semantic constraint. Implement business rules.