5.5 Relational Databases: Tables, Queries, and Reports

Introduction to Relational Databases

In the modern business landscape, data is one of the most valuable assets. However, raw, unorganized data is like crude oil—it’s full of potential but useless until it’s refined. A Relational Database Management System (RDBMS) is the refinery for business data. It provides a structured way to store, manage, and retrieve vast amounts of information, enabling informed decision-making across all departments. This model, based on organizing data into related tables, is the foundation of most business information systems today, from banking applications to e-commerce platforms. In this section, we will explore the three core components of a relational database: Tables, Queries, and Reports.

flowchart TB
    subgraph RDB["🗃️ Relational Database Structure"]
        direction TB

        subgraph Customers["👥 Customers Table"]
            C_PK["🔑 CustomerID (PK)"]
            C_Name["Name"]
            C_Phone["Phone"]
        end

        subgraph Orders["📝 Orders Table"]
            O_PK["🔑 OrderID (PK)"]
            O_FK["🔗 CustomerID (FK)"]
            O_Date["OrderDate"]
            O_Amt["Amount"]
        end

        subgraph Products["📦 Products Table"]
            P_PK["🔑 ProductID (PK)"]
            P_Name["ProductName"]
            P_Price["Price"]
        end
    end

    C_PK -->|"🔗 Links to"| O_FK
    O_PK <-.->|"Order Details"| P_PK

Figure: Relational Database - Tables linked by Primary and Foreign Keys

Detailed Content: The Core Components

A relational database organizes data into a collection of tables. The “relational” aspect comes from the ability to create links, or relationships, between these tables based on common data points.

1. Tables (Entities and Attributes)

A Table is the fundamental structure for storing data in a relational database. Each table represents a specific entity, which is a person, place, thing, or event that the business needs to track. For example, a university database might have tables for Students, Courses, and Instructors.

  • Rows (Records): Each row in a table represents a single instance of that entity. For example, in a Students table, each row would contain the information for one specific student. A row is also called a record or a tuple.
  • Columns (Fields): Each column represents a specific characteristic or attribute of the entity. In the Students table, columns might include StudentID, FirstName, LastName, Address, and PhoneNumber. A column is also called a field.

Keys: The Foundation of Relationships

Keys are special fields within a table that are crucial for identifying records and linking tables together.

  • Primary Key (PK): A primary key is a field (or a combination of fields) that uniquely identifies each record in a table. It cannot be empty (NULL) and its value must be unique for every row.
    • Example: In a Customers table, the CustomerID would be the primary key. In a Products table, ProductID would be the primary key.
  • Foreign Key (FK): A foreign key is a field in one table that is the primary key in another table. It acts as a cross-reference, creating a logical link or relationship between the two tables.
    • Example: Consider an Orders table. It would have its own primary key (OrderID). To know which customer placed the order, it would also include a CustomerID column. This CustomerID is a foreign key that links back to the primary key of the Customers table.

Visual Example:

Customers Table | CustomerID (PK) | FirstName | LastName | City | | ——————- | ——— | ———- | ——— | | 101 | Ram | Shrestha | Kathmandu | | 102 | Sita | Maharjan | Lalitpur |

Orders Table | OrderID (PK) | OrderDate | Amount | CustomerID (FK) | | —————- | ———- | —— | ——————- | | 5001 | 2023-10-26 | 1500 | 101 | | 5002 | 2023-10-27 | 3200 | 101 | | 5003 | 2023-10-27 | 850 | 102 |

Here, the CustomerID in the Orders table links each order to a specific customer in the Customers table.

2. Queries (Asking Questions of Your Data)

Storing data is only useful if you can retrieve it in a meaningful way. A Query is a request to the database for specific information. It’s like asking a precise question. The standard language used to write these queries is SQL (Structured Query Language).

While BBA students don’t need to be expert SQL programmers, it’s crucial to understand what queries can do. Common query actions include:

  • SELECT: Retrieve data from one or more tables.
  • INSERT: Add new records to a table.
  • UPDATE: Modify existing records in a table.
  • DELETE: Remove records from a table.

For business analysis, the SELECT query is the most powerful tool. It allows you to filter, sort, and combine data to find answers.

  • Simple Question: “Show me the names of all customers from Kathmandu.”
  • Complex Question: “Show me the total sales amount for each product category in the last quarter, sorted from highest to lowest.”

A query returns raw data that matches your criteria, often in a temporary, table-like format.

3. Reports (Presenting the Answers)

While a query provides the raw data, a Report is a formatted, organized, and often summarized presentation of that data. Reports are designed for human consumption and are used to support business decisions, analysis, and communication.

  • Query Result vs. Report: A query might return a list of 5,000 sales transactions. A report would take that data and present it as a “Monthly Sales Summary” with charts, graphs, totals, and averages, making it easy for a manager to understand performance at a glance.

Key characteristics of reports:

  • Formatted: They have professional layouts, headers, footers, and branding.
  • Summarized: They often include calculations like totals, averages, counts, and percentages.
  • Visual: They can incorporate charts and graphs to visualize trends.
  • Targeted: They are designed for a specific audience (e.g., a financial statement for investors, a sales dashboard for the marketing team).

Business Applications Across Functions

Relational databases are the backbone of information systems in every business department.

  • Finance & Accounting:
    • Tables: ChartOfAccounts, Transactions, Invoices, CustomerPayments.
    • Queries: “Find all unpaid invoices older than 90 days.” or “Calculate the total revenue for the fiscal year.”
    • Reports: Generating financial statements like the Income Statement, Balance Sheet, and Cash Flow Statement.
  • Human Resources (HR):
    • Tables: Employees, Departments, Salaries, Attendance, PerformanceReviews.
    • Queries: “List all employees who are eligible for a promotion based on their latest performance review score.” or “Identify departments with high employee turnover rates.”
    • Reports: Monthly payroll summaries, employee attendance reports, and annual performance analysis.
  • Marketing & Sales:
    • Tables: Customers, SalesLeads, MarketingCampaigns, Products, SalesTransactions.
    • Queries: “Identify the top 10% of customers by purchase value in the last year.” or “Which marketing campaign generated the most leads?”
    • Reports: Sales performance dashboards, campaign ROI (Return on Investment) analysis, and customer segmentation reports.
  • Operations & Supply Chain Management:
    • Tables: Products, Inventory, Suppliers, PurchaseOrders, Shipments.
    • Queries: “Generate a list of all products with an inventory level below the reorder point.” or “Find the average delivery time for each supplier.”
    • Reports: Inventory status reports, supplier performance scorecards, and production efficiency reports.

Real-World Examples from Nepal

  1. Banking Sector (e.g., NIC Asia Bank, Nabil Bank):
    • Banks rely heavily on relational databases to manage all customer and transaction data.
    • Tables: Customers (stores your personal info), Accounts (stores your account number, type, balance), and Transactions (stores every deposit, withdrawal, and transfer).
    • Relationship: Your CustomerID (Primary Key in Customers table) is used as a Foreign Key in the Accounts table to link you to your various accounts. Your AccountNumber is then used as a Foreign Key in the Transactions table.
    • Query: When you log into your mobile banking app and check your balance, the app sends a query to the database: SELECT Balance FROM Accounts WHERE AccountNumber = 'your_account_number'.
    • Report: Your monthly bank statement is a classic report, generated by querying all transactions for your account within a specific date range and presenting them in a structured, easy-to-read format.
  2. E-commerce (Daraz Nepal):
    • Daraz’s entire operation runs on a complex relational database system.
    • Tables: Users, Products, Sellers, Orders, Reviews.
    • Relationship: When you place an order, a new record is created in the Orders table. This record contains your UserID (a Foreign Key linking to the Users table) and the ProductID of the item you bought (a Foreign Key linking to the Products table).
    • Query: The “You might also like…” feature is powered by complex queries that analyze your past purchases and browsing history to find related products.
    • Report: Daraz provides its sellers with a dashboard, which is essentially a collection of real-time reports showing their sales figures, top-selling products, and inventory levels.
  3. Digital Wallets (eSewa, Khalti):
    • Similar to banks, digital wallets use relational databases to manage millions of users and transactions securely.
    • Tables: Users (name, phone number, KYC status), Merchants, Transactions (P2P transfers, utility payments, mobile top-ups).
    • Query: When you scan a merchant’s QR code, the app queries the database to verify the merchant’s details. When you enter an amount and your PIN, an INSERT query logs the transaction, and UPDATE queries adjust your balance and the merchant’s balance.
    • Report: The “Transaction History” or “Statement” section in the app is a report generated by querying all transactions linked to your UserID.

Key Takeaways

  • A relational database organizes data into linked tables to reduce redundancy and improve data integrity.
  • Tables store data about a single entity (like Customers or Products) in rows (records) and columns (fields).
  • Primary Keys uniquely identify each record in a table, while Foreign Keys link related tables together.
  • Queries, written in SQL, are used to ask specific questions and retrieve data from the database.
  • Reports are the final, formatted output of data, designed for analysis and decision-making by business users.
  • This structure is fundamental to nearly all modern business applications, from finance and HR to marketing and operations.

Review Questions

  1. Explain the difference between a Primary Key and a Foreign Key using a business example (e.g., a library system with Books and Borrowers tables).
  2. A marketing manager wants to send a promotional offer to all customers who have spent more than NPR 10,000 in the last 6 months. Is this task best accomplished using a table, a query, or a report? Explain your reasoning.
  3. Describe how a company like Nepal Telecom (NTC) might use tables, queries, and reports to manage its customer billing system.
  4. Why is a formatted report often more useful for a business manager than the raw data output from a query?