Advance Database Management Systems MCA I PDF

**Course: Advance** Database Management Systems **Program:** MCA **Semester:** I **[Course Code: 3CIT101]** **Course Outcomes** **Description** --------------------- -------------------------------------------------------------------------------------------------------------------- **CO1*...

**Course: Advance** Database Management Systems **Program:** MCA **Semester:** I **[Course Code: 3CIT101]** **Course Outcomes** **Description** --------------------- -------------------------------------------------------------------------------------------------------------------- **CO1** Identify advance database concepts and database models. **CO2** Apply and analyze various terms related to transaction management in centralized and distributed database. **CO3** Produce data modelling and database development process for object --oriented DBMS. **CO4** Analyze and Implement the concept of object- relational database in development of the various real time software. **CO5** Examine the issues related to multimedia and mobile database performance. **Section -A** **(20** x **5= 100 Marks)** 1. **What is the purpose of a database system? Explain why it is preferred over traditional file processing systems.** \[CO1\]\[UNIT-1\]\[Remember\]\[LOT\] 2. **What is a primary key? Explain its importance in relational databases.** \[CO1\]\[UNIT-1\]\[Remember\]\[LOT\] 3. **Differentiate between the terms \"entity\" and \"entity set\" in an Entity-Relationship (ER) model.** \[CO1\]\[UNIT-1\]\[Remember\]\[LOT\] 4. **Define the terms \"schema\" and \"instance\" in the context of a database system. How are they related?** \[CO1\]\[UNIT-1\]\[Remember\]\[LOT\] 5. **Briefly explain the three-schema architecture. How does it promote data independence?** \[CO1\]\[UNIT-1\]\[Remember\]\[LOT\] 6. **Given the following relation schema for a library database:** \[CO2\]\[UNIT-2\]\[Remember\]\[LOT\] a. **Books(BookID, Title, AuthorID, Publisher)** b. **Authors(AuthorID, AuthorName)** 7. **What is a referential integrity constraint? Provide an example using two tables: Employees (EmployeeID, Name, DeptID) and Departments (DeptID, DeptName).** \[CO2\]\[UNIT-2\]\[Remember\]\[LOT\] 8. **Explain the difference between Tuple Relational Calculus and Domain Relational Calculus with an example for each.** \[CO2\]\[UNIT-2\]\[Remember\]\[LOT\] 9. **What is the role of transactions in relational databases? Describe the basic properties of transactions using the ACID model.** \[CO2\]\[UNIT-2\]\[Remember\]\[LOT\] 10. **Explain the concept of an SQL view. How does it differ from a regular table? Provide an example SQL statement for creating a view.** \[CO2\]\[UNIT-2\]\[Remember\]\[LOT\] 11. **Define a functional dependency in the context of relational database design. Give an example.** \[CO3\]\[UNIT-3\]\[Remember\]\[LOT\] 12. **What is a common pitfall in relational database design? How can it be avoided?** \[CO3\]\[UNIT-3\]\[Remember\]\[LOT\] 13. **Explain the difference between partial dependency and transitive dependency. Why are they important for normalization?** \[CO3\]\[UNIT-3\]\[Remember\]\[LOT\] 14. **What is the purpose of decomposition in database design? Explain how it is achieved using functional dependencies.** \[CO3\]\[UNIT-3\]\[Remember\]\[LOT\] 15. **State the general definition of the First Normal Form (1NF). Explain the significance of eliminating repeating groups.** \[CO3\]\[UNIT-3\]\[Remember\]\[LOT\] 16. **Describe the different states a transaction can be in during its lifecycle.** \[CO4\]\[UNIT-4\]\[Remember\]\[LOT\] 17. **What is meant by the serializability of a schedule? How does it ensure correctness in a database system?** \[CO4\]\[UNIT-4\]\[Remember\]\[LOT\] 18. **Explain the concept of lock-based protocols in concurrency control. How do they help ensure transaction isolation?** \[CO4\]\[UNIT-4\]\[Remember\]\[LOT\] 19. **What is a 3-tier client-server architecture? Explain its basic structure.** \[CO5\]\[UNIT-5\]\[Remember\]\[LOT\] 20. **Define a distributed database. How does it differ from a centralized database system?** \[CO5\]\[UNIT-5\]\[Remember\]\[LOT\] **Section -B** 12. x **10= 120 Marks)** 21. **Compare and contrast the various types of data models: hierarchical, network, relational, and object-oriented data models. \[CO1\]\[UNIT-1\]\[Understand\]\[LOT\]** 22. **Describe the architecture of a Database Management System (DBMS). Include the components of a DBMS and their respective functions. \[CO1\]\[UNIT-1\]\[Understand\]\[LOT\]** 23. **Explain the significance of the \"aggregation\" concept in the context of ER diagrams. How does it simplify complex relationships? \[CO1\]\[UNIT-1\]\[Understand\]\[LOT\]** 24. **Consider the following relations for a university database: \[CO2\]\[UNIT-2\]\[Understand\]\[LOT\]** c. **Students(StudentID, Name, Age)** d. **Courses(CourseID, CourseName)** e. **Enrollments(StudentID, CourseID)** 25. **Describe the concept of binary relational operations with respect to JOIN and DIVISION operations. Provide examples of each operation. \[CO2\]\[UNIT-2\]\[Understand\]\[LOT\]** 26. **Explain how updates (INSERT, UPDATE, DELETE) are handled in a relational database. How do these operations interact with constraints such as primary keys and foreign keys? \[CO2\]\[UNIT-2\]\[Understand\]\[LOT\]** 27. **Discuss Boyce-Codd Normal Form (BCNF). How does it differ from 3NF? Provide an example of a relation that is in 3NF but not in BCNF. \[CO3\]\[UNIT-3\]\[Understand\]\[LOT\]** 28. **Describe the process of decomposition using functional dependencies. How does this method help to eliminate redundancy and maintain data integrity? \[CO3\]\[UNIT-3\]\[Understand\]\[LOT\]** 29. **What is a schedule in transaction processing, and how is it classified based on recoverability and serializability? Discuss the significance of serializable schedules. \[CO4\]\[UNIT-4\]\[Understand\]\[LOT\]** 30. **Compare and contrast the following concurrency control techniques: Lock-Based Protocols, Timestamp-Based Protocols, and Validation-Based Protocols. Provide an example of when each technique is most useful. \[CO4\]\[UNIT-4\]\[Understand\]\[LOT\]** 31. **Discuss the types of distributed databases (homogeneous and heterogeneous) and explain the main differences between them. \[CO5\]\[UNIT-5\]\[Understand\]\[LOT\]** 32. **Compare and contrast the 2-tier and 3-tier client-server architectures. How does a 3-tier architecture improve scalability and maintainability? \[CO5\]\[UNIT-5\]\[Understand\]\[LOT\]** **Section -C** 33. **Design a conceptual database for a library management system using an ER diagram. Identify the entities, relationships, and constraints involved. Then, reduce the ER diagram to a set of relational tables, explaining each step in detail.** \[CO1\]\[UNIT-1\]\[Analyze\]\[HOT\] 34. **Explain the different types of data models (hierarchical, network, relational, and object-oriented models) in the context of database design. How does the relational model differ from the other models in terms of structure, operations, and data representation?** \[CO1\]\[UNIT-1\]\[Analyze\]\[HOT\] 35. Explain in detail the working of the join strategy. \[CO2\]\[UNIT-2\]\[Analyze\]\[HOT\] **Perform the following operations:** **5. CROSS JOIN** 36. **Consider the following database schema for an online shopping system:** a. **Customers(CustomerID, Name, Email)** b. **Products(ProductID, ProductName, Price)** c. **Orders(OrderID, CustomerID, OrderDate)** d. **OrderDetails(OrderID, ProductID, Quantity)** 37. Discuss how functional dependencies and decomposition techniques are applied in the design of a banking system database. Provide examples of relations and demonstrate normalization. \[CO3\]\[UNIT-3\]\[Analyze\]\[HOT\] 38. **Consider the following relation:** Employee (EmpID, EmpName, DeptID, DeptName, Skills) **Functional dependencies:** f. EmpID → EmpName, DeptID g. DeptID → DeptName h. EmpID, DeptID → Skills 39. Describe concurrency control protocols, focusing on their impact on transactions and performance. Discuss Lock-Based, Timestamp-Based, and Validation-Based Protocols, including implementation, advantages, and limitations. \[CO4\]\[UNIT-4\]\[Analyze\]\[HOT\] 40. **Describe the architecture and components of a 3-tier client-server architecture. How does this model support scalability, security, and flexibility in distributed applications? Provide a detailed analysis with use cases.** \[CO5\]\[UNIT-5\]\[Analyze\]\[HOT\] \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--End\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-- **[Summary Sheet]** **[Course Outcomes (CO) Wise]** +-----------------------+-----------------------+-----------------------+ | **CO** | **Q. No** | **Marks** | +=======================+=======================+=======================+ | CO1 | ---- ---- ---- ---- | 95 | | | ---- ----- ----- --- | | | | -- ----- ---- | | | | 1, 2, 3, 4, | | | | 5, 26, 27, 28, | | | | 41, 42 | | | | ---- ---- ---- ---- | | | | ---- ----- ----- --- | | | | -- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | CO2 | ---- ---- ---- ---- | 95 | | | ----- ----- ----- -- | | | | --- ----- ---- | | | | 6, 7, 8, 9, | | | | 10, 29, 30, 31 | | | | , 43, 44 | | | | ---- ---- ---- ---- | | | | ----- ----- ----- -- | | | | --- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | CO3 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 11, 12, 13, 1 | | | | 4, 15, 32, 33, | | | | 34, 45, 46 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | CO4 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 16, 17, 18, 1 | | | | 9, 20, 35, 36, | | | | 37, 47, 48 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | CO4 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 21, 22, 23, 2 | | | | 4, 25, 38, 39, | | | | 40, 49, 50 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | **Total** | **475** | | +-----------------------+-----------------------+-----------------------+ **[Unit Wise]** +-----------------------+-----------------------+-----------------------+ | **Unit** | **Q. No** | **Marks** | +=======================+=======================+=======================+ | Unit 1 | ---- ---- ---- ---- | 95 | | | ---- ----- ----- --- | | | | -- ----- ---- | | | | 1, 2, 3, 4, | | | | 5, 26, 27, 28, | | | | 41, 42 | | | | ---- ---- ---- ---- | | | | ---- ----- ----- --- | | | | -- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | Unit 2 | ---- ---- ---- ---- | 95 | | | ----- ----- ----- -- | | | | --- ----- ---- | | | | 6, 7, 8, 9, | | | | 10, 29, 30, 31 | | | | , 43, 44 | | | | ---- ---- ---- ---- | | | | ----- ----- ----- -- | | | | --- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | Unit 3 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 11, 12, 13, 1 | | | | 4, 15, 32, 33, | | | | 34, 45, 46 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | Unit 4 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 16, 17, 18, 1 | | | | 9, 20, 35, 36, | | | | 37, 47, 48 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | Unit 5 | ----- ----- ----- - | 95 | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 21, 22, 23, 2 | | | | 4, 25, 38, 39, | | | | 40, 49, 50 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | +-----------------------+-----------------------+-----------------------+ | **Total** | **475** | | +-----------------------+-----------------------+-----------------------+ **[Blooms Taxonomy Level (BTL) Wise]** +-----------------------+-----------------------+-----------------------+ | **BTL** | **Q. No** | **Marks** | +=======================+=======================+=======================+ | LOT | 1, 2, 3, 4 | 195 | | | , 7, 8, 9, | | | | 10, 11, 12 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ---- | | | | 13, 14, 16, 1 | | | | 7, 18, 19, 20, | | | | 21, 23, 24 | | | | 26, 27, 29, 3 | | | | 3, 34, 35, 36, | | | | 37, 39, 26 | | | | 27, 29, 33, 3 | | | | 4, 35, 36, 37, | | | | 39, | | | |     | | | |       | | | |       | | +-----------------------+-----------------------+-----------------------+ | | | | +-----------------------+-----------------------+-----------------------+ | | | | +-----------------------+-----------------------+-----------------------+ | HOT | 5, 6, 15, 2 | 285 | | | 2, 25, 28, 30, | | | | 31, 32, 38 | | | | ----- ----- ----- - | | | | ---- ----- ----- ---- | | | | - ----- ----- ----- | | | | 40, 41, 42, 4 | | | | 3, 44, 45, 46, | | | | 47, 48, 49, | | | | 50 | | | | | | | | | | +-----------------------+-----------------------+-----------------------+ | | | | +-----------------------+-----------------------+-----------------------+ | **Total** | **475** | | +-----------------------+-----------------------+-----------------------+