Phase 3: Database Programming

Overview

In this phase, you will create a web API that provides access to your database.

Web API

You may not use generative AI to assist you in this part of the assignment.

Part 1: Basic CRUD API (40 points)

Write a web API in Python (or another language approved by the instructor) that provides the following endpoints:

• A set of “CRUD” /register endpoints to allow a user to retrieve, update, and delete registration records in the MySQL database you created in Phase 2. The endpoints should behave like the example web API endpoints we discussed in class. You should have the following endpoints:
  • GET /register - retrieve registrations
  • POST /register - add a registration
  • PATCH /register/(id) - update a registration
  • DELETE /register/(id) - remove a registration
  • DELETE /register - remove all registrations

I suggest that you use the products_api.py example as a starting point.

The GET, POST, and PATCH /register endpoints must use the following JSON format:

{
   "id": 1,
   "firstName": "George",
   "lastName": "Banks",
   "address": "101 Anywhere Ln",
   "gradeFinished": "K5",
   "gender": "M",
   "rideBus": true,
   "class": "K5-A"
}

Notes:

• POST /register - the “class” attribute is optional. All others are required.
• We’re not supplying / retrieving guardian data, to keep the project simple. If your database requires the child’s guardian column(s) to be NOT NULL, change it so that it allows NULL.
• If you designed your database to autogenerate ID’s for registrations, you may have to change it so that it allows the ID’s to be specified in the POST as shown above. This is to simplify automated testing.

Part 2: Assignment (10 points)

Wrote a POST /assign endpoint that assigns children to classes.

When the endpoint is invoked, it should:

1. Remove any existing assignment of children to classes
2. Assign children to classes as follows:
   1. Basic credit (5 points): for each child, assign the child to a class corresponding to their grade finished. Assign boys to the “A” class and girls to the “B” class.
   2. Full credit (10 points): For grades K-5, 1, and 2, assign a mix of boys and girls to the “A” class and the “B” class. For grades above grade 2, assign boys to the “A” class and girls to the “B” class.

The endpoint should return the string “OK”.

Extra Credit Options

You may do either or both of the options below. If you do both, you can get +25 points.

Option 1: Deploy to Internet (+15 points)

Setup a virtual private server using a cloud provider like DigitalOcean. Deploy the database and web API to the server. Configure the server firewall so that both the database and the web API are accessible.

• You may use generative AI to assist you in working out the commands to use to install software and configure your server. However, you must do the actual work yourself; you may not use an agent to setup and configure the server.

If you do this option, in your report, on the title page, provide the URL I can use with curl to test your deployed API.

Option 2: Web Front end (+10 points)

Write a web user interface for your database. You may use generative AI to write the front end, or write it by hand. The front end code should be in the same project as your web API. It can either:

1. Interact directly with the database
2. Make calls to the web API, which interacts with the database.

When your project runs, a GET / request should return a web page that allows the user to

• Display the list of registrations
• Add a new registration
• Update an existing registration
• Delete a registration
• Remove all registrations
• Invoke the POST /assign functionality (if you did that)

Note that curl commands sent to the /register endpoints should continue to work.

If you do this option, create a screen recording demonstrating your project in action, and include a link to your recording on the title page.

Collaboration Policy

No collaboration with other humans (other than the instructor) is permitted on this assignment. This includes debugging help. You may post questions clarifying assignment requirements on Microsoft Teams.

You may use generative AI as specified above.

Include the following Academic Integrity Statement at the end of your report, specifying any use of generative AI debugging help received.

“By affixing my signature below, I certify that the accompanying work represents my own intellectual effort. Furthermore, I have received no outside help other than what is documented below.”

Signed: ________ (Type your name here)

Help Report:

Submission

Use the following command to export your MySQL database to a SQL script:

mysqldump -u root -p -R --set-gtid-purged=OFF --result-file=vbs.sql vbs 

Submit the following to canvas:

• vbs.sql (generated as shown above)
• project.zip - a zip file containing your source code
• report.pdf containing the following:
  1. Cover sheet, with your name, class, assignment, date, and the number of hours you spent.
  2. Academic integrity statement, signed
  3. A brief description of the work you completed
  4. The command needed to run your project
  5. A transcript showing curl commands and their output that demonstrate your web API in action