Data Definition Language (DDL) in Database

Data Definition Language (DDL) is a group of commands used to define database or table. Some basic commands of DDL are :

1. CREATE : this command is used to create database, table, view, or index.
2. ALTER : this command is used change table structure.
3. DROP : this command is used to delete database, table, view or index.

A. Database

Command to create database :

CREATE DATABASE databasename;

Create database must be done the first time before we can next create table, view, or any others component in database system. Database name can consist of a combination of letters, digit, and characters, but should not space character or punctuation marks in order to ease the database use or setting in the future. Example
of creating database given here :

CREATE DATABASE academic;

 Meanwhile, the command to delete database :

DROP DATABASE databasename;

 Example :

DROP DATABASE academic;

To be able to manage a database, the user must first enter into a database environment that will be processed. To be able to process a database syntax used are:

USE nama_database;

B. Tabel

Common syntax to create table of a database is,

CREATE TABLE tablename (Field1 DataType1(length)[column definition (like primary key or not null if needed)], Field2 DataType 2 (length) [ (like primary key or not null if needed)],Field3....);

Or more detail :

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name (create_definition,...) [table_option ...][partition options] 

Field1 is the name of the first column (attribute one), field2 is the name of the second column (attribute two), and so on as many as the number of column exist in a database table. DataType1, DataType2 is used to define the type of the appropriate column. Whereas, length is maximum digit allocated for the appropriate column/type. Some data type optionally ask for data type length to declare and when it is not declared, system will automatically set it.

Next is query example to create a simple table.

create table lecturer(id varchar(20) primary key not null, name varchar(50), sex varchar(25),position varchar(50), major varchar(50), telephon varchar(20));

Some data types that is supported by MySQL:

Data type	Declaration	Information
Char	char(length)	Column of char type could be filled with text data. Maximum data length is 255 character.
Varchar	varchar(length)	Column of this type could be filled with text with maximum length is like in the declaration.
Tinytext	tinytext	Like varchar
Text	text	Column of this type is filled with text data with character length longer than previous type.
Integer	Int(length) [unsigned]	Data will be in range of digit 0 - 4294967295 if unsigned or -2147483648 - 2147483647 if signed. Maximum length will be like in the declaration.
Tinyint	Tinyint(length) [unsigned]	Data will be in range of digit 0 - 255 if unsigned or -128 - 127 if signed. Maximum length will be like in the declaration.
Mediumint	mediumint(length) [unsigned]	Data will be in range of digit 0 - 1677215 if unsigned or -8388608 - 8388607 if signed. Maximum length will be like in the declaration.
Bigint	bigint(length) [unsigned]	Data will be in range of digit 0 - 10^2 .
Float	float(length)	Data will be in real format digit with precision up to 38 scale. Maximum length is 53.
Double	double(length)	Data will be in real format digit with precision up to 38 scale.
Date	date	Data is saved in format of (year - month - day).
Datetime	datetime	Data is saved in format of (year - month - day - hour - minute - second).
Timestamp	Timestamp (length)	Data saved in date and time format with the declaration length.
Time	time	Data saved in format of (hour minute second)

Foreign Key

There are some ways to declare foreign key column when creating a table which has foreign key column. Below given some examples to create foreign key column in table creation.

create table subject(subject_id varchar(10) primary key not null, name varchar(50), credit int, lecturer varchar(20) references lecturer(id), days vachar(10), lecture_time time, room varchar(50));

The table creation above will influence data insertion. Everytime we insert record value into the table, system will automatically check whether the id inserted into table subject is exist in table lecturer. The syntax above can be replaced with syntax below.

create table subject(subject_id varchar(10) primary key not null, name varchar(50), credit int, lecturer varchar(20) references lecturer, days vachar(10), lecture_time time, room varchar(50));

Without explicitly declare the referenced column name of table lecturer (id), system will automatically direct the foreign key reference into the column with primary key identifier in the referenced table (lecturer). The syntax above can be done only with condition that there is only one column identified as primary key in the referenced table. If foreign key reference involve more than one column on the same referenced
table, then the syntax became :

create table example (a integer, b integer, c integer, foreign key (a,c) references table2(x,y));

 To create table consist of foreign key columns that reference to more than one different table, we can use syntax :

create table subject(subject_id varchar(10) primary key not null, name varchar(50), credit int, lecturer varchar(20) references lecturer(id), days vachar(10), lecture_time time, room varchar(room_id));

Usually, when we create column with foreign key definition, we can include some actions that can influence the data condition in the referenced or referencing table in the future. Some actions supported by MySQL are :

1. On Delete Restrict: Data in the referenced column cannot be deleted.
2. On Delete No Action: Same like On Delete Restrict.
3. On Delete Cascade: If data in referenced tabel is deleted then data in the referencing table will be leted too.
4. On Delete Set Null: If data in referenced tabel is deleted then data in the referencing table will be null.
5. On Update Restrict: Data in the referenced column cannot be updated. 
6. On Update Cascade: If data in referenced tabel is updated then data in the referencing table will be updated too.
7. On Update Set Null: If data in referenced tabel is updated then data in the referencing table will be null.

See Also : Data Manipulation Language

Syntax to change table name is

RENAME TABLE old_table_name to new_table_name;

 Below is syntax to delete a table of a database:

DROP TABLE table_name;

Syntax used to show all tables exist in a table is

SHOW TABLES database_name;

Below is syntax to show structure of a table of a database,

DESCRIBE table_name;

ALTER TABEL

ALTER is used to change/modify table structure. Some alter example are adding column, modifying column name, etc. Common syntax for alter table is

ALTER TABLE tablename kind_of_alter condition;

To add column in a table, we can use syntax.

ALTER TABLE tablename add columnname type AFTER/BEFORE exist_column_name; 

Example,

ALTER TABLE student add age int AFTER/BEFORE birth_date; 

The syntax to change the name of existing column is,

ALTER TABLE tablename change old_column_name new_column_name new_column_data_type(length); 

Example,

ALTER TABLE student change stu_name name varchar(50); 

To delete column in a table, we can use syntax

ALTER TABLE tablename DROP columnname;

Example,

ALTER TABLE student DROP age;

To modify exist column of a table, we can use syntax,

 ALTER TABLE tablename MODIFY COLUMN columnname column_definiton;

Below given an example to change condition of a column/field. In the case, column birth_date type will be change to varchar with length 10.

ALTER TABLE student MODIFY COLUMN birth_date varchar(10);

To change an exist column definition to became primary key, we can use syntax,

ALTER TABLE tablename ADD PRIMARY KEY(column_name);

Example to change an exist column definition to became primary key,

ALTER TABLE subject ADD PRIMARY KEY(subject_id);

The syntax to delete primary key identifier in a table is

ALTER TABLE tablename DROP PRIMARY KEY;

Below is example to delete primary key identifier in a table,

ALTER TABLE subject DROP PRIMARY KEY;

C. Index

Index is object scheme used to increase the speed of system when searching and selecting record data, by using pointer. Index creation is based on column. Index can be created either on table creation or in exist table. To create index on table creation, the syntax used is :

CREATE TABLE tablename(column type definition, column type definition,…,INDEX(column));

To create index in exist table, we can use syntax :

CREATE INDEX index_name ON table_name(column_name);

To drop index, use syntax:

DROP INDEX index_name;

EXERCISE

1. Create academic database then create table student (with attributes: NIM, name, sex, place of birth, date of birth, telephone and counselors), lecturer (NIP consists of attributes, name, gender, occupation, interests, and telephone ) subjects (consisting of attributes course code, course name, credits, lecturer, day, hour, and code space), KRS (consisting of the id attribute KRS, course code, NIM, year, semester and value). Every fieled defined as primary key must be defined as auto_increment and not null.
2. Show all tables in academic database.
3. Show each table structure in academic database.
4. Create query to do these case:
   1)Delete (drop) primary key definition on “NIP”.
   2) Add primary key defenition back to “NIP”.
   3) Show all columns in table “Lecturer”.
   4) Change table “Lecturer” name to “Lecturer_Table”.
   5) Change attribute “Name” name to “Lecturer_Name”.
   6) Change data type field “Sex” become enum {'Male', 'Female'}.
   7) Change data type field “Phone” become int.