Logical → Physical Schema Mapping

9. Practice Problems

Ten problems covering ER mapping, type choice and migrations.

1. Map the relationship "Each Pathao rider owns at most one motorbike" to a 1:1 schema.
   "প্রতিটি Pathao rider সর্বোচ্চ একটি motorbike-এর মালিক" — 1:1 schema বানান।
   ✨ Show Answer (উত্তর দেখুন)

   ans1.sql

   CREATE TABLE riders (rider_id INTEGER PRIMARY KEY, name TEXT);
   CREATE TABLE motorbikes (
       bike_id  INTEGER PRIMARY KEY,
       plate    TEXT,
       rider_id INTEGER UNIQUE REFERENCES riders(rider_id)
   );
   INSERT INTO riders     VALUES (1,'Arif');
   INSERT INTO motorbikes VALUES (10,'DHA-1234',1);
   SELECT * FROM motorbikes;

   Run Copy Open in SQLite Online

2. Build an M:N schema for "students take many courses; courses have many students" plus a relationship attribute grade.
   student-course M:N + grade attribute সহ schema বানান।
   ✨ Show Answer (উত্তর দেখুন)

   ans2.sql

   CREATE TABLE students(student_id INTEGER PRIMARY KEY, name TEXT);
   CREATE TABLE courses (course_id  INTEGER PRIMARY KEY, title TEXT);
   CREATE TABLE enrollments (
       student_id INTEGER REFERENCES students(student_id),
       course_id  INTEGER REFERENCES courses(course_id),
       grade      TEXT,
       PRIMARY KEY(student_id, course_id)
   );
   INSERT INTO students    VALUES(1,'Arif');
   INSERT INTO courses     VALUES(101,'DBMS');
   INSERT INTO enrollments VALUES(1,101,'A');
   SELECT * FROM enrollments;

   Run Copy Open in SQLite Online

3. "Why is FLOAT wrong for storing money?" — answer with a SQL demonstration.
   টাকা-পয়সার জন্য FLOAT কেন ভুল — SQL দিয়ে দেখান।
   ✨ Show Answer (উত্তর দেখুন)

   ans3.sql

   SELECT 0.1 + 0.2 AS floaty,
          (100 + 200) / 1000.0 AS normalized_paisa;
   -- 0.30000000000000004 vs 0.3 — store integer paisa, not floats.

   Run Copy Open in SQLite Online

   Solution: store amounts as integer paisa (amount_paisa INTEGER) and divide only at presentation time.

4. Map a weak entity "ticket" that exists only in the context of a "concert".
   "concert" ছাড়া অর্থহীন একটি "ticket" weak entity-কে map করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans4.sql

   CREATE TABLE concerts (concert_id INTEGER PRIMARY KEY, artist TEXT);
   CREATE TABLE tickets (
       concert_id INTEGER,
       seat_no    TEXT,
       price      INTEGER,
       PRIMARY KEY(concert_id, seat_no),
       FOREIGN KEY(concert_id) REFERENCES concerts(concert_id) ON DELETE CASCADE
   );
   INSERT INTO concerts VALUES(1,'James');
   INSERT INTO tickets  VALUES(1,'A1',1500),(1,'A2',1500);
   SELECT * FROM tickets;

   Run Copy Open in SQLite Online

5. Implement an ISA hierarchy "Person → Student / Teacher" with the class-per-table strategy.
   class-per-table পদ্ধতিতে Person → Student / Teacher schema বানান।
   ✨ Show Answer (উত্তর দেখুন)

   ans5.sql

   CREATE TABLE persons (
       person_id INTEGER PRIMARY KEY,
       name      TEXT NOT NULL,
       phone     TEXT
   );
   CREATE TABLE students (
       person_id  INTEGER PRIMARY KEY REFERENCES persons(person_id),
       student_no TEXT UNIQUE NOT NULL,
       cgpa       REAL
   );
   CREATE TABLE teachers (
       person_id INTEGER PRIMARY KEY REFERENCES persons(person_id),
       salary    INTEGER
   );
   INSERT INTO persons  VALUES(1,'Arif','017'),(2,'Karim','018');
   INSERT INTO students VALUES(1,'NSU-2021',3.7);
   INSERT INTO teachers VALUES(2,75000);
   SELECT p.name, s.student_no, t.salary
   FROM persons p
   LEFT JOIN students s ON s.person_id = p.person_id
   LEFT JOIN teachers t ON t.person_id = p.person_id;

   Run Copy Open in SQLite Online

6. Spot all naming and type problems: CREATE TABLE Order (ID FLOAT, CustEmail VarChar(20), AmtTk FLOAT, dt DATE);
   উপরের CREATE statement-এ কী কী সমস্যা?
   ✨ Show Answer (উত্তর দেখুন)

   Problems: (1) Order is a reserved word; use orders. (2) MixedCase + plural mismatch with the rest of the schema. (3) ID FLOAT — IDs should be INTEGER or BIGINT; never floats. (4) VARCHAR(20) is too short for an email — use VARCHAR(254) or TEXT. (5) AmtTk FLOAT — money must be NUMERIC or integer paisa. (6) dt is a meaningless name; use created_at TIMESTAMPTZ.

7. Write an idempotent migration that adds a deleted_at column for soft delete on a users table.
   users টেবিলে soft-delete-এর জন্য idempotent migration লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans7.sql

   CREATE TABLE IF NOT EXISTS users (
       user_id    INTEGER PRIMARY KEY,
       email      TEXT    UNIQUE NOT NULL,
       created_at TEXT    DEFAULT CURRENT_TIMESTAMP
   );
   ALTER TABLE users ADD COLUMN deleted_at TEXT;
   CREATE INDEX IF NOT EXISTS idx_users_active ON users(user_id) WHERE deleted_at IS NULL;
   SELECT name FROM sqlite_master WHERE type='index';

   Run Copy Open in SQLite Online

8. Pick the right type for each: order_id, product price in BDT, customer email, login timestamp, "is active" flag.
   প্রতিটি কলামের জন্য সঠিক type বেছে নিন।
   ✨ Show Answer (উত্তর দেখুন)

   Answer:

   • order_id — BIGINT (or INTEGER for small systems).
   • price_paisa — INTEGER; or NUMERIC(12, 2) if you must use decimals.
   • email — VARCHAR(254) / TEXT.
   • last_login_at — TIMESTAMPTZ in Postgres, ISO-8601 TEXT in SQLite.
   • is_active — BOOLEAN (Postgres) or INTEGER 0/1 (SQLite).
9. Why might Flyway-style "V0001__name.sql" filenames be safer than "ALTER TABLE in a chat message"?
   "chat-এ ALTER TABLE পাঠানোর" চেয়ে Flyway filename কেন বেশি নিরাপদ?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: (1) Migrations are version-controlled — every environment runs the same script, in the same order. (2) Flyway records what it has already run; rerunning is a no-op. (3) Code review catches bad SQL before it touches production. (4) New environments (staging, hot-fix branch) can be rebuilt from the same migration history. A chat message has none of these properties.

10. Reverse-engineer the ER diagram implied by this SQL: CREATE TABLE order_items(order_id INTEGER REFERENCES orders(order_id), product_id INTEGER REFERENCES products(product_id), qty INTEGER, PRIMARY KEY(order_id, product_id));
    উপরের SQL থেকে ER diagram বের করুন।
    ✨ Show Answer (উত্তর দেখুন)

    Answer: Two strong entities — orders and products — connected by an M:N relationship "contains" with attribute qty. The composite primary key on (order_id, product_id) tells us each pair appears at most once per order, which is the natural meaning of an order line. The order_items table is a junction table.