Lab-1 compress - FDSFDSDFS PDF

Preview

Summary

Lab 1 : Algebraic query languageWe 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)Invoic...

Description

Lab 1 : Algebraic query language 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)

Exercise 1: Write expressions of relational algebra to answer the following queries: a. Find name and sell price of televisions supplied by Samsung.

R1 : 



Name, SellPrice

type 'television' and SupplierName 'Samsung'

 Product 

Supplier  

b. Find name and address of all suppliers who supply television product.

R2 :  SupplierName  type ' television'  Product  Supplier   c. Find name of all employee who were born in 1983.

R3 :  FullName   BirthDate '01/01/1983' and BirthDate '31/12/1983'  Employee  d. Find name and type of all products sold in ‘23/05/2018’.

R4 :   Name, Type   SellDate '23/05/2018'   InvoiceLine  Invoice   Product   e. Find name of female employees who sold televisions. R5 :  



FullName

Gender 'female'and Type 'Television'

  InvoiceLin e 

Invoice   Product   Employee 

f. Find name and address of suppliers who supply both television and mobile.

R61 :  SupplierName, address type 'television'  Product  Supplier   R62 :  SupplierName, address type 'mobile' Product  Supplier   R63 : R 61  R62 g. List name and price of all product sold by employee “Nguyễn Văn A” in April 2018. R 5.1  :    InvoiceLine Invoice  Product   Employee R5 :  Name, SellPrice FullName  'Nguyen Van A' and (SellDate '01/04/2018' andSellDate '30/0 4/2018')  R 5.1 

h. Find name and price of all mobile products of Samsung sold in April 2018. R7.1 :    InvoiceLin e  Invoice   Product   Employee  T ype 'mobil e'andSuppl ierName 'Samsung'and (SellDate '01/04/ 2018' andSell Date '30/0 4/2018')  R7.1  R7 :  Name, SellPrice

i. Find the product with highest SellPrice.



R8 :  ProductID, Name, Type  SellPrice  all( SellPrice(Product)) Product 



j. Find the amount (quantity * sellPrice) of each invoice line of product sold in 30/04/2018. R 10.1:   InvoiceLine Invoice  Product R10 :  Quanti ty*SellPrice Amount  Sell Da te '30/ 04 /2018'  R10.1  

Exercise 2: Use Relational Algebra to express following constraints: a. The sell price must be higher than the purchase price.

SellPrice PurchasePrice  Product    b. A product of Samsung must be television, mobile or tablet.

 SupplierName 'Samsung' and Type 'television' and Type 'mobile'and Type  'tablet' Product    c. No supplier of mobile’s or tablet’s may also supply food.

R1 :   (Product)



(Prod uct.Type ' mobile' and R1.Type 'food') or (Product.Type ' tablet' and R1.Type  'food')

 Product x R1   

d. No product may appear more than one time in an invoice. R1 :   (InvoiceLine) R2 : (R1)



(R1.ProductID  R2.ProductID and R1.InvoiceID R2.InvoiceID and R1.quantity  R2.quantity)

 R1 x R2   

e. The quantity of each product in each invoice should be greater than 0.

 (Quantity0) InvoiceLine    f. There is no invoice without product.

 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.

 (PurchasePrice 500000 and SellPrice 9000000)  Product    h. The sell price could not be greater than 2 times the purchase price.

 ((PurchasePrice *2 )  SellPrice )  Product    i. The gender of an employee should be “Nam” or “Nữ”.

 (Gender 'Nam' and Gender  'Nu' )  Employee   k. 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. ABS: tính trị tuyệt đối

R1 : (Product) R 2 :  (Product)



(R1.PurchasePrice  R2.PurchasePrice and ( ABS(R1.Sel lPrice- R2.SellPrice ) 0.5*R1.PurchasePrice )

 R1 x R2   ...