Skip to content

Database Systems

ITM · Final · Fall 23

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 Information Technology & Management Faculty of Science & Information Technology Final Examination, Fall 2023 Course Code: SE 223; Course Title: Database Systems Sections & Teachers: All 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) Department | Marks-10 | CLO-3 Department_id | Department_name | MANAGER_id | Location_id | Level-3 10 | Administration | 200 | 1700 20 | Marketing | 201 | 1800 30 | Purchasing | 114 | 1700 40 | Human Resources | 203 | 2400 50 | Shipping | 121 | 1400 EMPLOYEE EMPLOYEE_ID | FIRST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID 200 | Steven | 5151234567 | 2003-06-17 | AD_PRES | 24000.00 | null | 90 101 | Neena | 5241234567 | 2003-09-21 | AD_VP | 17000.00 | 100 | 90 102 | Lex | 5151258567 | 2001-01-13 | AD_VP | 17000.00 | 90 | 90 103 | Alexander | 5151348567 | 2006-01-03 | IT_PROG | 9000.00 | 102 | 60 104 | Bruce | 515489567 | 2005-06-25 | IT_PROG | 6000.00 | 103 | 60 105 | David | 578489567 | 2006-02-05 | IT_PROG | 4800.00 | 101 | 60 106 | Valli | 689489567 | 2007-02-07 | IT_PROG | 4800.00 | 101 | 60 a. Develop a SQL query to retrieve the average salary for each department. b. Develop a SQL query to find the names and salaries of employees who work in the "IT_PROG" department. c. Develop a SQL query to find those employees who earn more than the average salary. d. Develop a SQL query to find those employees who report to that manager whose first name is 'Lex'. e. Develop a SQL query to know the details of employees whose manager from "Marketing Department". f. Develop a SQL query to know the employee details whose job_id "AD_VP". g. Develop a SQL query to know the employee information whose name starts with "A". h. Develop a SQL query to the phone number of those employees who are hired in between 17th June 2003 to 3rd January 2006. i. Develop a SQL query to know the all department names. j. Develop a SQL query to find employee id who gets highest salary. Consider the following Information Hospital Management System Data PatientID: 02122000123456789 PatientName: John Doe DepartmentName: Cardiology DepartmentNo: 10 PatientEmail: johndoe@mall.com PatientPhoneNo: 01589955454 Prescription: MedicineID: M001 MedicineName: Aspirin Dosage: 75 mg daily MedicineManufacturer: Bayer VisitID: V001 VisitDate: 20-11-24 NextVisitDate: 04-12-24 MedicineID: M002 MedicineName: Atorvastatin Dosage: 10 mg nightly MedicineManufacturer: Pfizer DoctorID | DoctorName | DoctorSpecialization | DoctorPhoneNo D001 | Dr. Jane Smith | Cardiologist | 01712233445 D001 | Dr. Jane Smith | Cardiologist | 01712233445 a) Normalize the table from 1NF up to BCNF; examining every step of the process from those tables given in question 3 with proper explanation. | Marks-8 | CLO-4 Level-4 b) List the normalization rules and explain why we need normalization to design databases. | Marks-4 | CLO-4 Level-4 c) Distinguish between full functional dependency and partial dependency and transitive dependency from the above tables. | Marks-4 | CLO-4 Level-4 d) Establish an Entity relationship diagram based on the normalized tables with appropriate attribute type, cardinality, and relationship. | Marks-4 | CLO-4 Level-4 Imagine a Banking System where a user is transferring funds from Account A to Account B. The transaction consists of several steps: 1. Check Account A for sufficient funds. 2. Debit Account A by the transfer amount. 3. Credit Account B with the transfer amount. 4. Commit the transaction if all steps succeed; otherwise, rollback if any step fails. a) Explain the states of the transaction from scenario given in question 4. | 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 from scenario given in question 4. | Marks-2 | CLO-5 Level-2