Relations, SQL, DBMS

In SQL’s viewpoint, there are 3 kinds of relations
1. stored relations — real tables residing on disk
2. views — virtual tables resulting from computation
3. temporary tables — internally created, used, and thrown away; can’t be accessed by users
As to SQL, we’ll stick to the standard version as much as possible. In this section we study the DDL part.
As to DMBS, we use the open-source MariaDB. Other possible choice could be MySQL, PostgreSQL, SQLite, etc.

SQL data types

CHAR(n) — e.g. ‘bar’
VARCHAR(n)
BIT(n)
BIT VARYING(n)
BOOLEAN — with possible values TRUE, FALSE, UNKNOWN
INT or INTEGER
SHORTINT
FLOAT or REAL
DOUBLE PRECISION
DECIMAL(n, d) — e.g. 1234.45 has type DECIMAL(6, 2)
NUMERIC
DATE — e.g. DATE ‘2012-08-09’
TIME — e.g. TIME ‘14:29:06.34’
etc, including nonstandard types specific to the DBMS’s

Table creation

Here are some example SQL table creation commands

CREATE TABLE Movies (
    title       CHAR(100),
    year        INT,
    length      INT,
    genre       CHAR(10),
    studioName  CHAR(30),
    producerC#  INT
);

CREATE TABLE MovieStar (
    name        CHAR(30),
    address     VARCHAR(255),
    gender      CHAR(1),
    birthdate   DATE
);

Mention case-(in)sensitivity of keywords & names here.

Modifying relation schemas

Deleting a relation:
```
DROP TABLE R;
```

Adding a new attribute:

ALTER TABLE MovieStar ADD phone CHAR(16);

Deleting an existing attribute:
```
ALTER TABLE MovieStar DROP birthdate;
```

Default values

When creating a table, we can provide default values that’s different from NULL to attributes, e.g.

CREATE TABLE MovieStar (
    name        CHAR(30),
    address     VARCHAR(255),
    gender      CHAR(1) DEFAULT '?',
    birthdate   DATE DEFAULT DATE '0000-00-00'
);

Can use default values at attribute modification time as well, e.g.,
```
ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT 'unlisted';
```

Declaring keys, first method

Approriate when key has exactly one attribute.

Add one phrase right after the normal attribute declaration, like this

CREATE TABLE MovieStar (
    name        CHAR(30) PRIMARY KEY,
    address     VARCHAR(255),
    gender      CHAR(1),
    birthdate   DATE
);

or like this

CREATE TABLE MovieStar (
    name        CHAR(30) UNIQUE,
    address     VARCHAR(255),
    gender      CHAR(1),
    birthdate   DATE
);

UNIQUE means NULL value allowed while PRIMARY KEY means NULL value not allowed.

Declaring keys, second method

Approriate for all cases.

Declare the group of attributes to be key after declaring all attributes, like this

CREATE TABLE MovieStar (
    name        CHAR(30),
    address     VARCHAR(255),
    gender      CHAR(1),
    birthdate   DATE,
    PRIMARY KEY (name)
);

CREATE TABLE Movies (
    title       CHAR(100),
    year        INT,
    length      INT,
    genre       CHAR(10),
    studioName  CHAR(30),
    producerC#  INT,
    PRIMARY KEY (title, year)
);

UNIQUE can be used in place of PRIMARY KEY with the same meaning described in last slide.

Defining a relation schema

San Skulrattanakulchai

February 15, 2019