SQL - CREATE Database

The CREATE DATABASE statement is the primary command used to create or initialize a new Database or empty container within a Database Management System (DBMS)

Database or container

The database acts as a base namespace that holds the tables, views, and other data structures.

Creating a Database

Creating a database is very simple, the syntax is represented as CREATE DATABASE followed by the unique name of the database you want to create, so a complete database initialization command could be CREATE DATABASE database_name;

sql

CREATE DATABASE database_name;

Mathematical Constraint:
In a single DBMS environment, every database name must be unique. If we define $S$ as the set of all existing databases, the new name $n$ must not already exist in $S$

n \notin S

Naming Conventions

When creating a database, there are specific rules and best practices for the database name :

Uniqueness: No two databases in the same system can share a name.
Characters: Names usually consist of letters, numbers, and underscores.
Case Sensitivity: Depending on the OS (Linux vs. Windows) and the DBMS (MySQL vs. PostgreSQL), names may or may not be case-sensitive.
No Spaces: Spaces are generally not allowed; underscores (_) are used instead.

Creating Database Safely

If you attempt to create a database that already exists, the system will return an error. To prevent this, especially in automated scripts, we use the IF NOT EXISTS clause.

Command:

CREATE DATABASE IF NOT EXISTS database_name;

The logic can be represented as a conditional function:

f(n) = \begin{cases} \text{Create } D & \text{if } n \notin S \\ \text{Do Nothing} & \text{if } n \in S \end{cases}

Specifying Character Sets and Collations

When you create a database, you can specify how characters are stored (Character Set) and how they are compared or sorted (Collation). This is vital for ensuring your database supports multiple languages and handles case sensitivity correctly.

Character Set: A set of symbols and their encodings (e.g., utf8mb4).
Collation: A set of rules for comparing characters (e.g., utf8mb4_unicode_ci).

sql

CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Mathematically, if $C$ is the set of all characters and $R$ is the relation used for comparison:

D = \{T_1, \dots, T_n\}, \quad \text{subject to } \{C, R\}

Summary: CREATE DATABASE

Definition: The CREATE DATABASE command initializes a new, empty namespace within a database management system.
Logical State: It transforms the state of the system from having no database to containing an empty set $D = ∅$
Uniqueness: Every database name must be unique within the server; you cannot have two databases with the same name.
Safety: Using the IF NOT EXISTS clause prevents scripts from failing if the database was previously created.
Configuration: You can define specific character sets and collations during the creation process to handle international text and sorting rules.