Newton Institute of Technology Logo
Newton Institute of Technology Building Your Dream Career

What Is a Database? How Databases Work in Real Applications

Database Fundamentals Beginner Guide

What Is a Database?
How Databases Work in Real Applications

Frontend · Backend · Database

Follow data from a user’s screen into a database and back, then learn how tables, keys, queries, constraints, indexes and transactions make real applications work.

The data route

Store it. Find it. Return it.

FocusDatabase fundamentals

FlowApp → data → response

Core ideasTables · keys · queries

Updated1 November 2026

Quick answer

What is a database?

A database is an organised collection of related data that can be stored, retrieved and managed. A database management system (DBMS) is the software that manages that data. In a real web application, a common flow is User → Frontend → Backend → Database system → Backend → Frontend. [1]

  • Store
  • Retrieve
  • Relate
  • Protect
  • Return
DatabaseOrganised data
DBMSManagement software
TableRows + columns
KeyIdentity + relationships
QueryAsk for data
TransactionAll or nothing

Why does an application need a database?

Suppose you complete an online course-registration form today and return tomorrow. If the application needs to remember your registration, the information must be stored somewhere persistent.

Persistent data is information that remains available after the current page, session or program ends. A database is one common way to organise that long-lived information.

School system

Students, courses, enrolments, examination results, attendance and payments.

Online shop

Customers, products, prices, stock levels, orders and delivery records.

Booking system

Users, available slots, bookings, status changes and payment records.

Why not save everything in one file?

A small program can use a CSV, JSON or another ordinary file. That can be perfectly reasonable. The challenge grows when thousands of records, relationships, repeated queries and several simultaneous users must be managed safely.

A useful distinction

A file stores data. A database system is designed to manage structured data and operations around it—such as queries, relationships, constraints, indexes and transactions. The right choice depends on the problem, not on whether one tool sounds more advanced.

Follow one real request: a student checks examination results

Imagine Amina opens a school portal and presses View My Results. The final result may appear in a moment, but several parts of the application cooperate behind that button.

1. Frontend

The browser detects Amina’s click. It knows the interface needs examination data that is not currently on the screen.

2. Backend

The frontend sends a request. The backend identifies the signed-in account, checks permission and decides which records are required.

3. Database system

The backend performs the required database operation. The DBMS finds matching records and returns the result.

results-query.sql
SELECT subject, score FROM results WHERE student_id = 1042;

The purpose of the query here is not to turn this into a SQL tutorial. It simply means: find the result records belonging to student 1042. NIT’s separate SQL guide covers querying in more depth. [11]

result_idstudent_idsubjectscore
5011042Mathematics78
5021042English71
5031043Mathematics82

On a phone, swipe the table sideways to view every column.

  1. Amina clicks: the frontend detects the action.
  2. The request is sent: the backend receives the request.
  3. Identity and permission are checked: the application decides what Amina may see.
  4. The database is queried: matching result records are requested.
  5. The DBMS returns data: Mathematics 78 and English 71 match student 1042.
  6. The backend prepares the response: only the required information is returned.
  7. The frontend displays it: the records become a readable results table.

The mental model to keep

User → Frontend → Backend/API → Database system → Backend → Frontend → User. The database is one layer in a larger application, not the whole application.

How does a relational database organise information?

A relational database organises information in tables. Each table consists of rows and columns, and each column has a data type that limits and gives meaning to the values it can contain. [2]

student_idstudent_namephone
101Amina0712 345 678
102Brian0700 123 456

Table

A structured collection of one kind of related record, such as students or payments.

Row

One record. In the table above, the row beginning with 101 represents Amina.

Column

One defined property, such as student_name, phone or a date.

Data type

The allowed kind of value, such as integer, numeric, text, date or timestamp. [2]

Primary key

A column or set of columns used to identify each row uniquely.

Schema

The defined structure of the database: tables, columns, data types, relationships and rules.

Why use an ID instead of a person’s name?

Two students can share the same name. Their database records still need to remain distinct.

student_idstudent_name
102Brian Otieno
587Brian Otieno

A primary key provides a reliable row identifier. PostgreSQL’s documentation describes primary-key values as unique and non-null. [3]

Beginner rule

Names describe people. IDs identify records.

How do database tables connect?

A real school system might have separate tables for students, courses, enrolments, payments and results. Relationships connect those records without copying the same information everywhere.

payment_idstudent_idamount
7001101KSh 10,000
7002101KSh 8,000
7003102KSh 15,000

The repeated value student_id = 101 links two payment records to Amina. This creates a one-to-many relationship: one student can have many payments.

A foreign-key constraint can require a referenced parent record to exist, helping preserve referential integrity. [3]

Why not put everything in one huge table?

A single table with Payment 1, Payment 2, Payment 3, Result 1, Result 2 looks simple only until the real system changes. A learner may make a fourth payment, take another course or accumulate more results. Separating real-world entities into related tables gives the data a structure that can grow.

Turn a real-world sentence into a database model

Identify the things

“A student can enrol in a course.” The important entities are Student and Course.

Identify the relationship

A course can have many students, and a student may take more than one course over time.

Create the bridge

An Enrolment record can connect one student record to one course record.

Keep each record focused

The student describes the learner, the course describes the programme, and the enrolment describes the connection.

Database thinking

Identify the things → identify their properties → identify how they relate.

What happens inside a DBMS after a query arrives?

“The database searches the table” is useful for a first explanation, but a real database management system does more. PostgreSQL, for example, creates an execution plan that can use different scan and join strategies depending on the query and available structures. [4]

  1. Receive: the DBMS receives a query or data operation.
  2. Understand: it identifies the referenced tables, columns, conditions and operation.
  3. Plan: the query planner chooses how to access and combine the required information.
  4. Locate: the system reads the relevant data, potentially using an index where appropriate.
  5. Check rules: constraints, data types, permissions and transaction rules still apply.
  6. Read or change: the required operation is performed.
  7. Return: the result or operation status goes back to the application.

What is an index?

An index is an additional data structure that can help a database find suitable rows faster. PostgreSQL’s documentation notes that indexes can substantially improve retrieval speed for suitable queries, but they also add overhead and must be used sensibly. [5]

More indexes are not automatically better

Indexes consume storage and must be maintained when data changes. Good indexing starts with the queries and workloads the application actually needs.

How does a database stop bad data?

A serious application should not accept every possible value blindly. Relational database constraints can protect important rules at the data layer. PostgreSQL documents CHECK, NOT NULL, UNIQUE, primary-key and foreign-key constraints. [3]

Required value

A student ID can be required instead of allowing it to be empty.

Unique value

An official student number can be prevented from appearing twice when uniqueness is required.

Valid relationship

A payment can be prevented from pointing to a student record that does not exist.

Valid range

A CHECK constraint can enforce a rule such as price > 0. [3]

This is part of data integrity: keeping stored information consistent with the rules of the system.

CRUD and transactions: how applications change data safely

Most database-driven applications repeatedly perform four basic categories of operation, often summarised as CRUD.

Create

Add information—for example, register a new student or create a new order.

Read

Retrieve information—for example, display a student profile or check stock.

Update

Change existing information—for example, update a phone number or reduce stock.

Delete

Remove information when deletion is permitted by the application’s rules.

Practical example: an online shop

A product starts with 24 school bags. A customer orders two. The application may need to check stock, create the order and reduce stock to 22.

Read product

Confirm product 301 exists.

Read stock

Current stock is 24; requested quantity is 2.

Create order

Record the accepted order and its items.

Update stock

24 − 2 = 22. Save the new stock level and return confirmation.

What if the application fails halfway?

A transaction groups related operations into one logical unit. The essential idea is all or nothing: if a failure prevents the transaction from completing, its partial changes do not have to remain applied. [6]

Transaction mental model

Everything succeeds → COMMIT. Something fails → ROLLBACK. Transactions are especially important for payments, stock, bookings and other multi-step changes.

What happens when two users act at the same time?

Imagine one appointment slot: Tuesday at 10:30 a.m. Amina and Brian both open the booking page. Both initially see it as available, then both press Book almost together.

  1. Amina checks: the slot appears free.
  2. Brian checks: the slot also appears free to him.
  3. Amina submits: her booking reaches the server.
  4. Brian submits: his request arrives close behind.
  5. The database must preserve the rule: one slot cannot end up with two accepted bookings.

This is a concurrency problem. Transactions are designed so incomplete changes are not exposed as if they were final, and database designs can combine transactions, constraints and appropriate concurrency controls to preserve important rules. [6]

The screen can be stale

“It looked available on my screen” does not guarantee that it is still available when the database receives the request. Real applications must handle that possibility.

Database vs DBMS vs SQL vs backend vs API

TermMain roleBeginner example
DatabaseOrganised persistent dataStudents, payments and results
DBMSSoftware that manages databasesExecutes queries, constraints and transactions
SQLLanguage commonly used with relational databasesSELECT matching results
BackendApplication logicChecks identity, permission and business rules
APISoftware communication interfaceDefines how a frontend requests results
FrontendUser interfaceDisplays the final results table

On a phone, swipe the table sideways to view every column.

Keep the layers separate

Backend ≠ database. API ≠ database. SQL ≠ database. These parts cooperate, but each has a different responsibility.

Database vs spreadsheet

NeedSpreadsheetDatabase
Calculations and chartsExcellentUsually handled by another layer
Small personal listOften idealMay be unnecessary
Connected operational dataCan become awkwardStrong fit
Relationships and constraintsLimitedCore capability
Application-driven transactionsNot its primary purposeCore capability

Microsoft’s guidance similarly distinguishes spreadsheets as especially useful for numerical analysis, while database software is better suited to organising, searching and managing relational data for multi-user scenarios. [7]

Does every website need a database?

No. A static website can simply serve files. A database becomes useful when a site or application must persist and manage changing information such as accounts, orders, messages, bookings or enrolments. [13]

Where is a database stored?

A database can run on a user’s device, a local computer, an organisation’s own server, a private network or cloud infrastructure. Browsers also provide local database technology such as IndexedDB. [8]

Are all databases made of tables?

No. Tables are central to relational databases, but modern applications use several database models. Current database guidance distinguishes relational systems from document, key-value, graph, vector and time-series approaches. [1]

Database modelBasic ideaExample use
RelationalTables, rows, columns and relationshipsOrders, enrolments, finance
DocumentDocument-like recordsFlexible content and catalogues
Key-valueValues retrieved through keysSessions and fast lookups
GraphNodes and relationshipsHighly connected networks
VectorNumerical representations used for similarity searchSemantic search and recommendations
Time-seriesValues organised around timeSensors and monitoring

On a phone, swipe the table sideways to view every column.

What should a beginner learn first?

Start with tables → rows → columns → keys → relationships → queries. Those relational ideas make the foundations visible before you move into specialised database models.

Does using a database automatically make an application secure?

No. A database is only one part of application security. Developers still have to design authentication, authorisation, safe queries, permissions, password protection, backups and recovery deliberately.

Authentication

Who are you? The application establishes the identity associated with a request.

Authorisation

What may you access? Knowing a record ID does not mean a user should be allowed to read it.

Safe queries

OWASP recommends parameterised queries so untrusted values remain data rather than becoming SQL syntax. [9]

Least privilege

Application database accounts should receive only the access they genuinely need. [9]

Password storage

Passwords should not be stored as readable plaintext; modern guidance recommends dedicated password-hashing methods. [10]

Recovery

Important systems also need appropriate backups and a tested way to recover after failures.

A database is not a security guarantee

Security comes from the whole system: the application, database permissions, queries, authentication, infrastructure and operational practices must work together.

Try it yourself: design a small school database

Do not start with code. Start with the real-world things you need to represent.

students

student_id
student_name
phone

courses

course_id
course_name

enrolments

enrolment_id
student_id
course_id

payments

payment_id
student_id
amount
payment_date

school-data-model.txt
students 1 ─────< enrolments >───── 1 courses │ └──────────────< payments

Test the design with questions

  1. Which course is Amina taking? Follow students → enrolments → courses.
  2. How much has Amina paid? Follow students → payments and total the matching rows.
  3. Can two students have the same name? Yes. Their unique IDs still distinguish them.
  4. Can a payment point to a student who does not exist? A foreign-key rule can prevent that relationship. [3]
  5. What if one student takes two courses? Create two enrolment records rather than copying the whole student record.

A practical learning path

Data foundations

Tables, rows, columns, data types and records.

Identity + relationships

Primary keys, foreign keys and one-to-many or many-to-many relationships.

SQL + integrity

SELECT, INSERT, UPDATE, DELETE, filtering, joins, constraints and transactions.

Complete application

Connect frontend → backend/API → database → response, then add testing, security and performance work.

NIT’s SQL for Data Analysis guide is the natural next step for deeper relational querying. [11]

The three database models to remember

Application flow

User → Frontend → Backend/API → Database system → Backend → Frontend → User

Relational structure

Tables → Rows → Columns → Keys → Relationships → Queries

DBMS work

Receive → Understand → Plan → Locate → Check rules → Read/change → Return

If you understand those three flows, you already understand much of the foundation needed to begin working with databases in real applications.

Database FAQs for beginners

What is a database in simple terms?

A database is an organised collection of related data that can be stored, retrieved and managed. A DBMS is the software that manages that data.

What is an example of a database?

A school database could contain students, courses, enrolments, results, attendance and payments. An online shop database might contain customers, products, orders and stock.

Is SQL a database?

No. SQL is a language commonly used to perform operations on relational database systems.

What is a DBMS?

A database management system is software that creates, stores, retrieves, updates and controls database data and operations.

What is the difference between a database and a table?

A database can contain several tables. A table contains one structured category of related records.

What is a primary key?

A primary key identifies each row uniquely and uses unique, non-null values. [3]

What is a foreign key?

A foreign key references a key in another table and helps preserve valid relationships between records. [3]

Is Excel a database?

Excel is spreadsheet software. Spreadsheets can store tabular data, but database systems are specialised for structured data management, relationships, constraints, queries and transactions. [7]

Does every website need a database?

No. A static website may work entirely from files. A database becomes useful when the application must persist and manage changing information such as accounts, orders, messages or bookings.

Where is a database stored?

A database can run locally, on an organisation’s server, inside a private network, on a user’s device or on cloud infrastructure. The hosting arrangement is separate from the database model.

What database should a beginner learn first?

A relational database is a strong starting point because it exposes tables, data types, keys, relationships, SQL, constraints, indexes and transactions clearly.

Build with real data

Register for Full-Stack Software Development at NIT

Learn how front-end interfaces, backend logic, APIs and databases work together in complete applications. NIT’s current programme includes SQL and NoSQL databases, data modelling, application integration, testing and full-stack project development. [12]

Institutional training8 months

Industrial attachmentAt least 3 months

Current published tuitionKSh 90,000

References and further reading

Information checked: 1 November 2026.

Editorial responsibility: Newton Institute of Technology Editorial Team.