09 March 2022
EAST WEST UNIVERSITY
Department of Computer Science and Engineering
B.Sc. in Computer Science and Engineering Program
Mid Term Assessment I (Online), Spring 2022 Semester
Course: CSE 302 Database Systems (Section 2)
Instructor: Dr. Mohammad Rezwanul Huq, Associate Professor, CSE Department
Full Marks: 100 (15 will be counted for final grading)
Time: 1 Hour 30 Minutes + 10 Minutes for uploading the answer
Note: There are 6 (SIX) questions. Answer ALL of them. Course outcome, cognitive level and the mark of
each question are mentioned at the right margin.
1. User submits query as a SQL command to the database and then gets the result back. [CO1, C2,
Explain the process of how queries are being processed within a database system. 10]
2. Consider the following database schema. [CO1, C3,
15]
• Customer (CCode, CName, CGender, CAddress, CPostCode, CBalance)
• Vendor (VCode, VName, VAddress, VPostCode)
• Product (PCode, PName, PPrice, PDiscount, VCode)
• Invoice (InvoiceNumber, CCode, InvoiceDate)
• Cart (InvoiceNumber, ItemNumber, PCode, ItemUnits, ItemPrice)
Customer relation contains customer details and CCode is the primary key.
Vendor relation contains vendor (supplier) details and VCode is the primary key.
Product relation contains product details and PCode is the primary key. VCode is the
foreign key referencing Vendor relation.
Invoice relation contains invoice details such as invoice number, customer code
(CCode) and an invoice date. InvoiceNumber is the primary key and CCode is the
foreign key.
Cart relation contains the products sold under a given invoice. The primary key in
this relation is (InvoiceNumber, ItemNumber) in which item number is more like a
serial number, i.e., 1, 2, 3 and so on, depending on the number of products sold under
the same invoice. Invoice number is a foreign key referencing the Invoice relation and
product code (PCode) is another foreign key referencing Product relation. Item units
refers to the quantity of that item and ItemPrice refers to the price of the item.
Draw the schema diagram of the above-mentioned database schema. You must
show the primary key and the referential integrity constraints appropriately in your
diagram.
Page 1 of 3
09 March 2022
3. Based on the database schema given in Question 2, Construct SQL Statements for [CO2, C3,
the following queries. 16]
a) Write a SQL Statement to create the Cart relation. Use appropriate data type. Define
appropriate constraints. Make sure that ItemUnits and ItemPrice cannot be NULL and
both must be a positive value.
b) Write a SQL Statement to add a new attribute CPhone in Customer relation. Use
appropriate data type. Assume that the Vendor relation is already created for you.
c) Write a SQL Statement to decrease the price of the product with code ‘P1-AB-01’
by 20%.
d) Write a SQL Statement to delete all the products with a discount.
4. Based on the database schema given in Question 2, Construct SQL Statements for [CO2, C3,
the following queries. 18]
a) Show customer code and name who are ‘female’ and balance are not in between
10000 and 20000 (both bounds are inclusive).
b) Find product name, product price and vendor name of those products in which their
name ends with ‘Oil’.
c) Generate a listing of all purchases made by customers according to the columns in
the result relation shown below.
CCode InvoiceNumber InvoiceDate PName ItemUnits ItemPrice
d) List all the products supplied by ‘ACI Limited’ in the ascending order of their price.
5. Using the same database schema as given in Question 2, Write Relational Algebra [CO1, C3,
Expressions for the following queries. 25]
a) Find the product code, product name and product price supplied by the vendor
‘Samsung’.
b) Find the customer’s name and address who has the lowest balance.
c) Find the number of products supplied by the vendor ‘Samsung’.
d) Calculate the total amount for each invoice. Total amount can be calculated by
summing up the multiplication of item units and item price of all items under the same
invoice.
e) Using the appropriate set operator, find the product code and name of those
products which have not been sold yet.
Page 2 of 3
09 March 2022
6. Consider the following relations as shown below. [CO1, C3,
16]
Determine the output of the following expressions.
a) Relation1 ⋈ Relation2
b) Relation1 Relation2
Page 3 of 3
