Microsoft 70-229 Exam - OKExams.com

Free 70-229 Sample Questions:

1. You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, courses, and classroom assignments only on paper. The training center wants to eliminate the use of paper to keep records by developing a database to record this information. You design the tables for this database. Your design is shown in the exhibit.

You want to promote quick response times for queries and minimize redundant data. What should you do?
A. Create a new table named Instructors.
Include an InstructorID column, an InstructorName column, and an OfficePhone column.
Add an InstructorID column to the Courses table.
B. Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.
C. Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle columns.
D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.
Answer: A

2. You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit.

You want to promote quick response times for queries and minimize redundant data. What should you do?
A. Create a new table named CustomerContact.
Add CustomerID, ContactName, and Phone columns to this table.
B. Create a new composite PRIMARY KEY constraint on the OrderDetails table.
Include the OrderID, ProductID, and CustomerID columns in the constraint.
C. Remove the PRIMARY KEY constraint from the OrderDetails table.
Use an IDENTITY column to create a surrogate key for the OrderDetails table.
D. Remove the CustomerID column from the OrderDetails table.
E. Remove the Quantity column from the OrderDetails table.
Add a Quantity column to the Orders table.
Answer: D

3. You are designing a database that will contain customer orders. Customers will be able to order multiple products each time they place an order. You review the database design, which is shown in the exhibit.

You want to promote quick response times for queries and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Create a new table named OrdersDetail.
Add OrderID, ProductID, and Quantity columns to this table.
B. Create a composite PRIMARY KEY constraint on the OrderID and ProductID columns of the Orders table.
C. Remove the ProductID and Quantity columns from the Orders table.
D. Create a UNIQUE constraint on the OrderID column of the Orders table.
E. Move the UnitPrice column from the Products table to the Orders table.
Answer: AC

4. You are a database developer for an IT consulting company. You are designing a database to record information about potential consultants. You create a table named CandidateSkills for this database. The table is shown in the exhibit.

How should you uniquely identify the skills for each consultant?
A. Create a PRIMARY KEY constraint on the CandidateID column.
B. Create a PRIMARY KEY constraint on the CandidateID and DateLastUsed columns.
C. Create a PRIMARY KEY constraint on the CandidateID and SkillID columns.
D. Create a PRIMARY KEY constraint on the CandidateID, SkillID, and DateLastUsed columns.
Answer: C

5. You are designing an inventory and shipping database for Contoso, Ltd. You create the logical database design shown in the exhibit.

You must ensure that the referential integrity of the database is maintained. Which three types of constraints should you apply to your design? (Each correct answer presents part of the solution. Choose three.)
A. Create a FOREIGN KEY constraint on the Products table that references the Order Details table.
B. Create a FOREIGN KEY constraint on the Products table that references the Suppliers table.
C. Create a FOREIGN KEY constraint on the Orders table that references the Order Details table.
D. Create a FOREIGN KEY constraint on the Order Details table that references the Orders table.
E. Create a FOREIGN KEY constraint on the Order Details table that references the Products table.
F. Create a FOREIGN KEY constraint on the Suppliers table that references the Products table.
Answer: BDE

6. You are a database developer for Wide World Importers. You are creating a database that will store order information. Orders will be entered in a client/server application. The database must support this client/server application. Each time a new order is entered, a unique order number must be assigned. Order numbers must be assigned in ascending order. An average of 10,000 orders will be entered each day.
You create a new table named Orders and add an OrderNumber column to this table. What should you do next?
A. Set the data type of the column to uniqueidentifier.
B. Set the data type of the column to int, and set the IDENTITY property for the column.
C. Set the data type of the column to int.
Create a user-defined function that selects the maximum order number in the table.
D. Set the data type of the column to int.
Create a NextKey table, and add a NextOrder column to the table.
Set the data type of the NextOrder column to int.
Create a stored procedure to retrieve and update the value held in the NextKey table.
Answer: B

7. You are a database developer for Proseware, Inc. You are implementing a database for the company's human resources department. This database will store employee information. You create a table named EmployeeContact that contains the following columns: HomePhone, BusinessPhone, FaxNumber, and E-mailAddress.
You must ensure that each record contains a value for either the HomePhone column or the BusinessPhone column. What should you do?
A. Create a rule that disallows null values.
Bind the rule to both the HomePhone and BusinessPhone columns.
B. Create a rule that prevents null values from being entered into both the HomePhone and BusinessPhone columns.
Bind the rule to the table.
C. Add CHECK constraints on the HomePhone and BusinessPhone columns that prevent null values from being entered into the columns.
D. Add a CHECK constraint on the table to validate that at least one of the values entered into the HomePhone and BusinessPhone columns is non-null.
E. Create a trigger that counts the number of items entered without a value in the HomePhone column and then counts the number of items entered without a value in the BusinessPhone column.
Configure the trigger so that if one or more rows are found that meet these conditions, the trigger will cancel the data modification.
Answer: D

8. You are a database developer for an insurance company. You create a table named Insured, which will contain information about persons covered by insurance policies. You use the script shown in the exhibit to create this table.

A person covered by an insurance policy is uniquely identified by his or her name and birth date. An insurance policy can cover more than one person. A person cannot be covered more than once by the same insurance policy.
You must ensure that the database correctly enforces the relationship between insurance policies and the persons covered by insurance policies. What should you do?
A. Add the PolicyID, InsuredName, and InsuredBirthDate columns to the primary key.
B. Add a UNIQUE constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.
C. Add a CHECK constraint to enforce the uniqueness of the combination of the PolicyID, InsuredName, and InsuredBirthDate columns.
D. Create a clustered index on the PolicyID, InsuredName, and InsuredBirthDate columns.
Answer: B

9. 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 schema for the Human Resources database is shown in the Human Resources Schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts exhibit.


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
Answer: BE

10. You are a database developer for a telemarketing company. You are designing a database named CustomerContacts. This database will be updated frequently. The database will be about 1 GB in size. You want to achieve the best possible performance for the database. You have 5 GB of free space on drive C.
Which script should you use to create the database?
A. CREATE DATABASE CustomerContacts
ON
( NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 10,
MAXSIZE = 1GB,
FILEGROWTH = 5 )
B. CREATE DATABASE CustomerContacts
ON
( NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 10,
MAXSIZE = 1GB,
FILEGROWTH = 10% )
C. CREATE DATABASE CustomerContacts
ON
( NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 100,
MAXSIZE = UNLIMITED )
D. CREATE DATABASE CustomerContacts
ON
( NAME = Contacts_dat,
FILENAME = 'c:\data\contacts.mdf',
SIZE = 1GB )
Answer: D

11. You are a database developer for Wide World Importers. You are creating a table named Orders for the company's SQL Server 2000 database. Each order contains an order ID, an order date, a customer ID, a shipper ID, and a ship date.
Customer service representatives who take the orders must enter the order date, customer ID, and shipper ID when the order is taken. The order ID must be generated automatically by the database and must be unique. Orders can be taken from existing customers only. Shippers can be selected only from an existing set of shippers. After the customer service representatives complete the order, the order is sent to the shipping department for final processing. The shipping department enters the ship date when the order is shipped.
Which script should you use to create the Orders table?
A. CREATE TABLE Orders
(
OrderID uniqueidentifier PRIMARY KEY NOT NULL,
OrderDate datetime NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customers (CustomerID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers (ShipperID),
ShipDate datetime NULL
)
B. CREATE TABLE Orders
(
OrderID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
OrderDate datetime NOT NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customers (CustomerID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers (ShipperID),
ShipDate datetime NULL
)
C. CREATE TABLE Orders
(
OrderID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
OrderDate datetime NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customers (CustomerID),
ShipperID int NULL,
ShipDate datetime NULL
)
D. CREATE TABLE Orders
(
OrderID uniqueidentifier PRIMARY KEY NOT NULL,
OrderDate datetime NOT NULL,
CustomerID char(5) NULL FOREIGN KEY REFERENCES Customers (CustomerID),
ShipperID int NULL FOREIGN KEY REFERENCES Shippers (ShipperID),
ShipDate datetime NULL
)
Answer: B

12. You are a database developer for Lucerne Publishing. The company stores its sales data in a SQL Server 2000 database. This database contains a table named Orders. There is currently a clustered index on the table, which is generated by using a customer's name and the current date.
The Orders table currently contains 750,000 rows, and the number of rows increases by 5 percent each week. The company plans to launch a promotion next week that will increase the volume of inserts to the Orders table by 50 percent.
You want to optimize inserts to the Orders table during the promotion. What should you do?
A. Create a job that rebuilds the clustered index each night by using the default FILLFACTOR.
B. Add additional indexes to the Orders table.
C. Partition the Orders table vertically.
D. Rebuild the clustered index with a FILLFACTOR of 50.
E. Execute the UPDATE STATISTICS statement on the Orders table.
Answer: D

13. You are a database developer for an automobile dealership. The company stores its automobile inventory data in a SQL Server 2000 database. Many of the critical queries in the database join three tables named Make, Model, and Manufacturer. These tables are updated infrequently.
You want to improve the response time of the critical queries. What should you do?
A. Create an indexed view on the tables.
B. Create a stored procedure that returns data from the tables.
C. Create a scalar user-defined function that returns data from the tables.
D. Create a table-valued user-defined function that returns data from the tables.
Answer: A

14. You are a database developer for Trey Research. You are designing a SQL Server 2000 database that will be distributed with an application to numerous companies. You create several stored procedures in the database that contain confidential information. You want to prevent the companies from viewing this confidential information.
What should you do?
A. Remove the text of the stored procedures from the syscomments system table.
B. Encrypt the text of the stored procedures.
C. Deny SELECT permissions on the syscomments system table to the public role.
D. Deny SELECT permissions on the sysobjects system table to the public role.
Answer: B

15. You are a database developer for your company's SQL Server 2000 database. You use the following script to create a view named Employee in the database:
CREATE VIEW Employee AS
SELECT P.SSN, P.LastName, P.FirstName, P.Address, P.City, P.State, P.Birthdate, E.EmployeeID, E.Department, E.Salary
FROM Person AS P JOIN Employees AS E ON (P.SSN = E.SSN)
The view will be used by an application that inserts records in both the Person and Employees tables. The script that was used to create these tables is shown in the exhibit.

You want to enable the application to issue INSERT statements against the view. What should you do?
A. Create an AFTER trigger on the view.
B. Create an INSTEAD OF trigger on the view.
C. Create an INSTEAD OF trigger on the Person and Employees tables.
D. Alter the view to include the WITH CHECK option.
E. Alter the view to include the WITH SCHEMABINDING option.
Answer: B

16. You are a database developer for your company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The Sales table contains sales information for all departments in the company. Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department.
You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table.
What should you do?
A. Create a new table, and move the columns that are most frequently queried to this table.
Retain the DepartmentID column in both tables.
Create a view on the original table and on the new table.
Add a FOREIGN KEY constraint on the join columns of the new table.
B. Create a new table, and move the columns that are most frequently queried to this table.
Retain the DepartmentID column in both tables.
Create a view on the original table and on the new table.
Add a CHECK constraint on the DepartmentID column in both tables.
C. Create one new table for each department, and move the sales information for each department to that department's table.
Add a CHECK constraint on the DepartmentID column of the new tables.
Create a view on the new tables.
D. Create one new table for each department, and move the sales information for each department to that department's table.
Create a view on the new tables.
Add a CHECK constraint on the DepartmentID column in the view.
E. Create a stored procedure that accepts an input parameter for the department.
Use the stored procedure to return data from the Sales table.
Answer: C

17. You are a database developer for a hospital. You are designing a SQL Server 2000 database that will contain physician and patient information. This database will contain a table named Physicians and a table named Patients.
Physicians treat multiple patients. Patients have a primary physician and usually have a secondary physician. The primary physician must be identified as the primary physician. The Patients table will contain no more than 2 million rows.
You want to increase I/O performance when data is selected from the tables. The database should be normalized to the third normal form.
Which script should you use to create the tables?
A. CREATE TABLE Physicians
(
PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL
)
GO
CREATE TABLE Patients
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NOT NULL,
CONSTRAINT FK_Patients_Physicians1 FOREIGN KEY (PrimaryPhysician) REFERENCES Physicians (PhysicianID),
CONSTRAINT FK_Patients_Physicians2 FOREIGN KEY (SecondaryPhysician) REFERENCES Physicians (PhysicianID)
)
B. CREATE TABLE Patients
(
PatientID smallint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NULL
)
GO
CREATE TABLE Physicians
(
PhysicianID smallint NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
CONSTRAINT FK_Physicians_Patients FOREIGN KEY (PhysicianID) REFERENCES Patients (PatientID)
)
C. CREATE TABLE Patients
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL
)
GO
CREATE TABLE PatientPhysician
(
PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysician PRIMARY KEY CLUSTERED,
PhysicianID int NOT NULL,
PatientID bigint NOT NULL,
PrimaryPhysician bit NOT NULL,
FOREIGN KEY (PhysicianID) REFERENCES Physicians (PhysicianID),
FOREIGN KEY (PatientID) REFERENCES Patients (PatientID)
)
D. CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL PRIMARY KEY,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL
)
GO
CREATE TABLE PatientPhysician
(
PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID),
PatientID int NOT NULL REFERENCES Patients (PatientID),
PrimaryPhysician bit NOT NULL,
CONSTRAINT PK_PatientPhysician PRIMARY KEY (PhysicianID, PatientID)
)
Answer: D

18. You are a database developer for your company's SQL Server 2000 database. This database contains a table named Products and a table named Companies. You want to insert new product information from a linked server into the Products table. The Products table has a FOREIGN KEY constraint that references the Companies table. An UPDATE trigger is defined on the Products table.
You want to load the data as quickly as possible. What should you do?
A. Use the ALTER TABLE statement and the ON UPDATE clause to modify the Products table.
B. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Products table.
C. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Companies table.
D. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Companies table.
E. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.
Answer: E

19. You are a database developer for an insurance company. The company has one main office and 18 regional offices. Each office has one SQL Server 2000 database. The regional offices are connected to the main office by a high-speed network.
The main office database is used to consolidate information from the regional office databases. The tables in the main office database are partitioned horizontally. The regional office location is used as part of the primary key for the main office database. You are designing the physical replication model.
What should you do?
A. Configure the main office as a publishing Subscriber.
B. Configure the main office as a Publisher with a remote Distributor.
C. Configure the main office as a central Publisher and the regional offices as Subscribers.
D. Configure the regional offices as Publishers and the main office as a central Subscriber.
Answer: D

20. You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices. Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases. Transactional replication is used to perform these updates.
Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800 MB in size. You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense.
What should you do?
A. Use transactional replication.
Mark all subscriptions for reinitialization.
B. Copy the snapshot to an NTFS compressed share.
Create a CD from that share.
Distribute the CD to the regional offices.
C. Create a compressed snapshot in the default snapshot folder on the Distributor.
Create a CD from that folder.
Distribute the CD to the regional offices.
D. Create a compressed snapshot in an alternate snapshot folder on the network.
Create a CD from that folder.
Distribute the CD to the regional offices.
E. Create a compressed snapshot in an alternate snapshot folder on the network.
Use FTP to distribute the snapshot to the regional offices.
Answer: D