Sprint Challenge: Relational Databases

Challenge Overview

Demonstrate your mastery of relational databases by building a complete database application.

In this sprint challenge, you'll build a complete database application that demonstrates your understanding of:

  • Database schema design
  • Data modeling and normalization
  • Multi-table queries
  • Database migrations and seeds
  • Knex.js query builder

Project Setup

Getting Started

  1. Fork and clone the starter repository
  2. Install dependencies using npm
  3. Set up your database configuration
  4. Run the initial migrations
View Starter Repository

Project Requirements

Database Schema

  • Design and implement a normalized database schema
  • Create appropriate table relationships
  • Include necessary indexes and constraints
  • Document your schema design

Your schema should apply normalization principles to avoid data redundancy while maintaining data integrity. Your tables should use appropriate data types and include essential constraints like primary and foreign keys.

// Example migration for a normalized project schema
exports.up = function(knex) {
  return knex.schema
    .createTable('projects', table => {
      table.increments('id');
      table.string('name').notNullable();
      table.string('description');
      table.timestamps(true, true);
    })
    .createTable('tasks', table => {
      table.increments('id');
      table.string('description').notNullable();
      table.boolean('completed').defaultTo(false);
      table.integer('project_id')
        .unsigned()
        .notNullable()
        .references('id')
        .inTable('projects')
        .onDelete('CASCADE')
        .onUpdate('CASCADE');
      table.timestamps(true, true);
    });
};

API Endpoints

Create the following API endpoints to interact with your database:

  • Create a new project
  • Get all projects
  • Get a project by ID
  • Create a task for a project
  • Get all tasks for a project
  • Update a task's completion status
  • Delete a task

Middleware & Validation

Implement middleware for:

  • Validating request data
  • Error handling
  • Checking if a project exists

Testing

Write tests for your endpoints using Jest and Supertest:

  • Test successful operations
  • Test error cases
  • Test validation logic

Stretch Goals

If you complete the main requirements, attempt these additional features:

  • Add user authentication
  • Add project categories/tags
  • Implement sorting and filtering for projects and tasks
  • Add task priority levels
  • Create a front-end UI using React

Helpful Resources

Knex.js Documentation Express.js Documentation Jest Testing Framework Supertest Documentation

Submission Guidelines

Submit your solution by:

  • Pushing your code to GitHub
  • Submitting the repository URL on Canvas
  • Deploying your application (optional)

Your submission will be evaluated based on functionality, code quality, and completion of requirements.