- The schema for each relation
- The domain of value 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 the disk
The domain of SQL
CHAR(n) is a fixed-length string, with user-specified length n. The full form CHARACTER, can be used instead.
VARCHAR(n) is a variable-length character-string, with user-specified maximum length n.
SMALLINT is a small integer.
NUMERIC (p,d) is a fixed-point number, with user-specified precision. The number consists of p digits9plus a sign 0 and d of the p digits are to the right of the decimal point. Thus numeric (3,1) allows us to store 66.6 exactly, but neither 666.6 nor 0.66.
FLOAT(n) is floating-point number, with user specified precision of at least n digits.
DATE is a calendar date, containing a (four digit) year, month, and day of the month.
Time is the time of the day in hours, minutes, and seconds.
Create DDL Command
We define an SQL relation using create table command:
Create table r (A1, D1, A2, D2,……An, Dn, <integrity constraint 1>,< integrity constraint 2>)
Where r is the name of the relation, each A, is the name of the attribute in the schema of the relation r, and D1 is the domain type of values in the domain of the attribute A1. The allowed integrity constraints include PRIMARY KEY and CHECK (p).
The primary key specification says that attributes Ai1 , Ai2 from the primary key for the relation. Although the primary key specification is optional, it is generally a good idea to specify a primary key for each relation. The CHECK clause specifies a predicate p that must be satisfied by every tuple in the relation.
Create table customer
(cust_name char(20) not null,
primary key (cust-name))
( br_name char (15) not null,
br_city char (30),
Primary key (br_name),
Check (assets >= 0))
(account – no char(10) not null,
primary key (account-no),
check (balance >=0))
(cust-name char(20) not null,
Account-no char(10) not null,
Primary key (cust_name, account_no)).
The drop command in DDL
To remove a relation from a database we use the DROP TABLE command. This command deletes all information about the dropped relation from the database.
Drop table r
Where r is the relation to be removed from the table
Drop table customer
This would drop the customer relation from the database
The alter table DDL command
We use this command to add attributes to an existing relation. All tuples in this relation are assigned NULL values for the attribute. This command is also used along with MODIFY clause to alter the domain type of the existing attributes in the relation. The form of ALTER command is as follows.
Alter table r add A D