5 marks 3 define relvar sp of the suppliers and parts

Info icon This preview shows pages 23–27. Sign up to view the full content.

View Full Document Right Arrow Icon
Define a view for suppliers in London. (5-Marks) (3) Define relvar SP of the suppliers and parts database as a view of relvar SPJ of the suppliers-parts-projects database. (5-Marks) (4) Define a view over the suppliers-parts-projects database consisting of all projects (project number and city attributes only) that are supplied by supplier S 1 and use part P 1 . (5-Marks) 13. Give SQL solutions to the following operations. (20-Marks) (1) Define a view consisting of supplier numbers and part numbers for suppliers and parts that are not colocated. (5-Marks) (2) Define a view for suppliers in London. (5-Marks)
Image of page 23

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
(3) Define relvar SP of the suppliers and parts database as a view of relvar SPJ of the suppliers parts projects database. (2-Marks) (4) Define a view over the suppliers-parts-projects database consisting of all projects (project number and city attributes only) that are supplied by supplier S 1 and use part P 1 . (5-Marks) 14. Define a view to the following operation by using SQL facilities. (20-Marks) (1) Get supplier number, status and city for suppliers that status is greater than 15. (5-Marks) (2) Get full details for all parts that color is red. (5-Marks) (3) Get part numbers and the corresponding total quantity. (5-Marks) (4) Get all pairs of city names such that a supplier in the first city suppliers a project in the second city. (5-Marks) (5) Get part number, name, weight and city that weight is more than 12. (5-Marks) 15. Give calculus-based analogs of the algebraic view definitions for following view define in terms. (20-Marks) (1) VAR REDPART VIEW ((P WHERE COLOR = COLOR (‘RED’)) {ALL BUT COLOR}) RENAME WEIGHT AS WT; (2) VAR PQ VIEW SUMMARIZE SP PER P { P#} ADD SUM ( QTY ) AS TOTQTY; (3) VAR CITY-PAIR VIEW ( ( S RENAME CITY AS SCITY ) JOIN SP JOIN ( P RENAME CITY AS PCITY) ) { SCITY , PCITY} ; (4) VAR HEAVY_REDPART VIEW REDPART WHERE WT > WEIGHT (12.0); 16. Consider the following query with a name relational expression. (a) Define a view consisting of supplier numbers and part numbers for suppliers and parts that are not collocated. (b) Define a view for suppliers in London. (c) Define relvar SP of the suppliers and parts database as a view of relvar SPJ of the suppliers-parts-projects database. (d) Define a view over the suppliers-parts-projects database consisting of all projects (project number and city attributes only) that are supplied by supplier S1 and use part P1. (20-Marks)
Image of page 24
Advanced Data Management Techniques IT 4016 for Second Semester Sample Answer & Question
Image of page 25

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
1.Using the supplier-part. Project tables above, write the SQL facilities for the following operations. (i) Get color and city for “nonParis” parts with weight greater than ten pounds. (5-Marks) (ii) Get the maximum and minimum quantity for part P 2 . (5-Marks) (iii) For each part being supplied to a project, get the part number, the project number, and the corresponding total quantity. (5-Marks) (iv) Get the total quantity of part P 1 supplied by supplier S 1 . (5-Marks) Sol (i) SELECT P.COLOR,P.CITY FROM P WHERE P.CITY< > ‘PARIS’ AND P. WEIGHT > 10.0; ( ii ) SELECT MAX (SPJ.QTY)ASQ,MIN(SPJ.QTY) ASY FROM SPJ WHERE SPJ.PH = ‘P2’; (iii) SELECT SPJ.P#,SPJ.J#,SUM (SPJ.QTY) AS Q FROM SM GROUP BY SPJ.P# ,SPJ .j#; (iv) SELECT SUM ( SPJ.QTY) AS Q
Image of page 26
Image of page 27
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern