In a large mainframe or enterprise-level network DBMS installation, the corporate database administrator is solely responsible for creating new databases. On smaller workgroup DBMS installations, individual users may be allowed to create their own
personal databases, but it’s much more common for databases to be created centrally and then accessed by individual users. If you are using a personal computer DBMS, you are probably both the database administrator and the user, and you will have to create the database(s) that you use personally.
The SQL1 standard specified the SQL language used to describe a database structure, but it did not specify how databases are created, because each DBMS brand had taken a slightly different approach. Those differences persist in present-day mainstream DBMS products. The techniques used by these SQL products illustrate the differences:
- IBM’s DB2 has a simple default database structure. A DB2 database is associated with a running copy of the DB2 server software, and users access the database by connecting to the DB2 server. A DB2 “database” is thus effectively defined by an installation of the DB2 software on a particular computer system.
- Oracle, by default, creates a database as part of the Oracle software installation process, like DB2. For the most part, user tables are always placed in this single, systemwide database, which is named by an Oracle configuration file and associated with this particular copy of the Oracle server software. More recent versions of Oracle have been extended with a CREATE DATABASE statement for defining database names.
- Microsoft SQL Server and Sybase include a CREATE DATABASE statement as part of their Data Definition Language. A companion DROP DATABASE statement destroys previously created databases. These statements can be used with interactive or programmatic SQL. The names of these databases are tracked in a special master database that is associated with a single installation of SQL Server. Database names must be unique within this SQL Server installation. Options to the CREATE DATABASE statement specify the physical I/O device on which the database is to be located.
- Informix Universal Server supports CREATE DATABASE and DROP DATABASE SQL statements as well. An option to the CREATE DATABASE statement allows the database to be created in a specific dbspace, which is a named area of disk storage controlled by the Informix software. Another option controls the type of database logging to be performed for the new database, with trade-offs between performance and data integrity during system failures.
The SQL2 standard specifically avoids a specification of the term database because it is so overloaded with contradictory meanings from DBMS products. SQL2 uses the term catalog to describe a named collection of tables that is called a database by most popular DBMS brands. (Additional information about the database structure specified by the SQL2 standard is provided later in the section “Database Structure and the ANSI/ ISO Standard.”) The standard does not specify how a catalog is created or destroyed, and specifically says that creation or destruction is implementation dependent. It also indicates how many catalogs there are, and whether individual SQL statements that can access data from different catalogs are implementation defined. In practice, as shown by the preceding examples, many of the major DBMS vendors have moved toward the use of a CREATE DATABASE/DROP DATABASE statement pair.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.