2. Design
2.1. Database Design
2.2. API Endpoints
3. Running Guide
3.1. Setup
3.2. Running Guide
- This file is responsible for setting up the database connection and creating the necessary tables.
- It also inserts some initial data into the tables.
- This file contains various functions that interact with the database.
- It includes functions for viewing, adding and updating a course, registering a learner, creating, updating and searching for a lead, and adding a comment.
- Each function corresponds to an API endpoint.
- Created for the sake simplicity, so that app.js can import and utilise it.
- This file includes functions for validating input data for courses, learners, and leads.
- It checks if the input data follows the expected format and returns an error message if any inconsistencies are found.
- This is the main entrypoint.
- It imports and utilizes functions from DB_Functions and app_validate_input.
- Holds the API endpoints.
- instructor_id (primary key, auto increment)
- name
- email (unique)
- course_id (primary key, auto increment)
- course_name
- instructor_id (foreign key referencing Instructors table)
- max_seat
- free_seats (≤ max_seats)
- start_date
- learner_id (primary key, auto increment)
- name
- email (unique)
- phone_number (unique)
- linkedin (unique)
- lead_id (primary key, auto increment)
- course_id (foreign key referencing Courses table)
- learner_id (foreign key referencing Learners table)
- status (Accept / Reject / Waitlist)
- comment_id (primary key, auto increment)
- lead_id (foreign key referencing Leads table)
- instructor_id (foreign key referencing Instructors table)
- comment
- One instructor can have multiple courses.
- Multiple learners can apply for multiple courses.
- Instructor can add multiple comments for each lead.
- John Doe (instructor_id=1) teaches JavaScript 101 (course_id=1).
Alice Johnson (learner_id=1) was accepted into the JavaScript 101 course, indicated by lead_id = 1.
Against lead_id = 1, John Doe inserts a comment, “Good Job!” - John Doe (instructor_id=1) teaches Node.js Basics (courses_id=2).
Bob Lee (learner_id = 2) was waitlisted from Node.js Basics course, indicated by lead_id = 2.
Against lead_id = 3, John Doe inserts a comment, “We will update you shortly” - Jane Smirth (instructor_id=2) teaches Python Basics (course_id=3).
Riya De (learder_id=3) was rejected from Python Basics course, indicated by lead_id=3.
Against lead_id=3, Jane Smith inserts a comment, “Unfortunately, registration has ended”.
- Unique keys should be maintained
- Cannot add new Course if a course with the same course_name and instructor_id exists.
- Students are waitlisted in a courses if free_seats ≤ 0 If course has already started, the student is automatically rejected. When a student is accepted into a course, the free_seats in that course is decreased by 1.
- Only instructors teaching the course can insert comments to their corresponding leads.
- Method: GET
- Endpoint:
/course - Parameters: none
- Action: View existing courses
- Method: POST
- Endpoint:
/course - Request Body: { course_name, instructor_id, max_seat, free_seats, start_date }
- Actions: Create a new course in the Courses table with the provided details.
- Method: PUT
- Endpoint:
/course/:id - Parameter: { course_name, instructor_id, max_seat, free_seats, start_date }
- Actions: Update the course details in the Courses table based on the provided course ID.
- Method: POST
- Endpoint:
/courses/:id/register/ - Parameters: { course_id, learner_id, status }
- Actions: Create a new learner in the Learners table and create a new lead in the Leads table linking the course and learner.
- Method: PUT
- Endpoint:
/leads/:id - Parameters: { }
- Actions: Update the lead status in the Leads table based on the provided lead ID.
- Method: GET
- Endpoint:
/leads/:email - Parameters: {}
- Actions: Retrieve leads from the Leads table based on the provided email.
- Method: POST
- Endpoint:
/leads/comments - Parameters: { instructor_id, comment }
- Actions: Create a new comment in the Comments table for the specified lead and instructor.
Before running the application, make sure you have the following dependencies installed:
- npm
- express
- dotenv
- mysql2
- nodemon
- Ensure that you have Node.js installed on your system.
- Clone or download the code files to your local machine.
- Install Dependencies:
npm install express
npm install dotenv
npm install mysql2
npm install --save-dev nodemon
- Create a
.envfile in the project directory and configure the following environment variables:
DB_HOST = <your_database_host>, DB_USERNAME = <your_username>, DB_PASSWORD = <your_password>, DB = <your_database_name>, DB_SOCKETPATH = <your_socketpath> (/Applications/MAMP/tmp/mysql/mysql.sock for MAC) - Import the database schema and example data using the DB_setup.js script.
- Run the following command:
node DB_setup.js
This will create the necessary tables and populate them with example data.
NOTE: Once connection is successfully established, running the script more than once can lead to duplicate records in some cases.
- Open a command line interface and navigate to the project directory.
- Run the following command to start the server:
npm run devStart - The server will start running, and you should see a message indicating that the server is listening on a specific port (e.g., 8000 in our case). The application will now be running, and you can access the API endpoints described above.
