ABC Electronics: the ER design
ABC sells electronics in stores across the country and online. You found what data it must keep, how the pieces link, and drew it.9 entitiesOrder Item is weakQuantity on STOCKS (M:N)10 relationshipsAn ER diagram is a map of what a business must remember. Read the case and ask: what things need records (customers, products, orders)? Those are entities. What links them (a customer places an order)? Those are relationships.
Step 1 · Learn the symbols
Step 2 · Explore the diagram: tap any entity
Tap any entity in the diagram
Its relationships light up here, with its key and attributes. Try Order Item (the weak one) or Store.
Step 3 · The choices examiners ask about
Why is stock quantity not an entity?
"Store A has 5 of this TV." The number belongs to one store and one product together, so it sits on the M:N STOCKS relationship. As a table: Stock(StoreID, ProductID, Quantity).
Why is Order Item weak?
"Line 2" means nothing without its order, so it is a weak entity. Its key is OrderID + LineNo, where LineNo is the partial key.
Why store UnitPrice in Order Item?
Product prices change. An old bill must show the price actually charged that day.
Which attributes are special?
Composite: Name, Address. Multivalued: Phone. Derived: Age, Amount, TotalAmount.
Step 4 · The main rule: selling reduces stock
Every sale reduces Quantity for that store and product inside one transaction. Try the last-unit case.
Each shelf is one row of the Stock(StoreID, ProductID, Quantity) table.
UPDATE Stock SET Quantity = Quantity - 1
WHERE StoreID = ? AND ProductID = ? AND Quantity >= 1;Extra: the same design as tables, with keys
Customer(CustomerID, FirstName, LastName, Email, DateOfBirth, Street, City, State, PIN)
CustomerPhone(CustomerID, Phone) -- the multivalued attribute
Category(CategoryID, CategoryName)
Product(ProductID, ProductName, Brand, ModelNo, Price, CategoryID)
Store(StoreID, StoreName, Street, City, State, PIN, Phone)
Stock(StoreID, ProductID, Quantity, ReorderLevel) -- the M:N relationship
Employee(EmployeeID, FirstName, LastName, Role, StoreID)
Orders(OrderID, OrderDate, Channel, Status, CustomerID, StoreID, EmployeeID)
OrderItem(OrderID, LineNo, ProductID, Quantity, UnitPrice)
Payment(PaymentID, OrderID, PaymentDate, Mode, Amount, Status)
Delivery(DeliveryID, OrderID, Street, City, State, PIN, CourierName, Status)Primary keys come first. Every ID pointing to another table is a foreign key. ORDER is a reserved word in SQL, so the table is called Orders.
I first listed nine assumptions, for example that each store keeps its own stock and an online order is fulfilled from one store. I found nine entities by picking the nouns in the case. Order Item is weak because it depends on Order. The available quantity is not an entity but an attribute of the many-to-many STOCKS relationship between Store and Product. I gave each relationship its cardinality and participation and drew it in Chen notation. When an order is confirmed, the stock for that store and product is reduced in one transaction.