Programming business logic

These questions are based on: 70-229 – Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition Microsoft Self-Test Software Practice Test.

 

Objective: Programming business logic
SubObjective: Manage data manipulation by using stored procedures, transactions, triggers, user-defined functions and views

 

Item No. 70-229.4.1.6
Single Answer, Multiple Choice

 

You are a database developer for a large political action association. Each constituent is assigned to the appropriate state and congressional district based on the constituent’s address. The association’s home page displays bulletins that are customized for each state or congressional district. When a constituent logs on to the Web site, the home page identifies the constituent’s address and displays the appropriate political bulletins.

 

The bulletins are stored in a table named Bulletins, which is in a database named Politics. The script that was used to create the Bulletins table is shown in the exhibit. (Click the Exhibit(s) button.)

 

Constituents need to view data in the Bulletins table, but they do not need to insert, update or delete data in the table. You need to deliver only the appropriate data to the Web site, based on the constituent’s address. Constituents only have permission to view bulletins for their area.

 

What should you do?

 

 

  1. Grant SELECT permissions on the Bulletins table for each state and congressional district.
  2. Grant SELECT permissions on the CongDist and State columns for each state and congressional district.
  3. Create a stored procedure that returns the rows that apply to a specific state and congressional district. Grant appropriate permissions on the stored procedure.
  4. Create a view that is defined to return the rows that apply to a specific state and congressional district. Grant appropriate permissions on the view and the underlying table.

 

Answer:

 

 

  1. Create a stored procedure that returns the rows that apply to a specific state and congressional district. Grant appropriate permissions on the stored procedure.

 

Tutorial:
You should create a stored procedure that returns the rows that apply to a specific state and congressional district and grant appropriate permissions on the stored procedure. This will allow the constituent to view bulletin information based on his address, but it will prevent the constituent from editing any information he sees. One stored procedure will be created that will allow two input parameters (CongDist and State). The Web application will pass the constituent’s congressional district and state to the stored procedure. The application will have permission to execute the stored procedure.

 

You should not grant SELECT permissions on the Bulletins table for each state and congressional district. This would enable constituents to view all bulletins, not just he ones that pertain to their state and congressional district.

 

You should not grant SELECT permissions on the CongDist and State columns for each state and congressional district. This would only allow constituents to view the data in those two columns.

 

You should not create a view that is defined to return the rows that apply to a specific state and congressional district and grant appropriate permissions on the view and the underlying table. You would need to create separate views for each state and/or congressional district. Views do not accept input parameters. This option would require a lot of effort on your part. You would need to ensure all constituents did not have the INSERT or UPDATE permission on the view to prevent them from editing the data.

 

Reference:
1. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Stored Procedures
– Lesson 1: Introduction to Stored Procedures – Purpose and Advantages of Stored Procedures

 

2. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Implementing Stored Procedures
– Lesson 2: Creating, Executing, Modifying, and Deleting Stored Procedures

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment: