Data definition language

Data definition language

Data definition language – The SQL DDL allows the specification of not only a relations, but also information about each relation, including
  • 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 three important commands of DDL are CREATE, DROP, ALTER commands.

The domain of SQL

The SQL command supports a verity of built-in domain types, including the following
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.
INT is an integer (a finite subset of the integers that is machine-dependent). The full from INTEGER is equivalent.
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.
DOUBLE  PRECISION are the floating-point and double-precision floating-point numbers, with machine dependent precision.
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

A table (schema)is created via the CREATE TABLE command, which can include all the tale element definitions.
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,
cust-street         char(30),
cust-city               char(30),
primary key (cust-name))

create table branch
( br_name char (15)  not null,
br_city char (30),
assets      integer,
Primary key (br_name),
Check (assets >= 0))
Create table account 
(account – no char(10) not  null,
br-name  char(15),
balance     integer,
primary  key (account-no),
check (balance >=0))
create table depositor 
(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

 Here r is the name of the elation in the database and A is the attribute and D is the domain type associated with the attribute.

Leave a Reply

Your email address will not be published.