Skip to content

Database Management System

CSE · Midterm · Fall 24

Batch 62 and 63

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

Read the questions as text

Read off the PDF by AI, so it may have mistakes. The PDF is the original.

Daffodil International University Faculty of Science & Information Technology Department of Computer Science & Engineering Midterm Examination, Fall 2024 Course Code: CSE311, Course Title: Database Management System Level: 3, Term: 1, Batch: 62 & 63 Time: 01: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. Suppose you want to design a software system to manage Gym Membership of DIU. You tried to talk with the gym supervisor to find out the detailed information about gym management. The requirements are given below, The gym has a registration process for the students, faculties and staff of DIU. Users should be able to register for an account with their personal information, including name, ID, contact information, and a unique username and password. Registered users should be able to log in securely with their credentials. Gym trainers and administrators, should have additional privileges and access rights. The system should provide information about different membership plans, including their features, pricing, and duration (e.g., monthly, annual). No membership plan with the same type (regular or economic), price and duration are allowed to be set. However the same plan may have multiple working features in it. Prospective members should be able to view and compare the available plans before making a decision. Prospective gym members should be able to enroll in a specific membership plan. The system should handle the payment process for membership fees, with options for payment methods (e.g., credit card, cash). Members should be able to schedule fitness training sessions, one-on-one consultations with trainers. Staff members should be able to manage and confirm these appointments. The system should track member attendance, allowing staff to record check-ins and check-outs. The check in day and time should be unique for a specific member as no member is allowed to enter twice in the same day. Attendance records can help in assessing member engagement and plan utilization Now Design an ER diagram based on the given scenario. Be certain to indicate necessary constraints. | [7] | CO1 2. [Figure: ER diagram showing entities Customer, Employee, Product, Category, and their relationships (purchases, supports, manages, supervises, belongs to)] Explain How above ER diagram can be converted into a relational schema. | [4] | CO1 3. Perform the necessary SQL queries or sub queries to answer the following queries based on the two tables: Table 1 Employee ID | Name | Department | Gender | Salary 510046 | Rahab | IT | M | 30000 510047 | Iqram | Finance | M | 56000 510048 | Susmi | HR | F | 63000 510049 | Baker | HR | M | 25000 510050 | Alif | IT | M | 37000 510050 | Alam | IT | F | 32000 Table 2 Project ID | Project Name | Supervisor Name 115 | Cloud Migration | Dr. Esrat 116 | Data Analytics | Mr. Abu Sufian 117 | Green Computing | Dr. Shaha 118 | Database | Mr. Andrew a) Add a column Age to Table 1 with an integer data type. b) Retrieve the Employee ID and name of employees whose salary is greater than 50,000 or whose second last character in their name is 'a' in Table 1. c) Display the highest salary of female employees in the HR department in Table 1. d) Modify the data type of the Project ID column to VARCHAR(20) and insert a new record with values ('119', 'Mobile App Launch', 'Ms. Emma') in Table 2. e) Update the record with Supervisor Name from 'Mr. Andrew' to 'Mr. Akash' where the Project ID is 118 in Table 2. f) Retrieve the names of employees who work in the same department as 'Rahab' g) Find the name of the employee who has the lowest salary in the 'HR' department. | [8] | CO2 4. Customer ID | Customer Name | Phone | ZIP Code | City | Order ID | Delivery Person | Order Status 1055 | Hasibul Islam | +8511111 | 1002 | Dhaka | 101 | Arif | Delivered 1056 | Rashid Mia | +6127777 | 1345 | Chittagong | 167 | Rahad | Processing 1057 | Labiba Islam | +3888888 | 4833 | Khulna | 205 | Farhan | Cancelled 1058 | Jamal Uddin | +3333333, +2555555 | 8765 | Bogura | 209 | Muzahid | Delivered 1059 | Mahbubul Alam | +1122222 | 3465 | Rajshahi | 214 | Rafi | Delivered 1060 | Tasfia Ahmed | +125678, +7890123 | 5432 | Sylhet | 314 | Nayan | Processing Simplify and break down the process of normalizing the table shown in figure up to 3rd Normal Form. State any assumptions you make about the data shown in this table. Consider that Composite key is(Customer id ad Order Id) | [6] | CO1 Note: All corresponding COs for each question are listed in the rightmost cell of each row.