Skip to content

Database Systems

SWE · Final · Spring 22

Section A, PC-B

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 Department of Software Engineering Faculty of Science & Information Technology Final Examination, Spring 2022 Course Code: SE223 (CC+DSC) Course Title: Database Systems Level: 2 Term: 2 Section: A, PC-B Course Teacher: NJ Date: Monday, 18 April, 2022 Time: 11:45 AM - 01:45 PM Marks: 40 Time: 2 Hours Answer All the Questions Question 01 [CLO-3, Level-3] [Marks-15] Student | Competition: StudentId | Name | Age | Address | RegistrationDate | StudentId | Registration Fee | Competition Type 181-35-2222 | Raihan | 18 | Mirpur | 2022-02-12 | 181-35-2222 | 200 | Business Idea 181-35-2224 | Rakhi | 17 | Ashulia | 2022-02-12 | 181-35-2222 | 300 | Mobile App 181-35-2225 | Alif | 22 | Mirpur | 2022-02-14 | 181-35-2224 | 200 | Business Idea 181-35-2226 | Aman | 20 | Ashulia | 2022-02-15 | 181-35-2225 | 300 | Mobile App 181-35-2228 | Niloy | 23 | Dhanmondi | 2022-02-15 | 181-35-2226 | 300 | Mobile app 2022-02-15 | 181-35-2226 | 250 | Robotics Competition 2022-02-15 | 181-35-2228 | 200 | Business Idea 2022-02-15 | 181-35-2228 | 300 | Mobile App 2022-02-15 | 181-35-2226 | 250 | Robotics Competition a. Write sql statement to find the number of students who did register in 15th February 2022. b. Write sql statement to find student name of those students whose age more than the highest age. (using sub query) c. Write sql statement to find the number of students who have done their registration "Mobile app" competition. d. Write sql statement to find the total registration fee collected for each competition. e. Write sql statement to find ID of those students who participated in competition. f. Write sql statement to find the student ID whose name starts with 'N'. g. Write sql statement to find those students information who lives in Mirpur but age should be less than 22. h. Write sql statement to find those student name whose age is greater than any age of the student who lives in Mirpur. (using subquery) i. Write sql statement to delete information of those students who lives in Ashulia. Write output of the following SQL command k. Select * from student where age>23 1. Select Distinct(StudentID) from competition m. Select student.StudentID from student inner join competition on student.studentID=competition.studentID n. Alter table student add column Phone int. o. Select * from student order by age Question 02 [CLO2, Level 4] [Marks 25] a. Consider the Billing Receipt for Project Development Form given below: [Figure: Billing Receipt for Project Development Form containing Project No: 1001, Project Name: Class Monitoring System, Dept No: 35, Dept Location: Ashulia, Dhaka, Bill No: 405, Bill Date: 12/01/22, Dept Name: Marketing, and a table with EmployeeNo, Name, Responsibility, Rate/Hour, Chargeable Hour, and Total for Arif Rahman, Rani, and Afjal, ending with Grand Total.] a. Normalize the table up to Third Normal Form (3NF); explaining every steps of the process. b. Describe composite key based on the question given in a. c. List the rules of normalization and explain why we need normalization to design database. d. Explain anomalies for the question b. e. Online Retail Application: A customer can register to purchase an item. The customer will provide the bank account number and bank name (can have multiple account numbers). After registration, each customer will have a unique customer, user id, and password. A customer can purchase one or more items in different quantities. The items can have different classes based on their prices. Based on the quantity, the price of the item, and discount (if any) on the purchased items, the bill will be generated. A bank account is required to settle the bill. The items can be ordered from one or more suppliers. Draw an Entity relationship diagram based on the project you get from the above link (minimum 4-6 entity) with appropriate attribute type, cardinality, and relationship. f. Differentiate between multi valued attribute and derived attribute with appropiate example.