Database Management System (dbms) Codes

Group A

Q4: Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory. Suggested Problem statement: Consider Tables: 1. Borrower (Roll_no, Name, Date_of_Issue, Name_of_Book, Status) 2. Fine (Roll_no, Date, Amt) • Accept Roll_no and Name_of_Book from user. • Check the number of days (from Date_of_Issue). • If days are between 15 to 30 then fine amount will be Rs 5 per day. • If no. of days > 30, per day fine will be Rs 50 per day and for days less than 30, Rs. 5 per day. • After submitting the book, status will change from I to R. • If condition of fine is true, then details will be stored into fine table. • Also handles the exception by named exception handler or user define exception handler. OR MongoDB – Aggregation and Indexing: • Design and Develop MongoDB Queries using aggregation and indexing with suitable example using MongoDB. MongoDB – Map-reduce operations: • Implement Map-reduce operation with suitable example using MongoDB.

PL/SQL Control Structure and MongoDB Aggregation

Solution and implementation for Q4 from Database Management System (dbms).

4_plsql_unnamed_block.sql Download 
-- 4a) PL/SQL Question Implementation in MySQL

-- 1. Create database and select it
CREATE DATABASE library_system;
USE library_system;

-- 2. Create tables
CREATE TABLE borrower (
    roll_no INT PRIMARY KEY,
    name VARCHAR(100),
    date_of_issue DATE,
    name_of_book VARCHAR(100),
    status CHAR(1)
);

CREATE TABLE fine (
    roll_no INT,
    date DATE,
    amt INT
);

-- 3. Insert sample data
INSERT INTO borrower VALUES
(1, 'Arun',   '2025-09-13', 'Maths', 'I'),
(2, 'Bina',   '2025-09-11', 'Physics', 'I'),
(3, 'Chetan', '2025-09-10', 'Biology', 'I'),
(4, 'Deepa',  '2025-09-09', 'Chemistry', 'I'),
(5, 'Esha',   '2025-08-10', 'DBMS', 'I'),
(6, 'Farhan', '2025-09-07', 'AI', 'I');

-- 4. View borrower data
SELECT * FROM borrower;

-- 5. Create stored procedure
DELIMITER //
CREATE PROCEDURE fine_check(IN p_roll_no INT, IN p_book VARCHAR(100))
BEGIN
    DECLARE v_days, v_fine INT DEFAULT 0;
    
    SELECT DATEDIFF(CURDATE(), date_of_issue) INTO v_days
    FROM borrower
    WHERE roll_no = p_roll_no AND name_of_book = p_book;
    
    IF v_days > 30 THEN SET v_fine = v_days * 50;
    ELSEIF v_days >= 15 THEN SET v_fine = v_days * 5;
    END IF;
    
    UPDATE borrower SET status = 'R'
    WHERE roll_no = p_roll_no AND name_of_book = p_book;
    
    IF v_fine > 0 THEN
        INSERT INTO fine VALUES (p_roll_no, CURDATE(), v_fine);
    END IF;
END //
DELIMITER ;

-- 6. Execute the procedure
CALL fine_check(6, 'AI');

-- 7. View results
SELECT * FROM fine;
4_mongodb_aggregation_mapreduce.js Download 
Fuck MongoDB. 
I hate that
piece of shit. 

You’re on your own now.

Other Questions in Database Management System

See All Available Questions 


        

        

            Download