This uses microsoft SQL management Studio

I. OBJECTIVES At the end of this activity, the students will be able to: 1. Create, Alter, and Drop SQL VIEWs. 2. Understand the use of WITH SCHEMABINDING. 3. Create clustered and non-clustered index on Views to increase query performance. 4. Understand the use of WITH CHECK OPTION on Updatable Views. II. DISCUSSION A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions: - Restrict a user to specific rows in a table. for example: Allow an employee to see only the rows recording his or her work in a labortracking table. - Restrict a user to specific columns. for example: Allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information. - Join columns from multiple tables so that they look like a single table. - Aggregate information instead of supplying details. for example: Present the sum of a column, or the maximum or minimum value from a column. Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, titleview is a view that selects data from three base tables to present a virtual table of commonly needed data: CREATE VIEW titleview AS SELECT title, au_ord, au_lname, price, ytd_sales, pub_id FROM authors AS a JOIN titleauthor AS ta ON (a.au_id = ta.au_id) JOIN titles AS t ON (t.title id = ta.title_id) You can then reference titleview in statements in the same way you would reference a table: SELECT * FROM titleview A view can reference another view. For example, titleview presents information that is useful for managers, but a company typically discloses year-to-date figures only in quarterly or annual financial statements. A view can be built that selects all the titleview columns except au_ord and ytd_sales. This new view can be used by customers to get lists of available books without seeing the financial information: CREATE VIEW Cust_titleview AS SELECT title, au_lname, price, pub_id FROM titleview Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example: UPDATE titleview SET price = price 1.10 WHERE pub_id =0736 SQL Server supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Indexed views are a SQL Server 2000 feature that greatly improves the performance of complex views of the type usually found in data warehouses or other decision support systems. Views are called virtual tables because the result set of a view is us not usually saved in the database. The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time. Complex queries, such as those in decision support systems, can reference large numbers of rows in base tables, and aggregate large amounts of information into relatively concise aggregates such as sums or averages. When the CREATE INDEX statement is executed, the result set of the view SELECT is stored permanently in the database. Future SQL statements that reference the view will have substantially better response times. Modifications to the base data are automatically reflected in the view. The SQL Server 2000 CREATE VIEW statement supports a SCHEMABINDING option that prevents the tables referenced by the view being changed without adjusting the view. You must specify SCHEMABINDING for any view on which you create an index. PROCEDURES 1. Create a new database Activity01. 2. Open a New Query window and execute the following SQL code to create the three (3) table required for this activity. -. To create Customers table CREATE TABLE Customers (CustID INT IDENTITY (1002,2) PRIMARY KEY CLUSTERED, FName VARCHAR(50) NOT NULL, LNme VARCHAR(50) NOT NULL, UserID VARCHAR(100) NOT NULL, PinCode int NOT NULL DEFAULT 1234 ) - To create Products table CREATE TABLE Products (ProductID INT PRIMARY KEY CLUSTERED, ProductDesc VARCHAR(50) NOT NULL, ManufacturingDate DATETIME, ExpiryDate DATETIME, IsSalable BIT,-1 for Salable/Active and for Non-Salable Product Price MONEY NOT NULL ) - To create Bookings table - with reference to both Products and Customers table CREATE TABLE Bookings ( BookingID INT IDENTITY (10,2) PRIMARY KEY CLUSTERED, ProductID INT REFERENCES dbo.Products(ProductID), CustID INT REFERENCES dbo. Customers(CustID), DateofBooking DATETIME NOT NULL, QTY INT ) 3. Add a new database diagram and select the three(3) newly created tables. You will see that your tables have an existing foreign key constraints (primarykey - foreign key relationship) 4. Next, insert records into these tables by executing the following code below: INSERT INTO PRODUCTS VALUES (1, 'Biscuits', '2011-09-01 00:00:00.000', '2012-09-01 00:00:00.000', 1, 20), (2, 'Butter', '2010-09-01 00:00:00.000', '2011-09-01 00:00:00.000', , , 30), (3, 'Milk' , '2011-10-01 00:00:00.000', '2011-11-01 00:00:00.000 ', 1,46 ) INSERT INTO Customers (FName, LNme, UserID, PinCode) VALUES ('Michael', 'Jordan' , 'jordan@abc.com ', 1111), ('Magic', 'Jonson ', 'magic@xyz.com' , 2222), ('Micky' , 'Mouse ', 'mickey@mno.com', 3333) INSERT INTO Bookings (ProductID, CustID, DateOfBooking, QTY) VALUES (1,1002, '2011-11-01 00:00:00.000", 3), (2,1004,GETDATE(),4), (3,1006, ' 2011-10-01 00:00:00.000', 2) 5. Verify if the three (3) tables have successfully inserted all the sample records. 6. To create a view of all the Booking transactions showing all important field from the referenced tables' Products and Customers, type the following code and execute: CREATE VIEW BookingsView AS SELECT C. FName ,C. LNme ,P. ProductDesc ,B. Date0fbooking ,P. Price , B.QTY , (B.QTY*P.Price) AS TotalAmountPayable FROM BOOKINGS B INNER JOIN PRODUCTS P ON B. ProductID=P. ProductID INNER JOIN Customers C ON B. CustID=C. CustID; 7. Try using SELECT statement against the newly created view and see what happen. This is the most credible use of a VIEW. It reduces apparent complexity and prevent redundant hosting of data in the database. Another reason is that if you want to protect some fields and don't want to expose your table structure to some API that needs access to your tables. 1/24/2023| 8. To create a VIEW of Customers' table with only few fields expose, type the following code and execute: CREATE VIEW dbo.CustomersView AS Select CustID , FName AS [First Name] , LNme AS [Last Name] , UserID FROM dbo. Customers - - Retrieve all records from Customers' View SELECT * FROM CustomersView You have just created a View which can be used by the API to fetch Customers details - (Minus) the PinCode field. Views can be used to prevent sensitive information from being selected, while still allowing other important data. Views do not have a physical existence, but still they do return a set of record set as a table does. In short, View is simply an additional layer which calls the underlying code which finally returns the record set. 9. To prevent your View from being orphaned when the underlying table (source table of your view) changes its definition (alter/drop), a view schema binding is needed. To schema bind your existing Customersviews, execute the following code: 10. Now you can create a clustered index on this BookingsView view. To create an index, type the following code and execute: CREATE UNIQUE CLUSTERED INDEX Bookings_View_Indx ON dbo. BookingsView(Fname, LNme); To create an additional non clustered index named Bookings_View_Indx2, type the following code and execute: CREATE NONCLUSTERED INDEX Bookings_View_Indx 2 ON dbo. BookingsView (ProductDesc); DML (Insert / Update / Delete) can also be used on Views but needs to adhere with the following rules: i. If you are using a view to insert data, then your view should have a single select and the mandatory columns of the base table must be included in the view unless the base table has a default values for all NOT NULL columns. ii. for Views with "WITH CHECK OPTION" enabled, insert and update operation that makes the record disappear from view result set raises a trappable runtime error. iii. VIEWS having joins with more than one table, data modifications are negligible unless INSTEAD OF Triggers are in place to handle the request. 11. Execute the following code to alter the CustomersView definition: ALTER VIEW [dbo] [CustomersView] WITH SCHEMABINDING AS Select CustID , FName AS [First Name] , LNme AS [Last Name] ,UserID FROM dbo. Customers WITH CHECK OPTION 12. To INSERT through CustomersView, execute the following code: INSERT INTO [CustomersView] ([First Name], [Last Name], UserID) VALUES ('JOED', 'GOH', 'joed@gmail.com') Verify the CustomersView and Customers table content: SELECT*FROMCustomersviewSELECT*FROMCustomers Screenshot of Result Set: Notice that the insert happened though the columns CustID and PinCode mandatory but CustID is IDENTITY and PinCode has a DEFAULT VALUE. All the o mandatory data was supplied in the insert query. 13. To UPDATE through CustomersView, execute the following code: UPDATE Customersview SET [First Name]= 'SAM' WHERE [First Name]=' JOED' SELECT * FROM Customers 14. To DELETE through CustomersView, execute the following code: \begin{tabular}{|l|} \hline Screenshot of Result Set (Update and Delete): \\ \hline What is your understanding? \\ \hline \\ \hline \end{tabular} 15. Now, change the result set produced by CustomersView by adding the WHERE clause to filter Customers with [Last Name] that starts with 'J'. ALTER VIEW [dbo] - [CustomersView] WITH SCHEMABINDING AS Select CustID , FName AS [First Name] , LNme AS [Last Name] ,UserID FROM dbo. Customers WHERE LNme like 'J\%' WITH CHECK OPTION Verify the result set generated by CustomersView: SELECT * FROM CustomersView 16. Now, with the WITH CHECK OPTION enabled, data modification is not allowed if the subsequent result set for the CustomersView will make the modified record disappear. Try executing the following SQL code: INSERT INTO [CustomersView] ([First Name], [Last Name], UserID) VALUES ('JOED', 'GOH', 'joed@gmail.com') Or UPDATE CustomersView SET [Last Name]= 'MYERS' WHERE [Last Name]= ' JORDAN " Observe the error message generated upon the execution of these codes. \begin{tabular}{|l|} \hline Screenshot of error message: \\ \hline \end{tabular} All your VIEWS created can be seen under Views folder in your database. See figure below: Tables System Tables FileTables External Tables Graph Tables dbo.Bookings | dbo.Customers dbo.Products Views System Views dbo.BookingsView dbo.CustomersView