pass4sure Mcse 70-229 v2.83

MCSE Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition : 70-229 Exam

Exam Number/Code: 70-229
Exam Name: MCSE Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition
VUE Code: 70-229
Questions Type: Multiple choice,
Real Exam Question Numbers: 45 Question
Exam Language(s): English

“MCSE Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition”, also known as 70-229 exam, is a Microsoft certification.
Preparing for the 70-229 exam? Searching 70-229 Test Questions, 70-229 Practice Exam, 70-229 Dumps?

With the complete collection of questions and answers, Pass4sure has assembled to take you through 105 questions to your 70-229 Exam preparation. In the 70-229 exam resources, you will cover every field and category in MCSE helping to ready you for your successful Microsoft Certification.
Exam news
Exam 70-229 became available in May 2002.

• This exam is scheduled to retire in March 2009
• This exam is available at 40 percent off retail price until it retires

Audience profile
Candidates for this exam work in a medium-sized to enterprise computing environment that uses Microsoft SQL Server 2000 Enterprise Edition. Candidates have at least one year of experience implementing relational databases in environments with the following characteristics:

• Heterogeneous databases.

• SQL Server security integrated with Windows Authentication.

• Client/server configurations of 50 to 5,000 or more users.

• Web configurations that use Microsoft Internet Information Services (IIS) or COM+.

• Databases as large as 2 TB.

• Multiple installations of SQL Server 2000.

Credit toward certification
When you pass the Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition, you achieve Microsoft Certified Professional status.

• Learn about Microsoft Certified Professional status

You also earn credit toward the following certifications:

• Core credit toward Microsoft Certified Database Administrator on Microsoft SQL Server 2000 certification

• Elective credit toward Microsoft Certified Systems Engineer on Microsoft Windows 2000 certification

• Elective credit toward Microsoft Certified Systems Engineer on Microsoft Windows NT 4.0 certification

• Elective credit toward Microsoft Certified Solution Developer for Microsoft .NET certification

• Elective credit toward Microsoft Certified Solution Developer certification

• Elective credit toward Microsoft Certified Application Developer for Microsoft .NET certification

Database professionals who use Microsoft SQL Server 2005 should consider the Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) credentials. They provide IT professionals with a simpler and more targeted framework to display their technical and on-the-job skills.

• Learn about Microsoft Certified Technology Specialist certification
• Learn about Microsoft Certified IT Professional certification

Preparation tools and resources
To help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the “Skills measured” section.

Classroom training Microsoft E-Learning Microsoft Press books Practice tests
(Prerequisite) Course 2071: Querying Microsoft SQL Server 2000 with Transact-SQL (two days)

Course 2073: Programming a Microsoft SQL Server 2000 Database (five days)
There is no Microsoft E-Learning training currently available.
MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition (ISBN: 9780735619609)
MeasureUp
(Measureup.com)

Self Test Software
(Selftestsoftware.com)

Microsoft online resources
• Learning Plan: Get started with a step-by-step study guide that is based on recommended resources for this exam.

• Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.

• TechNet: Designed for IT professionals, this site includes how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.

• MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.

Skills measured
This certification exam measures your ability to design and implement database solutions by using Microsoft SQL Server 2000 Enterprise Edition. Before taking the exam, you should be proficient in the job skills listed in the following table. The table shows which Official Microsoft Learning Products may help you reach competency in the skills being tested in the exam.

KEY: = The course provides a general introductory overview of this task. You will need to supplement the course with additional work. = The course includes some material to prepare you for this task. You will need to supplement the course with additional work. = The course includes material to prepare you for this task.
Skills measured by Exam 70-229 Course 2071 Course 2073
Developing a Logical Data Model
Define entities. Considerations include entity composition and normalization.

• Specify entity attributes.

• Specify degree of normalization.

Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints.

• Specify attributes that uniquely identify records.

• Specify attributes that reference other entities.

Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability.

• Specify scale and precision of allowable values for each attribute.

• Allow or prohibit NULL for each attribute.

• Specify allowable values for each attribute.

Implementing the Physical Database
Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements.

• Specify space management parameters. Parameters include autoshrink, growth increment, initial size, and maxsize.

• Specify file group and file placement. Considerations include logical and physical file placement.

• Specify transaction log placement. Considerations include bulk load operations and performance.

Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views.

• Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR, foreign keys, nonclustered, primary key, and UNIQUE constraints.

• Specify schema binding and encryption for stored procedures, triggers, user-defined functions, and views.

• Specify recompile settings for stored procedures.

• Specify index characteristics. Characteristics include clustered, FILLFACTOR, nonclustered, and uniqueness.

Alter database objects to support replication and partitioned views.

• Support merge, snapshot, and transactional replication models.

• Design a partitioning strategy.

• Design and create constraints and views.

• Resolve replication conflicts.

Troubleshoot failed object creation.

Retrieving and Modifying Data
Import and export data. Methods include the bulk copy program, the Bulk Insert task, and Data Transformation Services (DTS).

Manipulate heterogeneous data. Methods include linked servers, OPENQUERY, OPENROWSET, and OPENXML.

Retrieve, filter, group, summarize, and modify data by using Transact-SQL.

Manage result sets by using cursors and Transact-SQL. Considerations include locking models and appropriate usage.

Extract data in XML format. Considerations include output format and XML schema structure.

Programming Business Logic
Manage data manipulation by using stored procedures, transactions, triggers, user-defined functions, and views.

• Implement error handling in stored procedures, transactions, triggers, and user-defined functions.

• Pass and return parameters to and from stored procedures and user-defined functions.

• Validate data.

Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views.

• Specify trigger actions.

• Design and manage transactions.

• Manage control of flow.

• Filter data by using stored procedures, triggers, user-defined functions, and views.

Troubleshoot and optimize programming objects. Objects include stored procedures, transactions, triggers, user-defined functions, and views.

Tuning and Optimizing Data Access
Analyze the query execution plan. Considerations include query processor operations and steps.

Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags.

Create and implement indexing strategies. Considerations include clustered index, covering index, indexed views, nonclustered index, placement, and statistics.

Free Pass4sure Practice Engine Demo Download Pass4sure offers free demos for each certification exam, including all IT vendors. You can check out the testing engine software, or pdf file question quality and usability of our practice exams before you decide to buy it. We are the only one site that offers demos for almost all IT certification exams.If you want to try p4s exam practice engine demo. www.pass4sure.org.cn/braindumps/download/p4s/iengine/ www.pass4sure.org.cn/free_testking/download/demo/pdf

Improve index use by using the Index Tuning Wizard.

Monitor and troubleshoot database activity by using SQL Profiler.

Designing a Database Security Plan
Control data access by using stored procedures, triggers, user-defined functions, and views.

• Apply ownership chains.

• Use programming logic and objects. Considerations include implementing row-level security and restricting direct access to tables.

Define object-level security including column-level permissions by using GRANT, REVOKE, and DENY.

Create and manage application roles.

QUESTION 1
You work as a database developer for Certkiller .com. Certkiller .com is an online training provider and its network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Microsoft Windows Server 2000 and the database servers run Microsoft SQL Server 2000. You are designing a database for Certkiller .com. This database base must accommodate the customers who want to make reservations online or by phone to
sit for examinations. To make a reservation, a new customer is required to provide his/her name, telephone number, address and relevant examination information.
This relevant information includes details like certification, preferred dates and times. Once a reservation has been confirmed, the customer will receive a unique customer number, a unique reference number that pertains to the reservation, and
accurate examination information. This accurate information include details such as vendor, examination number, name of certification, data and time of the
reservation.
1. To make another reservation at a later time, the customer must provide his/her customer number.
2. To enquire about an existing reservation or to change it, the customer must provide the reservation’s reference number.
Examination numbers are assigned according to the certification that it contributes toward and are independent from vendor to vendor. Each vendor may offer one or more examinations towards certain certifications. The database should be
normalized to the third normal form. You create the following tables: CREATE TABLE Examinations
( Vendor int, ExamID int,
Certification nvarchar (20)) CREATE TABLE TimeTable
( Vendor int, ExamID int, ExamDate datetime)
Now you need to define the foreign keys for these tables. What should you do?

A. Define a FOREIGN KEY constraint on the Vendor, ExamID and ExamDate columns
in the TimeTable table that references the Examinations table.
B. Define a FOREIGN KEY constraint on the Vendor, ExamID and Certification columns in the Examinations table that references the TimeTable table.
C. Define a FOREIGN KEY constraint on the Vendor and ExamDate columns in the
TimeTable table that references the Examinations table.
D. Define a FOREIGN KEY constraint on the Vendor and ExamDate columns in the
Examinations table that references the TimeTable table. Answer: C

Explanation: Foreign Key constraints are used to enforce referential integrity of data.
Online Transaction Processing (OLTP) systems usually has a large number of relatively narrow interrelated tables. Thus for optimal performance regarding query activity, a database should comply with formal criteria called normal forms.
A First Normal Form for a database is when no table has columns that define similar attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.
The Third Normal Form for a database is when it complies with the second normal form and if, in each table, columns that are not covered by the primary key do not depend on each other.
In this scenario:
1. Each row in the Examinations table represents an examination.
2. Each examination is uniquely identified by the vendor and the examination number
3. Each row in the
TimeTable table represents an individual occurrence of the examination that is registered
in the Examinations table.
4. For each examination occurrence in the TimeTable table, exactly one examination with the same combination of values in the Vendor and ExamID columns must exist in the Examinations table.
Thus the Vendor and ExamID columns constitute a foreign key on the TimeTable table that references the Vendor and the ExamID columns in the Examinations table.
You should use the following statements to define the primary and foreign keys on the
Examinations and TimeTable tables:
ALTER TABLE Examinations ADD PRIMARY KEY (ExamID, Vendor) ALTER TABLE TimeTable ADD
PRIMARY KEY (ExamID, Vendor, ExamDate),
FOREIGN KEY (ExamID, Vendor) REFERENCES Examinations (ExamID, Vendor) Reference:
Microsoft SQL Server 2000 Books Online (2004), Contents: Creating and Maintaining
Databases, “Databases”, “Database Design Considerations,” “Normalization.”
Microsoft SQL Server 2000 Books Online (2004), Contents: Creating and Maintaining
Databases, “Tables”, “Designing Tables,” “Using Constraints, Defaults, and Null Values,
” FOREIGN KEY constraints.”

QUESTION 2
You work as a database developer for Certkiller .com. Certkiller .com is an online training provider and its network consists of a single Active Directory domain named Certkiller .com. All servers on the Certkiller .com network run Microsoft Windows Server 2000 and the database servers run Microsoft SQL Server 2000. You are designing a database for Certkiller .com. This database base must accommodate the customers who want to make reservations online or by phone to
sit for examinations. To make a reservation, a new customer is required to provide his/her name, telephone number, address and relevant examination information.

This relevant information includes details like certification, preferred dates and
times. Once a reservation has been confirmed, the customer will receive a unique customer number, a unique reference number that pertains to the reservation, and accurate examination information. This accurate information include details such as vendor, examination number, name of certification, data and time of the
reservation.
1. To make another reservation at a later time, the customer must provide his/her customer number.
2. To enquire about an existing reservation or to change it, the customer must provide the reservation’s reference number.
The database must be normalized to the third normal form. To this end you need to decide on which tables you should create in the database.
What should you do?

A. Create a Customers table that contains only the columns for the CustomerID, Name, Address and Telephone number.
B. Create a Reservations table that contains only the columns for the CustomerID, Name, Address, Telephone number, reference number, vendor, examination number,
examination date and reservation date.
C. Create an Examinations table that contains only the columns for the vendor, examination number, examination date and time and certification.
D. Create a Reservations table that contains only the columns for the CustomerID, certification, vendor, examination number, examination date and reservation date.

Answer: A

Explanation: Normalization is usually applied to OLTP systems which are usually subject to extensive additions, deletions and modifications of data because in many situations normalization improves database performance.
A First Normal Form for a database is when no table has columns that define similar attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.
The Third Normal Form for a database is when it complies with the second normal form and if, in each table, columns that are not covered by the primary key do not depend on each other.
In this scenario:
Customers constitute a separate entity that is independent of all other relevant entities. – thus you should create a Customers Table and include in it only the columns that specify information pertinent to the individual customer. This information would include details like name, address and telephone number. In order to uniquely identify each customer
and avoid including redundant data, you should include a column for CustomerID in the
Customers table. Incorrect answers:

pass4sure 70-229
Questions and Answers : 105 questions Expected Date: October 22nd , 2008 Price: $129.99 $89.99 Pre-Buy price: $62.99 Save $27
Free download:pass4sure Mcse 70-229 v2.83
Free download:testking Mcse 70-229 v2.83

P4S Free Downloads

Type	Exam Braindumps	New Questions & Answers	Latest Updated	Available link
	All Pass4sure's Exam Pack	858	1 days ago	Download Free Testing Engines