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;

CREATE DATABASE University;

• 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.

CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));

• 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.

CREATE TABLE / ALTER TABLE - Solved Exercises

How to create a table? / How to alter the structure of a table? / How to add constraints? / How to remove constraints? / What is table definition? / How to add a column to an existing table?

Create Table / Alter Table Exercises

Question:

Consider an application which is being developed for a ‘Used Car seller’. For storing various details related to the cars and owners, create the tables according to the schemas given below;

CAR(Car_Reg_No, Brand, Model, Variant, Model_Year, Color, Year_Of_Purchase, Kilometers)

OWNER(Owner_ID, Owner_Name, Owner_Phone, License_Number)

OWNER_CAR(Owner_ID, Car_Reg_No, Price)

I - Consider the following as important components while creating the tables;

a. The columns that are underlined are Primary keys

b. All columns must contain some values.

c. Choose the appropriate data types which would match the most for all the attributes.

d. Use constraint names for all the constraints you create.

e. For table CAR;

          i. Brand should be one of { ‘Maruti’, ‘Ford’, ‘Hyundai’ }

          ii. Color should be one of { ‘Black’, ‘White’, ‘Red’, ‘Blue’ }

          iii. Kilometers should be less than 50000.

f. For table OWNER;

          i. Owner_ID should start with ‘OID’

          ii. License_Number should be unique value.

g. For table OWNER_CAR;

          i. Price should be greater than Rs.150000.

II - After table creation, write the queries to alter the tables according to the requirements given below;

a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table.

b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table.

c. Add an attribute Owner_Address in OWNER table.

d. Add an attribute Test_Status of the car which accepts character based values.

e. Remove the column Year_Of_Purchase from CAR.

f. Remove the column Owner_Address from OWNER table.

g. Remove the Unique constraint of License_Number attribute.

h. Remove the color constraint so that any colored cars can be inserted.

i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner.

j. Remove the table OWNER_CAR from the database completely.

k. Change the type of License_Number attribute from character based type to number type.

Answers:

The queries are written in SQL as used in Oracle RDBMS

I – Table Creation:

I have listed the requirements for creating tables as specified in the question in the table given below; Type column in the following table lists the appropriate types for every attribute and Constraints column list the conditions to be satisfied while creating a table.

Requirement specification for CAR;

Attributes	Type	Constraints
Car_Reg_No	CHAR (Because the length of the registration number will not be in varying lengths. They are fixed length values)	·         PRIMARY KEY, ·         NOT NULL
Brand	VARCHAR (Brand name can be of varying length. Hence, it is advisable to use VARCHAR or VARCHAR 2)	·         CHECK (The permitted values are ‘Maruti’, ‘Ford’, ‘Hyundai’ only) ·         NOT NULL
Model	VARCHAR (Model name is of varying length)	·         NOT NULL
Variant	VARCHAR (Variant is like ‘ZXi’, ‘LDi’ etc. But it could be of different length for different brand cars. Hence, VARCHAR would be used)	·         NOT NULL
Model_Year	NUMBER (Stores only year value)	·         NOT NULL
Color	VARCHAR	·         CHECK (The permitted values for color are ‘Black’, ‘White’, ‘Red’, ‘Blue’ only) ·         NOT NULL
Year_Of_Purchase	NUMBER	·         NOT NULL
Kilometers	NUMBER	·         CHECK (The value should be less than 50000 kilometers) ·         NOT NULL

Query:

(Convention used - All the words given in ALL CAPS are keywords. All the words represented in RED color are either table names/attribute names. All the words represented in GREEN color are constraint names.)

CREATE TABLE Car

(Car_Reg_No CHAR(12) NOT NULL,

Brand VARCHAR(10) NOT NULL,

Model VARCHAR(10) NOT NULL,

Variant VARCHAR(5) NOT NULL,

Model_Year NUMBER(4) NOT NULL,

Color VARCHAR(10) NOT NULL,

Year_Of_Purchase NUMBER(4) NOT NULL,

Kilometers NUMBER(5) NOT NULL,

CONSTRAINT Car_Reg_No_PK PRIMARY KEY (Car_Reg_No),

CONSTRAINT Brand_CK CHECK (Brand IN (‘Maruti’, ‘Ford’, ‘Hyundai’)),

CONSTRAINT Color_CK CHECK (Color IN (‘Black’, ‘White’, ‘Red’, ‘Blue’)),

CONSTRAINT Km_CK CHECK (Kilometers < 50000));

Requirement specification for OWNER;

Attributes	Type	Constraints
Owner_ID	CHAR	·         PRIMARY KEY, ·         CHECK (every owner id should start with the letters ‘OID’) ·         NOT NULL
Owner_Name	VARCHAR	·         NOT NULL
Owner_Phone	NUMBER	·         NOT NULL
License_Number	CHAR	·         UNIQUE (We accept unique values only. Duplicate values should be denied) ·         NOT NULL

Query:

CREATE TABLE Owner

(Owner_ID CHAR(10) NOT NULL,

Owner_Name VARCHAR(30) NOT NULL,

Owner_Phone NUMBER(10) NOT NULL,

License_Number CHAR(10) NOT NULL,

CONSTRAINT Owner_ID_PK PRIMARY KEY (Owner_ID),

CONSTRAINT Owner_ID_CK CHECK (Owner_ID LIKE ‘OID%’),

CONSTRAINT LN_UQ UNIQUE (License_Number));

Requirement specification for OWNER_CAR;

Attributes	Type	Constraints
Owner_ID	CHAR	·         PRIMARY KEY, ·         NOT NULL
Car_Reg_No	CHAR	·         PRIMARY KEY ·         NOT NULL
Price	NUMBER	·         CHECK (Car price should be more than 150000) ·         NOT NULL

Query:

CREATE TABLE Owner_Car

(Owner_ID CHAR(10) NOT NULL,

Car_Reg_No CHAR(12) NOT NULL,

Price NUMBER(8) NOT NULL,

CONSTRAINT Owner_Car_PK PRIMARY KEY (Owner_ID, Car_Reg_No),

CONSTRAINT Price_CK CHECK (Price > 150000));

II – Table Alteration:

As per the given specification of section I, the tables are created. Let us modify the tables as per the instruction in section II.

a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table.

A FOREIGN KEY can be added using the following syntax;

“FOREIGN KEY (attribute_name_in_ForeignKey_Table) REFERENCES Target_Table_Name(attribute_referred_in_Target_table)”

Query II a:

ALTER TABLE Owner_Car ADD CONSTRAINT Owner_FK FOREIGN KEY (Owner_ID) REFERENCES Owner(Owner_ID);

b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table.

Query II b:

ALTER TABLE Car ADD CONSTRAINT Car_FK FOREIGN KEY (Car_Reg_No) REFERENCES CAR(Car_Reg_No);

c. Add an attribute Owner_Address in OWNER table.

Query II c:

ALTER TABLE Owner ADD Owner_Address VARCHAR(40) NOT NULL;

d. Add an attribute Test_Status of the car which accepts character based values.

Query II d:

ALTER TABLE Car ADD Test_Status VARCHAR(10) NOT NULL;

e. Remove the column Year_Of_Purchase from CAR.

The syntax to remove a column from an existing table is “DROP COLUMN column_name”

Query II e:

ALTER TABLE Car DROP COLUMN Year_Of_Purchase;

f. Remove the column Owner_Address from OWNER table.

Query II f:

ALTER TABLE Owner DROP COLUMN Owner_Address;

g. Remove the Unique constraint of License_Number attribute.

Removing constraints can be done using the syntax “DROP CONSTRAINT constraint_name”

Query II g:

ALTER TABLE Owner DROP CONSTRAINT LN_UQ;

[Recall from the table definition of Owner, LN_UQ is the name of the UNIQUE constraint]

h. Remove the color constraint so that any colored cars can be inserted.

Query II h:

ALTER TABLE Car DROP CONSTRAINT Color_CK;

i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner.

Query II i:

ALTER TABLE Owner_Car ADD Years_Used NUMBER(2);

j. Remove the table OWNER_CAR from the database completely.

The syntax for removing a table from the database permanently is “DROP TABLE table_name”

Query II j:

DROP TABLE Owner_Car;

k. Change the type of License_Number attribute from character based type to number type.

The syntax for changing the data type or size is “MODIFY COLUMN column_name datatype”

Query II k:

ALTER TABLE Owner MODIFY COLUMN License_Number NUMBER(10);

[Note: If you would like to change the data type of size of any attribute, then you should delete all the existing records before alteration. This can be done in two ways;

DELETE FROM table_name;

TRUNCATE TABLE table_name;]