← BIT 202 Database Systems
The Relational Model and ER Modelling
Notes
Relations and integrity
The relational model organises data into relations, commonly implemented as tables containing rows and columns. A row represents one occurrence of an entity, while a column represents an attribute with a defined domain of permitted values. A primary key uniquely identifies each row and cannot be null. A foreign key references a candidate key in another table, or sometimes the same table. Referential integrity prevents references to missing records, while additional constraints enforce requirements such as positive quantities and unique registration numbers.
Modelling business relationships
An entity relationship model describes entities, their attributes and the relationships between them before implementation. Cardinality specifies how many instances may participate, such as one customer placing many orders. Optionality states whether participation is required. A many-to-many relationship usually becomes an associative table: OrderLine connects orders and products and records quantity. Distinguish a product from an order line because the same product can appear in many orders. Validate the diagram against actual business rules and exceptional cases rather than drawing connections only from convenient screen layouts.
Practical example
A Thika wholesale shop has 40 customers, 120 orders and 300 order lines. Each order references one customer through CustomerID, and each line references an order and product. Order 501 contains 3 bags of rice at KES 2,400 and 2 cartons of soap at KES 1,500. Its total is 3 times 2,400 plus 2 times 1,500 = KES 10,200. A composite key of OrderID and LineNumber distinguishes its lines. ProductID alone cannot identify an order line because other customers can order the same product on different dates.
Quiz
1. Which rule does a customer foreign key primarily enforce?
2. Why is ProductID alone unsuitable as the primary key of OrderLine?
3. How should a many-to-many order-product relationship normally be implemented?
Sign in to submit and securely grade your quiz.