{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Projects project number and city attributes only that

Info iconThis preview shows pages 38–42. Sign up to view the full content.

View Full Document Right Arrow Icon
projects (project number and city attributes only) that are supplied by supplier S 1 and use part P 1 . (5-Marks) Sol (1) CREATE VIEW NON-COLOCATED AS SELECT S.S#, P.P# FROM S, P WHERE S.CITY <> P.CITY; (2) CREATE VIEW LONDON-SUPPLIER AS SELECT S.S#, S.SNAME, S.STATUS FROM S.CITY = ‘London’; (3) CREATE VIEW SP AS SELECT SPJ.S#, SPJ.P#, SUM (SPJ.QTY) AS QTY FROM SPJ GROUP BY SPT.S#, SPJ.J#; (4) CREATE VIEW JC AS SELECT J.J#, J.CITY FROM J WHERE J.J# IN ( SELECT SPJ.J# FROM` SPJ WHERE SPJ.S# = ‘S1’ ) AND J.J# IN ( SELECT SPJ.J# FROM SPJ 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) Sol (1) CREATE VIEW GOOD-SUPPLIER AS SELECT S.S#, S.STATUS, S.CITY FROM S WHERE S.STATUS > 15 WITH CHECK OPTION; (2) CREATE VIEW REDPART
Background image of page 38

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

View Full Document Right Arrow Icon
AS SELECT P.P#, P.NAME, P.WEIGHT AS WT, P.CITY FROM P WHERE P.COLOR = ‘Red’ WITH CHECK OPTION; (3) CREATE VIEW CITY PAIR AS SELECT DISTINCT S.CITY AS SCITY, P.CITY AS PCITY FROM WHERE AND (4) CREATE VIEW HEAVY REDPART AS SELECT RP.P#, RP.PNAME, RP.WT, RP.CITY FROM REDPART AS RP WHERE RP.WT > 12.0 WITH CHECK OPTION; 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); SOL : (1) VAR REDPART VIEW ( PX . P#, PX . PNAME, PX.WEIGHT AS WT, PX.CITY ) WHERE PX . COLOR = (‘Red’) ; (2) VAR PQ VIEW ( PX . P#, SUM ( SPX WHERE SPX . P# = PX . P#, QTY ) AS TOTQTY); (3) VAR CITY PAIR VIEW ( SX . CITY AS SCITY, PX.CITY AS PCITY ) WHERE EXITS SPX ( SPX . S# = SX . S# AND SPX . P# = PX . P# );
Background image of page 39
(4) VAR HEAVY_REDPART VIEW RPX WHERE RPX.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) Sol (a) VAR NON_COLOCATED VIEW ( S TIMES P ) { S#, P# } MINUS ( S JOIN P ) {S#, P# }; (b) VAR LONDON_SUPPLIER VIEW ( S WHERE CITY = ‘LONDON’ ) {ALL BUT CITY}; (c) VAR SP VIEW SUMMARIZE SPJ PER SPJ { S# , P# } ADD SUM (QTY) AS QTY ; (d) VAR JC VIEW ( (SPJ WHERE S# = S#(‘S1’) ) {J#} JOIN (SPJ WHERE P# = P#(‘P1’) ) {J#} )JOIN J {J#,CITY} ; ---------------------------------------------------END--------------------------------------------------
Background image of page 40

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

View Full Document Right Arrow Icon
Analog and Digital Control IT 4017 for Second Semester
Background image of page 41
Image of page 42
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}