-
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
-
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?
-
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 :-)
-
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 4 250 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 4 50 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 4 100 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.
-
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.
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|