0861347ccd0a85e2640cb0ccf756e70b_1302996.docx - Goiit.com

shrubberystatuesqueData Management

Dec 1, 2012 (4 years and 9 months ago)

192 views

A Data Definition Language (DDL) is a computer language for defining data structures. The term was first
introduced in relation to the Codasyl

database model, where the schema of the database was written in
a Data Definition Language describing the records, fields, and "sets" making up the user Data Model.
Initially it referred to a subset of SQL, but is now used in a generic sense to refer to a
ny formal language
for describing data or information structures, like XML schemas.



* 1 SQL


o 1.1 CREATE statements


+ 1.1.1 CREATE TABLE


o 1.2 DROP statements


o 1.3 ALTER statements


o 1.4 Referen
tial integrity statements


* 2 XML Schema


* 3 DDL Tools and Related Applications


o 3.1 Apache DdlUtils




SQL


A subset of SQL's instructions form another 'DDL'. These SQL statements define the structure of a
database, including rows, c
olumns, tables, indexes, and database specifics such as file locations. DDL SQL
statements are more part of the DBMS and have large differences between the SQL variations. DDL SQL
commands include the following:


CREATE statements


Create
-

To make a new d
atabase, table, index, or stored query. A CREATE statement in SQL creates an
object inside of a relational database management system (RDBMS). The types of objects that can be
created depends on which RDBMS is being used, but most support the creation of t
ables, indexes, users,
and databases. Some systems (such as PostgreSQL) allow CREATE, and other DDL commands, inside of a
transaction and thus they may be rolled back.


CREATE TABLE


Perhaps the most common CREATE command is the CREATE TABLE command. The t
ypical usage is:


CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].


Column Definitions: A comma
-
separated list consisting of any of the following



* Column definition: [column name] [data type] {NULL | NOT NULL} {column options}


* Primary key definition: PRIMARY KEY ( [comma separated column list] )


* CONSTRAINTS: {CONSTRAINT} [constraint definition]


* RDBMS specific functionality


Fo
r example, the command to create a table named employees with a few sample columns would be:


CREATE TABLE employees (


id INTEGER PRIMARY KEY,


first_name CHAR(50) NULL,


last_name CHAR(75) NOT NULL,


dateofbirth DAT
E NULL

);


DROP statements


Drop
-

To destroy an existing database, table, index, or view.


A DROP statement in SQL removes an object from a relational database management system (RDBMS).
The types of objects that can be dropped depends on which RDBMS

is being used, but most support the
dropping of tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL
commands to occur inside of a transaction and thus be rolled back.


The typical usage is simply DROP objecttype object
name. For example, the command to drop a table
named employees would be:


DROP TABLE employees;


The DROP statement is distinct from the DELETE and (non
-
standard) TRUNCATE statements, in that they
do not remove the table itself. For example, a DELETE state
ment might delete some (or all) data from a
table while leaving the table itself in the database, whereas a DROP statement would remove the entire
table from the database.


ALTER statements


Alter
-

To modify an existing database object.


An ALTER statement in SQL changes the properties of an object inside of a relational database
management system (RDBMS). The types of objects that can be altered depends on which RDBMS is
being used.


The typical usage is ALTER objecttype objectname parame
ters. For example, the command to add (then
remove) a column named bubbles for an existing table named sink would be:


ALTER TABLE sink ADD bubbles INTEGER;

ALTER TABLE sink DROP COLUMN bubbles;


Referential integrity statements


Finally, other kind of DDL

sentence in SQL are the statements to define referential integrity
relationships, usually implemented as primary keys and foreign keys tags in some columns of the tables.


These two statements can be included inside a CREATE TABLE or an ALTER TABLE senten
ce.

XML Schema


XML Schema is an example of a pure DDL (although only relevant in the context of XML).


DDL Tools and Related Applications

Apache DdlUtils


Apache DdlUtils is a small, easy
-
to
-
use component for working with Database Definition (DDL) files.
These are XML files that contain the definition of a database schema, e.g. tables and columns. These
files can be fed into DdlUtils via its Ant task or programmatically in order to create the corresponding
database or alter it so that it corresponds to the

DDL. Likewise, DdlUtils can generate a DDL file for an
existing database.