EXERCISE 5.1 Database Server and Transaction Server

Direction: Distinguish between Figure 5.1 and Figure 5.2

Figure 5.1

Figure 5.2

Exercise 4.6-Creating a VIEW in SSMS

Direction: Follow the steps below on creating a VIEW in SSMS.

1. If it’s not already open, launch SSMS, double-click Databases, double-click the dbSales database created earlier, and double-click Views.
2. Right-click Views and select New View. In the Add Table dialog box, select Customer | Add | Close.
   
3. In the Customer table, select cfname, clname, and ccontactno.
   
4. To the right of clname, under Sort Type, select Ascending. Your display should look similar to Figure 4.5.
   

5. Press CTRL-S to save the VIEW.
6. In the Choose Name dialog box, enter vw_CustContactInfo. Click OK.
7. With the VIEW created, we can view the data in it. Click the New Query button.
8. If the Connect To Server dialog box appears, accept the defaults and click Connect.
9. Enter the following T-SQL statements and then click Execute:
   

USE Sales
Select * from vw_CustContactInfo

1. 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
    
   
   
   
     
   
   
   
   
   
   
   
   
   
   
   figure 4.5
   
   
   
   
   
   

    
   

   Exercise 4.7-Creating a VIEW in T-SQL

   
   

   Direction: Follow the steps below on creating a VIEW in T-SQL.

   
   

   1. 
      
      
      
      
      Use the same query window that you used to create the VIEW in SSMS. Clear
      the lines that you’ve entered. If you closed the query window, open another
      with the New Query button.
      
   2. 
      
      
      
      
      Enter the following T-SQL statements:

   
   

   USE Sales
   GO
   CREATE VIEW dbo.[vw_CustCreditLimit]
   AS
   SELECT cfname, clname, cmi, ccreditlimit
   FROM dbo.[Customer]

   3. 
      
      
      
      
      Enter the following T-SQL statement:

   
   

   Select * from vw_CustCreditLimit

   4. 
      
      
      
      
      Highlight the statement that you just entered, and press F5 to execute just that
      line. You should have a result set based on data in your table.
      
   5. 
      
      
      
      
      It’s also possible to see the original syntax that created the view with the
      sp_helptext stored procedure. Enter the following T-SQL statement to see the
      definition you just used to create this VIEW:
      

   USE Sales;
   GO
   EXEC sp_helptext ‘dbo.vw_CustCreditLimit’
   GO

   
   

   
   

   
   

   
   

   Exercise 4.8-Update Data in a View

   Direction: Follow the steps below on updating data in a VIEW.

   
   

   1. 
      
      
      
      
      With SSMS open, right-click the dbo.vw_CustCreditlimit VIEW and select Open View. If you don’t see the VIEW, right-click Views and select Refresh. Your VIEW should displayed most likely of Figure 4.2.
   2. 
      
      
      
      
      At the bottom, where the nulls are, fill the columns with data such as your firstname, lastname, middle initial and credit limit.

   Add the name of a friend.

   3. 
      
      
      
      
      Close the VIEW. You can do so by clicking the X at the top right of the tabbed document (not the X in SSMS).
   4. 
      
      
      
      
      Reopen the VIEW. You’ll see the data that you entered has been saved. 
      
      
       
       

   
   
   

    
   
   
   

   Figure 4.2

   
   

   
   

   
   

   
   

   Exercise 4.9-Creating a Script of Database Objects

   Direction: Follow the steps below on creating a script of database objects within SSMS.

   
   

   1. 
      
      
      
      
      Open SSMS, open Databases, and select Sales.
   2. 
      
      
      
      
      Right-click Sales and choose Tasks | Generate Scripts.
   3. 
      
      
      
      
      On the Script Wizard Welcome page, click Next.
   4. 
      
      
      
      
      On the Select Database page, ensure Sales is selected and click Next.
   5. 
      
      
      
      
      On the Choose Script Options page, review the options selected and click Next.
   6. 
      
      
      
      
      On the Choose Object Types page, click Select All to select all the database objects and click Next.
   7. 
      
      
      
      
      On the Choose Database (DDL) Triggers page, click Select All to select the DDL Trigger and click Next.
   8. 
      
      
      
      
      On the Choose Schemas page, click Select All to select all the schemas included in the Sales database and click Next.
   9. 
      
      
      
      
      On the Choose Tables page, choose Select All | Next.
   10. 
       
       
       
       
       On the Output Option page, accept the default of “Script to New Query Window” and click Finish.
   11. 
       
       
       
       
       On the Script Wizard Summary page, click Finish. The wizard will review your selections and create a comprehensive script for the database and all the objects you’ve created. Be patient, this will take a while. When finished, you can view the report that identifies all the objects that were scripted. On the Generate Script Progress page, click Close.
   12. 
       
       
       
       
       In the query window, press CTRL-A to select all the text. Press CTRL-C to copy it to the clipboard.
   13. 
       
       
       
       
       Open an instance of Notepad. Within Notepad, press CTRL-V to paste the scripts into Notepad. Press CTRL-S to save the file.
   14. 
       
       
       
       
       In the Save As dialog box, browse to where you want to save the file, enter Sales.sql, and click Save. 
       
       
       
       
        

   Exercise 4.10-Creating a Database Diagram

   Direction: Follow the steps below on creating a database diagram within SSMS.

   
   

   1. 
      
      
      
      
      In the Sales database, select the Database Diagrams container. In the SSMS warning, click OK to allow SSMS to create necessary objects for database diagramming.
   2. 
      
      
      
      
      Right-click Database Diagrams and click New Database Diagram.
   3. 
      
      
      
      
      In the Add Table dialog box, select all then click add.
   4. 
      
      
      
      
      Arrange the tables so that they look like Figure 4.10.1.

   
   

   5. 
      
      
      
      
      Click the File drop-down menu and click Save Diagram.
   6. 
      
      
      
      
      In the Choose Name dialog box, enter SalesDiagram and click OK.

     

   figure 4.10.1

   

   
   
   
   
   
   
    
   
   

   
   

   
   

    
   
    

CSIT402 LAB EXERCISE 4.3 DENORMALIZATION

Directions: Write your answers on the coding sheet/booklet.

1. Discuss the Denormalization process.

2. Give an example ER Diagram translation from the normalized ER Diagram to the Denormalized ER diagram.

Exercise 4.1-Logical Database Design

Direction: Below is an ER Diagram of a typical Sales System. Convert it to a Relational Schema.Try to do a research about the ER Diagram and the Relational Schema. Write your answer on a 1 whole sheet of paper. 


 

Exercise 4.2-Logical to Physical Database Design

Direction: Using MS SQL SERVER 2005, implement the ER Diagram of a typical Sales System (refer Exercise 4.1). Named your database as SALES. Save a copy of a SALES.sql file to your account.