Assignments

Course Management System

Name: Online Course Platform Management System

Description:
This project involves developing a web application to manage an online course platform. The application features functionalities like managing a course catalog and instructor information. Users can perform operations such as retrieving course details, adding new courses, updating course prices, and deleting courses. The backend interacts with a relational database to manage data seamlessly.

Technologies:

Node.js: For server-side scripting and handling API endpoints.
Express.js: To create a web server and manage HTTP requests.
SQL: For relational database management.
JavaScript: For implementing backend logic.

Interactive Elements:

Database Operations:
- Retrieve the list of all courses with instructor names.
- Add a new course to the catalog, ensuring the instructor exists.
- Update course prices dynamically through user input.
- Delete courses from the catalog by course ID.
Web Form Integration:
- Forms for adding, updating, and deleting courses.

Code Components:

Database Creation (create_database.js): Establishes the e_div database and initializes tables for courses and instructors.
Data Insertion (insert.js): Populates the database with sample courses and instructors.
Server Application (server.js): Manages CRUD operations through API endpoints and forms.

Challenges and Solutions:

Challenge: Ensuring the database schema supports relational integrity.
Solution: Designed the schema with foreign keys and validations to link courses and instructors.
Challenge: Handling dynamic updates and deletions via forms.
Solution: Utilized Express.js middleware to parse form data and execute SQL queries seamlessly.

Reflection on Learning:
This project deepened my understanding of backend development using Node.js and MySQL. I learned to design relational databases, interact with them programmatically, and create dynamic web applications. The hands-on experience in building RESTful APIs and integrating them with a database reinforced my full-stack development skills. Implementing CRUD operations taught me how to manage server-client interactions efficiently while ensuring data integrity.

Code Snippet

DATABASE.

const mysql = require('mysql');

const con = mysql.createConnection({

host: "localhost",

user: "root",

password: ""

});

con.connect(function(err) {

if (err) throw err;

console.log("Connected!");

con.query("CREATE DATABASE IF NOT EXISTS course", function(err, result) {

if (err) throw err;

console.log("Database 'course' created or already exists.");

con.query("USE course", function(err, result) {

if (err) throw err;

console.log("Using the 'course' database.");

const coursesTable = `

CREATE TABLE IF NOT EXISTS courses (

course_id INT PRIMARY KEY,

course_name VARCHAR(255),

instructor_id INT ,

duration VARCHAR(255) ,

prize int

)`;

con.query(coursesTable, function(err, result) {

if (err) throw err;

console.log("Table 'courses' created or already exists.");

});

const instr_table = `

CREATE TABLE IF NOT EXISTS instructors (

instructors_id INT PRIMARY KEY,

instructor_name VARCHAR(255)

)`;

con.query(instr_table, function(err, result) {

if (err) throw err;

console.log("Table 'instructors' created or already exists.");

});

INSERT.js:

const mysql = require('mysql');

const con = mysql.createConnection({

host: "localhost",

user: "root",

password: "",

database: "course"

});

con.connect(function(err) {

if (err) throw err;

console.log("Connected to MySQL!");

const insertcourses = `

INSERT INTO courses (course_id, course_name, instructor_id , duration , prize )

VALUES

(1, 'a',1,'2h',200),

(2, 'b',2,'2h',300),

(3, 'c',3,'2h',400),

(4, 'd',1,'2h',500),

(5, 'e',2,'2h',600)

`;

con.query(insertcourses, function(err, result) {

if (err) throw err;

console.log("Inserted courses into the 'courses' table.");

const insertinstructors = `

INSERT INTO instructors (instructors_id,instructor_name)

VALUES

(1,'koushik'),

(2,'kumar'),

(3,'reddy')

`;

con.query(insertinstructors, function(err, result) {

if (err) throw err;

console.log("Inserted instructors into the 'instructors' table."); });

});

SERVER.js :

var express = require('express');

var app = express();

var bodyParser = require('body-parser');

var mysql = require('mysql');

app.use(bodyParser.urlencoded({ extended: true }));

var db = mysql.createConnection({

host: 'localhost',

user: 'root',

password: '',

database: 'course'

});

db.connect(function(err) {

if (err) {

console.error('Database connection failed:', err.stack);

return;

}

console.log('Connected to database');

});

app.get('/', function(req, res) {

res.send("hello welcome");

});

app.get('/courses', function(req, res) {

const sql = \`

SELECT c.course_id, c.course_name, i.instructor_name

FROM courses c

JOIN instructors i ON i.instructors_id = c.instructor_id;

\`;

db.query(sql, function(err, results) {

if (err) {

console.error(err);

}

res.json(results);

});

app.get('/update-course', function (req, res) {

var formHtml = "";

formHtml += "";

formHtml += "

";

formHtml += "course ID:

";

formHtml += "";

formHtml += "

";

formHtml += "";

res.send(formHtml);

});

app.post('/update-course', function (req, res) {

var course_id = req.body.course_id;

const sql = `update courses set prize = 700 WHERE course_id = ?`;

db.query(sql, [course_id], function (err, result) {

if (err) {

console.error(err)

}

res.send("updated")

});

app.get('/delete-course', function (req, res) {

var formHtml = "";

formHtml += "";

formHtml += "

";

formHtml += "course ID:

";

formHtml += "";

formHtml += "

";

formHtml += "";

res.send(formHtml);

});

app.post('/delete-course', function (req, res) {

var course_id = req.body.course_id;

const sql = `delete from courses WHERE course_id = ?`;

db.query(sql, [course_id], function (err, result) {

if (err) {

console.error(err)

}

res.send("deleted")

});

app.get('/add-course', function (req, res) {

var formHtml = "";

formHtml += "";

formHtml += "

";

formHtml += "course ID:

";

formHtml += "course Name:

";

formHtml += "instructor ID:

";

formHtml += "duration:

";

formHtml += "Prize:

";

formHtml += "";

formHtml += "

";

formHtml += "";

res.send(formHtml);

});

app.post('/add-course', function (req, res) {

var course_id = req.body.course_id;

var name = req.body.name;

var instructor_id = req.body.instructor_id;

var duration = req.body.duration;

var prize = req.body.prize;

const sql = `

INSERT INTO courses (course_id, course_name, instructor_id , duration , prize )

VALUES (?, ?, ?, ?,?);

`;

db.query(sql, [course_id,name,instructor_id,duration,prize], function (err, result) {

if (err) {

console.error(err);

} else {

res.send("added");

}

});

app.listen(3000, function() {

console.log('Server is running on http://localhost:3000');

});

OUTPUT:

nod.mp4