Sprint Challenge for RDBMS and SQL

The purpose of this exercise is to get you used to being quizzed on interview questions commonly asked about Relational Database Management Systems and SQL.

Please work on this challenge alone, but feel free to use outside resources. You can reference any old code you may have, however, please refrain from copying and pasting any of your answers. Try and understand the question and put your responses in your own words. Be as thorough as possible when explaining something.

Assignment

Start by forking and cloning this repository.

Open the Review.md file and answer the questions inside, then move on to working on the following project.

Project description

The application lets users track Projects and Actions in the spirit of David Allen's Getting Things Done (GTD) methodology.

You'll use Node.js, Express.js and Knex to build a RESTful API for a Project Tracker application that persists data to a SQLite database.

General Requirements

• A project can contain multiple actions and has:
  • a unique Id.
  • a name.
  • a description.
  • a flag that indicates if the project is complete or not.
• An action belongs to only one project. An action has:
  • a unique id.
  • a description of what needs to be done.
  • a notes column to add additional information.
  • a flag that indicates if the action has been completed.

Feel free to name the tables and fields anything you want. Add relationships as you see fit.

tasks

• Build the database and tables using knex migrations. Seeding is not needed.
• Build the API to add projects and actions. Only the POST is needed, the rest is part of the stretch problem.
• Build an endpoint to retrieve a project by its id that returns an object with the following structure:

{
  id: 1,
  name: 'project name here',
  description: 'the project description',
  completed: false, // or true, the database will return 1 for true and 0 for false
  actions: [
    {
      id: 1,
      description: 'action description',
      notes: 'the action notes',
      completed: false // or true
    },
    {
      id: 7,
      description: 'another action description',
      notes: 'the action notes',
      completed: false // or true
    }
  ]
}

Stretch Problem

This section is optional and not counted towards MVP. Start working on it after you're done with the main assignment.

Add the remaining CRUD operations for projects and actions.

Use knext to add data seeding scripts for projects and actions.

Add support for the concept of contexts. A context is something like at home, at work or at computer. The idea is that some actions require one or more contexts in order to be worked on. For example, the action of file income taxes may require that you are at home, at computer and online so if you are at work and look at the list of pending actions you could do in your current context, filing your taxes will not be one of them.

A context can be applied to more than one action. An action can be tied to more than one context, like in the example above.

When retrieving an action by id, add a property that lists all the contexts related to that action.

Remember to run npm init -y to generate a package.json before adding your dependencies.

Good luck and have fun!