X FT 2094_5171 A U UN PA ULICU ULUNAUDIELLA Gel 1T3171%20Tutorial%204.pdf 4.2.3. Advanced Relational Algebra If your tutor has time at the end of the class, let's apply your knowledge from the world of relational algebra above to see if you can solve a theoretical problem. Considers these four relations: CUSTOMER (CUST_ID, CUST_NAME, CUST_ADDRESS) PRODUCT (PROD_NO. PROD_DESC, PROD_UNITPRICE, PROD_STOCK) STAFF(STAFF NAME, STAFF_POSITION) SALE (CUST_ID. SALE_DATE. PROD_NO. SALE_QTY, SOLD_BY) *Note that sold_by value is the staff who made the sale Page 4 of 6 also refer to the following tables as seen in an RDBMS CUSTOMER CUST ID CUST_NAME CUST_ADDRESS 12 Clive Clark Charles Cila India Rd Kent St Windsor Av Black St PRODUCT KUTOA For PRODUCT PROD NO k3 ks K11 PROD_UNITPRICE $17.95 PROD_STOCK 105 $6.95 PROD_DESC Knife Set Ladie Scraper Rack Table Stool 56 $0.95 $22.95 $399.50 $17.95 STAFF STAFF NAME Sandra STAFF_POSITION Manager Clerk Simon Steve Packer Sean Clerk Director SALE CUSTID PROD_NQ SALE_QTY SALE_DATE 20170311 20170121 20170123 SOLD_BY Simon Simon 1 1 k11 W I LCU UULUI A DICLA DEL 20Tutorial%204.pdf # 20170123 20170123 K11 Simon Page 5 of 6 Sorcha 20161130 1 20170228 201611293 2 1 Sean Sean Using Relational Algebra answer the following queries. You must represent your answer in symbolic notation and where a query has several solutions, your answer must represent the most efficient solution 1. List names of customers and descriptions of products bought by the customers. How many tuples will be returned by the relational algebra query that you have constructed as your answer? 2. List names of customers and product numbers/codes bought by the customer. Include customer(s) who has not bought any product in the list. How many tuples will be returned by the relational algebra query that you have constructed as your answer? 3 List names of products that haven't been sold 4 List names of chers who don't have any sales yet 5. List categories positions of staff who have made sales Important After you have completed your current lab activities, at the end of each session remember to add commit and push any changes you have made to the FIT Gitlab server. You need to get into the habit of establishing this as a standard FIT 2004-FIT3171 workflow Puhasta m on work on the activities are able to X FT 2094_5171 A U UN PA ULICU ULUNAUDIELLA Gel 1T3171%20Tutorial%204.pdf 4.2.3. Advanced Relational Algebra If your tutor has time at the end of the class, let's apply your knowledge from the world of relational algebra above to see if you can solve a theoretical problem. Considers these four relations: CUSTOMER (CUST_ID, CUST_NAME, CUST_ADDRESS) PRODUCT (PROD_NO. PROD_DESC, PROD_UNITPRICE, PROD_STOCK) STAFF(STAFF NAME, STAFF_POSITION) SALE (CUST_ID. SALE_DATE. PROD_NO. SALE_QTY, SOLD_BY) *Note that sold_by value is the staff who made the sale Page 4 of 6 also refer to the following tables as seen in an RDBMS CUSTOMER CUST ID CUST_NAME CUST_ADDRESS 12 Clive Clark Charles Cila India Rd Kent St Windsor Av Black St PRODUCT KUTOA For PRODUCT PROD NO k3 ks K11 PROD_UNITPRICE $17.95 PROD_STOCK 105 $6.95 PROD_DESC Knife Set Ladie Scraper Rack Table Stool 56 $0.95 $22.95 $399.50 $17.95 STAFF STAFF NAME Sandra STAFF_POSITION Manager Clerk Simon Steve Packer Sean Clerk Director SALE CUSTID PROD_NQ SALE_QTY SALE_DATE 20170311 20170121 20170123 SOLD_BY Simon Simon 1 1 k11 W I LCU UULUI A DICLA DEL 20Tutorial%204.pdf # 20170123 20170123 K11 Simon Page 5 of 6 Sorcha 20161130 1 20170228 201611293 2 1 Sean Sean Using Relational Algebra answer the following queries. You must represent your answer in symbolic notation and where a query has several solutions, your answer must represent the most efficient solution 1. List names of customers and descriptions of products bought by the customers. How many tuples will be returned by the relational algebra query that you have constructed as your answer? 2. List names of customers and product numbers/codes bought by the customer. Include customer(s) who has not bought any product in the list. How many tuples will be returned by the relational algebra query that you have constructed as your answer? 3 List names of products that haven't been sold 4 List names of chers who don't have any sales yet 5. List categories positions of staff who have made sales Important After you have completed your current lab activities, at the end of each session remember to add commit and push any changes you have made to the FIT Gitlab server. You need to get into the habit of establishing this as a standard FIT 2004-FIT3171 workflow Puhasta m on work on the activities are able to
