Impact of Data Redundancy and Inconsistency: Retail Business Example (Bangladesh/India)
1. Problematic Schema (with redundancy and inconsistency)
| TransactionID | CustomerName | CustomerPhone | ProductName | ProductPrice | StoreLocation |
|---|---|---|---|---|---|
| 1001 | Rahim | 01712345678 | Rice | 50 | Dhaka Market |
| 1002 | Anjali | 9876543210 | Wheat Flour | 40 | Kolkata Market |
| 1003 | Rahim | 01712345678 | Rice | 50 | Dhaka Market |
Issues:
- Customer info repeats for multiple transactions.
- Product info repeats with every sale.
- Store info repeats in every transaction.
- Updating price or location requires multiple row updates, risking inconsistency.
- Wastes storage and increases error chances.
2. Impact on Data Integrity and System Efficiency
- Data Inconsistency: If product price changes but some rows are not updated, incorrect billing happens.
- Data Redundancy: Same customer, product, and store info repeated many times.
- Update Anomalies: Multiple rows require update.
- Insertion/Deletion Anomalies: Cannot add products or stores without dummy transaction data.
3. Normalized Schema (Splitting into multiple tables)
Customers
| CustomerID | CustomerName | CustomerPhone |
|---|---|---|
| 1 | Rahim | 01712345678 |
| 2 | Anjali | 9876543210 |
Products
| ProductID | ProductName | ProductPrice |
|---|---|---|
| 10 | Rice | 50 |
| 11 | Wheat Flour | 40 |
Stores
| StoreID | StoreLocation |
|---|---|
| 100 | Dhaka Market |
| 101 | Kolkata Market |
Transactions
| TransactionID | CustomerID | ProductID | StoreID |
|---|---|---|---|
| 1001 | 1 | 10 | 100 |
| 1002 | 2 | 11 | 101 |
| 1003 | 1 | 10 | 100 |
4. Constraints to Enforce Integrity
- Primary Keys: CustomerID, ProductID, StoreID, TransactionID uniquely identify each table record.
- Foreign Keys: Transactions references Customers, Products, Stores tables to ensure valid data.