WEB106 · Databases and Data Modeling for Web Apps

Categories: Cloud & Software
Wishlist Share

About Course

Model business data, write effective SQL and connect a database to a Node.js API with migrations and safe queries. You will design, build and query the database for a small online store on your own computer.

Advance · 8 hours · 4 lessons, a lab, an assessed assignment and a portfolio piece

Who it is for

Developers who can build an API and now need reliable, well-structured data storage behind it.

Prerequisites

Complete WEB105 or be able to build a basic Express API with routes and validation.

What you will be able to do

  • Design a normalized relational schema with primary keys, foreign keys and constraints from business requirements.
  • Write SQL queries with joins, aggregates, filters and indexes to answer business questions.
  • Use transactions to keep related changes consistent.
  • Connect a Node.js API to PostgreSQL through an ORM with migrations and parameterized queries, and justify SQL versus document databases.

Lessons

  • Lesson 1 · Relational data modeling
  • Lesson 2 · SQL for business questions
  • Lesson 3 · Databases in your application
  • Lesson 4 · Lab and assignment

Lab: Design and query a store database

You will draw an entity-relationship diagram for a small store, create the tables with a Prisma migration and seed sample data. You will then write ten SQL queries that answer real business questions, add an index and measure its effect with EXPLAIN ANALYZE, and connect the schema to your Express API.

Free tools: PostgreSQL (free, installed locally or with Docker Desktop), pgAdmin or DBeaver Community, Prisma ORM, and the free dbdiagram.io or draw.io for diagrams

Assessed assignment

Design the database for a small business of your choice, such as a gym with members, plans and check-ins, or a catering company with clients, events and menu items. Deliver an ER diagram, the migration files, a seed script with realistic data, and ten SQL queries that answer questions a manager would ask, each with its result. Implement one operation that must be atomic (for example, creating an order and reducing stock) inside a transaction, and explain in a short note which indexes you added and why.

Criterion Weight What excellent work looks like
Schema design 30% Tables are normalized, keys and constraints enforce business rules, and data types are appropriate.
SQL queries 25% Ten correct queries use joins, grouping and filters to answer meaningful business questions.
Integrity and transactions 25% The atomic operation uses a transaction correctly and fails safely without partial writes.
Performance and documentation 20% Indexes are justified with EXPLAIN output and the diagram and notes are clear.

Portfolio piece

A documented relational database design with migrations, seed data and business queries for a realistic small business.

Self-paced, 12-month access. Questions are answered in writing through the student panel. A completion record is issued when you finish the course.

Next step

Continue with WEB107 · Mobile Apps with Cross-Platform Tools · 8 hours · $56.00

Continue to the next course

Prefer the whole program? Program in Full-Stack Web & Mobile Development · $378.00 (10% less than buying the courses separately)

Show More

What Will You Learn?

  • Design a normalized relational schema with primary keys, foreign keys and constraints from business requirements.
  • Write SQL queries with joins, aggregates, filters and indexes to answer business questions.
  • Use transactions to keep related changes consistent.
  • Connect a Node.js API to PostgreSQL through an ORM with migrations and parameterized queries, and justify SQL versus document databases.

Course Content

WEB106 · Databases and Data Modeling for Web Apps (8 h)
Model business data, write effective SQL and connect a database to a Node.js API with migrations and safe queries. You will design, build and query the database for a small online store on your own computer.

  • Lesson 1 · Relational data modeling
  • Lesson 2 · SQL for business questions
  • Lesson 3 · Databases in your application
  • Lesson 4 · Lab and assignment
  • WEB106 Assessment · Databases and Data Modeling for Web Apps
Scroll to Top