Question

An electronic gadget shop keeps records of its electronic products, such as mobile phones, laptops, etc., in a table named Gadgets, which has columns for ItemID (Primary key), ItemType (mobile, iPad, Laptop), Size, Quantity, Price and Supplier. It also stores the billing data in another table named Bill, which has columns as BillNo (Primary key), ItemID, Price, CustName and CustPhoneNo.

Based on the above, answer the following questions:

1. Name any two columns of the Gadgets table where duplicate values are allowed.
2. Assume that the table Gadgets already contains a product having value of ItemID as I001. Can we enter a new record with value of ItemID as I001? Justify.
3. Is it possible to store photos of the products in a new column of the table, Gadgets? If yes, then suggest a suitable data type to store images.
4. Briefly explain the principle of referential integrity with respect to the tables, Gadgets and Bill.

Answer 1

1. Any two columns except the Primary Key (ItemID) can have duplicates.
   Example: ItemType and Supplier
2. No, Justification: ItemID is the Primary Key of the Gadgets table. Primary keys must contain unique values to uniquely identify each record; duplicate values are strictly prohibited.
3. Yes, Suitable Data Type: BLOB (Binary Large Object) or LONGBLOB.
4. Referential integrity ensures consistency between the two tables using the Foreign Key (ItemID in the Bill table). It ensures that:
   • A Bill cannot be created for an ItemID that does not exist in the Gadgets table.
   • An ItemID cannot be deleted from the Gadgets table if it is linked to records in the Bill table.