Nguyễn Phùng Thịnh – SE1720 – DBI202
We have the database consist of 5 relations:
Product (ProductCode, Name, PurchasePrice, SellPrice, Type,
SupplierCode)
Supplier (SupplierCode, SupplierName, Address)
Employee (EmloyeeID, FullName, Gender, BirthDate, Address)
Invoice (InvoiceID, SellDate, EmployeeID)
InvoiceLine(ProductCode, InvoiceID, Quantity)
Excersise 1:
a. Find name and sell price of televisions supplied by
Samsung.
R1 := Supplier ⋈ Product
Result := π(Name, SellPrice) ( σ(SupplierName = “Samsung”) (R1))
b. Find name and address of all suppliers who supply
television product.
R1 := Supplier ⋈ Product
Result := π(SupplierName, Address) ( σ(type = “television”) (R1))
c. Find name of all employee who were born in 1983.
Result := π(FullName)σ(BirthDate = 1983) (Employee)
d. Find name and type of all products sold in ‘23/05/2018’.
R1 := π(InvoiceID)(σ(SellDate = “23/05/2018”) (Invoice))
R2 := R1 ⋈ InvoiceLine
R3 := R2 ⋈ Product
Result := π(Name,Type) (R3)
e. Find name of female employees who sold televisions.
R1 := π(Address) (σ(gender = “female”) (Employee))
R2 := R1 ⋈ Supplier
Result := R3 := π(name) (R2 ⋈ Product)
f. Find name and address of suppliers who supply both
television and mobile.
R1 := Product ⋈ Supplier
Result := π(SupplierName,Address) (σ(type=”television” ^ type=”mobile”)(R1))
g. List name and price of all product sold by employee
“Nguyễn Văn A” in April 2018.
R1 := π(EmployeeID)( σ(FullName=”Nguyễn Văn A”)(Employee))
R2 := R1 ⋈ Invoice
R3 := π(InvoiceID)( σ(SellDate >= “01/04/2018” ^ SellDate <= “30/04/2018”)(R2))
R4 := π(ProductCode)( R3 ⋈ InvoiceLine)
Result := π(Name,Price)(R4 ⋈ Product)
h. Find name and price of all mobile products of Samsung
sold in April 2018.
R1 := π(SupplierCode)(σ(SupplierName=”SamSung”)(Supplier))
R2 := R1 ⋈ Product
Result := π(Name,SellPrice)(σ(SellDate >= “01/04/2018” ^ SellDate <=
“30/04/2018”)((R2 ⋈ InvoiceLine) ⋈ Invoice))
i. Find the product with highest SellPrice.
R1 := Product R2 := Product
R3 := R1 ⋈(R1.SellPrice < R2.SellPrice) R2
Result := R1 – R3
j. Find the amount (quantity * sellPrice) of each invoice line
of product sold in 30/04/2018.
R1 := π(InvoiceID)( σSellDate = “30/04/2018”(Invoice))
R2 := (R1 ⋈ InvoiceLine) ⋈ Product
Result := π(Quantity * SellPrice -> Amount)(R2)
Excersice 2: Use Relational Algebra to express following
constrains:
a. The sell price must be higher than the purchase price.
Result := σSellPrice < PurchasePrice (Product) = ∅
b. A product of SamSung must be television, mobile or tablet.
R1 := π(SupplierCode)( σ(SupplierName = “SamSung”)(Supplier))
Result := σ(type <> “television” ^ type <> “mobile” ^ type <> “tablet”)(R1 ⋈
Product)
c. No supplier of mobile’s or tablet’s may also supply food.
R1 := Product
Result := σ(Product.Type= “mobile” ^ R1.type = “food”) v (Product.Type= “tablet” ^
R1.type = “food”)(Product x R1) = ∅
d. No prodcut may appear mỏe than one time in an invoice.
R1 := InvoiceLine R2 := InvoiceID
Result := σ(R1.InvoiceID = R2.InvoiceID ^ R1.ProductCode = R2.ProductCode)(R1
x R2) = ∅
e. The quantity of each product in each invoice should be
greater than 0.
Result := σ(Quantity <= 0)(InvoiceLine) = ∅
f. There is no invoice without product.
Result := π(ProductID)(InvoiceLine) - π(ProductID)(Product) = ∅
g. If purchase price is less than 500.000 VND, the sell price
could not be greater than 9.000.000 VND.
Result := σ(PurchasePrice < 500000 ^ SellPrice > 9000000)(Product) = ∅
h. The sell price could not be greater than 2 times the
purchase price.
Result := σ(SellPrice > PurchasePrice * 2)(Product) = ∅
i. The gender of an employee should be “Nam” or “Nữ”.
Result: = σ(gender <> “Nam” ^ gender <> “Nữ”)(Employee) = ∅
j. With the same purchase price, the sell price of two products
could not have the difference more than 0.5 times of the
purchase price.
R1 := Product R2 := Product
Result := σ(R1.PurchasePrice = R2.PurchasePrice ^ ABS(R1.SellPrice - R2.SellPrice)
> 0.5 * R1.PurchasePrice)(R1 x R2) = ∅
