INFORMATION MANAGEMENT

MODULE 6: Data Definition Language (DDL)

 MODULE 6

DATA DEFINITION LANGUAGE

(DDL)
 MODULE 6

OBJECTIVES

■At the end of the chapter, the learner should be able to:
• Define terms
• Define a database using SQL data definition language
• Establish referential integrity using SQL
• Use SQL commands to manage tables
Data Definition Language (DDL)
• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE
• COMMENT

Table
• Basic unit of storage; composed of rows

Database Systems 1
Table names and column names:
• Must begin with a letter
• Must be 1–30 characters long
• Must contain only A–Z, a–z, 0–9, _, $, and #
• Must not duplicate the name of another object owned by
the same user
• Must not be an Oracle server–reserved word

Database Systems 1
Data Type Description
VARCHAR2(size) Variable-length character data
CHAR(size) Fixed-length character data
NUMBER(p,s) Variable-length numeric data
DATE Date and time values
LONG Variable-length character data (up to 2 GB)
CLOB Character data (up to 4 GB)
RAW and LONG Raw binary data
RAW
BLOB Binary data (up to 4 GB)
BFILE Binary data stored in an external file (up to 4 GB)
ROWID A base-64 number system representing the unique address of a
row in its table
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key–foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table

Database Systems 1
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there
are dependencies.
• The following constraint types are valid:
o NOT NULL
o UNIQUE
o PRIMARY KEY
o FOREIGN KEY
o CHECK

Database Systems 1
• You can name a constraint, or the Oracle server generates a name by using the
SYS_Cn format.
• Create a constraint at either of the following times:
• At the same time as the creation of the table
• After the creation of the table
• Define a constraint at the column or table level.
• View a constraint in the data dictionary.

Database Systems 1
 CREATE TABLE [schema.]table
• Syntax: (column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

• Column-level constraint syntax:

column [CONSTRAINT constraint_name] constraint_type,

• Table-level constraint syntax:

column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),

Database Systems 1
Database Systems 1
Figure 6-6 SQL database definition commands for Pine Valley Furniture Company (Oracle 11g)

Overall table
definitions
 Non-nullable specification

Identifying primary key

 Non-nullable specifications

Primary key

Some primary keys are composite–

composed of multiple attributes
 Controlling the values in attributes

Default value

Domain constraint
Identifying foreign keys and establishing relationships

Primary key of
parent table

Foreign key of dependent table

Referential integrity–constraint that ensures that foreign key values
of a table must match primary key values of a related table in 1:M
relationships

Restricting:
• Deletes of primary records
• Updates of primary records
• Inserts of dependent records

Database Systems 1
• FOREIGN KEY: Defines the column in the child table at the table-
constraint level
• REFERENCES: Identifies the table and column in the parent table
• ON DELETE CASCADE: Deletes the dependent rows in the child table
when a row in the parent table is deleted
• ON DELETE SET NULL: Converts dependent foreign key values to null

Database Systems 1
Figure 6-7 Ensuring data integrity through updates

Relational
integrity is
enforced via the
primary-key to
foreign-key match
ALTER TABLE statement allows you to change column specifications:

Table Actions:

Example (adding a new column with a default value):

Database Systems 1
Use the ALTER TABLE statement to:
• Add or drop a constraint, but not modify its structure

ALTER TABLE <table_name>

ADD [CONSTRAINT <constraint_name>]
type (<column_name>);

ALTER TABLE emp2

ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);

Database Systems 1
RENAME COLUMN statement allows you to rename an existing column in
an existing table in any schema.

ALTER TABLE table-name

RENAME COLUMN old-column-name to new- column-name;
ALTER TABLE employee
RENAME COLUMN manager to supervisor;

Database Systems 1
You can use the ALTER TABLE syntax to:
• Put a table into read-only mode, which prevents DDL or DML changes during table maintenance
• Put the table back into read/write mode

ALTER TABLE employees READ ONLY;

-- perform table maintenance and then

-- return table back to read/write mode

ALTER TABLE employees READ WRITE;

Database Systems 1
• DROP TABLE statement allows you to remove tables from your
schema.
• Moves a table to the recycle bin
• Removes the table and all its data entirely if the PURGE clause is
specified
• DROP TABLE table_name [PURGE];
• DROP TABLE CUSTOMER_T;

Database Systems 1
• Enables you to recover tables to a specified point in time with a single statement
• Restores table data along with associated indexes, and constraints
• Enables you to revert the table and its contents to a certain point in time or SCN

SCN

Database Systems 1
• Repair tool for accidental table modifications
• Restores a table to an earlier point in time
• Benefits: Ease of use, availability, and fast execution
• Is performed in place
• Syntax:
FLASHBACK TABLE[schema.]table[,
[ schema.]table ]...
TO { TIMESTAMP | SCN } expr
[ { ENABLE | DISABLE } TRIGGERS ];

Database Systems 1
DROP TABLE emp2;

SELECT original_name, operation, droptime FROM recyclebin;

FLASHBACK TABLE emp2 TO BEFORE DROP;

• RENAME TABLE allows you to rename an existing table in any schema
(except the schema SYS).
• To rename a table, you must either be the database owner or the table owner.
• RENAME table-name to new- table-name;
• RENAME employees to emp;

Database Systems 1
• Removes all rows from a table, leaving the table empty and the table structure
intact
• Is a data definition language (DDL) statement rather than a DML statement;
cannot easily be undone
• Syntax:
TRUNCATE TABLE table_name;

• Example:
TRUNCATE TABLE copy_emp;

Database Systems 1
• You can add comments to a table or column by using the COMMENT statement:

COMMENT ON TABLE employees

IS 'Employee Information';

COMMENT ON COLUMN employees.first_name

IS 'First name of the employee';

Database Systems 1
In this lesson, you should have learned the following:
• Define a database using SQL data definition language
• Establish referential integrity using SQL
• Use SQL commands to manage tables
END OF MODULE 6
• Taylor, A. G. (2019). SQL for dummies (9th ed.). Hoboken, NJ: For
Dummies.
• Harrington, J. (2016). Relational Database Design and Implementation
(4th Edition). Morgan Kaufmann
• Juric, N., Vrbsky, S., Nestorov, S. (2016). Database Systems: Introduction
to Databases and Data Warehouses. Prospect Press
• Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
• Sullivan, D. (2015). NoSQL for Mere Mortals (1st ed.). Boston: Addison-
Wesley.
• Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database Management 11th
Edition, Prentice Hall.