Coginiti menu Coginiti menu
SQL Tutorial
Data Modeling and Architecture Terminology Guide
A Brief History of SQL and its Usefulness
What are Different Types of Databases?
Writing SQL in Coginiti
Beginner
SQL Select
SQL Limit
SQL Where
SQL Create Table & Insert Into
SQL Comparison Operators
SQL Logical Operators
SQL In
SQL Like
SQL Is Null
SQL Between
SQL And, Or
SQL Not
SQL Order by
Export Data
Create a Table
Drop a Table
Create a View
SQL Parameters
Drop a View
Rename a Table
SQL Delete
Add a Column
Drop a Column
SQL Update
SQL Insert
Rename a Column
Intermediate
SQL Aggregate Functions
SQL Count
SQL Sum
SQL Min/Max
SQL Average
SQL Group by
SQL Case
SQL Having
Pivoting Data
Transpose Data
SQL Join
SQL Distinct
SQL Joins on Multiple Keys
SQL Union
Data Types
Precision and Scale
Subqueries
SQL Date Formats
Snowflake Advanced Date Formatting
Redshift TO_CHAR for Dates
BigQuery Date Formatting Functions
SQL Window Functions
Charting
String Functions in SQL
A Quick Guide to Pivoting Data
Advanced
Databricks SQL Functions
Trino Advanced SQL
CoginitiScript Macros
Churn Rate
Index in SQL
Query Date and Time
Group by Time
Round Timestamps
Get the First Row per Group
CoginitiScript Constants
Analysis
Coalesce in SQL
Calculating Percentiles
How to Do Type Casting
Import a CSV
Compare Values When One Is Null
How to Query a JSON Column
How to Write a Case Statement
How to Have Multiple Counts
Using a Common Table Expression (CTE)
Calculate Cumulative Sum (Running Total)

What are Different Types of Databases?

Learn the basic concepts to understand how data is stored and managed in databases.

Contents

  • What is a Database?
  • Basic concepts to understand how data is stored and managed
  • What are the types of Databases?
What is a Database?

A database is an organized collection of data stored physically on a local computer or a server. Data is usually managed and organized in a Database Management System (DBMS). It provides functions for adding, modifying, and retrieving data in a structured manner and offers tools for managing the security, reliability, and integrity of the database. Common DBMS examples are Amazon Redshift, Azure SQL Database, Google BigQuery, Microsoft SQL Server, Netezza, PostgreSQL, Snowflake, and Yellowbrick.

Databases are designed to provide efficient and reliable access to a large amount of information and are typically used by businesses, organizations, and individuals to store, manage, and retrieve information as needed.

Databases can range from simple collections of data to complex systems with multiple data types and sophisticated data relationships, and they can be used to support a wide range of applications, such as online shopping, financial transactions, customer relationship management, and many others.

Basic concepts to understand databases

Let’s take a look at some foundational concepts for understanding and working with the most common types of databases used at organizations today: .

  • Table is a collection of related data stored in rows and columns.
  • Field is a single piece of data within a table, typically represented as a column.
  • Record is a collection of fields that represent a single item within a table, typically represented as a row in a table.
  • Primary Key is a unique identifier for each record in a table.
  • Foreign Key is a field in one table that refers to the primary key of another table.
  • Relationships are used to define how data in different tables are related often using primary and foreign keys. For example, one table might contain information about customers, and another might contain information about their orders. The relationship between these two tables would be the keys ‘CUSTOMER_ID’, which associates the customer’s information with their order information.
  • SQL is a standard language for interacting with relational databases. Knowledge of SQL is essential for database management, while knowledge of the query language used by a NoSQL database is necessary for interacting with those databases.
  • Query is a request for information from a database. Queries return specific records and calculate or updates data in the database.
  • Dataset is a collection of data that is organized and structured for a specific purpose or use case. A dataset can contain various types of data, such as numerical, categorical, and textual data, and can have different structures, such as tables, arrays, lists, and graphs, depending on the type of data and the requirements of the specific use case.
What are the types of Databases?

Relational databases
In a relational database, data is organized into tables with unique names, and each table contains a number of columns, each with a unique name, which defines the type of data that can be stored in that column. Rows in a table represent individual data records, and columns represent the attributes of those records. The relationships between tables allow the data in one

table to be related to another through the use of keys, making it possible to retrieve and analyze data flexibly and efficiently.

Relational databases are widely used in many applications, including e-commerce, finance, and healthcare. Well-know relational databases include: Amazon Redshift, Azure SQL Database, Google BigQuery, Microsoft SQL Server, Netezza, PostgreSQL, Snowflake, and Yellowbrick.

Non-relational databases (NoSQL)
Non-relational databases, also known as NoSQL databases, are a type of database that doesn’t use the relational model of storing data in tables with rows and columns linked by relationships. Instead, they use a variety of data models, including key-value, document, columnar, and graph, to store data in a more flexible and scalable way.

Non-relational databases are designed to handle large amounts of unstructured or semi-structured data, such as text, images, and social media posts, and are often used for big data and real-time web applications where traditional relational databases may struggle to provide the necessary performance and scalability.

Some examples of non-relational databases include MongoDB, Cassandra, Couchbase, and Redis. These databases are known for their horizontal scalability, ability to handle diverse data types, and support for agile development.

Object-oriented databases
In an object-oriented database, data is stored as a collection of objects.Each object has its attributes and methods, just like in an object-oriented programming language. The objects are self-containted and can be manipulated independently of other objects, providing a more intuitive way of representing complex data strcutures. modeling and organizing data, and it can also simplify the process of building and maintaining database-driven applications.

Object-oriented databases are used in various applications, including simulation, gaming, and multimedia, and they are well-suited for handling complex and changing data structures. Examples of object-oriented databases include GemStone, ObjectDB, and ZODB.

Columnar databases
A columnar database stores data in columns rather than rows. In a columnar database, each column contains the data for a specific type of attribute, making it more efficient to retrieve only the necessary data for a specific query, rather than reading the entire row.

Columnar database are well-suited for data warehousing, business intelligence, and analytics applications, where complex queries are executed against large datasets. Popular examples of columnar databases are Apache Cassandra, Amazon Redshift, and Google Bigtable.

Graph databases
Graph databases are NoSQL databases that use graph theory to store, manage, and retrieve data. In a graph database, data is stored as nodes and edges, which represent entities and relationships between entities. This allows complex relationships to be modeled and easily queried, making it well-suited for applications such as recommendation systems, social networks, and fraud detection. Unlike traditional relational databases, which use tables and foreign keys to define relationships, graph databases can dynamically evolve their schema as new relationships are added. Some popular examples of graph databases are Neo4j, Amazon Neptune, and OrientDB.

Time-series databases
Time-series databases are specialized databases designed to handle large amounts of time-stamped data, such as measurements or events. These databases are optimized for storing and querying time-series data and provide specific features for handling temporal data such as indexing, aggregation, and efficient querying based on time intervals. Time-series data is commonly used in IoT, finance, telecommunication, and server logs. Some popular examples of time-series databases are InfluxDB, TimescaleDB, and OpenTSDB.

These are some of the main types of databases, but there are also many other specialized databases, each with its strengths and weaknesses and suited to specific use cases and requirements.

Conclusion

A database is a collection of data organized in a specific and systematic way to allow efficient storage, retrieval, and manipulation of information. It is typically used to store and manage large amounts of structured information, such as customer information, financial data, or inventory records. SQL is the structured programming language analysts use to make requests (queries) to acquire or manipulate information in the database. There are different databases for different data structures and types. Relational databases are best when you have large amounts of structured data, and Non-relational databases are better suited for large amounts of unstructured or semi-structured data, such as text and images.