8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

The standard language used for both DDL and DML is SQL, which stands for Structured Query Language.

SQL has been used for many years and is accepted as the normal language for relational databases. Different database programs may have small differences in the way they write some commands, but the main ideas stay the same.

For your syllabus, you need to understand and write the SQL forms listed in the learning objectives.

Data Definition Language (DDL) is used to create, modify, and remove the data structures that make up a database.

This means DDL is used when you want to:

• create a new database
• create a new table
• define fields in a table
• choose the correct data type for each field
• change a table definition
• add primary keys
• add foreign keys

DDL does not deal with the actual records stored in the table. It deals with the structure only.

The CREATE DATABASE command is used to create a new database.

Example:

CREATE DATABASE School

This creates a new database called School.

You usually do this before creating any tables, because the tables need to be stored inside a database.

The CREATE TABLE command is used to create a table definition.

A table definition includes:

• the table name
• the field names
• the data type for each field

Example:

CREATE TABLE Student(
 StudentID CHARACTER,
 FirstName CHARACTER,
 SecondName CHARACTER,
 DateOfBirth DATE,
 ClassID CHARACTER
);

This command creates a table called Student.

It also creates five fields:

• StudentID
• FirstName
• SecondName
• DateOfBirth
• ClassID

Each field is given a data type so that the DBMS knows what kind of data can be stored there.

A data type tells the DBMS what kind of value a field can hold. Choosing the correct data type is important because it helps the DBMS store and check data properly.

CHARACTER is used for fixed-length text.

This means the field is designed to store text of a set size. In simple terms, it is useful when all values are expected to be about the same length, such as codes or short IDs.

Example:

StudentID CHARACTER

VARCHAR(n) is used for variable-length text.

The n shows the maximum number of characters allowed. This is useful for names, addresses, and other text where the length may change.

Example:

FirstName VARCHAR(50)

This means the field can store text up to 50 characters long.

BOOLEAN stores a true or false value.

In SQL, this is often represented using 1 and 0.

For example, a field such as PaidFees could store whether fees have been paid.

Example:

PaidFees BOOLEAN

INTEGER is used for whole numbers.

It does not store decimal values.

Example:

Age INTEGER

REAL is used for numbers with decimal places.

Example:

AverageMark REAL

DATE stores a date value.

A common format is: YYYY-MM-DD

Example:

DateOfBirth DATE

TIME stores a time value.

A common format is: HH:MM:SS

Example:

StartTime TIME

The ALTER TABLE command is used to change an existing table definition.

This means you can use it after a table has already been created.

For example, you may want to:

• add a new field
• add a primary key
• add a foreign key

Example:

ALTER TABLE Student ADD PRIMARY KEY (StudentID)

This changes the Student table by making StudentID the primary key.

A primary key is a field that uniquely identifies each record in a table.

This means no two rows should have the same primary key value.

For example, in a student table, each student should have a different StudentID. That makes StudentID a good primary key.

Example:

ALTER TABLE Student ADD PRIMARY KEY (StudentID)

You may also see a primary key written inside a CREATE TABLE command.

Example:

CREATE TABLE Students (
 StudentID INTEGER,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 DateOfBirth DATE,
 PRIMARY KEY (StudentID)
);

A foreign key is a field in one table that links to the primary key in another table.

Foreign keys are used to create relationships between tables.

For example, if a student belongs to a class, then the ClassID in the Student table can refer to the ClassID in the Class table.

Example:

ALTER TABLE Student ADD FOREIGN KEY ClassID REFERENCES Class(ClassID)

This means the ClassID field in Student is linked to the ClassID field in Class.

That link helps the DBMS connect related data across tables.

Here is a simple DDL script for creating a school database:

CREATE DATABASE School

CREATE TABLE Student(
 StudentID CHARACTER,
 FirstName CHARACTER,
 SecondName CHARACTER,
 DateOfBirth DATE,
 ClassID CHARACTER
);

ALTER TABLE Student ADD PRIMARY KEY (StudentID)

CREATE TABLE Class(
 ClassID CHARACTER,
 Location CHARACTER,
 LicenceNumber CHARACTER
);

ALTER TABLE Class ADD PRIMARY KEY (ClassID)

ALTER TABLE Student ADD FOREIGN KEY ClassID REFERENCES Class(ClassID)

The order matters when writing a DDL script.

First, the database is created. Then the tables are created. Then primary keys are added. Then foreign keys are added after the referenced table already exists.

This matters because you cannot create a foreign key link to a table that has not been created yet.