Virtual Gate Test Series: Databases - Referential Integrity

Question

We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets at all?

 $1)$ We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity

 $2)$ Weak entities reflect the logical structure of an entity being dependent on another entity

$3)$Weak entities can be deleted automatically when their strong entity is deleted

What is/are the possible correct reasons from the above:

$A)1$ and $2$                                            $B)2$ and $3$

$C)1$ and $3$                                            $D)1,2$ and $3$

Comments

D????

Answer 1

We can convert any weak entity set to a strong entity set by simply adding appropriate attributes.Why, then, do we have weak entity sets?

Answer :We have weak entities for several reasons:

•  We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity.

•  Weak entities reflect the logical structure of an entity being dependent on another entity.

•  Weak entities can be deleted automatically when their strong entity is deleted.

• Weak entities can be stored physically with their strong entities.

 

Answer 2

1. Strong Entity = Globally Unique ID

You generate a unique ID (like a UUID, auto-incremented integer, or email) before the data hits the database. Every row stands completely on its own two feet.

• Example: User table.
• The Data: UserID: 99421 | Name: Alex
• The Reality: You can move Alex to any table, look up Alex from any webpage, or delete other tables, and Alex's record remains perfectly valid.

2. Weak Entity = Composite ID (Owner ID + Local Counter)

The row does not get its own independent ID. Instead, its identity is welded to its parent's ID. Its "key" is a combination of the Parent's ID and a local descriptor.

• Example: Invoice_Line_Item table (the individual items inside a shopping receipt).
• The Data: InvoiceID: 55 + ItemNumber: 1 (Milk).
• The Reality: There are thousands of items in your database with ItemNumber: 1. To fetch, update, or delete that specific milk row, your backend code is forced to write a query using both numbers: WHERE InvoiceID = 55 AND ItemNumber = 1.

---

⚙️ Why choose Weak over Strong in actual code?

If you just make everything a Strong Entity with an auto-incrementing ID, you run into three practical headaches that Weak Entities solve:

1. Massive Index Fragmentation: If you have 1 million invoices, and each invoice has 5 items, a strong entity approach creates 5 million global IDs. A weak entity keeps the scope local to the parent, saving massive amounts of index memory.
2. API URL Routing: If you build a REST API, a weak entity structure naturally dictates your URL paths: /invoices/55/items/1. It guarantees a developer cannot accidentally view an item without verifying they have access to the parent invoice first.
3. Data Lifecycle Control: In code, you don't want orphaned data. By designating something as weak, you tell the database engine: "If Invoice 55 is deleted, hard-delete all line items tied to 55 instantly." You don't have to write manual cleanup scripts.

---

Answering Question:

1. "Avoid data duplication and inconsistencies"

   • The Practical Reality: If you tried to force the Invoice_Line_Item (Weak) to be a Strong entity by giving it its own global ID, you would end up duplicating data just to track things manually.
   • The Problem: If you change or update the parent InvoiceID in one place but forget to update your duplicated reference in another, your database becomes corrupted and inconsistent. Keeping it a Weak Entity prevents this duplication nightmare.

2. "Reflect the logical structure of dependency"

   • The Practical Reality: A line item on a receipt cannot exist in outer space. It only exists because the invoice exists.
   • The Problem: If you made it a Strong Entity, the database would treat it as a standalone object (like a standalone product or user). Keeping it a Weak Entity forces the code to structurally acknowledge that the item belongs 100% to that specific invoice.

3. "Deleted automatically when strong entity is deleted"

   • The Practical Reality: If a customer cancels Invoice 55, you do not want the individual line items (Milk, Bread) taking up space in your database forever as orphaned rows.
   • The Problem: With a Strong Entity, you have to write extra code to find and delete every single related item. With a Weak Entity, the database engine handles it natively—delete the Invoice, and the database automatically purges all connected items instantly.

4. " Stored physically with their strong entities: Physical Co-location (Data Clustering)"

   • Because a weak entity (like Invoice_Line_Items) cannot exist without its strong entity (Invoices), you will almost never query a child record on its own. You will always fetch them together (e.g., loading a receipt page loads the main invoice data along with all its line items at the exact same time).
   • The Practical Advantage: Because the database engine formally knows it is a weak entity, it can optimize the hardware storage layout. It physically stores the child rows on the exact same hard drive sectors (or memory blocks) as the parent row.
   • The Performance Impact: When the database server reads the strong entity from disk, it grabs the weak entity records in a single physical sweep. This drastically reduces slow disk read operations, making your queries run much faster than if the child data were stored as a standard independent strong entity elsewhere on the disk.

Hence, all of reasons listed are correct.