70229考试指南及题库分享.docx

70229考试指南及题库分享.docx

《70229考试指南及题库分享.docx》由会员分享，可在线阅读，更多相关《70229考试指南及题库分享.docx（29页珍藏版）》请在冰豆网上搜索。

70229考试指南及题库分享

最新活动：

Testinside考题大师权威Microsoft、Cisco、SUN、CIW、IBM、Nortel、HP、Oracle等各大IT认证题库，最新考题售价仅180元起。

（时间有限！

）

机会不容错过！

需要的朋友一定要抓住这个机会！

下面开始我们的正文！

70-229考试资料

DesigningandImplementingDatabaseswithMicrosoftSQLServer2000,EnterpriseEdition

科目编号：

70-229

科目名称：

DesigningandImplementingDatabaseswithMicrosoftSQLServer2000,EnterpriseEdition

语言（s）：

英语，法语，德语，日语，西班牙语

考生（补）：

IT专业人士

技术：

微软SQLServer2000

类型：

监考考试

相关分类：

MCSE2003

70-229考试是Microsoft公司的DesigningandImplementingDatabaseswithMicrosoftSQLServer2000,EnterpriseEdition认证考试官方代号，DesigningandImplementingDatabaseswithMicrosoftSQLServer2000,EnterpriseEdition认证作为全球IT领域专家Microsoft热门认证之一，是许多大中IT企业选择人才标准的必备条件。

下面是由Testinside考题大师带来的70-229考试资料分享：

70-229：

用MicrosoftSQLServer2000EnterpriseEdition设计和实现数据库

基本情况

及格分数：

704分

考试实践：

两个半小时左右

考试题数：

44道

考试描述

该科主要考核SQLSERVER2000数据库的配置和管理，在我们IT认证考试资源网的辅导老师看来这是MCDBA考试难度中仅次于70-216的科目。

考试难度

考了很多T-SQL的程序设计，要求熟悉，否则容易失手，感觉70-229比70-228难得多，ITExamP的学员学习时要多多注意。

学习建议

在真正的工作中为SQLServer做开发不是一件容易的事，要让SQLServer运行得尽可能高效、快速不算容易，SQLServer开发人员除了要掌握好现有的技术、利用好现有的资源以外，还应该不断观察、了解SQLServer，掌握它的运行状态，在必要的时候找出影响运行效率的瓶颈所在。

经常涉及的考点和题型

T-SQL的程序设计：

考T-SQL语法的试题在70-229的考试中占据了相当多的题量，而且出题的方式有除错、修改、增加、判断使用语法是否正确等的很多种。

对于相关数据库需求的考题：

这种类型的考题会有一段叙述，要注意其文字叙述内容，加以推敲答案就会浮现出来。

XML和SQL结合的程序设计。

70-229考试题库：

Exam:

Microsoft70-229

Title:

MicrosoftDesigningandImplementingDatabaseswithMicrosoftSQLServer2000,EnterpriseEdition

1.Youarethedeveloperofareportingandanalysisdatabaseforasmallinsurancecompany.Youcreateatable

namedDailyPremiumsbyusingthescriptshownintheScriptforDailyPremiumsTableexhibit.（Clickthe<>button.）

Thistablecontainsthedailytotalsoflifeinsuranceandautoinsurancepremiumpayments.

Dataisenteredintothisdatabasefromtwoapplications.Oneapplicationentersthedailytotaloflifeinsurance

premiumsandtheotherentersthedailytotalofautoinsurancepremiums.Somedays,nopremiumsarecollectedfor

lifeinsurancepoliciesorautoinsurancepolicies.Theapplicationthatentersthelifeinsurancepremiumsrepresents

nopremiumswithavalueofzero.Theapplicationthatenterstheautoinsurancepremiumsrepresentsnopremiums

withanullvalue.Anexampleofaweek'sdataisshownintheSampleDataexhibit.（Clickthe<>button.）

Youneedtocreateareportthatdisplaystheaveragepremiumforlifeinsurancepoliciesandautoinsurancepolicies.

Whichscriptshouldyouuse?

A.SELECTAVG（LifePolicy）,AVG（AutoPolicy）FROMDailyPremiums

B.SELECTAVG（NULLIF（LifePolicy,0））,AVG（AutoPolicy）FROMDailyPremiums

C.SELECTAVG（LifePolicy）,AVG（ISNULL（AutoPolicy,0））FROMDailyPremiums

D.SELECTAVG（LifePolicy）,SUM（AutoPolicy）/SUM（CASEWHENAutoPolicyISNULLTHEN0ELSE1END）

FROMDailyPremiums

Answer:

C

2.YouaredesigninganinventoryandshippingdatabaseforContoso,Ltd.Youcreatethelogicaldatabasedesign

shownintheexhibit.（Clickthe<>button.）

Youmustensurethatthereferentialintegrityofthedatabaseismaintained.Whichthreetypesofconstraintsshould

youapplytoyourdesign?

（Eachcorrectanswerpresentspartofthesolution.Choosethree.）

A.CreateaFOREIGNKEYconstraintontheProductstablethatreferencestheOrderDetailstable.

B.CreateaFOREIGNKEYconstraintontheProductstablethatreferencestheSupplierstable.

C.CreateaFOREIGNKEYconstraintontheOrderstablethatreferencestheOrderDetailstable.

D.CreateaFOREIGNKEYconstraintontheOrderDetailstablethatreferencestheOrderstable.

E.CreateaFOREIGNKEYconstraintontheOrderDetailstablethatreferencestheProductstable.

F.CreateaFOREIGNKEYconstraintontheSupplierstablethatreferencestheProductstable.

Answer:

BDE

3.YouaredesigningadatabaseforTailspinToys.Youreviewthedatabasedesign,whichisshownintheexhibit.

（Clickthe<>button.）

Youwanttopromotequickresponsetimesforqueriesandminimizeredundantdata.Whatshouldyoudo?

A.CreateanewtablenamedCustomerContact.AddCustomerID,ContactName,andPhonecolumnstothistable.

B.CreateanewcompositePRIMARYKEYconstraintontheOrderDetailstable.IncludetheOrderID,ProductID,and

CustomerIDcolumnsintheconstraint.

C.RemovethePRIMARYKEYconstraintfromtheOrderDetailstable.UseanIDENTITYcolumntocreateasurrogate

keyfortheOrderDetailstable.

D.RemovetheCustomerIDcolumnfromtheOrderDetailstable.

E.RemovetheQuantitycolumnfromtheOrderDetailstable.AddaQuantitycolumntotheOrderstable.

Answer:

D

4.Youareadatabasedeveloperforaplumbingsupplycompany.YoucreatetwotablesnamedProductsand

Suppliers.Thesetablesareshownintheexhibit.（Clickthe<>button.）

Foreachproduct,youpurchasefromonlyonesupplier.Thesuppliersmightchangeonamonthlybasis.Whena

supplierisnolongerused,youmustremovethesupplierfromthedatabase.Youmustaccomplishthistaskbyusing

theleastamountofadministrativeeffort.Whatshouldyoudo?

A.OntheProductstable,createaFOREIGNKEYconstraint,andspecifytheONDELETECASCADEclause.

B.OntheSupplierstable,createaFOREIGNKEYconstraint,andspecifytheONDELETECASCADEclause.

C.CreateanAFTERDELETEtriggerontheSupplierstable.UsethistriggertodeletetherowsintheProductstable

thatcorrespondtotheSupplierIDcolumn.

D.CreateanINSTEADOFtriggerontheSupplierstable.UsethistriggertodeletetherowsintheProductstablethat

correspondtotheSupplierIDcolumninthedeletedtable.UsethetriggertodeletetherowsintheSupplierstablethat

correspondtotheSupplierIDcolumninthedeletedtable.

E.CreateastoredprocedurethatacceptsSupplierIDasaparameter.Usethisstoredproceduretodeletetherowsin

theProductstablethatcorrespondtotheSupplierIDcolumn.Usethestoredproceduretodeletetherowsinthe

SupplierstablethatcorrespondtotheSupplierIDcolumn.

Answer:

D

5.YouareadatabasedeveloperforLucernePublishing.Thecompany'sexistingHumanResourcesdatabaseisa

dBASE5.0database.YouaremigratingthisdatabasetoaSQLServer2000database.YouarecreatingaData

TransformationServices（DTS）packagetoperformthismigration.ToprepareforimportintotheSQLServerdatabase,

youexportthedBASE5.0tablenamedEmployeeintocomma-delimitedtextfiles.Oneofthetextfilescontainsthe

followingcolumns:

EmpID,Last,First,DeptID,MgrID,anddeleted.Thistextfilewillbeusedtopopulatethe

EmployeetableinSQLServer.Youwillimportonlythoserecordsthathavenotbeenmarkedfordeletion.ouwantto

loadthedataintothetableasquicklyaspossiblewhileminimizingspacerequirements.Howshouldyoucreatethe

DTSpackage?

A.CreateaDataDrivenQuerytasktoimportthedataintotheEmployeetable,andspecifythatthedeletedcolumn

containsazerovalue.

B.Usethebcputilitytocreateaformatfile,andtheneditthefilesothatitcontainsthecolumnsyouwanttoimport.

UseaBulkInserttasktoimportthedataintotheEmployeetable.SpecifytheBatchSizepropertyequalto100.

SpecifytheCHECK_CONSTRAINTSproperty,andallowonlythoserowsthatcontainazerovalueforthedeleted

column.

C.CreateaTransformDatatasktoimportthedataintotheEmployeetable.UseanActiveXscripttoprocessthe

rowsindividually,andacceptonlythoserowsthatcontainazerovalueforthedeletedcolumn.

D.Createatemporarytable.UsetheBulkInserttasktoloadthedatafromthetextfileintothetemporarytable.

UseanExecuteSQLtasktoinsertthedatafromthetemporarytableintotheEmployeetableforthoserowsthat

containazerovalueforthedeletedcolumn.

Answer:

C

6.YouareadatabasedeveloperforanITconsultingcompany.Youaredesigningadatabasetorecordinformation

aboutpotentialconsultants.YoucreateatablenamedCandidateSkillsforthisdatabase.Thetableisshowninthe

exhibit.（Clickthe<>button.）

Howshouldyouuniquelyidentifytheskillsforeachconsultant?

A.CreateaPRIMARYKEYconstraintontheCandidateIDcolumn.

B.CreateaPRIMARYKEYconstraintontheCandidateIDandDateLastUsedcolumns.

C.CreateaPRIMARYKEYconstraintontheCandidateIDandSkillIDcolumns.

D.CreateaPRIMARYKEYconstraintontheCandidateID,SkillID,andDateLastUsedcolumns.

Answer:

C

7.Youareadatabasedeveloperforatechnicaltrainingcenter.Currently,administrativeemployeeskeeprecordsof

students,instructors,courses,andclassroomassignmentsonlyonpaper.Thetrainingcenterwantstoeliminatethe

useofpapertokeeprecordsbydevelopingadatabasetorecordthisinformation.Youdesignthetablesforthis

database.Yourdesignisshownintheexhibit.（Clickthe<>button.）

Youwanttopromotequickresponsetimesforqueriesandminimizeredundantdata.Whatshouldyoudo?

A.CreateanewtablenamedInstructors.IncludeanInstructorIDcolumn,anInstructorNamecolumn,andan

OfficePhonecolumn.AddanInstructorIDcolumntotheCoursestable.

B.MoveallthecolumnsfromtheClassroomtabletotheCoursestable,anddroptheClassroomtable.

C.RemovethePRIMARYKEYconstraintfromtheCoursestable,andreplacethePRIMARYKEYconstraintwitha

compositePRIMARYKEYconstraintbasedontheCourseIDandCourseTitlecolumns.

D.RemovetheClassroomIDcolumn,andbasethePRIMARYKEYconstraintontheClassroomNumberand

ClassTimecolumns.

Answer:

A

8.Youareadatabasedeveloperforacommunitycollege.Youaredesigningadatabasethatwillbeusedforclass

registrations.Youreviewthedatabasedesign,whichisshownintheexhibit.（Clickthe<>button.）

Youwanttonormalizedata,promotequickresponsetimesforqueries,andminimizeredundantdata.Whatsh