Database Management Systems (DBMS) Lab

LIST OF ASSIGNMENT

  1. Study of Open Source Relational Databases : MySQL
  2. Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym
  3. Design at least 10 SQL queries for suitable database application using SQL DML statements: Insert, Select, Update, Delete with operators, functions, and set operator.
  4. Design at least 10 SQL queries for suitable database application using SQL DML statements: all types of Join, Sub-Query and View.
  5. Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory. Write a PL/SQL block of code for the following requirements:-
    • Schema:
      • 1. Borrower(Rollin, Name, DateofIssue, NameofBook, Status)
      • 2. Fine(Roll_no,Date,Amt)
    • Accept roll_no & 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 5per day.
    • If no. of days>30, per day fine will be Rs 50 per day & 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.

Frame the problem statement for writing PL/SQL block inline with above statement

  1. Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor). Write a PL/SQL block of code using parameterized Cursor, that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table then that data should be skipped.  Frame the separate problem statement for writing PL/SQL block to implement all types of Cursors inline with above statement. The problem statement should clearly state the requirements.
  2. PL/SQL Stored Procedure and Stored Function. Write a Stored Procedure namely proc_Grade for the categorization of student. If marks scored by students in examination is <=1500 and marks>=990 then student will be placed in distinction category if marks scored are between 989 and900 category is first class, if marks 899 and 825 category is Higher Second Class. Write a PL/SQL block for using procedure created with above requirement. Stud_Marks(name, total_marks) Result(Roll,Name, Class). Frame the separate problem statement for writing PL/SQL Stored Procedure and function, inline with above statement. The problem statement should clearly state the requirements.
  3. Database Trigger (All Types: Row level and Statement level triggers, Before and After Triggers). Write a database trigger on Library table. The System should keep track of the records that are being updated or deleted. The old value of updated or deleted records should be added in Library_Audit table. Frame the problem statement for writing Database Triggers of all types, in-line with above statement. The problem statement should clearly state the requirements.
  4. Study of Open Source NOSQL Database: MongoDB (Installation, Basic CRUD operations, Execution)
  5. Design and Develop MongoDB Queries using CRUD operations. (Use CRUD operations, SAVE method, logical operators)
  6. Implement aggregation and indexing with suitable example using MongoDB.
  7. Design and Implement any 5 query using MongoDB
  8. Program to demonstrate database connection using Java/Python and MySql/MongoDB.
  9. Mini Project