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.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
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.
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_id | student_id | subject | score |
|---|---|---|---|
| 501 | 1042 | Mathematics | 78 |
| 502 | 1042 | English | 71 |
| 503 | 1043 | Mathematics | 82 |
On a phone, swipe the table sideways to view every column.
- Amina clicks: the frontend detects the action.
- The request is sent: the backend receives the request.
- Identity and permission are checked: the application decides what Amina may see.
- The database is queried: matching result records are requested.
- The DBMS returns data: Mathematics 78 and English 71 match student 1042.
- The backend prepares the response: only the required information is returned.
- 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_id | student_name | phone |
|---|---|---|
| 101 | Amina | 0712 345 678 |
| 102 | Brian | 0700 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_id | student_name |
|---|---|
| 102 | Brian Otieno |
| 587 | Brian 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_id | student_id | amount |
|---|---|---|
| 7001 | 101 | KSh 10,000 |
| 7002 | 101 | KSh 8,000 |
| 7003 | 102 | KSh 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]
- Receive: the DBMS receives a query or data operation.
- Understand: it identifies the referenced tables, columns, conditions and operation.
- Plan: the query planner chooses how to access and combine the required information.
- Locate: the system reads the relevant data, potentially using an index where appropriate.
- Check rules: constraints, data types, permissions and transaction rules still apply.
- Read or change: the required operation is performed.
- 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.
- Amina checks: the slot appears free.
- Brian checks: the slot also appears free to him.
- Amina submits: her booking reaches the server.
- Brian submits: his request arrives close behind.
- 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
| Term | Main role | Beginner example |
|---|---|---|
| Database | Organised persistent data | Students, payments and results |
| DBMS | Software that manages databases | Executes queries, constraints and transactions |
| SQL | Language commonly used with relational databases | SELECT matching results |
| Backend | Application logic | Checks identity, permission and business rules |
| API | Software communication interface | Defines how a frontend requests results |
| Frontend | User interface | Displays 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
| Need | Spreadsheet | Database |
|---|---|---|
| Calculations and charts | Excellent | Usually handled by another layer |
| Small personal list | Often ideal | May be unnecessary |
| Connected operational data | Can become awkward | Strong fit |
| Relationships and constraints | Limited | Core capability |
| Application-driven transactions | Not its primary purpose | Core 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 model | Basic idea | Example use |
|---|---|---|
| Relational | Tables, rows, columns and relationships | Orders, enrolments, finance |
| Document | Document-like records | Flexible content and catalogues |
| Key-value | Values retrieved through keys | Sessions and fast lookups |
| Graph | Nodes and relationships | Highly connected networks |
| Vector | Numerical representations used for similarity search | Semantic search and recommendations |
| Time-series | Values organised around time | Sensors 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_idstudent_namephone
courses
course_idcourse_name
enrolments
enrolment_idstudent_idcourse_id
payments
payment_idstudent_idamountpayment_date
Test the design with questions
- Which course is Amina taking? Follow students → enrolments → courses.
- How much has Amina paid? Follow students → payments and total the matching rows.
- Can two students have the same name? Yes. Their unique IDs still distinguish them.
- Can a payment point to a student who does not exist? A foreign-key rule can prevent that relationship. [3]
- 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
- [1] Google Cloud — What is a database?. Database definition, application use and current database-model overview.
- [2] PostgreSQL 18 Documentation — Table Basics. Primary technical reference for relational tables, rows, columns and data types.
- [3] PostgreSQL 18 Documentation — Constraints. Primary keys, foreign keys, uniqueness, not-null rules, check constraints and referential integrity.
- [4] PostgreSQL 18 Documentation — EXPLAIN. Execution plans, sequential scans, index scans and join strategies.
- [5] PostgreSQL 18 Documentation — Indexes. Index performance benefits and maintenance overhead.
- [6] PostgreSQL 18 Tutorial — Transactions. Atomic operations, commits, rollbacks and concurrent visibility.
- [7] Microsoft Support — Using Access or Excel to manage your data. Spreadsheet analysis versus relational data-management use cases.
- [8] MDN Web Docs — Database glossary. Web-development database overview and browser-side IndexedDB.
- [9] OWASP Cheat Sheet Series — SQL Injection Prevention. Parameterised queries and least-privilege database access.
- [10] OWASP Cheat Sheet Series — Password Storage. Password hashing and avoiding plaintext password storage.
- [11] Newton Institute of Technology — SQL for Data Analysis. Internal next-step guide for relational querying, filtering, joins and analysis.
- [12] Newton Institute of Technology — Full-Stack Software Development Course. Current programme duration, tuition and database/application-development coverage.
- [13] MDN Web Docs — What is a web server?. Static and dynamic web-server concepts.
Information checked: 1 November 2026.
Editorial responsibility: Newton Institute of Technology Editorial Team.