Project 5

Website REST Service

For Project 4 you created a list of tables to control the website. You were required to implement three of those tables with properties, and two of the tables were required to have a relationship. You were also required to implement five queries.

For Lab 6, you were required to duplicate code to create a working REST service using Node JS for the COLOR database table.

For this project, you will combine the efforts of Project 4 and Lab 6 and create a REST service for ONE of your Project 4 tables. Below are the required elements.

Do NOT use a table with a JOIN to avoid complicated queries

Copy the Lab 6 server-color.js file and name the new file p5-server.js

Update the GET, POST, DELETE and PUT routes in p5-server.js to work with your table

Remove ALL references to color in p5-server.js, and update with references to your new tables

Optional: You can optionally remove any routes used to load web page content (e.g. the HTML, CSS and JS files).

Test your REST routes using Postman, and document the following information in p5-server-postman.txt

GET

The GET URL to retrieve ALL rows from your table

The JSON result from Postman for all your rows

The GET URL to retrieve the first row by primary key from your table

The JSON result from Postman for your first row

POST

The POST URL to insert a new row into your table

The JSON sent in Postman

The JSON result from Postman from POST action

PUT and DELETE

Use Postman to perform DELETE and PUT actions

Below are two useful tutorials on using Postman:

Postman Tutorial For Beginners with API Testing ExampleLinks to an external site.

Postman Tutorial For Beginners to perform API TestingLinks to an external site.

Note: You may want to open the Postman Console while performing the REST actions to view the REST communication with your NodeJS server.

Deliverables

You will need to collect all of the files for this project, and create a single compressed/ZIP folder called p5.zip that contains all of the project files. You will also need to include all of any required lab files. Upload this single file to Canvas.

Important: Do not include the node_modules folder within any of your submitted files.

Below is the list of files that are required for this project.

Lab 6 files

(1) helloworld-fastify.js

(1) dbInfo.js – we do not need to know your password, so change your password to “password”

(1) connection-test.js

(1) server-color.js

(1) color.html

(1) color.js

(1) color.css

Assignment 5 files

(25) p5-server.js

(10) p5-server-postman.txt

Project 6

Overview

This project is additional requirements given to Project 5. Submit Project 5 and Project 6 separately.

For Project 6, you will create two SQL views, and one stored procedure, using your Project 5 files. You will also update your NodeJS code to execute one of the views, and use the stored procedures. The specifics are below.

SQL Views

Create two SQL views that work with your Project 5 alumni database. Both of your views must start with the vw_ prefix.

View #1: Create a simple view that returns data from one of your tables. Your view MUST include at least a single calculated column (e.g. a column that does not exist within any of your tables, but is within the view and is calculated from some part of your data). Export the SQL to create this view to a file called view1.sql.

View #2: Create a view that returns data from two tables using a JOIN statement. Export the SQL to create this view to a file called view2.sql.

Add a new GET route to your NodeJS code to use the second view (the view with the JOIN statement). Remember you will have to ensure the new route is not the same as any other GET route.

Stored Procedure

Create a stored procedure that deletes a row from one of your tables. The stored procedure must take an appropriate primary key parameter to determine which row to delete. The stored procedure name must start with the sp_ prefix. Export the SQL to create this stored procedure to a file called sp.sql.

Add a new DELETE route to your NodeJS code to use the stored procedure to delete a row. You will need to adjust your SQL to call the stored procedure, and supply the primary key as an argument to the stored procedure. Remember you will have to ensure the new route is not the same as any other DELETE route.

Deliverables

You will need to collect all of the files for this project, and create a single compressed/ZIP folder called p6.zip that contains all project files. Upload this single file to Canvas.

Below is the list of files that are required for this assignment.

1.(10) view1.sql

2.(10) view2.sql

3.(10) sp.sql

4.(20) p5-server.js