Data Base, Study notes of Database Management Systems (DBMS)

Download Data Base and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

Views

CREATE VIEW REDPARTS AS

SELECT *

FROM P

WHERE COLOR = ‘RED’;

• View are virtual tables

• (^) Not physically stored in database
• (^) Created on demand

• Created via view definition

• Looks just like a table

Query on a View

• (^) Views can be queried just like relations
  • (^) How is this done?
• (^) Query modification: DBMS changes a query on a view to a query on underlying table. SELECT * FROM REDPARTS WHERE WEIGHT > 15;

is converted to

SELECT * FROM P WHERE COLOR = ‘RED’ AND WEIGHT > 15;

Figures 4.3 - 4.4: Access Query Design of View

5

Eg:INTO: alternative to Nested From:

warehouse with largest #parts

• (^) Save “WPCount” in a query/temporary

table:

SELECT Warehouse AS W, COUNT (PartNum) AS NumberofParts INTO WPcount FROM Part GROUP BY Warehouse;

• (^) Now get warehouse with largest count: SELECT W FROM WPcount WHERE WPcount.NumberofParts = (SELECT MAX (NumberofParts) FROM WPcount);

Count Distinct Eg

• (^) How many customers have placed an order SELECT COUNT(DISTINCT CustomerNum ) FROM Orders;
• (^) Access does not support DISTINCT with aggregate ops. Eg: above will not work. How to do?
• (^) First do DISTINCT and store in temp table (query)
• (^) Then run aggregate op query on temp table SELECT DISTINCT CustomerNum FROM Orders;
• (^) Save query as query55, then do aggregate operation: SELECT COUNT (CustomerNum) FROM query55;
• (^) This will work in Access

8

Views are dynamic

• (^) Any change in underlying base table gets reflected in the view. CREATE VIEW REDPARTS AS SELECT * FROM P WHERE COLOR = ‘RED’;
• (^) Suppose initially 3 red parts in P SELECT * FROM REDPARTS How many rows?
• (^) 3 rows. Now insert new red part into P. Then do SELECT * FROM REDPARTS How many rows?
• (^) 4 rows. Why?
• (^) Query modification. This query becomes: SELECT * FROM P WHERE COLOR = ‘RED’ ;

Views : subset of columns

• (^) Column names can be given explicitly or be inherited from base table CREATE VIEW SOMERED (P#, PARTNAME, WT) AS SELECT P#, PNAME, WEIGHT FROM P WHERE COLOR = ‘RED’;

More on views

• (^) Column names must be explicitly stated if derived
• (^) Derived columns CREATE VIEW PARTQUANT (PNO, TOTALQT) AS SELECT P#, SUM (QTY) FROM SP GROUP BY P# ;
• (^) Can be used to create other views CREATE VIEW HEAVYREDPARTS(P#, PARTNAME) AS SELECT P#, PARTNAME FROM SOMERED WHERE WT > 13;