Database Management System
CSE · Midterm · Fall 24
Batch 62 and 63
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.