MSCE Interview Questions and Answers

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


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

CREATE TABLE Examinations
( Vendor int,
ExamID int,
Certification nvarchar (20))
CREATE TABLE TimeTable
( Vendor int,
ExamID int,
ExamDate datetime)
Now you need to define the foreign keys for these tables.
What should you do?

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

Answer: C

070-229

Explanation: Foreign Key constraints are used to enforce referential integrity of data. Online Transaction Processing (OLTP) systems usually has a large number of relatively narrow interrelated tables. Thus for optimal performance regarding query activity, a database should comply with formal criteria called normal forms.
A First Normal Form for a database is when no table has columns that define similar
attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and
also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.

The Third Normal Form for a database is when it complies with the second normal form
and if, in each table, columns that are not covered by the primary key do not depend oneach other.

In this scenario:
1. Each row in the Examinations table represents an examination.
2. Each examination is uniquely identified by the vendor and the examination number
3. Each row in the TimeTable table represents an individual occurrence of the examination that is registered in the Examinations table.
4. For each examination occurrence in the TimeTable table, exactly one examination with the same combination of values in the Vendor and ExamID columns must exist in the Examinations table.
Thus the Vendor and ExamID columns constitute a foreign key on the TimeTable table
that references the Vendor and the ExamID columns in the Examinations table.
You should use the following statements to define the primary and foreign keys on the
Examinations and TimeTable tables:
ALTER TABLE Examinations ADD PRIMARY KEY (ExamID, Vendor)
ALTER TABLE TimeTable ADD
PRIMARY KEY (ExamID, Vendor, ExamDate),
FOREIGN KEY (ExamID, Vendor) REFERENCES Examinations (ExamID, Vendor)


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

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

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

Answer: A

Explanation: Normalization is usually applied to OLTP systems which are usually
subject to extensive additions, deletions and modifications of data because in many
situations normalization improves database performance.

A First Normal Form for a database is when no table has columns that define similar
attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and
also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.

The Third Normal Form for a database is when it complies with the second normal form
and if, in each table, columns that are not covered by the primary key do not depend on each other.

In this scenario:
Customers constitute a separate entity that is independent of all other relevant entities. thus you should create a Customers Table and include in it only the columns that specify information pertinent to the individual customer. This information would include details like name, address and telephone number. In order to uniquely identify each customer and avoid including redundant data, you should include a column for CustomerID in the Customers table.

Incorrect answers:

B: Each reservation is uniquely identified by a reference number. Thus the column for
the reference number should be defined as the primary key for a Reservations table. If you include the customer information in each reservation record presented by a row in the Reservations table, then the columns for the customer's name, address and telephone number would depend on the column for the customer number. This would thus violate the requirements of the third normal form.
C: Each individual occurrence of an examination is uniquely identified by the vendor, by the examination number and the examination date and time, thus a composite primary key should be created on these three columns. The certification, however depends only on the vendor and the examination number and does not depend on examination date and time. Thus if you creates an Examinations table as posed in option C, then the database would violate the requirements of the second normal form.
D: If you included the column for the certification in the Reservations table then the database would fail to comply with the third normal form because then the certification will depend on the vendor and examination number.


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

A. Define a separate FOREIGN KEY constraint on each of the Vendor, ExamID and
ExamDate columns in the Reservations table that references the TimeTable table.
B. Define a separate FOREIGN KEY constraint on each of the Vendor, ExamID and
ExamDate columns in the TimeTable table that references the Reservations table.
C. Define a FOREIGN KEY constraint on the Vendor, ExamID and ExamDate columns
in the TimeTable table that references the Reservations table.
D. Define a FOREIGN KEY constraint on the Vendor, ExamID and ExamDate columns
in the Reservations table that references the TimeTable table.

Answer: D

Explanation: Foreign Key constraints are used to enforce referential integrity of data. Online Transaction Processing (OLTP) systems usually has a large number of relatively narrow interrelated tables. Thus for optimal performance regarding query activity, a database should comply with formal criteria called normal forms.
A First Normal Form for a database is when no table has columns that define similar
attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and
also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.
The Third Normal Form for a database is when it complies with the second normal form
and if, in each table, columns that are not covered by the primary key do not depend on each other.

In this scenario:
1. Each row in Reservations table represents a reservation for a specific occurrence of an examination.
2. Reservations can be made only for those occurrences of the examinations that are
registered in the TimeTable table.
3. Each occurrence is uniquely identified by the examinations and by the date and time when the examination scheduled to take place.
4. Each examination is uniquely identified by a vendor and examination number.
Thus all three these columns in the TimeTable table constitute a composite primary key. None of the columns alone in that table is guaranteed to be unique, and no combination of any two columns in that table is guaranteed to be unique. Hence the foreign key that references the TimeTable table can only reference all three columns together. You should use the following statements to define the primary and foreign keys on the Reservations and TimeTable tables:
ALTER TABLE TimeTable ADD PRIMARY KEY (ExamID, Vendor, ExamDate)
ALTER TABLE Reservations ADD
PRIMARY KEY (ReferenceID),
FOREIGN KEY (ExamID, Vendor, ExamDate)
REFERENCES TimeTable (ExamID, Vendor, ExamDate)


QUESTION 4
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.

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

A. ALTER TABLE Examinations ADD PRIMARY KEY (ExamID)
ALTER TABLE TimeTable ADD PRIMARY KEY (ExamDate)
B. ALTER TABLE Examinations ADD PRIMARY KEY (ExamID, Vendor)
ALTER TABLE TimeTable ADD PRIMARY KEY (ExamID, Vendor, ExamDate)
C. ALTER TABLE Examinations ADD PRIMARY KEY (Certification)
ALTER TABLE TimeTable ADD PRIMARY KEY (ExamID, ExamDate)
D. ALTER TABLE Examinations ADD PRIMARY KEY (ExamID, Vendor,
Certification)
ALTER TABLE TimeTable ADD PRIMARY KEY (ExamID, Vendor, ExamDate)

Answer: B

Explanation
Normalization is usually applied to OLTP systems which are usually subject to
extensive additions, deletions and modifications of data because in many situations
normalization improves database performance.
A First Normal Form for a database is when no table has columns that define similar
attributes and if no columns contains multiple values in a single row.
A Second Normal Form for a database is if it complies with the first normal form; and
also if each column that is not part of a primary key depends on all of the columns that are covered by the primary key in that table and not a subset of the columns that are covered by the primary key.
The Third Normal Form for a database is when it complies with the second normal form
and if, in each table, columns that are not covered by the primary key do not depend on each other.

In this scenario there are two tables:
1. Examinations table represents all examinations that are available/registered
2. TimeTable table represents all individual occurrences of the examinations.
3. The Vendor and ExamID uniquely identify each examination in the Examinations
Table. Because each Vendor may offer one or more examinations towards certain certifications including multiple examinations towards the same Certification, neither the Vendor column alone nor the Vendor and Certification columns together can be a candidate key. Because more than one vendor can offer different examinations with the same ExamID, including examinations towards the same Certification, neither the ExamID column alone not the ExamID and Certification Columns together can be a candidate key. Because each Vendor can offer more than one examination to a single certification, the Certification Column alone cannot be a candidate key. database would not comply with the third normal form requirement because the Certification column depends on the Vendor and ExamID Columns.
To identify an individual occurrence of an examination, it is necessary to identify the examination and the date and time when the examination is schedules. Therefore only all three columns together in the TimeTable Table constitute a candidate key and can thus be defines as a primary key.

QUESTION 5
You work as a developer at Certkiller .com. You are currently designing a database
schema for a large grocery store chain. The partial database schema is illustrated in
the exhibit below.
Exhibit missing!
The script that you utilized to produce the Customers table is displayed in the
following illustration.
Exhibit missing!
The store managers would like to track customer demographics for the purpose of
targeting advertisements and coupon promotions to customers, which will be based
on the past purchases of existing customers. These advertisements and promotions
will also target buying patterns by one or more of the following demographics:
1. Gender
2. Age
3. Postal code
4. Region
The majority of the promotions will be based on gender and age, and customer
demographics information will be retrieved using Queries.
Which of the following is the task that you should complete if you want the query
response time to be as fast as possible?

A. Add indexes on the PostalCode, State, and DateOfBirth columns of the Customers
table.
B. Denormalize the Customers table
C. Create a view on the Customers, SalesLineItem, State, and Product tables.
D. Create a function to return the required data from the Customers table.

Answer: B.

Explanation:
If a database is over-normalized, i.e. the database is defined with numerous, small,
interrelated tables, database performance is reduced. This is because when the database processes the data in numerous, small, interrelated tables, it has to combine the related data, which results in an increase in the database's workload. In these situations, denormalizing the database slightly to simplify complex processes can improve performance. In this scenario, the denormalizing could be accomplished by adding an extra column to the Customers table and use that column to track past purchases.

Incorrect answers:
A: Adding indexes to columns can be beneficial, especially if the keys are used in join operations. These keys often, almost all, have foreign key constraints.
B: However, the PostalCode, State, and DateOfBirth columns are not likely to have
foreign key constraints or to be used in joins. Creating indexes on these columns would not improve performance. Probably performance would decrease, since adding indexes increases the size of the database.
C: Create a view of the tables would not improve performance. The joins of the tables
would still have to be made.
D: A function can be used to simplify the coding of some queries. It cannot, however,
improve performance.


QUESTION 6
You work as a database developer at Certkiller .com. You are currently in the
process of designing a SQL Server 2000 database for a hospital that will contain
physician and patient information. A table named Physicians and a table named
Patients will be included in this database.
Patients generally have a primary physician and a secondary physician. The
hospital requires that the primary physician be listed as the primary physician, and
that the Patients table contain no more than 2 million rows.
Which of the following is the script that you should make use of to create the tables,
if you want to increase I/O performance when data is selected from the tables and
normalize the database to the third normal form?

A. CREATE TABLE Physicians
(
Physicians ID int NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY
CLUSTERED,
LastName varchar(25) NOT NULL,
)
GO
CREATE TABLE Patients
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY
CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NOT NULL,
CONSTRAINT PK_Patients_Physicians1 FOREIGN KEY (PrimaryPhysician)
REFERENCES Physicians (PhysicianID),
CONSTRAINT PK_Patients_Physicians2 FOREIGN KEY (SecondaryPhysician)
REFERENCES Physicians (PhysicianID)
)

B. CREATE TABLE Patients
(
PatientID smallint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY
CLUSTERED,
LastName varchar(25) NOT NULL,
FirstName varchar (25) NOT NULL,
PrimaryPhysician int NOT NULL,
SecondaryPhysician int NOT NULL,
)
GO
CREATE TABLE Physicians
(
PhysicianID smallint NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY
CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
CONSTRAINT PK_Physicians_Patients FOREIGN KEY (PhysicianID) REFERENCES
Patients (PatientID)
)

C. CREATE TABLE Patients
(
PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY
CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY
CLUSTERED,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE PatientPhysician
(
PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysicians
PRIMARY KEY CLUSTERED,
PhysicianID int NOT NULL,
PatientID bigint NOT NULL,
PrimaryPhysician bit NOT NULL,
FOREIGN KEY (PhysicianID) REFERENCES Physicians (PhysicianID),
FOREIGN KEY (PatientID) REFERENCES Patients (PatientID)
)

D. CREATE TABLE Patients
(
PatientID int NOT NULL PRIMARY KEY,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE Physicians
(
PhysicianID int NOT NULL PRIMARY KEY,
LastName varchar (25) NOT NULL,
FirstName varchar (25) NOT NULL,
)
GO
CREATE TABLE PatientPhysician
(
PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID),
PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit
NOT NULL,
CONSTRAINT PK_PatientsPhysicians PRIMARY KEY (PhysicianID, PatientID)
)


Answer: D.

Explanation:
We want to normalize the design. We notice that there is a many-to-many relationship
between the Patients and the Physicians tables. One patient can have many doctors.
("Two" counts as "many" in logical database design.) Doctors can have many patients. So we have to create a third table, PatientPhysician to connect them through two
one-to-many relations, since SQL Server doesn't support many-to-many relations.
We must carefully choose the datatype of the primary keys. No table would require more than about 2 million rows. We can safely use the int datatype for the primary key columns in all the tables, since the maximum value of an int column is 2,147,483,647.

We must also take care in creating foreign keys referencing the primary keys in the
appropriate tables. We must create two foreign keys in the PatientPhysician table: one referencing the Patient table and one referencing the Physicians table. It is also important that the corresponding columns have the same datatype, in this scenario the int datatype.
Note:int datatype
The int datatype represents whole numbers -2,147,483,648 through 2,147,483,647 and
uses a storage size of 4 bytes, while the smallint ranges from -32,768 through 32,767and the tinyint ranges from 0 through 255. It is recommended that the smallest data type that provides a sufficient range of values be used.
Bigint can handle integers in the range of 2^63 (-9223372036854775808) through 2^63-1
(9223372036854775807).

Incorrect answers:
A: This code does not allow the column holding the secondary physician identifier, i.e. the SecondaryPhysician column in the Patient table, to hold nulls. This will create database integrity problems when the patient does not have a secondary physician. Furthermore, the database is not normalized to the third normal form, as
PrimaryPhysician and SecondaryPhysician columns in the Patient table are not dependent upon the PRIMARY KEY placed on the PatientID column.
B: This code does not allow the column holding the secondary physician identifier, i.e. the SecondaryPhysician column in the Patient table, to hold nulls. This will create database integrity problems when the patient does not have a secondary physician. This code also uses the smallint data type to uniquely identify the patients in the PatientID column in the Patient table. As this data type has a range of -32,768 through 32,767, it can only apply unique identifiers to 65, 535 patients, which would be insufficient since the hospital has more than 2 million patients. Furthermore, the database is not normalized to the third normal form, as PrimaryPhysician and SecondaryPhysician columns in the Patient table are not dependent upon the PRIMARY KEY placed on the PatientID column.
C: There is no need to use the bigint datatype. A column with the int datatype would be able to hold more than 2000 million distinct values, and only around 2 million rows are required. We should not use a larger datatype than necessary.


QUESTION 7
You work as a database developer at Certkiller .com. You are stationed at a
Certkiller .com technical training center. Administrative employees at the technical
training center currently keep records of students, instructors, courses, and
classroom assignments only on paper. Certkiller .com would like to do away with this
method by developing a database to record this information.
You are asked to design the tables for this database, and come up with the design
shown in the exhibit below.



Which of the following is the option that you should take if you want to promote
quick response times for queries and minimize redundant data?

A. Create a new table named Instructors. Include an InstructorID column, and
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.
D. Remove the ClassroomID column, and base the PRIMARY KEY constraint on the
ClassroomNumber and ClassTime columns.

Answer: A.

Explanation:
A normalized database is often the most efficient. This database design is not normalized.
The data on the instructors are contained in the Courses table. This would duplicate
information whenever an Instructor has more than one course; InstructorName and
OfficePhone would have to be registered for every course.
We normalize the database in the following steps:
* Create a new table called Instructors.
* Create a new column in the Instructors table called InstructorID. This is the given
candidate for Primary key.
* Add the InstructorName and OfficePhone columns to the Courses table.
* Remove the InstructorName and Office Phone columns from the Courses table (not in
scenario).
* Add the InstructorID column to the Courses table. This column will later be used to
create a foreign key constraint to the InstructorID column of the Instructors table.

Incorrect answers:

B: Moving all columns from the Classroom table to the Courses table would only make
matters worse. Every student's data would have to be entered for every course that
student took. We would have an even more denormalized database.
C: By removing the Primary Key constraint on the CourseID of the Courses table and
replacing it with a composite Primary Key constraint on the CourseID and CourseTitle
columns would make the database more denormalized. It would not allow two courses
with the same CourseTitle, so every semester (or year) the school would have to invent new names for the courses.
D: Changing the Primary Key constraint on the Classroom table would not improve the
situation; on the contrary, the ClassroomID column would be redundant.
This procedure doesn't address the problem with the InstructorName and OfficePhone
columns in the Courses table.


QUESTION 8
You work as a developer at Certkiller .com. You are currently designing a database
that will contain customer orders. Customers will now be able to order multiple
products every time they place an order. The database design is illustrated in the
exhibit below.



After reviewing your database design, you decide to promote quick response times
for queries and lessen redundant data.
Which two of the following are the tasks that you should complete to achieve this?
(Choose two.)

A. Create a new order table named OrderDetail, and then add OrderID, ProductID, and
Quantity columns to it.
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: A, C.

Explanation: From a logical database design viewpoint we see that there is some
problem with the relationship between the Orders tables and the Products table. We
want to have the following relationship between those two tables:
* Every order contains one or several products.
* Every product can be included in 0, 1, or several orders.
In short, we want a many-to-many relationship between the Orders and Products table,
but SQL Server doesn't allow many-to-many relationship, so we have to implement the
many-to-many relation via two one-to-many relations by using an extra table that will
connect the Orders and the Products table. We do this as follows:
* Create a new table OrderDetail.
* Add the OrderID, ProductID, and Quantity columns to the OrderDetail table.
* Remove the Quantity and the ProductID columns from the Orders table.
* Create a foreign key constraint on the OrderID column in the OrderDetail table
referencing the OrderID column in the Orders table.
* Create a foreign key constraint on the ProductID column in the OrderDetail table
referencing the ProductID column in the Products table.
We have now normalized the database design and the benefits are faster query response
time and removal of redundant data.
Another less theoretical line of thought is the realization that the OrderID, ProductID andQuantity columns would be of primary concern in the transaction, thus it would be beneficial to create a new table that contains these columns and to remove the Quantity column from the Order table to reduce redundant data.

Incorrect answers:

B: Making a composite primary key out of the OrderID and ProductID columns of the
Orders table is not a good idea. From a logical database design standpoint the ProductID doesn't restrict the non-key columns of the Orders table at all, and it should not be part of the Primary Key. Instead, the Orders table should be split into two tables.
D: Creating a UNIQUE constraint on the OrderID column of the Orders table ensures
that the values entered in the OrderID column are unique and would prevent the use of
null values. It doesn't, however, address the problem of the relationship between the
Orders and Products table, which have to be adjusted.
E: Moving the UnitPrice column from the Products table to the Orders table would be
counterproductive. The UnitPrice column stores the price of a product and belongs to the Products table and shouldn't be moved to the Orders table. The only way to fix the problem with the Products and Orders table is to add a new table to connect them.


QUESTION 9
You work as a database developer at Certkiller .com. Certkiller .com has recently
acquired a company called SG Publishers. Certkiller .com then asks you to design a
database for SG Publishers. Your design is displayed in the exhibit below.



When you review the database design, you find that it is necessary to promote quick
response times for queries and minimize redundant data.
What should you do?

A. Create a new table named CustomerContact, and then add CustomerID, ContactName,
and Phone columns to this table.
B. Create a new composite PRIMARY KEY constraint on the OrderDetails table, and
then include the OrderID, ProductID, and CustomerID columns in the constraint.
C. Remove the PRIMARY KEY constraint from the OrderDetails table, and then 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, and then add a Quantity
column to the Orders table.

Answer: D.

Explanation:
A normalized database design is the best starting point for an efficient design. By
examining the exhibit we see that the CustomerID column in the OrderDetails table looks out of place. The CustomerID column belongs to the Customer table and to the tables that reference the CustomerID column in the Customers table (here, the Orders table) with a foreign key constraint. Removing the CustomerID column from the OrderDetails table would normalize the design, reduce redundancy, and improve performance.

Incorrect answers:

A: Adding a new table would increase the possibilities to store data, but it would not improve query performance. We should normalize the design before adding more tables.
B: The primary key of the OrderDetails table already uniquely defines the rows in the
table. Adding another column to the primary key would increase the size of the index.
This would most likely decrease performance.
Note: When a PRIMARY KEY constraint is specified for a table, SQL Server 2000
enforces data uniqueness by creating a unique index for the primary key columns. This
index permits fast access to data when the primary key is used in queries. If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
C: Removing the PRIMARY KEY constraint from the OrderDetails table and using an
IDENTITY column to create a surrogate key for the OrderDetails table would not
improve query times on this table. It is thus inappropriate
E: Removing the Quantity column from the OrderDetails table and adding it to the
Orders table would denormalize the database design. Quantity is an attribute of the
OrderDetails table (quantity of ordered items), not an attribute of Orders.


QUESTION 10
You work as a database developer at Certkiller .com. The Sales personal at
Certkiller ,com would like to track sales by multiple parameters such as age, country,
etc for the purpose of spotting relevant sales patterns.
You have to join four tables, from the highly normalized database, to generate this
information.
Which of the following describes the best How to make the query response time as
fast as possible?

A. Create a view on the four tables. Create an index on the view.
B. Denormalize the database design.
C. Further normalize the table.
D. Add more indexes on the relevant columns in the tables that are joined.
E. Improve performance by using functions to implement the query.

Answer: B.

Explanation:
If a database is over-normalized, i.e. the database is defined with numerous, small,
interrelated tables, database performance is reduced. This is because when the database processes the data in numerous, small, interrelated tables, it has to combine the related data, which results in an increase in the database's workload. In these situations, denormalizing the database slightly to simplify complex processes can improve performance.

Incorrect answers:

A: Create a view of the tables would not improve performance. The joins of the tables
would still have to be made.
C: Normalizing the database further would decrease the performance of the query.
D: Adding indexes to columns can be beneficial, especially if the keys are used in join operations. These keys often, almost all, have foreign key constraints. But it is not the best solution.
E: A function can be used to simplify the coding of some queries. It cannot, however,
improve performance.


QUESTION 11
You work as the database administrator at 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 data 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. The manager of the Sales department will run queries against the Invoices table to
calculate the commission for each sales person. To ensure the success of the queries,
you want to enforce the values that the SalesPersonID column in the Invoices table
will accept.
What should you do?

A. Create a foreign key constraint that references the CK_Sales.SalesPersons table.
B. Create a check key constraint that references the CK_Sales.SalesPersons table.
C. Create a trigger that references the CK_Sales.SalesPersons table.
D. Create a unique constraint on the SalesPersonID column of the CK_Sales.Invoices table.

Answer: A

Explanation: A foreign key constraint that references the CK_Sales.SalesPersons
table will ensure that only SalesPersonIDs that exist in the SalesPersons table may
be used in the Invoices table.

Incorrect answers:

B: A check constraint defines the value that can be entered into a column.
C: A trigger fires when a UPDATE, INSERT or DELETE statement is run against a
table. This is not what is required.
D: A unique constraint ensures that a value in a column only appears once in that
column. This is not what is required.


QUESTION 12
You work as a database administrator 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 2003 and the database servers run Microsoft SQL Server 2005.
Your job description at Certkiller .com includes the administration of the database
server named Certkiller -DB01.
Each student is provided with a unique student ID value. This value is used in the
Students, StudentAddress, StudentAccounts, and StudentCourse tables.
1. This value is used as the primary key in the Students and StudentAddress tables.
2. A separate account number table is used as the primary key in the
StudentAccounts table.
3. The column named StudentID is used in each table.
4. The account number column in StudentAccounts is named StAccnt.
The StudentAccounts table was added to the database only recently. Account
information is being added manually. There must be a one-to-one relationship
between the Students and StudentAccounts tables. In the StudentAccounts table,
each student ID should be used and each ID must appear only once in the table.
You need to ensure that these requirements are met so as to avoid potential conflicts
in the tables. You now need to take a decision as to which action you can take to
carry out this task with the least amount of processing overhead.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Modify the StudentAccounts primary key to include both StudentID and StAccnt as
key columns.
B. In both tables, create a foreign key in StudentAccounts that references the Students
table using the StudentID as the key column.
C. In Students, create a stored procedure to enter student accounts that verify the
StudentID value and verify that the value does not already exist in StudentAccounts.
D. In the StudentAccounts table, create a unique constraint with the StudentID as the key column.
E. Add an additional table to manage the relationship between Students and StudentID
with three columns; the StudentID from Students, the StudentID from StudentAccounts,
and StAccnt from StudentAccounts.

Answer: B, D

Explanation: You will need to create a unique constraint in the StudentAccounts table
with StudentID as the key column. And you should use the foreign key constraint to
ensure that the StudentID value in StudentAccounts exists in Students. This unique
constraint will ensure that each StudentID value in StudentAccounts is unique.

Incorrect answers:

A: Modifying the StudentAccounts primary key to include both StudentID and StAccnt
as key columns will not meet the requirements. With this option you can have duplicate StudentID values, as long as each is associated with a different StAccnt value.
C: Creating a stored procedure in Students to enter student accounts that verify the
StudentID value and verify that the value does not already exist in StudentAccounts will meet the stated requirements, but this solution has a higher processing overhead than using a foreign key and unique constraints.
E: Adding an additional table to manage the relationship between Students and StudentID with three columns; the StudentID from Students, the StudentID from StudentAccounts, and StAccnt from StudentAccounts; does nothing to meet her equirements and just adds unnecessary overhead to the database. You would use this type of solution if you need to support a many-to-many relationship.


QUESTION 13
You work as a database developer for Certkiller .com. Certkiller .com is financial
services provider and its network consists of a single Active Directory domain
named Certkiller .com. All servers on the Certkiller .com network run Microsoft
Windows Server 2000 and the database servers run Microsoft SQL Server 2000.
You are designing a database for Certkiller .com. Certkiller .com wants to register all the investments of their customers in a table named Investments as illustrated in the exhibit below:
Exhibit:



You are required o ensure that each EquityAmount can be uniquely identified and
attributes to a single owner.
What should you do? (Choose all options that are correct.)

A. Create a FOREIGN KEY constraint on the InvestmentID column.
B. Create a FOREIGN KEY constraint on the InvestorID column.
C. Create a PRIMARY KEY constraint on the InvestmentID column.
D. Create a PRIMARY KEY constraint on the InvestorID column.

Answer: B, C

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

In this scenario:
Each row in the Investments Table represents a investment account. A natural choice to a primary key in the Investments Table is the InvestmentID column.
A value in the InvestorID identifies the Investor. Because no other information regarding the Investor is contained within the Investments Table, it would be reasonable to make the assumption that information regarding the investor is stored in another table.
Also the database should include a separate table that may be named Investors in which each row represents a different investor, each value in the InvestorID column in the Investments Table should reference the row for a specific investor in the Investors Table; this defines a relationship between the Investments and Investors Tables. Columns that define relationships between two tables are referred to as foreign keys. A foreign key relationship can be enforced by a FOREIGN KEY constraint to be defined on a foreign key column or set of columns. For a FOREIGN KEY constraint to be defines on a column or set of columns, the values in the references column or set of columns in the references table must be unique. The uniqueness of the values in the references column or sets of columns can be enforces either by a PRIMARY KEY constraint or by a UNIQUE constraint,
In this case the Investors table should contain a primary key, e.g. a InvestorID column. For each value in InvestorID column, exactly one matching value must exist in the InvestorID column in the Investors Table. The FOREIGN KEY constraint will enforce such a relationship.


QUESTION 14
You work as a database developer at Certkiller .com. Certkiller .com has asked you to
design a database to record information about potential consultants. You create a
table named Candida CertK ills for the database, which is illustrated in the exhibit



Which of the following explains how you should 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.

Explanation:
A PRIMARY KEY constraint enforces the uniqueness of data entered in specified
columns that do not allow null values. Duplicate data values are allowed in a specific column if a PRIMARY KEY consists of more than one column, but each combination of data values from all the columns in the PRIMARY KEY must be unique. In this scenario we are required to create a PRIMARY KEY constraint that will uniquely identify the skills of potential consultants. We place the data of each potential consultant in the CandidateID column and the corresponding data pertaining to their skills in the SkillID column. Thus we would require a PRIMARY KEY constraint on the CandidateID and SkillID columns.

Incorrect answers:

A: A PRIMARY KEY constraint placed on the CandidateID would only enforce the
uniqueness of the potential consultant. It will not uniquely relate the potential candidate data to the potential candidate's skill.
B: Creating a PRIMARY KEY constraint on the CandidateID and DateLastUsed
columns would enforce the uniqueness of the combination of data in the CandidateID and DateLastUsed columns. It will not enforce the uniqueness of the combination of data in the CandidateID and SkillID columns and would thus not meet the requirements set in this scenario.
D: Creating a PRIMARY KEY constraint on the CandidateID, SkillID, and
DateLastUsed columns would enforce the uniqueness of the combination of data in the
CandidateID, SkillID and DateLastUsed columns. However, the scenario does not require
us to uniquely identify the data in the DateLastUsed column. Therefore Answer C would
be the best answer.


QUESTION 15
You work as a developer at Certkiller .com. You are in the process of creating a table named Orders for Certkiller .com's SQL Server 2000 database. This database will be located on a server named Certkiller -SQL01. Every order includes the following:
1. OrderID
2. OrderDate
3. CustomerID
4. ShipperID
5. ShipDate
When customer services representatives take orders, they have to enter the
OrderDate, CustomerID, and ShipperID. The OrderID must be automatically
created by the database and has to be unique. Orders can only be taken from
existing customers, and shippers can only be chosen from an existing set of shippers.
Once the customer service representatives complete an order, it is sent to the
shipping department for final processing. When the order is shipped, the shipping
department enters the ship date.
Of the following, which is the script that you should make use of 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 Customer (Customer
ID),
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 Customer (Customer
ID),
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 Customer (Customer
ID),
ShipperID int NULL,
ShipDate datetime Null
)
D. CREATE TABLE Orders
(
OrderID uniqueidentifier PRIMARY KEY NOT NULL,
OrderDate datetime NOT NULL,
CustomerID char(5) NOT NULL FOREIGN KEY REFERENCES Customer (Customer
ID),
ShipperID int NOT NULL FOREIGN KEY REFERENCES Shippers(ShipperID),
ShipDate datetime Null
)

Answer: B.

Explanation:
In this scenario, the rows must be identified from the OrderID column, hence OrderID
must be designated with the identity property. Furthermore, the value inserted into the OrderID column must be generated automatically and must be unique. This can be
accomplished by assigning a seed, which is the value that is used for this column in the first row of the table, and an increment, which is the value that must be added to the identity value of the previous row that was loaded. This is expressed in the form identity (seed, increment). Either the seed and increment must be specified, or neither must be specified. The ShipDate column must allow nulls as it is to be filled in when the order is shipped, hence when the order is completed. In addition, as the OrderID column will be searched in order to enter the ShipDate value at a later stage, the OrderID must be the FOREIGN KEY. Because orders can only be taken from existing customers and shippers must be existing shippers, FOREIGN KEY constraints must exist against the CustomerID in the Customer table and against the ShipperID column in the Shippers table.

Incorrect answers:
A: The uniquely identified OrderID column in this script will not be generated
automatically as it does not contain a seed and an increment in its definition.
C: The code in this solution does not ensure referential integrity between the ShipperID column in the Orders table and the ShipperID column in the Shippers table. Referential integrity is ensured through the use of FOREIGN KEY constraints and is required to ensure that orders are only taken from existing customers and to ensure that shippers are selected only from the existing set of shippers.
D: The uniquely identified OrderID column in this script will not be generated
automatically as it does not contain a seed and an increment in its definition.


QUESTION 16
You work as a database developer at Certkiller .com. Certkiller .com has been asked
to develop a database for a local dog breeder. The purpose of this database is to
store information about the breeder's dogs. You make use the script, displayed
below, to create a table named Dogs:
CREATE TABLE[dbo].[Dogs]
(
[DogID] [int] NOT NULL,
[BreedID] [int] NOT NULL,
[DateofBirth] [datetime] NOT NULL,
[WeightAtBirth] [decimal] (5, 2) NOT NULL,
[NumberOfSiblings] [int] NULL,
[MotherID] [int] NOT NULL,
[FatherID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Dogs] WITH NOCHECK ADD
CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED
(
[DogID]
) ON [PRIMARY]
GO
The dog breeder requires you to make sure that each dog has a valid value for the
MotherID and FatherID columns.
Which of the following is an action that you should execute to enforce this rule,
while minimizing disk I/O?

A. Create an AFTER INSERT trigger on the Dogs table that rolls back the transaction of the MotherID or FatherID column is not valid.
B. Create a table-level CHECK constraint on the MotherID and FatherID columns.
C. Create two FOREIGN KEY constraints: one constraint on the MotherID column and
one constraint on the FatherID column. Specify that each constraint reference the DogID column.
D. Create a rule and bind it to the MotherID. Bind the same rule to the FatherID column

Answer: C.

Explanation: From logical database design reasoning we can see that there is a
one-to-many relationship between the DogID column and the MotherID column:
* Every dog has one specific mother (and father).
* Every mother (or father) can have 0, 1, or several children (puppies).
One-to-many relations are implemented by foreign key constraints in most RDBMS like
SQL Server. Two foreign key constraints, one for each column, can be used to ensure
that a new row contains proper values for the MotherID and FatherID. Foreign keys
constraints that reference a column in the same table, usually the primary key, are not unusual in relational database design. This is called self-referencing and is mostly used to define hierarchical structures, for example family (or dog!) relations like in this example. Note: A FOREIGN KEY constraint is placed on a column or combination of columns to establish and enforce a referential integrity between the data in two tables in a database. A link is created between two tables by adding the column or columns that hold one table's PRIMARY KEY values or UNIQUE constraint to a second table. This column becomes a foreign key in the second table and can be created when that table is created or altered. A table can also be self-referencing - that is, the foreign key can reference one or more columns in the same table.

Incorrect answers:

A: A trigger can be constructed that accomplish the same thing as the preferred solution with foreign keys. But there are drawbacks with triggers compared to foreign keys:
1. They must be coded.
2. They are not as efficient as foreign key constraints. In particular a trigger for this scenario would require a select dogs clause and use some mechanism to decide if there are corresponding rows that match the MotherID and FatherID columns respectively. Note on triggers: SQL Server 2000 uses two primary mechanisms, constraints and triggers, to enforce business rules and data integrity. Triggers are a special class of stored procedures that execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. It can reject or accept each data modification transaction as a whole. By using a correlated subquery in a trigger, such as an AFTER INSERT trigger, the trigger can examine the modified rows one by one. This will prevent a complete rollback of all data modifications when some of the data changes are not acceptable; instead, the trigger deletes only the rows containing unacceptable data.
B: One-to-many relations cannot be enforced by CHECK constraints.
Note: CHECK constraints are used to enforce domain integrity by checking that invalid
values are not entered in a column. They are similar to FOREIGN KEY constraints in
that they control the values that can be placed in a column. FOREIGN KEY constraints
are able to get the list of valid values from another table, while CHECK constraints
determine the valid values from a logical expression that is not based on data in another column or table. It is possible to apply multiple CHECK constraints to a single column; these are applied in the order in which they were created. It is also possible to apply a single CHECK constraint to multiple columns by creating them at the table level.
D: One-to-many relations cannot be enforced by rules.
Note: Rules are used in cases where backward compatibility is required. They perform
the same function as CHECK constraints. CHECK constraints are the preferred, standard
way to check or restrict the values in a given column. CHECK constraints 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.


QUESTION 17
You work as a database developer at Certkiller .com. You are in the process of
creating a database that will store Certkiller .com's order information. Orders will be entered in a client/server application. A unique order number has to be assigned in ascending order, every time a new order is entered. An average of 10, 000 orders
will be entered each day. You start by creating a new table named Orders, and then adding an OrderNumber column to this table.
Which of the following is the task that you should complete 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.

Answer: B.

Explanation:
In MS SQL Server 2000, identifier columns can be implemented by using the IDENTITY
property which allows the database designer to specify an identity number for the first row inserted into the table and an increment to be added to successive identity numbers. When inserting values into a table with an identifier column, MS SQL Server 2000 automatically generates the next identity value by adding the increment to the previous identity value. A table can have only one column defined with the IDENTITY property, and that column must be defined using the decimal, int, numeric, smallint, bigint, or tinyint data type. The default increment value by which the identity number grows is
1. Thus identity values are assigned in ascending order by default.

Incorrect answers:

A: MS SQL Server 2000 uniqueidentifier is used during table replication. In this process a unique column for each row in the table being replicated is identified. This allows the row to be identified uniquely across multiple copies of the table.
C: Functions are subroutines that encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic. SQL Server 2000 supports two types of functions: built-in functions and user-defined functions. There are two types of user-defined functions: scalar user-defined functions, which return a scalar value, and inline user-defined functions, which return a table.
D: The creation of additional tables to track order number is inappropriate in this
scenario. It would require cascading FOREIGN KEY constraints with the OrderNumber
column in the Orders table, which would require manual updating before the
OrderNumber column in the Orders table could be updated automatically.


QUESTION 18
You work as a database developer at Certkiller .com. Certkiller .com is in the
insurance business. Information regarding Certkiller .com's insurance policies is
currently stored in a SQL Server 2000 database on a server named
Certkiller -SQL01. You use the script shown below to create a table named Policy
for this database.
CREATE TABLE Policy
(
PolicyNumber int NOT NULL DEFAULT (0),
InsuredLastName char (30) NOT NULL,
InsuredFirstName char (20) NOT NULL,
InsuredBirthDate datetime NOT NULL,
PolicyDate datetime NOT NULL,
FaceAmount money NOT NULL,
CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
)
Every time Certkiller .com sells a new policy, it has to be assigned a unique policy
number. As soon as a new policy is entered, the database must assign a new policy
number.
Which of the following will allow you to achieve this objective?

A. Create an INSTEAD OF INSERT trigger to generate a new policy number, and
include the policy number in the data inserted into the table.
B. Create an INSTEAD OF UPDATE trigger to generate a new policy number, and
include the policy number in the data inserted into the table.
C. Create an AFTER UPDATE trigger to generate a new policy number, and include the
policy number in the data inserted into the table.
D. Replace the DEFAULT constraint with an AFTER INSERT trigger that generates a
new policy number and includes the policy number in the data inserted into the table.

Answer: A.

Explanation:
This scenario requires a constraint that ensures the uniqueness of the data entered into reach row of a particular column. This can be achieved by means of triggers. An
INSTEAD OF INSERT trigger can be used to replace of the regular action of the
INSERT statement. In this scenario, it can replace the data being inserted with a unique numeric value.

Incorrect answers:
B: An INSTEAD OF UPDATE trigger can be used to replace of the regular action of the
UPDATE statement on a table or a view. However, when a new policy is sold, new data
will be inserted rather than updated in the table. Thus, the trigger will not fire, as no UPDATE is applied to the table. An INSTEAD OF INSERT trigger should be used
instead.
C: An AFTER UPDATE trigger can be used to execute a function after an UPDATE is
applied to a table. However, when a new policy is sold, new data will be inserted rather than updated in the table. Thus, the trigger will not fire, as no UPDATE is applied to the table. Furthermore, AFTER triggers are not executed if a constraint violation occurs, as it is executed after the constraint is checked. Thus, AFTER triggers cannot be used for the prevention of constraint violations as in this scenario.
D: AFTER triggers are not executed if a constraint violation occurs, as it is executed after the constraint is checked. If we remove the DEFAULT statement on the PolicyNumber column, the NOT NULL constraint will prevent the AFTER INSERT trigger from firing when a null value is entered in the column. Thus AFTER triggers cannot be used for the prevention of constraint violations as in this scenario.


QUESTION 19
You work as a database developer at Certkiller .com. Certkiller .com is in the
insurance business. You are in the process of designing a database that will include
information about persons covered by insurance policies. You start by creating a
table named Insured, using the script shown in the exhibit below.



The CIO informs you that a person covered by an insurance policy has to be
uniquely identified by name and birth date. He also informs you that more than one
person can be covered by an insurance policy, but a person cannot be covered more
than once by the same insurance policy.
Which of the following is the task that you should carry out to ensure that the
database correctly enforces the relationship between insurance policies and the
persons covered by insurance policies?

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.

Explanation:
UNIQUE constraints enforce the uniqueness of the values in a set of columns and ensure that no duplicate values are entered in specific columns that do not participate in a PRIMARY KEY. Primary keys also enforce uniqueness, but they do not allow null values. Although both UNIQUE constraints and PRIMARY KEY constraints enforce uniqueness, UNIQUE constraints are recommended over PRIMARY KEY constraints
when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key, as multiple UNIQUE constraints can be defined on a table, while only one PRIMARY KEY constraint can be defined on a table.

Incorrect answers:

A: We only want to enforce uniqueness. We don't have to use a PRIMARY KEY
constraint. Unless some specific performance gain is achieved by adding the column to
the primary key, we should use an UNIQUE constraint instead. Too many columns in an
index would decrease performance. Note: PRIMARY KEY constraints identify the column or set of columns whose values uniquely identify a row in a table. No two rows in a table can have the same PRIMARY KEY value or NULL values. Using a small, integer column as a primary key is recommended and each table should have a PRIMARY KEY. A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key of which one is chosen to be the primary key.
C: CHECK constraints cannot be used to force uniqueness in a column. They enforce
domain integrity by limiting the values that can be placed in a column. A CHECK
constraint specifies a Boolean search condition that is applied to all values entered for the column. All values that do not evaluate to TRUE are rejected.
D: There can only be one clustered index in a table, and in this table the primary key is clustered. We cannot add another clustered index.
Note: A clustered index determines the physical order of data in a table. As a result, a table can contain only one clustered index, but the index can comprise multiple columns. A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent.

QUESTION 20
You work as a database developer at Certkiller .com. The exhibit below shows a table
design that you recently created.



Which two of the following are the columns that should you use to uniquely identify
the skills for each Certkiller .com consultant? (Choose two)

A. CandidateID
B. SkillID
C. DateLastUsed
D. Proficiency

Answer: A, B

Explanation:
Both CandidateID and SkillID are required.