Skip to content

Database Management System

CSE · Final · Fall 23

Batch 60

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 Department of Computer Science and Engineering Faculty of Science & Information Technology Final Examination, Fall-2023 Course Code: CSE311 Course Title: Database Management System Level: 3 Term: 1 Batch: 60 Time: 2 Hours 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. a) Discover Normalization for the following table up to Third Normal Form. Be certain to indicate necessary constraints. [10] [CO4] SSN | Contract_No | Weekly_Hours | Staff_Name | Restaurant_No | Restaurant_Address ZA75632, SQ54782 | N8005 | 20, 23 | Alex, Stuart | R81 | London MX68931, TR78925 | N8006 | 21, 24 | Marry, George | R84 | Manchester FH18237, HJ78925 | N8007 | 22, 25 | David, Michel | R87 | Birmingham KL61481 | N8008 | 23 | Chapel | R88 | London 2. a) Consider the following relational schema representing a university database: Student (StudentID, Name, Age, Department) Course (CourseID, Title, Credits) Enrollment (StudentID, CourseID, Semester, Grade) Department (DepartmentID, DepartmentName, HeadOfDepartment) Professor (ProfessorID, Name, DepartmentID) Now, write the SQL query and relational algebra expression for every following question - i. Retrieve the names of students who have enrolled in the "Database Management System" course. ii. Find the total number of credits taken by each student. iii. Find the average age of students in the "Computer Science" department. iv. List the names of professors who are also heads of departments. v. Display the names and grades of students who have taken the "Data Structures" course in descending order of grades. [5*2] [CO2] b) Write the following queries in SQL (use subquery only): i. List the names of professors who teach courses in the "Computer Science" department. ii. Retrieve the names of students who have enrolled in more than two courses. [2*2] 3. a) In a student information database, design a stored procedure that calculates the average grade for a given student based on their individual subject grades. Specify the parameters for the stored procedure and give a brief example of how it would work. [3] [CO3] b) In your employee database, you have a table named Salaries that stores the salary information for each employee. Due to a company-wide salary adjustment, you want to increase all employees' salaries by 10%. Create a trigger that, whenever a salary update is attempted, automatically calculates and updates the salary by applying a 10% increase and explain how it would work. [3] 4. a) In a company's database system, there is a database containing employee information. The database administrators have designed multiple views for different departments within the company. The HR department has access to a view that displays employee names, contact details, and job titles. The IT department has a separate view showing employee names, system access levels, and hardware allocation. Additionally, the physical level consists of Various data files stored on different servers, while the logical level defines the relationships between different tables such as employees, departments, and projects. Identify the levels of data abstraction (view, physical, and logical) in the given scenario of the company's database system and explain their roles or functionalities within the database structure. [3] [CO3] b) Explain the significance of ACID properties in ensuring database transaction reliability and consistency. Provide examples illustrating each of the ACID properties in a database system. [4] c) BEGIN TRANSACTION; CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR (50), Salary DECIMAL (10, 2)); INSERT INTO Employees (ID, Name, Salary) VALUES (1, 'John', 50000.00); INSERT INTO Employees (ID, Name, Salary) VALUES (2, 'Emma', 60000.00); INSERT INTO Employees (ID, Name, Salary) VALUES (3, 'Alex', 55000.00); SAVEPOINT sp1; UPDATE Employees SET Salary = 52000.00 WHERE ID = 2; SAVEPOINT sp2; INSERT INTO Employees (ID, Name, Salary) VALUES (4, 'Sophia', 52000.00); INSERT INTO Employees (ID, Name, Salary) VALUES (5, 'Michael', 58000.00); SAVEPOINT sp3; UPDATE Employees SET Salary = 57000.00 WHERE ID = 3; ROLLBACK TO sp2; SAVEPOINT sp4; UPDATE Employees SET Salary = 54000.00 WHERE ID = 1; ROLLBACK TO sp1; SAVEPOINT sp5; INSERT INTO Employees (ID, Name, Salary) VALUES (6, 'Olivia', 59000.00); INSERT INTO Employees (ID, Name, Salary) VALUES (7, 'William', 63000.00); ROLLBACK TO sp4; COMMIT; Based on the provided SQL transactions, what will be the final state of the 'Employees' table after executing the entire script? Provide the contents of the 'Employees' table at the end of the transactions, considering the effects of COMMIT, ROLLBACK, and SAVEPOINT commands. [3]