Data basics: entities, relationships, cardinality, keys

The learner can build a conceptual ER model: identify entities and attributes, define relationships with correct cardinality (1:1 / 1:N / M:N), and explain primary and foreign keys.

An ER diagram of an online shop

Entities, keys and cardinality on one picture

Read the lines: the crow's foot means 'many'. The many-to-many link between orders and products is resolved by a junction table.

ER diagram in crow's foot notation. Box 'Customer' (customer_id PK, name, email) —one-to-many— box 'Order' (order_id PK, customer_id FK, date). 'Order' —one-to-many— 'Order_Item' (order_id FK, product_id FK, quantity; PK = order_id + product_id) —many-to-one— 'Product' (product_id PK, name, price). A note under Order_Item: 'junction table resolves Order M:N Product'. Circle/bar marks show optionality on each end. — The FK always sits on the 'many' side; M:N becomes two 1:N via a junction table.

Lesson notes

Entity-Relationship Modeling Essentials

An Entity-Relationship (ER) model is a conceptual blueprint of the data a system needs to store. An entity is a distinct type of thing about which we store data — for example, Customer, Order, or Product. An attribute is a property of an entity (Customer has Name, Email, Date of Birth). A useful test: if a 'property' has properties of its own or can repeat (a Book's authors), it is probably a separate entity, not an attribute. A relationship describes how two entities are associated (a Customer places Orders). Cardinality specifies how many instances of one entity can relate to how many instances of another. There are three fundamental patterns. One-to-One (1:1): each instance of A relates to at most one instance of B and vice versa — e.g. each Employee has one assigned company Laptop, and each Laptop is assigned to one Employee. Cardinality also has a minimum, called optionality: an Employee may have zero Laptops or one, and diagrams mark this with a circle (zero) or a bar (one). One-to-Many (1:N): one instance of A relates to many instances of B, but each B belongs to exactly one A — e.g. one Customer can have many Orders, but each Order belongs to one Customer. Many-to-Many (M:N): many instances of A relate to many instances of B — e.g. a Student enrolls in many Courses, and each Course has many Students.

Keys: how tables are linked

Keys enforce uniqueness and link tables together. A Primary Key (PK) is a column (or set of columns) that uniquely identifies every row in a table — no two rows can share the same PK value. A Foreign Key (FK) is a column in one table that references the PK of another table, creating the link. In a 1:N relationship, the FK goes on the 'many' side: the Orders table stores a Customer_ID column that references the Customers table. An M:N relationship cannot be stored directly; it must be resolved using a junction table (also called an associative or bridge table) that holds two FKs — one for each side of the relationship.