Step 3
Database Systems
Almost every application keeps its data in a database. Schema design, SQL, indexes and transactions are covered here.
Know these firstData Structures
- Relational model
- SQL
- ER diagrams
- Normalization
- Indexes
- Transactions and ACID
- Concurrency
Why it matters
Backend, data or mobile, it does not matter: users, orders and messages all live in a database. A badly designed table causes years of trouble, a missing index slows the site down and a query built by string concatenation becomes a security hole.
What to know
- The relational model: tables, rows, primary and foreign keys.
- SQL: SELECT, JOIN, GROUP BY, subqueries; INSERT, UPDATE, DELETE.
- Design with ER diagrams and normalization (1NF, 2NF, 3NF).
- Indexes and why B-trees are used.
- Transactions, ACID and concurrency problems.
- Parameterized queries and SQL injection.
Practice and projects
- Install PostgreSQL or SQLite and design a small schema from scratch, such as a school or a library.
- Run the same query with and without an index and compare the EXPLAIN output.
- Connect it to an app: insert and list data from a simple web or desktop app.
Common mistakes
- Learning SQL only through SELECT *; practice JOIN and GROUP BY a lot.
- Adding user input to a query as a string.
- Putting everything in a single table.