[Solved] Midterm Exam

For this exam, consider the following schema and instances of the relations. Feel free to remove this page from the exam.Our database is very simple. It is composed of three relations: Parts, Suppliers and Catalog.The Catalog table contains the parts that are being offered by a given supplier at a given price (a part is missing a price if this field is NULL). Every pid in Catalog exists in Parts, and every sid in Catalog exists in Suppliers.Parts(pid: integer, pname character(40), color character(20));Primary key: pid.pid pname color6 Anti-Gravity Turbine Generator Cyan7 Anti-Gravity Turbine Generator Magenta8 Fire Hydrant Cap Red9 7 Segment Display Green10 SQL queries GreenSuppliers(sid: character(10), sname: character(40), address: char(50));Primary key: sid.sid sname addressamazon Amazon Canada 1 Grub St., Potemkin Village, IL 61801walmart Walmart Inc 4 My Way, Bermuda Shorts, OR 90305rim Research in Motion 99999 Short Pier, Terra Del Fuego, TX 41299google Google Inc. 2 Groom Lake, Rachel, NV 51902Catalog(sid: character(10), pid: integer, price: real);Primary key: (sid,pid).sid pid costamazon 8 11.7walmart 8 7.95rim 8 12.5rim 9 1amazon 10 10.5amazon 9CSC 370 PAGE 21. Writing queries in Relational Algebra and SQLGive both relational algebra and SQL queries to answer the following questions. Your relational algebra should match your SQL queries.1.1) [4] For every supplier, lists its sname and the pid of each of the parts they offer. Result should contain two attributes.1.2) [4] List the pname of parts that are being offered at $10 or more. Result should contain only one attribute.1.3) [4] For every pid in relation Parts, list the number of suppliers that offer it, and the minimal price at which it is offered. Result should contain three attributes.1.4) [4] How many parts in table Parts are not being offered by any supplier? Result should contain only one attribute.CSC 370 PAGE 31.5) [4] List the pid and sid of parts that offered by such supplier and are missing a price. Result should contain two attributes.1.6) [4] For every supplier, list its sid and the average price of the parts they offer. Result should contain two attributes.1.7) [4] List the pid and the pname of parts that are offered by exactly 3 suppliers. Result should contain two attributes.1.8) [4] List the pid of the parts that are being offered by both suppliers: Amazon and Walmart (these are their sid). Result should contain one attribute.CSC 370 PAGE 41.9) [4] Compute the difference between the average price of parts with pid l2 and 32. In other words, compute (the average price of partid l2) minus (the average price of pid 32). The result should contain one tuple with one attribute.1.10) [4] For every pid in the relation Catalog, list the sname of the supplier who offers it a the lowestprice, and such price. Result should contain three attributes.1.11) [4] List the pid of parts that are being offered by at least two suppliers at exactly the same price. Your result should contain two columns: the pid of the two parts, and their price.CSC 370 PAGE 52. Relational Model2.1) [4] Given the relation R(A;B;C;D) and the set of functional dependencies A ! BC, BC ! A, and B ! D. Find all the candidate keys of this relation. Show all your work.For this you have to compute the closure of each combination of attributes ABCD, ABC, ABD, … A, B, C, D (15 in total). The candidate keys are only A and BC. End of examination