Database Systems
SWE · Midterm · Spring 25
Paper 1 of 2By SIFAT SHAHARIA -43Switch
Paper 1 of 2
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
Midterm Examination, Spring 2025
Course Code: SE 223; Course Title: Database Systems
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: 1:30 Hrs Marks: 25
Answer ALL Questions
1. An online Learning Management System (LMS) is used by a university to manage courses, students, instructors, and resources. The university has several departments, each identified by a unique department ID, name, and location. Each department offers multiple courses, and each course is identified by a course ID, name, credit hours, and description. Instructors are assigned to departments and are identified by an instructor ID, name, email, phone number, and the department they belong to. Each instructor can teach multiple courses, and each course is taught by one instructor. Students are identified by a student ID, name, email, phone number, and the department they are enrolled in. Students can enroll in multiple courses, and each course can have many students enrolled. Each course has resources such as lecture notes, videos, and assignments, identified by a resource ID, type, title, and upload date. Resources are uploaded by instructors and are accessible to students enrolled in the course. The system also tracks grades for each student in a course, including the student ID, course ID, and grade (e.g., A, B, C), as well as student progress, such as the percentage of course material completed. [CLO-1, Level-4]
a) Describe the schema for the LMS system above. [Marks-2]
b) Explain data abstraction for the above-mentioned system. [Marks-3]
c) Demonstrate how data independence (logical and physical) can be achieved in the LMS system. [Marks-3]
d) Discuss how the three-tier architecture can be implemented for this system. [Marks-2]
2. a) Consider the question 1's scenario.
Illustrate an ERD identifying their entity, attributes & relationship. [Marks-5] [CLO-2, Level-4]
b) StudentID | Name | Email | Phone | DepartmentID
101 | John Doe | john@example.com | 1234567890 | 1
102 | Jane Smith | jane@example.com | 9876543210 | 2
103 | Alice Lee | lee@example.com | 5555555555 | 1
CourseID | CourseName | CreditHours | DepartmentID
C101 | Database Systems | 3 | 1
C102 | Web Development | 4 | 2
C103 | Data Structures | 3 | 1
Solve relational algebra for the following questions.
1. Retrieve the names and emails of all students in the DepartmentID = 1.
2. Find the CourseName and CreditHours of courses offered by DepartmentID = 2.
3. List the CourseID and CourseName of courses who are enrolled in the course C101.
4. Retrieve the CourseName of courses with CreditHours > 3.
5. Find the Name and Phone of students whose names start with the letter "J". [Marks-5]
3. a) Show the results of Left Join and Full Outer Join for the table Student and Course [Marks-2] [CLO-3, Level-3]
b) Solve SQL command to express each of the following queries:
I. Create those two tables using proper constraints
II. Insert data into those tables.
III. Add a new column CourseType in the course table using the alter command. [Marks-3]