Posted on Jun 30

Introduction to DBMS: Why and How We Store Data

DBMS, or Database Management System, is a fundamental concept in the world of data handling and software systems.
Before we dive deep into this topic, let’s first understand some basic terminologies that will give you a clear picture of what DBMS is and why it matters.

Basic Terminologies

Data — Raw facts and figures without any context, such as numbers, text, or symbols stored in a database.
Information — Processed and organized data that is meaningful and useful for decision-making.
Database — A structured collection of data that enables easy access, management, and updates. It is generally stored and accessed electronically from a computer system.
Database Management System (DBMS) — Software that facilitates efficient storage, retrieval, and management of data within a database.

Why Use a DBMS?

You might be wondering: Why DBMS? Why not store data in a file system or even in an Excel sheet?

The reason lies in the powerful features that a DBMS offers:

It supports data querying, reporting, and analytics to enable informed decision-making.
It provides structured querying capabilities, allowing for faster and more efficient data access.
It ensures data integrity, consistency, and security, which are hard to manage in a plain file system.

Three Views of a Database in DBMS

1. Internal View (Physical Level)

This is the lowest level of abstraction that defines how the data is physically stored in memory (files, indexes, etc.). This level is managed by the DBMS and is invisible to users and even most developers.

2. Conceptual View (Logical Level)

This level shows the logical structure of the entire database — what data is stored and how different data items relate to each other. This level is maintained by database administrators and is common for all users. It hides the internal view but reveals how data is logically organized.

3. External View (View Level)

This is the highest level of abstraction, showing only a part of the database that a specific user or application needs. Different users can have different external views, depending on their roles or permissions.

Data Independence

Data Independence is the ability to change the structure of a database at one level without affecting the structure at the next higher level. It ensures that changes in how data is stored (physical level) or how it's organized (logical level) do not force changes in the applications using that data.
There are two types of data independence:

Logical Data Independence – changing the logical schema without affecting external views.
Physical Data Independence – changing physical storage without affecting the logical schema.

Instance and Schemas

Instance - An instance is the actual content or data stored in the database at a particular moment in time. An instance changes frequently as data is inserted, updated, or deleted.
Schemas - A schema is the overall design or structure of the database — it defines how the data is organized, including tables, fields, relationships, and constraints. The schema is defined once when the database is created and usually does not change frequently.

Types of Data Bases

Databases come in different types based on how data is stored, organized, and accessed. Here are some of the most common types:

1.Relational Database - Stores data in tables (rows and columns) with relationships between them using keys.

Example: MySQL, PostgreSQL, Oracle.

Use Case: Banking systems, inventory management, e-commerce websites.

Analogy: Like an Excel spreadsheet where rows are records and columns are fields.

2.Hierarchical Database - Stores data in a tree-like structure with parent-child relationships.

Example: IBM Information Management System (IMS).

Use Case: File systems, legacy applications.

Analogy: Like folders inside folders on your computer.

3.Network Database - Similar to hierarchical but allows many-to-many relationships between records using graph-like structure.

Example: Integrated Data Store (IDS), CODASYL.

Use Case: Telecom and complex network applications.

Analogy: Like a web where one page can link to many others and vice versa.

4.Object-Oriented Database - Stores data in the form of objects, like in object-oriented programming.

Example: db4o, ObjectDB.

Use Case: Applications using C++, Java, Python with complex data types.

Analogy: Like storing not just the data, but also the behavior (methods) of the object.

5.Document-Oriented (NoSQL) Database - Stores data as documents (usually JSON or XML), allowing flexible schema.

Example: MongoDB, CouchDB.

Use Case: Real-time analytics, content management systems, modern web apps.

Analogy: Like folders of dynamic, self-contained documents.

6.Columnar Database - Stores data column-wise instead of row-wise for faster analytics.

Example: Apache Cassandra, HBase.

Use Case: Big Data analytics, data warehousing.

Like flipping a spreadsheet and focusing on one column at a time for analysis.

7.Key-Value Store - Stores data as key-value pairs, great for fast lookups.

Example: Redis, DynamoDB.

Use Case: Caching, session storage, high-speed real-time systems.

Analogy: Like a dictionary where each word (key) has a definition (value).

Beside them a new and growing class of databases tailored for semantic and AI-driven workloads is evolving which is called as Vector Databases. Vector Databases Stores and queries high-dimensional embeddings using ANN indexing (e.g., Pinecone, Milvus, Chroma). Especially useful for semantic search, AI retrieval (RAG), image/text similarity . While they stand alongside relational, NoSQL, and graph databases, their ability to combine with other models places them firmly in the evolving landscape of multi-model/hybrid databases.

ER Diagram

An ER (Entity-Relationship) Diagram is a visual representation of the data and the relationships between different entities in a database. It helps in designing a database by showing entities (like tables), their attributes (fields), and how they are connected.

Entity

An Entity is a real-world object or concept that can be distinctly identified and has data stored about it in a database. Each entity has attributes (properties) that describe it.

Example: In a school database, entities can be Student, Teacher, or Course.

There are two types of entity

Tangible- Entities which physically exist in real world. E.g. - Car, Pen, locker.
Intangible- Entities which exist logically. E.g. – Account, video.

In ER diagram we cannot represent an entity, as entity is an instant not schema, and ER diagram is designed to understand schema. In a relational model entity is represented by a row or a tuple or a record in a table.

Entity set

An entity set is a collection of similar types of entities (i.e., multiple entities of the same kind stored together in a table). An entity set is the entire table — all the rows (entities) that represent the same type of thing. Just like a "Student" table is an entity set made up of many "Student" entities.

Based on the above table

"John" is one entity.
"Marry" is another entity.

Together, they belong to the Customer entity set.

Attributes

An attribute is a property or characteristic of an entity. In databases, attributes are represented as columns in a table. If an entity is a row, then each attribute is a column that stores information about that row.

Here are the attributes of the above customer entity set CustomerID, FirstName, LastName, BirthDate. Each column is an attribute, and it holds a specific piece of information about every customer.

In an ER diagram attributes are represented by ellipse or oval connected to rectangle.

While in a relational model they are represented by independent column. e.g. EmpNo, EmpName, Salary

Types of Attributes

Simple Attribute - Cannot be divided further. eg: Age, Email

Think of it as: One solid value.
Composite Attribute - Can be divided into smaller parts. eg: Name → can be split into First Name and Last Name

Think of it like: Full Name = First Name + Last Name
Derived Attribute - Can be calculated from other attributes. eg: Age can be derived from Date of Birth

Think of it like: Not stored directly, just calculated when needed.
Single-valued Attribute - Holds only one value per entity. eg: Student_ID, Date of Birth

Just a regular field, not a list
Multi-valued Attribute - Can have multiple values for one entity. eg: Phone Numbers (a student can have more than one)
Key Attribute - Uniquely identifies an entity (usually the Primary Key) eg: Student_ID

It's the ID card of each row

Relationship / Association

A relationship in DBMS is an association between two or more entities. It shows how entities are connected or related to each other in the real world.
If an entity is a table (like Student or Course), then a relationship shows how those tables are connected — for example, which student is studying which course.

Example:

Entities: Student, Course
Relationship: Study

📊 In an ER diagram, relationships are shown as diamonds, with lines connecting the related entities. While in relational model
sometimes through foreign key and other time by a separate table.

Component of Relationships.

In an ER (Entity-Relationship) diagram, a relationship connects two or more entities. But every relationship is more than just a connection — it has important components that describe how it works. Let’s understand the three main components of a relationship:

Name - The name simply identifies what the relationship means or represents in real life. For example: In a school database, the relationship between Student and Course can be named "Enrolled" — indicating which student is enrolled in which course. A meaningful name makes your ER diagram easy to read and understand. It's like labeling arrows in a flowchart. Every relation must have a unique name.
Degree – It can be defined as the number of entity sets (relations/tables) associated (participating) in the relationship set. Most of the relationship sets in a database system are binary. Occasionally, however, relationship sets involve more than two entity sets. In short, the degree tells us how many entities are connected and helps in choosing the right design structure. It has three types:
- Unary Relationship – One single entity set participates in a relationship, meaning two entities of the same entity set are related to each other. Example: A member in a team may be the supervisor of another member in the same team.
- Binary Relationship – Two entity sets participate in a relationship. It is the most common type of relationship. Example: Student enrolled in Course.
- Ternary Relationship – Three different entity sets participate in a single relationship. It is used when a relationship logically requires all three entities. Example: A Doctor prescribes a Medicine to a Patient.
Structural constraints (Cardinalities Ratios, Participation) – These constraints define the rules of how entities participate in relationships. They help in understanding the nature of the relationship between entities and are mainly of two types:
- Cardinality Ratios – This defines the number of instances of one entity that can be associated with instances of another entity. It determines the type of relationship:
  - One-to-One (1:1) – One instance of an entity A is related to at most one instance of entity B, and vice versa. Example: One person has one passport.
  - One-to-Many (1:N) – One instance of entity A is related to multiple instances of entity B, but one instance of B is related to only one instance of A. Example: One teacher teaches many students.
  - Many-to-One (N:1) – Many instances of entity A are related to one instance of entity B. Example: Many employees work in one department.
  - Many-to-Many (M:N) – Many instances of entity A can be related to many instances of entity B. Example: Students enroll in many courses and courses have many students.
- Participation – It defines whether all entities in an entity set must participate in a relationship or not. It is of two types:
  - Total Participation – Every instance of the entity set must participate in the relationship. Example: Every employee must belong to at least one department.
  - Partial Participation – Some instances of the entity set may not participate in the relationship. Example: Some students may not enroll in any course.

Advantages of ER Diagrams

Simple and Clear Visualization – ER diagrams provide a graphical representation of entities, relationships, and attributes, which makes database design easier to understand.
Helps in Logical Database Design – They are excellent tools for planning and organizing database structures before implementation.
Improves Communication – ER diagrams help developers, database designers, and stakeholders communicate and collaborate effectively.
Error Detection – It becomes easier to identify inconsistencies, missing entities, or unnecessary relationships at an early stage.
Documentation Purpose – ER diagrams serve as useful documentation for maintaining and updating the database over time.

Disadvantages of ER Diagrams

Not Suitable for Complex Logic – They are not ideal for representing complex constraints or business logic like triggers or stored procedures.
Scalability Issues – For very large systems, ER diagrams can become overly complex and hard to manage or read.
No Standard Symbols – Different organizations or tools may use different notations, which can create confusion.
Lack of Support for Some Data Models – ER diagrams are primarily used for relational databases and may not represent NoSQL or object-oriented models effectively.
Loss of information content

What’s Next?

Now that we've covered the basics in this blog, get ready to dive deeper!
In the next blog, we’ll explore Relational Database Management Systems (RDBMS) and break down some complex concepts in a simple and beginner-friendly way.

To stay updated with the series, follow me on X and LinkedIn.

Thank you for reading!

DEV Community