Features of a Relational Database
What Is a Relational Database?
A relational database is a structured collection of data that is organised into tables with predefined relationships.
Data is stored in rows and columns, with unique identifiers known as keys used to link and manage related data across the database.
Flat File Databases
Definition
A flat file database stores everything in a single table (or simple file). It is easy to set up, but because all facts live together (passengers, flights, seats, etc.), the same information is repeated many times. This repetition leads to errors and extra work as the data grows.
Storing Data (One Big Table)
Suppose we record airline bookings in one table. Each row repeats passenger and flight details:
| BookingID | PassengerName | PassportNo | FlightNo | FlightDate | Origin | Destination | Seat |
|---|---|---|---|---|---|---|---|
| BK-1001 | Aisha Khan | A123456 | BA145 | 2025-02-12 | LHR | JFK | 12C |
| BK-1002 | Aisha Khan | A123456 | BA146 | 2025-02-20 | JFK | LHR | 18A |
| BK-1003 | Ben Li | B987654 | BA145 | 2025-02-12 | LHR | JFK | 14F |
Shading shows repeated facts: grey = passenger details repeated per booking; amber = flight details repeated per booking.
Data Operations in a Flat File (Anomalies)
In a single “everything-in-one-table” design, everyday actions like adding a flight, changing a passport number, or removing a booking can create insertion, update, and deletion anomalies. The examples (tabs) below illustrate these pitfalls and why a better (relational) design (separate tables with primary/foreign keys) avoids them.
Inserting Data
With one big table, adding a new flight is awkward if there is no passenger yet. You either insert nothing (and lose the flight info) or you insert a “placeholder” row with empty passenger fields-both are poor choices.
| BookingID | PassengerName | PassportNo | FlightNo | FlightDate | Origin | Destination | Seat |
|---|---|---|---|---|---|---|---|
| - | (empty) | (empty) | BA200 | 2025-03-01 | LHR | MAD | (empty) |
Insertion anomaly: You cannot store a flight on its own without inventing a booking row.
Updating Data
If a passenger’s passport number changes, you must update every row for that passenger. If you miss one, the table becomes inconsistent.
| BookingID | PassengerName | PassportNo (old) | PassportNo (new) | FlightNo | Seat |
|---|---|---|---|---|---|
| BK-1001 | Aisha Khan | A123456 | A555999 | BA145 | 12C |
| BK-1002 | Aisha Khan | A123456 | (not updated) | BA146 | 18A |
Update anomaly: The same real-world change must be made in many places; missing one creates conflicting data.
Deleting Data
If a passenger cancels one flight, you delete that row. That seems fine-unless it was their only booking; then you lose the passenger’s details entirely. Likewise, if the last booking for a flight is deleted, you lose the flight.
Before
| BookingID | PassengerName | PassportNo | FlightNo |
|---|---|---|---|
| BK-2001 | Charlie Wong | C222333 | BA200 |
Charlie has exactly one booking.
After deleting the booking
| BookingID | PassengerName | PassportNo | FlightNo |
|---|---|---|---|
| No rows remain | |||
Deletion anomaly: Removing the only booking also removes Charlie’s passenger data.
Relational Databases
A relational database separates repeated facts into their own tables (e.g. Passengers, Flights) and links them with a small “join” table (e.g. Bookings). This reduces repetition and avoids insertion, update, and deletion anomalies. We will cover how this is enforced (keys, constraints, normalisation) later-below is the idea:
| PassengerID (PK) | Name | PassportNo |
|---|---|---|
| P001 | Aisha Khan | A555999 |
| P002 | Ben Li | B987654 |
Each passenger is stored once.
| FlightNo (PK) | Date | From→To |
|---|---|---|
| BA145 | 2025-02-12 | LHR→JFK |
| BA146 | 2025-02-20 | JFK→LHR |
Each flight is stored once.
| PassengerID (FK) | FlightNo (FK) | Seat |
|---|---|---|
| P001 | BA145 | 12C |
| P001 | BA146 | 18A |
| P002 | BA145 | 14F |
Bookings links passengers to flights with foreign keys (FKs), avoiding repetition.
In short: flat files are simple but fragile at scale; relational databases organise data to keep it consistent as it grows.
Key Terminology of a Relational Database
Entity
An entity type (e.g. Student, Book) is usually implemented as a table, and each entity instance becomes a row.
Examples
| StudentID (PK) | Name | DateOfBirth |
|---|---|---|
| 1023 | Amira Khan | 2007-09-14 |
| 1041 | J. Patel | 2006-12-03 |
Here, StudentID uniquely identifies each student.
| ISBN (PK) | Title | Author |
|---|---|---|
| 9780140449136 | The Odyssey | Homer |
| 9780131103627 | The C Programming Language | Kernighan & Ritchie |
| 9780262033848 | Introduction to Algorithms | Cormen et al. |
ISBN is a natural primary key for books.
Field / Attribute
A field (attribute) is a single column within a table that stores one type of data for every record. Field choices reflect design (data type, constraints, uniqueness, indexing).
Examples
| CustomerID (PK) | EmailAddress | Name |
|---|---|---|
| 2001 | [email protected] | Alex Green |
| 2002 | [email protected] | Maya Smith |
EmailAddress could be unique or indexed to speed lookups.
| VehicleID (PK) | RegistrationNumber | MakeModel |
|---|---|---|
| 501 | AB12 CDE | Toyota Corolla |
| 502 | XY34 ZZZ | Ford Focus |
RegistrationNumber is an attribute; enforcing uniqueness avoids duplicates.
Record
A record (row) is one complete set of field values describing a single entity instance.
Examples
| StudentID | Name | DateOfBirth |
|---|---|---|
| 1023 | Amira Khan | 2007-09-14 |
This single highlighted row is one record.
| OrderID | OrderDate | Status |
|---|---|---|
| 4581 | 2024-03-18 | Shipped |
Each row captures all values for one order.
Tables
A table stores records of the same entity type. Columns are fields; rows are records. Tables organise data for efficient storage, querying, and integrity.
Examples
| EmployeeID (PK) | Name | HireDate |
|---|---|---|
| 301 | S. Jones | 2021-08-01 |
| 302 | L. Chen | 2022-02-15 |
| PaymentID (PK) | Amount | PaymentMethod |
|---|---|---|
| 9001 | £49.00 | Card |
| 9002 | £15.50 | Cash |
Primary Keys
A primary key (PK) is one field (or combination) that uniquely identifies a record in its table. No two rows share the same PK value; PKs are not NULL.
Examples
| StudentID (PK) | Name |
|---|---|
| 1023 | Amira Khan |
| 1041 | J. Patel |
StudentID is unique and never NULL-ideal as a PK.
| InvoiceNumber (PK) | InvoiceDate |
|---|---|
| INV-2024-001 | 2024-01-10 |
| INV-2024-002 | 2024-02-02 |
Each InvoiceNumber points to exactly one invoice.
Foreign Keys
A foreign key (FK) is a field in one table that refers to the primary key of another table, creating relationships and enforcing integrity (no orphan rows).
Examples
| StudentID (FK) | CourseCode | EnrolDate |
|---|---|---|
| 1023 | CS101 | 2024-09-02 |
| 1041 | MA201 | 2024-09-03 |
Enrolments.StudentID must match a valid Students.StudentID.
| StudentID (PK) | Name |
|---|---|
| 1023 | Amira Khan |
| 1041 | J. Patel |
The FK “points to” this PK to ensure referential integrity.
| OrderID | ProductID (FK) | Qty |
|---|---|---|
| 7001 | P-10 | 2 |
| 7001 | P-22 | 1 |
Each detail row references a product via the FK.
| ProductID (PK) | Name |
|---|---|
| P-10 | USB-C Cable |
| P-22 | HDMI Adapter |
The PK in Products guarantees referenced products exist.
Composite Keys
A composite key combines two (or more) fields to make a row unique-common in many-to-many join tables where no single field is unique on its own.
Examples
| StudentID (PK part) | CourseID (PK part) | Role |
|---|---|---|
| 1023 | CS101 | Learner |
| 1023 | MA201 | Learner |
| 1041 | CS101 | Learner |
The pair (StudentID, CourseID) is unique; either alone is not.
| PassengerID (PK part, FK) | FlightNumber (PK part, FK) | Seat |
|---|---|---|
| PX77 | BA145 | 12C |
| PX77 | BA146 | 18A |
Composite key (PassengerID, FlightNumber) uniquely identifies each booking.
| PassengerID (PK) | Name |
|---|---|
| PX77 | N. Clarke |
| FlightNumber (PK) | Date |
|---|---|
| BA145 | 2025-01-18 |
| BA146 | 2025-01-20 |
Both columns are also FKs pointing to PKs in their respective parent tables.
Benefits of Relational Databases
Data Integrity
Relational databases preserve the correctness of the data by enforcing rules such as referential integrity. These rules ensure that keys are unique and that links between tables are valid.
Example 1: A student record can't be added to the Enrolments table unless the corresponding StudentID exists in the Students table.
Example 2: Deleting a student from the Students table might automatically remove all matching enrolment records to prevent orphaned data, if cascading deletes are enabled.
Data Consistency
Consistency means that data is the same across all uses and tables. Using the 'enter once' principle, changes made in one part of the database (like updating a student's name) are reflected wherever that data is referenced.
Example 1: If a student's name is updated in the Students table, all enrolment records referencing that student continue to point to the same StudentID.
Example 2: Changing a course title in the Courses table updates all future enrolment records that reference the same course.
Reduced Data Duplication
Normalisation structures data across related tables to minimise redundancy and improve storage efficiency. By separating data into logical groups, the same information doesn’t have to be repeated multiple times across the database.
Example 1: Student details are stored once in a Students table and linked to multiple Enrolments.
Example 2: Course descriptions exist in a single Courses table instead of being repeated for every enrolment.
Reliable Transaction Processing
Relational databases support the ACID principles-Atomicity, Consistency, Isolation, Durability-to ensure that transactions are processed reliably and securely. If part of a transaction fails, the whole process is rolled back to prevent data corruption.
Example 1: In an e-commerce system, if a customer places an order, the payment, inventory update, and order record must all succeed together or none at all.
Example 2: Banking applications ensure that if money is deducted from one account, it must be credited to the other within the same transaction.
Scalability
Relational databases can efficiently manage increasing volumes of data while maintaining stable performance. Proper indexing and partitioning allow systems to scale to handle more users and larger datasets over time.
Example 1: A national health database can scale from thousands to millions of patient records.
Example 2: A university system managing course enrolments across multiple campuses can continue to perform well as student numbers grow.
Security Features
Relational databases offer built-in security mechanisms such as user authentication, role-based access control, and data encryption. These tools help prevent unauthorised access and data breaches.
Example 1: A teacher may only view their own class records, while administrators can access all student information.
Example 2: Password-protected access ensures that only verified staff members can modify sensitive fields such as exam grades.
Community Support
Popular relational platforms such as MySQL, PostgreSQL, and Oracle benefit from active communities that provide documentation, online forums, and expert troubleshooting help. This support shortens development time and simplifies maintenance.
Example 1: An error in a MySQL query can often be resolved with guidance from the community forum.
Example 2: Developers can access open-source plugins and extensions for performance tuning or additional features.
Limitations of Relational Databases
Big Data Scalability Issues
Relational databases are not designed to handle massive volumes of unstructured or high-velocity data efficiently. Scaling out horizontally is complex and often costly, making them less suitable for real-time big data environments.
Example 1: A video-sharing platform may switch to a NoSQL solution like Cassandra or MongoDB to store millions of uploads and comments per day.
Design Complexity
Creating an effective relational schema requires careful planning, particularly when modelling complex relationships. Poor design can lead to redundant data, inefficient queries, and maintenance issues.
Example 1: An e-commerce database without proper normalisation may store the same customer address in multiple tables, making updates tedious.
Example 2: A school timetable system that fails to structure courses, rooms, and teacher assignments relationally may struggle to generate accurate schedules.
Hierarchical Data Handling
Relational models are not inherently optimised for managing deeply nested or tree-like structures. Representing hierarchies often requires complex techniques (such as multiple joins).
Example 1: Displaying a product category tree in an online store (e.g. Electronics → Phones → Accessories) involves complex queries in SQL.
Rigid Schema
Relational databases require predefined schemas. Adding, removing, or changing columns can impact existing applications, queries, and stored procedures-especially in large deployments.
Example 1: A fitness app wanting to add StepGoal to its user table must update the schema and, possilby have to migrate or update legacy data.
Object-Relational Impedance Mismatch
There are different approaches between object-oriented programming (OOP) models and relational database models. OOP uses classes with, for example, inheritance and encapsulation. However, relational databases use tables with rows and foreign keys-making it hard to map objects to tables cleanly. This mismatch can lead to complex code when converting between objects in code and records in a database.
Example 1: In OOP, a class Car might inherit properties from a class Vehicle. In a relational database, you'd need separate Car and Vehicle tables and join them manually, making data retrieval more complex.
Unstructured Data Handling
Relational databases are not suited for storing unstructured data such as location data, images, videos, PDFs. While they can store binary large objects (BLOBs), performance and querying capabilities are limited.
Example 1: A content management system (CMS) storing PDFs, thumbnails, and user-uploaded files might rely on object storage like Amazon S3.
Example 2: A healthcare system storing radiology images might use a separate file server or NoSQL database to manage multimedia alongside patient records.
Comparison of Benefits and Limitations
| Category | Benefits | Limitations |
|---|---|---|
| Data Management | Ensures data integrity and reduced redundancy | Struggles with unstructured data |
| Scalability | Handles growing datasets efficiently | Challenges in scaling with big data |
| Flexibility | Supports reliable transaction processing | Rigid schema makes changes difficult |
| Security | Provides authentication and access control | Complex permission structures required |
Why Are Relational Databases Important?
- Structured Data Management: Ideal for organising data into clear, accessible formats.
- Reliable Transactions: Guarantees consistency and recovery after failure using ACID properties.
- Data Integrity: Maintains accuracy through relationships and key constraints.
- Widely Used: Essential for industries like finance, e-commerce, healthcare, and government systems.
Snapshot: Relational Features
| Feature | Plain-English definition | Why it matters | Mini example |
|---|---|---|---|
| Tables | Named grids (rows/columns) that hold one kind of thing. | Keeps data tidy and fast to search. | Students(StudentID, Name, DoB) |
| Primary keys (PK) | Column(s) with values that are unique and never empty. | Pinpoints one row exactly; avoids duplicates. | Students.StudentID = 1023 |
| Foreign keys (FK) | Column(s) that point to a PK in another table. | Links tables and enforces valid references. | Enrolments.StudentID → Students.StudentID |
| Composite keys | Using two+ columns together to make a unique key. | Prevents duplicates in join tables (many↔many). | (StudentID, CourseID) in Enrolments |
| Relationships | How rows relate across tables (1:1, 1:N, M:N). | Makes updates consistent and queries meaningful. | One student → many enrolments (1:N) |
Snapshot: Relationships
| Type | What it means | Typical design | Example |
|---|---|---|---|
| 1:1 | Each row matches at most one row in the other table. | Share the same PK or use a unique FK. | Students ↔ StudentCards |
| 1:N | One “parent” row links to many “child” rows. | Child table has FK to parent PK. | Courses → Enrolments |
| M:N | Many rows relate to many rows. | Use a join table with a composite key. | Students ↔ Courses via Enrolments |
Snapshot: Benefits
| Benefit | What it looks like in practice | Why it helps |
|---|---|---|
| Data integrity | FKs stop enrolments for non-existent students. | Prevents “orphan” rows and bad links. |
| Data consistency | Update a student’s name once; everywhere shows the new name. | Avoids conflicting copies of the same fact. |
| Reduced duplication / redundancy | Store course details once; link from many enrolments. | Saves space, reduces mistakes. |
| Concurrency control | Two admins editing different tables don’t corrupt each other’s work. | Safe multi-user changes. |
| Reliable transactions (ACID) | Order creation + stock update succeed or both roll back. | No half-done operations. |
| Data retrieval | Indexes speed up WHERE StudentID=… lookups. |
Fast reports and searches. |
| Scalability | Partition large tables; add read replicas. | Serves more users without big rewrites. |
| Security features | Roles limit who can see grades vs. names only. | Protects sensitive data. |
| Community support | MySQL/PostgreSQL docs, forums, plugins. | Quicker fixes and better patterns. |
Snapshot: Limitations and Consequences
| Limitation | Where you notice it | Typical workaround |
|---|---|---|
| “Big data” scalability | Massive, fast-arriving event logs slow queries. | Use sharding, warehousing, or NoSQL for that workload. |
| Design complexity | Poor schema causes slow joins and duplicate facts. | Normalise sensibly; review ERD before coding. |
| Hierarchical data | Product category trees need tricky joins. | Use adjacency lists, nested sets, or graph/JSON features. |
| Rigid schema | Adding a column requires migrations & app changes. | Plan schema; use nullable/JSON for optional data. |
| Object–relational mismatch | OOP inheritance doesn’t map neatly to tables. | Use ORMs carefully; choose a mapping strategy (TPH/TPT). |
| Unstructured data | Images/videos are clumsy as BLOBs. | Store files in object storage; keep metadata in the DB. |
Key Takeaways
- Relational databases store and manage structured data using tables and relationships.
- They offer strong data integrity, scalability, and security features.
- Common challenges include schema rigidity, scalability limits, and poor support for unstructured data.
- Used in a wide range of applications where accuracy, reliability, and consistency are crucial.