DDL (CREATE, ALTER, DROP) in DBMS Theory - Full Explanation

Introduction

When working with databases, you often need to build or change the structure where data is stored. This involves creating new tables, changing existing ones, or removing them entirely. Managing these changes clearly and safely is essential for keeping data organized and accessible.

Explanation

CREATE Statement

The CREATE command is used to make new database objects like tables, indexes, or views. When you create a table, you define its columns and the type of data each column will hold. This sets up the structure where data will be stored.

CREATE builds new structures in the database to hold data.

ALTER Statement

ALTER lets you change the structure of an existing database object without deleting it. For example, you can add or remove columns from a table, or change a column's data type. This helps adapt the database as needs evolve without losing stored data.

ALTER modifies existing database structures safely.

DROP Statement

DROP removes database objects completely, such as tables or indexes. When you drop a table, all its data and structure are deleted permanently. This is useful when you no longer need certain data or want to clean up the database.

DROP deletes database objects and their data permanently.

Real World Analogy

Imagine managing a library. Creating a new shelf is like CREATE, adding or removing sections on a shelf is like ALTER, and removing a whole shelf is like DROP. Each action changes how books are stored and organized.

CREATE Statement → Building a new bookshelf to hold books

ALTER Statement → Changing the sections on an existing bookshelf to fit different books

DROP Statement → Removing an entire bookshelf and all its books

Diagram

Diagram

┌─────────────┐      ┌─────────────┐      ┌─────────────┐
│   CREATE    │─────▶│   ALTER     │─────▶│    DROP     │
│ (Make new)  │      │ (Change)    │      │ (Remove)    │
└─────────────┘      └─────────────┘      └─────────────┘

This diagram shows the flow of creating, altering, and dropping database objects.

Key Facts

CREATE → Command to make new database objects like tables or indexes.

ALTER → Command to change the structure of existing database objects.

DROP → Command to delete database objects and their data permanently.

Table → A database object that stores data in rows and columns.

Column → A named data field in a table that holds a specific type of data.

Code Example

DBMS Theory

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT
);

ALTER TABLE Employees ADD Email VARCHAR(100);

DROP TABLE Employees;

Common Confusions

ALTER deletes data when changing a table.

ALTER deletes data when changing a table. ALTER changes the structure without deleting existing data unless explicitly dropping columns.

DROP can be undone easily.

DROP can be undone easily. DROP permanently removes objects and data; recovery requires backups.

CREATE can add columns to an existing table.

CREATE can add columns to an existing table. CREATE only makes new objects; ALTER is used to add columns to existing tables.

Summary

CREATE builds new database structures to store data.

ALTER changes existing structures without deleting data.

DROP removes database objects and their data permanently.