Tom Kelliher, CS 318
Feb. 1, 2002
Recommendation: Use a special section of your notebook to collect SQL statement details. It will grow in terms of number of statements and aspects of statements; plan accordingly.
Have a look through the PostgreSQL Documentation on the course web site to familiarize yourself with PostgreSQL.
Intro to relational data model.
psql
info.
SQL DDL II.
Most DBMSs support most of this, with extensions.
Some DBMSs support parts of this.
Some DBMSs support parts of this?
Some useful psql
meta-commands:
\i <filename>
--- Read and execute a list of SQL commands in
the text file <filename>
.
\d <table>
--- List <table>
's description.
\l
--- List available databases on the system.
\dS
--- Display list of system tables. Example queries on two
system tables:
SELECT * FROM pg_user; SELECT * FROM pg_tables;
\dp
--- List permissions in current database.
\dt
--- List tables in current database.
\?
in psql
for the entire list and see the man page for
psql
.
What we need to do:
NOT NULL
, DEFAULT
.
CREATE TABLE Course ( CrsCode CHAR(6), DeptId CHAR(4), CrsName CHAR(20), Descr CHAR(100) ); -- Use text?
Some data types in PostgreSQL:
INTEGER
--- four bytes. Also SMALLINT
and
BIGINT
.
REAL
--- single precision. Also DOUBLE PRECISION
.
SERIAL
--- unique serial number. Some caveats --- see
manual.
CHAR(n)
--- fixed length string of n
characters.
VARCHAR(n)
--- variable length string of at most n
characters.
TEXT
--- variable length string without limit. Not SQL
standard.
See manual.
CREATE TABLE Course ( CrsCode CHAR(6), DeptId CHAR(4), CrsName CHAR(20), Descr CHAR(100), PRIMARY KEY (CrsCode), UNIQUE (DeptId, CrsName) );
PRIMARY KEY
. Table usually indexed on this.
UNIQUE
constraint for each remaining candidate key.
CREATE TABLE Course ( CrsCode CHAR(6) NOT NULL, Limit INTEGER DEFAULT '10', Division CHAR(5), DeptId CHAR(4), CrsName CHAR(20), Descr CHAR(100), CHECK (Limit > 0 AND Limit <= (SELECT MAX(Seats) from Room)), CHECK (Division IN ('Upper', 'Lower')) );
ALTER TABLE
to add checks once
tables are populated.
CREATE ASSERTION CoursesShallNotBeEmpty CHECK (NOT EXISTS ( SELECT * FROM Teaching Te WHERE NOT EXISTS ( SELECT * FROM Transcript Tr WHERE Te.CrsCode = Tr.CrsCode AND Te.Semester = Tr.Semester )));
NOT EXISTS
: empty set test. EXISTS
?
Briefly explain the correlated sub-query: for loop similarity.
CREATE TABLE Teaching ( ProfId INTEGER, CrsCode CHAR(6), Semester CHAR(6), PRIMARY KEY (CrsCode, Semester), FOREIGN KEY (CrsCode) REFERENCES Course, FOREIGN KEY (ProfID) REFERENCES Professor (Id) );
See handout.