Skip to content

Database Systems

SWE · Final · Summer 25

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, Summer 2025 Course Code: SE 223; Course Title: Database System Sections & Teachers: NJ(A), SS(B,K), KRA(C, F), ZZZ(D), AR(E, G,H), FM(I), AAM(J) Time: 2:00 Hrs Marks: 40 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. a) Salesman salesman_id | name | city | commision 5001 | Steven | New York | 0.15 5002 | John Doe | Paris | 0.13 5003 | Lex | London | 0.11 5004 | Alexander | Paris | 0.14 5005 | Bruce | Rome | 0.15 Customer cust_id | cust_name | city | grade | salesman_id 3001 | Nick Rimando | New York | 100 | 5001 3002 | Brad Davis | New York | 200 | 5001 3003 | Graham Zusi | California | 200 | 5005 3004 | Julian Green | London | 300 | 5003 3005 | Brad Guzan | Paris | 300 | 5005 3006 | Jozy Altidor | Berlin | 100 | 5002 Solve these SQL queries for the given question: a. Retrieve the names and cities of all salesmen who are based in Paris. b. Find the average commission of all salesmen. c. List the customer names and their corresponding salesmen's names. d. Display all salesmen and their customers, including salesmen who have no customers. e. Show the total number of customers handled by each salesman. f. List salesmen who handle more than 1 customer. g. Find the names of customers served by salesmen from New York. (Nested Query) h. Display the names of customers whose names start with the letter B. i. Retrieve the names of customers whose grade is between 200 and 300. j. Find the names of salesmen who serve customers with a grade higher than the average grade of all customers. (Nested Query) [Marks-10] [CLO-3, Level-3] 2. Consider the following tables: BookStore CustomerID: C0012 | ISBN: 234AZ CustomerName: Alice Jhonson | BookTitle: Introduction to AI CustomerPhone: 555-1234 | AuthorName: John Smith CustomerAddress: 56 Maple Street | AuthorCountry: USA OrderID | OrderDate | PublisherName | PublisherAddress | Genre | Price | Quantity | TotalAmount | PaymentMethod 101 | 2025-08-01 | TechPress | New York | Technology | 50 | 2 | 100 | Credit Card 101 | 2025-08-01 | TechPress | New York | Technology | 60 | 1 | 60 | Credit Card 102 | 2025-08-02 | EduWorld | London | Technology | 55 | 1 | 55 | Cash 103 | 2025-08-02 | WordCraft | Toronto | Literature | 40 | 3 | 120 | Credit Card a) Examine the given BookStore table and show step by step how it can be normalized from 1NF to 3NF, explaining clearly what happens at each stage. [Marks-8] [CLO-4, Level-4] b) Differentiate between full functional dependency and partial dependency using the tables above. [Marks-4] [CLO-4, Level-4] c) Student StudentID | StudentName | CourseID | CourseName | Instructor | InstructorPhone | Grade Analyze the given Student table and identify its current normal form (UNF, 1NF, 2NF, or 3NF), justifying your answer. [Marks-4] [CLO-4, Level-4] d) Explain four anomalies that may exist in the above tables. [Marks-4] [CLO-4, Level-4] 3. a) Illustrate an ER diagram from the tables in question 2, showing attribute types, cardinality, and relationships. [Marks-5] [CLO-2, Level-4] 4. a) Describe each ACID property and explain how it helps keep database transactions reliable, accurate, and well-coordinated. [Marks-3] [CLO-5, Level-2] b) Visualize the stages of the transaction with a proper explanation. [Marks-2] [CLO-5, Level-2]