ADBMS Question Bank

    Unit 1: Introduction to ADBMS

1.      What is ADBMS?  Explain features of ADBMS

2.     What are different database users? Explain roles of DBA

3.     What are data models? Explain ER data model in detail

4.     Draw ERD of Attendance management system. List out all possible entities and its attributes with its relationships

5.      Draw RED of Library management system. List out all possible entities and its attributes with its relationships

6.     Draw ERD of Admission management system. List out all possible entities and its attributes with its relationships

7.      Draw ERD of Admission management system. List out all possible entities and its attributes with its relationships

8.     Draw ERD of Event management system. List out all possible entities and its attributes with its relationships

9.     Draw ERD of Online Food Order system. List out all possible entities and its attributes with its relationships

10. Draw ERD of Placement management system. List out all possible entities and its attributes with its relationships

11. Draw ERd of Hotel management system. List out all possible entities and its attributes with its relationships

12. Explain Normalization in detail.

13.  Normalize following database

Table: Student

S_id : 101,102, 103,104,105

N_name: Rohan, Sheetal, Sanika, Gopal,

Phone: 9511775185/23456789, 19764523, 3456789, 23456789, 2345678

State: Maharashtra, Goa, Rajasthan, Delhi, UP

 

Table Teacher:

T_id: 001, 002, 003, 004, 005

Subject: Java, HTML, PHP, ADBMS, Python

Age: 34, 45, 27, 49, 35

 

Table Teacher:

T_id: 001, 002, 003, 004, 005

T_name: Prof. Shyam, Prof. Ram, Dr. Sejal, Dr. Roshan, Dr. Gautam

T_Zip_Code:  411046, 400223, 12023, 1345, 15643

T_City:  Pune, Satara, Sangli, Solapur, Parbhani

 

 

Table Student:

S_id: 101, 102, 103

S_Country: India, UK, USA

S_Specialization: Finance, Data Science, Business Analytica

Dept_Type: Finance, Computer, BA

Specialization_Number: s01, so2, s03

Apply 1^st Normal form, 2^nd Normal Form, 3^rd Normal Form, BCNF on above tables and normalize data

 Unit 02: Different types of databases

1.     What are different databases?

2.     Explain relational database in detail

3.     Explain distributed database in details

4.     What are document databases

5.     What are NoSQL database

6.     Explain distributed database and distributed transactions

7.     Write short on data warehouse

Unit 03: Transaction management, Concurrency and Recovery

1.     What is transaction management? Explain features of transaction management

2.     Explain ACID properties

3.     Explain lock based and timestamp based protocols

4.     What is concurrency control? Explain its features

5.     What is deadlock? Explain deadlock handling methods

6.     What is recovery? Explain recovery in concurrent transactions

Unit 4:  The Relational Database Standard

1.    What  is DDL? Explain DDL statements with syntax and example

2.    What is DML? Explain DML statements with syntax and example

3.    Explain Data constraint in Oracle with syntax and example

4.    What is difference between primary key, unique key and Foreign key

5.    What are different clauses in Oracle? Explain with syntax and example

6.    Explain Aggregate functions in Oracle with syntax and example

7.    Create table client_master with following details

Client_id number(10) Primary Key

Client_name varchar(20) Not Null

Address Varchar(30) Not Null

Contact Number(10)  Unique

8.     Create table sales_order with following details

S_id varchar(4) ,

S_type varchar(15),

S_order_date default,

Amount number(4) check(amount>100)

Delivery_address varchar(10) default address In (‘Pune’ and ‘Mumbai’)

Write queries for following questions

Add new column contact_number to sales_order table

Modify amount column withs new size 6

Modify the Delivery_address  column with Address

Add primary key to s_id column

Add unique key to contact_number column

Rename column Amount with P_amount

Rename table Sales_Order with Sales

Drop column S_Order_Date and Address

Drop primary key, foreign key, not null, check, default

9.     Insert 5 rows in above tables using single row insertion and multiple row insertion query

10. retrieve all rows from above tables

11. retrieve all the products whose price is in between 100 and 1000

12. retrieve all the products whose type is electronic and home appliance

13. retrieve all the products whose delivery was 10 days back 

14. retrieve all the products whose delivery date is 5 days after

15. retrieve all the products with its price and new price *5 as new price

16. retrieve all the products whose delivery is today

17. retrieve all the products whose total amount is greater than 5000 and less than 10000

18.Retrieve all the products in ascending and descending order

19. retrieve all products group by its type

20. Create view on sales_order table and retrive data from view 

21. Retrieve all products group by product type and whose total amount is greater than 1000

22. Write query to delete first row from table

23. Write query to delete 5th row from table 

24. Write query to delete all rows from table

25. Write query to delete all products from sales table whose price is greater than 500

26. Retrieve all the rows from sales and sales details table

27. Write query to perform left, right, full and cross join

28. Create procedure to calculate simple interest 

29. Create procedure to perform arithmetic operation and display result

30. Create procedure to calculate average marks of the student. Calculate percentage of student

31. create cursor to retrieve all data from student table

32. Create cursor to retrieve rows from sales_order table whose product price greater than 500

33. Create trigger after insert on Employee table. Perform insertion automatically in employee_details table

34. Normalization Case Study

1.          First Normal Form (1NF):

        Initial Table:

       Consider a table named "StudentCourses" that tracks student enrollments in different courses. Each row represents a student-course combination, and we want to bring it to 1NF.

       Student ID Student Name Courses

     101 Alice Math, Physics

      102 Bob Chemistry

     103 Carol Biology, History

      To achieve 1NF, we separate the repeating values in the "Courses" column into distinct rows, and each row must have a unique identifier (in this case, the Student ID).

       Modified Tables:

     Students:

       Student ID Student Name

            101 Alice

           102 Bob

         103 Carol

        StudentCourses:

       Student ID Course

          101 Math

         101 Physics

        102 Chemistry

       103 Biology

         103 History

       2. Second Normal Form (2NF): 

        Initial Tables:

        Consider two tables, "Students" and "Courses," which are related. We want to bring

       them to 2NF.

       Students:

          Student ID Student Name Course ID

         101 Alice 1

           102 Bob 2

           103 Carol 1  

          Courses:

     Course ID Course

       1 Math

       2 Chemistry

      To achieve 2NF, we create two tables with proper relationships:

       Modified Tables:

        Students:

     Student ID Student Name

         101 Alice

        102 Bob

        103 Carol

       StudentCourses:

       Student ID Course ID

               101 1

               102 2

                103 1

3.       Third Normal Form (3NF):

       Initial Tables:

        Consider a database for a library. Two tables are involved: "Books" and "Authors." We want to bring them to 3NF.

       Books:

         ISBN                Title        Author ID

          978-12345 "Great Novel"    1

           978-54321 "Mystery Story" 2

      Authors:

             Author ID Author Name

                    1 Author A

                    2 Author B

       To achieve 3NF, we create separate tables for "Books," "Authors," and "BookAuthors" to eliminate transitive dependencies.

       Modified Tables:

         Books:

         ISBN          Title

          978-12345 "Great Novel"

         978-54321 "Mystery Story"

      Authors:

        Author ID Author Name

                1    Author A

               2         Author B

         BookAuthors:

            SBN         Author ID

              978-12345 1

              978-54321 2

Unit 05: 

    1. What is PLSQL? Explain in brief

    2. Explain PLSQL block with syntax and example

    3. What is view? Explain view with example

    4. What is cursor? Explain explicit and implicit cursor with example

    5. Explain cursor with syntax and example

    6. What is trigger? What are different types of triggers? Explain After insert trigger with example

    7. Create table sales_order and insert 5 rows. Write cursor to retrieve order_id, product_type, Product_name and total_amount

    9.