1、微软认证 70-228模拟试卷 4及答案与解析 1 You are the administrator of a database that contains 64 lookup tables. These tables store static data that should not change. However, users report that some of this data is being changed. You need to prevent users from modifying the data. You want to minimize changes to y
2、our security model and to your database applications. How should you modify the database? （ A） Create a filegroup named LOOKUP. Move the lookup tables to this filegroup. Select the read only check box for the filegroup. （ B） Create a database role named datamodifier. Grant SELECT permissions to the
3、datamodifier role. Add all users to the role. （ C） Deny INSERT, UPDATE, and DELETE permissions for all users. Create stored procedures that modify data in all tables except lookup tables. Require users to modify data through these stored procedures. （ D） Create a view of the lookup tables. Use the v
4、iew to allow users access to the lookup tables. 2 You are the administrator of a SQL Server 2000 computer. The server contains a database named inventory. The database has a Parts table that has a field named InStock. When parts are shipped, a table named PartsShipped is updated. When Parts are rece
5、ived, a table names PartsReceived is updated. The relationship of these tables is shown in the exhibit. You want the database to update the InStock field automatically. What should you do? （ A） Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the Parts t
6、able. （ B） Create a user-defined function those calculations that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables. （ C） Use a view that creates an InStock as a part of an aggregate query. （ D） Create stored procedures for modifying the PartsRece
7、ived and the PartsShipped tables that also modify the InStock fielding the parts table. Use these procedures exclusively when modifying data in the PartsReceived and the PartsShipped tables. 3 You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecord
8、s. You have recently added a hard disk to the server and configured the database.The data files are configured as shown in the Database Properties exhibit. The database is configured as shown in the Database Schema exhibit.Each primary key is clustered. All foreign keys contain nonclustered indexes.
9、 You want to use the space on the new hard disk to make the database respond more quickly to queries.What should you do? （ A） Re-create the Notes table on the SECONDARY filegroup. （ B） Re-create the nonclustered indexes on the SECONDARY filegroup. （ C） Re-create the Patients and Doctors tables and t
10、heir indexes on the SECONDARY filegroup. （ D） Re-create the Cases and Notes tables and their indexes on the SECONDARY filegroup. 4 You are the administrator of a SQL Server 2000 computer. You are creating a data transformation services package. As the first step in this process, you need to load dat
11、a from text files into a database table. These text files contain data on new stores that join your franchise. The text files list the data columns in the following format StoreID, StoreName, Address, City, State, PostalCode, ManagerID, StoreTypeID, FacilityID.The destination table is configured as
12、shown in the exhibit. You want to load the data into the table as quickly as possible. What should you do? （ A） Use a Bulk Insert Task to read the data into a temporary table. Use an Execute SQL task to import the appropriate data into the destination table. （ B） Create and edit a format file to sel
13、ect the columns you want to import. Use a Bulk Insert Task, and then specify the format file to import the appropriate data into the destination table. （ C） Use a transform data task to import the data. Use Microsoft ActiveX transformation scripts to write the appropriate data to the appropriate col
14、umns in the destination table. （ D） Create and edit a format file to select the columns you want to import. Use a transform data task, and then specify the format file to import the appropriate data into the destination table. 5 You are the administrator of a SQL Server 2000 computer. The server con
15、tains confidential information about contracts on which your company has placed bids. Company policy requires that bid information be removed permanently from the database one year after the bid closes. You need to comply with this policy and minimize server overhead. What should you do? （ A） Create
16、 a Data Transformation Services (DTS) package to remove bids that have a closing data older than one year. （ B） Create a trigger to delete any bids that have a closing data older than one year. Bind the trigger to the bids table for all INSERT, UPDATE, and DELETE events. （ C） Create a stored procedu
17、re to delete any bids that have a closing data order than one year. Use SQL server agent to schedule the stored procedure to run every night. （ D） Create a view that contains a WHERE clause to exclude bids that have a closing date older than one year. 6 You are the administrator of a SQL Server 2000
18、 computer. The server contains a database named Sales. The database will store sales transactions. Retail locations must be able to add transactions to the database 24 hours a day every day. The server is configured as shown in the exhibit. You need to configure the data files and transaction log fo
19、r the Sales database. What should you do? （ A） Place the transaction log on physical disk 0 and the data file on the RAID-5 disk array. （ B） Place the transaction log on the RAID-5 disk array and the data file on physical disk 0. （ C） Place the transaction log and the data file on physical disk 0. （
20、 D） Place the transaction log and the data file on the RAID-5 disk array. 7 You are the administrator of two Microsoft Windows 2000 computers. One computer is running Internet Information Services (IIS), and the other is running SQL Server 2000. Company partners need to connect by means of the Inter
21、net and query data stored on the SQL Server computer. Some of these partners have computers that do not use Microsoft operating systems or Web browsers. You need to configure the IIS and SQL Server 2000 computers to allow access to data by means of the IIS virtual directory. IIS uses Basic Authentic
22、ation to allow access to the virtual directory. Only company partners should have access to the SQL Server computer by means of the Internet. The partners should not be allowed to make ad hoc queries. You want to be able to audit successful and failed logins to the SQL Server computer. You want to a
23、llow the partners access to the SQL Server computer while keeping your security scheme as simple as possible. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.) （ A） Configure the IIS virtual directory to allow URL queries. （ B） Configure the IIS vi
24、rtual directory to allow template queries. （ C） Create a new Windows user account. Create a corresponding Windows Authenticated login on the SQL Server computer. Configure the IIS virtual directory to always use this login when connecting to the SQL Server computer. （ D） Create a Windows Authenticat
25、ed login on the SQL Server computer for the IIS Internet Guest Account. Configure the IIS virtual directory to always use the Internet Guest Account when connecting to the SQL Server computer. （ E） Create a Windows Authenticated login for each company partner on the SQL Server computer. Configure th
26、e IIS virtual directory to use Windows Integrated Authentication when connecting to the SQL Server computer. 8 You are the administrator of a SQL Server 2000 database. You import a table of geographic information from a Microsoft access database into a SQL Server 2000 database. The table has 12,000
27、rows. Each row averages 5,000 bytes. The table contains lockup data that does not change. You want to minimize the size of the data file and the time required to back up the data. Which two actions should you take? (Each correct answer presents part of the solution. Choose two) A. Create a 60-MB dat
28、a file named geography.ndf B. Create a 95-MB data file named geography.ndf C. Create a 60-MB data file named geography.mdf D. Create a 95-MB data file named geography.mdf E. Place the table in the PRIMARY filegroup. F. Place the table in a new filegroup named LOCATION. 9 You are the administrator of
29、 several SQL Server 2000 computers. You configure these servers to send e-mail messages to SQL Server operators whenever a SQL server agent job fails or encounters an error. Your companys software developers create a set of stored procedures that send query results in email messages about stored pro
30、cedures that have not been successful. By using a MAPI profile named MS Exchange Settings, you verify that SQLAgentMail is correctly configured and working properly. You need to configure the servers so that the stored procedures function correctly. Which two actions should you take? (Each correct a
31、nswer presents part of the solution. Choose two.) A. Set the security permissions on your e-mail server to permit multiple connections to the SQL Server mailbox. B. Set the security permissions on your e-mail server to permit connections from the MSSQLServer service account. C. Configure the MSSQLSe
32、rver service to use the same service account as the SQLServerAgent service. D. Configure the SQLServerAgent service to use the same service account as the MSSQLServer service. E. Configure SQL Mail to use the MS Exchange Settings MAPI profile. F. Configure SQL Mail to use the Default Settings MAPI p
33、rofile. 微软认证 70-228模拟试卷 4答案与解析 1 【正确答案】 A 【试题解析】 Explanation: SQL Server 2000 allows filegroups other than the primary filegroup to be marked as readonly. A filegroup that is marked read-only cannot be modified. Tables that must not be modified can be placed on a filegroup that can then be mark as r
34、ead-only. This will prevents accidental updates. Incorrect Answers: B: This solution effectively permits all users to modify any table in the database, as they would all have SELECT permissions to the database. C: This solution will effectively prevent all users from being able to performing these t
35、asks on all tables in the database as they are denied permissions to run INSERT, UPDATE, or DELETE statements against the database. A better solution would be to apply the deny permissions at the table level of the lookup tables. This would, however, require unnecessary administrative time. D: Views
36、 are virtual tables that are used to filter tables or rows that the users should not have access to, however, if the users have access to the base tables, they can still run INSERT, UPDATE, and DELETE statements against the base table. 2 【正确答案】 A 【试题解析】 Explanation: Triggers are a special class of s
37、tored procedure defined to fire automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. They are powerful tools that can be used to enforce business rules automatically when data is modified. Triggers can extend the integrity checking logic of SQL Server constrai
38、nts, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality. In this scenario an AFTER UPDATE trigger can be used to update the tables to update the PartsShipped and the PartsReceived tables that update the InStock column in t
39、he parts table. Incorrect Answers: B: A user-defined function is a subroutine that is made up of one or more Transact-SQL statements and can be used to encapsulate code for reuse. It can be used to run aggregate calculations. However, a simple update of the InStock column, and not a aggregate functi
40、on, is required when changes are made to the PartsShipped and PartsReceived tables. This can be accomplished through the use of triggers. C: A view would not be able to detect changes in any of the columns. Note: A view is a virtual table that allows data to be accessible through it rather than thro
41、ugh the base table. A view can be referenced through Transact-SQL statements in the same way a table is. In addition a table can be used to restrict a user to specific rows and/or columns in a table, join columns from multiple tables so that they appear as a single virtual table and can aggregate in
42、formation instead of supplying details to the user. D: A simple update of the InStock column, and not a stored procedure, is required when changes are made to the PartsShipped and PartsReceived tables. This can be accomplished through the use of triggers. Note: A stored procedure is a group of Trans
43、act-SQL statements compiled into a single execution plan. Stored procedures can return data as output parameters; return codes; a result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure; or a global cursor that can be refer
44、enced outside the stored procedure. Stored procedures assist in achieving a consistent implementation of logic across applications and can improve performance. 3 【正确答案】 B 【试题解析】 Explanation: With SQL Server 2000 it is possible to create tables or indexes on specific filegroups. This allows us to con
45、trol where the databases tables and indexes are physically located as filegroups can be placed on different hard drives. Placing heavily accessed tables in one filegroup and the tables nonclustered indexes in another filegroup on different physical disk can improve database performance because it wi
46、ll allow separate threads to access the tables and indexes. However, a table and its clustered index cannot be separated into different filegroups as the clustered index determines the physical order of the data in the table. Incorrect Answers: A, C, D: With SQL Server 2000, placing the tables and t
47、heir nonclustered indexes in different filegroups that are located on different physical drives provides better performance than placing tables in different filegroups and on different physical drives. 4 【正确答案】 B 【试题解析】 Explanation: The Bulk Insert Task is the fastest mechanism for copying large amo
48、unts of data into a SQL Server 2000 table or view. However, transformations cannot be performed on the data while it is moved from the source file to the table or view, as this would slow down the data copy process. Thus the text file must be formatted before the Bulk Insert Task is used. The format
49、 file provides the default information used either to bulk copy the data in the data file into an instance of SQL Server 2000 or to bulk copy data out from the table. The format file also provides a way to bulk copy data selectively from a data file to an instance of SQL Server. This allows for the transfer of data to a table when there is a mismatch between fields in the data file and columns in the table. By using a format file, it is possible to bulk copy data into an instance of SQL Server without having to add or delete unnecessary data, or reorder existing data, in the data file. Inco
