Students, you all are aware of the term DATABASE and in Web-Application Development Process or(and) in Software Development Process, all BIG INFORMATION TECHNOLOGY (IT-TECH) FIRMS utilizes different kinds-of/or types-of databases as-per their need or as-per their client's desire or as-per the feasibility of the project.
Why we need a DATABASE?
To store your different kinds of datas you are required to have a databse management system, a Software Application which provides features so-that you could store your data related to your project or not related to your project (like storing random files, images, audio/video, text-files etc.).
Full-stack web applications primarily use two main types of databases: relational (SQL) and non-relational (NoSQL). Many modern applications use a combination of both types, a strategy known as polyglot persistence, to leverage their respective strengths.
Here are the names and types of databases commonly used:
Relational Databases (SQL)
Relational databases store data in structured tables with predefined schemas and enforce data integrity and consistency through ACID properties, making them ideal for complex queries and transactional systems like e-commerce or financial applications.
Non-Relational Databases (NoSQL)
NoSQL databases offer flexible schemas and are designed for high scalability and performance, particularly useful for handling large volumes of unstructured or semi-structured data like user-generated content or real-time analytics.
Databases can be broadly categorized into older legacy systems (e.g., Hierarchical, Network) and modern systems which are primarily defined by their data models, such as Relational (SQL) and NoSQL databases (Document, Key-Value, Graph, and Wide-Column).
Here is a list of all major types of databases, categorized by their development era and data model:
Legacy and Early Databases (1960s–1980s)
These types, though less common today, paved the way for modern systems and are still found in niche or legacy environments.
Modern Databases (1970s–Present)
These are the dominant database types used in modern application development.
Relational Databases (SQL)
Relational Database Management Systems (RDBMS) store data in structured tables with rows and columns, using SQL (Structured Query Language) for defining and manipulating data. They excel at managing structured data with complex relationships and ensuring data integrity through ACID properties.
NoSQL Databases
NoSQL (Not only SQL) databases were developed in the late 2000s and 2010s to handle large volumes of unstructured or semi-structured data, prioritizing flexibility and horizontal scalability over strict data consistency. They are generally classified by their data model:
Emerging and Specialized Databases
These are newer or niche types designed for specific modern use cases.
SQL (Structured Query Language) is a non-procedural language used for managing and manipulating data in relational databases, while PL/SQL (Procedural Language/Structured Query Language) is a procedural extension to SQL (primarily associated with Oracle) that adds programming capabilities like loops and conditional statements.
SQL Queries
SQL queries are statements (instructions) used to communicate with a database to perform specific tasks, such as retrieving, inserting, updating, or deleting data. SQL is declarative, meaning you specify what you want to do, not how to do it.
Common types of SQL commands (queries/statements) fall under different categories:
CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE.SELECT, INSERT, UPDATE, DELETE.GRANT, REVOKE.COMMIT, ROLLBACK, SAVEPOINT. PL/SQL Queries (Blocks):
PL/SQL is a proprietary extension that wraps SQL statements within a procedural framework, enabling more complex application logic. A PL/SQL program is a block structure which can be a standalone anonymous block, a stored procedure, function, or trigger.
A basic PL/SQL block has three main sections:
| Section | Keyword | Description |
|---|---|---|
| Declaration | DECLARE |
Optional. Used to declare variables, constants, cursors, and exceptions. |
| Executable | BEGIN |
Mandatory. Contains the procedural and SQL statements to be executed. |
| Exception Handling | EXCEPTION |
Optional. Contains code to handle errors that occur during execution. |
PL/SQL incorporates DML statements (like INSERT, UPDATE, DELETE) within its BEGIN...END blocks, but it often uses the special SELECT INTO statement to fetch data from a query directly into a local variable.
For example, a simple PL/SQL block to output a message:
SQL_CODE:
DECLARE
message VARCHAR2(20) := 'Hello, World!';
BEGIN
DBMS_OUTPUT.PUT_LINE(message);
END;
/
SQL and PL/SQL are both data management languages used with relational databases, but they serve different purposes and have distinct features.
SQL Queries
SQL (Structured Query Language) is a standard, declarative language used to manage and manipulate data in relational database management systems (RDBMS). It defines what needs to be done, rather than how it should be done.
Key operations using SQL queries include:
CREATE TABLEALTER TABLEDROP TABLESELECT (data retrieval)INSERT INTO (adding data)UPDATE (modifying data)DELETE (removing data)GRANT, REVOKE).COMMIT, ROLLBACK). PL/SQL Queries
PL/SQL (Procedural Language/SQL) is an extension developed by Oracle Corporation that adds procedural programming capabilities to SQL. It allows developers to embed SQL queries within programmatic blocks to control the flow of data processing.
Key features of PL/SQL include:
IF-THEN-ELSE) and loops (FOR, WHILE).EXCEPTION section in blocks to handle runtime errors gracefully.Key Differences Summary
| Feature | SQL | PL/SQL |
|---|---|---|
| Purpose | Single query for DML/DDL operations. | Program block for complex logic, procedures, functions. |
| Nature | Declarative (defines what is needed). | Procedural (defines how things need to be done). |
| Execution | Executes single statements. | Executes a block of code. |
| Control Structures | Does not support loops or conditional logic. | Supports loops, IF-ELSE statements, etc.. |
| Error Handling | No built-in exception handling. | Provides robust exception handling mechanisms. |
| Usage | Direct data manipulation and retrieval. | Building complex application backends and business logic. |
-->
Here are the key features of databases, Database Management Systems (DBMS), database schemas, data models, and PL/SQL programming:
Databases
A database is a structured collection of inter-related data that is organized for efficient storage, retrieval, and management.
- Organized Storage: Data is stored in a structured or semi-structured format (e.g., tables, documents, graphs) for efficient access.
- Data Integrity and Consistency: Mechanisms ensure the data is accurate, reliable, and uniform across the system.
- Data Security: Provides robust access controls, authentication, and encryption to protect sensitive information.
- Multi-User Access: Allows multiple users and applications to access and manipulate data simultaneously without conflicts through concurrency control.
- Persistence: Data remains stored permanently until explicitly deleted, with mechanisms for backup and recovery to prevent data loss.
Database Management Systems (DBMS)
A DBMS is a software system that acts as an interface between the database and end users or applications, enabling users to define, create, maintain, and control access to the database.
- Data Abstraction (Data Independence): Separates the logical view of the data from its physical storage details, so changes in storage don't affect application programs.
- Reduced Data Redundancy: Centralized control minimizes duplicate data entries, saving storage space and improving consistency.
- Query Language: Provides a language (like SQL or PL/SQL) to interact with the database for data retrieval and manipulation.
- Transaction Management: Ensures reliable execution of operations, often adhering to the ACID properties (Atomicity, Consistency, Isolation, Durability) in case of system failures.
- Backup and Recovery: Built-in subsystems create automatic backups and restore data in case of hardware or software failures.
Database Schema
A database schema is the skeleton structure or blueprint that defines the logical design and organization of the entire database.
- Logical Structure Definition: Defines the tables, columns, data types, and the relationships between data elements.
- Enforces Constraints: Formulates all rules and constraints (e.g., primary keys, foreign keys, unique constraints, "not null" rules) to maintain data accuracy and integrity.
- Provides a Blueprint: Serves as essential documentation for developers and administrators to understand how data is organized and how the components relate.
- Separation from Data: The schema is the description of the database, but it does not contain the actual data itself (the data at any given moment is the database instance).
Data Models
A data model defines the logical design and structure of a database and determines how data will be stored, accessed, and updated within a DBMS.
- Representation of Real-World Entities: Helps in representing data accurately by modeling real-world objects (entities), their characteristics (attributes), and associations (relationships).
- Conceptual, Logical, and Physical Views: Data models provide different levels of abstraction: the conceptual model (high-level view), the logical model (detailed structure like tables and columns), and the physical model (how data is physically stored on disk).
- Foundation for Database Design: A chosen data model (e.g., Relational, Hierarchical, Network, NoSQL document/graph) guides the overall database design and implementation.
- Minimizing Redundancy: A good data model helps minimize data duplication by organizing data logically (e.g., through normalization in relational models).
PL/SQL Programming
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle database.
- Procedural Capabilities: Extends SQL with procedural programming features like loops (
LOOP, WHILE), conditional statements (IF-THEN-ELSE), and variables, enabling complex logic.
- Integration with SQL: Allows seamless embedding of SQL statements within PL/SQL code, combining the power of declarative data access with procedural control.
- Modular Programming: Supports the creation of reusable program units such as stored procedures, functions, packages, and triggers, which can be stored in the database itself.
- Performance Enhancement: By running application logic directly within the database server, it reduces network traffic between the application and the database.
- Error and Exception Handling: Includes robust mechanisms to catch and handle runtime errors gracefully, improving application reliability.
Databases can be categorized by their historical development, data models, and specific use cases, ranging from older, foundational types to modern specialized systems.
Here are the main types of databases:
Legacy and Foundational Databases
These older models were among the first approaches to data management.
- Hierarchical Databases: Data is organized in a tree-like structure, with a single parent node for each child node, representing one-to-many relationships. This model was used by early systems like IBM's Information Management System (IMS).
- Network Databases: An evolution of the hierarchical model, the network database allows a child record to have multiple parent records, forming a more flexible graph-like structure (many-to-many relationships). Examples include Integrated Data Store (IDS).
Modern Conventional Databases
These types are widely used today, with the relational model being one of the most established and common.
- Relational Databases (RDBMS): Store data in structured tables with rows and columns, using SQL (Structured Query Language) for querying and management. They enforce data integrity through a defined schema and are ideal for structured, predictable data, such as in banking systems.
- Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.
- Object-Oriented Databases (OODBMS): Store data as objects, similar to those used in object-oriented programming languages. This approach aims to bridge the gap between application code and the database, making it useful for complex data models like those found in CAD software.
- Examples: ObjectDB, db4o.
Modern & Specialized Databases
These newer types (often collectively referred to as NoSQL or NewSQL) are designed to handle the demands of big data, scalability, and semi-structured or unstructured data.
- NoSQL Databases: Offer flexible schemas and are designed for high performance and scalability, handling data that doesn't fit well into the relational model. They are often categorized further by their specific data model:
- Document Databases: Store data in semi-structured document formats (e.g., JSON), ideal for content management systems.
- Examples: MongoDB, Couchbase.
- Key-Value Databases: Store data as simple key-value pairs, offering very fast data access, often used for caching and user profiles.
- Examples: Redis, Amazon DynamoDB.
- Wide-Column Stores: Store data by columns rather than rows, optimizing for large-scale analytical queries and handling massive datasets efficiently.
- Examples: Apache Cassandra, Google Spanner.
- Graph Databases: Use nodes and edges to represent and store relationships, making them ideal for social networks, fraud detection, and recommendation engines.
- Examples: Neo4j, Amazon Neptune.
- Time-Series Databases: Optimized for storing and querying data that is time-stamped, such as IoT sensor logs, system metrics, or stock prices.
- Examples: InfluxDB, Prometheus.
- NewSQL Databases: Aim to combine the scalability and performance of NoSQL systems with the transactional consistency and relational model of traditional RDBMS.
- Examples: CockroachDB, Google Spanner.
- Multi-model Databases: Systems that support more than one data model (e.g., document, graph, key-value) within a single integrated backend.
- Examples: ArangoDB, OrientDB.
- Cloud Databases: Databases built and deployed on a cloud computing platform, offering scalability, availability, and managed services without the need for hardware maintenance.
- Examples: Amazon Aurora, Google Cloud SQL, Microsoft Azure SQL Database.
Databases can be categorized by their historical development, data models, and specific use cases, ranging from older, foundational types to modern specialized systems.
--->