MSCE Interview Questions and Answers

QUESTION 21
You work as a database developer for Certkiller .com 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. Your job description at Certkiller .com includes the
administration of the database server named Certkiller -DB01.
The Certkiller .com Sales Department manager named Rory Allen makes use of a
database to record the sales transactions carried out by his department. There are
currently two tables named Inventory and Invoices. These two tables currently hold
the information regarding products and orders as illustrated by the following
exhibit:
Exhibit:



At present only one product can be listed per invoice. Rory Allen wants the ability
to list multiple products on the same invoice because it would greatly reduce the
amount of administrative overhead. You thus received instruction to modify the
database scheme in such a way as to support Rory Allen's request. Your solution
should not involve storing redundant data and should also not affect query
performance negatively.
What should you do?

A. The Inventory and Invoices tables should be merged into a single table named Sales.
B. A table named Sales that will reference Inventory and Invoices should be created.
C. Multiple products should be recorded in the SKU column in the Invoices table.
D. Multiple products should be added for SKU and quantity should be added to the
Invoices table.

Answer: B

Explanation: The ability to record multiple sales transactions on a single invoice can be achieved if you create a separate Sales table. This Sales table should include two foreign keys: InvoiceNumber column and SKU column.
Each row in Sales will represent the sale of a single product on a specific invoice.
Each invoice is represented by a row in the Invoices table.
For each row there can be multiple corresponding rows in the Sales table with the same invoice number and different SKUs.
The SKU column in Sales will contain only the values that exist in the SKU column in
the Inventory table. You should implement these relationships by means of foreign keys on the InvoiceNumber and SKU columns in the Sales table.
This should result in a normalized schema in which the database will not contain
redundant data.

Incorrect answers:

A: Merging Inventory and Invoices will result in redundant information because then the same product information will have to be recorded in multiple rows - once for each transaction that involves a sale of a particular product.
C:Recording multiple products in a single SKU column will compel you to record multiple
values in the Quantity column. As a consequence you will then have to contend with
slower performance on queries since both indexes on the SKU and Quantity columns
would be useless.
D: Adding multiple columns for SKU and Quantity to the Invoices table will limit the
number of products sold on a single invoice to the number of columns. If a customer
purchased fewer products that the columns provided for product information, then some
of those columns will remain empty, this will result in an unnecessary increase in the size of the database. also querying will become inefficient as performance would then be slower.


QUESTION 22
You work as a database developer for Certkiller .com. Certkiller .com is a 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. Your job
description at Certkiller .com includes the administration of the database server
named Certkiller -DB01. Certkiller .com hires teachers to be instructors to its
students.
You have been instructed to design a database to comply with the following business
rules:
1. For each instructor, the database must contain information about all the exams
which the instructor is qualified to teach.
2. Any instructor may have any number of exams that they are qualified to teach.
3. One or more instructors may be associated with each registered exam.
Your solution should not contain any redundant data. To this end you created the
following tables as illustrated in the exhibit:
Exhibit:



Now you need to decide on the following step to comply with all the business rules:
What should you do?

A. A InstructorExam table must be created.
B. A FOREIGN key on the Examinations table that references the Instructors table must
be defined.
C. A ExamID column must be added to the Instructors table.
D. A InstructorID column must be added to the Examinations table.

Answer: A

Explanation: A translation table is required in this scenario. This table will link the Instructors and Examinations tables without introducing any redundant data. The primary key for this translation table, named InstructorExam, should consist of two columns: one that references the InstructorID column in the Instructors table and the other that references the ExamID in the Examinations table.
Each row in the InstructorExam table will represent qualifications by a specific teacher in a specific subject.

Incorrect answers:

B: If you define a FOREIGN key on the Examinations table that references the
Instructors table then you would only represent the fact that each of the registered
subjects has been tutored by one of the registered teachers. This is not a stipulation according to the business rules and as such do not require representation.
C: If you added a ExamID column to the Instructors table instead of creating a
translation table the you could define a foreign key that references ExamID in the
Examinations table. However, then you will only be allowed to specify one subject per
instructor. If you specified multiple exams in the same row in the ExamID column in the Instructors table, then you will not be able to define that column as a foreign key. Then the possibility of specifying invalid IDs, such as IDs that does not correspond to any of the registered subjects to become a reality.
D: If you added a InstructorID column to the Examinations table instead of creating a
translation table, then you could define a foreign key that references the InstructorID column in the Instructors table. However, then you will be allowed to specify only one instructor for each exam. And if you specified multiple instructors in the same row in the InstructorID column in the Exams table, then you would not be able to define that column as a foreign key and this would result in the possibility to specify invalid IDs, such as IDs that does not correspond to any of the instructors.


QUESTION 23
You work as a database developer at Certkiller .com. When a Certkiller .com instructs
you to design an inventory and shipping database for a customer, you create the
following logical database design.



Certkiller .com then informs you that the referential integrity of the database has to
be maintained.
Which of the following describes three actions that you should take to achieve this
objective? (Choose three)

A. Create a FOREIGN KEY constraint on the Products table that references the
OrderDetails 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
OrderDetails table.
D. Create a FOREIGN KEY constraint on the OrderDetails table that references the
Order table.
E. Create a FOREIGN KEY constraint on the OrderDetails table that references the
Products table.
F. Create a FOREIGN KEY constraint on the Suppliers table that references the Products table.

Answer: B, D, E.

Explanation:
From a logical database design line of thought:
The tables are entities and they should be connected by relations. In particular,
one-to-many should be used in almost every case. Occasionally, one-to-one relations are used, but it is very rare.
The Suppliers table Every supplier can supply one or more products.
This is a one-to-many relation from the Suppliers table to the Products table.
The one-to-many relationship is implemented by creating a foreign key constraint on the SupplierID column in the Products table referencing the primary key column SupplierID in the Suppliers table. (Answer B.)
The Products table Every product could be included in one or several orders.

Every order could include one or several products.
So we have a many-to-many relationship between the Orders and Products table. Since
SQL Server, and almost every other database management system, doesn't support
many-to-many relations, the extra table OrderDetails has been added. The OrderDetails
table will be used to relate the Orders and Products table with each other. We need two one-to-many relations:
* One one-to-many relation from the Products table to the OrderDetails table. This
relationship will be implemented by a foreign key constraint on the ProductID column of the OrderDetails table referencing the ProductID primary key column of the Product
table.

(Answer D.)

* One one-to-many relation from the Orders table to the OrderDetails table. This
relationship will be implemented by a foreign key constraint on the OrderID column of
the OrderDetails referencing the OrderID primary key column of the Orders table.

(Answer E.)

From a procedural line of thought:
The OrderDetails table would have a FOREIGN KEY against the Orders table and the
Products table so that the Orders table and the Products table would be updated, once an order for a particular product or products has been placed. The Products table would have a FOREIGN KEY against a Suppliers table to update the Suppliers table once products are received.

Incorrect answers:

A: Logical design argument
A FOREIGN KEY constraint on the Products table that references the OrderDetails table
is a one-to-many relation from the OrderDetails to the Products table, which is to say that one OrderDetail row can be included in many rows in the Products table, but we want the opposite. We want a one-to-many relation from the Products table to the OrderDetails table.
Procedural argument
Creating a FOREIGN KEY constraints on the Products table that references the Order
Details table would result in the Order Details table being updated when products are
received and entered into the Products table.
C: Logical design argument
We want a one-to-many relation from the Orders to the OrderDetails table, not the other way around. We want that every order row should have one or several corresponding rows in the OrderDetails table, not the other way around.
Procedural argument Incoming orders would be entered into the OrderDetails table and not the Orders table, thus the OrderDetails table has a FOREIGN KEY against the Orders table so that the Orders table would be updated once data is entered into the OrderDetails table.
F: Logical design argument
Every supplier can supply one or several products, so we need a one-to-many relation
from the Suppliers table to the Products table. This relationship is implemented with a foreign key constraint on a column in the Products table referencing a column in the Suppliers table, not the other way around.
Procedural argument Data pertaining to products received would be entered into the Products table, after which the Suppliers table would be updated, thus the Products table would have a FOREIGN KEY against a Suppliers table to update the Suppliers table once products are received.


QUESTION 24
You work as the database administrator for Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a database server named
Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that
stores sales data for the company. The tables in the CK_Sales database are shown in
the following database diagram.



A PRIMARY KEY column has been declared on the ProductID column of the
Products table and the InvoiceID column of the Invoices table. You are creating a
table named Inventory to track stock levels. You need to ensure that combination of
the DateShipped, ProductID and InvoiceID is unique for each row in the Inventory
table.
What should you do?

A. Create a rule.
B. Create a table-level constraint.
C. Create a UNIQUE constraint for each of the three columns.
D. Create a CHECK constraint for each of the three columns.

Answer: B

Explanation: A table-level constraint allows you to ensure that a combination of
columns is unique for each row in the table.

Incorrect answers:

A, D: A rule and CHECK constraints are used to ensure FOREIGN KEY integrity. The
yare not used to ensure that data in a column or combination of columns is unique.
C: UNIQUE constraints do not allow a value to be repeated in the column. This means
that every value in the DateShipped column must be unique; each value in the ProductID column must be unique; and each value in the InvoiceID column must be unique.


QUESTION 25
You work as a database developer for Certkiller .com. The Certkiller .com 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. Your job description at Certkiller .com
includes the administration of the database server named Certkiller -DB01.
You have been instructed to design a database for Certkiller .com to use to record
their daily business activities. The following exhibit illustrates a portion of the
database.
Exhibit:



Certkiller .com currently has the following business rules:
1. Each sales transaction must appear in exactly one invoice.
2. Sales transactions are allowed only with registered customers.
3. Only registered products can be sold.
4. Each product must belong to a single registered category.
Your solution must comply with the Certkiller .com business rules. Your intentions
are to make use of foreign keys to enforce these business rules. Thus you need to
take a decision on which foreign keys to define.
What should you do? (Each correct answer presents part of the solution. Choose
FOUR.)

A. Define a FOREIGN key on the Categories table that references the Inventory table.
B. Define a FOREIGN key on the Inventory table that references the Category table.
C. Define a FOREIGN key on the Inventory table that references the Sales table.
D. Define a FOREIGN key on the Sales table that references the Inventory table.
E. Define a FOREIGN key on the Sales table that references the Invoices table.
F. Define a FOREIGN key on the Sales table that references the Customers table.
G. Define a FOREIGN key on the Customers table that references the Sales table.
H. Define a FOREIGN key on the Customers table that references the Invoices table.
I. Define a FOREIGN key on the Invoices table that references the Customers table.
J. Define a FOREIGN key on the Invoices table that references the Sales table.

Answer: B, D, E, I

Explanation: Foreign keys is a set of one or more columns that references a matching set of columns in the same table or another table. Thus only values that exists in the referenced column are allowed in a foreign key column. Foreign keys can only reference primary key columns or columns with a unique constraint. As such Foreign Key constraints enforce referential integrity of data.

This means that values in a references column cannot be modified and rows in a
referenced table cannot be deleted if any of these actions would cause even one row in the foreign key table to become orphaned.

Each row in the Sales table represents a sales transaction - thus it must reference an invoice that is registered in the Invoices table. - foreign key in Sales table that reference InvoiceNumber column in Invoices table Each row in Invoices table represents an invoice that lists all sales transactions performed at one time with a specific customer. - thus customer should thus be registered in the Customers table. - foreign key on CustomerID column in Invoices table that reference
CustomerID column in Customers table.

Only registered products may be sold -registration is in the Inventory table - foreign key in ProductID column in Sales table that reference the ProductID column in the Inventory table.

Each row in Inventory table represents a product that must belong to a single category represented by a row in the Categories table - foreign key in CategoryID column in the Inventory table that reference the CategoryID column in the Categories table.

Incorrect answers:

A: Each row in Inventory table represents a product that must belong to a single category represented by a row in the Categories table - foreign key in CategoryID column in the Inventory table that reference the CategoryID column in the Categories table. And NOT a FOREIGN key on the Categories table that references the Inventory table.
C: Only registered products may be sold -registration is in the Inventory table - foreign key in ProductID column in Sales table that reference the ProductID column in the Inventory table. And NOT a FOREIGN key on the Inventory table that references the Sales table.
F: Each row in the Sales table represents a sales transaction - thus it must reference an invoice that is registered in the Invoices table. - foreign key in Sales table that reference InvoiceNumber column in Invoices table - and NOT a FOREIGN key on the Sales table that references the Customers table.
G, H: Each row in Invoices table represents an invoice that lists all sales transactions performed at one time with a specific customer. - thus customer should thus be registered in the Customers table. - foreign key on CustomerID column in Invoices table that reference CustomerID column in Customers table - and NOT a FOREIGN key on the Customers table that references the Invoices table, NOT a FOREIGN key on the Customers table that references the Sales table.
J: Each row in Invoices table represents an invoice that lists all sales transactions
performed at one time with a specific customer. - thus customer should thus be registered in the Customers table. - foreign key on CustomerID column in Invoices table that reference CustomerID column in Customers table - and NOT a FOREIGN key on the Invoices table that references the Sales table.


QUESTION 26
You work as the database administrator for Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a database server named
Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that
stores sales data for the company. The tables in the CK_Sales database are shown in
the following database diagram.



Certkiller .com decides to switch to commission-based salaries for its Sales staff. You need to generate information that Rory Allen, the manager of the Sales department, will use to calculate the commission for each sales person. Your solution must meet the following criteria:

1. You must be able to use multiple SELECT statements to produce the result set.
2. The result set must be returned as a table for which the PRIMARY KEY
constraint is defined.
3. Rory Allen must be able to reference the result set in the FROM clause of a
SELECT statement.
What should you do?

A. Create a stored procedure.
B. Create a view.
C. Create a user-defined function.
D. Create a Common Runtime Language (CLR) function.

Answer: C

Explanation: You need a user-defined function to meet the requirements for your
solution.

Incorrect answers:
A: Result sets from a stored procedure cannot be in the FROM clause of a SELECT
statement.
B: A view only supports a sing SELECT statement. You need to be able to use multiple
SELECT statements to produce the result set.
D: You cannot define a PRIMARY KEY constraint on the result set returned from a
Common Runtime Language (CLR) function.


QUESTION 27
You work as a database developer for Certkiller .com. The Certkiller .com 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. Your job description at Certkiller .com
includes the administration of the database server named Certkiller -DB01.
The Certkiller .com Human Resources department is responsible for the updating of
staff information. They store the staff information in a table named Staff in a SQL
Server database. In this table the staff members' birth dates will be stored in the
DateOfBirth column. Whenever a new person joins the staff a row is added for the
new staff member, and a valid date of birth must be specified in an INSERT
statement.
You received instruction to ensure that the dates of birth a valid and thus need to
specify the appropriate features for the DateOfBirth column.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Specify a PRIMARY KEY constraint on the DateOfBirth column.
B. Specify a FOREIGN KEY constraint on the DateOfBirth column.
C. Specify a CHECK constraint on the DateOfBirth column.
D. Specify the NOT NULL constraint on the DateOfBirth column.
E. Specify a DEFAULT definition on the DateOfBirth column.
F. Specify the SQL Server timestamp data type on the DateOfBirth column

Answer: C, D

Explanation: To ensure that the dates of birth are valid you should specify the NOT
NULL constraint on the particular column. This will ensure that a non-null value will be specified for the NOT NULL constraint on that column.
Further you should specify the datetime data type for the DateOfBirth column and define a CHECK constraint so as to prevent dates that are obviously invalid from being entered.
i.e. specify the data of birth be no earlier than 1900 and that s new staff member be at least 18 years of age.
Incorrect answers:
A: A PRIMARY KEY constraint is used to prevent nulls and enforce uniqueness of
values in primary key column/s. this is not feasible in the case of dates of birth as there is
no guarantee that two employees cannot have the same birth date.
B: A FOREIGN KEY constraint is used to enforce referential integrity of data. it is
usually assigned to a foreign key in the referenced column. No mention is made of the
table with all unique birth dates. This thus constraint will not be appropriate.
E: It is possible to use a DEFAULT definition to automatically assign a value to a
column if no value is explicitly specified in an INSERT statement. Thus it will result in the prevention of null values from being entered, but it will not prevent an INSERT statement from completing successfully even if no valid date of birth were specified.
F: The SQL Server timestamp data type is not related to date or time. Instead it is a
binary value that can be used to detect whether modifications has been made to a row.


QUESTION 28
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.
Your job description at Certkiller .com includes the administration of the database
server named Certkiller -DB01.
The Certkiller .com Human Resources department is responsible for the updating of
staff information. This staff information includes details such as job positions and
job descriptions. All the Certkiller .com employees are encouraged to prepare
themselves for multi-tasking and consequently are encouraged to train for various
positions. Each employee can be trained for multiple positions, and multiple
employees can be trained for a given position.
You received a request from the Human Resources manager to develop a database
schema that will allow information regarding each employee's ability to fill different positions to be recorded in the database. you need to design the database without introducing redundant data.
What should you do?

A. Create two tables named Employees and Positions.
Define a FOREIGN KEY to reference the Employees table on the Positions table.
B. Create two tables named Employees and Positions.
Define a FOREIGN KEY to reference the Positions table on the Employees table.
C. Create three tables named Employees, Positions, and EmployeePosition.
Define a FOREIGN KEY to reference the Positions table on the EmployeePosition table.
Define a FOREIGN KEY to reference the Employee table on the EmployeePosition
table.
D. Create three tables named Employees, Positions, and EmployeePosition.
Define a FOREIGN KEY to reference the Positions table on the Employees table.
Define a FOREIGN KEY to reference the EmployeePosition table on the Position table.

Answer: D

Explanation: You need to create three tables and define the primary and foreign keys as follows:
In the Employees table - each row represents one employee.
In the Positions table - each row represents one position
In the EmployeePosition table - each row represents the fact that a specific individual employee qualifies for a specific position.
The Primary key on the EmployeeID and PositionID columns in the EmployeePosition
table eliminates duplicate row possibilities.
The Foreign key referencing the EmployeeID column in the Employees table ensures
only registered Certkiller .com staff will qualify for these positions.
The Foreign key referencing the PositionID column in the Positions table ensures that
staff members qualify only for registered positions.

Incorrect answers:

A: With this arrangement only one employee can be associated with each position.
B: With this arrangement only one position can be associated with each employee.
C: With this arrangement you will encounter duplication of rows. You should instead
define a FOREIGN KEY to reference the Positions table on the Employees table.


QUESTION 29
You work as a database developer at Certkiller .com. You are currently designing a
database that will store employee information for Certkiller .com's human resources
department.
You create a table named EmployeeContact that contains the following columns:

1. HomePhone
2. BusinessPhone
3. FaxNumber
4. EmailAddress Of the following options, which is the on that will ensure that each record includes a value for either the HomePhone column or the BusinessPhone column?

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.

Explanation:
We want to check rows so that rows cannot have NULL values in both the HomePhone
columns. One NULL value in either column is OK, though.
We need to create one constraint mechanism that takes both columns into account. We
should add a CHECK table constraint that prevents NULL values from being entered into
both mentioned columns. This could be done by:
CONSTRAINT CK_phone CHECK (NOT (HomePhone is NULL and
BusinessPhone is NULL))
Note that rules can be bound to columns but not to tables. So we cannot use rules in this scenario.
A general note on rules and check constraints:
Rules perform the same function as CHECK constraints and are used in cases where
backward compatibility is required. CHECK constraints are the preferred, standard way
of restricting the values in a column and are also more concise than rules. Multiple
CHECK constraints can be applied to a column, while only one rule can be applied per
column. CHECK constraints are also specified as part of the CREATE TABLE statement
while rules are created as separate objects and then bound to the column.

Incorrect answers:

A: This solution is too simplistic. This rule requires that both columns are non-NULL, but the requirement is that only one of them is non-NULL.
B: We need a restriction on the table, not on particular columns. Rules can be bound to columns but not to tables, so they cannot be used here.
C: This CHECK constraint would enforce non-NULL values in both columns, but we
want to allow a NULL value in one of the columns as long as the other column is not
NULL.
E: A trigger could solve this problem. However, triggers require more system resources and should only be used if no simple solution exists. A constraint is the preferred solution.

Note: Constraints and triggers have different benefits making them useful in different situations. Triggers can contain complex processing logic that uses Transact-SQL code and can therefore support all the functionality of constraints. However, triggers are not always the best method for a given situation. It is recommended that entity integrity be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints, that domain integrity be enforced through CHECK constraints, and that referential integrity be enforced through FOREIGN KEY constraints where these features meet the functional needs of the application. Triggers should only be used when the features supported by the other constraint types cannot meet the functional needs of the application. In this scenario a CHECK constraint applied at the table level can meet the functional needs of the application and would be the preferred method.


QUESTION 30
You work as a Microsoft SQL developer for Certkiller .com. Certkiller .com is a book
distributor. All servers on the Certkiller .com network run Microsoft Windows
Server 2000 and the database servers run Microsoft SQL Server 2000. Your job
description at Certkiller .com includes the administration of the database server
named Certkiller -DB01.
You are in the process of developing a database for Certkiller .com. The database has
two tables named EmployeeData and ShippingData respectively. All the
Certkiller .com employees' data is contained in the EmployeeData table. In this table
there is a column named Salary. The normal salary range that a Certkiller .com
employee can earn ranges from $35,000 to $110,000. You need to limit users from
entering any values in this column that are outside the normal salary range. You
now need to take a decision as to which action you can take to carry out this task
with the least amount of administrative effort.
What should you do?

A. A UNIQUE constraint should be created.
B. A CHECK constraint should be created.
C. A FOREIGN KEY constraint should be created.
D. A PRIMARY KEY constraint should be created.

Answer: B

Explanation: A CHECK constraint is used to enforce domain integrity. It specifically
limits the datatype, format, and range of values that can be entered into a column. By creating a CHECK constraint, you can prevent users from entering any salaries that do not fall in the normal salary range.

Incorrect answers:

A: A UNIQUE constraint is used to enforce uniqueness of values in a set of columns.
This is also used to enforce entity integrity and as the referenced key in a foreign key relationship. This will prevent users from entering other values.
C: A FOREIGN KEY constraint is used to establish end enforce links between data in
two tables. A foreign key in one table points t a column that has a primary key constraint or a unique constraint in another table. The foreign key constraint prevents the link between the tables from being destroyed, but it does not prevent users from entering other values.
D: A PRIMARY KEY constraint is a unique identifier for a row within a database table.
This is used to enforce entity integrity, but will not prevent users from entering any other values.

QUESTION 31
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You have received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information.
Certkiller .com knows that no row will contain more than 600 bytes. And the two
tables will not exceed 24 MB. The database will consist of two tables the Current
Price and Past price table shown below:



Certkiller .com expects that the number of customer will increase by three percent
per year. Certkiller .com has all variable-length character fields expected to be filled with data an average 50 percent. You are required to specify the growth increment for the data file but you do not want the performance to de deteriorated by frequent automatic growth of the data file. You are required to select which of the parameters to specify.
What should you do? (Choose TWO.)

A. You should specify the parameter Size = 30 MB.
B. You should specify the parameter FILEGROWTH = 10%.
C. You should specify the parameter SIZE = 300 MB.
D. You should specify the parameter FILEGROWTH = 50.

Answer: A, B

Explanation: In the scenario you should remember that the approximate disk space
is calculated by taking the sum of the estimated size of all tables, this will determine the approximate amount of disk space required.

Incorrect Answers:
C: This option should not be used in the scenario as it is already stated that the databases will never exceed 24 MB so 30 MB will suffice.
D: This option should not be used in the scenario as the correct amount of file growth that will take place in the scenario is 10%.


QUESTION 32
You work as a database developer at Certkiller .com. You are in the process of
creating a database named HumanResources that will include all employee records
and demographics information. Certkiller .com currently has 2,000 employees and
experiences a yearly turnover rate of approximately 2 percent.
When employees leave Certkiller .com, all of their records have to be kept for
auditing purposes. Changes to employees Demographics information occurs at an
annual rate of about 9 percent. You are not required to maintain a history of
demographics changes.
The following exhibit illustrates the schema for the human resources database.



The scripts that you will use to create the indexes are shown in the exhibit 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].[EmployeeDemographics] PRIMARY KEY
CLUSTERED
([EmployeeID])
WITH FILLFACTOR = 90
GO
You have received instruction from the CIO to conserve disk space, and reduce the
number of times that expansion of the database files needs to occur. You also
discover that all varchar columns are 50 percent full.
Which of the following are the two parameters that you should specify for the
CREATE DATABASE statement? (Choose two)

A. SIZE = 1GB
B. SIZE = 1MB
C. SIZE= 2048KB
D. FILEGROWTH = 20
E. FILEGROWTH = 5%
F. FILEGROWTH = 0

Answer: C, E.

Explanation:
C: We must decide the initial size and the file growth of the database. We calculate the specifics for this database and then add the size of the model database. All new databases are based on the model database. The model database has an initial size of 1 MB. A quick estimate of the user data of this table (see Note below) is that less than 1 MB is needed, so a total size of 2 MB is sufficient. E: As the company experiences a yearly turnover of about 2% of its employee size, and because data on employees who have left the company must be maintained for auditing purposes, a FILEGROWTH of 5% should be an adequate rate of growth of the database.
Note: Estimate size of user data:
EmployeeDemographics table
We estimate the size of one row:
Varchar columns: total of 160. They are less than 50% full, so about 80 bytes would be needed.
Rest of columns. An estimate. Less than 30 bytes.
We conclude: one row = 110 bytes.
Employee table
Varchar columns: total of 150. They are less than 50% full. So about 75 bytes would be needed.
Rest of columns: An estimate. Around 30 bytes.
We conclude: one row = 105 bytes.
Both tables.
There is a one-to-one relationship between the tables, so we can simply add the estimates of the tables.
Both tables: 225 bytes
All rows (2000 rows): 2000*125 bytes = 250,000 bytes
Data pages only fill to 90%, due to fill factor. So we actually need approx. 278, 000 bytes (250,000/0.9).
If we add the size of the model database (1 MB) we see that 2 MB (or 2048 KB) would
be more than enough and a good value for the default file size of the database.

Incorrect answers:

A: 1 GB would be too much. Only 2 MB are needed.
B: The model database has a size of at least 1 MB. A default file size of 1 MB would be too small.
D: FILEGROWTH specifies the growth increment of the file size and cannot exceed the
MAXSIZE setting. If the FILEGROWTH value is not represented as a percentage, then
the value represents a number of MB. A FILEGROWTH of 20 thus represents a FILEGROWTH of 20 MB.
F: FILEGROWTH specifies the growth increment of the file size and cannot exceed the
MAXSIZE setting. If the FILEGROWTH value is not represented as a percentage, then
the value represents a number of MB. SQL Server does not increment a file in units
smaller than 1 MB. Furthermore, a SQL Server does not permit a FILEGROWTH of 0%
or 0 MB.


QUESTION 33
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01.
You are currently planning to implement a SQL Server 2000 database to store the
Certkiller .com business information. You estimate that you will require
approximately 8 GB of disk space to the database and approximately 3 GB of disk
space for the transaction log file. The computer that you intend using to host the
database contains two disks: Disk 0 and Disk 1.
1. Disk 0 is configured with two partitions
Each partition has 7 GB of free disk space.
Drive C and D is located on Disk 0.
Drive C hosts the Windows 2000 Server operating system files and the SQL Server
program files.
1. Disk 1 is configured with two partitions.
Each partition has 7 GB of free disk space.
Drive E and F is located on Disk 1.
You need to ensure that you get the best performance and minimal administration
and thus need a strategy to place the data files and the transactions log files
accordingly.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Create two transaction log files.
B. Create two data files.
C. Place one transaction log file on drive D and the other on drive E.
Then place the data file on drive F.
D. Place one transaction log file on drive E and the other on drive F.
Then place the data file on drive D.
E. Place one data file on drive D and the other on drive E.
Then place the transaction log on drive F.
F. Place one data file on drive E and the other on drive F.
Then place the transaction log file on drive D.

Answer: B, E

Explanation: For optimal write performance, a database's transaction log is best
placed on a separate physical disk. The transaction log is written to sequentially,
isolating the transaction log from any other data or programs improves
performance by allowing the disk read/write head to remain at the point it last
wrote to. The next record will then be written from that point. There are no large
enough partitions to accommodate the data, thus you will need to create two data files in a single file group as this will also minimize administrative overhead.

Incorrect answers:

A, C, D: There is no sense in creating two transaction log files since there is no
benefit to be had in terms of performance. Only one transaction log can be written
to at a time. Only when the first one is full, will a second log file be written to.
Furthermore, by the time the second transaction log file becomes filled with data, the log will have to be truncated and then the first transaction log file will be written to etc.
F: The best write performance in this case can be obtained by placing one data file on drive D and the other on drive E and then placing the transaction log file on drive F.

QUESTION 34
You work as a database developer at Certkiller .com. You are currently designing a
database named CustomerContacts, which will be updated frequently. You have 5
GB of free space on drive C, and the database will be approximately 1 GB in size.
You would like to accomplish the best possible performance for the database.
Which of the following is the script that you should employ 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.

Explanation:
We should create a database that has a size close to the estimated database size of 1GB.
The database will be created by the T-SQL CREATE DATABASE command.
The SIZE argument is used to define the initial size of the database. The default size is 1MB. The default type of the size argument is MB, but in can be specified in KB, GB and TB as well.
We should set the size of the database to 1 GB with the argument, SIZE = 1GB.
We should not set a MAXSIZE. If max_size is not specified, the file grows until the disk is full. There is not any need to set the FILESIZE argument, since the database size is not likely to grow.

Incorrect answers:
A: The SIZE=10 option sets the initial database size to 10MB. This is much too small
and the database would have to grow multiple times by 10% to reach the estimated size
of 1GB. This would decrease performance.
B: The SIZE=10 option sets the initial database size to 10MB. This is much too small
and the database would have to grow multiple times by 5MB to reach the estimated size
of 1GB. This would decrease performance.
Setting the MAXSIZE is not a good idea. The database is about 1GB, maybe slightly
over.
C: The SIZE=100 option sets the initial database size to 100MB. This is much too small and the database would have to grow multiple times to reach the estimated size of 1GB. This would decrease performance.


QUESTION 35
You work as a database developer at Certkiller .com. You have just completed
designing Certkiller .com's SQL Server 2000 online transaction processing database,
which is located on a server named Certkiller -SQL01.
Many of the tables have 1 million or more rows, and all tables have a clustered
index. The tables that are heavily accessed also have at least one non-clustered
index. Two RAID arrays on Certkiller -SQL01 will be utilized to contain the data
files. To guarantee optimal I/O performance, you would now like to place the tables
and indexes.
You start by creating one filegroup on each RAID array.
Of the following options, which is the one that you should perform NEXT?

A. Place tables that are frequently joined together on the same filegroup.
Place heavily accessed tables and all indexes belonging to those tables on different
filegroups.
B. Place tables that are frequently joined together on the same filegroup.
Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.
C. Place tables that are frequently joined together on different filegroups.
Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups.
D. Place tables that are frequently joined together on different filegroups.
Place heavily accessed tables and the nonclustered indexes belonging to those tables on the same filegroup.

Answer: C.

Explanation:
Database performance can be improved by placing heavily accessed tables in one
filegroup and placing the table's nonclustered indexes in a different filegroup on different physical disk arrays. This will improve performance because it allows separate threads to access the tables and indexes. A table and its clustered index cannot be separated into different filegroups as the clustered index determines the physical order of the data in the table. Placing tables that are frequently joined together on different filegroups on different physical disk arrays can also improve database performance. In addition, creating as many files as there are physical disk arrays so that there is one file per disk array will improve performance because a separate thread is created for each file on each disk array in order to read the table's data in parallel. Log files and the data files should also, if possible, be placed on distinct physical disk arrays.

Incorrect Answers:

A: Placing tables that are frequently joined together on the same filegroup will not
improve performance, as it minimizes the use of multiple read/write heads spread across multiple hard disks and consequently does not allow parallel queries. Furthermore, only nonclustered indexes can reside on a different file group to that of the table.
B: Placing tables that are frequently joined together on the same filegroup will not
improve performance, as it minimizes the use of multiple read/write heads spread across multiple hard disks and consequently does not allow parallel queries.
D: Placing heavily accessed tables and the nonclustered indexes belonging to those
tables on the same filegroup will not improve performance. Performance gains can be
realized by placing heavily accessed tables and the nonclustered indexes belonging to
those tables on different filegroups on different physical disk arrays. This will improve performance because allow separate threads to access the tables and indexes.


QUESTION 36
You work as a developer at Certkiller .com. You have recently completed the design
of a SQL Server 2000 database that stores order information. This database
includes the tables named Order and OrderDetails. The database is located on a
computer named Certkiller -SQL01 that has four 9-GB disk drives available for data storage. Certkiller -SQL01 has two disk controllers and each disk controller
controls two of the drives. The Order and OrderDetail tables are frequently joined
in queries.
Which of the following are the two tasks that you need to complete to tune the
performance of the database? (Choose two.)

A. Create a new filegroup on each of the four disk drives.
B. Create the clustered index for the Order table on a separate filegroup from the
nonclustered indexes.
C. Store the data and the clustered index for the OrderDetail table on one filegroup, and
create the nonclustered indexes on another filegroup.
D. Create the Order table and its indexes on one filegroup, and create the OrderDetail table and its indexes on another filegroup.
E. Create two filegroups that each consists of two disk drives connected to the same
controller.

Answer: D, E.

Explanation:

We need to increase performance, and we will concentrate on making the joins between
the Order and the OrderDetail table optimized, since these joins occur frequently.
We have to make two decisions:
* how will the filegroups be created
* how will the data and indexes be distributed on the filegroups
Filegroups
A filegroup is a group of files. We don't have to put a filegroup on every disk. Instead we can use two filegroups, that each consists of two disk drivers connected to the same controller. This will make administration easier. (Answer E.)
Data and indexes The tables are frequently joined. We should therefore separate them to increase performance. (Answer D.)

Incorrect answers:

A: Creating a filegroup in each drive is not a bad idea in general. The point is that
performance will improve a separate thread is created for each file on each disk array in order to read the table's data in parallel.
Here, however, we use the fact that there are two different disk controllers and that we use many joins to find another better solution.
B: There is only one index of concern in the Order table and it is the clustered index. It is not crucial for the performance of the joins to separate the clustered and the non-clustered indexes of the Orders table.
C: Separating the clustered index and the nonclustered indexes is a common practice to improve performance. It is not the best solution in this scenario, however. It would be the next step in improving performance further.


QUESTION 37
You work as the network database administrator at Certkiller .com. The Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You are responsible for administering the SQL Server 2000 database of
Certkiller .com. You have recently created two tables named Inventory which will be
used to register the merchandise and the Invoices table which will be used to
register the purchases. The Invoice and Inventory tables are shown below:



The network users will make use of a data-entry application which prepares a text
file during the day. Each time a new order is registered the application will query
the Inventory table ensuring the requested quantity of products is available before
recording the information in the text file.
Certkiller .com has made configurations ensuring that the data from the text file is
imported in to the Invoices table after business hours. You are required to have the
information about the new orders to be imported as quickly as possible.
What should you do?

A. You should use a BULK INSERT statement with the default options.
B. An ALTER TABLE statement should be carried out against the Inventory table and
drop the FOREIGN KEY constraint. You should then use a BULK INSERT
statement to import the data. Finally recreate the foreign key.
C. An ALTER TABLE statement should be carried out against the Invoices table and
specify the ON INSERT option in the REFERENCES clause. You should then use a
BULK INSERT statement to import the data.
D. You should use a BULK INSERT statement and specify the WITH
CHECK_CONSTRAINTS option.

Answer: A

Explanation: In the scenario you should remember that the FOREIGN KEY
constraint is defined on the SKU column in the Invoices table which references the
SKU column in the Inventory table. The FOREIGN KEY is use to maintain
referential and domain integrity of data. The default option ignores the FOREIGN
KEY constraint to improve performance of the BULK INSERT statement executed.

In correct Answers:

B: This option should not be used in the scenario because the data-entry application usedwill verify the correct product code were entered and return a error so there is no need to drop the FOREIGN KEY.
C: This option should not be considered for use in the scenario because the ON
INSERT is not a valid option for a REFERENCES clause in the CREATE TABLE
or ALTER TABLE statements.
D: This option should not be used in the scenario because if invalid values were
specified in the SKU column the bulk-load operation would fail.


QUESTION 38
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You have received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information. The
information about the business will be stored in a table named Bill. The script
below was used to create the Bill table and its indexes:
CREATE TABLE Bill
(BillNo int PRIMARY KEY,
CustomerID int REFERENCES Customers (CustomerID),
AgentID int REFERENCES Agent (AgentID),
StartDate datetime,
EndDate datetime,
Amount money,
Premiums money)
CREATE INDEX IX_Cust ON Bill (CustomerID)
CREATE INDEX IX_Ag ON Agent (AgentID)
Certkiller .com expects that every month data will be added to the Bill table from a
text file compiled from the products sold by the agents. The amount of data that will
be loaded each month is estimated about 25 to 30 percent of the data in the Bill
table.
During the course of the day you have created a FOR UPDATE trigger defined on
the Bill table. You are required to design the process of loading the new data into
the Bill table which should be imported as quickly as possible.
What should you do?

A. All the indexes that exist on the Bill table should be dropped.
B. In the Bill table definition you should specify DISABLE TRIGGER ALL.
C. In the Bill table definition you should specify NOCHECK CONSTRAINT ALL.
D. In the REFERENCES clause in the Bill table definition you should specify ON
UPDATE CASCADE.

Answer: A

Explanation: In the scenario you should use the BULK INSERT
statement to insert large amounts of data into a table as quickly as possible. You
should also remember that if clustered or non-clustered indexes exist and the
amount of data added is 25 percent or more that all indexes should be dropped.

Incorrect Answers:

B: This option should not be used in the scenario because the definition disables all
triggers on a table and that is not required since bulk load operations ignore them anyway.
C: This option should not be used in the scenario because constraints and triggers do not fire and are not checked during bulk load operations.
D: This option should not be used in the scenario because the data will be added to the referencing table not the referenced table.


QUESTION 39
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You have recently received instruction from the Certkiller .com network CIO to
create a database. You will be responsible for the database that will be used to store the business information of the company. You create the tables shown below:
You are required to select which of the following SQL statements you should use?

A. CREATE TABLE Sales
(InvoiceNo int FOREIGN KEY REFERENCES Invoices (InvoiceNo) ,
ProductID int,
Quantity int,
PRIMARY KEY (InvoiceNo, ProductID) )
CREATE TABLE Customers
(CustomerID int IDENTITY PRIMARY KEY,
ComapnyName nvarchar (30)
Address nvarchar (50)
City nvarchar (20) )
CREATE TABLE Invoices
(InvoiceNo int IDENTITY PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID),
OrderDate datetime)
B. CREATE TABLE Invoices
(InvoiceNo int IDENTITY PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID) ,
OrderDate datetime)
CREATE TABLE Sales
(InvoiceNo int FOREIGN KEY REFERENCES Invoices (InvoiceNo)
ProductID int,
Quantity int,
PRIMARY KEY (InvoiceNo, ProductID) )
CREATE TABLE Customers
(CustomerID int IDENTITY PRIMARY KEY,
CompanyName nvarchar (30),
Address nvarchar (50),
City nvarchar (20) )
C. CREATE TABLE Invoices
(InvoiceNo int IDENTITY PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID),
OrderDate datetime)
CREATE TABLE Customers
(CustomerID int IDENTITY PRIMARY KEY,
ComapnyName nvarchar (30)
Address nvarchar (50)
City nvarchar (20) )
CREATE TABLE Sales
(InvoiceNp int FOREIGN KEY REFERENCES Invoices (InvoiceNo),
ProductID int,
Quantity int,
PRIMARY KEY (InvoiceNo, ProductID) )
D. CREATE TABLE Customers
(CustomerID int IDENTITY PRIMARY KEY,
CompanyName nvarchar (30),
Address nvarchar (50),
City nvarchar (20) )
CREATE TABLE Invoices
(InvoiceNo int IDENTITY PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID),
OrderDate datetime)
CREATE TABLE Sales
(InvoiceNo int FOREIGN KEY REFERENCES Invoices (InvoiceNo),
ProductID int,
Quantity int,
PRIMARY KEY (InvoiceNo, ProductID) )

Answer: D

Explanation: In the scenario you should remember that the Foreign keys enforce
referential and domain integrity of data by means of restricting that values that can
be entered in a foreign key column. You should also keep in mind that each value
entered in a foreign key column should exist in the appropriate table.

Incorrect Answers:

A, B, C: These options should not be used in the scenario because you are allowed to
create the tables in any order if the Primary keys are defined but you should not define the foreign keys.


QUESTION 40
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows 2000 Professional.
You have recently received instruction from the Certkiller .com network CIO to
design a database that will be used by the network users of the Human Resources
department. The database you created includes the Employees and Departments
table shown below:




Certkiller .com has made sure that all the employees were assigned a unique
employee ID and a unique department ID. The Certkiller .com network consists of
900 network users. All the network users belongs to one department. You are
required to enforce this relationship in your database design.
What should you do?

A. A FOREIGN KEY constraint should be created on the Employee table to reference the
Departments table.
B. A PRIMARY KEY constraint should be created on the Departments table to reference
the Employees table.
C. A UNIQUE constraint should be created on the Departments table.
D. A rule should be created to bind it to the Employees table.

Answer: A

Explanation: In the scenario you should always remember that a foreign key
implements many-to-one relationship between two tables or columns of the same
table. To achieve what is required in the scenario you should define a foreign key on
the DepartmentID column to reference the table to the DepartmentID column of the
departments table.

Incorrect Answers:

B: This option should not be used in the scenario because only a foreign key constraint can be used to reference a table and not the PRIMARY KEY constraint.
C: In the scenario there is no stipulation that the values in the columns should be unique so there is no need to use the UNIQUE KEY constraint in the scenario.
D: You should not make use of this option in the scenario because it is recommended by Microsoft that administrators make use of CHECK constraints instead of rules as rules specify restrictions on values.