Entity-Relationship (ER) Modeling

8. Practice Problems

Try each problem yourself first — sketch on paper. Then click Show Answer. Some answers include runnable SQL right here on this page.

1. List the four kinds of attribute aside from simple, with one Bangladeshi example each.
   simple ছাড়া বাকি চার ধরনের attribute এবং প্রতিটির জন্য বাংলাদেশী একটি উদাহরণ লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   • Composite — name = first_name + last_name on a national ID card.
   • Derived — age derived from date_of_birth.
   • Multi-valued — multiple phone_numbers for one bKash agent.
   • Key — nid_number on a citizen, student_id on a learner.
2. Is ISBN of a book a key attribute, multi-valued, or composite? Explain.
   একটি বইয়ের ISBN কি key, multi-valued নাকি composite attribute? ব্যাখ্যা করুন।
   ✨ Show Answer (উত্তর দেখুন)

   Answer: Key. ISBN is a globally unique identifier for a book, so it can serve as the primary key of a Book entity. It is a single value (not multi-valued), and we don’t typically need to break it into parts (not composite).

3. In the university case study, why is Dependent a weak entity?
   case study-এ Dependent-কে কেন weak entity বলা হলো?
   ✨ Show Answer (উত্তর দেখুন)

   Two different teachers may both have a child named “Tahmid” — so name alone is not unique. The dependent only makes sense in the context of the owning teacher, and its identity needs the teacher’s id. Thus its full PK is (teacher_id, name), an identifying relationship.

4. Design an ER for a simple bKash system: a Customer has many Accounts; each Account has many Transactions; each Transaction connects two Accounts (sender and receiver).
   একটি সরল bKash সিস্টেমের জন্য ER ডিজাইন করুন: একজন Customer-এর একাধিক Account আছে; প্রতিটি Account-এ একাধিক Transaction; প্রতিটি Transaction দুটি Account-কে যুক্ত করে।
   ✨ Show Answer (উত্তর দেখুন)

   Entities: Customer, Account, Transaction.

   • Customer 1 — N Account (1:N “owns”).
   • Account participates twice in Transaction — once as sender, once as receiver. This is a binary relationship played by two roles.

   bkash.sql

   SELECT t.txn_id, c1.name AS sender, c2.name AS receiver, t.amount
   FROM txn t
   JOIN account  a1 ON a1.account_id = t.from_acc
   JOIN customer c1 ON c1.customer_id = a1.customer_id
   JOIN account  a2 ON a2.account_id = t.to_acc
   JOIN customer c2 ON c2.customer_id = a2.customer_id;

   Run Copy Open in SQLite Online

5. Why is M:N modelled with a junction table instead of a multi-valued FK column?
   M:N relationship-এর জন্য কেন junction table ব্যবহার করা হয়, multi-valued FK কেন নয়?
   ✨ Show Answer (উত্তর দেখুন)

   Because relational tables are flat — each cell is atomic. A multi-valued FK breaks atomicity and prevents joins, indexes, and FKs from working. The junction table also gives us a clean place to store relationship attributes (like the grade in Enroll) and lets us enforce uniqueness with a composite PK.

6. Sketch an ER for a Bangladeshi public library: Member borrows Book; a Book has Author(s); a Book has many copies and only a copy can be borrowed.
   বাংলাদেশী public library-এর ER আঁকুন: Member বই ধার নেয়; একটি বইয়ের একাধিক Author থাকে; একটি বইয়ের একাধিক copy থাকে এবং শুধু copy ধার নেওয়া যায়।
   ✨ Show Answer (উত্তর দেখুন)

   Entities: Member, Book, Author, Copy. Relationships:

   • Book — Author : M:N (a book has many authors; an author writes many books).
   • Book — Copy : 1:N (a book has many physical copies).
   • Member — Copy : M:N through Borrow (with attribute borrow_date, return_date).
7. Total or partial participation? “Every car must have an owner.”
   “প্রতিটি গাড়ির একজন মালিক থাকতেই হবে” — total নাকি partial participation?
   ✨ Show Answer (উত্তর দেখুন)

   Total participation on Car’s side. In SQL we enforce it with owner_id INTEGER NOT NULL REFERENCES owner(owner_id). The owner side is partial — an owner may exist (in our DB) without owning a car.

8. Write a CREATE TABLE statement for a multi-valued skills attribute of an Employee.
   Employee-এর multi-valued attribute skills-এর জন্য CREATE TABLE লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   multi_valued.sql

   CREATE TABLE employee_skill (
       emp_id INTEGER REFERENCES employee(emp_id),
       skill  TEXT,
       PRIMARY KEY (emp_id, skill)
   );
   INSERT INTO employee_skill VALUES (1,'SQL'),(1,'Python'),(2,'React');
   SELECT e.name, GROUP_CONCAT(s.skill, ', ') AS skills
   FROM employee e LEFT JOIN employee_skill s ON s.emp_id=e.emp_id
   GROUP BY e.emp_id;

   Run Copy Open in SQLite Online

9. A unary recursive 1:N relationship: Manager manages Employees. Show how to model it without a separate Manager table.
   Unary recursive 1:N: একজন Manager অনেক Employee manage করে। আলাদা Manager table ছাড়া কীভাবে model করবেন?
   ✨ Show Answer (উত্তর দেখুন)

   manages.sql

   SELECT e.name AS employee, m.name AS manager
   FROM employee e
   LEFT JOIN employee m ON m.emp_id = e.manager_id;

   Run Copy Open in SQLite Online

   Trick: the FK manager_id points back into the same table (a self-reference). It’s a unary 1:N relationship.

10. Identify the entities in this requirement: “On Daraz, a Customer places Orders. Each Order contains many Products from many Sellers. Each Order is paid via one Payment using one of: bKash, Nagad, Card, COD.”
    উপরের Daraz-এর বর্ণনায় কোনগুলো entity হবে?
    ✨ Show Answer (উত্তর দেখুন)

    Entities: Customer, Order, Product, Seller, Payment. “bKash/Nagad/Card/COD” is an attribute (method) of Payment, not an entity. Relationships: Customer 1:N Order; Order M:N Product (junction order_item, with attributes quantity, unit_price); Order 1:1 Payment; Product N:1 Seller.

11. Why does the relational model not natively support multi-valued attributes?
    Relational model কেন multi-valued attribute সরাসরি সমর্থন করে না?
    ✨ Show Answer (উত্তর দেখুন)

    Codd’s First Normal Form (1NF) requires every cell to hold a single atomic value. Without atomicity, joins, indexes, constraints, and the relational algebra operators all break. So multi-valued attributes must be promoted to a separate table — turning the multi-value into many rows.

12. Convert this English to ER and SQL: “A Mobile Recharge System has Customers, SIMs (each SIM belongs to exactly one Customer, but a Customer may own many SIMs), and Recharges (each recharge tops up exactly one SIM, with amount and time).”
    উপরের mobile recharge সিস্টেমের ER ও SQL লিখুন।
    ✨ Show Answer (উত্তর দেখুন)

    Customer 1:N SIM, SIM 1:N Recharge.

    recharge.sql

    SELECT c.name, s.msisdn, SUM(r.amount) AS total_recharge
    FROM customer c
    JOIN sim      s ON s.customer_id = c.id
    LEFT JOIN recharge r ON r.msisdn = s.msisdn
    GROUP BY c.id, s.msisdn;

    Run Copy Open in SQLite Online