Skip to content

Database Systems

SWE · Final · Spring 24

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 Software Engineering Faculty of Science & Information Technology Final Examination, Spring 2024 Course Code: SE 223; Course Title: Database Systems Sections & Teachers: NJ, KRA, AM 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. a) Employee Employee_id | Employee_name | Department_ID | Department 101 | Steven | 200 | HR 202 | John Doe | 201 | Finance 301 | Lex | 114 | Marketing 402 | Alexander | 203 | Accounting 501 | Bruce | 121 | IT Salary EMPLOYEE_ID | Salary | PHONE_NUMBER | HIRE_DATE | Department_ID 101 | 20000 | 5151234567 | 2003-06-17 | 200 | 202 | 3598 | 5241234567 | 2005-09-21 | 201 | 301 | 3000 | 5151258567 | 2001-01-13 | 114 | 404 | 79000 | 5151248567 | 2006-01-03 | 203 | 505 | 348799 | 515489567 | 2005-06-25 | 121 | 102 | 12390 | 578489567 | 2006-02-05 | 123 | Develop SQL queries for the given question: a. Retrieve the average salary for each department. b. Find all employees who earn more than John Doe. c. Find those employees who earn more than the average salary. d. Count the number of employees in each department. e. Get the details of employees who work in the "Marketing Department". f. Find all employees who do not have a salary entry. g. Get the employee information whose name starts with "A". h. Find the phone numbers of those employees who were hired between 17th June 2003 and 3rd January 2006. i. Find all employees whose salary is higher than the average salary of their department. j. Find the employee id who gets the highest salary. [Marks-10] | CLO-3, Level-3 2. Consider the following tables: Topic: DIU library Management DIU library management data where student's information is recorded, when a student borrowed a book, when he returned etc. StudentID: 02422200051888888 StudentName: Sumaiya StudentEmail: sumu23@gmail.com StudentPhoneNo: 01789955454 BorrowlD: 04 DepartmentName: SWE DepartmentNo: 35 BorrowedDate: 20-04-24 ReturnDate: 04-05-24 BookID | BookName | AuthorName | Publisher 04 | To Kill a Mockingbird | Harper Lee | HarperCollins 05 | 1984 | Georg Orwell | Penguin Books 06 | The Great Gatsby | F. Scott Fitzgerald | Scribner a) Normalize the table from 1NF up to BCNF; examining every step of the process from those tables given in question 2 with proper explanation. [Marks-6] | CLO-4, Level-4 b) Distinguish between full functional dependency and partial dependency from the above tables. [Marks-4] | CLO-4, Level-4 c) List the normalization rules and explain why we need normalization to design databases. [Marks-4] | CLO-4, Level-4 d) Analyze four anomalies that may exist in the above tables. [Marks-4] | CLO-4, Level-4 e) Student_Id | Student_name | Student_Address | Course_no | Course_title Explain the current normalization level of the given table. [Marks-2] | CLO-4, Level-4 3. a) Establish an Entity relationship diagram based on the tables given in question 2 with appropriate attribute type, cardinality, and relationship. [Marks-5] | CLO-2, Level-3 4. a) Demonstrate the transaction properties along with an explanation of any two properties, and explain the states of the transaction. [Marks-3] | CLO-5, Level-2 b) Define each component of ACID properties, and how they ensure reliability, integrity, and concurrency control in database transactions. [Marks-2] | CLO-5, Level-2