Description
The following relational schema is given for an online store:
Product(PID, PName, Description)
Supplier(SID, SName, City, DateOF)
Category(CID, CName)
SoldBy(PID, SID, StockDate, Price, ExpirationDate, Amount)
InCategory(PID, CID)
Product: Stores the name and the description of products along with a unique PID.
Supplier: Stores the name, the origin city, and the date of foundation of suppliers along with
a unique SID.
Category: Stores the name of product categories along with a unique CID.
InCategroy: Associates products with categories. Each product can be in several categories
and each category can include multiple products.
SoldBy: Associates products with suppliers. StockDate is the date this product becomes
available in store (as a delivery to the store). Note that there could be multiple deliveries of
the same product by the same supplier on different dates. This table also stores the price, the
expiration date and the amount of the product that is being sold.
1. Draw the E/R Diagram that corresponds to the given relational schema. Write any
assumptions that you make. You may use draw.io or draw it on a paper. (10 points)
For rest of the questions, write the corresponding Relational Algebra expression to given
queries. (10 points each)
2. Find the name of products that are in category “Dairy” or “Vegetable”. (Note that these are
the names of the categories)
3. Find the name, the price and the expiration date of the products that are sold by the supplier
whose SID is equal to “836”.
4. Find the name of products that are sold by both “Barilla” and “Filiz”. (Note that these are
the names of the suppliers)
5. Find the product name, the stock date, and the amount of the products that are sold for a
price less than 50 TL.
6. Find the product name and the amount of the products whose expiration date is today.
7. Find the name and the origin city of the oldest supplier. (The one with the smallest date of
foundation)
8. Find the name of the suppliers that sell products from all categories.
9. Find the product name, the stock date, the price, and the amount of the products that are
sold by a supplier in “Ankara” or “İstanbul”.
10. Find the name and the price of the most expensive product in category “Toy” sold by the
supplier “Adore”. (Note that “Toy” is the name of the category and “Adore” is the name of
the supplier)
By doing this homework, you agree that you would follow Bilkent University’s policy on
plagiarism, and you accept that all the solutions belong individually to you. You also accept
that in case of an act of plagiarism, you would not get any points from this homework, and
disciplinary action will be taken.
