Combined Bank Assistant Programmer Exam 17 January 2026 — Question Solution | সমন্বিত ব্যাংক (ব্যাংকার্স সিলেকশন কমিটি) সহকারী প্রোগ্রামার নিয়োগ পরীক্ষা ২০২৬ — প্রশ্ন সমাধান

Delivering a packet across networks needs two things at the network layer: IP addressing (where the destination is) and routing (which way to send it). OSPF (Open Shortest Path First) builds every router's routing table, and each router then forwards packets hop by hop using that table.

1. IP addressing: which network is the destination on?

An IPv4 address has a network part and a host part, separated by the subnet mask (prefix). A host ANDs an address with the mask to find its network:

• PC-A = 192.168.1.10/24 → network 192.168.1.0
• PC-D = 192.168.4.20/24 → network 192.168.4.0

The networks differ, so PC-A does not deliver the packet directly. It sends it to its default gateway (router R1, 192.168.1.1), after using ARP to learn R1's MAC address.

2. OSPF: how routers learn the best path

OSPF is an open-standard, classless link-state interior gateway protocol (OSPFv2, RFC 2328, for IPv4; OSPFv3, RFC 5340, for IPv6).

1. Neighbours: routers send Hello packets (every 10 s on Ethernet by default) and form adjacencies.
2. Flooding: each router describes its links (subnets, costs, neighbours) in Link-State Advertisements (LSAs) and floods them through the area.
3. LSDB: every router in the area builds the same link-state database, a complete map of the area.
4. SPF: each router runs Dijkstra's shortest-path-first algorithm with itself as the root and installs the lowest-cost route to every network in its routing table.

Metric: cost = reference bandwidth ÷ interface bandwidth (default reference 100 Mbps, so a 100 Mbps link costs 1 and a 10 Mbps link costs 10). The route cost is the sum of the outgoing interface costs, not the hop count.

3. Example topology (single area, Area 0)

graph LR
    PCA["PC-A 192.168.1.10/24"] --- R1(("R1"))
    R1 ---|"cost 10 (10 Mbps)"| R2(("R2"))
    R1 ---|"cost 1"| R3(("R3"))
    R3 ---|"cost 1"| R2
    R2 ---|"cost 1"| R4(("R4"))
    R3 ---|"cost 10 (10 Mbps)"| R4
    R4 --- PCD["PC-D 192.168.4.20/24"]

Link / LAN	Subnet	Addresses	OSPF cost
R1 LAN	192.168.1.0/24	R1 .1, PC-A .10	1
R1–R2	10.0.12.0/30	R1 .1, R2 .2	10
R1–R3	10.0.13.0/30	R1 .1, R3 .2	1
R2–R3	10.0.23.0/30	R2 .1, R3 .2	1
R2–R4	10.0.24.0/30	R2 .1, R4 .2	1
R3–R4	10.0.34.0/30	R3 .1, R4 .2	10
R2 LAN / R3 LAN / R4 LAN	192.168.2.0/24, 192.168.3.0/24, 192.168.4.0/24	router .1	1 each

R1's SPF calculation (Dijkstra from R1):

• R3: direct, cost 1.
• R2: direct link costs 10, but via R3 it costs 1 + 1 = 2, so R2 is reached via R3.
• R4: via R3 → R2 costs 2 + 1 = 3; via R1 → R2 → R4 costs 10 + 1 = 11; via R3's direct link 1 + 10 = 11. Best = 3 via R3.

OSPF therefore prefers the 3-hop path (cost 3) to the 2-hop path R1 → R2 → R4 (cost 11) that a hop-count protocol such as RIP would pick.

R1's routing table (O = OSPF, [administrative distance/cost]):

Code	Destination	[AD/cost]	Next hop
C	192.168.1.0/24	—	directly connected (LAN)
C	10.0.12.0/30	—	directly connected (to R2)
C	10.0.13.0/30	—	directly connected (to R3)
O	192.168.3.0/24	[110/2]	10.0.13.2 (R3)
O	10.0.23.0/30	[110/2]	10.0.13.2 (R3)
O	192.168.2.0/24	[110/3]	10.0.13.2 (R3)
O	10.0.24.0/30	[110/3]	10.0.13.2 (R3)
O	192.168.4.0/24	[110/4]	10.0.13.2 (R3)
O	10.0.34.0/30	[110/11]	10.0.13.2 (R3)

4. Hop-by-hop delivery of one packet (PC-A → PC-D)

Step	Device	Decision	Packet leaves with
1	PC-A	192.168.4.0 ≠ own network 192.168.1.0 → send to gateway R1	Src IP 192.168.1.10, Dst IP 192.168.4.20, TTL 64; frame to R1's MAC
2	R1	Longest-prefix match → 192.168.4.0/24 via 10.0.13.2	TTL 63; new frame to R3's MAC
3	R3	Match 192.168.4.0/24 via 10.0.23.1 (cost 3, not its own 10 Mbps link, cost 11)	TTL 62; new frame to R2's MAC
4	R2	Match 192.168.4.0/24 via 10.0.24.2	TTL 61; new frame to R4's MAC
5	R4	192.168.4.0/24 is directly connected → ARP for 192.168.4.20	TTL 60; frame to PC-D's MAC, delivered

Key points of forwarding:

• The source and destination IP addresses stay the same end to end (without NAT); the MAC addresses change at every hop because each link carries a new frame.
• Each router decrements TTL (at 0 the packet is discarded, which stops loops) and recomputes the IPv4 header checksum.
• Each router decides independently, but all share the same LSDB, so their SPF results agree and the path is loop-free. OSPF only builds the tables (control plane); the lookup is the data plane.

5. When a link fails

If the R2–R3 link fails, R2 and R3 flood new LSAs and every router reruns SPF. R1's route to 192.168.4.0/24 becomes [110/12] with two equal-cost next hops, 10.0.12.2 (via R2) and 10.0.13.2 (via R3), and OSPF load-balances across both. Only an LSA flood and an SPF run are needed, so convergence is fast.

Key OSPF facts

Property	Value
Carried in	IP directly, protocol number 89 (not TCP or UDP)
Multicast addresses	224.0.0.5 (all OSPF routers), 224.0.0.6 (DR/BDR)
Packet types	Hello, DBD, LSR, LSU, LSAck
Hierarchy	Areas; Area 0 is the backbone, other areas join it through Area Border Routers
Administrative distance	110 (Cisco)

Exam tip: cover three layers: the host's mask/gateway decision; OSPF's LSA → LSDB → Dijkstra → routing table; and the hop-by-hop lookup (IP stays, MAC changes, TTL drops). Choose a topology where the lowest-cost path is not the fewest-hop path.

Answer: Many-to-Many (M:N) between Book and Borrower, when the database keeps the borrowing history, as a library database normally does.

Justification: read the relationship in both directions

• One borrower → many books: a member can borrow several books at once and many more over the years.
• One book → many borrowers: a title (or a copy) is lent to one member, returned, then lent to another; over time it is linked to many members.

"Many" holds on both sides, so the relationship is M:N. It is not 1:1, because that would allow a member only one book in a lifetime and a book only one reader. It is not 1:N as long as we record past loans.

Resolving M:N: a junction table. A relational table cannot store an M:N link directly: one foreign-key cell holds only one value. So we add a LOAN (Borrowing/Issue) table that holds a foreign key to each side. The M:N becomes two 1:N relationships, and the loan's own facts (issue date, due date, return date, fine) live there, since they belong to neither a book nor a borrower alone.

ER diagram

erDiagram
    BORROWER ||--o{ LOAN : "makes"
    BOOK ||--o{ LOAN : "is lent in"
    BOOK {
        int book_id PK
        string isbn UK
        string title
        string author
        int copies
    }
    BORROWER {
        int borrower_id PK
        string name
        string phone UK
        date member_since
    }
    LOAN {
        int loan_id PK
        int book_id FK
        int borrower_id FK
        date issue_date
        date due_date
        date return_date
    }

Conceptually: BOOK (M) —— borrows —— (N) BORROWER.

Table schemas (SQL)

CREATE TABLE Book (
  book_id   INTEGER PRIMARY KEY,
  isbn      VARCHAR(13) NOT NULL UNIQUE,
  title     VARCHAR(200) NOT NULL,
  author    VARCHAR(120) NOT NULL,
  copies    INTEGER NOT NULL CHECK (copies >= 0)
);
CREATE TABLE Borrower (
  borrower_id  INTEGER PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  phone        VARCHAR(15) UNIQUE,
  member_since DATE NOT NULL
);
CREATE TABLE Loan (
  loan_id     INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES Book(book_id),
  borrower_id INTEGER NOT NULL REFERENCES Borrower(borrower_id),
  issue_date  DATE NOT NULL,
  due_date    DATE NOT NULL,
  return_date DATE,              -- NULL = not yet returned
  UNIQUE (book_id, borrower_id, issue_date)
);

A surrogate loan_id is used because the same member may borrow the same book again later; a plain composite key (book_id, borrower_id) would block that.

Sample data

loan_id	book_id	borrower_id	issue_date	return_date
1	1 (The C Programming Language)	101 (Rahim)	2026-01-02	2026-01-12
2	2 (Introduction to Algorithms)	101 (Rahim)	2026-01-05	NULL
3	1 (The C Programming Language)	102 (Karima)	2026-01-14	NULL

• Rahim (101) → books 1 and 2: one borrower, many books.
• Book 1 → Rahim and Karima: one book, many borrowers.

SELECT b.title FROM Loan l JOIN Book b ON b.book_id = l.book_id
WHERE l.borrower_id = 101 ORDER BY l.issue_date;

Result: The C Programming Language, Introduction to Algorithms. A loan for a non-existent book_id is rejected with "FOREIGN KEY constraint failed".

When would it be 1:N? If "Book" means one physical copy and only the current holder is stored, each copy is with at most one borrower at a time while a borrower may hold several copies: Borrower 1 : N Book copy, done with a nullable borrower_id FK in the copy table. That loses all history, so real designs keep the LOAN table (often with a BOOK 1 : N COPY table so each copy is tracked).

Exam tip: test both directions ("one X → many Y?"), conclude M:N, and always show the junction table that turns it into two 1:N links.