SQL Data Definition

The set of relations in a database must be specified to the system by means of a
data-definition language (DDL). The SQL DDL allows specification of not only a
set of relations, but also information about each relation, including:

• The schema for each relation.

• The types of values associated with each attribute.

• The integrity constraints.

• The set of indices to be maintained for each relation.

• The security and authorization information for each relation.

• The physical storage structure of each relation on disk.

We discuss here basic schema definition and basic types; we defer discussion of
the other SQL DDL features to later topics.

1. Basic Types

The SQL standard supports a variety of built-in types, including:

• char (n): fixed-length character string with user-specified length n. The full
form, character, can be used instead.

• varchar(n): A variable-length character string with user-specified maximum
length n. The full form, character varying, is equivalent.

• int: An integer (a finite subset of the integers that is machine dependent). The
full form, integer, is equivalent.

• smallint: A small integer (a machine-dependent subset of the integer type).

• numeric(p, d): A fixed-point number with user-specified precision. The num?ber consists of p digits (plus a sign), and d of the p digits are to the right of
the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but
neither 444.5 or 0.32 can be stored exactly in a field of this type.

• real, double precision: Floating-point and double-precision floating-point
numbers with machine-dependent precision.

• float(n): A floating-point number, with precision of at least n digits.

Each type may include a special value called the null value. A null value
indicates an absent value that may exist but be unknown or that may not exist at
all. In certain cases, we may wish to prohibit null values from being entered, as
we shall see shortly.

The char data type stores fixed length strings. Consider, for example, an
attribute A of type char(10). If we store a string “Avi” in this attribute, 7 spaces
are appended to the string to make it 10 characters long. In contrast, if attribute B
were of type varchar(10), and we store “Avi” in attribute B, no spaces would be
added. When comparing two values of type char, if they are of different lengths
extra spaces are automatically added to the shorter one to make them the same
size, before comparison.

When comparing a char type with a varchar type, one may expect extra spaces
to be added to the varchar type to make the lengths equal, before comparison;
however, this may or may not be done, depending on the database system. As a
result, even if the same value “Avi” is stored in the attributes A and B above, a
comparison A=B may return false. We recommend you always use the varchar
type instead of the char type to avoid these problems.

SQL also provides the nvarchar type to store multilingual data using the
Unicode representation. However, many databases allow Unicode (in the UTF-8
representation) to be stored even in varchar types.

?2. Basic Schema Definition

We define an SQL relation by using the create table command. The following
command creates a relation department in the database.

create table department

(dept_name varchar (20),
building varchar (15),
budget numeric (12,2),
primary key (dept_name));

The relation created above has three attributes, dept_name, which is a character
string of maximum length 20, building, which is a character string of maximum
length 15, and budget, which is a number with 12 digits in total, 2 of which are
after the decimal point. The create table command also specifies that the dept_name attribute is the primary key of the department relation.
The general form of the create table command is:

create table r

(A₁ D_2,
A₂ D₂,
...,
A_n D_n,
_{{integrity-constraint 1},}
...,
{integrity-constraint k });

where r is the name of the relation, each A_i is the name of an attribute in the
schema of relation r, and D_i is the domain of attribute A_i; that is, D_i specifies the
type of attribute A_i along with optional constraints that restrict the set of allowed
values for A_i.

The semicolon shown at the end of the create table statements, as well as
at the end of other SQL statements later in this chapter, is optional in many SQL
implementations.

SQL supports a number of different integrity constraints. In this section, we
discuss only a few of them:

• primary key (Aj1 , Aj2 ,..., Ajm ): The primary-key specification says that at?tributes Aj1 , Aj2 ,..., Ajm form the primary key for the relation. The primary?key attributes are required to be nonnull and unique; that is, no tuple can have
a null value for a primary-key attribute, and no two tuples in the relation
can be equal on all the primary-key attributes. Although the primary-key specification is optional, it is generally a good idea to specify a primary key
for each relation.

• foreign key (Ak1 , Ak2 ,..., Akn )references s: The foreign key specification says
that the values of attributes (Ak1 , Ak2 ,..., Akn ) for any tuple in the relation
must correspond to values of the primary key attributes of some tuple in
relation s.

Figure 3.1 presents a partial SQL DDL definition of the university database we
use in the text. The definition of the course table has a declaration “foreign key
(dept_name)references department”. This foreign-key declaration specifies that
for each course tuple, the department name specified in the tuple must exist
in the primary key attribute (dept_name) of the department relation. Without
this constraint, it is possible for a course to specify a nonexistent department
name. Figure 3.1 also shows foreign key constraints on tables section, instructor
and teaches.

• not null: The not null constraint on an attribute specifies that the null value
is not allowed for that attribute; in other words, the constraint excludes the
null value from the domain of that attribute. For example, in Figure 3.1, the
not null constraint on the name attribute of the instructor relation ensures that
the name of an instructor cannot be null.

SQL prevents any update to the database that violates an integrity constraint.
For example, if a newly inserted or modified tuple in a relation has null values for
any primary-key attribute, or if the tuple has the same value on the primary-key
attributes as does another tuple in the relation, SQL flags an error and prevents the
update. Similarly, an insertion of a course tuple with a dept_name value that does
not appear in the department relation would violate the foreign-key constraint on
course, and SQL prevents such an insertion from taking place.

A newly created relation is empty initially. We can use the insert command
to load data into the relation. For example, if we wish to insert the fact that there
is an instructor named Smith in the Biology department with instructor_id 10211
and a salary of $66,000, we write:

insert into instructor

values (10211, ’Smith’, ’Biology’, 66000);

The values are specified in the order in which the corresponding attributes are
listed in the relation schema. The insert command has a number of useful features,
and is covered in more detail later.

We can use the delete command to delete tuples from a relation. The command

delete from student;

would delete all tuples from the student relation. Other forms of the delete command allow specific tuples to be delete.

To remove a relation from an SQL database, we use the drop table command.
The drop table command deletes all information about the dropped relation from
the database. The command

drop table r;

is a more drastic action than

delete from r;

The latter retains relation r, but deletes all tuples in r. The former deletes not only
all tuples of r, but also the schema for r. After r is dropped, no tuples can be
inserted into r unless it is re-created with the create table command.

We use the alter table command to add attributes to an existing relation. All
tuples in the relation are assigned null as the value for the new attribute. The form
of the alter table command is

alter table r add A D;

where r is the name of an existing relation, A is the name of the attribute to be
added, and D is the type of the added attribute. We can drop attributes from a
relation by the command

alter table r drop A;

where r is the name of an existing relation, and A is the name of an attribute of the
relation. Many database systems do not support dropping of attributes, although
they will allow an entire table to be dropped.

Frequently Asked Questions