Database Systems
ITM · Final · Fall 23
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