Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
71. You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies.
You create several stored procedures in the database that contain confidential information.
You want to prevent the companies from viewing this confidential information.
What should you do?
A. Remove the text of the stored procedures from the syscomments system table.
B. Encrypt the text of the stored procedures.
C. Deny SELECT permissions on the syscomments system table to the public role.
D. Deny SELECT permissions on the sysobjects system table to the public role.
Ans: B
72. You are a database developer for Southridge Video. The company stores its sales information in a SQL Server 2000 database.
You are asked to delete order records from this database that are more than five years old.
To delete the records, you execute the following statement in SQL Query Analyzer:
DELETE FROM Orders WHERE OrderDate < (dateadd(year, -5, getdate()))
You close SQL Query Analyzer after you receive the following message:
(29979 row(s) affected)
You examine the table and find that the old records are still in the table.
The Current Connection properties are shown in the exhibit:
What should you do?
A. Delete records in the tables that reference the Orders table.
B. Disable triggers on the Orders table.
C. Execute a SET IMPLICIT_TRANSACTIONS OFF statement.
D. Execute a SET CURSOR_CLOSE_ON_COMMIT ON statement.
E. Change the logic in the DELETE statement.
Ans:C
73. You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size.
You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C.
Which script should you use to create the database?
A. CREATE DATABASE CustomerContacts
ON
(NAME = Contacts_database,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 10,
MAXSIZE = 1GB
FILEGROWTH= 5)
B. CREATE DATABASE CustomerContacts
ON
(NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 10,
MAXSIZE = 1GB
FILEGROWTH= 10%)
C. CREATE DATABASE CustomerContacts
ON
(NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 100,
MAXSIZE = UNLIMITED)
D. CREATE DATABASE CustomerContacts
ON
(NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 1GB)
Ans:D
74. You are a database developer for WoodGrove Bank. The company stores its sales data in a SQL Server 2000 database.
You want to create an indexed view in this database.
To accomplish this, you execute the script shown in the exhibit:
The index creation fails, and you receive an error message.
You want to eliminate the error message and create the index.
What should you do?
A. Add an ORDER BY clause to the view.
B. Add a HAVING clause to the view.
C. Change the NUMERIC_ROUNDABORT option to ON.
D. Change the index to a unique, nonclustered index.
E. Add the WITH SCHEMABINDING option to the view.
Ans:E
75. You are a database developer for your company's SQL Server 2000 database. Another database developer named Andrea needs to be able to alter several existing views in the database. However, you want to prevent her from viewing or changing any of the data in the tables.
Currently, Andrea belongs only to the Public database role.
What should you do?
A. Add Andrea to the db_owner database role
B. Add Andrea to the db_ddladmin database role
C. Grant Andrea CREATE VIEW permissions.
D. Grant Andrea ALTER VIEW permissions
E. Grant Andrea REFERENCES permissions on the tables.
Ans: B
76. You are a database developer for a rapidly growing company. The company is expanding into new sales regions each month.
As each new sales region is added, one or more sales associates are assigned to the new region. Sales data is inserted into a table named RegionSales, which is located in the Corporate database.
The RegionSales table is shown in the exhibit:
Each sales associate should be able to view and modify only the information in the RegionSales table that pertains to his or her regions.
It must be as easy as possible to extend the solution as new regions and sales associates are added.
What should you do?
A. Use GRANT, REVOKE and DENY statements to assign permission to the sales associates.
B. Use SQL Server Enterprise Manager to assign permission on the RegionSales table.
C. Create one view on the RegionSales table for each sales region. Grant the sales associates permission to access the views that correspond to the sales region to which they have been assigned.
D. Create a new table named Security to hold combinations of sales associates and sales regions. Create stored procedures that allow or disallow modifications of the data in the RegionSales table by validating the user of the procedures against the security table. Grant EXECUTE permissions on the stored procedures to all sales associates.
E. Create a new table named Security to hold combinations of sales associates and sales regions. Create user-defined functions that allow or disallow modifications of the data in the RegionSales table by validating the user of the function against the security table. Grant EXECUTE permissions on the functions to all sales associates.
Ans:D
77. You are a database developer for a toy company. Another developer, Marie, has created a table named ToySales. Neither you nor Marie is a member of the sysadmin fixed server role, but you are both members of db_owner database role.
The ToySales table stored the sales information for all departments in the company.
This table is shown in the exhibit:
You have created a view under your database login named vwDollSales to display only the information from the ToySales table that pertains to sales of dolls.
Employees in the dolls department should be given full access to the data. You have also created a view named vwActionFigureSales to display only the information that pertains to sales of action figures.
Employees in the action figures department should be given full access each other's data. The two departments currently have no access to the data.
Employees in the doll department are associated with the Doll database role. Employees in the action figures department are associated with the ActionFigure database role.
You must ensure that the two departments can view only their own data.
Which three actions should you take? (Each correct answer presents part of the solution. Choose three)
A. Transfer the ownership of the table and the views to the database owner.
B. Grant SELECT permissions on the ToySales table to your login.
C. Grant SELECT permissions on the vwDollSales view to the Doll database role.
D. Grant SELECT permission on the vwActionFigureSales view to the ActionFigure database role.
E. Deny SELECT permission on the ToySales table for the Doll database role.
F. Deny SELECT permissions on the ToySales table for the ActionFigure database role.
Ans:A,C,D
78. You are a database developer for your company's SQL Server 2000 database. The database is installed on a Microsoft Windows 2000 server computer. The database is in the default configuration.
All tables in the database have at least one index. SQL Server is the only application running on the server.
Database activity peaks during the day, when sales representatives enter and update sales transactions. Batch reporting is performed after business hours.
The sales representatives report slow updates and inserts.
What should you do?
A. Run System Monitor on the SQL Server:Access Methods counter during the day. Use the output from System Monitor to identify which tables need indexes.
B. Use the sp_configure system stored procedure to increase the number of locks that can be used by SQL Server.
C. Run SQL Profiler during the day. Select the SQL:BatchCompleted and RPC:Completed events and the EventClass and TextData data columns. Use the output from SQL Profiler as input to the index Tuning Wizard.
D. Increase the value of the min server memory option.
E. Rebuild indexes, and use a FILLFACTOR of 100.
Ans:C
79. You are a database developer for a shipping company. You have a SQL Server 2000 database that stores order information. The database contains tables named Order and OrderDetails.
The database resides on a computer that has four 9-GB disk drives available for data storage. The computer has two disk controllers. Each disk controller controls two of the drives. The Order and OrderDetail tables are often joined in queries.
You need to tune the performance of the database.
What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new filegroup on each of the four disk drives.
B. Create the clustered index for the Order table on a separate filegroup from the non-clustered indexes
C. Store the data and the clustered index for the OrderDetail table on one filegroup, and create the non-clustered indexes on another filegroup
D. Create the order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup
E. Create two filegroups that each consist of two disk drives connected to the same controller.
Ans: D,E
80. You are the database developer for a brokerage firm. The database contains a tablenamed Trades.
The script that was used to create this table is shown in the Script for Trades Table exhibit:
The Trades table has frequent inserts and updates during the day. Reports are run against the table each night.
You execute the following statement in the SQL Query Analyzer:
DBCC SHOWCONTIG (Trades)
You want to ensure optional performance for the insert and select operations on the Trades table.
What should you do?
A. Execute the DBCC DBREINDEX statement on the table.
B. Execute the UPDATE STATISTICS statement on the table.
C. Execute the DROP STATISTICS statement on the clustered index.
D. Execute the DBCC INDEXDEFRAG statement on the primary key index.
E. Execute the DROP INDEX and CREATE INDEX statements on the primary key index.
Ans: A