Database Systems
SWE · Final · Spring 24
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