-
Okay, as part of a document I'm writing, I am basically dissecting the default SCOTT/TIGER schema provided by Oracle. The problem is that I can't find any documentation on it. With a well-designed schema, this would not be a problem. I have to say, however, that what they provide as an example for others to follow is perfectly disgraceful. Basically, I have absolutely no idea what the BONUS table is supposed to store, especially since the one they provide is blank (nice, eh?).
Anyone out there have any insights or links for me?????
Thanks in advance,
- Chris
-
No clue since the table containts redudant data and I cant see which column can be a PK to start with :D
-
-
Come on guys and gals, *somebody* out there has to have a clue about this one.
PPPPPPllllllllleeeeeaaaaaaassssssseeeeeeeee :)
- Chris
-
Here's something out of 'Oracle: A Beginner's Guide' from Oracle Press. (pages 191-192)
CREATE TABLE bonus (
emp_id number(4),
emp_class varchar2(2),
fac_id varchar2(3), -- factory Id
bonus_amt number);
Insert into bonus values (123,null,'AE',2000);
Insert into bonus values (124,null,'AF',2200);
Insert into bonus values (125,null,'AH',1200);
Insert into bonus values (126,null,'AH',1200);
Insert into bonus values (127,null,'AF',1200);
Insert into bonus values (128,null,'AT',1500);
Insert into bonus values (129,null,'AT',1100);
Insert into bonus values (130,null,'AU',1400);
Insert into bonus values (131,null,'AE',200);
Insert into bonus values (132,null,'AF',220);
Insert into bonus values (133,null,'AG',120);
Insert into bonus values (134,null,'AG',200);
Insert into bonus values (135,null,'AG',200);
Insert into bonus values (136,null,'AU',1400);
Insert into bonus values (137,null,'AH',100);
Insert into bonus values (138,null,'AU',1400);
The book uses this data to show how to create reports.
-
Hey, thanks for the effort.
Unfortunately, that bonus table does not match the structure of the bonus table in the default SCOTT schema.
The SCOTT.BONUS table looks like:
BONUS
-EMPNO
-JOB
-SAL
-COMM
What's odd is that there is no PK and the EMP table already has all 4 of these columns.
What I can glean from the various SQL examples in the document is very little. The only use for the table I can find is when they search it for employees that have bonuses. Basically, simple existence in the table signifies you got a bonus. How much or why we don't know. We also don't know why this isn't just a flag in the EMP table, or why we have these other 3 fields in there, or why there is no PK.
Sorry for the rant, but I just think it is so ridiculous that this is there 'example' database that they use to teach people their product.
Again,
***********************************
Any and all help would be appreciated!!
***********************************
Thanks,
- Chris
-
"this is there 'example' database that they use to teach people their product. "
look into designer/2000 repository:
select table_name, count(1) from user_tab_columns group by table_name order by 1 desc
So, it is the same story as with GOTO operator: not for beginers, only for experienced programmers.
-
Sorry, but I have no clue what you just said :)
Yes, it is the 'example' database that I am asking about
I don't use Designer/2000 so I don't have such a repostory. Even if I did, the best that SELECT would give me is the table name. Since I already have the schema, I already have access to all the table and column names. What I'm asking about is the logical reasoning behind the design of the table:
- What purpose does it serve?
- What data is each of the fields supposed to hold and why?
From the design of the table, I am left with:
- Why is there no PK?
- Why does it only have 4 fields that are already in the EMP table?
- If it is a M:1, then where is the new info
- If it is 1:1, then where is the new info
- If these fields are not meant to hold the same values as they do in EMP, then what data are they supposed to hold?
- As it is called BONUS, where is the monetary information for said bonus?
Again,
***********************************
Any and all help would be appreciated!!
***********************************
Thanks again,
- Chris
-
Hi. You get the dubious honor of being my first reply on this board ( I just joined the site this morning). Anyway, I am still very new to this and going through the classes. Seems to me that I remember the instructors having to run some scripts(supplied by oracle) to creat and populate that training db (emp, dept, bonus, etc). Just what I overheard them talking about.
-
ok, the some statistics about designer/2000 - this is comercial product intended to help develop good db's. Lets look into the product itself:
table: SDD_ELEMENTS
no of columns: 184
some column names:
EL_2ND_WITHIN_ID
EL_2ND_WITHIN_NAME
EL_3RD_WITHIN_ID
EL_4TH_WITHIN_ID
EL_5TH_WITHIN_ID
EL_6TH_WITHIN_ID
EL_7TH_WITHIN_ID
EL_8TH_WITHIN_ID
EL_9TH_WITHIN_ID
EL_USRX0
EL_USRX1
EL_USRX2
EL_USRX3
EL_USRX4
EL_USRX5
EL_USRX6
EL_USRX7
EL_USRX8
EL_USRX9
EL_USRX10
EL_USRX11
EL_USRX12
EL_USRX13
EL_USRX14
EL_USRX15
EL_USRX16
EL_USRX17
EL_USRX18
EL_USRX19
any more questions?
But damn, I understand those developers.
-
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
EMPNO JOB SAL COMM
1043 4 250 comm
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
1043 4 50 comm
But say in that pay period he also won an award that gives him another $100, which would insert
1043 4 100 comm
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 [email protected].
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.
