Data & API Fundamentals

Data & API Fundamentals

Use this page to revise how data is modelled, stored, and exposed: entity modelling, SQL, and REST APIs.

Modeling entities and relationships

• An entity is a thing you store data about (e.g. a User, an Order); its attributes are the properties it has.
• A primary key uniquely identifies a row; a foreign key refers to a primary key in another table, creating a relationship.
• Relationship types: 1:1 (one row relates to exactly one other row), 1:many (one row relates to many rows elsewhere), many:many (rows on both sides can relate to multiple rows on the other side, usually via a join table).
• In UML, a class diagram shows classes, attributes, methods, and relationships (association, aggregation, composition, inheritance); a sequence diagram shows the order of interactions between objects over time; a data flow diagram shows how data moves between users, processes, and stores. Each answers a different question, which is why you pick the right one for what you're trying to communicate.

SQL beyond basic CRUD

• A subquery is a query nested inside another query, often used to filter based on a computed value.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute a single value from many rows; GROUP BY groups rows before aggregating, and HAVING filters groups after aggregation (unlike WHERE, which filters rows before).
• A transaction groups multiple statements so they succeed or fail as one unit (BEGIN, COMMIT, ROLLBACK) — this is what "atomic, consistent changes" means. Locking prevents two transactions from corrupting each other's changes when they touch the same data concurrently.
• Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) enforce data integrity at the database level; ON DELETE CASCADE on a foreign key automatically deletes dependent rows when the referenced row is deleted.
• User rights control which database roles (e.g. admin vs. normal user) can read, write, or manage which data.
• An ORM (Object-Relational Mapper) lets you work with database rows as objects in your programming language, instead of writing raw SQL for every operation — useful for productivity and consistency, at the cost of some control over the exact queries run.

REST APIs

• REST is a resource-oriented style: a URL identifies a resource (e.g. /users/42), and CRUD operations map to HTTP methods.
• HTTP methods: GET (read), POST (create), PUT/PATCH (update — replace vs. partial update), DELETE (remove).
• The request/response cycle: a request has a method, a URL, headers, and optionally a body; a response has a status code, headers, and optionally a body (often JSON).
• Routing and controllers map an incoming request (method + path) to the function that should handle it.
• HTTP status code conventions: 2xx = success, 4xx = client error (the caller did something wrong), 5xx = server error (the server did something wrong).

Starting Points

• Bennett, S. UML Fundamentals [video]. O'Reilly.
• Fowler, M. (2003). UML Distilled (3rd ed.). Addison-Wesley.
• Beaulieu, A. (2020). Learning SQL (3rd ed.). O'Reilly.
• MDN Web Docs. REST — Glossary; HTTP messages.
• System & data modeling
• Database integration
• API implementation

Key Points

• You can explain what an entity, attribute, primary key, and foreign key are.
• You can name the three relationship types (1:1, 1:many, many:many) with an example of each.
• You can explain when to use a class diagram, a sequence diagram, or a data flow diagram.
• You can write a subquery and use aggregate functions with GROUP BY/HAVING.
• You can explain what a transaction is and why locking matters for concurrent access.
• You can name the common constraint types and explain ON DELETE CASCADE.
• You can explain what an ORM is and why it's used.
• You can explain REST's resource-oriented model and match HTTP methods to CRUD operations.
• You can explain the difference between 2xx, 4xx, and 5xx status codes.