Database Creation

Database Creation

Introduction

Previous chapter లో మనం SQL basics, history, మరియు importance గురించి discuss చేసాం. ఈ chapter లో మనం database ఎలా create చేయాలి, modify చేయాలి, delete చేయాలి అనే process గురించి నేర్చుకుందాం.

Database అంటే ఏమిటి?

Database అనేది related data collection, ఇది structure గా organize అవుతుంది – access, manage, మరియు update చేయడానికి. కొన్ని ఉదాహరణలు:

• Personal info (names, addresses, phone numbers)
• Product inventory
• Customer orders
• Financial transactions
• Employee records

How to Create a Database

SQL లో database create చేయడానికి CREATE DATABASE statement use చేస్తాం:

CREATE DATABASE database_name;

Example:

CREATE DATABASE school_management;

IF NOT EXISTS Clause

అదే పేరుతో డేటాబేస్ already ఉన్నట్లయితే, CREATE DATABASE statement error throw చేస్తుంది. ఈ error నివారించడానికి, మనం IF NOT EXISTS క్లాజ్‌ని ఉపయోగించవచ్చు:

CREATE DATABASE IF NOT EXISTS school_management;

CHARACTER SET and COLLATION

Database create చేస్తున్నప్పుడు character set & collation specify చేయవచ్చు:

CREATE DATABASE school_management
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

• Character Set – data encode చేయడానికి
• Collation – string comparison rules

How to Select a Database

USE school_management;

List All Databases

• MySQL/MariaDB: SHOW DATABASES;
• PostgreSQL: SELECT datname FROM pg_database;
• SQL Server: SELECT name FROM sys.databases;
• SQLite: No direct command (databases = files)
• Oracle: SELECT username FROM all_users;

View Database Structure

• MySQL/MariaDB: SHOW TABLES;
• PostgreSQL:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

• SQL Server: SELECT name FROM sys.tables;
• SQLite: .tables
• Oracle: SELECT table_name FROM user_tables;

Modify Database Properties

MySQL/MariaDB

ALTER DATABASE school_management
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

PostgreSQL

ALTER DATABASE school_management SET timezone TO 'UTC';

SQL Server

ALTER DATABASE school_management SET RECOVERY FULL;

Rename a Database

PostgreSQL

ALTER DATABASE school_management RENAME TO new_school_management;

SQL Server

ALTER DATABASE school_management MODIFY NAME = new_school_management;

MySQL/MariaDB

Direct rename support లేదు – instead:

1. Create new DB
2. Copy data
3. Drop old DB

Delete a Database

DROP DATABASE school_management;

With IF EXISTS

DROP DATABASE IF EXISTS school_management;

Backup and Restore

MySQL/MariaDB

Backup:

mysqldump -u username -p school_management > school_management_backup.sql

Restore:

mysql -u username -p school_management < school_management_backup.sql

PostgreSQL

Backup:

pg_dump -U username school_management > school_management_backup.sql

Restore:

psql -U username school_management < school_management_backup.sql

SQL Server

Backup:

BACKUP DATABASE school_management TO DISK = 'C:\backup\school_management_backup.bak';

Restore:

RESTORE DATABASE school_management FROM DISK = 'C:\backup\school_management_backup.bak';

Real-World Example – School Management System

CREATE DATABASE IF NOT EXISTS school_management
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE school_management;

SHOW TABLES;

ALTER DATABASE school_management
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

DBMS-Specific Features

MySQL/MariaDB

CREATE DATABASE school_management
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
ENCRYPTION = 'Y';

PostgreSQL

CREATE DATABASE school_management
WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

What We Learned in This Chapter

• What is a Database
• How to create, select, modify, rename, and delete a database
• Use of IF NOT EXISTS, CHARACTER SET, and COLLATE
• How to list databases and view tables inside them
• How to backup and restore a database in MySQL, PostgreSQL, and SQL Server
• Real-world example: School Management System DB
• DBMS-specific features for MySQL, PostgreSQL, SQL Server