KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus

1. Design a database schema for an application with ER diagram from a problem description **.

2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **.

3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1).

4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**.

5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**.

6. Implementation of built-in functions in RDBMS**.

7. Implementation of various aggregate functions in SQL**.

8. Implementation of Order By, Group By & Having clause **.

9. Implementation of set operators nested queries, and join queries **.

10. Implementation of queries using temp tables.

11. Practice of SQL TCL commands like Rollback, Commit, Savepoint **.

12. Practice of SQL DCL commands for granting and revoking user privileges **.

13. Practice of SQL commands for creation of views and assertions ** .

14. Implementation of various control structures like IF-THEN, IF-THEN-ELSE, IF-THENELSIF,

CASE, WHILE using PL/SQL **.

15. Creation of Procedures, Triggers and Functions**.

16. Creation of Packages **.

17. Creation of Cursors **.

18. Creation of PL/SQL blocks for exception handling **.

19. Database backup and restore using commands.

20. Query analysis using Query Plan/Show Plan.

21. Familiarization of NoSQL Databases and CRUD operations**.

22. Design a database application using any front end tool for any problem selected. The application constructed should have five or more tables**.

** mandatory

Text Books

1. Elmasri R. and S. Navathe, Database Systems: Models, Languages, Design and Application Programming, Pearson Education, 2013.

2. Sliberschatz A., H. F. Korth and S. Sudarshan, Database System Concepts, 6/e, McGraw

Hill, 2011.

References

1. Adam Fowler, NoSQL for Dummies, John Wiley & Sons, 2015

2. NoSQL Data Models: Trends and Challenges (Computer Engineering: Databases and Big

Data), Wiley, 2018 

Practise Questions

Design a normalized database schema for the following requirement.

The requirement: A library wants to maintain the record of books, members, book issue, book return, and fines collected for late returns, in a database. The database can be loaded with book information. Students can register with the library to be a member. Books can be issued to students with a valid library membership. A student can keep an issued book with him/her for a maximum period of two weeks from the date of issue, beyond which a fine will be charged. Fine is calculated based on the delay in days of return. For 0-7 days: Rs 10, For 7 – 30 days: Rs 100, and for days above 30 days: Rs 10 will be charged per day.

Sample Database Design

BOOK (Book_Id, Title, Language_Id, MRP, Publisher_Id, Published_Date, Volume, Status) 
//Language_Id, Publisher_Id are FK (Foreign Key)

AUTHOR(Author_Id, Name, Email, Phone_Number, Status)

BOOK_AUTHOR(Book_Id, Author_Id) 
// many-to-many relationship, both columns are PKFK
(Primary Key and Foreign Key)

PUBLISHER(Publisher_id, Name, Address)

MEMBER(Member_Id, Name, Branch_Code, Roll_Number, Phone_Number, Email_Id,Date_of_Join, Status)

BOOK_ISSUE(Issue_Id, Date_Of_Issue, Book_Id, Member_Id, Expected_Date_Of_Return,Status)
 // Book+Id and Member_Id are FKs

BOOK_RETURN(Issue_Id, Actual_Date_Of_Return, LateDays, LateFee) 
// Issue_Id is PK and FK

LANGUAGE(Language_id, Name) //Static Table for storing permanent data

LATE_FEE_RULE(FromDays, ToDays, Amount) 
// Composite Key

EXERCISES

1. Create a normalized database design with proper tables, columns, column types, and constraints

2. Create an ER diagram for the above database design.

3. Write SQL commands to

a. Create a database by name Library. Drop the database and re-create it.
b. Create DDL statements and create the tables and constraints (from the design) in the
database created in step-a (Library)
Notes: [ Create a script file and execute it. Create the script file in such a way that,,if the
table exists, drop the tables and recreate )]
c. Create and execute DROP TABLE command in tables with and without FOREIGN
KEY constraints.
d. Create and execute ALTER TABLE command in tables with data and without data.
e. Create and execute SQL commands to build indices on Member_Id and Book_Id on
table Book_Issue.
f. Create and execute GRANT/REVOKE commands on tables.
g. Create and execute SQL commands to insert data into each of the tables designed
h. Learn and execute bulk import of data to tables from CSV files (insert 1000 records of
books into the BOOK table from a CSV file).
i. Create and execute UPDATE/DELETE commands on tables. Try to update/delete
rows with Primary and Foreign Keys. Try bulk updates or deletes using SQL
UPDATE statement
4. Write SQLQuery to retrieve the following information
a. Get the number of books written by a given author
b. Get the list of publishers and the number of books published by each publisher
c. Get the names of authors who jointly wrote more than one book.
d. Get the list of books that are issued but not returned
e. Get the list of students who reads only ‘Malayalam’ books
f. Get the total fine collected for the current month and current quarter
g. Get the list of students who have overdue (not returned the books even on due date)
h. Calculate the fine (as of today) to be collected from each overdue book.
i. Members who joined after Jan 1 2021 but has not taken any books

5. Book return should insert an entry into the Book_Return table and also update the status in Book_Issue table as ‘Returned’. Create a database TRANSACTION to do this operation (stored procedure).
6. Create a database view ‘Available_Books’, which will list out books that are currently available in the library
7. Create a database procedure to add, update and delete a book to the Library database (use parameters).
8. Use cursors and create a procedure to print Books Issue Register (page wise – 20 rows in a page)
9. Create a history table (you may use the same structure without any keys) for the MEMBER table and copy the original values of the row being updated to the history table using a TRIGGER.

10. NoSQL Exercise
a. Practice Mongo DB CRUD operations. 
b. You may use a MongoDB local installation or cloud MongoDB services like MongoDB Atlas for this exercise

11.Application Development Problem examples:
1) Inventory Control System.
2) Material Requirement Processing.
3) Hospital Management System.
4) Railway Reservation System.
5) Personal Information System.
6) Web Based User Identification System.
7) Timetable Management System.
8) Hotel Management System.

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more