Revise Smart

Data definition and manipulation languages

Posted in Computing, Databases

Database layouts can be modelled using a specialised programming language called a Data Definition Language. This language defines the structure of a database and can include attributes such as fields, keys, data types, validations and default values.

Examples

A database containing information about locations may have a table called cities and could be created with the following DDL:


CREATE TABLE cities
(ID             INT NOT NULL PRIMARY KEY,
 Name           VARCHAR(200) NOT NULL,
 Population     INT,
 County         VARCHAR(100))

The left hand side of each line gives the name of the field and the right side gives its attributes. The right specifices the datatype, with a length if needed in brackets, whether it is optional or not (NOT NULL) and whether it is a primary key. The above example may be altered to contain a foreign key called 'CountryID' of a database called 'Countries':


CREATE TABLE cities
(ID             INT NOT NULL PRIMARY KEY,
 Name           VARCHAR(200) NOT NULL,
 Population     INT,
 County         VARCHAR(100),
 CountryID      INT NOT NULL,
 FOREIGN KEY(CountryID) REFERENCES Countries(CountryID))

Here the first new statement adds the CountryID field the second statement says that the field CountryID in this database is a foreign key which correpsonds with a key named CountryID in the Countries database

Data manipulation language

A data manipulation language is used to manipulate the data in a database opposed to a data definition language which is used to define the layout of a database.