Graduate Program KB

To create the tables outlined in the library system example and perform various types of joins using the pg library in a Node.js application with a PostgreSQL database, follow the steps below:

Step 1: Install the pg library:

npm install pg

Step 2: Set up the PostgreSQL connection:

Create a database.js file and insert the following code to set up the connection to your PostgreSQL database.

const { Pool } = require('pg');

const pool = new Pool({
    user: 'your_username',
    host: 'your_host',
    database: 'your_database_name',
    password: 'your_password',
    port: your_port
});

module.exports = pool;

Replace the placeholders with your PostgreSQL database credentials.

Step 3: Create tables:

Create a createTables.js file and insert the following code to create the tables for the library system.

const pool = require('./database');

const createTables = async () => {
    try {
        await pool.query(`
            CREATE TABLE authors (
                author_id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL
            );
            
            CREATE TABLE books (
                book_id SERIAL PRIMARY KEY,
                title VARCHAR(100) NOT NULL,
                author_id INTEGER REFERENCES authors(author_id)
            );
            
            CREATE TABLE users (
                user_id SERIAL PRIMARY KEY,
                username VARCHAR(100) NOT NULL UNIQUE
            );
            
            CREATE TABLE borrowed_books (
                borrow_id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(user_id),
                book_id INTEGER REFERENCES books(book_id),
                borrow_date DATE NOT NULL
            );
        `);
        console.log('Tables created successfully!');
    } catch (error) {
        console.error('Error creating tables:', error);
    } finally {
        pool.end();
    }
};

createTables();

Run the createTables.js script to create the tables:

node createTables.js

Step 4: Load the tables with dummy data:

Create a loadDummyData.js file and insert the following code to insert dummy data into the tables.

const pool = require('./database');

const loadDummyData = async () => {
    try {
        await pool.query(`
            INSERT INTO authors (name) VALUES
                ('George Orwell'),
                ('J.K. Rowling'),
                ('J.R.R. Tolkien');
                
            INSERT INTO books (title, author_id) VALUES
                ('1984', 1),
                ('Animal Farm', 1),
                ('Harry Potter and the Philosopher''s Stone', 2),
                ('Harry Potter and the Chamber of Secrets', 2),
                ('The Hobbit', 3),
                ('The Lord of the Rings', 3);
                
            INSERT INTO users (username) VALUES
                ('user1'),
                ('user2'),
                ('user3');
                
            INSERT INTO borrowed_books (user_id, book_id, borrow_date) VALUES
                (1, 1, '2022-01-01'),
                (1, 2, '2022-01-10'),
                (2, 4, '2022-02-05'),
                (3, 6, '2022-02-20');
        `);
        console.log('Dummy data loaded successfully!');
    } catch (error) {
        console.error('Error loading dummy data:', error);
    } finally {
        pool.end();
    }
};

loadDummyData();

Run the loadDummyData.js script to load the dummy data:

node loadDummyData.js

Step 5: Execute various types of joins:

Create a executeJoins.js file and insert the following code to execute various types of joins using the pg library in a Node.js application with a PostgreSQL database.

const pool = require('./database');

const executeJoins = async () => {
    try {
        // Inner Join
        const innerJoinResult = await pool.query(`
            SELECT users.username, books.title
            FROM users
            INNER JOIN borrowed_books ON users.user_id = borrowed_books.user_id
            INNER JOIN books ON borrowed_books.book_id = books.book_id;
        `);
        console.log('Inner Join Result:');
        console.table(innerJoinResult.rows);

        // Left Join (Left Outer Join)
        const leftJoinResult = await pool.query(`
            SELECT users.username, books.title
            FROM users
            LEFT JOIN borrowed_books ON users.user_id = borrowed_books.user_id
            LEFT JOIN books ON borrowed_books.book_id = books.book_id;
        `);
        console.log('Left Join Result:');
        console.table(leftJoinResult.rows);

        // Right Join (Right Outer Join)
        const rightJoinResult = await pool.query(`
            SELECT users.username, books.title
            FROM users
            RIGHT JOIN borrowed_books ON users.user_id = borrowed_books.user_id
            RIGHT JOIN books ON borrowed_books.book_id = books.book_id;
        `);
        console.log('Right Join Result:');
        console.table(rightJoinResult.rows);

        // Full Outer Join
        const fullOuterJoinResult = await pool.query(`
            SELECT users.username, books.title
            FROM users
            FULL OUTER JOIN borrowed_books ON users.user_id = borrowed_books.user_id
            FULL OUTER JOIN books ON borrowed_books.book_id = books.book_id;
        `);
        console.log('Full Outer Join Result:');
        console.table(fullOuterJoinResult.rows);

        // Cross Join (Cartesian Join)
        const crossJoinResult = await pool.query(`
            SELECT users.username, books.title
            FROM users
            CROSS JOIN books;
        `);
        console.log('Cross Join Result:');
        console.table(crossJoinResult.rows);
        
    } catch (error) {
        console.error('Error executing joins:', error);
    } finally {
        pool.end();
    }
};

executeJoins();

Run the executeJoins.js script to execute the various types of joins and display the results:

node executeJoins.js

By following these steps, you can create the tables, load them with dummy data, and execute various types of joins using the pg library in a Node.js application with a PostgreSQL database.