Assignment 2

• Due: Friday, 15th September 2023
• Marks: 15% of your final grade

Build the back-end for the “Birds of Aotearoa” web site.

1. The web interface should be able to
   • Create new birds
   • Edit birds
   • Delete birds
   • Upload images during create/update
2. Data should be stored in a SQL database.
   • Images should be stored in /public/images/

An example solution is available here http://170.64.147.80/

If the example solution breaks please let me know so I can fix it.

Starter code for your back-end: https://altitude.otago.ac.nz/cosc203/code/asgn2-starter

You may use the above code as a starting point. It’s similar to assignment 1, except it has an Express Server for path routing, and uses EJS for Server-Side Rendering (SSR). The provided code still uses nzbird.json — so your first task should be to replace it with a SQL database.

• Node.js already installed on lab machines.
• MySQL setup instruction in Lab 07 (on Blackboard)

First, download the provided code. Then from a terminal, run:

npm install
npm run start

It should now http://localhost:3000 in a web browser.

✅ Tip

You could adapt your assignment 1 submission. You’ll still find the provided code useful.

All tasks are in the marking scheme below. Most of the information needed to build this project can be found in Labs 7-13. However, there are a few things you will have to figure out on your own.

You first task should be to replace nzbird.json with a SQL database.

The script to set up the SQL database should be in sql/db_setup.sql, you will need to create more tables (see ERD below). We have provided sql/db_populate.sql which will insert ALL the data into the database.

To use UTF-8 for strings like Kākāpō

mysql -u root -p --default-character-set=utf8mb4

You can use either a local SQL database, or cosc203VM (VirtualBox).

To use the VirtualBox cosc203VM, you need to change some settings.

1. Power off the VM
2. Settings
3. Network
4. Adapter 2
5. Attached to: Host-only Adapter

Your back-end should now be able to connect to cosc203VM.local, (connection settings in db.js). You can test this by pinging the VM from your host machine.

ping cosc203VM.local

IMHO Docker is easier than VirtualBox, and cleaner than installing mysql. Docker is a popular tool for lightweight virtual machines (but no GUI).

The starter code contains docker-compose.yml, which is setup for mysql. First, install Docker, then run the following commands:

cd sql
docker compose up -d
docker exec -it cosc203mysql mysql -u root -p --default-character-set=utf8mb4

To create tables and insert data:

mysql> source sql/db_setup.sql
mysql> source sql/db_populate.sql   

Docker is an alternative to VirtualBox, you don’t need both.

Below is an entity relationship diagram the back-end should use.

And the corresponding data model.

the dataset (from nzbird.json) has been changed:

• 3 typos in the dataset were fixed
• The photo paths are now just file names
• some attributes were renamed to avoid SQL keywords

The routes (endpoints) to serve pages. The first two routes are already included in the provided code.

The routes (endpoints) to modify the database, and upload images.

You may want more depending on how you design your CRUD interface.

To POST files from the front-end you should use enctype="multipart/form-data" on the form Element.

Which renders the following HTML:

<form action="/route/endpoint" method="POST" enctype="multipart/form-data">
    <input type="text" name="myText"/>
    <input type="file" name="myFile">
    <button type="submit">Submit</button>
</form>

(where route/endpoint is the back-end route which receives the POST request)

The starter code for the back-end uses EJS for Server-Side Rendering (SSR). Lab 13 only covers the basics of EJS, so it will be worth your time to learn more.

• EJS Documentation

In Lab 12 we used the Express middleware body-parser to get the body of a POST request.

• body-parser

To get files from a POST requests you’ll need an additional middleware.

• express-fileupload

In Node.js, you can use fs to save files.

This assignment is worth 15% of your final grade.

Your project should be stored in the department’s GitLab server, altitude.otago.ac.nz. Ensure your repository is private and add Reuben Crimp (crire90p) as a Reporter (not Guest).

1. All project files must be in a git repo
2. The script to create tables MUST be in sql/db_setup.sql.

We will validate academic integrity with plagiarism detection software. So, make sure your git repo is set to private. If there are any false positives, we will manually inspect git logs to determine provenance.