Tom Kelliher, CS 318
Jan. 23, 2002
Read Chapters 1 and 2.
- A closer look: Student Registration System, relational databases,
properties of transactions.
Introduction to HTML, PHP, and Unix.
- a database?
- a DBMS?
Examples: Oracle, Sybase, MS SQL Server, mySQL, PostgreSQL.
- a Transaction? (Changes state of database.)
- a Transaction Processing System? (Transactions, TP monitor, DBMS,
Potentially multiple distributed databases on heterogeneous platforms.
Database characteristics. Envision an airline reservations system.
- Response time.
Common example: Student Registration System. Faculty and/or students can:
This is a starting point --- additional transactions needed. Also
- Register for courses for next semester.
- Obtain student status reports.
- Maintain information about students and courses.
- Enter final grades for completed courses.
- Table, relation.
View as a predicate --- a statement of truth. A set.
- Row, tuple.
An ordered n-tuple.
- Column, attribute.
Properties of tuples.
- Domain of an attribute.
CREATE TABLE "student" (
"id" integer NOT NULL,
"name" character(20) NOT NULL,
"status" character(10) DEFAULT 'Freshman',
Constraint "stu_key" Primary Key ("id")
CREATE TABLE "transcript" (
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 #.
where Id = '987654321';
-- Get Id and Name of all seniors.
select Id, Name
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:
- Student Ids are unique.
- Students must satisfy course prerequisites before registering for a
- The number of students registered for a course cannot exceed the
- Suppose there are two ways to count the number of students registered
fro a course (aggregate on Transcript relation and attribute of Courses
relation). These two ways of counting must yield the same result.
A transaction's ACID properties:
- Atomicity: All or nothing.
- Consistency: Integrity constraints are preserved.
Transaction designer assumes database is initially consistent.
- Isolation: Consider multiple simultaneous transactions. What bad
things can happen?
- Serial execution.
- Transaction schedules: serial, concurrent.
- Serializable concurrent schedules.
- Isolation definition: Even though transactions are executed
concurrently, the overall effect of the schedule must be the same as if
the transactions had executed serially in some order..
- Durability: Once a transactions commits, its results are permanent.
Thomas P. Kelliher
Tue Jan 22 17:41:23 EST 2002