UDB test doubts...

ritu zee

UDB test doubts...
Hi !

I was attempting UDB test paper on IBM website
and was confused by following questions:

1. Given the following:
CREATE TABLE tab1
(col1 INT CONSTRAINT notnul CHECK(col1 IS
NOT NULL),
col2 CHAR(10))

Which of the following will enforce uniqueness of col1
which currently does NOT contain duplicate values?

a. Create primary key on col1
b. Create unique index on col1
c. Create a cluster index on col1
d. Create unique constraint on col1

The answer given is 'b' but i thought it should have
been 'd'. Agreed that even 'b' will suffice but the
reason i preferred 'd' was because col1 is a 'not
null' column and a unique constraint does not allow
any nullabilty which seemed perfect for the given
scenario.

2. Given the following tables:
TEST_TAKEN
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL

CANDIDATE_DB2
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)

And the following information:
-- Candidate Ids are unique
-- A query which returns all addresses of individuals
who have taken a DB2 test
-- While reading a minimum number of physical pages,
an optimizer needs to consider the index to scan over
all rows of the table data.

Which of the following indexes must be created?

a. Primary key on candidate_db2 (CandidateID)
b. Unique clustered index on test_taken
(CandidateName)
c. Unique index on test_taken (CandidateID,
TestNumber)
d. Unique index on candidate_db2 (CandidateID,
CandidateName)
e. Unique clustered index on candidate_db2 (Candidate
ID, CandidateName)

For this, the answer given is 'e' while my answer was
'a' as ALTERing table to add CandidateID as primary
key should have ensured a unique index to be created
on CandidateID.

Thanks.

Ritu.

__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/



James Campbell

Re: UDB test doubts...
(in response to ritu zee)
Ritu

Hazading some guesses

1. While col1 might not _currently_ have any null occurances, the definition
of the column does allow them. All that is needed is to drop the "notnul"
constraint and col1 can have them. But if you've created the unique
constraint then you have done more than just enforced uniqueness, you've
forced additional steps to allow null values.

Buried away in the tests I took were the words "select the best answer".
While you might have given a possible answer, the additional constraints it
imposed might not make it the _best_ answer.

But since DB2 for OS/390 doesn't have a "create unique constraint"
statement, I could be wrong.

2. I wonder if this isn't a mistake in the test - the real requirement being
for a query to return _names_, not addresses. Or is it an example of
requiring you to know what the customer really wants when the specification
is for something quite different :-;

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]


-----Original Message-----
From: ritu zee [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 3:07 PM
To: [login to unmask email]
Subject: [DB2-L] UDB test doubts...


Hi !

I was attempting UDB test paper on IBM website
and was confused by following questions:

1. Given the following:
CREATE TABLE tab1
(col1 INT CONSTRAINT notnul CHECK(col1 IS
NOT NULL),
col2 CHAR(10))

Which of the following will enforce uniqueness of col1
which currently does NOT contain duplicate values?

a. Create primary key on col1
b. Create unique index on col1
c. Create a cluster index on col1
d. Create unique constraint on col1

The answer given is 'b' but i thought it should have
been 'd'. Agreed that even 'b' will suffice but the
reason i preferred 'd' was because col1 is a 'not
null' column and a unique constraint does not allow
any nullabilty which seemed perfect for the given
scenario.

2. Given the following tables:
TEST_TAKEN
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL

CANDIDATE_DB2
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)

And the following information:
-- Candidate Ids are unique
-- A query which returns all addresses of individuals
who have taken a DB2 test
-- While reading a minimum number of physical pages,
an optimizer needs to consider the index to scan over
all rows of the table data.

Which of the following indexes must be created?

a. Primary key on candidate_db2 (CandidateID)
b. Unique clustered index on test_taken
(CandidateName)
c. Unique index on test_taken (CandidateID,
TestNumber)
d. Unique index on candidate_db2 (CandidateID,
CandidateName)
e. Unique clustered index on candidate_db2 (Candidate
ID, CandidateName)

For this, the answer given is 'e' while my answer was
'a' as ALTERing table to add CandidateID as primary
key should have ensured a unique index to be created
on CandidateID.

Thanks.

Ritu.


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Terry Purcell

Re: UDB test doubts...
(in response to Sanjeev (CTS) S)
Ritu,

I have to agree with James on the first question. The best answer is b.
Create Unique Index. It is the actual unique index which enforces
uniqueness. Both creating a primary key or a unique constraint will actually
create a unique index. So there are 3 answers which are correct, but b. is
the best answer.

As for the second question, I'm not sure what the objective is. Unless there
is already a unique index on CandidateID, then creating the specified unique
index (CandidateID, CandidateName) does not enforce the required uniqueness.
The next two points about the query and minimising physical pages accessed,
then that will depend entirely on the query written. I could write it as an
inner join (with a distinct), a non-correlated IN, or a correlated EXISTS.
Each may have different index requirements. So I agree - a.

Unless there is more information in the test?
Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
James Campbell
Sent: Sunday, December 17, 2000 11:02 PM
To: [login to unmask email]
Subject: Re: UDB test doubts...


Ritu

Hazading some guesses

1. While col1 might not _currently_ have any null occurances, the definition
of the column does allow them. All that is needed is to drop the "notnul"
constraint and col1 can have them. But if you've created the unique
constraint then you have done more than just enforced uniqueness, you've
forced additional steps to allow null values.

Buried away in the tests I took were the words "select the best answer".
While you might have given a possible answer, the additional constraints it
imposed might not make it the _best_ answer.

But since DB2 for OS/390 doesn't have a "create unique constraint"
statement, I could be wrong.

2. I wonder if this isn't a mistake in the test - the real requirement being
for a query to return _names_, not addresses. Or is it an example of
requiring you to know what the customer really wants when the specification
is for something quite different :-;

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]


-----Original Message-----
From: ritu zee [mailto:[login to unmask email]
Sent: Monday, December 18, 2000 3:07 PM
To: [login to unmask email]
Subject: [DB2-L] UDB test doubts...


Hi !

I was attempting UDB test paper on IBM website
and was confused by following questions:

1. Given the following:
CREATE TABLE tab1
(col1 INT CONSTRAINT notnul CHECK(col1 IS
NOT NULL),
col2 CHAR(10))

Which of the following will enforce uniqueness of col1
which currently does NOT contain duplicate values?

a. Create primary key on col1
b. Create unique index on col1
c. Create a cluster index on col1
d. Create unique constraint on col1

The answer given is 'b' but i thought it should have
been 'd'. Agreed that even 'b' will suffice but the
reason i preferred 'd' was because col1 is a 'not
null' column and a unique constraint does not allow
any nullabilty which seemed perfect for the given
scenario.

2. Given the following tables:
TEST_TAKEN
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL

CANDIDATE_DB2
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)

And the following information:
-- Candidate Ids are unique
-- A query which returns all addresses of individuals
who have taken a DB2 test
-- While reading a minimum number of physical pages,
an optimizer needs to consider the index to scan over
all rows of the table data.

Which of the following indexes must be created?

a. Primary key on candidate_db2 (CandidateID)
b. Unique clustered index on test_taken
(CandidateName)
c. Unique index on test_taken (CandidateID,
TestNumber)
d. Unique index on candidate_db2 (CandidateID,
CandidateName)
e. Unique clustered index on candidate_db2 (Candidate
ID, CandidateName)

For this, the answer given is 'e' while my answer was
'a' as ALTERing table to add CandidateID as primary
key should have ensured a unique index to be created
on CandidateID.

Thanks.

Ritu.


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************








Sanjeev (CTS) S

Re: UDB test doubts...
(in response to James Campbell)
I agree with James for the 1st point but i would like to add few more thing,
Can we have more than one constraints clause coded in the single column in
the definition of CREATE or ALTER table ? If the CONSTRAINTS clause was not
coded for NOT NULL function the it could have been coded for UNIQUE .DB2 for
OS/390 doesn't have create constraints statement but it does have UNIQUE
clause which requires Unique index to be created.Infact i went thru' V7
books for UDB and it also do not have any CREATE CONSTRAINTS statements.I
hope i have seen the correct things.
For second question : The requirement "While reading a minimum number of
physical pages, an optimizer needs to consider the index to scan over all
rows of the table data." can be "best" fulfilled in the best manner if we
have cluster index in the CANDIDATE ID of CANDIDATE_DB2.(just after
presuming that it asks for the better performance depending on all the
alternatives)
Questions of these kind really creates lots of confusion but still the best
answers to be choosen and the answers which we think are most of the times
do not matches to the correct answers

HTH
Regards
Sanjeev



> -----Original Message-----
> From: James Campbell [SMTP:[login to unmask email]
> Sent: Monday, December 18, 2000 10:32 AM
> To: [login to unmask email]
> Subject: Re: UDB test doubts...
>
> Ritu
>
> Hazading some guesses
>
> 1. While col1 might not _currently_ have any null occurances, the
> definition
> of the column does allow them. All that is needed is to drop the "notnul"
> constraint and col1 can have them. But if you've created the unique
> constraint then you have done more than just enforced uniqueness, you've
> forced additional steps to allow null values.
>
> Buried away in the tests I took were the words "select the best answer".
> While you might have given a possible answer, the additional constraints
> it
> imposed might not make it the _best_ answer.
>
> But since DB2 for OS/390 doesn't have a "create unique constraint"
> statement, I could be wrong.
>
> 2. I wonder if this isn't a mistake in the test - the real requirement
> being
> for a query to return _names_, not addresses. Or is it an example of
> requiring you to know what the customer really wants when the
> specification
> is for something quite different :-;
>
> /* standard disclaimer */
> James Campbell
> DBA
> Hansen Corporation, Doncaster
> +61 3 9843 8442
> [login to unmask email]
>
>
> -----Original Message-----
> From: ritu zee [mailto:[login to unmask email]
> Sent: Monday, December 18, 2000 3:07 PM
> To: [login to unmask email]
> Subject: [DB2-L] UDB test doubts...
>
>
> Hi !
>
> I was attempting UDB test paper on IBM website
> and was confused by following questions:
>
> 1. Given the following:
> CREATE TABLE tab1
> (col1 INT CONSTRAINT notnul CHECK(col1 IS
> NOT NULL),
> col2 CHAR(10))
>
> Which of the following will enforce uniqueness of col1
> which currently does NOT contain duplicate values?
>
> a. Create primary key on col1
> b. Create unique index on col1
> c. Create a cluster index on col1
> d. Create unique constraint on col1
>
> The answer given is 'b' but i thought it should have
> been 'd'. Agreed that even 'b' will suffice but the
> reason i preferred 'd' was because col1 is a 'not
> null' column and a unique constraint does not allow
> any nullabilty which seemed perfect for the given
> scenario.
>
> 2. Given the following tables:
> TEST_TAKEN
> TestName CHAR(50) NOT NULL
> TestNumber INTEGER NOT NULL
> TestScore INTEGER NOT NULL
> CandidateID INTEGER NOT NULL
>
> CANDIDATE_DB2
> CandidateName CHAR(20) NOT NULL
> CandidateID INTEGER NOT NULL
> Address CHAR(100) NOT NULL
> CandidatePhoto BLOB(1M)
>
> And the following information:
> -- Candidate Ids are unique
> -- A query which returns all addresses of individuals
> who have taken a DB2 test
> -- While reading a minimum number of physical pages,
> an optimizer needs to consider the index to scan over
> all rows of the table data.
>
> Which of the following indexes must be created?
>
> a. Primary key on candidate_db2 (CandidateID)
> b. Unique clustered index on test_taken
> (CandidateName)
> c. Unique index on test_taken (CandidateID,
> TestNumber)
> d. Unique index on candidate_db2 (CandidateID,
> CandidateName)
> e. Unique clustered index on candidate_db2 (Candidate
> ID, CandidateName)
>
> For this, the answer given is 'e' while my answer was
> 'a' as ALTERing table to add CandidateID as primary
> key should have ensured a unique index to be created
> on CandidateID.
>
> Thanks.
>
> Ritu.
>
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **********************************************************************
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------