Database Systems
SWE · Final · Spring 25
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 2025
Course Code: SE223; Course Title: Database System
Sections & Teachers: NJ(A), TRT(B), KRA(C, D), MMSI(F), AR(G,H,N), SAN(J,K), LA(I,L), KMH(E, M)
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. Consider the following Schema and Solve with SQL for the following questions:
Project (P_ID, Project_Name, Manager, Budget)
Employee (E_ID, Employee_Name, Address, Department_No)
Department (Department_No, Department_Name, Location)
Emp_Timehour (P_ID, E_ID, Hour_Rate)
a. Retrieve project names and the names of employees working on them.
b. Show the project name, employee name, and hour rate for projects managed by 'Alice'.
c. List all employees working on projects with budgets over 5,000,000.
d. Find the total budget of projects managed by each manager.
e. Find projects where the total payment to employees (hour rate sum) exceeds 10,000.
[Marks-10] [CLO-3, Level-3]
2. Imagine you want to withdraw money from an ATM. You provide all the required details, and the system asks for the withdrawal amount. You input the amount and confirm the process. However, during this process, an error occurs, and the transaction does not complete.
Consider the above situation. Explain the states of the transaction by drawing a state diagram. Also, explain how the system maintains consistency in this case.
[Marks-5] [CLO-5, Level-2]
3. a. Apply the concepts of insert, update, and delete anomalies to justify the need for normalization with relevant examples.
[Marks-6] [CLO-4, Level-4]
Consider the given schema and answer the following questions:
CusNo | CustName | Address | Phone | AccNo | Branch | BranchID | Balance | BranchCity | BranchManage | CustType
C-11 | Ali | Mouchak | 7114345 | A-21 | Dhanmondi | B-41 | 50000 | Dhaka | Ahad | Depositor
C-11 | Ali | Mouchak | 7114345 | A-31 | Dhanmondi | B-42 | 5000 | Dhaka | Forhad | Borrower
C-12 | Mou | Gulshan | 7114446 | A-22 | Syamoli | B-49 | 500000 | Dhaka | Bashir | Depositor
C-13 | Robi | LamaBazar | 7124345 | A-23 | LamaBazar | B-57 | 30000 | Chittagong | Moyez | Depositor
C-14 | Saif | LamaBazar | 7114845 | A-24 | LamaBazar | B-57 | 28000 | Chittagong | Moyez | Depositor
C-15 | Suny | Kamarpara | 7114645 | A-32 | Kamarpara | B-72 | 2000 | Sylhet | Feroz | Borrower
b. List the normalization rules sequentially.
[Marks-3] [CLO-4, Level-4]
c. Normalize the table from 1NF up to 3NF, examining every step of the process with a proper explanation.
[Marks-11]
4. Outline ERD from the result of Normalization that you solved in Q. 3. c.
[Marks-5] [CLO-2, Level-4]