SQL Data Definition Language I

Tom Kelliher, CS 318

Feb. 1, 2002

Administrivia

Announcements

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.

Assignment

Have a look through the PostgreSQL Documentation on the course web site to familiarize yourself with PostgreSQL.

From Last Time

Intro to relational data model.

Outline

1. SQL, psql info.

2. SQL DDL: creating tables, primary and candidate keys, constraints, and foreign keys.

3. Exercise.

Coming Up

SQL DDL II.

SQL Variants, Useful psql Meta-Commands

1. SQL-92.

   Most DBMSs support most of this, with extensions.

2. SQL:99.

   Some DBMSs support parts of this.

3. SQL 3.

   Some DBMSs support parts of this?

Some useful psql meta-commands:

1. \i <filename> --- Read and execute a list of SQL commands in the text file
   <filename>.

2. \d <table> --- List <table>'s description.

3. \l --- List available databases on the system.

4. \dS --- Display list of system tables. Example queries on two system tables:

   SELECT * FROM pg_user;
   SELECT * FROM pg_tables;
   

5. \dp --- List permissions in current database.

6. \dt --- List tables in current database.

SQL DDL

What we need to do:

1. Create tables.

2. Create assertions within tables.
   1. Domain constraints: NOT NULL, DEFAULT.

   2. Semantic constraints (cannot order a negative number of widgets).

   3. Keys: primary, candidate, and foreign.

3. Create assertions between (external to) tables.

4. Create data types (or restrict existing types).

Creating Tables

CREATE TABLE Course (
   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100) );  -- Use text?

Some data types in PostgreSQL:

1. Numeric:
   1. INTEGER --- four bytes. Also SMALLINT and BIGINT.

   2. REAL --- single precision. Also DOUBLE PRECISION.

   3. SERIAL --- unique serial number. Some caveats --- see manual.

2. Character:
   1. CHAR(n) --- fixed length string of n characters.

   2. VARCHAR(n) --- variable length string of at most n characters.

   3. TEXT --- variable length string without limit. Not SQL standard.

3. Others: Monetary, date/time, boolean, etc.

   See manual.

Primary and Candidate Keys

CREATE TABLE Course (
   CrsCode        CHAR(6),
   DeptId         CHAR(4),
   CrsName        CHAR(20),
   Descr          CHAR(100),
   PRIMARY KEY (CrsCode),
   UNIQUE (DeptId, CrsName) );

1. One PRIMARY KEY. Table usually indexed on this.

2. One UNIQUE constraint for each remaining candidate key.

Specifying Constraints

1. Domain and semantic constraint examples:

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

   1. Use for intra-table constraints.

   2. Chicken and egg problem: use ALTER TABLE to add checks once tables are populated.

2. Assertion example:

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

   1. NOT EXISTS: empty set test. EXISTS?

      Briefly explain the correlated sub-query: for loop similarity.

   2. Note: this will fail before the registration period!

   3. Use for inter-table constraints.

   4. Tables must first exist!

   5. PostgreSQL does not have assertions! Use triggers?

Foreign Keys

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

1. In PostgreSQL the referenced key must be a primary key!

Exercise

See handout.