|
Home > Archive > microsoft.public.cert.exam.mcsd > June 2002 > Re: 70-229, Problematic Questions, Please help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: 70-229, Problematic Questions, Please help
|
|
|
| Would you like someone to drive to the testing center and take the exam for
you also? By June 11 12 GMT of course.
| |
| Middletree 2002-06-22, 7:15 pm |
| That's pretty rude. Going to a NG for help is not cheating. It's research.
It's a way of learning the material. No difference between getting help
from someone knowledgeable and getting the answers from a book.
"Dave" <none@nowhere.com> wrote in message
news:ubzbb3IECHA.2296@tkmsftngp05...
> Would you like someone to drive to the testing center and take the exam
for
> you also? By June 11 12 GMT of course.
>
>
| |
| Mr Lyng 2002-06-22, 7:15 pm |
| I guess Dave was just born with all this knowledge and never needed any
books or help.
"Middletree" <middletree@htomail.com> wrote in message
news:etHxz#uECHA.2444@tkmsftngp05...
> That's pretty rude. Going to a NG for help is not cheating. It's
research.
> It's a way of learning the material. No difference between getting help
> from someone knowledgeable and getting the answers from a book.
>
> "Dave" <none@nowhere.com> wrote in message
> news:ubzbb3IECHA.2296@tkmsftngp05...
> > Would you like someone to drive to the testing center and take the exam
> for
> > you also? By June 11 12 GMT of course.
> >
> >
>
>
| |
|
| The answer is "C" and "E".
Explanation:
When you execute create database, the SQL 2000 creates the database
from the model database template, which is already 1MB. Then if you
calculate the size of the row in each tables (problem tables) which
comes around another 256 byte (including 50% varchar file size; sum
the table fields).
So:
2000 rows x 256byte = 512,000kb (around .5M)
+ 1MB from system files created from model database template.
Approximately it comes around 1.5 MB. So choose "C".
Make sense?
-Aru
"Michael" <kuzma_prutkov2002@yahoo.com> wrote in message news:<adt5po$5qr$1@news.inter.net.il>...
> Hi All, please provide your comments on the following questions:
>
>
> 10.
>
> You are a database developer for Proseware,Inc. You are creating a
> database named Human Resources for the company.
> This database will contain all employee records and demographics
> information.
> The company has 2,000 employees and
> experiences a yearly turnover rate of about 2 percent.
> When employees leave the company, all of their records must be retained for
> auditing purposes.
> Employee demographics information changes at a yearly rate of about 9
> percent.
> You do not need to maintain a history of demographics changes.
> The scripts that will be used to create the indexes are shown below:
>
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED
> ([EmployeeID])
> WITH FILLFACTOR = 90
> GO
> ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD
> CONSTRAINT [dbo].[pk_EmployeeDemographics] PRIMARY KEY CLUSTERED
> ([EmployeeID])
> WITH FILLFACTOR = 90
> GO
>
> You want to conserve disk space and minimize the number of times that
> expansion of the database files needs to occur. All varchar columns are 50
> percent full.
> Which two parameters should you specify for the CREATE DATABASE statement?
>
> (Each correct answer presents part of the solution.)(Choose two)
>
>
> A. SIZE = 1GB
> B. SIZE = 1MB
> C. SIZE= 2048KB
> D. FILEGROWTH = 20
> E. FILEGROWTH = 5%
> F. FILEGROWTH = 0
>
> Diffuculty: to choose between B and C: 1MB and 2048KB = 2MB.
>
> Look at the calculation:
> If 1MB then each record in the clustered indexes is 1MB : 2000 : 2 ~= 256B,
> where 2 is the number of clustered indexes (tables).
> Since the the ratio between clustered index size and table size is 1.2
> in the average, the average record size is 256 : 1.2 = 213 B brutto
> ~= 205 B netto, since fillfunctor is 90% it leaves us
> with 180 B per record in each of the two tables in the average.
> Seems to be too little.
> So 1MB is too little, 1GB to large, therefore 2048KB is the size.
>
> So I choose C and E, please your comments!!!
>
>
> 19.
> You are the database developer for a brokerage firm. The database
> contains a tabled named Trades. The script that was used to create this
> table is shown below:
> CREATE TABLE Trades
> ( TradeID int IDENTITY(1,1)PRIMAEY KEY NONCLUSTERED NOT NULL,
> TradeDate datetime NULL,
> SettlementDate datetime NULL,
> Units decimal(18, 4) NULL,
> Symbol char(5) NULL,
> ClientID int NULL,
> BrokerID int NULL )
> GO
> CREATE CLUSTERED INDEX c_symbol ON Trades (Symbol)
>
> The Trades table has frequent inserts and updates during the day. Reports
> are run against the table each night.
> You execute the following statement in
> the SQL Query Analyzer:
>
> DBCC SHOWCONTIG (Trades)
> The output for the statement is shown below:
>
> DBCC Statement Output
> DBCC SHOWCONTIG scanning 'Trades' table. . . . . Table:
> 'Trades'(1621580815); index ID:1, database ID:12Table level scan performed.
> -Pages Scanned-----------------------------------------:104
> -Extents Scanned---------------------------------------:16
> -Extent Switches----------------------------------------:24
> -Avg. Pages per Extenbt-------------------------------:6.5
> -Scan Density[Best Count:Actual Count]-----------:52.00%[13:25]
> -Logical Scan Fragmentation-------------------------:7.69%
> -Extent Scan Fragmentation---------------------------:43.75%
> -Avg. Bytes Free per page-----------------------------:460.1
> -Avg. Page Density (full)------------------------------:94.32%
> DBCC execution completed. If DBCC printed error messages, contact your
> system
>
> You want to ensure optional performance for the insert and select operations
> on the Trades table. What should you do?
>
> A. Execute the DBCC DBREINDEX statement on the table.
> B. Execute the UPDATE STATISTICS statement on the table.
> C. Execute the DROP STATISTICS statement on the clustered index.
> D. Execute the DBCC INDEXERRAG statement on the primary key index.
> E. Execute the DROP INDEX and CREATE INDEX statements on the primary key
> index.
>
> Difficulty: to choose between A and D.
>
> Assumption: INDEXERRAG is misprint for INDEXDEFRAG.
> Considerations:
> For A: seem to be a better solution(solves physical fragmentation as well,
> and works for both indexes),
> but is not online and reports are run each night.
> For D:
> 1) The nonclsutered index can hardly be fragmented because it's IDENTITY.
> 2) The statistics in the question only indicates the fragmentation of the
> leaf level of the
> clustered index.
> 3)The operation should better be online.
>
> I chose D. Is that correct?
>
> 45.
> You're conducting a class to help some junior-level developers hone their
> database-development skills. You enter a discussion with them about local
> and global variables, and are trying to clarify the differences between the
> two. Which of the following statements can you always make about these two
> kinds of variables?
> (Choose two.)
>
>
> A. Global variables are really functions.
> B. All values returned by them are specific to the object context.
> C. They use different characters to denote themselves.
> D. All values returned by them are specific to the connection context.
> E. Both of them are available to all connections.
>
> I am sure that A is correct, B and D are not.
> I guess it's E, because it's true.
> It's not C, because even local variables can start with @@, it's just not
> recommended.
> Is that correct?
>
> 72.
> You are evaluating a junior-level developer design for a new database. This
> database was to be designed with an emphasis on query performance. Replying
> to this directive, the developer has sketched out several indexes for the
> tables with the highest expected query load. As you review his design, you
> notice that the new indexes will provide varying degrees of benefit to
> queries. Which of his nonclustered indexes is likely to be the most
> effective?
>
>
> A. An index on gender for the local state 134,000 registered voters.
> B. An index on the sales agent¡?s last initial for a table holding 25,000
> orders.
> C. An index for the StateCode (primary key) column in a US_States table.
> D. An index for the State column in a PacificTime_ZIP_Codes table.
>
> Since no information about types of query is given and therefore it's
> impossible to determine the selectivity of potential queries,
> the only criteria is the density of indexes.
> For A: it's 134000 / 2 = 67000
> For B: it's 25000 / 26 ~= 1000
> For C: it's 1 - unique
> For D: it's a guesswork but somewhere around 100000 / 50 = 2000,
> where 100000 is the max amount of post offices in the US,
> 50 is the amount of states, while the distribution of the offices over the
> territory is not known.
> I would definitely choose C, but some users on forums choose B, because
> 1) primary key is clustered by default - seems irrelevant to me
> 2) The table in A is too small for massive index usage, anyway table scan
> will be used in most cases
> - again seems disputable to me.
> What do you think?
>
>
> 89.
> You have designed the database for a Web site that is used to purchase
> concert tickets.
> During a ticket purchase, a buyer view a list of available tickets,
> decides whether to buy the tickets, and then attempts to purchase the
> tickets.
> This list of available tickets is retrieved in a cursor.
> For popular concerts, thousands of buyers might attempt to purchase tickets
> at the same time.
> Because of the potentially high number of buyers at any one time,
> you must allow the highest possible level of concurrent access to the data.
> How should you design the cursor?
>
>
> A. Create a cursor within an explicit transaction, and set the transaction
> isolation level to REPEATABLE READ.
> B. Create a cursor that uses optimistic concurrency and positioned updates.
> In the cursor,
> place the positioned UPDATE statements within an explicit transaction.
> C. Create a cursor that uses optimistic concurrency. In the cursor, use
> UPDATE statements
> that specify the key value of the row to be updated in the WHERE clause,
> and place the UPDATE statements
> within an implicit transaction.
> D. Create a cursor that uses positioned updates.
> Include the SCROLL_LOCKS argument in the cursor definition to enforce
> pessimistic concurrency.
> In the cursor, place the positioned UPDATE statements within an implicit
> transaction
>
> Can't choose between B and C.
> I guess it's B, because positioned update seems to be the only option for
> this application.
> I don't quite understand what the key value can be here for C?
> How WHERE would look and how the uniqueness will be provided here?
> Is an update that is not positioned feasible at all for this application?
> If yes, what will provide better concurrency: B or C?
> What will be the range of locks in both cases.
>
> Explicit or implicit transaction doesn't seem to matter here.
>
> Is that correct?
>
>
>
> Please refer your comments to kuzma_prutkov2002@yahoo
.com,
> Please reply by June 11, 2002, 12GMT
>
> Regards,
> Mike
|
|
|
|
|