Database Management System

Daffodil International University Department of Information Technology & Management Faculty of Science & Information Technology Mid Examination, Fall 2025 Course Code: ITM 221; Course Title: Database Management Systems Sections & Teachers: All Time: 1.30 Hrs Marks: 25 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. 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. a) Describe the schema for the LMS system above. [Marks-2] CLO-1 Level-4 b) Analyze the three levels of abstraction for the LMS system [Marks-4] c) Examine the key responsibilities of a Database Administrator in ensuring data integrity and consistency when managing in the company's database? [Marks-2] 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] b) Consider the following Schema and Solve with Relational Algebra for the following questions. [Marks-5] 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 | StudentID C101 | Database Systems | 3 | 101 C102 | Web Development | 4 | 102 C103 | Data Structures | 3 | 101 Solve relational algebra for the following questions. 1. Retrieve the names and emails of all students in the DepartmentID = 1. 2. Find the student Id who yet not enrolled in any course. 3. List the CourseID and CourseName of courses 4. Retrieve the CourseName of courses with CreditHours > 3. 5. Find the Name and Phone of students who take Database Systems course. 3. a) Solve SQL command to express each of the following queries: [Marks-5] CLO-3 Level-3 I. Write two constraints with example II. Create one tables using key constraints III. Define data type with example IV. Add a new column Emp_salary in the employee table using the alter command and Project_start_date in the Project table.