Description
Submission Instructions
– Name the file to submit as lastnameFirstnameL1.txt (e.g. chaturvediRituL1.txt).
Schema for this lab:
Assume that a tools company called GMT stores data on different parts they manufacture and on suppliers who supply these parts. The database is called SHIPMENT and consists of 3 tables (note that table names are case-insensitive for this lab).
supplier (sno, sname, status, citys) part (pno, pname, colour, weight, cityp) shipment (sno, pno, qty)
Integrity Constraints
• Primary Key of relation o SUPPLIER is sno o PARTS is pno o SHIPMENT is (sno, pno)
• Foreign key o sno in SHIPMENT references (sno) in SUPPLIER o pNo in SHIPMENT references (pno) in PARTS
————————————————————————————————
Relational Algebra queries are written using pen and paper. But there is an online relational calculator available for students to test their queries. You must follow the given steps to temporarily load a dataset that we will use for lab1 and worksheet1. This work by Johannes Kessler is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
Relational algebra calculator link for SHIPMENT Database:
https://dbis-uibk.github.io/relax/calc/gist/a7cb531d6dc1a3db7a99825bd893c642
Ritu Chaturvedi
Tip: When you complete a query successfully on the calculator, download it or copy it to a text editor before you start the next query.
Write query in relational algebra to do the following. For each question, you must (a) write the query in relational algebra (b) show the resulting relation.
1. Get supplier names for suppliers who supply part P2.
2. Get supplier names for suppliers who do not supply part P2.
3. Get supplier names for suppliers who supply at least one blue part.
4. Get supplier names for suppliers who supply all blue parts.
5. Find names of all suppliers who supply both parts P1 and P2.
6. Find the max quantity supplied.
7. Find names of all suppliers who supply 2 parts.
————
Ritu Chaturvedi
