Database Management System
CSE · Final · Fall 24
Paper 2 of 3Batch 61 · Abdullah Tawsif (CSE-232)Switch
Batch 61
Paper 2 of 3
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Daffodil International University
Faculty of Science & Information Technology
Department of Computer Science & Engineering
Final Semester Examination, Fall 2024
Course Code: CSE311, Course Title: Database Management System
Level: 3 Term: 2 Batch: 61
Time: 2:00 Hrs
Marks: 40
Answer ALL Questions
[The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.]
1. Consider the following relational schemas. [6] [CO1]
Employee (eid, emp_name, emp_address, speciality)
Project (pid, project_name)
ProjectAssigned (eid, pid)
Construct the following queries using relational algebra.
a) Write a query to display the names of all employees.
b) Write a query to find employees who do not have the speciality 'Civil Engineer'.
c) Generate a query to find projects where the project name is 'Road Development'.
d) Generate a query to rename the emp_name column to employee_name.
e) Develop a query to find all project IDs from both the Project and ProjectAssigned tables.
f) Create a query to list the projects that have not been assigned to any employee.
2. Nahid and Akash are two friends who tried to book tickets online during Eid. Nahid successfully reserved a seat but didn't complete the purchase. Akash completed all the steps, including entering the payment OTP, but refreshed the page after submitting the OTP, which caused the transaction to fail. Now describe the different stages of a transaction and apply your understanding to determine which stages caused Nahid and Akash's transactions to fail. Additionally, if an error occurs during the process, how would you use COMMIT and ROLLBACK to handle the transactions and ensure everything works correctly? [6] [CO3]
3. The Products table contains information about products, Product(ProductID, ProductName, Category, Price) [5] [CO2]
i) Construct a stored procedure named "selected_product" that accepts two input parameters which are a category name and a price. It should fetch the ProductID, ProductName, and Price of products that belong to the specified category and have a price greater than or equal to the specified price.
ii) Create a view named "highRevenueProducts" that displays the ProductName, Price, and AnnualRevenue (assuming Price represents the monthly revenue) for products with a price greater than 10,000.
4. In a hospital management system, the IT department has been tasked with tracking deleted patient records for auditing purposes. The hospital's database includes a Patient table, which stores details such as PatientID, PatientName, DateOfBirth, AdmissionDate, and DischargeDate. To ensure that any deletion of patient records is logged, you are required to create a DeletedPatientLog table to store information about deleted records. This table should include LogID (auto-increment, primary key), PatientID, PatientName, DateOfDeletion, and DeletedBy (a field that captures who deleted the record, with a placeholder like 'System'). Analyze the scenario and provide an SQL for creating both tables. Additionally, ensure that a trigger is created on the Patient table to automatically log any deleted patient records into the DeletedPatientLog table, including the patient's ID, name, deletion date, and the user who performed the deletion. [5] [CO2]
5. customers(customer_id, name, email, age, phone)
products(product_id, product_name, price)
orders(order_id, customer_id, product_id, order_date, quantity) [8] [CO3]
Based on the given relational tables, construct the SQL queries to answer the following questions.
a) List the name, email, order id and order date of customers who have placed orders for products, showing the most recent orders first.
b) Display the order id, the total number of products in each order, and the total price for all products in that order.
c) Retrieve the details of all customers and their orders whose names start with 'J' or end with 's', including customers who have not placed any orders.
d) Retrieve the order id, order date, product name, and product price for each order, including products that have not been ordered yet, and only include products where the price is greater than 170 Tk.
6. Demonstrate how distributed database systems ensure data availability compared to a centralized DBMS and discuss how access control mechanisms like role-based access control (RBAC) and encryption can enhance security in these systems to protect sensitive data from unauthorized access. [5] [CO3]
7. Explain how the CAP theorem applies to NoSQL databases and describe the different types of NoSQL databases and their key characteristics. [5] [CO1]