Simple Table Declarations

Simple Table Declarations

The simplest form of declaration of a relation schema comprises the keywords CREATE TABLE followed by the name of the relation and a parenthesized list of the attribute names and their types.

Example 1 : The relation schema for our example MovieStar relation, which was explained informally in "Relational Algebra", is expressed in SQL as in Figure 1. The first two attributes, name and address, have each been declared to be character strings.  However, with the name, we have made the decision to use a fixed-length string of 30 characters, padding a name out with blanks at the end if necessary and truncating a name to 30 characters if it is longer. On the contrary, we have declared addresses to be variable-length character strings of up to 255 characters. It is not clear that these two choices are the best possible, but we use them to show two kinds of string data types.

The gender attribute has values that are a single letter, M or F. In this way, we can safely use a single character as the type of this attribute. Lastly, the birthdate attribute naturally deserves the data type DATE. If this type were not available in a system that did not conform to the SQL standard, we could use CHAR(10) instead, since all DATE values are actually strings of 10 characters: eight digits and two hyphens.

Declaring the relation schema for the MovieStar relation

Modifying Relation Schemas

We can delete a relation R by the SQL statement:


Relation R is no longer part of the database schema, and we can no longer access any of its tuples.

More commonly than we would drop a relation that is part of a long-lived database, we may need to modify the schema of an existing relation. These modifications are done by a statement that begins with the keywords ALTER TABLE and the name of the relation. We then have many options, the most important of which are

1. ADD followed by a column name and its data type.
2. DROP followed by a column name.

Example 2 : In this way, for example, we could modify the MovieStar relation by adding an attribute phone with

ALTER TABLE MovieStar ADD phone CHAR(16);

As a result, the MovieStar schema now has five attributes: the four mentioned in Figure 1 and the attribute phone, which is a fixed- length string of 16 bytes. In the actual relation, tuples would all have components for phone, but we know of no phone numbers to put there. In this way, the value of each of these components would be NULL. In "Default Values", we shall see how it is possible to choose another "default" value to be used instead of NULL for unknown values.

As another instance, we could delete the birthdate attribute by

ALTER TABLE MovieStar DROP birthdate;