SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 388

Definition of Database Objects SQL provides functions to define and create
database objects. In addition to the provision of statements to define and create the
overall schema consisting of the complete set of relations in the database, SQL
provides statements for the specification of the following for each relation:

• Schema and name for the relation
• Security and authorization
• List of attributes
• Data type and domain of values for each attribute
• Primary and foreign keys
• Integrity constraints
• Indexes
• Structure on physical storage medium

Here is a list of the major data definition statements in SQL:

CREATE SCHEMA Define a new database schema for the set of relations or

tables

DROP SCHEMA Remove the definition and contents of an existing

database

CREATE TABLE Define a new table with its columns

ALTER TABLE Add or delete columns to an existing table

DROP TABLE Remove the definition and contents of an existing table

CREATE VIEW Define a logical data view consisting of columns from

one or more tables or views

DROP VIEW Remove an existing view

CREATE DOMAIN Define a domain name with certain a data type

(domain name to be used later to define the data types
of attributes)

ALTER DOMAIN Change the data types of a defined domain

DROP DOMAIN Remove a defined domain

CREATE INDEX Define an index on one or more columns

DROP INDEX Remove an existing index

SQL-based database management systems usually provide additional definition
features as noted below:

CREATE SYNONYM Define an alternative name for a table or view (usually

an abbreviation or acronym to reduce the number of
key strokes for referring to the table or view)

DROP SYNONYM Remove a defined synonym

LABEL Define a column heading for an attribute when results

are displayed

COMMENT Provide remarks or comments for table columns

(stored as part of the table in the data dictionary)