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

(A1 D2,
A2 D2,
An Dn,
{integrity-constraint 1},
{integrity-constraint k });

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

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

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;

SQL Data Definition�

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

Ans: IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. view more..
Ans: An object database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. Object-relational databases are a hybrid of both approaches. view more..
Ans: All procedural relational query languages provide a set of operations that can be applied to either a single relation or a pair of relations. view more..
Ans: The set of relations in a database must be specified to the system by means of a data-definition language (DDL). view more..
Ans: The basic structure of an SQL query consists of three clauses: select, from, and where. view more..
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
Ans: The natural join operation operates on two relations and produces a relation as the result. view more..
Ans: Reason to rename a relation is a case where we wish to compare tuples in the same relation. view more..
Ans: SQL permits a variety of functions on character strings. Read to know about them. view more..
Ans: The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.  A department controls a number of projects, each of which has a unique name, a unique number, and a single location view more..
Ans: Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) view more..
Ans: A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribut view more..
Ans: An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique view more..
Ans: There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute view more..
Ans: Select clause uses. view more..
Ans: Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types view more..
Ans: If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints view more..
Ans: The individual entity instances in an entity set and the individual relationship instances in a relationship set. In ER diagrams the emphasis is on representing the schemas rather than the instances. view more..

Rating - 4/5