Skip to content

Database Management System

CSE · Final · Spring 25

Paper 1 of 2Batch 64 · Abir Mia -231Switch

Batch 64

Paper 1 of 2

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

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, Spring 2025 Course Code: CSE 311, Course Title: Database Management System Level: 3 Term: 1 Batch: 64 Time: 2:00 Hrs Marks: 40 Answer ALL Questions [Optional] [The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.] 1. a) You are given the following unnormalized table that stores student course enrollment information. Apply the principles of database normalization to systematically transform the table step by step into Third Normal Form (3NF). Clearly demonstrate each step (1NF, 2NF, 3NF) by identifying functional dependencies, keys, and necessary decomposition to eliminate anomalies. S_ID | S_Name | Course_ID | Course_Name | Instructor | Instructor_Office | Department | Course_Fee 101 | Alice | CSE101, CSE102 | Database, OOP | Dr. Smith, Dr. Brown | R-101, R-102 | CSE | 5000, 6000 102 | Bob | CSE101 | Database | Dr. Smith | R-101 | CSE | 5000 103 | Charlie | CSE103, CSE102 | PPS, OOP | Dr. Green, Dr. Brown | R-103, R-102 | SWE, CSE | 5500, 6000 [6] | CO1 b) Explain the differences between partial and transitive dependencies in the context of database design. Support your explanation with appropriate examples for each. [4] 2. a) Doctors(DoctorID, Name, Specialty, Phone) Patients(PatientID, Name, DOB, Phone, Gender) Appointments(AppointmentID, DoctorID, PatientID, AppointmentDate, Diagnosis, Treatment) Based on the given Schemas, construct the SQL queries to answer the following questions: i) Use an inner join to find each doctor's name and the total number of appointments they handled in the year 2024. ii) Use a left join to list all doctors along with their latest appointment date, if any, including doctors who have never had an appointment. Sort the results by appointment date in descending order. iii) Use a full outer join, list all doctors and their corresponding appointments. Include doctors who do not have any appointments, and appointments that do not have a corresponding doctor. Show the doctor's name and the appointment date, with null values where appropriate. iv) Write an sql query to find the doctor with the most appointments. [6] | CO2 b) Based on the provided schemas, answer the following questions: * Books(BookID, Title, Author, Genre, Price) * Sales(SaleID, BookID, Quantity, SaleDate) i) Construct a stored procedure named "getBooksByGenre" that accepts a genre name as a parameter. It should fetch the BookID, Title, and Author of all books belonging to the specified genre. ii) Design a view named "monthlySalesView" that calculates the total sales for each book in a given month. The total sales should be computed by multiplying the price of each book by the quantity sold. The view should display the BookID, Title, and the total sales for each book during the month. [6] c) In an E-commerce system, the sales department needs to track customer orders for auditing and analysis purposes. The database includes an Orders table with details like OrderID, CustomerID, ProductID, Quantity, and OrderDate. To achieve this, create an OrderLog table with LogID (auto-increment, primary key), OrderID, OrderDate, Quantity, InsertedBy (e.g., 'adminUser'). Analyze the scenario and provide an SQL for creating both tables. Additionally, Implement a trigger on the OrderLog table to automatically log details of new order entries into the OrderLog table upon insertion. [4] d) Apply the concept of transactions to the following scenario: You are tasked with transferring $200 from Account A to Account B in a banking system. Using SQL, construct the code for a transaction that does the following: * Verify that Account A has a balance of at least $200 and subtract $200 from Account A. * Add $200 to Account B. * Commit the transaction if both operations are successful. * If Account A does not have enough funds (less than $200), rollback the transaction, ensuring that no changes are made to the database. * Handle any potential errors during the transaction, ensuring the transaction is rolled back to maintain data consistency. [4] 3. A university database system manages student records, faculty details, and administrative operations, enforcing different access levels for various users. Students can only view their own grades and update personal details, while faculty members can modify grades for their own courses but not those of others. Administrative staff have broader access, including modifying course assignments, but cannot alter faculty credentials or system policies. The university has expanded its campus to a different location. Now it wants to install a database in the new campus for faster processing. But since the hardware for the old database server has become old and unavailable in the market, a new server model needs to be purchased. a) Identify with proper justification the type of Access Control that can be applied to the university database. | 4 | CO3 b) Analyze the application of distributed database in the scenario and also identify which type of Distributed Database Management System (DDBMS) can be used? | 3 | c) Differentiate between the capabilities of Relational Databases and NoSQL Databases on the basis of the Scenario. | 3