DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Help! What is Scott.Bonus?

  1. #11
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay,

    As for creating and populating the schema - It is created and it is populated. There are 4 tables. EMP, DEPT, SALGRADE and BONUS.
    EMP, DEPT and SALGRADE all come with data.
    BONUS does not.
    My problem, again, is to try to determine what was going through their demented little minds when they designed this aberration.
    The intentions behind the EMP, DEPT and SALGRADE tables are decipherable, although poorly implemented.

    The problem is that I don't know what purpose the BONUS table is supposed to serve.

    As for the DESIGNER repository, I think I see where you're going. That certainly does appear obtuse. I am in complete agreement that Oracle seems to have a proclivity for creating confusing data models. I also agree that both data models are, uh, odd, shall we say. Unfortunately, that does not get me any closer to an answer :).

    Again,

    ***********************************
    Any and all help is appreciated!!
    Keep 'em coming! :)
    ***********************************

    Thanks again,

    - Chris



  2. #12
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    I dont' have Designer on my PC anymore, but the tutorial for it did load some data into these tables. I wonder if this table gets populated as you work through the scenarios in the Tutorials, and therefore is not pre-populated. It may also guide you to make primary keys and such.
    Isn't that a possibility?

  3. #13
    Join Date
    Dec 2000
    Posts
    43
    Chris this is something even stranger with the demo build script of mine. This is the structure of the BONUS table I have:
    CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER,
    COMM NUMBER
    );
    So we can easily say that don't use the SCOTT user as your example to teach design :-)

  4. #14
    Join Date
    Jun 2000
    Posts
    417
    Possibly the bonuses are modifiers so to speak for their salary.

    I'm not sure what COMM is as I don't have the tables or definition here, maybe commission but can be ignored for now

    Eg a record in EMP would be
    <font face="courier">
    EMPNO JOB SAL COMM
    1043&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;250&nbsp;comm
    </font>
    Obviously EMPNO is a primary key because that specifies base info about the employee.

    Now in the BONUS table, say it's Christmas time and in the next paycheck he's supposed to get a $50 dollar bonus. It adds
    <font face="courier">
    1043&nbsp;&nbsp;4&nbsp;&nbsp;50&nbsp;&nbsp;comm
    </font>
    But say in that pay period he also won an award that gives him another $100, which would insert
    <font face="courier">
    1043&nbsp;&nbsp;4&nbsp;&nbsp;100&nbsp;&nbsp;comm
    </font>
    There can't be a primary key on EMPNO because then he'd only be able to have one bonus.

    Just a stab in the dark but maybe that's the idea.

  5. #15
    Join Date
    Nov 2000
    Posts
    212
    if interested about bonus references in forms help, provide me with e-mail: I can send help topics on it.
    Here are some of them:

    1. The following statement deletes from the bonus table all employees whose sales were below quota:
    DELETE FROM bonus WHERE sales_amt < quota;

    2. The following examples show various forms of INSERT statement:
    INSERT INTO bonus SELECT ename, job, sal, comm FROM emp
    WHERE comm > sal * 0.25;


    3. The following cursor FOR loop calculates a bonus, then inserts the result into a database table:
    DECLARE
    bonus REAL;
    CURSOR c1 IS SELECT empno, sal, comm FROM emp;
    BEGIN
    FOR c1rec IN c1 LOOP
    bonus := (c1rec.sal * 0.05) + (c1rec.comm * 0.25);
    INSERT INTO bonuses VALUES (c1rec.empno, bonus);
    END LOOP;
    COMMIT;
    END;

    From all it looks if bonus is really a temporary table to hold results of some calculations. That explains a lot.

  6. #16
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    maachan:
    While certainly possible, it would still leave the rest of the Oracle community with a poor data model. This is not a model installed specifically for Designer, but installed with the base product (RDBMS) and used throughout the documentation as a basis for all their SQL examples.

    **************************************
    If, however, anyone has Designer, and it *does* make modifications to these tables, I would be interested in that as well.
    **************************************

    arshiah:
    You are correct, it is ENAME, not EMPNO - I was doing this from memory and forgot that it was that bad :). (I have it installed at home).

    pwoneill:
    That's about the best I have come up with as well. I just didn't want to taint anyone's thoughts with my theory on that one. The *only* assumption that makes the least bit of sense is that the bonus table holds a list of bonuses per employee, with the SAL and COMM fields actually holding the part of the bonus as based on the SAL and COMM fields in the EMP table. Barring any better suggestions, that is the assumption that I will go with.

    What a sad, sad data model :)

    Thanks to everyone that responded.

    **************************************
    We have a working assumption, but if anyone has any better info or alternate suggestions, please feel free to speak up.
    **************************************

    Thanks again,

    - Chris


  7. #17
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, I seemed to overlap with LND's response. Unfortunately, example 1 uses a field that is not actually in the table. Example 3 uses a different table called bonuses. Example 2 seems to be the only one that targets the SCOTT.BONUS table. If so, it is disturbing that it inserts the values directly from the EMP table. This would invalidate the theory pwoneill and I had. Disturbing, to say the least.

    As for e-mail, it is chrisrlong@hotmail.com.

    Thanks again,

    - Chris

  8. #18
    Join Date
    Oct 2000
    Posts
    80
    Chris.
    I don't know Oracle's thinking behind their demo DBs, but they now supply another demo DB, called DEMO, which can be created by running DEMO.SQL. This little DB is much better than Scott/Tiger, has vastly more data, more tables, and more interesting interrelationships.
    John Doyle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width