Combined Bank Assistant Database Administrator Exam 8 May 2026 — Question Solution | সমন্বিত ব্যাংক (ব্যাংকার্স সিলেকশন কমিটি) সহকারী ডাটাবেজ অ্যাডমিনিস্ট্রেটর নিয়োগ পরীক্ষা ২০২৬ — প্রশ্ন সমাধান

Query (standard form):

SELECT HospitalName
FROM   Hospital
WHERE  Specialization = 'Accident'
ORDER  BY PatientCount DESC
FETCH  FIRST 5 ROWS ONLY;      -- SQL:2008 standard; Oracle 12c+, PostgreSQL, Db2

How it works, clause by clause (logical order)

1. FROM Hospital reads the table.
2. WHERE Specialization = 'Accident' keeps only accident-treatment hospitals.
3. ORDER BY PatientCount DESC sorts them, highest patient count first.
4. FETCH FIRST 5 ROWS ONLY keeps the top five.
5. SELECT HospitalName displays only the names, as the question asks.

The same query in other DBMSs

DBMS	Row-limit syntax
MySQL, PostgreSQL, SQLite	... ORDER BY PatientCount DESC LIMIT 5;
SQL Server	SELECT TOP 5 HospitalName FROM Hospital WHERE ... ORDER BY PatientCount DESC;
Oracle 11g and older	SELECT HospitalName FROM (SELECT HospitalName FROM Hospital WHERE Specialization = 'Accident' ORDER BY PatientCount DESC) WHERE ROWNUM <= 5;

In old Oracle, the sort must be done in the inline view before ROWNUM is applied. Otherwise five arbitrary rows are taken first and then sorted.

Test run (sample data)

HospitalName	Specialization	PatientCount
Dhaka Trauma Centre	Accident	9500
City Heart Hospital	Cardiology	12000
Pongu Orthopaedic	Accident	8700
Chattogram Accident Care	Accident	8700
Sylhet Emergency	Accident	7200
Rajshahi Trauma	Accident	6100
Barishal Trauma Unit	Accident	6100
Khulna Accident Hospital	Accident	5400
Green Eye Clinic	Ophthalmology	3000

Output of the LIMIT 5 query: Dhaka Trauma Centre (9500), Pongu Orthopaedic (8700), Chattogram Accident Care (8700), Sylhet Emergency (7200), Rajshahi Trauma (6100). City Heart Hospital has the most patients overall, but it is excluded because it is not an accident hospital.

Ties at fifth place. Rajshahi Trauma and Barishal Trauma Unit both have 6100, so LIMIT 5 returns one of them arbitrarily. To include every hospital tied with the fifth:

-- Oracle 12c+, PostgreSQL 13+ (SQL Server: SELECT TOP 5 WITH TIES ...)
SELECT HospitalName FROM Hospital
WHERE  Specialization = 'Accident'
ORDER  BY PatientCount DESC
FETCH  FIRST 5 ROWS WITH TIES;

-- portable, using a window function
SELECT HospitalName
FROM  (SELECT HospitalName, PatientCount,
              RANK() OVER (ORDER BY PatientCount DESC) AS rnk
       FROM   Hospital
       WHERE  Specialization = 'Accident') t
WHERE  rnk <= 5
ORDER  BY PatientCount DESC;

The RANK version returns six names (both 6100 hospitals). DENSE_RANK would instead return the top five distinct counts, which gives seven names here.

Data quality. If Specialization holds values like 'Accident & Emergency' or mixed case, use WHERE LOWER(Specialization) LIKE '%accident%'. A leading % cannot use a B-tree index, so a better design stores a clean code or a separate Specialization lookup table.

Performance (DBA view). Without an index, the DBMS scans the whole table and then sorts. A composite index matches both the filter and the sort:

CREATE INDEX idx_hosp_spec_cnt ON Hospital (Specialization, PatientCount DESC);

The plan changes from SCAN Hospital + USE TEMP B-TREE FOR ORDER BY to SEARCH Hospital USING INDEX idx_hosp_spec_cnt (Specialization=?). The rows are read already in order, and the scan stops after 5 rows, with no sort step.

Exam tip: filter (WHERE) → sort (ORDER BY … DESC) → limit (LIMIT / TOP / FETCH FIRST). Name the limit syntax for your DBMS and mention ties.

Answer: the decomposition is lossless only if B is a key of at least one of the two parts, i.e. if the functional dependency B → A or B → C holds. No FDs are given, so losslessness is not guaranteed: in general the decomposition is lossy. With a dependency such as B → C (for example A → B, B → C), it is lossless.

Conditions for a lossless-join (binary) decomposition of R into R1 and R2

1. Attribute preservation: R1 ∪ R2 = R. Here {A, B} ∪ {B, C} = {A, B, C} ✓
2. A common attribute exists: R1 ∩ R2 ≠ ∅. Here R1 ∩ R2 = {B} ✓
3. The common attributes form a super key of R1 or of R2: (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2. Here that means B → A or B → C. This must come from the FDs.

When these hold, R1 ⋈ R2 = R for every legal instance. With functional dependencies, condition 3 is exact (if and only if). If multivalued dependencies are also allowed, the exact condition is the MVD B ↠ A (equivalently B ↠ C): for each B value, every A value appears with every C value; B → C is the common special case.

Case 1: no FD on B, so lossy (counterexample)

A	B	C
a1	b1	c1
a2	b1	c2

R1 = π(A,B) R = {(a1, b1), (a2, b1)}; R2 = π(B,C) R = {(b1, c1), (b1, c2)}.

R1 ⋈ R2 (joined on B):

A	B	C
a1	b1	c1	original
a1	b1	c2	spurious
a2	b1	c1	spurious
a2	b1	c2	original

The join gives 4 tuples instead of 2. A natural join never loses tuples, but the spurious tuples mean we can no longer tell which A goes with which C. Information is lost, so the decomposition is lossy.

Case 2: B → C holds, so lossless

A	B	C
a1	b1	c1
a2	b1	c1
a3	b2	c2

Because each B value has exactly one C, joining on B attaches the one correct C to each (A, B). R1 ⋈ R2 returns exactly these 3 tuples, with no spurious row.

Tableau (chase) test for F = {B → C}

	A	B	C
R1	a	b	c₁ (unknown)
R2	a₂ (unknown)	b	c

The rows agree on B, so B → C forces c₁ = c. Row R1 becomes (a, b, c), a row of all known symbols, so the decomposition is lossless ✓.

flowchart TD
  D["R(A,B,C) into R1(A,B) and R2(B,C)"] --> Q{"Is B a key of R1 or R2? B → A or B → C"}
  Q -->|"Yes"| L["Lossless: R1 ⋈ R2 = R"]
  Q -->|"No / unknown"| X["Lossy: spurious tuples possible"]

Related points

• If A → B and B → C hold, this is the standard 3NF decomposition that removes the transitive dependency A → B → C. It is lossless (B → C) and also dependency-preserving (A → B lies in R1, B → C in R2).
• Lossless join is compulsory for every decomposition. Dependency preservation is desirable but not always achievable (BCNF may sacrifice it).

Exam tip: a binary decomposition is lossless iff R1 ∩ R2 → R1 or R1 ∩ R2 → R2. Here: is B a key of either part?