COSC203 Web, Databases, and Networks
Toggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Assignment 2

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

Project Brief

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/

Example Solution

An example solution is available here

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

Provided Code

Starter code for your back-end:

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)

Run the Project

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.

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.

MySQL 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).

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

VM network setup

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

VM network test


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.

Data Base Design

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

ER Diagram

And the corresponding data model.

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

Path Routing

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

Methodroute (endpoint)response (action)
GET/the home page (all birds)
GET/birds/the home page (all birds)
GET/birds/createa page with a form to create a bird
GET/birds/:ida page for the bird with specified id
GET/birds/:id/updatea page with a form to update bird

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

Methodroute (endpoint)response (action)
GET/birds/:id/deletemodify the database, then redirect to the home page
POST/birds/createmodify the database, then redirect to the home page
POST/birds/editmodify the database, then redirect to the home page

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

Uploading Images

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>

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

Rendereng HTML with EJS

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.

Express Middleware

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

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

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

Marking Scheme

This assignment is worth 15% of your final grade.

Create a 404 “page not found” page and route1
Replace .json with a SQL database5
Each birds has it’s own page2
Ability to delete each bird2
Ability to update each bird3
Ability to create new birds3
Ability to upload images (for create/update)4


Your project should be stored in the department’s GitLab server, 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.