Designing and Implementing Databases with SQL Server 1002000 Enterprise Edition [Electronic resources] نسخه متنی

Chapter 12. Answers to Practice Exam One

1. A 2. D 3. B 4. B, E 5. D 6. C 7. B 8. E, F 9. C 10. E 11. D 12. C, D 13. B 14. D 15. A, C, D 16. F 17. A 18. C 19. E 20. D 21. B 22. D 23. B, C 24. C 25. A, C 26. A 27. A 28. C 29. C 30. C 31. B 32. C 33. C 34. B 35. C 36. A 37. D 38. B, C 39. A 40. B, C, D 41. D 42. B 43. A, C 44. A, C, D 45. A, C 46. B 47. D 48. D 49. B 50. CA1:
   Answer: A. By placing joined tables into separate filegroups on different arrays you can allow for the reading of both arrays at the same time during the join operation. For the same reason, when nonclustered indexes are separated from their data, both arrays can be best utilized simultaneously, allowing for sequential access through indexes that don''t interfere with the random accesses of the data. Striped volumes will provide better performance than mirrored volumes.Chapter 3, "Implementing the Physical Database," and Chapter 8, "Designing for Optimized Data Access."

Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Files and Filegroups, Using Files and Filegroups.

Go to A2:
   Answer: D. There are several keys to watch out for in this type of question. The first is the types of systems being distributed to. When you can''t determine the design of the application that is going to use the data, more or less the data system that will store the data, you need a mechanism that provides for the most versatility for generic data distribution. This pretty well eliminates the backup/restore approach, which mandates the same versions and sort order of SQL Serveronly database systems. Replication would require a different solution for each subscriber and would require some level of connectivity between the systemsin this instance, it''s just too difficult a solution. XML, Text, an132 files are all reasonable (or possible) solutions, but XML is preferred because it provides for descriptive data schema within the files themselves.

For more information see Chapter 6, "Programming Business Logic," and Chapter 10, "Completing a Business System Design."

Go to SQL Server Books Online, SQL Server Architecture, SQL Server and XML Support.

Go to A3:
   Answer: B. An integer data type of one form or another would be the correct choice for the type for the order number. The size of integer used would be determined based on the total number of orders maintained in the table over time. By the looks of the volume being discussed, it may even be worth considering an alphanumeric data type in the future. Small and tiny integers would be out of the question because they don''t provide for a size large enough to even hold a week''s worth of data. An IDENTITY column is the best way to implement sequential numbering. It can be automated at the data store and thus guarantee uniqueness. The front-end application solution could easily produce duplicates between two different entry points. A uniqueidentifier is never a good choice for any field value that has the possibility of being placed within a key or an index. The space taken up by this type of field will hamper performance of data inserts and retrievals. A timestamp, though producing the necessary uniqueness, is cumbersome to work with as an order number.Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Transact-SQL Reference, Data Types.

Go to SQL Server Books Online, Using the SQL Server Tools, Using Interface Reference, Visual Database Tools, Developing Database Structure, Working with Keys, Defining Primary Keys.

Go to A4:
   Answer: B, E. The key to this question is that this operation is going to be performed as a one-time thing, so the creation of data objects would likely be avoided and views would not be warranted. However, a script that performs the activity could easily be saved if needed in the future. Table aliases may help in your development, but in this scenario column aliases provide the end user with the necessary data definition.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Data Transformation Services.

Go to SQL Server Books Online, Replication.

Go to SQL Server Books Online, Administering SQL Server, Importing and Exporting Data.

Go to A5:
   Answer: D. Dates are inclusive with the BETWEEN function. Be careful when using comparisons that may rely on the time elements of the data because improperly formulating a condition could exclude some desired data.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Transact SQL Reference, BETWEEN.

Go to SQL Server Books Online, Transact SQL Reference, IN.

Go to A6:
   Answer: C. You are probably not ordering the data to achieve the desired results. Grouping of the resultset doesn''t seem to be warranted because the question is asking for five rows. NULL values should not affect this query, though in some instances NULL data can interfere with the results.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Query Fundamentals, Filtering Rows with WHERE and HAVING, NULL Comparison Search Conditions.

Go to A7:
   Answer: B. With all versions of SQL Server, the interface doesn''t always show newly created objects. A periodic refresh in the Enterprise Manager and the object browser within the Query Analyzer is needed to ensure accuracy of the display.

For more information see Chapter 1, "Database Development on SQL Server 2000."

Go to SQL Server Books Online, SQL Server Architecture, Administration Architecture, Graphical Tools, SQL Query Analyzer.

Go to A8:
   Answer: E, F. Both E and F produce the desired results, though in this instance the IN may be easier to read and use and somewhat more efficient. The exam may ask you to choose the best of several working queries, in which case F would be the best answer.

For more information see Chapter 1, "Database Development on SQL Server 2000."

Go to SQL Server Books Online, SQL Server Architecture, Administration Architecture, Graphical Tools, SQL Query Analyzer.

Go to A9:
   Answer: C. Date data types also include information for time, which causes many comparisons to not provide the desired results. Use the DATEDIFF function to prevent this problem. In this case the current date would be greater than the production date on file, and therefore the first parameter must be PDate.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Transact SQL Reference, DATEDIFF.

Go to SQL Server Books Online, Transact SQL Reference, Data Types.

Go to A10:
   Answer: E. The most efficient and quickest way of performing this task would be to get the data out before you create the default. When the data is placed back without the ID, the default will generate the missing values.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Creating and Modifying a Table, Creating and Modifying Default Definitions.

Go to SQL Server Books Online, Transact SQL Reference, CREATE DEFAULT.

Go to A11:
   Answer: D. Both A and C essentially do the same thing and would provide the desired results if tables were already present. The INTO within the SELECT command is optional, which would allow for either syntax. SELECT INTO is the most appropriate when tables do not already exist.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Designing Data Retrieval and Manipulation, Manipulating Data, Creating INSERT INTO Queries.

Go to SQL Server Books Online, Transact SQL Reference, SELECT INTO.

Go to SQL Server Books Online, Transact SQL Reference, INSERT INTO.

Go to A12:
   Answer: C, D. You don''t want to set unnecessary read-only properties that wouldn''t permit any alterations to the database or the records contained therein.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Modifying a Database, Setting Database Options.

Go to SQL Server Books Online, Transact SQL Reference, System Stored Procedures, sp_dboption.

Go to A13:
   Answer: B. The query needed is a simple SELECT query with a WHERE condition for the site.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Transact SQL Reference, SELECT, SELECT Examples.

Go to A14:
   Answer: D. Option A is the fastest, and the options get slower as you go down the list.Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Using the SQL Server Tools, Command Prompt Utilities, Getting Started with Command Prompt Utilities, bcp Utility.

Go to A15:
   Answer: A, C, D. In choosing UPDATE, you would be selecting an option whose purpose is exactly what you want to avoid. You should be able to increase the data storage size and alter a column name without affecting the internal data. However, decreasing the size for data storage results in data truncation or loss. INSERT, used appropriately, adds data but does not alter any existing values.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Transact-SQL Reference, INSERT.

Go to SQL Server Books Online, Transact-SQL Reference, ALTER TABLE.

Go to A16:
   Answer: F. All the reasons, excluding the agent, are very possibly a cause of the symptoms being described. The SQL Server Agent handles nondata activity on the server related to operators, jobs, and events configured on the system. If the Agent is not running, only these particular processes are interrupted, not the entire database.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Transact-SQL Reference, INSERT.

Go to A17:
   Answer: A. Given the problem scenario, you will need an additional entity to maintain a normalized structure while still allowing for the instructor information to be attached to the course. Option B would cause significant redundant data and would be a poor design choice. Option C would create a far more complex key than is needed and would also cause the length of the key to be rather large. Option D is not necessarily a poor choice but would do nothing to sort out the data design issues.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables.

Go to A18:
   Answer: C. The communication configuration between the two servers has already been set up, so the only necessary element on the coding would be the use of a four-part name within the query. If the desire was to pass the query to the linked server, the OPENQUERY functionality could be utilized.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Administering SQL Server, Managing Servers, Configuring Linked Servers.

Go to A19:
   Answer: E. SCHEMABINDING refers only to SQL Server objectsspecifically tables, views, and user-defined functions. An XML schema cannot be bound in this manner. XML resides in memory and is processed against its own internal set of rules, referred to as a schema. An XML schema interacts directly with the data to supply logic and display attributes on the user''s browser. HTML does not have the required functionality.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, What''s New, XML Integration of Relational Data.

Go to A20:
   Answer: D. Option A is incorrect because the LIKE keyword is not efficient in searching on text-based columns. Option B is wrong for the same reason. Option C is wrong because the syntax for CONTAINS is wrong. Option D is correct because it uses the correct method of searching, using FREETEXT. Where test searching is available, SQL Server will perform queries more efficiently with their use.Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Full-Text Search, Using the FREETEXT Predicate.

Go to A21:
   Answer: B. What you are really looking at with this solution is to allow the client to have a 10-record window of data. The bulk of the data will be maintained on the server, with 10 records being sent to the client when the client requests the information. This is likely to be the best choice for the implementation of the process.

For more information see Chapter 6, "Programming Business Logic."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Cursors, Cursor Implementations.

Go to A22:
   Answer: D. You know exactly how many new records are coming in. You know that 25,000 is 50% of 50,000, so filling the page by 50% and leaving 50% free space for the remaining 50,000 records seems logical. The default FILLFACTOR of 0 doesn''t leave any room for additions; this will slow inserts. If you set the FILLFACTOR too big, searches slow down because any query processed has to cycle through a lot of empty space.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Building SQL Server Applications, SQL-DMO, SQL-DMO Reference, Properties, F, FILLFACTOR.

Go to A23:
   Answer: B, C. The column that you plan to index has to be made up of text data. After you create a full-text index, it is not automatically updated and it resides in a storage space called a full-text catalog.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, SQL Server Architecture, Relational Database Engine Architecture, Full-Text Query Architecture.

Go to A24:
   Answer: C. To gain optimum performance, you want to get as many different physical devices into the fray as possible. By pulling data simultaneously from different drive arrays and controllers, you can improve the speed at which data is read.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Files and Filegroups.

Go to A25:
   Answer: A, C. The WITH SCHEMABINDING argument of the CREATE INDEX statement is needed when creating an indexed view. When WITH SCHEMABINDING is specified, tables participating in the indexed view are prevented from alteration and deletion.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Creating and Maintaining Databases, Views, Creating a View, Creating an Indexed View.

Go to A26:
   Answer: A. You can use the stored procedures sp_who and sp_lock to find the locks that are currently in place and the owners of the processes. Similar information can be received by viewing the lock information in the Current Activity window of the SQL Server Enterprise manager.

For more information see Chapter 7, "Tuning and Optimizing Data Analysis."

Go to SQL Server Books Online, Transact-SQL Reference, Stored Procedures, sp_who.

Go to SQL Server Books Online, Transact-SQL Reference, Stored Procedures, sp_lock.

Go to A27:
   Answer: A. Before executing full-text searches, you must create and populate a full-text catalog. A full-text catalog is the basis of the storage used for the indexes. Periodically these catalogs should be repopulated to ensure usefulness. Repopulation can be done by schedule or by administrative task.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, SQL Server Architecture, Relational Database Engine Architecture, Full-Text Query Architecture.

Go to A28:
   Answer: C. There can be only one clustered index per table and as many as 249 nonclustered indexes. This will become an issue only when you attempt to create a second clustered index or when you add an index to a table that has already been given a primary key with the default settings.

For more information see Chapter 3, "Implementing the Physical Database," and Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Database Development and Visual Database Tools, Database Objects, Indexes.

Go to A29:
   Answer: C. This is a many-to-many relationship scenario, which in SQL Server is implemented using three tables. The center table, often referred to as the connecting or joining table, is on the many side of both of the relationships to the other base table.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Using the SQL Server Tools, User Interface Reference, Visual Database Tools, Database Development, Database Objects, Table Relationships.

Go to A30:
   Answer: C. Option A is not correct because you can''t use COUNT(*) with COMPUTE BY. Option B is not correct because it''s missing the ORDER BY. The fourth choice is totally fictitious.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Summarizing Data, Summarizing Data Using COMPUTE and COMPUTE BY.

Go to A31:
   Answer: B. Option A doesn''t fit the requirements, option C uses if rather than when, and option D is missing an end.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Accessing and Changing Data Fundamentals, Using Multiple Statements, Control-of-Flow, Using CASE.

Go to A32:
   Answer: C. The other close answer is A, but that shows you all the records, and not necessarily the count. Option B is wrong because CNT() is not an aggregate function. COUNT(*) is an aggregate function.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Using Aggregate Functions, Using COUNT(*).

Go to A33:
   Answer: C. Option A would work if you could find someone who wanted to sit and count through the output. Options B and D are invalid syntax: B because you have to name fields in a GROUP BY, and D because the Type field isn''t in the SELECT list.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Grouping Rows with GROUP BY.A34:
   Answer: B. Only option B uses correct syntax. Option A doesn''t have everything in the select list it needs, and doesn''t count anything.

Option C should read COMPUTE COUNT(*) BY PCType and it would work; but it still wouldn''t be as efficient as option B. Option D is just a mess syntactically.

For more information see Chapter 5, "Retrieving and Modifying Data."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Advanced Query Concepts, Summarizing Data, Summarizing Data with ROLLUP.

Go to A35:
   Answer: C. Vertical partitioning is the selection of some (but not all) columns in a table. Horizontal partitioning is the creation of an article based on some (but not all) rows in a table. Horizontal filtering is a term that is sometimes used as a synonym for horizontal partitioning.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Replication, Replication Options, Filtering Published Data.

Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Physical Database Design, Partitioning.

Go to A36:
   Answer: A. The Merge agent connects to the publishing server and the subscribing server and updates both as changes are made. The major role of the Merge agent is to propagate the updates, and then monitor for conflicts. The agent is also responsible for applying the initial snapshot at the subscriber.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Merge Agent.

Go to A37:
   Answer: D. In transactional replication, the Log Reader agent, by default, resides on the Distributor. Because you are using a push subscription, the Distribution agent is also located on the Distributor, by default. If you were to use a pull subscription, the Distribution agent would by default be located on the Subscriber. You can alter the location on which the agent is run by using remote agent activation if the load would be better distributed by not using defaults.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Merge Agent.

Go to A38:
   Answer: B, C. Out of the answer choices provided, only two were actually possible causes: The Model, Master, Msdb, and TempDB databases cannot be replicated, and a replication process must contain at least two agents, depending on the replication options chosen.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Replication, Replication Overview.

Go to SQL Server Books Online, Replication, Replication Data Considerations.

Go to A39:
   Answer: A. The Log Reader agent moves transactions from the transaction log of the published database on the Publisher to the distribution database or server.

For more information see Chapter 4, "Advanced Physical Database Implementation."

Go to SQL Server Books Online, Replication, Administering and Monitoring Replication, Replication Agents, Log Reader Agents.

Go to A40:
   Answer: B, C, D. Because of index fragmentation, the reduction in performance in this database could be improved by rebuilding, re-creating, or defragmenting the current index.

For more information see Chapter 7, "Tuning and Optimizing Analysis," and Chapter 8, "Designing for Optimized Data Access."

Go to SQL Server Books Online, Transact-SQL Reference, DBCC.

Go to A41:
   Answer: D. Procedures 72 and 78 are holding locks against the database and preventing the stored procedure from executing.

For more information see Chapter 7, "Tuning and Optimizing Analysis."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Locking, Displaying Locking Information.

Go to A42:
   Answer: B. The data file is growing at too small an increment, which causes growth to occur in small, too-frequent increments. You may even want to set the growth rate higher than 10%, but of the available choices this is the best solution.

For more information see Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Parts of a Database, Database Files.

Go to SQL Server Books Online, SQL Server Architecture, Database Architecture, Physical Database Architecture, Physical Database Files.

Go to A43:
   Answer: A, C. By definition, timestamp and uniqueidentifier data types are guaranteed to be unique. The timestamp is an automatically entered value. The uniqueidentifier is usually entered using a NEWID() function to generate the uniqueidentifier.

For more information see Chapter 2, "Creating a Logical Data Model," and Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Accessing and Changing Relational Data, Transact-SQL Syntax Elements, Using Data Types.

Go to A44:
   Answer: A, C, D. An Identity provides for uniqueness by incrementing a value continually, and therefore it is a standard choice for a column that requires a unique value. Unique indexes and unique constraints enforce the uniqueness of entered values and do not let any entry come into the system that already exists.

For more information see Chapter 2, "Creating a Logical Data Model," and Chapter 3, "Implementing the Physical Database."

Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables, Autonumbering and Identifier Columns.

Go to A45:
   Answer: A, C. The name fields can be broken down into firstname and lastname and therefore are not in their most decomposed form. This breaks the first normal form rule of normalization. The salesman name should not be stored in this entity because it depends on the SalesmanID and not the CustomerID. This breaks the third normal form rule of normalization.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Creating and Maintaining Databases, Databases, Database Design Considerations, Normalization.A46:
   Answer: B. An automobile''s VIN (Vehicle Identification Number), though unique, is character data and is much too large to use as a primary key. This is a perfect situation for an automatically incremented numeric surrogate key that will take up a lot less storage space.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Creating and Maintaining Databases, Tables, Designing Tables, Using Constraints, Defaults and NULL Values, PRIMARY KEY Constraints.

Go to A47:
   Answer: D. According to byte sizes, int would take considerably less space compared against the current nchar(5) setting. Smallint would even be better, but it has an upper limit of 32,767. Char(5) would cut the space used in half but is not as good as using actual numeric storage. Whenever a variable is going to contain only numbers, numeric storage is always more efficient.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Transact-SQL Reference, Data Types.

Go to A48:
   Answer: D. This is a tricky question to resolve, and if it were not for the space restriction, there would be a temptation to use characters for the storage. At 8 bytes each (double that of int) the easier technique would be to track days from the beginning of recorded time in an integer. requires six digits, and therefore int is the closest to the size required. Datetime allows dates only in the 1700s; smalldatetime, in the 1900s.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Transact-SQL Reference, Data Types.

Go to A49:
   Answer: B. The many-to-many relationship in this scenario occurs because many contractors can work on a single site, and a single contractor can work at many sites. The connection needs to involve both sites and contractors for an appropriate relationship to be drawn.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Optimizing Database Performance, Database Design, Logical Database Design.

Go to A50:
   Answer: C. Float gives accuracy up to 308 decimal places, which is almost 10 times better than can be achieved with any of the other types. Real and decimal data types provide only 38 decimal places of accuracy at, whereas money and smallmoney have accuracy to only the ten-thousandths.

For more information see Chapter 2, "Creating a Logical Data Model."

Go to SQL Server Books Online, Transact-SQL Reference, Data Types.

Go to