Catalog: A catalog is a collection of database schema. A catalog is defined by a distinct name and a database environment can have any number of catalogs. Access across the catalogs can be set up as per the needs of the database management system. There is also a default catalog for a SQL environment. It can be changed by using SET CATALOG statement.
Schema: A schema in SQL is a high level named entity in a database, which is used as a container for objects. A schema can contain the following objects of a database:
1) Table: Table along with columns, primary keys, foreign keys, and table constraints, etc.
2) Domain: A domain is a collection of valid data values defined with a name in a database.
3) Assertion: Assertion is a database constraint used for specifying a relationship between data values which crosses multiple tables with in the database.
4) Privilege: It is a capability provided to a user to access and update data.
5) View: It is a virtual table defined in schema.
6) Character set: It is used to represent non Roman characters in a schema.
7) Translation: It is used to control how text is converted from one character set to another.
8) Collation: It is used to define sorting sequence for a character set.
A schema can be created by using CREATE SCHEMA statement.
CREATE SCHEMA ESCHEMA AUTHORIZATION MAX
CREATE TABLE EMPLOYEES (NAME VARCHAR(40), AGE INTEGER)
CREATE TABLE REGION (CITY VARCHAR(40), STATE VARCHAR(40))
GRANT ALL PRIVILEGES ON EMPLOYEES TO PUBLIC
where, ESCHEMA is the name of schema,
MAX, is the owner of the schema.
A schema can be dropped by using DROP SCHEMA statement. Standard CASCADE and RESTRICT drop rules are used to drop the schema.
For Example: By using RESTRICT:
DROP SCHEMA ESCHEMA RESTRICT
By using CASCADE:
DROP SCHEMA ESCHEMA CASCADE