发信人: powerbuilder()
整理人: reynolds(2001-02-07 12:52:47), 站内信件
|
1. Your users report time when they modify data in your transactio
n
process application. Response time is excellent when users
retrieving data. All transactions are short & follow standard
guide. Your monitor is blocking locks & you find out what caus
es?
a. The transaction log is placed on busy disk drive.
b. The transaction log is nearly full.
c. The checkpoint process is set too short.
d. The tempDB database is too small.
e. The tempDB database is on the same physical disk
drive as the database.
Answer: a
2. A shipping company has database application. When each vesse
l is
unloaded at its destination, the INVENTORY table is created
&
Arrived_Quantity column is updated in database. Each shipme
nt is
identified by Shipment_ID column, each item is identified by
Item_Number column. You want to make sure the update of the
Arrived_Quantityis fast as possible. What do you do?
a. Create nonclustered index on the Shipment_ID column, the
Item_Number column & Arrived_Quantity column.
b. Create clustered index on the Shipment_ID column, the
Item_Number column & Arrived_Quantity column.
c. Create clustered index on the Shipment_ID column & the
Item_Number column.
d. Create nonclustered index on the Shipment_ID column, &
the Item_Number column.
Answer: c
3. You automate the backup & recovery process for your database
Application, after database is restored, you discover the
queries that use the FREETEXT & CONTAINS keyword no longer
return the expected rows. What do you do?
a. After query to use the LIKE keyword instead of the
FREETEXT & CONTAINS keyword.
b. After query to use the FREETEXTTABLE & CONSTAINSTABLE
keyword instead of the FREETEXT & CONTAINS keyword
c. Add the database FULLTEXT catalog to backup job &
recovery job.
d. Add the job to the restoration process to recreate &
populate the FULLTEXT catalog.
Answer: d
4. EXPERIMENT table shows:
CREATE TABLE Experiment (
ExperimentID char (32)
Description Text,
Status Integer,
Results Text )
You write the following:
SELECT * FROM Experiment WHERE CONTAINS (DESC, 'angina')
You received an empty result set. Chose 2.
a. Ensure that there is a nonunique index on Description
column of Experiment table.
b. Ensure that there is a clustered index on Results column o
f
Experiment table.
c. Create FULLTEXT catalog that includes the Experiment table
.
d. Create a scheduled job to populate the FULLTEXT catalog.
Answer: c & d
5. The WHERE clause of query includes Search on Column A, Column
B,
Column C. Column A is nearly identical in all row. Column
B is
about 50%, Column C is about 10% of the row.
a. Create composite clustered index on Column A, Co. B, Co. C.
b. Create composite clustered index on Column C, Co. B, Co. A.
c. Create composite nonclustered index on Column A, Co. B, Co.
C.
d. Create composite nonclustered index on Column C, Co. B, Co.
A.
e. Create composite clustered index on Column A, separate
nonclustered index on Column B & C.
f. Create separate nonclustered index on each column.
Answer:d
6. Your database table:
CREATE TABLE SalesInformation
(SalesInformation_ID int Identify (1,1)
Not NULL
SalesPerson_ID int not NULL
Region_ID int not NULL
Receipt_ID int not NULL
Salesamount money)
You want to populate the table with data from existing applic
ation
that has numeric Primary Key. In order to maintain referenti
al
integrity,you want to preserve the value of original Primary
Key.
What can you do to populate the table.
a. Set IDENTIFY_INSERT option to OFF & then insert the
data by using a SELECT statement that has a column list.
b. Set IDENTIFY_INSERT option to ON & then insert the
data by using a SELECT statement that has a column list.
c. Insert data by using a SELECT statement that has a column l
ist,
and then ALTER TABLE to add Foreign Key.
d. Insert data by using a SELECT statement that has a column l
ist,
and then ALTER TABLE to add Primary Key.
Answer: b
7. Table:
Member_Ship table
Committee table
Address table
When a person resigns from organization, you want to delete t
he
Membership row & have all related rows to be automatically
removed.
a. Create delete trigger on Member_Ship table that deletes any
rows in Committee table, Address table & Phone table that ref
erence
The Primary Key in Member_Ship. Do not place a FK CONTAINS
on Committee, Address & Phone.
b. Create delete trigger on Member_Ship table that deletes any r
ows in
Committee table, Address & Phone table that
reference the Foreign Key on Committee, Address & Phone.
c. Place a PK CONTAINS on Member_Ship table with FK CONTAINS
on Committee, Address & Phone.
d. Place PK on Member_Ship.
Place FK on Committee, Address & Phone that reference the
PK in Member_Ship table.
e. Create delete trigger on Committee, Address & Phone will fire
when
FK CONTAINS are violated.
Answer: a
8. DBCC SHOWCONTIG:
Pages Scanned 354
Extents Scanned 49
Extent Switches 253
Avg. pages per extent 7.2
Scan Density 17.79% [45:94]
Extent Scan Fragmentation 82.21%
Avg. Bytes Free per Page 485.2
Avg. Page Density (full) 94.01%
How to improve the query performance?
a. Update statics on clustered index.
b. Change the row size to fit efficiently on a page.
c. Rebuild clustered index with fill factor 100
d. Rebuild clustered index with fill factor 25
e. Rebuild clustered index with fill factor 75
Answer:e
9. DBCC SHOWCONTIG:
Pages Scanned 158
Extents Scanned 21
Extent Switches 20
Avg. pages per extent 7.5
Scan Density 95.24% [20:21]
Extent Scan Fragmentation 4.76%
Avg. Bytes Free per Page 408.4
Avg. Page Density (full) 94.95%
This output tells you about the data stored.
Select all apply.
a. The table is not externally fragmented.
b. The table is not internally fragmented.
c. The number of Extent Switches is excessive.
d. The row size does not efficiently fit on a page.
e. The IAM page does not reflect the actual extent usage.
Answer: b & d
10.Vendors around the world must be able to authorize purchase in
in less than 30 second, 24 hrs a day, 7 days a week.
You implement the application to meet all requests.
a. Implement a Client/Server Architecture in which Vendors obt
ain
an authorized code from Centralized SQL SERVER that has
enabled fall back support.
b. Implement a Client/Server Architecture in which Vendors obt
ain
an authorized code from Centralized SQL SERVER that uses
Microsoft Windows NT Clustering service.
c. Implement an n-tier in which Vendors make calls to a single
Microsoft Transaction Server (MTS) which will obtain an
authorization code from a centralized SQL SERVER.
d. Implement an n-tier in which Vendors make calls to geograph
ically
dispersed MTS which will obtain an authorization code from
geographically dispersed SQL SERVER.
Answer:c (not sure check it out)
11 Designing Insurance database:
In POLICY table you ned to ensure that the value entered in t
o
Beginning_effective_date column is less than or equal to the
value entered into Ending_effective_date.
a. Process each application to compare the values before
updating the POLICY table.
b. Create a CHECK CONTAINS on POLICY table.
c. Create a rule & bind the rule to the
Beginning_effective_date column.
d. Create Insert & Update triggers on the POLICY table that
compare the values.
Answer:b (sure)
12. You are designing an INVENTORY database application for
a national automobile sales registry. This new database sal
es
registry. This new data available at all participating dealer
ships
will allow each dealership to sell automobile from INVENTORY
of other dealerships. You want to be able to track informatio
n
about each automobile. You want to normalize your
database. Which table should be included in database applicat
ion?
Chose 2.
a. A table containing the list of all dealership along with the
address & Identifications for each dealership.
b. A table containing contact information for each automobile
Manufacturer along with the name of each
Model manufactured by each Manufacturer.
c. A table containing the name & address of each dealership
along with automobile information.
d. A table containing an identification number for each automobi
le,
the owning dealership's identification number & the informati
on
specific to each automobile (many makes & models of
automobiles will be shown from each dealership).
Answer:c & d (not sure)
13.The Demographic table contains more than 1000 columns.
Nearly 90% of the users search & update 20 of the columns.
The remaining columns are seldom used but also important.
What to improve data retrieval & update performance when
accessing the information in the Demographic table.
a. Create clustered index on the Demographic over the most
accessed columns
b. Create a VIEW based on Demographic table which selects
the 20 most accessed columns.
c. Devide the data in Demographic table into 2 new tables with
one
containing the 20 most accessed columns & the other contain
ing
the remaining columns.
d. Create a series of stored Procedures that select or update
the
Demographic table according to user needs.
Answer: a ( a is good because the easy way is to create
a clustered index on a table for the most aaccessed
columns, but d still available. Check it out)
14. Your company uses an application, named Application Z that i
s
run daily after business hours. As your company expands it
s
business , data becomes more dynamic, & management wants the
report
to be produced every 2 hours. Multiple applications concu
rrently
access SQL Server during the day. Which two of the followi
ng
modifications to Applications Z should you implement to prod
uce
the reports more quickly without preventing other SQL Server
applications from performing their tasks.
a. Implememt trigger to recalculate aggregates each time the
appropriate columns updateed.
b. Specified the NOLOCK hint in the SELECT statements.
c. Specify the LOW deadlock priority for App. Z.
d. Set the SERIALIZABLE Transaction isolation level.
Answer: bc (sure)
15. You are designing a logical model for datasbase that will be
used
by an employment agency. The agency accepts applications fr
om
individuals who are looking for the jobs. In an attempt to
increase
their chances of being hired, some applicants may use multip
le
aliases & submit multiple applications for different jobs. T
he
employment agency's recruiters can identify some of the alia
ses by
aliases by address & phone number. Some recruiters try to f
ind
suitable jobs for registered applicants. When a match is fo
und
the applicat is provided with employer's contact information
.
You take the following steps:
* Design a table named JOBS that lists all available positio
ns
job descriptions & salaries.
* Design a table named RECRUITERS that lists all recruiters.
* Design a table named EMPLOYERS that list all employers. Cr
eate
a FK on RecruiterID cl. to reference the RECRUITERS tb.
* Design a table named APPLICANTS that lists all applicants,
their
desired positions & salaries, & description of each applic
ant's
skills. Create FK on the RecruiterID cl. to reference the
RECRUITERS tb.
* Design a table named ALIASES. Include in each record 2 app
licant
IDs that are associated with 2 different names used by sam
e
applicant. Create a FK on each of theses columns to refere
nce
the AplicantID cl. in the APPLICANTS tb.
Which results?
a. Each applicant can be matched to all employers who offer
suitable positions.
b. Each available position ca be associated with employer.
c. Each applicant can be associated with all corresponding al
iases.
d. The data does not contain redundant data.
e. All applicants & employers whom a recruiter has registered
can
be identified.
Answers: cde (sure 100%--Transcender B #15)
--
菩提本非树，明镜亦非台。
本就无一物，何处惹尘埃。
※ 来源:．月光软件站 http://www.moon-soft.com．[FROM: 202.104.37.220]
|
|