/*
ISC 561
ERD, 
Transforming data models into database design,
Database implementation (SQL-DDL/DML)

This sql script contains DDL and Transact-SQL to implement 
views

Definition of a view pp. 239-247

The names and datatype of attributes in the 
veiw do not have to be the same as the base table.

For example, in the CCER database, we created views to 
transition from a previous database design to the 
"new and improved" version. We maintained the original 
"view" as seen by legacy applications with
views until we migrated all of our software 
from VB 6.0 to ASP.Net.

*/
USE AMCIS

--Suppose we want to create a view for tblRoom

SELECT RoomID, RoomName, MaxOccupancy, ConferenceYear 
FROM tblRoom

--DROP VIEW vwRoom
--Create view
CREATE VIEW vwRoom
AS
SELECT RoomID, RoomName, MaxOccupancy, ConferenceYear 
FROM tblRoom

SELECT * FROM tblRoom
SELECT * FROM vwRoom

--ALTER view, change attribute names and datatype
ALTER VIEW vwRoom
AS
SELECT RoomID, RoomName AS Name, CAST(MaxOccupancy AS NVARCHAR) AS MaxOccupancy, ConferenceYear
FROM tblRoom

/*
Materialized view (Designing Read-Only Database) pp. 106 - 109
Refresh policy
CCER National IS exit exam (tblTakerProfiles...)
Reduces the complexity of SQL for reporting
  For example, consider the SQL to produce the sample program listing for
  the AMCIS conference.

  As in the homework one technique is:
   SELECT attribute list
   INTO newTableName
   FROM baseTableName(s)...

  OR if newTableName already exists
   INSERT INTO newTableName (attribute list)
     SELECT Attribute list
     FROM baseTableName(s)

*/
SELECT * FROM tbl_vwPresentationSchedule

--DROP TABLE tbl_vwPresentationSchedule

SELECT tblRoom.RoomName AS Room, SessionCode, 
       tblTrack.Title AS Track, FirstName + ' ' + LastName AS TrackChair, 
       AcceptanceRate, LcdProjectorRequired, PresentationOrder, 
       vwAuthorSubmission.Title, vwAuthorSubmission.Author
INTO tbl_vwPresentationSchedule --INTO statement
FROM tblSession, tblPresentation, tblTrackChair, tblParticipant, 
     tblTimeSlot, tblRoom, tblTrack,
     (SELECT tblPaper.PaperID, Title, FirstName + ' ' + LastName AS Author
      FROM tblPaper, tblAuthorPaper, tblAuthor, tblParticipant
      WHERE tblPaper.PaperID = tblAuthorPaper.PaperID AND
         tblAuthor.ParticipantID = tblAuthorPaper.AuthorID AND
         tblParticipant.ParticipantID = tblAuthor.ParticipantID) AS vwAuthorSubmission
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblTrackChair.ParticipantID = tblSession.TrackChairID AND
      tblParticipant.ParticipantID = tblTrackChair.ParticipantID AND 
      tblTimeSlot.TimeSlotID = tblSession.TimeSlotID AND
      tblRoom.RoomID = tblSession.RoomID AND
      tblPresentation.PaperID = vwAuthorSubmission.PaperID
ORDER BY SessionCode, PresentationOrder

/*
How is this different from this?
*/
CREATE VIEW vwPresentationSchedule
AS
SELECT tblRoom.RoomName AS Room, SessionCode, 
       tblTrack.Title AS Track, FirstName + ' ' + LastName AS TrackChair, 
       AcceptanceRate, LcdProjectorRequired, PresentationOrder, 
       vwAuthorSubmission.Title, vwAuthorSubmission.Author
FROM tblSession, tblPresentation, tblTrackChair, tblParticipant, 
     tblTimeSlot, tblRoom, tblTrack,
     (SELECT tblPaper.PaperID, Title, FirstName + ' ' + LastName AS Author
      FROM tblPaper, tblAuthorPaper, tblAuthor, tblParticipant
      WHERE tblPaper.PaperID = tblAuthorPaper.PaperID AND
         tblAuthor.ParticipantID = tblAuthorPaper.AuthorID AND
         tblParticipant.ParticipantID = tblAuthor.ParticipantID) AS vwAuthorSubmission
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblTrackChair.ParticipantID = tblSession.TrackChairID AND
      tblParticipant.ParticipantID = tblTrackChair.ParticipantID AND 
      tblTimeSlot.TimeSlotID = tblSession.TimeSlotID AND
      tblRoom.RoomID = tblSession.RoomID AND
      tblPresentation.PaperID = vwAuthorSubmission.PaperID

SELECT * FROM vwPresentationSchedule
SELECT * FROM tbl_vwPresentationSchedule

--DROP TABLE vwPresentationSchedule

/*
Any difference in performance?

The results for tbl_vwPresentationSchedule are stored in a 
materialized view and may need to be updated(refreshed)
as changes occur to the data.

In the CCER exit exam, we refresh the views at the end 
of each testing cycle.

mvwPresentationSchedule

*/

USE AMCIS


/*
Not only is the SQL simplier because of the view, but the query will run MUCH faster.

Note that the materialized view includes an ORDER BY clause. 
A view definition cannot contain an ORDER BY clause. The results of a view are ordered 
but not the definition.

*/

SELECT * FROM tbl_vwPresentationSchedule

SELECT * FROM vwPresentationSchedule
ORDER BY SessionCode, PresentationOrder

--SET SHOWPLAN_ALL ON
SELECT * FROM tbl_vwPresentationSchedule

SELECT * FROM vwPresentationSchedule
ORDER BY SessionCode, PresentationOrder
--SET SHOWPLAN_ALL OFF

/*
Customized duplicated tables pp. 108

*/


/*
Uses for Views pp. 241
 1. Hide columns or rows
 2. Display results of computations
 3. Hide complicated SQL syntax 
 4. Layer built-in functions (or reuse complicated SQL syntax)
 5. Provide level of isolation between table data and users' view of data
 6. Assign different processing permissions to different views of the same table
 7. Assign different triggers to different views of the same table


*/

/* 

1. Hide columns or rows pp. 241

Simplified views of tblParticipant
   By column (show Name "LastName, FirstName" and email only)
   By Row (show only participants with an InstitutionID)
   By Subtype (Show only authors: attributes same as "By Column")

Not only does this create "simplier" structures, it adds an additional
layer of security "need-to-now" basis.

*/
SELECT * FROM tblParticipant
--DROP VIEW vwParticipant
CREATE VIEW vwParticipant
AS
  SELECT LastName + ', ' + FirstName AS UserName, Email
  FROM tblParticipant

--This one does not work
INSERT INTO vwParticipant (UserName, Email) 
VALUES ('harold pardue', 'hpardue...')

--This one does...
UPDATE vwParticipant SET Email = NULL
WHERE UserName = 'Patankar, Ajit'

SELECT * FROM vwParticipant

SELECT * FROM tblParticipant

CREATE VIEW vwParticipantUSA
AS
SELECT * 
FROM tblParticipant WHERE InstitutionID = 1

SELECT * FROM vwParticipantUSA

CREATE VIEW vwAuthor
AS
SELECT FirstName, LastName, Email, InstitutionID, Attending
FROM tblParticipant, tblAuthor
WHERE tblParticipant.ParticipantID=tblAuthor.ParticipantID

SELECT * FROM vwAuthor

/*
 2. Display results of computations

The string concatenation in number 1 is an example.

In the NorthWind database, we could create a view for the 
Products base table that displays the 
inventory statistic: UnitPrice*UnitsInStock
*/
USE NORTHWIND

SELECT * FROM Products

DROP VIEW vwInventoryStats
CREATE VIEW vwInventoryStats
AS
SELECT ProductID, ProductName, UnitPrice*UnitsInStock AS InventoryStat 
FROM Products

SELECT * FROM vwInventoryStats

/*
 3. Hide complicated SQL syntax 

The AMCIS materialized view is a good example. That query 
didn't have
to be materialized. We could store it as a derived 
table or view.

We could create a view that displays the number of presenations
for a each session using a correlated subquery in the SELECT clause.

*/

USE AMCIS

CREATE VIEW vwPresentationCount
AS
SELECT SessionID, SessionCode, LcdProjectorRequired, RoomID, 
   (SELECT COUNT(*) AS PresentationCount
    FROM tblPresentation
    WHERE tblPresentation.SessionID=tblSession.SessionID) AS PresentationCount
FROM tblSession

SELECT * FROM vwPresentationCount

SELECT * FROM tblPresentation

/*
 4. Layer built-in functions

In the section on subqueries in the FROM clause we did...
*/
USE Northwind

SELECT Categories.CategoryId, CategoryName, vwProductCnt.NumberOfProducts
FROM Categories, (SELECT Products.CategoryID, COUNT(*) As NumberOfProducts
                  FROM Products
                  GROUP BY Products.CategoryID) AS vwProductCnt
WHERE Categories.CategoryID=vwProductCnt.CategoryID

CREATE VIEW vwProductCnt
AS
SELECT Products.CategoryID, COUNT(*) As NumberOfProducts
                  FROM Products
                  GROUP BY Products.CategoryID

SELECT Categories.CategoryId, CategoryName, vwProductCnt.NumberOfProducts
FROM Categories, vwProductCnt
WHERE Categories.CategoryID=vwProductCnt.CategoryID


/*
We could have done it for others.
If I see that I am using a subquery more than once, I store
it as a view.

-------------
Several Northwind customers have suggested that shipdate is correlated
with the number of orders placed. That is, they suggest that
customers who place more orders tend to get their orders shipped sooner.

List Customers.CompanyName, NumberOfOrders, AvgDaysToShip for 
all customers in the Nortwhind database
*/

SELECT Customers.CompanyName, NumberOfOrders, AvgDAysToShip
FROM Customers, 
  (SELECT CustomerID, COUNT(*) AS NumberOfOrders
   FROM Orders
   GROUP BY CustomerID) AS vwOrderCnt,
  (SELECT CustomerID, AVG(DATEDIFF(day, OrderDate, ShippedDate)) AS AvgDAysToShip
   FROM Orders
   GROUP BY CustomerID) AS vwDAysToShip
WHERE Customers.CustomerID = vwOrderCnt.CustomerID AND
      Customers.CustomerID= vwDaysToShip.CustomerID


/*
List the CategoryName of all categories for which there are a 
total of at least 400 UnitsInStock.

*/
SELECT CategoryName
FROM Categories
WHERE CategoryID IN(SELECT CategoryID
                    FROM Products
                    GROUP BY CategoryID
                    HAVING SUM(UnitsInStock) > 400)

SELECT CategoryName
FROM Categories
WHERE EXISTS(SELECT CategoryID
             FROM Products
             WHERE Products.CategoryID=Categories.CategoryID
             GROUP BY CategoryID
             HAVING SUM(UnitsInStock) > 400)

--Save the subquery as a view and rewrite
CREATE VIEW vwUnits400
AS
SELECT CategoryID
FROM Products
GROUP BY CategoryID
HAVING SUM(UnitsInStock) > 400

SELECT CategoryName
FROM Categories
WHERE CategoryID IN(SELECT CategoryID
                    FROM vwUnits400)

SELECT CategoryName
FROM Categories
WHERE EXISTS(SELECT CategoryID
             FROM vwUnits400
             WHERE vwUnits400.CategoryID=Categories.CategoryID)


/*
 pp. 245-246
 5. Provide level of isolation between table data and users' 
 view of data

*/


/*
 6. Assign different processing permissions to different views of the same table

Create two database logins and users for AMCIS
*/
--sp_addlogin 'login', 'password', 'database'
--sp_droplogin 'login'
--sp_revokedbaccess  'login'
--sp_grantdbaccess 'login'

USE AMCIS
sp_addlogin 'user1', 'user1pass', 'AMCIS'
--sp_droplogin 'user1'
--sp_revokedbaccess  'user1'
sp_grantdbaccess 'user1'

sp_addlogin 'user2', 'user2pass', 'AMCIS'
--sp_droplogin 'user2'
--sp_revokedbaccess  'user2'
sp_grantdbaccess 'user2'

/*
User1 and User2 can connect to the AMCIS database, but they do have not
been granted privilege.

Let's create two seperate views of tblParticipant.
User1 can only see the simplified view.
User2 can see all user attributes and institution information
*/

--View for user1
CREATE VIEW vwParticipantUser1
AS
SELECT LastName + ', ' + FirstName AS UserName, Email
FROM tblParticipant

GRANT SELECT ON vwParticipantUser1 TO user1
GRANT UPDATE ON vwParticipantUser1 TO user1


--View for user2

CREATE VIEW vwParticipantUser2
AS
SELECT LastName + ', ' + FirstName AS UserName, Email, ShortName AS Institution, ShortName
FROM tblParticipant, tblInstitution
WHERE tblInstitution.InstitutionID=tblParticipant.InstitutionID

GRANT SELECT ON vwParticipantUser2 TO user2
GRANT DELETE ON vwParticipantUser2 TO user2
GRANT UPDATE ON vwParticipantUser2 TO user2

REVOKE DELETE ON vwParticipantUser2 TO user2
REVOKE SELECT ON vwParticipantUser2 TO user2



/*
 7. Assign different triggers to different views of the same table

*/

/*
Are views updatable? pp. 247
It depends (Figure 7-24) general guidelines
   on the nature of the view
   on the DBMS

Can we update the view that derives "UserName" as 
a concatenation?

Can we update a view based on a join? Sometimes...

*/

/*
Referential integrity constraints hold.

*/


/*
Consider the following situation.
There is a view called vwParticipant25
This view is designed to restrict access to only
participants with InstitutionID = 25 (University of South Alabama)

*/
SELECT * FROM tblInstitution
SELECT * FROM tblParticipant
SELECT * FROM vwParticipantUSA

--Should this INSERT be allowed?
INSERT INTO vwParticipantUSA (FirstName, LastName, Email, InstitutionID)
   VALUES('Brue', 'Whte', 'bte@usouthal.edu', 3)

--Or this update?
UPDATE vwParticipantUSA SET InstitutionID = 4 WHERE ParticipantID = 8

UPDATE vwParticipantUSA SET InstitutionID = 4 WHERE ParticipantID = 11

UPDATE tblParticipant SET InstitutionID = 1 WHERE ParticipantID = 8

ALTER VIEW vwParticipantUSA
AS
SELECT * 
FROM tblParticipant WHERE InstitutionID = 25
WITH CHECK OPTION

--WITH CHECK OPTION

/*
Views can be based on other views.
There is a limit on the number of nested views in SQL Server 2000.

We could create a view based on user2's view.

*/

CREATE VIEW vwParticipant2
AS
SELECT UserName, Email, Institution 
FROM vwParticipantUser2

SELECT * FROM vwParticipant2

/*
A view can’t be defined in terms of itself (mutually dependent) 
either immediately or transitively

CREATE VIEW Temp1 (Attribute list) AS 
   SELECT (Attribute list) from Temp1

or

CREATE VIEW Temp1 (Attribute list) AS 
   SELECT (Attribute list) from Temp2

CREATE VIEW Temp2 (Attribute list) AS 
   SELECT (Attribute list) from Temp1
*/