Advanced Database Management System - Tutorials and Notes: Data Definition Language (DDL)

Monday, 5 January 2015

Data Definition Language (DDL)

What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements

Data Definition Language (DDL)

As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;

  • To create databases.

For example we can create a database named University in MySQL as follows;

  • To create table structures.

For example, we can create a table named Student in Oracle as follows;
CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));

  • To change the structure of the tables.

We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;
ALTER TABLE student ADD COLUMN Phone Number(10);

  • To remove tables.

We can delete the whole table structure Student using the following statement in Oracle;
DROP TABLE student;

  • To rename tables.

To rename an existing table, we can use the following query in Oracle;
RENAME student TO student_table;

  • To define referential integrities.

We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.

  • To analyze information.

We can analyze a table, index or clusters for handling performance related issues.

  • To add comments to the Data Dictionary.

We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.
COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;

When we execute DDL statements, it does the following things;

1. It does the required things as specified through query. [For example, as mentioned above]

2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.

No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents