Data Definition Language

UMBC CMSC 461 Spring '99

Data Definition Language (DDL)

SQL DDL allows the specification of a set of relations and about each relation:

Schema of each relation
Domain of values of associated with each attribute
Integrity constraints
Set of indices for each relation
Security and authorization information for each relation
Physical storage of each relation on the disk

Data types (Domain types) in standard SQL-92

char(n) is fixed-length character string. character is equivalent.
varchar(n) is a variable-length character of a user-specified maximum of n.
int is an integer (machine-dependent). integer is equivalent.
smallint is a small integer (machine-dependent).
numeric(p,d) is a fixed-point number with user-specified precision of pdigits and ddigits after the decimal point. Note that this does not include the decimal point!
real, double precision are floating point and double-precision numbers, with machine-dependent precision.
float(n) is a floating point number, with user-specified precision of at least n precision.
date is a calendar date, containing a (four-digit) year, month, and day of the month.
time is the time of day, in hours, minutes, and seconds.

Null values are a member of all data types.

Data types (Domain types) in Oracle -- Not the same!

Table names can be up to 30 characters long and cannot begin with a number. The table name cannot conflict with another object created in the same user account, and it cannot be the name of an Oracle reserved word.
There can be up to 254 columns for a single table.

Datatypes (only the commonly used ones):

number is used to declare both real and whole numbers. The data can be up to 38 characters long. (p,d are used for precision and number of decimals after the decimal point.
date can store the date and time (day month, year, century, hours, minutes, and seconds).
Example: '12/30/1996 23:11:12'
varchar2 has a maximum size of 2000 characters. Stores any kind of characters. Oracle only uses the number of bytes required. This is preferred over the varchar.
char(n) is a fixed length of n, where n is <= 255. Short entries are padded with spaces.
long is like varchar2, except that it holds up to 2GB of characters.
raw holds a maximum length of 255 bytes of binary data.
long raw is used to hold large amounts of binary data.
ANSI standard types are allowed in the DDL, but get converted to the preferred Oracle equivalent.

Schema Definition in SQL

We define an SQL relation using the create table command:

create table table_name ( attribute-domain-1,
                          attribute-domain-2, 
                          attribute-domain-3, 
                          ...,
                          attribute-domain-n,
                          integrity-constraint-1, 
                          integrity-constraint-2, 
                          integrity-constraint-3,
                          ..., 
                          integrity-constraint-n )

Integrity constrains

The primary key lists the attributes that form the primary key of the relation. Although the primary key specification is optional, it is generally a good idea to specify one.
The check specifies a condition that must be met by all rows in the table.
It is reasonable to assume that sometimes certain columns might not have a value, there are also times when the database design can not allow the column to be blank. This is done with the "not null" clause. An attempt to add a row with a value missing when it is defined as not null causes the row to be rejected and an error is logged.
One more possibility to mention here is that the attribute can be defined as unique. In that case, only one row may have that specific value. One example of when to use the unique clause if for Social Security numbers.

Examples of creating tables:

    create table customer
    (
        customer_name      char(20) not null,
        customer_street    char(30),
        customer_city      char(30),
        primary key ( customer_name  )
    )

    create table branch
    (
        branch_name        char(15) not null,
        branch_city        char(30),
        assets             number(30,2),
        primary key ( branch-name  )
    )

    create table account
    (
        account_number      char(10) not null,
        branch_name         char(15),
        balance             number(12,2),
        primary key ( account_number  ),
        check ( balance >= 0 )
    )

    create table depositor
    (
        customer-name       char(20) not null,
        account_number      char(10) not null,,
        primary key ( customer-name, account_number  )
    )

Tuesday, 08-Jun-1999 11:59:47 EDT