Review Questions

1.	Your server has recently shown some instability in input/output operations. You want to make certain your ClinFarm database that resides on the server is configured so that any pages read from disk are validated automatically, with any errors detected written to the Windows Event Log. You need to configure the database options. What should you do? Set the PAGE_VERIFY option to NONE. Set the PAGE_VERIFY option to TORN_PAGE_DETECTION. Set the PARAMETERIZATION option to FORCED. Set the PAGE_VERIFY option to CHECKSUM.
2.	You are validating a new drug serum for a study. You must provide a method to ensure that the value in the drug dosage column is between 25 and 40 mm. You have set the column nullability to not allow nulls. What else should you do? Create a trigger. Add a check constraint to the column. Use a uniqueidentifier data type for the column. Create a CLR user-defined type for the column.
3.	You are designing the ClinFarm database to validate clinical trial study data. You need to standardize the precision and accuracy for measuring the weight of the study subjects for all studies using the database. What should you do? Create a weight alias in the ClinFarm database. Create a CLR user-defined data type in the ClinFarm database. Create a CLR user-defined data type in the master database. Create a weight alias in the master database.
4.	You are designing the ClinFarm database to validate clinical trial study data. Almost all the trial data for a particular study is being collected at Sequel Labs. To make data entry easier, the monitor for the study has asked that you automatically enter Sequel Labs in the study_ location field for the trial data and allow a different lab to be entered if it is otherwise. What should you do? Create a check constraint for Sequel Labs in the study_location field of the trial data. Create a foreign key constraint for Sequel Labs in the study_location field of the trial data. Create an alias for Sequel Labs in the study_location field of the trial data. Create a default definition for Sequel Labs in the study_location field of the trial data.
5.	You are designing the ClinFarm database to validate clinical trial study data. You want to create a text field for patient response to the drug each time tests are scheduled. This field could contain a wide range of characters in any language ranging up to 4,000 bytes. What data type should you use to contain this text? ntext nvarchar(max) varchar(max) varbinary(max)
6.	You are in the course of updating the management of business processes for your company. You are looking for an integrated product that will enable your users to design, develop, deploy, and manage the various business processes. Which of the following will support this type of management? SQL Server Management Studio Visual Studio 2005 BizTalk Server 2006 SQL Server Integration Services
7.	You are designing the ClinFarm database to validate clinical trial study data. You need to gather information regarding data requirements. Which of the following provide useful information for this task? (Choose all that apply.) Users of the ClinFarm database Data managers in charge of the data in the ClinFarm database Legal and regulatory departments of the company Application developers working with the ClinFarm database
8.	You are querying the SequelSales database to validate data stored in the SalesTerritory and SalesPerson tables. You use the following query: SELECT st.Name AS Territory, sp.SalesPersonID FROM Sales.SalesTerritory st LEFT OUTER JOIN Sales.SalesPerson sp ON st.TerritoryID = sp.TerritoryID WHERE sp.TerritoryID IS NULL; What do the results of this query tell you? Every territory that has a salesperson Every salesperson that has no territory Every territory that has no salesperson Every salesperson that has a territory
9.	You are designing the ClinFarm database to validate clinical trial study data. You need to provide a means to uniquely identify each row of the MedDelivery table and automatically create a unique index for each unique column. What should you do? (Choose all that apply.) Create a foreign key constraint on the column in the MedDelivery table. Create a unique constraint on the column in the MedDelivery table. Create a primary key constraint on the column in the MedDelivery table.. Create a uniqueidentifier on the column in the MedDelivery table.
10.	You are querying the SequelSales database to validate data stored in the SalesTerritory and SalesPerson tables. You want to return all the data from both data sets regardless of a match existing between the two sets. Which of the following should you use? UNION UNION ALL RIGHT OUTER JOIN FULL OUTER JOIN
11.	You are designing the ClinFarm database to validate clinical trial study data. You have two tables with identical metadata, TestData1 and TestData2. You must find any data that are common to both the tables. Which of the following should you use? INTERSECT EXCEPT UNION UNION ALL
12.	Which of the following set operators is not symmetrical? INTERSECT EXCEPT UNION UNION ALL
13.	Which of the following gives the most complete verification for data page recovery? Torn page detection Checkdisk Checksum Dirty page detection
14.	Which of the following tools should you use to cleanse disparate data sources? SQL Server Replication Services SQL Server Analysis Services Database Engine Tuning Advisor SQL Server Integration Services
15.	You are designing the ClinFarm database to validate clinical trial study data. Your database uses transactional replications with updating subscribers. What mechanism does SQL Server 2005 use to handle data conflicts for this publisher-subscriber paradigm? SQL Server inserts a timestamp column in each replicated table. SQL Server inserts a uniqueidentifier column in each replicated table. SQL Server inserts an identity column in each replicated table. SQL Server inserts a datetime column in each replicated table.
16.	You are designing the ClinSales database to validate data. Your company is acquiring another pharmaceutical company and soon will be merging its data with yours. The company has given you a copy of its vendor table that you want to merge with your vendors. You notice that some of the vendors are the same as yours but the names are worded slightly differently. How can you best merge the new vendors into your vendor table? You should use a fuzzy lookup transformation in SSIS. You should use a pivot transformation in SSIS. You should use a union all transformation in SSIS. You should use a term extraction transformation in SSIS.
17.	You are designing the ClinFarm database to validate clinical trial study data. Your database uses snapshot replication with immediate updating subscribers. What method does SQL Server 2005 use to handle data conflicts for this publisher-subscriber paradigm? SQL Server uses a conflict resolution policy. None. There is no conflict resolution. SQL Server creates a queue to hold the updates. SQL Server creates a datetime identifier and uses the latest date to resolve conflicts.
18.	You are designing the ClinFarm database to validate clinical trial study data. You need to choose a replication paradigm that uses the multiple master approach with both the publisher and the subscriber creating changes to their databases. Which of the following should you choose? (Choose all that apply.) Snapshot replication Transactional replication with updatable subscriptions Peer-to-peer replication Merge replication
19.	You are designing the ClinFarm database to validate clinical trial study data. The monitor for the HS0663 study has found problems with data entry for the study_lab column and wants you to fill in the column with Kwest Laboratories since this lab is used for most of the data. You decide to use a DEFAULT definition. After you entered the DEFAULT, you notice that the records that had already been entered in the table do not have the DEFAULT value, while the new records do. What did you do wrong? You should have defined the DEFAULT using the schemabinding option. You did nothing incorrect. The DEFAULT definition does not change existing data. You should have defined the DEFAULT using the CHANGE_ALL = ON option. You should have defined the DEFAULT using the WITH EXISTING option.
20.	You are designing the ClinFarm database to validate clinical trial study data. You are designing a new table to contain integer values representing regions of the United States. Each region contains at least one state and some regions contain multiple states. Which of the following is the most efficient data type to use for this column? int smallint tinyint bigint

Answers

1.	D. You should set the PAGE_VERIFY option to CHECKSUM. The CHECKSUM option creates a checksum for a database when it is written to disk, writing it in the header of the database page file. The checksum is recomputed as the page is read from disk and compared to the header value. Unmatched values assume a physical damage in the data with an error being written to the event log. TORN_PAGE_DETECTION ensures only hardware reliability by detecting write errors, and NONE gives no protection. Setting the PARAMETERIZATION option to FORCED converts any literal value appearing in a SELECT, INSERT, UPDATE, or DELETE statement to be converted to a parameter during query compilation.
2.	B. You should create a check constraint comparing the value entered in the dosage column to be between the values of 25 and 40. You do not need to create a trigger or a CLR user-defined type; the constraint has less overhead than either of these solutions. A uniqueidentifier does not meet the requirements.
3.	A. You should create a weight alias in the ClinFarm database using the necessary precision and accuracy. The weight alias can be used in any application using the database. The alias is usable in the database in which it is created, so it is not correct to create the alias in the master database. It is not necessary and too much overhead to create a CLR user-defined type.
4.	D. You should create a default definition for Sequel Labs in the study_location field of the trial data. When a new data row is entered, if no value is placed in the study_location column, the default definition will fill the column. The check constraint, alias, or foreign key will not fill the column as requested.
5.	B. You should use nvarchar(max). Since the data can contain foreign characters, you need a Unicode character set. Because of the size needs, you must use the max specifier. You should not use ntext since it is being deprecated.
6.	C. BizTalk Server 2006 is a business process management server especially designed to automate and optimize business processes though a variety of tools that are familiar to users. Visual Studio 2005 is an integrated development environment tool. SQL Server Management Studio is a business intelligence tool used for database management. SQL Server Integration Services is a data ETL platform.
7.	A, B, C, D. All groups mentioned are stakeholders in the ClinFarm data. Each group has its own set of standards it can bring to the table for your consideration.
8.	C. Your result set will be all the territories that do not have a salesperson assigned. Since the Sales.SalesTerritory table is in the FROM statement of the left outer join, the entire table is returned in the result set. The WHERE clause restricts the output to those territories that have no corresponding salesperson in the SalesPerson table.
9.	B, C. You should create a primary key constraint or a unique constraint on the column in the MedDelivery table. The primary key uniquely identifies each row and automatically creates a unique index. The foreign key constraint and the uniqueidentifier do not uniquely identify a row.
10.	D. The FULL OUTER JOIN returns the results from both data sets regardless of any common data existing between them. The RIGHT OUTER JOIN is not correct because there are data elements in the left table that are not included in the result set. UNION and UNION ALL cannot be used since the data columns in the tables are not compatible.
11.	A. INTERSECT returns the rows of data common to both tables. EXCEPT returns data in the first table but not in the second. UNION and UNION ALL return all the data in the collective tables. To use any of these set operations, the table columns must be compatible.
12.	B. Since EXCEPT returns data in the first data set but not in the second, it is important to state the order of the data sets correctly. Reversing the order gives a different result set. The other operators are symmetrical.
13.	C. Checksum is a redundancy check used to protect data integrity in SQL Server 2005. It is created from an algorithm based on the data themselves as they are written to disk and then compared when read from disk. Torn page detection is mostly a way of detecting page corruptions caused by power failures. Checkdisk is a disk operation, and there is no dirty page detection verification method.
14.	D. SQL Server Integration Services is used for data extraction, transformation, and load. This is the tool that is used for cleansing data from disparate sources. Replication services replicates data without transformations. Analysis Services is used for data analysis. The Database Engine Tuning Advisor enables you to choose an optimal set of indexes and indexed views for your databases.
15.	B. A uniqueidentifier column is added to the table. At the time of synchronization, the queue reader agent compares the values at the publisher and subscriber to see whether there is a change to be processed. If so, the publisher is updated.
16.	A. You should use a fuzzy lookup transformation to merge data against your existing master data. The fuzzy lookup will give you an index of the matches in of the incoming data. You can then decide the index level that you want to accept as a measure of confidence that the data are one and the same.
17.	B. Snapshot replication with immediate updating subscribers uses the Distributed Transaction Coordinator. There is never any concern for data conflicts with this process. Transactions are synchronized between the publisher and subscriber. SQL Server uses a conflict resolution policy with snapshot replication with queued updating using a queue to hold the updates. A datetime column can be used in a custom resolver, such as for merge replication.
18.	C, D. Only peer-to-peer replication and merge replication allow changes to be made at the subscribers. All other replication paradigms that allow updating subscriptions apply the updates to the publisher only.
19.	B. Existing data is not changed when applying a DEFAULT definition to an existing column. You would need to create a query to handle those changes. The other options specified are not valid for DEFAULT definition.
20.	C. Since you will have 50 or fewer regions, tinyint is the most efficient data type to use. It occupies 1 byte of disk space and can contain integer data from 0 to 255. All other choices use more bytes.