Designing Database Solutions by Using MS SQL Serv 2005

1.BACKGROUND Company Overview A.Datum Corporation provides online survey services.These services include creation.survey execution,and survey reporting. The company sells aggregated survey results to third-party subscribers.These results do not contain any personally identifiable information. Physical Locations A.Datum corporation is an international company that has offices in London,Seattle,and Sydney.Customers and survey participants are located worldwide. Planned Changes The company will be migration all existing applications to SQL S erver 2005. A survey application named APP1 will be modified to provide automatic customer notification when survey results are available. A new application named APP2 will be created to enable analysis of demographics of survey participants.This application must allow filtering based on a variety of criteria.One specific criterion that must be used for filtering is the participant's age at the time of survey completion. SQL Server 2005 will be the database system that must be used for all new development. Problem Statements Creating new reports for customers is a time-consuming process that currently requires custom development,because developers must understand the underlying database design. Customers do not currently have the ability to create ad hoc reports on their survey data .Customers want to be able to perform minor changes to reports;they do not want to wait for A.Datum Corporation developers to make those changes. EXISTING ENVIRONMENT Existing Application Environment A.Datum Corporation is currently running SQL Server 2000 Enterprise Edition with Service pack 3a.SQL Server is hosted on computers that run Microsoft Windows Server 2003 with Service Pack 1. APP1 is a Web-based application that is used by customers and survey participants. Internal analysts view data by using a smart client application named APP3 to perform their analyses. Existing Supporting Infrastructure A.Datum Corporation purchases information about potential survey participants from multiple business partners.These partners use File Transfer Protocol(FTP) to send XML files that contain the information. The current database server is named Server3.It is utilizing 25 percent of its allocated disk space. The Participant table in the current database contains approximately 15 million rows. BUSINESS REQUIREMENTS General Requirements Individual customers must receive and view only their own data. APP2 must allow viewing and analysis of demographic data on survey participants. Reports Customers might want to see their reports in multiole formats,including HTML,PDF,Microsoft Excel,comma-se[arated values (CSV),and TIFF. In customer reports,dates should be displayed in the calendar format that is native to the user of the report.This formatting should not affect the fime needed to produce the report. Reports must be available on demand or run as a scheduled service.Customers might want A.Datum Corporation to send reports to them automatically by e-mail Customers must not be required to understand the underlying database design in order to perform ad hoc reporting on their survey data. Customers have requested a new report named Results that lists each survey question and the names of the first five participants who selected each answer.The desired output display is shown in the following table. Question Answer choice Participant number 1 A Allison Brown,Wendy Kahn 1 B Amy Rusko,Paul Koch 1 C Anton Kirilov,Matthew Carroll 1 D Kevin Kennedy,Alan Shen Running the report must consume as few CPU cycles as possible on the A.Datum Corporation servers. Internal analysts need to view customer reports in APP3.Analysts must not be able to modify these reports.Analysts must not be granted unnecessary privileges. Aggregated survey results must be made available to subscribdrs in XML file format.The files will be transmitted to the subscribers by using FTP.The results will contain both element-centric and attribute-centric data. Views The company' s marketing department wants to measure the value of the data on potential participants that is purchased from business partners.The marketing department requires a view of the data that contains all potential participants to whom surveys were distributed and the names of surveys that the participants have submitted.Some potential participants might not have submitted any surveys.but they should be included in the view.The Participant table should contain all potential participants.The Survey table should contain all surveys that were submitted. Performance Queries must retum data in three seconds or less.This performance metric must be met even is cases where the database exceeds one terabyte in size. As part of the upgrade to SQL Server 2005,baseline database performance metrics must be developed .A performance monitoring process must be established that will allow the company to track database performance over time against the baseline measurements. Availability APP1 must be available to customers and survey participants 24 hours per day,seven days per week.A monthly two-hour downtime will be scheduled for maintenance activities. TECHNCIAL REQUIREMENTS Security Aggregate data files that are produced for third-party subscribers will contain a unique subset of the data for each subscriber .A history of the data that was distributed to each subscriber must be maintained. Maintainability The upgrade must fulfill the following requirements: . All SQL Server 2005 functionality must be enabled. . All stored procedures must continue to function as designed . The test team must validate the migration within a two-day test period. . The test team is familiar with Transact-SQL test execution;however,they are not trained on the new SQL Server management tools . The test results after the upgrade must be compared to the test results that occurred prior to the upgrade. . The database control strategy must ensure that authorized changes are applied to all the databases only during the monthly maintenance periods. . The database control strategy must minimize any permission resets that occur as a result of changes that are made to the production database. Data . The XML files that contain data on potential survey participants often contain duplicate records that vary slightly.An example of this variation is shown in the table below.This data must be standardized to minimize the number of duplicate participant records.The XML representation of the data must be stored in the database.when any of the data is updated,the XML representation of the data must be kept up-to-date. . The data on potential survey participants must be imported into a staging database named DB1. . Historical survey data must be retained for an indefinite period. An example of duplicate data for a single participant is shown in the forllowing table.

Correct:
2.You are designing a performance monitoring plan for the database that supports APP1. You need to monitor only necessary indicators. Which indicator or indicators should you include in the baseline? (Choose all that apply.)
A.response times for frequently used queries
B.database backup and restore completion times
C.disk I/O
D.CPU utilization
E.network packet size
F.SOAP requests failed
G.HTTP authenticated requests
Correct:A C D
3.You need to ensure that survey customers are automatically notified when their survey results are available. You must also ensure that customers can manage their own recipient lists. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Create a Subscription Management interface that will allow customers to modify their recipient lists.
B.Create a custom Notification Services Content Formatter to send an e-mail message to customers when their survey results are available.
C.Create a custom Notification Services Event Provider to send an e-mail message to customers when their survey results are available.
D.Create a Notification Services custom delivery protocol to send an e-mail message to customers when their survey results are available.
E.Create Notification Services Event rules to send an e-mail message to customers when their survey results are available.
Correct:A E
4.You need to cleanse and standardize the data on potential survey participants prior to inserting it into the staging database. What should you do?
A.Import the data into a staging table by using the OPENROWSET BULK statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.
B.Import the data into a staging table by using the BULK INSERT statement. Execute a Transact-SQL stored procedure to cleanse the data and to insert the data into the database.
C.Create a SQL Server Integration Services (SSIS) package to import, standardize, and cleanse the data.
D.Create a CLR stored procedure to import the data into a staging table, to cleanse and standardize the data, and to insert the data into the database.
Correct:C
5.You need to provide ad hoc reporting capabilities to fulfill business requirements. What should you do?
A.Create a SQL Server Reporting Services (SSRS) report. Enable customers to modify the report by using Report Builder.
B.Create an ASP.NET application that will enable customers to view and modify reports by calling the SQL Server Reporting Services (SSRS) API.
C.Create an ASP.NET application that will enable customers to view and modify reports by using a SQL Server Reporting Services (SSRS) ActiveX control.
D.Create a SQL Server Reporting Services (SSRS) Report Model. Enable customers to modify reports based on the model by using Report Builder.
Correct:D
6.You are upgrading the database servers that are used by all of the companys applications. As the first step in the upgrade, you upgrade the development database servers. You need to verify that the upgrade of the database servers did not introduce any errors into the databases. What should you do?
A.Execute unit test scripts for each stored procedure in each database.
B.Execute the stored procedures in the SQL Server Management Studio debugger, and verify the results.
C.Set the database compatibility level on the database servers to 80.
D.Execute the application user interface test scripts to verify proper operation.
Correct:A
7.You need to provide analyst reporting capabilities that fulfill the business requirements. What should you do?
A.Add a Report Viewer control to APP3.
B.Add an Internet Explorer ActiveX control to APP3. Set the controls URL to the A. Datum Corporation Report Manager.
C.Create a hyperlink in APP3 that references the Report Manager.
D.Create a proxy class to the Report Server Web service.
Correct:A
8.BACKGROUND Company Overview Woodgrove Bank is a large consumer bank that has one million sustomers in 10 cities. Planned Changes The company is developing a system for remote banking.Dedicated PC terminals that customers can use to perform many of theeir banking functions,such as paying bills and applying for loans,will be placed throughout the cities where Woodgrove Bank is active. Problem Statement The company wants a solution to be designed that will use SQL Server 2005 to manage the data that is used by the customer terminals. EXISTING ENVIRONMENT Existing Application Environment The company's main banking system is a proprietary application.The new terminal personal banking system will use SQL Server 2005 to host all information about the accounts. BUSINESS REQUIREMENTS Terminals The company has the following requirements for the new terminals: . The terminals need to support third-party advertisements that will be displayed while a customer is using the terminals. . The advertisers must be able to retrieve real-time statistics on the display of their ads any time,directly from their own systems. . The entire terminal application must support more than one language,and it must be possible to add more languages without changing the database structure. Multiple Language Support The company has the following requirements for the new application' s user interface (UI) elements: . Each UI textual element will have only one description of the element' s purpose,and it will be in English,For example,the text Save,regardless of language,should have the following description:The text displayed on buttons used to save changes. . The database design must conform to third normal form(3NF)

Correct:
9.You need to design the storage of the application texts for UI elements to fulfill the requirements. Which table and column design should you use?
A.Languages (LanguageID, Name)Elements (ElementID, Description)ElementLanguages (ElementID, LanguageID, Text)
B.Languages (LanguageID, Name)Elements (ElementID, LanguageID, Description, Text)
C.Languages (LanguageID, Name)ElementTexts (LanguageID, Text)ElementDescriptions (ElementID, Description)
D.Languages (LanguageID, Name)Elements (ElementID, Description, Text)
Correct:A
10.You need to ensure that the stored procedure that inserts new loan requests can access data in tables that are stored in another database on the same SQL Server instance. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Ensure that the stored procedure and the tables are owned by users that are associated with the same SQL Server login.
B.Ensure that the stored procedure and the tables are owned by the same database user.
C.Ensure that the stored procedure and the tables use the same database schema.
D.Configure the multiuser database option on both databases.
E.Configure the cross-database ownership chaining option on both databases.
Correct:A E
11.You are designing the loan application process for the customer terminals. You need to design a Transact-SQL stored procedure that fulfills the business and technical requirements. Which type of Transact-SQL stored procedure should you use?
A.one that uses an internal SQL Server Web service endpoint
B.one that runs a SQL Server Integration Services (SSIS) package
C.one that uses Service Broker
D.one that runs a user-defined function
Correct:C
12.You need to design the retrieval of advertising statistics without compromising security. What should you do?
A.Design a Notification Services solution that sends the advertising statistics to specified subscribers by e-mail.
B.Design a SQL Server Web service that runs a stored procedure to return the statistics on a call from the advertisers application.
C.Design a Database Mail solution that sends the advertising statistics to e-mail addresses that are specified in a table.
D.Design a stored procedure to return the statistics on a call from the advertisers application.
E.Design a user-defined function to return the statistics on a call from the advertisers application.
Correct:B
13.You need to design the storage of the loan response message. Which actions or actions should you perform? (Choose all that apply.)
A.Use the xml data type to store the message.
B.Use a user-defined data type to store the message.
C.Use the nvarchar(max) data type to store the message.
D.Use a check constraint to validate that the correct tags are used.
E.Use an XML schema definition (XSD) to validate that the correct tags are used.
F.Use a foreign key constraint to validate that the correct tags are used.
Correct:A E
14.You need to design the restrictions on the Bank.Customers table. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A.Design a trigger that verifies that the first names and surnames are not empty strings.
B.Design a check constraint that verifies that the first names and surnames are not empty strings.
C.Design a check constraint that uses a CLR user-defined function to verify that either the phone number is a null value or the format of the phone number is valid.
D.Design a trigger that uses a CLR user-defined function to verify that either the phone number is a null value or the format of the phone number is valid.
E.Design a check constraint that ensures that the phone number cannot be changed from a valid format to a null value or to an invalid format.
F.Design a trigger that ensures that the phone number cannot be changed from a valid format to a null value.
Correct:B C F
15.You need to design the database permissions for access from the terminals to the database server. Which action or actions should you perform? (Choose all that apply.)
A.Create a database role named BankTerminals.
B.Grant the BankTerminals role the Execute permission for the WoodgroveBanking database.
C.Grant database access to the Windows Bank Terminals group, and add the group to the BankTerminals role.
D.Grant the BankTerminals role the Execute permission for the Bank schema.
E.Grant the Windows Bank Terminals group the Execute permission for the Bank schema.
F.Grant the BankTerminals role the Execute permission for all stored procedures and user-defined functions in the Bank schema.
Correct:A C D
16.BACKGROUND Company Overview Trey Research performs analysis of research data for corporate customers. The company has a main office and 38 branch offices. Planned Changes The company is developing a new SQL Server 2005-based application solution that will be deployed to existing servers at all branch offices. EXISTING ENVIRONMENT Existing Application Environment A SQL Server 2000-based application is currently deployed to servers at branch offices. It will be replaced by the new SQL Server 2005-based application solution. BUSINESS REQUIREMENTS General Requirements Aggregated data from a table named Cust_Data is queried frequently. The aggregated value must be updated each time rows in the Cust_Data table are added or changed. Data in the table changes infrequently. The table includes four columns that contain numeric data. This data needs to be combined into a column named Cust_CalcData by using a mathematical algorithm. Users occasionally need to run queries that select data from both current and archived data. Old data will be moved to an archival table, and the users should not be required to consider this fact when they issue queries. The users must have the flexibility of building their own queries. Management has purchased a COM component that performs complex calculations. Neither the source code nor a specification for the calculations used in this component is available. The Results table contains a column named ComplexCalc that must contain a value that is calculated by the COM component. The column must always be kept up-to-date as data is inserted and updated in the Results table. The table is heavily queried by queries that return the ComplexCalc column. However, the table is updated infrequently. Some long-running reports will be processed asynchronously. Two Service Broker services will be created. One will process reports for customers, and the other will process reports that are used by employees. Customer and employee reports must be able to run in parallel, Six client applications will be able to submit requests for these reports, and the application users will receive the completed reports by e-mail. Performance The new application solution must not add more than 40 percent processor or disk overhead to branch office servers. When servers are in production, server administrators must be able to identify when the server hardware is approaching full utilization. A view named Research_Aggr will be migrated to the new application solution. This view produces aggregations on a large amount of data and is used frequently to read data, but not to change or add data. The view is usually queried by using an ORDER BY or WHERE clause, which usually specifies the Data1 or Data2 columns of the view. These columns are calculated by using deterministic functions. SQL Server Reporting Services (SSRS) will be used for on-demand reports. The number of columns that will be produced by these reports will vary depending on the data that is used by the reports. The performance of these reports is governed by a service level agreement (SLA). If a report fails to fulfill its defined SLA, the company must be able to identify whether the problem is caused by the report itself or by the underlying data source. The amount of new research data from customers can be very large. This data will be imported by using SQL Server Integration Services (SSIS) packages. The packages must be tested to ensure that the likelihood of their failure in production is as low as possible. The execution of these packages in production must not negatively affect end-user response times during business hours. Neither the customer data nor the research data contains any confidential information. TECHNCIAL REQUIREMENTS Security Branch office database administrators will need to have both user and administrator access to SQL Server 2005. However, they must not be able to view the source code of database objects such as stored procedures or triggers. Changes that are made to the database schema on any branch office server must be documented in an e-mail message that is sent to the main office. Maintainability Business logic must be centralized in SQL Server 2005 to the greatest degree possible. However, when possible, the use of server-side code should be minimized. Notification Services will be used to send notices to company managers. The size of the Notification Services database must be minimized.
Correct:
17.You need to ensure that old data and current data can be queried according to the business requirements. What should you do?
A.Specify a view based on the archival data table and current data tables.
B.Specify that the data must be queried by using a stored procedure.
C.Specify that both the archival data table and the current data tables must have indexes created on the same columns.
D.Specify that a SQL Server Agent job must be used to copy current data into the archival data table each night.
Correct:A
18.You need to design the Service Broker queues that will be used to submit requests for long-running reports. Your solution must minimize complexity of using the queues by applications. What should you do?
A.Specify one queue for each client application that can submit report requests.
B.Specify one queue for each of the two report-generation services.
C.Specify one queue for all long-running report requests.
D.Specify eight queues: One for each service, and one for each client application.
Correct:B
19.You need to ensure that database schema changes in branch offices result in the proper notifications. What should you do?
A.Install Notification Services on all branch office servers, and configure Notification Services to use a local notifications database.
B.Specify that all SQL Server log files must be archived and sent by e-mail to the main office.
C.Implement DML triggers that use Database Mail to send an e-mail message about the DML activity.
D.Implement DDL triggers that use Database Mail to send an e-mail message about the DDL activity.
Correct:D
20.You need to ensure that the Cust_CalcData column is implemented appropriately. What should you do?
A.Specify that the Cust_CalcData column must be implemented as a computed column of the table.
B.Specify that the Cust_CalcData column must be indexed using a non-clustered index.
C.Specify that the Cust_CalcData column must be implemented as a column that uses a user-defined data type.
D.Specify that the Cust_CalcData column must be updated by a SQL Server Agent job that runs nightly.
Correct:A
21.You need to ensure that the testing of the SSIS packages fulfills the business requirements. What should you do?
A.Specify that packages must be run, using standard sample data, on a test server.
B.Specify that packages must be run on the production database.
C.Specify that the packages must be run, using actual customer data, on a test server that contains a copy of the production database.
D.Specify that packages must be run on the production database after a full backup of the production database is made.
Correct:C
22.You need to ensure that the performance of the Research_Aggr view is optimized. What should you do?
A.Specify that access to the view must be made only through a stored procedure.
B.Specify that indexes must be created on the views Data1 and Data2 columns.
C.Specify that indexes must be created on the base table columns that contain the data that is used by the calculations in the Data1 and Data2 columns.
D.Specify that indexes that are present on the view must be rebuilt each month, with a fill factor of 50 percent.
Correct:B
23.You need to ensure that the process for identifying when the server hardware is approaching full utilization fulfills the business requirements. What should you do?
A.Use SQL Trace to log the activity on the servers to a log file.
B.Use Performance Logs and Alerts to log the activity on the servers to a log file.
C.Instruct the end users to report when application response time decreases.
D.Check the Windows event log for errors or warnings that are produced due to high utilization.
Correct:B
24.Currently the ComplexCalc column in the Results table is updated by a Transact-SQL trigger that calls the COM component by using the sp_OA* extended stored procedures. You want to migrate this process to a solution that does not use sp_OA* and that requires the minimum amount of development and testing time. What should you do?
A.Write a new extended stored procedure in Microsoft Visual C++ to call the COM component. Write a Transact-SQL trigger that calls this extended stored procedure to update the ComplexCalc column.
B.Rewrite the COM component in a managed code language. Write a managed trigger to call the new managed component to update the ComplexCalc column.
C.Create a stored procedure in a managed code language to call the COM component and insert the data into the ComplexCalc column or update the ComplexCalc column.
D.Create a user-defined function in a managed code language to call the COM component. Write a Transact-SQL trigger to call the new managed function to update the ComplexCalc column.
Correct:D
25.You need to ensure that SSIS packages that are used to import new customer data do not create unacceptable performance. What should you do?
A.While packages are running, modify the thread priority of the SQL Server Agent service to Low.
B.While packages are running, modify the thread priority of the SQL Server service to Low.
C.Specify that packages must be designed to use only a single thread of operation.
D.Schedule packages to run outside of normal office hours.
Correct:D
26.You need to minimize the size of the Notification Services database. What should you do?
A.Specify a one-day retention period for alerts and notifications in the Notification Services database.
B.Specify that the Notification Services database must receive a full backup once each week.
C.For the Notification Services database, specify a recovery model that truncates transaction logs on a regular basis.
D.Design a maintenance plan for the Notification Services database that shrinks the database monthly.
Correct:A
27.You need to create a performance testing plan to ensure that the new application solutions overhead is within allowable limits. What should you do?
A.Measure pagefile utilization before and after installing the new application solution.
B.Measure end-user response times in the existing solution and in the new solution.
C.Measure the response times of key queries in the existing solution and in the new solution.
D.Measure CPU counters and disk performance counters before and after installing the new application solution.
Correct:D
28.You need to ensure that on-demand reports fulfill the business requirements. What should you do?
A.Design the reports to use a matrix.
B.Design the reports to use a list.
C.Design the reports to use a table without a grouping level.
D.Design the reports to use a table with a grouping level.
Correct:A
29.BACKGROUND Company Overview Wide World Importers imports products from around the world. Each product or group of products is imported only to fulfill a customer order. Wide World Importers has a single location in New York City. However, the company works with shipping and handling partners that are located around the world. Planned Changes The company is implementing a new SQL Server 2005-based application to track import products and to facilitate communications with customers and partners. EXISTING ENVIRONMENT Existing Application Environment The existing solution is based on SQL Server 7.0. Existing Supporting Infrastructure The company has a high-speed Internet connection, which is used for all external communications. Wide World Importers uses an Active Directory domain for authentication. The new SQL Server2005 solution will be included in that domain, and all internal users will gain access to the solution by authenticating to the domain. BUSINESS REQUIREMENTS General Application Solution Requirements Customers will place orders for import products by using their own custom applications, which run locally at their locations. These applications must submit product import orders over the Internet and must receive a confirmation without delay. The status of the import products must be tracked. Status includes conditions such as the following: In customs Cleared customs Delivered The current design is implemented as shown in the following display.

Correct:
30.You need to ensure that access by government auditors to auditing information will fulfill all business and technical requirements. What should you do?
A.Replicate auditing information to a stand-alone SQL Server 2005 database server that is on the outside of the company firewall. Grant the auditors select permissions for the replicated audit information.
B.Replicate auditing information to a stand-alone SQL Server 2005 server that is on the inside of the company firewall. Grant the auditors select permission for the replicated audit information.
C.Use SQL Server Authentication for the auditors. Create a database role named GrantAudit that has select permissions for all audit tables. Assign this role to the auditors.Ensure that no other user has select permission for audit tables.
D.Use SQL Server Authentication for the auditors. Grant the auditors select permissions for all audit tables. Deny select permission on all audit tables to public.
Correct:A