Microsoft Exam #70-229 Designing and Implementing Databases with SQL Server 2000
7 questions. Answers can be found at the end of the exam.
courtesy of Transcender LLC
Questions
1. You are designing a SQL Server 2000 application for online banking. Customers will log on to the bank's Web site by specifying a customer ID and a password. After logging on, a customer should be allowed to view only information about his or her bank accounts. Which of the following methods should you use in the application to provide the required functionality?
a. a view
b. a CHECK constraint
c. a rule
d. a stored procedure
2. You are designing a SQL Server 2000 database for an insurance company. The information about policies will be stored in a table named Policies. The following script has been used to create the Policies table and its indexes. CREATE TABLE Policies (PolicyNo int PRIMARY KEY, CustomerID int REFERENCES Customers (CustomerID), AgentID int REFERENCES Agents (AgentID), StartDate datetime, EndDate datetime, Amount money, Premiums money) CREATE INDEX IX_Cust ON Policies (CustomerID) CREATE INDEX IX_Ag ON Agents (AgentID) Every month, new data will be added to the Policies table from a text file compiled from the policies sold by all agents and independent brokers. The amount of data loaded each month is estimated at approximately 25 to 30 percent of the data in the Policies table. A FOR UPDATE trigger has been defined on the Policies table. You must design the process of loading the new data from the file into the Policies table. The import must be performed as quickly as possible. Which of the following should you do before starting the import process?
a. Specify ON UPDATE CASCADE in the REFERENCES clauses in the Policies table definition.
b. Specify NOCHECK CONSTRAINT ALL in the Policies table definition.
c. Specify DISABLE TRIGGER ALL in the Policies table definition.
d. Drop all indexes that exist on the Policies table.
3. Your company stores its business information in a SQL Server 2000 database. Company policy requires that no individual users, except database administrators, be granted access to any database objects. All of your company's employees belong to Windows 2000 groups whose names correspond to department names. You have created a stored procedure to generate reports on weekly sales, and you have granted the EXECUTE permission for the procedure to the Sales group. John has recently been transferred to the Sales department from the warehouse and added to the Sales group. The Warehouse group has been denied access to the procedure. Although all Sales employees can successfully use the procedure, John reports that he is unable to use the procedure. You must enable John to create weekly sales reports by using the procedure. Which of the following should you do?
a. Remove John's individual Windows user account from the Warehouse group.
b. Revoke permissions for the procedure from John's individual Windows user account.
c. Grant the EXECUTE permission for the procedure to John's individual Windows user account.
d. Grant the SELECT permissions for all database objects referenced by the procedure to John's individual Windows user account.
4. Your company sells three types of merchandise: computer equipment, office equipment and telephone equipment. All sales transactions are recorded in a SQL Server 2000 database. Each sales representative deals with only one type of merchandise. Therefore, the products that are listed on a single invoice always belong to only one category. The Invoices table includes three columns, Amount1, Amount2 and Amount3, which correspond to the types of merchandise that your company sells. Depending upon the type of merchandise sold, one of these columns will store the total on an invoice. Only one of these columns contains a non-null value for any given invoice; the two other columns contain null. You must create a report that presents the total amount of sales on each invoice. Which of the following queries should you use?
a. SELECT InvoiceNo, 'Total' = COALESCE (Amount1, Amount2, Amount3) FROM Invoices
b. SELECT InvoiceNo, 'Total' = SUM (Amount1, Amount2, Amount3) FROM Invoices
c. SELECT InvoiceNo, 'Total' = SUM (COALESCE (Amount1, Amount2, Amount3)) FROM Invoices
d. SELECT InvoiceNo, 'Total' = SUM (ISNULL (Amount1, 0), ISNULL (Amount2, 0), ISNULL (Amount3, 0)) FROM Invoices
5. You want to insert rows in several tables of a SQL Server 2000 database. In Query Analyzer, you issue several INSERT statements. Then, you issue SELECT statements from Query Analyzer to query the tables and verify that the data has been entered. The next day, you query the same tables again and notice that the rows that you entered the previous day do not appear in the tables. Which of the following is the most likely reason that the data was not entered?
a. The Set rowcount option was set to zero for the connection.
b. The Set implicit_transactions option was enabled for the connection.
c. The Set parseonly option was enabled for the connection.
d. The Set nocount option was enabled for the connection.
6. You are a SQL Server 2000 database developer for your company. You are developing a custom application that relies mostly on stored procedures. Before the application has been implemented in the production environment, you want to test it in a lab in order to determine whether the stored procedures' performance will be adequate. Which of the following should you do?
a. Enable the auto create statistics and auto update statistics database options.
b. Use the Index Tuning Wizard.
c. Create composite nonclustered indexes on the columns that are frequently referenced by the procedures.
d. Set the transaction isolation level to REPEATABLE READ.
7. You are designing a SQL Server 2000 application for online banking. Customers will log on to the bank's Web site by specifying a customer ID and a password. After logging on, a customer should be allowed to view only information about his or her bank accounts. Which of the following methods should you use in the application to provide the required functionality?
a. a view
b. a CHECK constraint
c. a rule
d. a stored procedure
Answers
1) Answer: d. After a customer has provided his or her ID number, that ID should be used as an input parameter for a stored procedure. The procedure should issue a query to identify the account numbers for the current customer and then issue another query to return the customer's bank account information. Alternatively, you can use a table-valued function instead of a stored procedure. However, it is not necessary to reference the returned result set in other queries; therefore, the functionality provided by a stored procedure is sufficient in this scenario. You cannot use a view instead of a stored procedure because views do not accept parameters. In this scenario, a current user's ID must be used as a parameter in order to return only information about the user's bank accounts. CHECK constraints are used to restrict values that can be entered in one or more columns in a table. Rules are a backward-compatibility feature similar to CHECK constraints. Neither rules nor CHECK constraints can be used to return customers' bank account information. Reference: SQLSBO, Contents, "Creating and Maintaining Databases," "Stored Procedures," "Creating a Stored Procedure (entire section)." SQLSBO, Contents, "Creating and Maintaining Databases," "Views," "Creating a View." SQLSBO, Contents, "SQL Server Architecture," "Database Architecture," "Logical Database Components," "Constraints, Rules, Defaults, and Triggers," "Constraints." SQLSBO, Contents, "SQL Server Architecture," "Database Architecture," "Logical Database Components," "Constraints, Rules, Defaults, and Triggers," "Rules."
2) Answer: d. To import a large amount of data as quickly as possible, you should use a BULK INSERT statement, which provides the fastest method of bulk-load operations. If a clustered index and one or two nonclustered indexes exist on the target table and the amount of data to be added is 25 percent or more of the original amount of data in the table, then it is recommended that all existing indexes on the table be dropped and then recreated after the bulk-loading completes. Doing so provides better performance than bulk-loading data with existing indexes. The ON UPDATE CASCADE option can be specified in the REFERENCES clause of a foreign key definition; doing so ensures that when the referenced table is updated, changes to the referenced column or columns are propagated to the foreign key column or columns. In this scenario, new information will be added to the referencing table, not to the referenced ones, and no tables will be updated. Therefore, specifying ON UPDATE CASCADE is irrelevant to this scenario. NOCHECK CONSTRAINT ALL in a table definition disables all FOREIGN KEY and CHECK constraints in the table. DISABLE TRIGGER ALL in a table definition disables all triggers on the table. You do not need to specify the cascading action for the FOREIGN KEY constraints, disable the constraints or disable the triggers because, by default, constraints are not checked and triggers do not fire during a bulk-load operation. Additionally, the bulk-load process is functionally equivalent to INSERT statements, which would not fire FOR UPDATE triggers. Reference: SQLSBO, Contents, "Optimizing Database Performance," "Optimizing Utility and Tool Performance," "Optimizing Bulk Copy Performance." SQLSBO, Search, "ALTER TABLE."
3) Answer: a. When multiple permissions for the same object are assigned to a user through the user's membership in different groups, the most restrictive permissions become the user's effective permissions. Specifically, denied permissions override granted permissions. The EXECUTE permission is the only permission that is applicable to stored procedures. As a member of the Warehouse group, John is denied the EXECUTE permission for the procedure, and as a member of the Sales group, he is granted the same permission. The denied permission overrides the granted permission, and, therefore, John is unable to call the procedure. To resolve the permission conflict, you should remove John's Windows user account from the Warehouse group, which is denied the EXECUTE permission for the procedure. If you granted the EXECUTE permission for the procedure to John's individual Windows user account, then he would still be unable to run the procedure because the granted permission would be overridden by the permission denied to the Warehouse group. Revoking a permission for the procedure from John's individual user account would be ineffective because no permission has been assigned for the procedure to John's individual account. When a procedure and all objects referenced in its definition belong to the same owner, a user requires only the EXECUTE permission to run the procedure. If a procedure performs an update against a table that is owned by the creator of the procedure, then the EXECUTE permission granted to a user for the procedure will automatically allow the user to update the table. In this scenario, all Sales employees, except John, can successfully use the procedure that you created. Because no individual Windows user accounts are granted access to any database objects, you can resolve any permission conflicts at the group level. John should be assigned to a group that has the EXECUTE permission for the procedure, and he should not be assigned any permissions for the referenced objects at the individual level. Reference: SQLSBO, Contents, "Administering SQL Server," "Managing Security," "Managing Permissions (entire section)." SQLSBO, Search, "GRANT." SQLSBO, Search, "DENY." SQLSBO, Search, "REVOKE."
4) Answer: a. You can use the COALESCE function to return the first non-null value from the list of its arguments. Because in this scenario only one of the columns, Amount1, Amount2 or Amount3, contains a non-null value, the non-null value will be returned by the COALESCE function. The queries in choices b, c and d are all invalid because the SELECT list includes a column, InvoiceNo, that is not included in the SUM function, but there is no GROUP BY clause in those queries. Additionally, the SUM function can have only one argument, whereas, three arguments are specified in the queries in choices b and d. The ISNULL function evaluates to its first argument if it is not null and evaluates to the second argument if the first argument is null. You can use this function to produce the required report by using the following query: SELECT InvoiceNo, 'Total' = ISNULL (Amount1, 0) + ISNULL (Amount2, 0) + ISNULL (Amount3, 0) FROM Invoices You should not confuse the ISNULL function with the IS NULL operator, which returns either true or false depending on the value of the logical expression preceding the operator. Reference: SQLSBO, Search, "COALESCE." SQLSBO, Contents, "Accessing and Changing Relational Data," "Advanced Query Concepts," "Using Aggregate Functions in the Select List." SQLSBO, Search, "SUM." SQLSBO, Search, "ISNULL." SQLSBO, Search, "IS [NOT] NULL."
5) Answer: b. By default, all connections to SQL Server are configured to use autocommit mode in which each T-SQL statement automatically starts a new transaction. A transaction is automatically committed or rolled back upon completion of each statement. A user or an application can explicitly start and complete transactions by issuing statements such as BEGIN TRANSACTION, COMMIT and ROLLBACK. A user or an application can set the current connection to implicit transaction mode by issuing the SET IMPLICIT_TRANSACTIONS ON statement. In implicit transaction mode, the statement that follows a completed transaction automatically starts a new transaction. To complete a transaction, a COMMIT or ROLLBACK statement must be explicitly specified. Otherwise, all statements issued within the connection after switching to implicit transaction mode or after the most recent COMMIT or ROLLBACK statement are executed as a single transaction. If the connection is terminated without issuing COMMIT, then the outstanding transaction is automatically rolled back and all the changes made by the uncommitted statements in the transaction are lost. Query Analyzer prompts you to commit the outstanding transaction when a user attempts to terminate a connection that is set to implicit transaction mode. In this scenario, it may have happened that you inadvertently switched the connection to implicit transaction mode before you issued the INSERT statements. For example, you could have run an SQL script contained in a text file, and the script could have set implicit transaction mode without your noticing it. The SELECT statements that you issued after the INSERT statements were, therefore, executed as part of the same transaction and returned the result set that included the data that has not been committed yet. When you were terminating the connection, you may have overlooked or misinterpreted the message prompting you to commit the outstanding transaction. In Query Analyzer, you can set options for the current connections by clicking Current Connection Properties on the Query menu and then by specifying the appropriate options in the Current Connection Properties dialog box. The Set rowcount option, if set to a non-zero value, causes each query in the connection to stop after returning the specified number of rows. By default, the row count is set to zero, which causes all rows to be returned. The Set parseonly option is disabled by default; if enabled, it causes SQL Server to verify the syntax of each statement without compiling or executing that statement. Had this option been set, the SELECT statements following the INSERT statements would not have returned any data from the queried tables. The Set nocount option is disabled by default; if enabled, it causes SQL Server not to return a message that indicates the number of rows affected by each statement. This option, whether enabled or disabled, cannot prevent any statements from being executed. Reference: SQLSBO, Contents, "Accessing and Changing Relational Data," "Transactions." SQLSBO, Contents, "Accessing and Changing Relational Data," "Transactions," "Controlling Transactions." SQLSBO, Contents, "Accessing and Changing Relational Data," "Transactions," "Explicit Transactions." SQLSBO, Contents, "Accessing and Changing Relational Data," "Transactions," "Autocommit Transactions." SQLSBO, Contents, "Accessing and Changing Relational Data," "Transactions," "Implicit Transactions." SQLSBO, Contents, "Using the SQL Server Tools," "User Interface Reference," "SQL Query Analyzer Help," "SQL Query Analyzer Dialog Boxes and Windows," "Current Connection Properties Dialog box."
6) Answer: b. Among the available choices, only using the Index Tuning Wizard provides a mechanism that allows analysis of the behavior of stored procedures. You should simulate the production environment in a test lab and create a sample workload file or trace for the Index Tuning Wizard, which will analyze the queries used in the workload against a specified database and generate recommendations about which indexes in the database should be created or dropped. It will also generate a report that shows the percentage of queries in which each of the existing indexes has been used. You can use this information to create the appropriate additional indexes that will optimize the performance of the stored procedures. All other choices in this scenario suggest steps that typically may be taken after the relevant monitoring has been performed. Reference: SQLSBO, Contents, "Creating and Maintaining Databases," "Indexes," "Designing an Index," "Index Tuning Wizard." SQLSBO, Contents, "SQL Server Architecture," "Database Architecture," "Logical Database Components," "SQL Indexes," "Table Indexes."
7) Answer: d. After a customer has provided his or her ID number, that ID should be used as an input parameter for a stored procedure. The procedure should issue a query to identify the account numbers for the current customer and then issue another query to return the customer's bank account information. Alternatively, you can use a table-valued function instead of a stored procedure. However, it is not necessary to reference the returned result set in other queries; therefore, the functionality provided by a stored procedure is sufficient in this scenario. You cannot use a view instead of a stored procedure because views do not accept parameters. In this scenario, a current user's ID must be used as a parameter in order to return only information about the user's bank accounts. CHECK constraints are used to restrict values that can be entered in one or more columns in a table. Rules are a backward-compatibility feature similar to CHECK constraints. Neither rules nor CHECK constraints can be used to return customers' bank account information. Reference: SQLSBO, Contents, "Creating and Maintaining Databases," "Stored Procedures," "Creating a Stored Procedure (entire section)." SQLSBO, Contents, "Creating and Maintaining Databases," "Views," "Creating a View." SQLSBO, Contents, "SQL Server Architecture," "Database Architecture," "Logical Database Components," "Constraints, Rules, Defaults, and Triggers," "Constraints." SQLSBO, Contents, "SQL Server Architecture," "Database Architecture," "Logical Database Components," "Constraints, Rules, Defaults, and Triggers," "Rules."
These questions and answers are provided by Transcender LLC. Order the full version of this exam simulation online at www.transcender.com, phone 615-726-8779, 8 a.m. - 6 p.m., (CST), M - F, fax 615-726-8884, or mail to Transcender LLC, 565 Marriott Drive, Suite 300, Nashville, TN 37214.
More Pop Quiz:
|
