Database Integration

Database Integration

Most full-stack applications store their data in a relational database. Beyond basic CRUD, that means subqueries, aggregate functions with GROUP BY/HAVING, transactions (BEGIN/COMMIT/ROLLBACK) and locking to keep concurrent access safe, constraints to enforce data integrity, and user rights to control who can do what. An ORM abstracts a lot of this work for you, but understanding what happens underneath it is still worth having.

Starting Points

• Beaulieu, A. (2020). Learning SQL (3rd ed.). O'Reilly.
• LinkedIn Learning. Programming Foundations: Databases.

Key Points

• You write subqueries and use aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY and HAVING.
• You use transactions (BEGIN/COMMIT/ROLLBACK) and can explain their purpose (atomic, consistent changes).
• You have a basic understanding of locking and why concurrent access can cause conflicts.
• You define constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) and explain the effect of options like ON DELETE CASCADE.
• You explain the purpose of user rights and privileges in a database, and the difference between typical roles (e.g. admin vs. normal user).
• You explain what an ORM is and why it's used, and use it to perform CRUD actions.