Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Another interview question

Re: Another interview question

From: Bryan Dollery <bryan_at_bryan.prestel.co.uk>
Date: 1997/07/13
Message-ID: <33C90243.139721@bryan.prestel.co.uk>

This is a multi-part message in MIME format.

--------------5A3B0C5D18CD85706349D83F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

This response was quite cool until the end...

Michael Serbanescu wrote:
>
> Very interesting analysis. One correction, though: the required solution
> has to prevent users from entering non-integers into the column...

That isn't what the question said, it said

        "allow only integers into the column".

>, not change non-integer values already in the column into integers.

A trigger doesn't change the values already stored in the field, only new ones being entered.

>
> The solution I came up with (not at the interview, though): use the
> following CHECK constraint on the column:
>
> ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name>
> CHECK (<column_name>=TRUNC(<column_name>))
> EXCEPTIONS INTO <exception_table>;
>
> Using ROUND instead of TRUNC achieves the same result.
>
> Impact on the application: add a few lines of codes to trap the ORA-
> error message for constraint violation and display a meaningful message
> to the user (for most developers I worked with, this is optional). No
> index required, no disk space used up (except for the data dictionary).

Yes, but it depends on how many places in the application, and other future or existing aplications, update or insert data into this column of this table. This could add up to a lot of changes. Whilst small changes during implementation aren't monumental, during maintenance they can be devestating. Upto 90% of the costs of a piece of software are incured during the maintenance phase, therefore all changes during this phase are to be avoided.

The trigger idea makes a simple server-side change which affects all applications, and requires no modifications to the badly behaved application using this field.

>
> Easy to implement (and as soon as possible): How good are your typing
> skills ? (mine are lousy ). Seriously now, you can implement it any time
> the table is not in use (late at night (??), or maybe I could have done
> it during the interview if I could have come up with the solution then)
> with no impact on production.

See above note.

>
> Flexible: If they have to accept non-integers later on, DISABLE or DROP
> the constraint.
>

The same with the trigger.

> Generic: implemented in standard SQL DDL.
>

again.

And now for the bad bit...

> BTW, did the thought ever cross your mind that maybe they pay you too
> much ? Never heard of a "very well-paid consultant" who would tell
> his/her clients that "it cannot be done". I thought the opposite was
> true: they always tell their clients it can be done, for the right
> price, of course (otherwise known as "bullshitting the client"). I am a
> DBA, and I just added your name to the list of people I would not want
> to have as a developer (hey, I just realized how long the list has grown
> !)
>

  1. The thought has never crossed my mind that they pay me to much. Perhaps if they paid me more I would be able to suggest to them that they never hire a DBA with a bad attitude like yours.
  2. You obviously don't know many consultants. I've met loads of them who quite honestly say "it can't be done", and then politly suggest that a compromise may be reached whereby the customer drops some of the more constricing non-functional specifications, and the consultant provides the system they need, rather than the system they want.

Also, in an interview situation it isn't always a good idea to come up with a solution like yours. Sometimes an impossible situation is suggested just to see how one handles it. Because of the restrictive non-functional requirements of your spec I feel this may have been the case. To answer this question it would be correct to say "No.", as long as that refusal was fully qualified, and presented alternative solutions along with a list of the specification that they broke. This would enable the client to choose between a set of unatractive solutions looking for one which would be least painful.

3. I'm proud to be on your list. Perhaps one day someone with authority may find it, and hire everyone on it, because you are obviously such a bad judge of character and ability that your list will probably contain the best developers in the business.

4. I'm gald that you're a DBA, you and that dull job were made for each other.

5. It's really a shame about that last part of your mailing, this unprevoked attack turned your rather interesting thread into a complete sham. Andd I was enjoying it too.

Bryan



Bryan Dollery BSc.(Hons).
MD - ByteSmart Systems Ltd.

> Michael Serbanescu
> -----------------------------------------------------------------------
> Bryan Dollery wrote:
> >
> > Michael Serbanescu wrote:
> > >
> > > "A production database table contains a column defined as NUMBER(6,2).
> > > However, at this point in the life of the application using the table,
> > > *only integer numbers* can be used (although this may change in the
> > > future, hence the column format). All the values currently contained in
> > > the column are integers. Can you, the ORACLE DBA, provide as soon as
> > > possible, a solution to this problem, i.e. allow only integers in that
> > > column ? Your solution must have minimal or no impact on the
> > > application, be easy to implement, flexible and generic."
> > >
> > > Michael Serbanescu
> >
> > No.
> >
> > This is the only solution that fits the MUST HAVE requirements of:
> >
> > minimal or no impact on the application, easy
> > to implement, flexible and generic
> >
> > If these non-functional requirements were dropped however, a trigger
> > which truncated values to integers would do the trick. This would slow
> > down performance of the application by a factor dependant upon the
> > number of inserts into this column, hence violating the minimal, or no
> > impact policy.
> >
> > Also a trigger to do this would be tied to the particular column or the
> > particular table. Not particularly generic.
> >
> > This approach is however very easy to implement, and quite flexible. It
> > also fulfils the functional requirement nicely.
> >
> > However, I still wouldn't do it this way.
> >
> > I'm a very well paid development consultant, I would never want to be a
> > DBA ;-)
> >
> > Bryan
> > ---------------------------
> > Bryan Dollery BSc.(Hons).
> > MD - ByteSmart Systems Ltd.
> >
> > ---------------------------------------------------------------
> >
> > Name: vcard.vcf
> > Part 1.2 Type: text/x-vcard
> > Encoding: 7bit
> > Description: Card for Bryan Dollery

--------------5A3B0C5D18CD85706349D83F
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Bryan Dollery
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Bryan Dollery
n:              Dollery;Bryan
org:            ByteSmart Systems Ltd.
adr:            ;;;;;;England
email;internet: bryan_at_bryan.prestel.co.uk
note:           Consultant Software Engineer
=0A=
B.Sc. (Hons). Software Engineering
=0A=
MD - ByteSmart Systems Ltd.
x-mozilla-cpt: ;0
x-mozilla-html: TRUE
end: vcard

--------------5A3B0C5D18CD85706349D83F-- Received on Sun Jul 13 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US