Assess Developer D

A primary key uniquely identifies a row in its own table (unique, not null, one per table). A foreign key is a column that references the primary key of another table, so the database enforces that the referenced row exists.

One-to-many: the "many" side holds a foreign key to the "one" side (an order has a customer_id). Many-to-many needs a join table holding two foreign keys (e.g. student_course(student_id, course_id)).

NULL means "unknown / no value", not zero or empty string. Any comparison with NULL yields unknown, so = NULL never matches. Use IS NULL / IS NOT NULL.

INNER JOIN returns only rows that match on both sides. LEFT JOIN returns every row from the left table, with NULLs where the right side has no match. Use it to keep rows without a match, e.g. customers with no orders (WHERE o.id IS NULL).

WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregates, e.g. HAVING COUNT(*) > 5.

An index is a separate structure (usually a B-tree) that lets the database find rows without scanning the table. It speeds up reads on filtered/joined/sorted columns but costs disk space and slows every insert/update/delete. Add one for frequent, selective queries — confirmed with the query plan.

A transaction groups statements so they succeed or fail together. Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions do not see each other’s partial work), Durability (committed data survives a crash).

Read Uncommitted, Read Committed, Repeatable Read, Serializable — each prevents more anomalies at more cost. READ COMMITTED allows non-repeatable reads (a row changes between two reads in one transaction) and phantoms (new rows appear in a repeated range query).

Reproduce with the real parameters, run EXPLAIN ANALYZE, look for sequential scans, bad row estimates, and expensive sorts/joins. Fixes: a suitable (possibly composite) index, rewriting the query, updating statistics, pagination, or pre-aggregating. Measure before and after.

Make changes backward compatible in steps (expand → migrate → contract): add nullable column, deploy code that writes both, backfill in batches, then add constraints and remove the old column. Avoid long table locks (e.g. CREATE INDEX CONCURRENTLY), and have a rollback plan and a backup.

GET read, POST create, PUT replace, PATCH partial update, DELETE remove. GET must not change state.

2xx success (200 OK, 201 Created, 204 No Content); 4xx client error (400 Bad Request, 401 Unauthorized, 403 Forbidden, 404 Not Found, 422); 5xx server error (500, 503).

Authentication establishes who you are (login, token). Authorization decides what you are allowed to do (roles, ownership checks). A 401 means not authenticated; a 403 means authenticated but not allowed.

Repeating the same request has the same effect as sending it once. GET, PUT, DELETE (and HEAD, OPTIONS) are idempotent; POST is not. Matters for safe retries — POST can be made safe with an idempotency key.

Always on the server at the boundary, even if the client validates too. Return 400 or 422 with a consistent error body that says which field failed and why — never a 500 or a stack trace.

The server signs a token with claims (user id, expiry); the client sends it in the Authorization: Bearer header and the server verifies the signature without a session lookup. Weaknesses: hard to revoke before expiry, so keep access tokens short-lived with refresh tokens; never store secrets in the payload; store securely on the client.

Offset (?page=3&limit=20) is simple and allows jumping to a page, but gets slow on large offsets and skips/duplicates rows when data changes. Cursor (?after=<last id>) is fast and stable for feeds, but cannot jump to an arbitrary page. Both need a stable sort order.

Never break the old contract in place: version it (/v2 or a header), or make the change additive. Run both versions, monitor usage of the old one, communicate a deprecation timeline, and remove it only when old app versions are gone — mobile clients update slowly.

Rate limiting per user/key/IP (token bucket or sliding window) with 429 and Retry-After; request size limits; timeouts; caching; queueing slow work; autoscaling; and monitoring/alerting to spot abuse.

Use logs with correlation/request IDs and metrics (p95/p99 latency) to find which requests and when. Trace the request through its dependencies (DB, external APIs). Common causes: lock contention, N+1 queries, cold caches, slow third parties, resource exhaustion. Reproduce, fix, then confirm with the metrics.