Data Definition Language

from Wikipedia, the free encyclopedia

The Data Definition Language ( DDL ; German  data definition language ) is a database language that is used to describe, change or remove data structures and related elements. DDL originally referred to database systems , but the term is now also used in other contexts. As a database language, DDL is the data description language of a database.

There are very different versions of the DDL (depending on the intended use), examples:

  • The data structures and their logical views are defined in the form of a higher-level assembly language in the historical IMS databases.
    Example: ...  SEGM NAME=PROJECT,PARENT=FIRM,BYTES=45 FIELD NAME=(PROJNO,SEQ,U),BYTES=6,START=1 ...
  • In SQL (in addition to DML and DCL ) it is available in the form of English command clauses.
    Example:  ...CREATE TABLE PROJECT ( PROJNO DECIMAL(6,0) NOT NULL PRIMARY KEY,
  • XML Schema is a DDL for describing the structure of XML documents.

Some software manufacturers also assign authorization elements (e.g. GRANT) to the DDL term, but in theory these belong to the Data Control Language .

A DDL must be differentiated from the term “ declaration ”: While a DDL is generally used to define the structure of data in a DBMS , the definition of the format and structure information for data to be processed internally in the main memory - in the source code of a computer program , according to the Syntax of a programming language  - referred to as a "declaration" (sometimes also as a definition or specification).

SQL

In the practically important Structured Query Language , the syntax is as follows: 

CREATE TABLE Relation ( (Attribut-Definition [PRIMARY KEY])+
    [, FOREIGN KEY ( Attribut+ ) REFERENCES Relation ( Attribut+ )] )
DROP TABLE Relation
ALTER TABLE Relation Alter-Definition
CREATE INDEX Index-Name ON Relation ( Attribut+ )
DROP INDEX Index-Name
CREATE VIEW Sicht [( Attribut+ )] AS SFW-Block [WITH CHECK OPTION]
DROP VIEW Sicht
  • PRIMARY KEYand FOREIGN KEYare part of the SQL-89 IDL or SQL-92 and are not supported by some database systems.
  • The attribute definition contains the name of the attribute, the data type and optional information such as NOT NULL. In SQL-92, user-defined value ranges and default values ​​can be specified.
  • In CREATE TABLEcan from SQL-92 also by means of the CHECKstill constraints on the attributes, or for the table are specified clause.
  • The age definition is . In SQL-92 there is still or . Since SQL-92 is very restrictive with regard to the statement, this is one of the statements that has been universally expanded by the manufacturers, so that any changes are possible, such as through a sequence of and statements.ADD Attribut-DefinitionALTER Attribut Default-WertDROP AttributALTERDROPADD
  • When defining a view, new attribute names can be assigned. SFW block is any SQL query that WITH CHECK OPTIONspecifies whether certain change operations should be permitted (see views ). A ORDER BYclause is not permitted in view definitions, since views are again relations, and relations are (multi-) sets, i.e. not sorted by definition.
  • The CREATEstatement is used in modern DBMS to create all kinds of other objects besides relations, indices and views.
  • The SQL standard does not define indexes at all, so the corresponding statements CREATE INDEXand DROP INDEXstatements are always product-specific extensions. However, most DBMS use the same or very similar syntax.
  • With WITH CHECK OPTIONa view (virtual table) can be defined in order to enable a control over the change of the data, which are displayed in a view and can also be edited. This specification specifies that changes to the view that affect the part of a relation that is not visible in it WHEREare recognized and rejected in a test using the parameters specified by.

Examples: 

CREATE TABLE Student (
   MatrNr INT NOT NULL PRIMARY KEY,
   Name varchar(50) NOT NULL)
Creates the table named Student with the columns MatrNr and Name , where MatrNr is the primary key and empty fields are not allowed in any of the columns .
ALTER TABLE Student ADD Vorname varchar(35)
Defines a new column named First Name in the Student table .
DROP TABLE Student
Clears the entire Student table .
CREATE INDEX idx_Name ON Student (Name)
Creates an index on the Name column of the Student table . The index is named idx_Name and speeds up the search for records in the Student table if the name is specified as a search criterion.
DROP index idx_Name
Deletes the index idx_Name .
CREATE VIEW alte_Freunde
AS SELECT Name, Vorname, Wohnort, Geburtstag
FROM Freunde
WHERE Geburtstag <= '1-JAN-1970'
WITH CHECK OPTION;
Only shows friends who were born before January 1st, 1970 and prevents changes to values ​​greater than January 1st, 1970 and the creation of a new data record with an invalid value.

See also

Individual evidence

  1. ↑ Data Definition Language , Geoinformatics Lexicon, Faculty of Agricultural and Environmental Sciences - University of Rostock