Data Definition Language (DDL)

This article will discuss about Data Definition Language (DDL). In the previous article entitled Introductionon a Database Table has been presented about the table in the database, where the table is a major component in a database. The fundamental feature of a table is to have rows and columns. The tables themselves can be manipulated (add, modify, delete) and using certain techniques can generate useful information needed by the user.

Actually the table is one of the objects that exist in the database. Objects in the database itself is a component / part of the database that has a special function in which the component can be managed (create, modify, delete). Other objects that exist in the database other than tables include user, view, procedure, function and trigger. Each database may have different objects, but not for table objects where it can be said that all databases have the same table form. In this article, the author will refer to the objects that exist in the Oracle database. Maybe the question arises why should the Oracle database? The answer is simple, because the author is more familiar with the Oracle database (smile).

Okay .. let's continue the discussion about DDL, but let's touch on a bit about SQL. How to create tables in the database must use a particular technique that is by using SQL (Structured Query Language). SQL itself is simply a standard language for managing databases, especially relational databases. What is a relational database? The author will gives a short answer, relational database is a database consisting of several related tables. The related table means that there are 2 tables where one has the primary key and the pair table has the foreign key, what is the primary key and the foreign key? The discussion of primary key and foreign key will be discussed in another article about ERD (Entity Relationship Diagram).

Okay .. now we continue to discuss about DDL. The management of objects in Oracle databases is done by using the DDL (Data Definition Language) query command. The objects in the Oracle database include user, table, view, package, procedure and function. SQL (Structured Query Language) is a standard DBMS (Database Management System) command on a database to interact with objects inside it. The management of database objects is done by using one of the following 3 SQL commands:

CREATE; Used to create a new object.

ALTER; Used to make changes or modifications to objects that have been created previously.

DROP; Used to delete an existing object.

Implementation of DDL on Table

The table is the object of a database consisting of rows and columns. The rows in the table are known as record / row while the columns in the table are known as field/column/attribute. Here is an example of using DDL to create a table named TStudent that has 3 attributes (sid, name, phone) in the Oracle database:

First login to database using sqlplus command.

SQLPLUS system / yourpassword

è system is the username used to login.

è yourpassword is the password specified during the installation of the Oracle database.

Next create a table with the name TStudent which has 3 attributes (sid, name, phone). The TStudent table has 2 constraints, NOT NULL and PRIMARY KEY. A NOT NULL constraint is given in the sid attribute, meaning that the sid attribute must be loaded a value when the new row data is added. The PRIMARY KEY constraint is given in the sid attribute, meaning the sid attribute is a distinguishing attribute in the TStudent table where there should not be the same or repeated contents of the data entered in the sid attribute of the TStudent table. The discussion on constraint will be discussed in the article about ERD (Entity Relationship Diagram). Here is the DDL command to create a TStudent table:

CREATE TABLE TStudent (

Sid NUMBER NOT NULL,

Name VARCHAR (64),

Phone VARCHAR (16),

CONSTRAINT pk_student PRIMARY KEY (sid)

);

If successful will appear Table Created message.

Check the existence of TStudent table by typing command:

DESCRIBE TStudent

 If a table structure that contains the names of the attributes and data types means that the TStudent table has been successfully created. Describe is a command to display the structure of a table that is in the Oracle database (DESCRIBE command can be short with DESC).

The SID attribute of the TStudent table we have created has a data type NUMBER, meaning that only numbers can be entered on the SID attribute. If we fill in the combination of letters and numbers it will get an error message. To handle so that the SID attribute can accept the input value of the combination of attributes and numbers it is necessary to make changes to the SID data type. Here's how to make changes to data types on all SID attributes NUMBER to VARCHAR:

ALTER TABLE TStudent MODIFY sid VARCHAR2 (16);

The command will change the data type of the SID attribute to VARCHAR2 (16). The meaning of the attribute with the data type VARCHAR2 (16) is that the attribute can accept the input value of a combination of numbers and letters, but the number of characters entered can not be more than 16 characters.

Check the last structure of the TStudent table by using the DESCRIBE command.

DESCRIBE TStudent

Well now we have managed to create a table with the name TStudent which has 3 attributes (sid, name, phone). If we intend to delete the table, the DDL command we use is DROP.

DROP TABLE TStudent;

The command will delete the TStudent table. Please be careful using this DROP command because if the table already has data, all data in the table will be erased.

Okay.. that’s all about introduction to DDL. In this article we explained about create a table with the CREATE command, change the table attribute with the ALTER command and delete the table with the DROP command. For the implementation of DDL on other database objects will try the author review in another next article.

Thank You, Bobsis.