IT_Y4_new

# 5 marks 3 define relvar sp of the suppliers and parts

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

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)

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

View Full Document
(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)
Advanced Data Management Techniques IT 4016 for Second Semester Sample Answer & Question

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

View Full Document
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
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• 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.

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

• 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.

Dana University of Pennsylvania ‘17, Course Hero Intern

• 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.

Jill Tulane University ‘16, Course Hero Intern