MSCE Interview Questions and Answers

MCSE MCSA 70-272 Pass for sure software

2009-01-30T05:43:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-272 Pass for sure software

Rules to Download and use this software
Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE MCSA 70-272 Pass for sure software

MCSE 70-272 Questions and Answers Actual Test

2009-01-30T05:40:00.001-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-272 Questions and Answers Actual Test
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-272 Questions and Answers Actual Test

MCSE 70-272 Questions and Answers Test Inside

2009-01-30T05:35:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-272 Questions and Answers Test Inside

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-272 Questions and Answers Test Inside

MCSE 70-272 Training kit E-books

2009-01-30T05:31:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-272 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-272 Training kit E-books

MCSE 70-271 Pass for sure software

2009-01-30T05:21:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-271 Pass for sure software

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-271 Pass for sure software

MCSE 70-271 Questions and Answers Test King

2009-01-30T05:16:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-271 Questions and Answers Test King
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-271 Questions and Answers Test King

MCSE 70-271 Questions and Answers Actual Test

2009-01-30T05:10:00.001-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-271 Questions and Answers Actual Test
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-271 Questions and Answers Actual Test

MCSE 70-271 Trainign Kit E-book

2009-01-30T05:00:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-271 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-271 Trainign Kit E-book

MCSE 70-351 Training kit E-Book

2009-01-30T04:46:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-351 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-351 Training kit E-Book

MCSE 70-351 Pass for sure Software

2009-01-30T04:38:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-351 Pass for sure Software
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-351 Pass for sure Software

MCSE 70-351 Questions and Answers from Testking and Actual Test

2009-01-30T04:33:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-351 Questions and Answers from Testking and Actual Test

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-351 Questions and Answers from Testking and Actual Test

MCSE 70-351 Questions and Answers Test Inside

2009-01-30T04:15:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-351 Questions and Answers Test Inside
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-351 Questions and Answers Test Inside

MCSA 70-298 Questions and Answers from Test Inside and Actual Test

2009-01-30T04:13:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-298 Questions and Answers from Test Inside and Actual Test
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSA 70-298 Questions and Answers from Test Inside and Actual Test

MCSA 70-298 Questions and Answers from Test King

2009-01-30T04:05:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-298 Questions and Answers from Test King
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSA 70-298 Questions and Answers from Test King

MCSE MCSA 70-298 Questions and Answers and Software

2009-01-30T04:00:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-298 Questions and Answers and Software
Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE MCSA 70-298 Questions and Answers and Software

70-298 MCSE Training Kit E-book

2009-01-30T03:53:00.000-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-298 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - 70-298 MCSE Training Kit E-book

70-285 MCSE Training Kit E-book

2009-01-29T00:06:00.001-08:00

MCSE Interview Questions and Answers http;//mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-285 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - 70-285 MCSE Training Kit E-book

70-285 MCSE Questions and Answers from Test King

2009-01-29T00:02:00.000-08:00

This section consist of complete MCSE MCSA 70-285 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - 70-285 MCSE Questions and Answers from Test King

MCSE 70-285 Question and Answer from Actual Test

2009-01-28T23:56:00.001-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-285 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-285 Question and Answer from Actual Test

MCSE 70-270 Question and Answer from Test Inside

2009-01-28T22:19:00.000-08:00

This section consist of complete MCSE MCSA 70-270 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-270 Question and Answer from Test Inside

MCSE 70-270 Question and Answer from Testing King

2009-01-28T22:16:00.001-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-270 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding

2)Download Winrar as the download file is in winrar

3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.

4)After Downloading extract the file with Winrar

5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-270 Question and Answer from Testing King

70-262 MCSE Live Practice Exams Software

2009-01-28T22:08:00.000-08:00

MCSE Inteview Questions and Answers http://mcse-interview.blogspot.com/

This Download consist of 70-262 live practice software from where you can write real exams for free.

Note : I could get the Software but i got all the screen shots from first questions to last with screen shots and answers

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-262 MCSE Live Practice Exams Software

70-236 MCSE Live Practice Exams Software

2009-01-27T02:17:00.001-08:00

MCSE Inteview Questions and Answers http://mcse-interview.blogspot.com/

This Download consist of 70-236 live practice software from where you can write real exams for free.

Note : I could get the Software but i got all the screen shots from first questions to last with screen shots and answers

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.5)Last Leave comments

Download - 70-236 MCSE Live Practice Exams Software

MCSE 70-236 Training Kit

2009-01-27T02:06:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-236 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-236 Training Kit

MCSE 70-236 Questions and Answers from Test Inside

2009-01-27T01:26:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-236 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-236 Questions and Answers from Test Inside

MCSE 70-236 Questions and Answers from Actual test

2009-01-27T00:01:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-236 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-236 Questions and Answers from Actual test

70-648 MCSE Training kit

2009-01-26T23:24:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/
MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-648 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Download - 70-648 Training Kit

MCSE 70-648 Questions and Answers from Actual test

2009-01-26T22:44:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-294 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-648 Questions and Answers from Actual test

MCSE 70-620 Questions and Answers from Actual test

2009-01-26T22:35:00.000-08:00

MCSE Interview Questions and Answers http://mcse-interview.blogspot.com/

This section consist of complete MCSE MCSA 70-620 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-620 Questions and Answers from Actual test

MCSE 70-350 Training Kit

2009-01-22T22:55:00.000-08:00

This section consist of complete MCSE MCSA 70-350 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-350 Training Kit

MCSE 70-350 Questions and Answers from Test Inside

2009-01-22T22:38:00.000-08:00

This section consist of complete MCSE MCSA 70-350 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-350 Questions and Answers from Test Inside

MCSE 70-297Question and Answer from Testing King

2009-01-22T22:32:00.000-08:00

This section consist of complete MCSE MCSA 70-297 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-297Question and Answer from Testing King

MCSE 70-294 E-book training kit

2009-01-22T04:41:00.000-08:00

This section consist of complete MCSE MCSA 70-294 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Download - MCSE 70-204 E-Book Training Kit

MCSE 70-294 CBT Video

2009-01-22T04:21:00.000-08:00

MCSE 70-294 Video's in which each part is explained very clearly that you can learn as well as see real pratical exams solution procedure.

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download -

Part 1
Part - 2
Part - 3
Part - 4
Part - 5
Part - 6

MCSE 70-294 Questions and Answers from Test Inside

2009-01-22T04:10:00.000-08:00

This section consist of complete MCSE MCSA 70-294 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-294 Questions and Answers from Test Inside

MCSE 70-294 Questions and Answers from Actual test

2009-01-22T03:58:00.000-08:00

This section consist of complete MCSE MCSA 70-294 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-294 Questions and Answers from Actual test

MCSE Interview Questions by Tanla

2009-01-22T02:28:00.000-08:00

Windows admin interview questions

Describe how the DHCP lease is obtained. It’s a four-step process consisting of (a) IP request, (b) IP offer, © IP selection and (d) acknowledgement.

I can’t seem to access the Internet, don’t have any access to the corporate network and on ipconfig my address is 169.254.*.*. What happened? The 169.254.*.* netmask is assigned to Windows machines running 98/2000/XP if the DHCP server is not available. The name for the technology is APIPA (Automatic Private Internet Protocol Addressing).

We’ve installed a new Windows-based DHCP server, however, the users do not seem to be getting DHCP leases off of it. The server must be authorized first with the Active Directory.

How can you force the client to give up the dhcp lease if you have access to the client PC? ipconfig /release

What authentication options do Windows 2000 Servers have for remote clients? PAP, SPAP, CHAP, MS-CHAP and EAP.

What are the networking protocol options for the Windows clients if for some reason you do not want to use TCP/IP? NWLink (Novell), NetBEUI, AppleTalk (Apple).

What is data link layer in the OSI reference model responsible for? Data link layer is located above the physical layer, but below the network layer. Taking raw data bits and packaging them into frames. The network layer will be responsible for addressing the frames, while the physical layer is reponsible for retrieving and sending raw data bits.

What is binding order? The order by which the network protocols are used for client-server communications. The most frequently used protocols should be at the top.

How do cryptography-based keys ensure the validity of data transferred across the network? Each IP packet is assigned a checksum, so if the checksums do not match on both receiving and transmitting ends, the data was modified or corrupted.

Should we deploy IPSEC-based security or certificate-based security? They are really two different technologies. IPSec secures the TCP/IP communication and protects the integrity of the packets. Certificate-based security ensures the validity of authenticated clients and servers.
What is LMHOSTS file? It’s a file stored on a host machine that is used to resolve NetBIOS to specific IP addresses.

What’s the difference between forward lookup and reverse lookup in DNS? Forward lookup is name-to-address, the reverse lookup is address-to-name.
How can you recover a file encrypted using EFS? Use the domain recovery agent.

MCSE 70-293 E-book training kit

2009-01-22T00:52:00.000-08:00

This section consist of complete MCSE MCSA 70-293 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Download - MCSE 70-293 E-book Training Kit

MCSE 70-293 CBT Video

2009-01-21T23:03:00.000-08:00

MCSE 70-293 Video's in which each part is explained very clearly that you can learn as well as see real pratical exams solution procedure.

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download -

Part - 1
Part - 2

MCSE 70-293 Questions and Answers from Test Inside

2009-01-21T22:56:00.000-08:00

This section consist of complete MCSE MCSA 70-293 Questions and Answers E-books

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-293 Questions and Answers from Test Inside

MCSE 70-293 Question and Answer from Testing King

2009-01-21T22:48:00.000-08:00

MCSE 70-293 Questions and Answers from Actual test

2009-01-21T22:35:00.000-08:00

This section consist of complete MCSE MCSA 70-293 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-293 Questions and Answers from Actual test

MCSE 70-291 Questions and Answers from Actual test

2009-01-20T22:49:00.000-08:00

This section consist of complete MCSE MCSA 70-291 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-291 Questions and Answers from Actual test

MCSE 70-291 Questions and Answers from Test Inside

2009-01-20T22:43:00.000-08:00

MCSE 70-291 E-book training kit

2009-01-20T22:38:00.000-08:00

This section consist of complete MCSE MCSA 70-290 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Download - MCSE 70-291 E-book training kit

MCSE 70-291 Question and Answers from Testking

2009-01-20T22:29:00.000-08:00

MCSE 70-291 CBT Video

2009-01-20T22:22:00.000-08:00

MCSE 70-291 Video's in which each part is explained very clearly that you can learn as well as see real pratical exams solution procedure.

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download -
Part - 1
Part - 2

MCSE 70-290 Questions and Answers from Actual Test

2009-01-20T05:04:00.000-08:00

This section consist of complete MCSE MCSA 70-290 Questions and Answers E-books from Actual Test.

Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-290 Questions and Answers from Actual Test

MCSE 70-290 Question and Answer from Testing King

2009-01-20T04:52:00.000-08:00

This section consist of complete MCSE MCSA 70-290 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Doanload - MCSE 70-290 Question and Answer from Testing King

MCSE 70-284 Questions and Answers from Actual Tests

2009-01-20T04:42:00.000-08:00

This section consist of complete MCSE MCSA 70-284 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-284 Questions and Answers from Actual Tests

MCSE 70-284 CBT Videos

2009-01-20T03:33:00.000-08:00

MCSE 70-284 Video consists of videos in which each part is explained very clearly that you can learn as well as see real pratical exams solution procedure.

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download :
part -1
Part - 2
part - 3
part - 4

70-284 MCSE Questions and Answers

2009-01-20T03:17:00.000-08:00

IIS 7.0 Administration Training Kit

2009-01-20T02:56:00.000-08:00

This section consist of complete IIS 7.0 Administration Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Download - IIS 7.0 Administration Training Kit

IIS 6.0 Administration Video

2009-01-20T02:16:00.000-08:00

IIS 6.0 Administration Video

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - IIS 6.0 Administration Video

IIS 6.0 Training kits from different Authors

2009-01-20T01:44:00.000-08:00

This section consist of complete IIS 6.0 Administration Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Doanload - IIS 6.0 Training kits from different Authors

IIS 7.0 and Asp.Net Training kit

2009-01-20T01:32:00.000-08:00

This section consist of complete IIS 7.0 and ASP.NET Administration Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Download - IIS 7.0 and Asp.Net Training kit

Securing IIS 6.0 Training Kit

2009-01-20T01:27:00.000-08:00

This section consist of Securing IIS 6.0 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Download - Securing IIS 6.0 Training Kit

IIS 6.0 Administration Kit

2009-01-20T01:21:00.000-08:00

IIS 6.0 MCSE Training kit

2009-01-20T00:04:00.000-08:00

70-649 MCSE Training kit

2009-01-19T23:39:00.000-08:00

This section consist of complete MCSE MCSA 70-649 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Download - 70649_Study Guide.pdf

MCSE Real Interview Questions

2009-01-19T02:11:00.000-08:00

I have a collection of some interview questions asked by Major MNC like Hp, IBM, TCS, Wipro, Satyam, CA, CS etc

1)What is ADS? Explain?
2)What are FSMO Rules and explian the usefulness of each Rule?
3)Replication between domain servers?
4)Structure of ADS backup including Incrimental, Regular, Differential?
5)Consider one scenario there are one forest and 5 child domain, if forest is down what steps you take to make it up and if one child domain is down what steps you take to trouble shoot this issue.
6)Replication between forest and additional domain controllers?
7)What is DNS.What are types of DNS server and explain forward and reverse DNS ?
8)DHCP, scopes, superscopes and reservations?
9)What are Filesystems and difference between encryption and decription?
10)What is Disk Quota planning and how you configure disk quota?
11)System state backup and restoration?
12)what is GPO and functions if GPO?
13)what is RIS and how can you configure RIS?
14)SUS and client configurations?
15)CA unicenter (SDO, AMO, RCO) how to create package and what packags can be created?
16)Backup Third party like veritas or legatos?
17)Raid levels raid 0, raid 1, raid 5
18)Difference between software and hardware raids?
19)Clustering, difference between hardware and software clustering?
20)How will you promote addition domain controller to forest(tools and utilities))
21)Terminal server and client configuration?
22)Function of sites and servers in administrative tools?
23)Main function of trust,types of trusts and uses of creating trust?
24)what is trusting(transitive and non transitive?
25)what is one way trusting and 2 ways trusting?
26)structure of trusting?
27)User management in ADS?
28)full function if ntutils?
29)networking products (active components?
30)Differences between 2000,2003,NT 4.0 and 2008
31)what is shadowing 2003?
32)Any server you worked with specification most important like dell store edge 250 ,350 2500 etc
33)check logs in windows?
34)how to check login attempts in windows?
35)how to update registry from command line?
36)what is you current role in your office and daily schedules?
37)what is your strenght and weakness?
38)any knowledgeof unix?
39)Difference between DNS and WINS?
40)Default errors in DNS,,did you find any how did u solved it?
41)how to find which is primarch and child domain?
42)If DHCP fails what happens to client?what back up strategy you will follow?
43)what are features of NTFS?
44)how do you configure VLAN?
45)what is id file?
46)what is nsf?
47)what is ntf?
48)If blue dumps how will you solve the problem?
49)what antivirus server you are using how how you update the client antivrus and how you scan your network when virus attack?
50)What are the projects you worked previously?
51)How do configure print server?
52)what is nslookup command and what we use it?
53)port number for ftp telnet mail http dns dhcp etc
54)difference between hosts and lmhosts files?
55)in DHCP difference between reserved and excluded ips?
56)how to mount network shares from DOS windows?
57)what is global catlog?
58)what are 5 ADS files in winnt directory?
59)what is difference between native mode and mixed mode?
60)what is UPN (user principal name)in trust?
61)any idea about WMI?
62)what is VPN?what is its functions ?how its works and what protocol it uses?
63)Define TCP/IP layers and classes?
64)Formula for calculating subnets?
65)what is PPP and its functions?
66)add and delete a route?
67)why IP forwarding is required?
68)what is netbios?
69)what is ARP and what is its functions?
70)what is forward time out and recursion in DNS?
71)Define FDDN, SOA, NS, MX, CNAME PTR?
72)how to stop DNS from command mode?
73)Define Zone transfer?
74)what is round robin rotation?
75)how to referesh the DNS cache from command mode - ipconfig flush DNS
76)Debug logging enabled or disabled which is best?
77)what is recursive query and automatic query?
78)where is dns.log stored?
79)what is zone delagation?
80)what is Wins forward and Wins reverse?
81)define scavenging stale records?
82)how to view a client resolver cache?
83)how to flush and reset a client resolver cache?
84)how to renew DNS client registration?
85)how to verify a zone delagation?
86)Define the files boot, root.dns, cache.dns and zone?name, dns?
87)how to reconcile the DHCP database?
88)how manually compack or repair the DHCP database?
89)how to compact or repair the DHCP database?
90)what is multicast scope?
91)define BOOTP tables?
92)how to verify WINS registration of client netbios names?
93)Define tombstone?
94)Define RADIUS?
95)what is IIS ,how to install,configure, create sites and start stop IIS from command mode?
96)Difference between RAS and VPN?
97)GPO, different types of GPO how to create and delete a GPO,what are domain level and directory level GPO?
98)create user and create profiles, template?
99)Software deployment, group policy objects read GPO perfectly lot of questions can be asked from it?
100)create user in US Primary ADS sever and you are at client location, let us say primary ADS server is in US and you are in India the user is not able to login in India its says username and passwd wrong what steps you will take to resolve it?

70-649 MCSE Interview Questions and Answers part - 2

2009-01-19T01:12:00.000-08:00

This section consist of complete MCSE MCSA 70-210 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - 70-649 MCSE Interview Questions and Answers part - 2

70-649 different version MCSE Live Practice Exams Software

2009-01-19T00:03:00.000-08:00

This Download consist of 70-649 live practice software from where you can write real exams for free.

Note : I could get the Software but i got all the screen shots from first questions to last with screen shots and answers

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.5)Last Leave comments

Download - 70-649 different version MCSE Live Practice Exams Software

70-649 MCSE Live Practice Exams Software

2009-01-18T23:48:00.000-08:00

70-648 MCSE Live Practice Exams Software

2009-01-18T23:39:00.000-08:00

This Download consist of 70-648 live practice software from where you can write real exams for free.

Note : I could get the Software but i got all the screen shots from first questions to last with screen shots and answers

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.5)Last Leave comments

Doanload - 70-648 MCSE Live Practice Exams Software

70-649 MCSE Questions and Answers

2009-01-18T23:33:00.000-08:00

Upgrading from 70-648 to 70-649

2009-01-18T22:51:00.000-08:00

This section consist of complete MCSE MCSA 70-210 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost

Doanload - upgrade for MCSA ( 70-648) & MCSE(70-649).rar

70-649 MCSE Video

2009-01-18T22:33:00.000-08:00

MCSE 70-649 Video consists of 13 videos in which each part is explained very clearly that you can learn as well as see real pratical exams solution procedure.

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download
Part - 1 : 70-649

70-270 MCSE Questions and Answers

2009-01-15T21:25:00.000-08:00

This section consist of complete MCSE MCSA 70-270 Questions and Answers E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - 70-270 MCSE Questions and Answers

70-210 MCSE Questions and Answers

2009-01-15T05:16:00.000-08:00

70-210 MCSE Training Kit E-Book

2009-01-15T05:07:00.000-08:00

70-210 MCSE LIve Exam Practice software

2009-01-15T05:01:00.000-08:00

This Download consist of 70-210 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-210 MCSE LIve Exam Practice software

70-620 Windows Vista Configuration Training E-Book

2009-01-15T04:01:00.000-08:00

70-620 MCSE CBT Videos

2009-01-15T03:10:00.000-08:00

This Download consist of 70-620 CBT Videos .

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players or altest Media Players.
5)Last Leave comments

Download -
Part - 1
Part - 2
Part - 3
Part - 4
Part - 5

Download All the parts in one location extract and watch step by step .

70-620 MCSE Live Practice Exams Software

2009-01-15T02:43:00.000-08:00

This Download consist of 70-620 live practice software from where you can write real exams for free.

Note : I could get the Software but i got all the screen shots from first questions to last with screen shots and answers

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.5)Last Leave comments

Doanload - 70-620 MCSE Live Practice Exams Software

70-228 MCSE Live Practive Exams Software

2009-01-15T02:28:00.000-08:00

This Download consist of 70-228 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-228 MCSE live Practice Exams software

70-229 MCSE Live Practice Software

2009-01-15T02:19:00.000-08:00

This Download consist of 70-229 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-229 MCSE Live Practice Software

70-229 Part - 3

2009-01-14T23:09:00.000-08:00

QUESTION 41
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 SQL Server 2000 database for the Human Resource department which will
be used to store information about employees and involvements with projects.
Below is a fragment of the database schema:

Certkiller .com has each employee belonging to a specific department participating in
one project for which that department is responsible. Certkiller .com has additionally
assigned each department a unique alphabetic ID. Certkiller .com management has
recently decided to reorganization involving changing each departments ID.
Certkiller .com wants to have all the new IDs to be different from the original
department IDs.
What should you do? (Choose all that apply.)

A. You should specify ON UPDATE CASCADE in the REFERENCES clause in the
Projects table definition.
B. The values in the DepartmentID column in the Departments table should be updated.
C. You should specify ON UPDATE CASCADE in the REFERENCES clause in the
Employees table definition.
D. You should create a FOR UPDATE trigger on the Employee table.
E. You should create a FOR UPDATE trigger on the Projects table.
F. You should create a FOR UPDATE trigger on the Departments table.

Answer: A, B, C

Explanation: In the scenario you should always keep in mind that SQL Server 200
supports cascading referential integrity in the REFERENCE clause of a foreign key
definition which you can specify actions that the SQL Server will perform when the
specific values are referenced primary or candidate key column change or rows are
deleted.

Incorrect Answers:

D, E, F: In the scenario you should not consider using these options because if you
created the FOR UPDATE triggers you also have to create AFTER UPDATE
triggers. If you attempted to update the DepartmentID column the UPDATE statement
would not succeed because the existing FOREIGN KEY constraint on the column.

QUESTION 42
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 to implement a SQL Server 200 database
that will be used to store business information. A table named Test will be used to
store sales information and each sale will be uniquely identified by a specific SaleNo column. You are required to ensure the uniqueness of the values that will be entered
in the SalseNo column.
What should you do?

A. You should define a PRIMARY KEY constraint on the SaleNo column.
B. You should specify the unique identifier data type for the SaleNo column.
C. You should specify ROWGUIDCOL property for the SaleNo column.
D. You should specify an IDENTITY property for the SaleNo column.

Answer: A

Explanation: In the scenario there are several methods which could be used to
ensure the uniqueness of the values in the SaleNo column. The method used in the
answer defines a primary key for the table by specifying a PRIMARY KEY
constraint in a CREATE TABLE or ALTER TABLE statement.

Incorrect Answers:

B: This option should not be considered for use in the scenario because you are able
to include multiple columns of the uniqueidentifier data type.
C: This option should not be used by itself in the scenario because the
ROWGUIDCOL property by itself will not guarantee the uniqueness of the values in a
column.
D: This option should not be used by itself in the scenario as there will be no guarantee that the values in the column will be unique.

QUESTION 43
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.
Certkiller .com is about to start a car valet service for their customers. You have
recently received instruction from the Certkiller .com network CIO to implement a
SQL Server 2000 database that will be used to store the information of the valets.
The valets will be listed as a primary driver for some cars and may be listed a
secondary driver for other cars. Each car will always have one primary driver but
there can be zero or more secondary drivers for each vehicle.
You are required to create the appropriate tables to store the information about the
cars and drivers without introducing redundant data into the database. You are
additionally required to select which script to use.
What should you do?

A. CREATE TABLE Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID) )
CREATE TABLR Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10),
CarID int FOREIGN KEY REFERENCES Cars (CarID) )
B. CREATE TABLR Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10),
PrimaryDriver bit)
CREATE TABLE Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID) )
C. CREATE TABLE CarDriver
(CarID int PRIMARY KEY,
DriverID int PRIMARY KEY,
PrimaryDriver bit)
CREATE TABLR Cars
(CarID int FOREIGN KEY REFERENCES CarDriver (CarID),
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10) )
CREATE TABLE Drivers
(DriverID int FOREIFN KEY REFERENCES CarDriver (DriverID),
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10) )
D. CREATE TABLR Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10) )
CREATE TABLE Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10) )
CREATE TABLE CarDriver
(CarID int FOREIGN KEY REFERENCES Cars (CarID),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID),
PrimaryDriver bit,
PRIMARY KEY (CarID, DriverID) )

Answer: D

Explanation: In the scenario you should remember that online transaction
processing (OLTP) systems are often designated according to normalization rules
where each piece of information is stored only in one table avoiding redundant data.

Incorrect Answers:

A: This option should not be used in the scenario because each of the two tables are
referencing the other table and should not be done in the scenario.
B: This option should not be used in the scenario because the FOREIGN KEY
constraints are defined on the wrong tables in the scenario.
C: This option should not be used in the scenario as this will configure one driver to be associated with each car and that should not be done.

QUESTION 44
You work as a database developer at Certkiller .com. You have just finished the
design of a SQL Server 2000 database for Certkiller .com. The database contains a
table named Sales that has 2 million rows, and stores sales information for all
Certkiller .com departments. In the table, each department is identified by the
DepartmentID column. Most queries against the Sales table are utilized to locate
sales for a single department.
You now want to boost the I/O performance of these queries, without affecting the
applications that access the table.
Which of the following will allow you to reach your goal?

A. Create a new table, and move the columns that are most frequently queried to this
table. Retain the DepartmentID column in both tables. Create a view on the original table and on the new table. Add a FOREIGN KEY constraint on the join columns of the new table.
B. Create a new table, and move the columns that are most frequently queried to this
table. Retain the DepartmentID column in both tables. Create a view on the original table and on the new table. Add a CHECK constraint on the DepartmentID columns of both tables.
C. Create one new table for each department, and move the sales information for each
department to that department's table. Add a CHECK constraint on the DepartmentID
columns of the new tables. Create a view on the new tables.
D. Create one new table for each department, and move the sales information for each
department to that department's table. Create a view on the new tables. Add a CHECK
constraint on the DepartmentID column in the view.
E. Create a stored procedure that accepts an input parameter for the department. Use the stored procedure to return results from the Sales table.

Answer: C.

Explanation:
To improve performance, the huge Sales table will be split into several tables. A
horizontal split would be the most beneficial since most queries run are used to find sales for a particular department. In order to keep existing applications functioning, a view will be used which mimics the behavior of the original unsplit Sales table. Deleting the original Sales table and naming the view Sales would allow applications to use sales data in the same way as before.

Incorrect answers:

A: The original table is not needed; in fact, keeping the original table would store the same information in two places, which always is a bad idea in database design.
B: Vertical splitting (column splitting) is not as good as horizontal splitting (row
splitting) in this scenario. We have no indication that some columns are used less
frequently.
D: You can't put a check constraint on a column in a view, only to a column in a table.
E: To increase performance, the sales tables table must be split. Just using a stored
procedure on the existing Sales table will not improve I/O performance.

QUESTION 45
You work as a database developer at Certkiller .com. Certkiller .com recently
acquired a company named SG Publishers. Sales data of SG Publishers is stored in
a SQL Server 2000 database that contains a table named Orders. There is a
clustered index on the Orders table, which is produced by using a customer's name
and the current date.
The Orders table currently has 750,000 rows, and this number of rows will increase
by 5 percent every week. SG Publishers is planning to launch a promotion next
week that will increase the volume of inserts to the Orders table by 50 percent.
Which of the following is the task that you should complete to optimize inserts to the Orders table during the promotion?

A. Create a job that rebuilds the clustered index each night by using the default
FILLFACTOR.
B. Add additional indexes to the Orders table.
C. Partition the Orders table vertically.
D. Rebuild the clustered index with a FILLFACTOR of 50.
E. Execute the UPDATE STATISTICS statement on the Orders table.

Answer: D.

Explanation:
The table's size will increase with 50% during the next week. The database would need to grow and index pages would have to be split. This will degrade performance. It is better to anticipate this growth by rebuilding the data pages, and by rebuilding the clustered index. It is also import to choose an appropriate FILLFACTOR. The default value is 80. By choosing a FILLFACTOR of 50, the pages would be around 75% filled after the promotion and would be allow to grow further.
Note: FILLFACTOR It is seldom necessary to specify a fill factor when you create an index. The option is provided for fine-tuning performance. It is useful when you are creating a new index on a table with existing data, and particularly when you can accurately predict future changes in that data.
FILLFACTOR is specified as a percentage, and it indicates how full SQL Server should
make the leaf level of each index page during index creation. As a table is updated and the index page fills, SQL Server must split the index page to make room for new rows, which is requires time. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The default FILLFACTOR is usually efficient for most database usage. An explicit
FILLFACTOR setting applies only when the index is first created. SQL Server does not
dynamically keep the specified percentage of empty space in the pages. The default fill factor is 80.

Incorrect answers:

A: Rebuilding the clustered index every night with the default FILLFACTOR requires
more administration. The default FILLFACTOR is 80, and if we assume a few weeks
during which the 50% increase takes place, we see that the data pages would fill up
completely, especially the first day. This would increase index splitting and decrease performance.
B: Adding the correct indexes to a table can be useful for improving the performance of query on a table. Adding indexes, however, could hamper INSERT operations, since they would be added to existing index pages and would reduce the amount of space left in those pages for the indexes of new rows inserted into the table.
Instead, we should anticipate the 50% growth by rebuilding the indexes with a lower
fillfactor.
C: Partitions are used to store and manage pre-calculated aggregations and, sometimes, source data. They also offer flexibility in storing such data in multiple locations and in optimizing its access. This would not, however, anticipate the 50% increase of rows in the table.
E: UPDATE STATISTICS should be re-run when there is significant change in the key
values in the index such as occurs when a large amount of data in an indexed column has been added, changed, or removed, i.e., if the distribution of key values has changed, or the table has been truncated using the TRUNCATE TABLE statement and then
repopulated. In this scenario you are expecting an increase in the volume of data to be inserted into the table. This has not yet occurred, therefore re-running UPDATE
STATISTICS is not required. Furthermore, the UPDATE STATISTICS function is
related to indexing, which is of concern when queries are run against the table. It does not improve INSERT operations.

QUESTION 46
You work as a database developer at Certkiller .com. Certkiller .com is a company that generates an online telephone directory, so you create a table named
PhoneNumbers as shown in the exhibit below.

Subsequent to loading 100,000 names into the table, you create indexes by using the
script below:
ALTER TABLE [dbo]. [PhoneNumbers] WITH NOCHECK ADD
CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED (
[FirstName],
[LastName],
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX
[IX_PhoneNumbers] ON [dbo].[PhoneNumbers](
[PhoneNumberID]
) ON [PRIMARY]
GO
While testing the performance of the database, you detect that queries such as
"Return all names and phone numbers for persons who live in a certain city and
whose last name begins with W", take a long time to execute.
Which two of the following options make up the solution for improving the
processing performance of these types of queries? (Choose two)

A. Change the PRIMARY KEY constraint to use the LastName column followed by the
FirstName column.
B. Add a nonclustered index on the City column.
C. Add a nonclustered index on the AreaCode, Exchange, and Number columns.
D. Remove the unique index from the PhoneNumberID column.
E. Change the PRIMARY KEY constraints to nonclustered indexes.
F. Execute on UPDATE STATISTICS FULLSCAN ALL statements in SQL Query
Analyzer.

Answer: A, B.

Explanation:
The query searches the City column and then references the LastName column for values
that begin with 'W', thus the City column should be indexed. In addition, changing the PRIMARY KEY constraint, which is used to identify rows in a column, to use the
LastName column before the FirstName column would improve query time performance,
as the FirstName column does not have to be queried. Instead, the rows where the
FirstName column are in the same row as the rows in the LastName column that match
the search criteria only need to be returned. The code for this search would be something like:
SELECT FirstName, LastName, PhoneNumberID
FROM PhoneNumbers
WHERE City=[certain city]
AND LastName LIKE 'W%'

Incorrect answers:

C: The AreaCode, Exchange, and Number columns are not referenced in by the query,
thus the presence or lack of indexes on these columns would not affect query time
performance.
D: The PhoneNumberID column is not referenced by the query, thus the presence or
lack of indexes on this column would not affect query time performance.
E: Generally clustered indexes are faster that non-clustered indexes. The only reason to remove the clustered index on the primary key would be with the idea of using the clustered index on another column. There is no such option in this scenario, which makes the change from a clustered index to a non-clustered index pointless.
Note:
In a nonclustered index the data is stored in one place, and the index in another, with pointers that link the index to the storage location of the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order. SQL Server 2000 searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Thus, nonclustered indexes should be used on columns that contain a large number of distinct values, and in queries that do not
return large result sets, and on columns that are frequently involved in search conditions of a query that return exact matches. A clustered index alters the physical storage order of the data in the table; as a result, a table can contain only one clustered index. However, 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. If a column is frequently used to sort the data retrieved from a table, it can be advantageous to cluster the table on that column to save the cost of a sort each time the column is queried.
F: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on non-indexed columns by using the CREATE STATISTICS statement.
However, query optimization depends on the accuracy of the distribution of the index.
UPDATE STATISTICS should be re-run when there is significant change in the key
values in the index on that index as occurs when a large amount of data in an indexed
column has been added, changed, or removed, i.e., if the distribution of key values has changed, or the table has been truncated using the TRUNCATE TABLE statement and
then repopulated. The STATS_DATE function can be used to check when the statistics
were last updated. In this scenario large amounts of data have not been added, changed, or removed in the indexed column.

QUESTION 47
You work as a database developer at Certkiller .com. You have recently completed
the design of Certkiller .com's SQL Server 2000 database, which contains a table
named Products and a table named Companies.
You have received instruction from the CIO to insert new product information from
a linked server into the Products table. The Products table currently has a
FOREIGN KEY constraint that references the Companies table. There is also an
UPDATE trigger defined on the Products table.
Which of the following is the option that you should make use of to load the data as
quickly as possible?

A. Use the ALTER TABLE statement and the ON UPDATE clause to modify the
Products table.
B. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to
modify the Products table.
C. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to
modify the Companies table.
D. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to
modify the Companies table.
E. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to
modify the Products table.

Answer: E.

Explanation:
New products from a linked server must be inserted as quickly as possible on Certkiller 's database. The products should be inserted into the Products table, however there is a foreign key constraint from the Products table to the Companies table. This foreign key constraint will force overhead during the insertion of the new rows. By modifying the Products table by using the ALTER TABLE statement and the NOCHECK CONSTRAINT option, the data would be loaded much more quickly.
The idea is:
1. Remove the check constraints
2. Load the data
3. Add the CHECK constraints again.

Note 1:
A drawback of disabling the foreign key constraint is that product rows with no corresponding company row in the Companies table could be entered. However it seems safe to assume that the new products all belong to already existing companies.
Note 2:
The ALTER TABLE statement is used to modify a table definition by altering, adding,
or dropping columns and constraints, or by disabling or enabling constraints and triggers.
ALTER TABLE acquires a schema modify lock on the table to ensure no other
connections reference the table during the modification. The modifications made to the table are logged and are fully recoverable. Modifications that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records.
The WITH NOCHECK clause specifies that the data in the table being updated must not
be validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint.
If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK
is assumed for re-enabled constraints. The WITH CHECK and WITH NOCHECK
clauses cannot be used for PRIMARY KEY and UNIQUE constraints.
The WITH NOCHECK clause can be used if you do not want to verify new CHECK or
FOREIGN KEY constraints against existing data. This, however, is not recommended as
any constraint violations suppressed by the WITH NOCHECK clause when the
constraint is added may cause future updates to fail if they update rows that contain data that does not comply with the constraint but had been added while CHECK constraints had been suppressed by the WITH NOCHECK clause.

Incorrect answers:

A: If a row on the table that is being altered is updated through the ALTER TABLE
statement has a referential relationship with a row on another table, the ON UPDATE
clause can be used specify what action should occur to a row on the other table. The
default is NO ACTION. This will raise an error when the row in the referential relation is updated through the ALTER TABLE statement, and the update action on the row in the table that is being altered will be rolled back. If CASCADE is specified, the row in the other table that has a referential relation with a row that is updated through the ALTER TABLE statement. The CASCADE action ON UPDATE cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the referenced table. As this solution does not specify what action to take, the update will revert to the default NO ACTION, and as the Products table has a FOREIGN KEY constraint that references the Companies table, this will raise an error, and the update action performed by the ALTER TABLE statement on the row on the Products the references a row on Companies table will be rolled back.
B: Disabling the triggers on the products could improve the performance somewhat, but
there is only an UPDATE trigger on the Products and we are going to insert new data.
The update trigger would not be used anyway, so disabling triggers would do no good.
C: The DISABLE TRIGGER ALL clause specifies that all triggers on a table being altered must be disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled. Furthermore, as it is the Products table that data is being inserted into, the triggers on the Products table and not the triggers on the Companies table should be disabled.
D: As data is to be added to the Products table and not the Companies table, the ALTER TABLE statement will not be run against the Companies table.
The WITH NOCHECK clause specifies that the data in the table being updated must not
be validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint.
If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK
is assumed for re-enabled constraints. The WITH CHECK and WITH NOCHECK
clauses cannot be used for PRIMARY KEY and UNIQUE constraints.
The WITH NOCHECK clause can be used you do not want to verify new CHECK or
FOREIGN KEY constraints against existing data.

QUESTION 48
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 an indexed view in a SQL Server 2000 database. During the course of the day
you create a function that references the view. The function definition you created
includes the SHEMABINDING option.
You have recently successfully tested the function and implemented the view in a
production database residing on another SQL Server computer. You are now no
longer required to view the database on your computer. You are required to select
which of the following should be done to delete the view from your computer.
What should you do? (Choose TWO.)

A. The SCHEMABINDING option should be removed from the function definition.
B. The function should be dropped that references the view.
C. You should do nothing as you can delete the file without performing additional
actions.
D. The table should be dropped that references the view.

Answer: A, B

Explanation: In the scenario you should remember that the SCHEMABINDING
option can be used to prevent objects that are referenced from being changed by
using ALTER and DROP statements. You should also remember that it as a
requirement for all indexed views to include the SCHEMABINDING option.

Incorrect Answers:

C, D: This option will not allow you to delete the table because the
SHEMABINDING option defined in the definition is used to deny users from
changing properties by using ALTER and DROP statements in the scenario, All
indexed views require the SCHEMABINDING option.

QUESTION 49
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 separate databases on two SQL Server 2000 computers that will be used to
store sales information. The databases used on the network are named TestSales
and BillSales residing respectively one each on the servers named
Certkiller -SR01 and Certkiller -SR02. The two database have an identical
schema. You are required to create a view that will facilitate the creation of reports
about both sales by selecting which statement to use.
What should you do?

A. CREATE VIEW SalesReport AS
SELECT OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
JOIN Certkiller -SR02.BillSales.dbo.Orders
ON Equipment.Computers = Equipment.Office
ORDER BY OrderDate
B. CREATE VIEW SalesReport AS
SLECT OrderID, OrderDate, Amount FROM Orders
WHERE Equipment = 'Test' OR Equipment = 'Bill'
ORDER BY OrderDate
C. CREATE VIEW SalesReport AS
SELECT Equipment = 'Test', OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
JOIN Equipment = 'Bill' OrderID, OrderDate, Amount
FROM Certkiller -SR02.BillSales.dbo.Orders
ORDER BY OrderDate
D. CREATE VIEW SalesReport AS
SELECT Equipment = 'Test', OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
UNION ALL Select 'Bill', OrderID, OrderDate, Amount
FROM Certkiller -SR02.BillSales.dbo.Orders

Answer: D

Explanation: In the scenario you should always keep in mind that a view is a
SELECT statement stored in a database which you can query and update tables by
querying and updating views that are derived from the tables in question.

Incorrect Answers:

A, B, C: This statements should not be used in the scenario because they have various
irregularities that do not agree with what you are required to perform. The only common reason that this options share and why they should not be used is the use of the ORDER BY clause which is only allowed in a view definition if a TOP clause is specified.

QUESTION 50
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
implement a SQL Server 2000 patients database for the company. You created the
table named Patients which is shown below:

Certkiller .com has let you configure all the customers to be uniquely identified by
the first name and last name. The Certkiller .com network users will be able to issue
queries against this table which will return names addresses and telephone numbers
of customers last names starting with a specific letter in a specific city. You are
required to create indexes to optimize the performance of such queries.
What should you do? (Choose TWO.)

A. You should create a nonclustered index on the City column.
B. You should create a clustered index on the FirstName and LastName columns.
C. You should create a nonclustered index on the Address column.
D. You should create a nonclustered index on the Phone column.
E. You should create a nonclustered index on the State column.

Answer: A, B
Explanation: You should always remember that the FirstName and LastName are
primary key columns which are by default a unique clustered index on those
columns. Listing the last name first in the table is a brilliant move which helps the
implementation.

Incorrect Answers:

C, D, E: In the scenario you should always remember that it is not required for you to create indexes in the Phone Address or Region columns in the scenario. This option would only be effective if you wanted to return information for customers with specific phone numbers with a specific area code.

QUESTION 51
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 plan
the physical implementation of a database that will be used to record credit card
transactions. Certkiller .com expects that several of the tables would be large housing millions of rows. You will be required to create a clustered index on a primary key of each table and non clustered indexes on all columns expected to be frequently used.
The server that will be used to host the database has several hardware-based RAID
devices each with a separate controller. You decided to create a data file in a
separate filegroup on each RAID device and want to place tables and indexes on the
filegroups to optimize database performance.
What should you do?

A. You should place nonclustered indexes for the frequently joined tables on separate file groups.
B. You should create more files in each filegroup and place frequently used tables on a separate file.
C. You should place frequently used tables on a different filgroup than its clustered
index.
D. You should make an alphabetical list of all the tables in the database and place each table and its indexes on a separate filegroup in a round-robin fashion.

Answer: A

Explanation: In the scenario you should always remember in order to optimize the
performance of queries and transactions against a database that it is recommended
you distribute frequently used tables amongst many physical disks or RAID devices
as possible. Making this configuration ensures that physical I/O among multiple
devices which enables access to several tables or indexes in parallel.

Incorrect Answers:
B: There is no need to consider this option in the scenario because SQL Server
automatically distributes the data evenly amongst multiple files that belong to the same filegroup.
C: This option should not be considered in the scenario because a clustered index is
inseparable from its base table and cannot be placed on a different filegroup than the table.
D: This option should not be used in the scenario as there is no guarantee that the
frequently used tables will be placed on separate disks as this will only degrade the
performance.

QUESTION 52
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 makes use of a table that is updated when ever the wind tunnel
becomes empty by adding event parameters to the Detail table which is shown

The duration of each testing session will vary depending on the technology tested.
The wind tunnel will be pressurized about 100 times a day after which maintenance
of the tested equipment should be performed. The information of the Detail table
will be summarized and archived into a History table. Every time testing resumes
the Detail table should be truncated, the Detail table should not contain more than
100 rows at any time.
What should you do?

A. You should create a rule
B. You should create a column-level CHECK constraint
C. You should create a table-level CHECK constraint
D. You should create a trigger

Answer: D

Explanation: In the scenario you should remember that you are able to control the
number of rows in the table by creating an INSTEAD OF INSERT trigger which
will fire and use the COUNT (*) function to calculate the rows in the required table.

Incorrect Answers:
A: This option should not be used in the scenario because rules provide backward
compatibility by offering a subset of the functionality of CHECK constraints and can not be used to determine the number of rows.
B, C: This option should not be used in the scenario as this would require and additional row to be created on the table and the table should not have more than 10 rows at any given time.

QUESTION 53
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. Certkiller .com has its
headquarters in Chicago and branch office in Miami.
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. During the
course of the business day you receive additional instruction to create a view in a
SQL Server 2000 database. You want to create indexes on the view to improve the
performance.
You have successfully created the views but when you attempt to implement the
same view in the branch office only the view is created and not the indexes on the
view. You are required to determine the cause of the problem.
What could the cause be?

A. Tables are referenced by the view by using two-part names rather than four-part
names.
B. In the branch office the ANSI_NULLS option was set to on when the view was
created.
C. The SCHEMABINDING option is included in the view.
D. The Standard Edition of SQL Server 2000 is used at the branch office.

Answer: D

Explanation: In the scenario you should remember that a view is a SELECT
statement that is stored in a database and is dynamically executed when the view is
referenced in a query or another view.

Incorrect Answers:

A: This option is not what is wrong in the scenario because all referenced tables must be referenced by using two-part qualified names in the scenario.
B, C: This option is not what is wrong tin the scenario because this is one of the
requirements that should be met to be able to create indexed views.

QUESTION 54
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. A network
user named Rory Allen has recently decided to leave the company, she created a
table named BillAccount in a SQL Server 2000 database and loaded some
information into it before leaving. You are a member of the db_dlladmin database
role. You want to drop the BillAccounts table and issue the statement below:
DROP TABLE BillAccounts You receive the following message:
Server: Msg 3701, Level 11, State 5, Linr1
Cannot drop the table 'BillAccounts', because it does not exist in the system
catalog You are required to drop the table.
What should you do?

A. A system administrator should be asked to grant you the DROP TABLE permission in
the database.
B. A member of the db_owner database role should be asked to delete the table.
C. A member of the sysadmin server role should be asked to delete the table.
D. You use a two-part qualified name for the table in the DROP TABLE statement.

Answer: D

Explanation: In the scenario you should keep in mind that SQL Server 200 database
objects names can be preceded by up to three qualifiers. The database assumes that
the name of the current database owner and the user names in the current database
is mapped to the login of the current user.

Incorrect Answers:

A: This option should not be used in the scenario because the database assumes that the name of the current database owner and the user names in the current database is mapped to the login of the current user.
B, C: This option should not be considered in the scenario because the object
created by members of the roles in question belongs to dbo.

QUESTION 55
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
Certkiller .com network users frequently query a table named Parts which is used to
store the information about prototype modifications shown below:

The network users typically use queries which return the Part and price. You are
required to optimize the performance of such queries by adding or modifying
indexes. The table currently has a clustered index on the PartID column and a
nonclusterd index on the SKU column.
What should you do?

A. You should create a clusterd index on the Description column.
B. You should create a nonclustered index on the LocationID column.
C. The clusterd index on the PartID column should be changed to nonclustered.
D. The nonclusters index on the SKU column should be changed to include the
LocationID column.

Answer: D

Explanation: You should remember that indexes can be used to improve the
performance of such particular queries in the scenario. By creating a composite
nonclustered index of the two columns would likely improve query performance.

Incorrect Answers:
A, C: This option should not be used in the scenario as you would gain no performance
increase by making the changes specified in the answer.
B: There is no need for you to cerate a nonclustered index on the LocationID column
as its is not used to search for rows that meet the specified criteria.

QUESTION 56
You work as a database developer at Certkiller .com. You have recently finished the
design of Certkiller .com's SQL Server 2000 database.
You are now in the process of deleting objects in the database that is not being used
anymore. You then find that you are unable to drop the 1997Sales view. After a
brief investigation, you discover that the view has the following characteristics:
1. There is a clustered index on the view.
2. The sales database role has permissions on the view.
3. The view uses the WITH SCHEMABINDING option.
4. A schema-bound inline function references the view.
5. An INSTEAD OF trigger is defined on the view.
Of the following options, which is the one that you should perform before you will
be able to drop the view?

A. Drop the clustered index on the view.
B. Remove all permissions from the view.
C. Remove the WITH SCHEMABINDING option from the view.
D. Remove the WITH SCHEMABINDING option from the function that is referencing
the view.
E. Disable the INSTEAD OF trigger on the view.

Answer: D.

Explanation: The CREATE FUNCTION supports a SCHEMABINDING clause
that binds a function to the schema of an object, such as a table, a view, or another
user-defined function that it references. An attempt to alter or drop any object
referenced by a schema-bound function will fail. The ALTER FUNCTION can be
used to remove the SCHEMABINDING clause by redefining the function without
specifying the WITH SCHEMABINDING clauses.

Incorrect answers:

A: A clustered index on the view would not prevent it from being deleted.
Note: A clustered index alters the physical storage order of the data in the table. A table can contain only one clustered index and can comprise multiple columns. A clustered index is particularly efficient on columns that are often searched for a range 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. If a column is frequently used to sort the data retrieved from a table, it can be advantageous to cluster the table on that column to save the cost of a sort each time the column is queried. When an index is no longer needed, it can be deleted from a database and the storage space it uses can be reclaimed. Only the owner of a table can delete indexes on that table, and the owner of the table cannot transfer this permission to other users. However, members of the db_owner and db_ddladmin fixed database roles and sysadmin fixed server role can drop any database
object by explicitly specifying the owner of the object in the DROP VIEW statement.
Furthermore, indexes created on any views or tables are automatically deleted when the view or table is dropped, and since the DROP VIEW statement can be executed against indexed views, indexes do not prevent the dropping of a view.
B: A view can be deleted if it is no longer needed, or if the view definition and the
permissions associated with it need to be cleared. When a view is deleted, the tables and the data upon which it is based are not affected. Thus, the permissions that pertain to a view do not prevent the view from being dropped.
C: The SCHEMABINDING clause is supported by the CREATE VIEW statement and
binds the view to a schema. Views or tables participating in a view created with the
schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Dropping the SCHEMABINDING on the view would
make it possible to drop those other views or tables. This SCHEMABINDING does not,
however, prevent the view itself from being dropped.
E: Disabling a trigger on the view would not affect the possibility of dropping the view.
Note: Triggers are a special type of stored procedure that execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. INSTEAD OF triggers allow views that are not updateable to support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. A trigger can be deleted when it is no longer needed. When a trigger is deleted, the table and the data upon which it is based are not affected. While permissions to delete a trigger default to the owner of the table or view upon which the trigger is defined, deleting the table or view will automatically delete any triggers on that table or view.

QUESTION 57
You work as a database developer at Certkiller .com. Certkiller .com has recently
acquired a company named Test Labs, Inc. You have received instruction from the
CIO to design a SQL Server 2000 database for Test Labs, Inc. that will be
distributed with an application to numerous companies.
You have created a number of stored procedures in the database that contains
confidential information, and you now want to prevent the other companies from
viewing.
Which of the following will allow you to achieve this objective?

A. Remove the text of the stored procedures from the syscomments system table.
B. Encrypt the text of the stored procedures.
C. Deny SELECT permissions on the syscomments system table to the public role.
D. Deny SELECT permissions on the sysobjects system table to the public role.

Answer: B.

Explanation:
The WITH ENCRYPTION clause can be used to ensure that the stored procedure's
definition cannot be viewed by other users. The procedure definition is then stored in an unreadable form. When a stored procedure is encrypted, its definition cannot be
decrypted and cannot be viewed by anyone, including the owner of the stored procedure
or the system administrator.

Incorrect answers:

A: The syscomments system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column
contains the SQL definition statements, which are limited to a size of 4 MB. This table is stored in each database. None of the entries in syscomments should be deleted, as the corresponding stored procedure will not function properly when its entry in syscomments is manually removed or modified. To hide or encrypt stored procedure definitions, use CREATE PROCEDURE (or ALTER PROCEDURE) with the ENCRYPTION keyword.
C: The syscomments system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. Denying SELECT
permission for the public role would not hide the procedure definition from users.
D: The sysobjects system table contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. Denying SELECT permission for the public role would not hide the procedure definition from users.

QUESTION 58
You work as a database developer at Certkiller .com. The local bank recently hired
Certkiller .com to design a SQL Server 2000 database that will be used to store sales
data.
You have just completed the design, and now you want to create an indexed view in
this database. You make use of the script shown in the exhibit below to accomplish
this.

When the index creation fails, you are presented with an error message. You have to
ensure that the error message is eliminated and that the index is created.
Which of the following is a task that you should carry out to achieve this objective?

A. Add an ORDER BY clause to the view.
B. Add a HAVING clause to the view.
C. Change the NUMERIC_ROUNDABORT option to ON.
D. Change the index to a unique, nonclustered index.
E. Add the WITH SCHEMABINDING option to the view.

Answer: E.

Explanation:
To create a clustered indexed on a view, the view must meet a number of requirements.
One of these is that the view must be created with the SCHEMABINDING option.
SCHEMABINDING binds the view to the schema of the underlying base tables.
Reference: BOL, CREATE VIEW

Incorrect answers:

A: Although a view in which the SELECT statement that defines the view can use the
ORDER BY clause, the ORDER BY clause is not required in creating the clustered
index.
B: If GROUP BY is specified in a view, the SELECT list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify the HAVING,
CUBE, or ROLLUP clauses.
C: The NUMERIC_ROUNDABORT option would not prevent the creation of indexes
on the view.
Note: When NUMERIC_ROUNDABORT is set to ON, an error is generated when loss
of precision occurs in an expression. When it is set to OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result. SET NUMERIC_ROUNDABORT should be set to OFF when
indexes are created or manipulated on computed columns or indexed views to keep the
highest possible precision.
D: A clustered index determines the physical order of data in a table while a
nonclustered index does not. A unique index on a view can be either clustered or
nonclustered. A clustered index gives the best performance. We should therefore try to create a clustered index.

QUESTION 59
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
network users of Certkiller .com access the database using several custom
applications based on stored procedures. Certkiller .com has recently finished several new products.
The results of the new products has resulted in the network users reporting that
operations involving the products table have become slower. The performance of
operations that do not involve the products table has not deteriorated. You are
required to restore performance of the affected operations to their original level.
What should you do?

A. The EXECUTE statement that call the stored procedure in the applications should be
changed to include the RECOMPILE option.
B. The sp_updatestats stored procedure should be executed against the Products table.
C. The UPDATE STATISTICS statement should be issued against the database.
D. The sp-Recompile stored procedure should be executed against the Products table.

Answer: D

Explanation: In the scenario you should remember that SQL Server compiles
execution plans for the stored procedures and queries the first time they are issued.
So making use of this answer will definitely improve the performance of the custom
applications used by the network users.

Incorrect Answers:

A: This option should not be used in the scenario because the performance of the
application will not be improved since the stored procedure would be recompiled every
time that it is called.
B: This option should not be used because this stored procedure executes an UPDATE
STATISTICS statement against each user-defined table in the current database.
C: This option should not be considered for usage in the scenario because you are only able to issue this command against a table not an entire database.

QUESTION 60
You work as a database developer at Certkiller .com. Certkiller .com has a SQL
Server 2000 database that includes information about companies situated in specific
postal codes. The database has table named Company that contains this
information.
Currently, the database contains company data for five different postal codes.
Currently the number of companies in a specific postal code varies from 10 to 5,000,
and more companies and postal codes will be added to the database over time.
You are in the process of creating a query to retrieve information from the
database. You have to accommodate new data by making only minimal
modifications to the database. Also, the performance of your query should not be
influenced by the number of companies returned.
Which of the following is a task that you should execute to create a query that
performs consistently and reduces future maintenance?

A. Create a stored procedure that requires a postal code as a parameter. Include the
WITH RECOMPILE option when the procedure is created.
B. Create one stored procedure for each postal code.
C. Create one view for each postal code.
D. Split the Company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.

Answer: A.

Explanation:
We need to meet the following requirements:
1. The new data, more companies and postal codes, must be added with minimal changes
to the database.
2. Future maintenance should be minimized.
3. The performance of the query should not be affected by the number of companies
returned.
4. The query should perform consistently.
A single procedure with a postal code parameter meets the first requirement. We only add a single procedure and make no further changes to the existing database.
The second requirement is met since no adjustments have to be made for additional
companies and postal codes.
The WITH RECOMPILE option will force a recompilation of the execution plan every
time the stored procedure is run. This could decrease the performance, especially if the stored procedure is used frequently.
The third requirement is met because the performance of the query is not affected by the companies returned, since the execution is recompiled every time the stored procedure is run.
The fourth requirement, a consistent performance of the query, is also met by the same argument.
Note: Specifying the WITH RECOMPILE option in stored procedure definition indicates
that SQL Server should not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. The use of the WITH RECOMPILE option causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed. This option should only be used when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time

70-350 MCSE Live Exams practice software

2009-01-14T22:24:00.001-08:00

This Download consist of 70-350 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Doanload - 70-350 MCSE Live Exams practice software

70-297 MCSE Live Exams practive software

2009-01-14T22:20:00.000-08:00

This Download consist of 70-297 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-297 MCSE Live Exams practive software

70-294 MCSE Live Exams Practice Software

2009-01-14T22:17:00.000-08:00

This Download consist of 70-294 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Doanload - 70-294 MCSE Live Exams Practice Software

70-270 Live Practice software

2009-01-14T22:06:00.000-08:00

This Download consist of 70-270 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Doanload - 70-270 Live Practice software

70-293 MCSE Practice Exams Software

2009-01-14T22:02:00.000-08:00

This Download consist of 70-293 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments .

Downlaod - 70-293 MCSE Practice Exams Software

70-291 Practice Exams Software

2009-01-14T21:58:00.000-08:00

This Download consist of 70-291 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments

Download - 70-291 Practice Exams Software

70-290 live practice software

2009-01-14T21:52:00.000-08:00

This Download consist of 70-290 live practice software from where you can write real exams for free.

Rules to Download :-
1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.5)Last Leave comments

Download - 70-290 live practice software

70-284 Live Practice software

2009-01-14T21:37:00.000-08:00

This Download consist of 70-284 live practice software from where you can write real exams for free.

Rules to Download :-

1)Always Donate
2)Register with the shared site where the dump is available ,its absolutely free
3)Most of the files are in Winrar zipped so download Winrar its also free.
4)If you can see any Video please use the appropiate player or download VLC players.
5)Last Leave comments .

Download - 70-284 Live Practice software

Exchange Server 2003 Training Kit E-book

2009-01-13T04:22:00.000-08:00

This section consist of complete MCSE Exchange Server 2003 Training Kit E-book
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Download - MCSE Exchange Server 2003 Training Kit E-book

MCSE XP Pro Training Kit E-book

2009-01-13T04:14:00.000-08:00

This section consist of complete MCSE MCSE XP Pro Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Doanload - MCSE XP Pro Training Kit E-book

MCSE server 2003 Network Infrastructure

2009-01-13T03:55:00.000-08:00

This section consist of complete MCSE MCSE server 2003 Network InfrastructureTraining kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Doanload - MCSE server 2003 Network Infrastructure

Planning and Implementing Active Directory Infrastructure

2009-01-13T03:47:00.000-08:00

This section consist of complete MCSE MCSA SQL 2000 Data Design Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar

Doanload - Planning and Implementing Active Directory Infrastructure

SQL 2000 Design Study Guide E-Book

2009-01-13T01:56:00.000-08:00

SQL Server 2000 System Administration E-book

2009-01-13T01:48:00.001-08:00

This section consist of complete MCSE MCSA SQl 2000 System Administration Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cos

Download - SQL Server 2000 System Administration E-book

MCSE TCP/IP Training E-book

2009-01-13T01:42:00.000-08:00

This section consist of complete MCSE MCSA Networking Essentials Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cos

Download - MCSE TCP/IP Training E-Book

MCSE Networking Essentials Training E-book

2009-01-13T01:18:00.000-08:00

70-229 part - 2

2009-01-12T04:22:00.000-08:00

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.

MCSE 2008 Training E-books

2009-01-12T01:50:00.000-08:00

This section consist of complete MCSE 2008 Training E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - Window_Server_2008_ (Intro).pdf

MCSE Win XP Professional E-Book

2009-01-12T01:30:00.000-08:00

This section consist of complete MCSE Win XP Professional E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE WIN XP Professional E-book

MCSE ISA Server 2000 E-book

2009-01-11T23:55:00.000-08:00

This section consist of complete MCSE ISA Server Training E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

DOWNLOAD - MCSE ISA Server 2000

70-229 Part - 1

2009-01-11T22:17:00.000-08:00

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.

MCSE 70-297 Training Kit

2009-01-11T05:14:00.001-08:00

This section consist of complete MCSE MCSA 70-297 Training kit E-books
Rules to Download and use this E-book

Dowload Tips

1)Always donate for person who has made this site :-) Kidding
2)Download Winrar as the download file is in winrar
3)While you click this link its will go to the shared site so please register with that site as the site for download is absolute free.
4)After Downloading extract the file with Winrar
5)The Video are in WMV format so use any player and if you don't find any player download VLC players.Its also free of cost.

Download - MCSE 70-297 Training Kit