Database Systems
SWE · Final · Summer 25
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]