Data Definition Language
Introduction
Data Definition Language (DDL) is a type of syntax used in SQL (Structured Query Language) that allows database administrators to define and manage the structure of a database. It provides commands for creating, altering, and deleting database objects such as tables, indices, and users. Unlike Data Manipulation Language (DML), DDL statements do not manipulate data within the database, but rather define how the data is structured.
History
The concept of DDL was first introduced in the 1970s with the advent of the relational database model. The relational model, proposed by Edgar F. Codd, was a significant departure from the hierarchical and network database models that preceded it. The model's simplicity and flexibility led to the development of SQL, a language designed specifically for interacting with relational databases. SQL included DDL commands as a means of defining the structure of the database.
DDL Commands
There are several key DDL commands used in SQL:
CREATE
The CREATE command is used to create a new database object. This could be a new database, table, index, or view. The syntax for creating a new table, for example, would be:
``` CREATE TABLE table_name (
column1 datatype, column2 datatype, ...
); ```
ALTER
The ALTER command is used to modify an existing database object. This could involve adding a new column to a table, changing the data type of a column, or renaming a table. The syntax for adding a new column to a table would be:
``` ALTER TABLE table_name ADD column_name datatype; ```
DROP
The DROP command is used to delete an existing database object. This could be a database, table, index, or view. The syntax for dropping a table would be:
``` DROP TABLE table_name; ```
TRUNCATE
The TRUNCATE command is used to delete all data from a table, but not the table itself. The syntax for truncating a table would be:
``` TRUNCATE TABLE table_name; ```
COMMENT
The COMMENT command is used to add a comment to a database object. This can be useful for providing additional information or context about the object. The syntax for adding a comment to a table would be:
``` COMMENT ON TABLE table_name IS 'comment_text'; ```
RENAME
The RENAME command is used to rename a database object. The syntax for renaming a table would be:
``` RENAME TABLE old_table_name TO new_table_name; ```
DDL vs DML
While DDL provides commands for defining the structure of a database, DML (Data Manipulation Language) provides commands for manipulating the data within the database. DML commands include SELECT, INSERT, UPDATE, and DELETE.
Unlike DDL commands, DML commands do not immediately commit changes to the database. Instead, the changes are held in a temporary area until the transaction is committed. This allows for multiple DML operations to be performed as part of a single transaction, and for the transaction to be rolled back if necessary.
DDL in Database Design
DDL plays a crucial role in database design. The structure of a database, including the tables and their relationships, is defined using DDL commands. This structure, also known as the schema, determines how data is stored and accessed in the database.
In the early stages of database design, a conceptual data model is created. This model represents the high-level structure of the data, independent of any specific database management system (DBMS). The conceptual model is then translated into a logical model, which includes more detail and is specific to the relational database model. Finally, the logical model is translated into a physical model, which includes the specific DDL commands needed to create the database structure in a particular DBMS.