SQL Database (Overview)

A database is an organized, structured collection of data stored electronically in a computer system. In relational database systems, data is maintained across logical containers managed by a Relational Database Management System (RDBMS).

Before creating individual objects like tables, views, or stored procedures, you must first create and configure the Database that acts as the top-level container for all related schema objects.

The Database Hierarchy

In modern relational engines (such as PostgreSQL, SQL Server, MySQL, and Oracle), data structures are organized in a clear parent-child hierarchy:

               RDBMS SERVER INSTANCE
                         |
      +------------------+------------------+
      |                                     |
DATABASE A                            DATABASE B
  (e.g., ECommerceDB)                   (e.g., HR_AnalyticsDB)
      |
      +---> SCHEMAS (e.g., dbo, sales, inventory)
              |
              +---> TABLES (e.g., Customers, Orders)
              +---> VIEWS
              +---> STORED PROCEDURES / FUNCTIONS
              +---> INDEXES

RDBMS Server Instance: The underlying engine software running on a server (cloud or on-premise).
Database: The primary logical boundary. Objects in one database are isolated from objects in another database on the same server.
Schema: A logical grouping or namespace within a database (e.g., sales.Orders vs. hr.Employees).
Database Objects: Physical structures that store or manipulate data, such as tables, indexes, views, and procedures.

System Databases vs. User Databases

Most RDBMS platforms maintain two distinct types of databases on a single server instance:

Database Type	Purpose	Examples
System Databases	Built-in databases used internally by the database engine to store administrative metadata, system settings, user credentials, login sessions, and execution logs.	SQL Server: `master`, `msdb`, `model`, `tempdb`

PostgreSQL: postgres, template1

MySQL: mysql, information_schema, performance_schema |
| User Databases | Custom databases created by developers or DBAs to store application-specific business data. | ECommerceDB, Payroll_App, Inventory_System |

Core Database Lifecycle Operations

Managing a database involves four basic administrative tasks using Data Definition Language (DDL) commands:

1. Creating a Database

Establishes a new, empty database on the server instance.

CREATE DATABASE Production_DB;

2. Selecting a Database (`USE`)

In interactive SQL environments (like MySQL or SQL Server CLI), you explicitly select which database your subsequent queries should run against.

USE Production_DB;

Note: PostgreSQL does not use the USE command; instead, you connect directly to a specific database session via your connection string or client tools using \c database_name.

3. Modifying a Database

Used to alter physical storage files, change character sets/collations, or rename the database.

ALTER DATABASE Production_DB READ_ONLY = ON;

4. Dropping a Database

Permanently deletes the database and all objects (tables, views, indexes, records) stored inside it. This action cannot be undone.

DROP DATABASE Production_DB;

Important Database Configuration Concepts

When setting up a database, two core configurations dictate how data is formatted and preserved:

Character Set & Collation:
Character Set: Determines the character encoding supported by the database (e.g., utf8mb4 for universal Unicode support, including emojis).
Collation: Sets the rules for comparing and sorting string data (e.g., case-sensitivity like latin1_swedish_ci where ci = Case Insensitive).
ACID Transactions & Storage Engines:
Databases manage how changes are committed to physical disk storage. Engines like InnoDB (MySQL) or write-ahead logging (PostgreSQL/SQL Server) ensure data remains consistent even during unexpected hardware or power failures.