5. The following relational schema represents_Product-Supply schema: PRODUCT (P#

Question

5. The following relational schema represents_Product-Supply schema: PRODUCT (P#. P NAME, PRICE, QTY) SUPPLY(S, P. S PRICE) In this schema. P#-Product Number: QTY-Quantity on hand, S#-Suppler Number. PRICE in PRODUCT is the price of selling, while S_PRICE is the supplier-price supplied by the supplier Suppose we have the following data: PRODUCT SUPPLY SUPPLIER P# P-NAME PRICE QTY 53 TABLE 78 WALL UNIT 850 91 CHAIR 66 DRESSER 70038 S# P# S PRICE SA CITY 12 53 450 12 9 150 23 53 400 4536 450 45 66 475 55042 23 NY 45 Philly 12 DO 53 50 250 (a) Suppose we want to enforce referential integrity constraints for the database. The above relations contain an important error in data. What is it? Be specific. (4) (b) Based on the data above, what is the output from the query below? (4) SELECT FROM WHERE P NAME, PRICE, S_PRICE PRODUCT P, SUPPLY S, SUPPLIERR PPA-SPa AND S.S# = R.S# AND CITY = DC'; (c) How many comparisons does the above query in (b) need in computing the joins? Calculate as we discussed in the class. Explain your calculation. (3) (d) Can we insert a tuple (16, 91, 300) to SUPPLY relation? Why or why not?3)

Explanation / Answer

Hi Student,

Please find the answers below :

a. We can add a referential integrity constraint between

The error in the data is for the entry with row

45 36 450 in supply table. 36 is a product number which do not exist in the product table.

b.   p_name price s_price

table 550 450
chair 250 150

c. It takes (1*5)=5 * 4 = 20 comparisions.

d. No we cannot insert the tuple (16,91,300). This is because 16 doesnot exist in s# in supplier table.

All the reference keys must be present in the base table.

If you like this answer, give a thumbs up! Happy Learning :)

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.