Designing and Implementing Databases with Microsoft SQL Server 2000, Enterprise Edition

1. You are a database developer for a publishing company. You are designing a series of databases that will store excerpts of news articles. The database will be used for this purpose only. The data that will be stored includes text and images. The text columns will be bulk loaded from files. Each database will store data for one month of articles. Your database maintenance plan includes weekly re-indexing of the tables within each database. You must minimize the impact of the database maintenance on users of the database while retaining the ability to recover from media failure. You want to accomplish this by using the least amount of administrative effort. You are creating the first database, which is named Jan00. Which script should you use to create this database?
A. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:\data\Jan00.mdf') GO ALTER DATABASE Jan00 SET RECOVERY BULK_LOGGED GO
B. ALTER DATABASE model SET RECOVERY BULK_LOGGED GO CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:\data\Jan00.mdf') GO
C. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:\data\Jan00.mdf') GO ALTER DATABASE Jan00 SET RECOVERY FULL GO
D. CREATE DATABASE Jan00 ON ( NAME = Jan00_dat, FILENAME = 'c:\data\Jan00.mdf') GO ALTER DATABASE Jan00 SET RECOVERY SIMPLE GO Answer: B

2. 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. He 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
 
3. You are a database developer for Lucerne Publishing. The company's existing Human Resources database is a dBASE 5.0 database. You are migrating this database to a SQL Server 2000 database. You are creating a Data Transformation Services (DTS) package to perform this migration. To prepare for import into the SQL Server database, you export the dBASE 5.0 table named Employee into comma-delimited text files. One of the text files contains the following columns: EmpID, Last, First, DeptID, MgrID, and deleted. This text file will be used to populate the Employee table in SQL Server. You will import only those records that have not been marked for deletion. Ou want to load the data into the table as quickly as possible while minimizing space requirements. How should you create the DTS package?
A. Create a Data Driven Query task to import the data into the Employee table, and specify that the deleted column contains a zero value.
B. Use the bcp utility to create a format file, and then edit the file so that it contains the columns you want to import.Use a Bulk Insert task to import the data into the Employee table. Specify the BatchSize property equal to 100. Specify the CHECK_CONSTRAINTS property, and allow only those rows that contain a zero value for the deleted column.
C. Create a Transform Data task to import the data into the Employee table. Use an ActiveX script to process the rows individually, and accept only those rows that contain a zero value for the deleted column.
D. Create a temporary table. Use the Bulk Insert task to load the data from the text file into the temporary table. Use an Execute SQL task to insert the data from the temporary table into the Employee table for those rows that contain a zero value for the deleted column.
Answer: C

4.You are the developer of a reporting and analysis database for a small insurance company. You create a table named DailyPremiums by using the script shown in the Script for DailyPremiums Table exhibit. (Click the <> button.) 
This table contains the daily totals of life insurance and auto insurance premium payments.
Data is entered into this database from two applications. One application enters the daily total of life insurance premiums and the other enters the daily total of auto insurance premiums. Some days, no premiums are collected for life insurance policies or auto insurance policies. The application that enters the life insurance premiums represents no premiums with a value of zero. The application that enters the auto insurance premiums represents no premiums with a null value. An example of a week's data is shown in the Sample Data exhibit. (Click the <> button.) 
You need to create a report that displays the average premium for life insurance policies and auto insurance policies. Which script should you use?
A. SELECT AVG(LifePolicy), AVG(AutoPolicy) FROM DailyPremiums
B. SELECT AVG(NULLIF(LifePolicy, 0)), AVG(AutoPolicy) FROM DailyPremiums
C. SELECT AVG (LifePolicy), AVG(ISNULL(AutoPolicy, 0)) FROM DailyPremiums
D. SELECT AVG(LifePolicy), SUM(AutoPolicy)/SUM(CASE WHEN AutoPolicy IS NULL THEN 0 ELSE 1 END) FROM DailyPremiums
Answer: C

5. You are a database developer for your company's Human Resources database. This database includes a table named Employee that contains confidential ID numbers and salaries. The table also includes non-confidential information, such as employee names and addresses. You need to make all the non-confidential information in the Employee table available in XML format to an external application. The external application should be able to specify the exact format of the XML data. You also need to hide the existence of the confidential information from the external application. What should you do?
A. Create a stored procedure that returns the non-confidential information from the Employee table formatted as XML.
B. Create a user-defined function that returns the non-confidential information from the Employee table in a rowset that is formatted as XML.
C. Create a view that includes only the non-confidential information from the Employee table. Give the external application permission to submit queries against the view.
D. Set column-level permissions on the Employee table to prevent the external application from viewing the confidential columns. Give the external application permission to submit queries against the table.
Answer: C

6. You are a database developer for Coho Vineyard. You are designing a stored procedure that will be used to add customers to a database. The database schema is shown in the Database Schema exhibit. (Click the <> button.)
You create a stored procedure named spAddCustomer. This stored procedure is shown in the SpAddCustomer exhibit. (Click the <> button.) 
You use the following script to test the stored procedure:
SELECT * FROM Customers WHERE CustomerID = 'COHO'
EXEC spAddCustomer 'COHO','Coho Vineyard'
SELECT * FROM Customers WHERE CustomerID = 'COHO'
The output from this script is shown in the Output exhibit. (Click the <> button.)
You need to replace the stored procedure with one that correctly processes the test script. Which stored procedure should you use?
A. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS SET IMPLICIT_TRANSACTIONS ON BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) COMMIT TRANSACTION
B. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 RAISERROR ('Inserting into Customer', 16, 1) INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 RAISERROR ('Inserting into CustomerCustomerDemo', 16, 1) COMMIT TRANSACTION
C. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 GOTO on_error INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 GOTO on_error COMMIT TRANSACTION on_error: ROLLBACK TRANSACTION
D. CREATE PROCEDURE spAddCustomer @CustomerID varchar(5), @CoName varchar(40) AS BEGIN TRANSACTION INSERT INTO Customer values ( @CustomerID, @CoName, NULL, NULL, NULL ) IF @@ERROR <> 0 RETURN (-1) INSERT INTO CustomerCustomerDemo values ( @CustomerID, NULL ) IF @@ERROR <> 0 RETURN (-1) COMMIT TRANSACTION
Answer: C

7. You are a database developer for a sales company. The company's production database is hosted on a computer running SQL Server 2000. You are developing a view on the company's developer database. On the developer database, you design a view and an index on the view. The script that was used to create this view is shown in the exhibit. (Click the <> button.)
After you complete your testing on the developer database, you successfully create this view on the production database. You attempt to create the index on this view, but you are unable to do so. What should you do? (Each correct answer presents part of the solution. Choose two.)
A. Replace COUNT_BIG(*) with COUNT(*) in the select list.
B. Change the table names from two-part names to one-part names.
C. Re-create the view by using the WITH SCHEMABINDING option.
D. Remove AVG(SaleAmount) from the select list. Include AVG(SaleAmount) in the query that accesses the view.
E. Before creating the index, execute the SET NUMERIC_ROUNDABORT ON statement. Answer: CD

8.You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer. In the query pane, you create the following query:
SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P JOIN AgentPolicy AS AP
ON
(P.PolicyNumber = AP.PolicyNumber)
WHERE IssueState = 'IL'
AND PolicyDate BETWEEN '1/1/2000' AND '3/1/2000'
AND FaceAmount > 1000000 You choose Display
Estimated Execution Plan from the Query menu and execute the query. The query execution plan that is generated is shown in the Estimated Execution Plan exhibit. (Click the <> button.) You then choose Show Execution Plan from the Query menu and execute the query. The query execution plan that is generated is shown in the Query Execution Plan exhibit. (Click the <> button.) What should you do?
A. Rewrite the query to eliminate the BETWEEN keyword.
B. Add a join hint that includes the HASH option to the query.
C. Add the WITH (INDEX (0)) table hint to the Policy table.
D. Update statistics on the Policy table.
E. Execute the DBCC DBREINDEX statement on the Policy table.
Answer: D

9. You are a database developer for your company's SQL Server 2000 database named Sales. The database contains tables named Orders, OrderLineItems, and Customers. The Orders and OrderLineItems tables have frequent insert activity during the day. You have created identical views on the Orders, OrderLineItems, and Customers tables for the years 1998 and 1999. The script that was used to create the Orders1999 view is shown in the exhibit. (Click the <> button.)
Queries such as the following are frequently executed on the views:
SELECT OrderDate, Total, Discount, Company, EmployeeID
FROM Orders1999
WHERE OrderDate BETWEEN '10/1/1999' AND '12/31/1999' AND CompanyName = 'Tailspin Toys'
You want to improve the response time of this query. What should you do?
A. Alter the view to use the WITH CHECK OPTION clause.
B. Create a computed column on the OrderLineItems table by using the formula UnitPrice * Quantity.
C. Create a composite nonclustered index on the OrderID, EmployeeID, and CustomerID columns of the Orders table.
D. Create a nonclustered index on the OrderDate column of the views. E. Create a unique clustered index on the OrderID column of the views.
 Answer: E

10. You are a database developer for a SQL Server 2000 database. The database is in the default configuration.
The number of users accessing the database has increased from 100 to 1,000 in the last month. Users inform you that they are receiving error messages frequently. The following is an example of an error message that was received: Transaction (Process ID 56) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. What should you do?
A. Use a higher transaction isolation level for transactions used in the database.
B. Use SQL Profiler to capture deadlock events.
C. Use System Monitor to monitor lock requests and deadlocks.
D. Execute the sp_configure system stored procedure to increase the number of simultaneous user connections allowed to SQL Server.
E. Execute the sp_configure system stored procedure to increase the number of locks that can be used by SQL Server.
Answer: B

11. 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. (Click the <> button.)
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

12.You are designing a database for Tailspin Toys. You review the database design, which is shown in the exhibit. (Click the <> button.)
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

13.You are a database developer for a community college. You are designing a database that will be used for class registrations. You review the database design, which is shown in the exhibit. (Click the <> button.)

You want to normalize data, promote quick response times for queries, and minimize redundant data. What should you do? (Each correct answer presents part of the solution. Choose four.)
A. Create a UNIQUE constraint on the Class1, Class2, and Class3 columns.
B. Create a composite PRIMARY KEY constraint on the StudentID and Faculty columns of the Students table.
C. Create a new table named Faculty. Add FacultyID, Name, and Room columns to this table.
D. Create a new table named FacultyOffice. Add FacultyID and Room columns to this table.
E. Create a new table named CourseFaculty. Add FacultyID and ClassID columns to this table.
F. Create a new table named StudentFaculty. Add StudentID and FacultyID columns to this table.
G. Create a new table named StudentRegistration. Add StudentID and ClassID columns to this table.
Answer: CEFG

14.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. (Click the <> button.)
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

15. You are a database developer for a technical training center. Currently, administrative employees keep records of students, instructors, and courses 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 need to design the tables for this database. Each student can take courses from multiple instructors. Each instructor instructs multiple courses. You need to design the tables while enforcing data integrity. What should you do?
A. Create one table that includes both a Student column and an Instructor column.
B. Create three tables: Student, Instructor, and Course. Include the Student key and the Instructor key in the Course table.
C. Create three tables: Course, Instructor, and CourseInstructor. Create a FOREIGN KEY constraint on the Course table that references the CourseInstructor table. Create a FOREIGN KEY constraint on the Instructor table that references the CourseInstructor table.
D. Create three tables: Course, Instructor, and CourseInstructor. Create a FOREIGN KEY constraint on the CourseInstructor table that references the Course table. Create a second FOREIGN KEY constraint on the CourseInstructor table that references the Instructor table.
E. Create four tables: Student, Instructor, TaughtBy, and Teaches. Create a PRIMARY KEY constraint on each table.
Answer: D

16.You are a database developer for Wide World Importers. You are creating a sales-reporting database for the company. The company employs contract salespeople. Each time a salesperson is hired or re-hired, he or she is assigned a unique employee ID. You must be able to report total yearly sales for each employee. You have created the tables for this database by using the script shown in the Script exhibit. (Click the <> button.)

After creating the tables, you attempt to execute the following script:
ALTER TABLE [Sales] ADD CONSTRAINT FK_Sales_Employee FOREIGN KEY ([EmployeeID] ) REFERENCES [Employee](EmployeeID) You receive the error message shown in the Error Message exhibit. (Click the <> button.)
What should you do?
A. Remove the PRIMARY KEY constraint from the SSN column in the Employee table. Create a new PRIMARY KEY constraint on the EmployeeID column in the Employee table.
B. Create a new clustered index on the EmployeeID column in the Employee table.
C. Create a UNIQUE constraint on the EmployeeID column in the Employee table.
D. Create a UNIQUE constraint on the EmployeeID column in the Sales table.
Answer: A

17. 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. (Click the <> button.)
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

18.You are a database developer for a plumbing supply company. You create two tables named Products and Suppliers. These tables are shown in the exhibit. (Click the <> button.)

For each product, you purchase from only one supplier. The suppliers might change on a monthly basis. When a supplier is no longer used, you must remove the supplier from the database. You must accomplish this task by using the least amount of administrative effort. What should you do?
A. On the Products table, create a FOREIGN KEY constraint, and specify the ON DELETE CASCADE clause.
B. On the Suppliers table, create a FOREIGN KEY constraint, and specify the ON DELETE CASCADE clause.
C. Create an AFTER DELETE trigger on the Suppliers table. Use this trigger to delete the rows in the Products table that correspond to the SupplierID column.
D. Create an INSTEAD OF trigger on the Suppliers table. Use this trigger to delete the rows in the Products table that correspond to the SupplierID column in the deleted table. Use the trigger to delete the rows in the Suppliers table that correspond to the SupplierID column in the deleted table.
E. Create a stored procedure that accepts SupplierID as a parameter. Use this stored procedure to delete the rows in the Products table that correspond to the SupplierID column. Use the stored procedure to delete the rows in the Suppliers table that correspond to the SupplierID column.
Answer: D

19. You are designing an inventory and shipping database for Contoso, Ltd. You create the logical database design shown in the exhibit. (Click the <> button.)
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

20. 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 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

21. You are a database developer for Coho Vineyard. You are creating a table named LineItem that will contain invoice line items. The table needs to include InvoiceID, UnitsSold, UnitPrice, and ShippingCost columns. Each line item will include a value in the InvoiceID, UnitsSold, and UnitPrice columns. Not every line item will include a value in the ShippingCost column. You need to be able to calculate the total amount of an invoice by using the LineItem table. The script that you will use to calculate the total amount of an invoice is shown in the exhibit. (Click the <> button.)
You must ensure that the total sale price is always calculated correctly. Which script should you use to create the LineItem table?
A. CREATE TABLE LineItem ( ItemID int NOT NULL, InvoiceID int NOT NULL, UnitsSold int NOT NULL, UnitPrice money NOT NULL, ShippingCost money NULL, CONSTRAINT PK_LineItem PRIMARY KEY CLUSTERED (ItemID) )
B. CREATE TABLE LineItem ( ItemID int NOT NULL, InvoiceID int NOT NULL, UnitsSold int, UnitPrice money, ShippingCost money, CONSTRAINT PK_LineItem PRIMARY KEY CLUSTERED (ItemID), CONSTRAINT chkNull CHECK (UnitsSold IS NOT NULL AND UnitPrice IS NOT NULL) )
C. CREATE TABLE LineItem ( ItemID int NOT NULL, InvoiceID int NOT NULL, UnitsSold int NOT NULL DEFAULT (0), UnitPrice money NOT NULL DEFAULT (0), ShippingCost money NOT NULL DEFAULT (0), CONSTRAINT PK_LineItem PRIMARY KEY CLUSTERED (ItemID) )
D. CREATE TABLE LineItem ( ItemID int NOT NULL, InvoiceID int NOT NULL, UnitsSold int, UnitPrice money, ShippingCost money, CONSTRAINT PK_LineItem PRIMARY KEY CLUSTERED (ItemID) ) CREATE TRIGGER tI_LineItem ON LineItem FOR INSERT AS BEGIN DECLARE @count int SELECT @count = count(*) FROM inserted WHERE UnitsSold IS NULL OR UnitPrice IS NULL OR ShippingCost IS NULL IF (@count > 0) BEGIN RAISERROR('Error', 16, 1) ROLLBACK TRANSACTION END END
Answer: C

22. 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

23.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. (Click the <> button.)
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

24.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. (Click the <> button.)
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

25. You are a database developer for a sales organization. You create a database named Sales by using the following script:
CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'c:\data\saledat.mdf', SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 )
After several months of use, the database has grown to 7 MB. You want to reduce the unused space in the database and return the space to the operating system. You need to use this disk space for another database that you are creating. Which script should you use?
A. sp_dboption 'Sales', 'autoshrink'
B. ALTER DATABASE Sales SET AUTO_SHRINK ON
C. DBCC SHRINKDATABASE( Sales, 10, NOTRUNCATE) DBCC SHRINKDATABASE( Sales, 10, TRUNCATEONLY)
D. DBCC SHRINKFILE('c:\data\saledat.mdf', EMPTYFILE) DBCC SHRINKFILE('c:\data\saledat.mdf', TRUNCATEONLY)
Answer: C

26. 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

27. 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

28. You are a database developer for Proseware, Inc. You are designing a SQL Server 2000 database for the company's human resources department. This database contains a table named Employees and a table named JobTitles. Employee information will be stored in the Employees table. This table will contain a column named Salary, which will list employee salaries. Each job title has a minimum and a maximum salary. The salary range for each job title will be stored in the JobTitles table. This table will contain columns named MinSalary and MaxSalary. You must ensure that when an employee receives a salary increase, the salary is not greater than the maximum salary specified for his or her job title. What should you do?
A. Create a CHECK constraint on the Salary column of the Employees table.
B. Create a trigger on the Employees table that will fire whenever a row is updated.
C. Create a trigger on the JobTitles table that will fire whenever a row is updated.
D. Create a FOREIGN KEY constraint on the Employees table that references the JobTitles table.
 Answer: B

29.You are a database developer for an insurance company. You are designing a SQL Server 2000 database that will contain insurance information. A partial database schema is shown in the exhibit. (Click the <> button.)
The company has more than 100 sales agents. Each agent is assigned to one of five regions and can sell policies only to people who live in that region. In the database, agents must be able to update policies sold within their region. Agents cannot update policies sold outside their region. You want to ensure that agents can insert and update policies only within their region. What should you do?
A. Create an AFTER trigger on the Agents table.
B. Create INSERT and UPDATE triggers on the Policy table.
C. Create a CHECK constraint on the Region column of the Agents table.
D. Create a FOREIGN KEY constraint on the Agents table that references the Insured table.
E. Create views on the Agents, Policy, and Insured tables for each region by using the WITH CHECK option.
Answer: E

30. 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