How to deal this problem?

[gazal]

Hi Friends

I am using ORacle 8i, Forms 6i to develop an application in which i have to store the following data, for which i have created a table but i think i m doing lot of data duplication so how to store it more efficiently

The data is in the following manner:

Qualifications
Sr.No Department Phd. Msc. Bsc. Tech Tot
===== =========== ==== ==== === ==== ===
1 Research 5 10 5 2 22
2 Production 1 2 4 3 10
3 Quality Control 10 8 1 0 20
4 Packaging 4 2 8 1 15
5 Others 1 2 4 3 10

Now i have made one simple table like this:

Sr.No
Department
Qualification
Total

So for each deaprtment there are 5 records, that means there are total 25 records for 1 transaction, which i think is not the right way,

Though i have looked at Nested TAbles but was not able to figure out how to use them in such scenario...

Please guide its urgent.

Regards
Gazal

 

[tyb]

y don' u create a seperate table for department and refer the department to the corresponding qualifications.

rgrds

 

[gazal]

hi tyb

thanks for the suggestion but that wont prevent the data repetition, in that case also there will be 5 records for each department.

Nested tables seems to be a way but no experts of nested tables here i guess....

Gazal

 

[hoinz]

two ideas:

1) Are there only those qualifications you gave in your example?
If so, I would suggest one table with these columns:
Sr.No
Department
No_Phd
No_Msc
No_Bsc
No_Tec

2) I suggest you omit column 'Tot'
If needed, it can easily be computed.
And you won't get inconsistencies, if someone updates Phd and forgets to update Tot.
And you may even create a view with the same columns as your table, and an additional column Tot.

hth

 

[gazal]

thansk hoinz

but the values are not constant, i need it to be dynamic, today there are only 5 departments and qualifications but tomorrow it can be more than that 'n' number may be...

gazal

 

[SantaMufasa]

Gazal,

Nested tables seems to be a way but no experts of nested tables here i guess

I guess you guessed wrong. Post sample data here that shows data in the format:
Sr.No
Department
Qualification

...and we can show you how to produce the report you want for as many departments as you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 11:04 (25Oct04) UTC (aka "GMT" and "Zulu"),
@ 04:04 (25Oct04) Mountain Time

 

[gazal]

Hi Musafa

here is the sample data once more for ur reference.
please show me how do i insert, fetch and update the details in nested table from forms.

Qualifications
Sr.No Department Phd. Msc. Bsc. Tech Tot
===== =========== ==== ==== === ==== ===
1 Research 5 10 5 2 22
2 Production 1 2 4 3 10
3 Quality Control 10 8 1 0 20
4 Packaging 4 2 8 1 15

regards
gazal
5 Others 1 2 4 3 10

 

[SantaMufasa]

Gazal,

I presumed that your data represent the number of persons in each department with Doctorates, Masters of Science, Bachelors of Science, and Technical degrees/certificates. Those data will come from individual person records, not from data that appears in the format, above, right? In other words, how do you gather your summary data if it does not come from individuals' records?

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 11:16 (25Oct04) UTC (aka "GMT" and "Zulu"),
@ 04:16 (25Oct04) Mountain Time

 

[gazal]

hi musafa

the data is to be entered and saved in similar fashion as its shown, coz its just a part of a registration form, its got nothing to do with individual person and all, its the details of employees in each department of a specific company...

any more suggestions...

gazal

 

[SantaMufasa]

Gazal,

...its got nothing to do with individual person and all, its the details of employees in each department of a specific company...

I believe it has everything to do with individual persons since it is the individual persons that represent the degrees that appear in the report. Next you said, "...its the details of employees in each department..." Now that is correct...you have a table of details, which you want to summarize.

Therefore, I've created a solution that represents the two tables that you should query: "Department" and "Worker". (By the way, your original example had an addition error on the "Quality Control" line. ) Here are sample data that represent the contents of your two tables if they represent your output, above:

SQL> col degree_code heading "Degree|Code" format a6
SQL> select * from gazal_dept;

   DEPT_ID DEPT_NAME
---------- --------------------
         1 Research
         2 Production
         3 Quality Control
         4 Packaging
         5 Others

5 rows selected.

SQL> select dept_id, degree_code from gazal;

           Degree
   DEPT_ID Code
---------- ------
         1 P
         1 P
         1 P
         1 P
         1 P
         2 P
         3 P
         3 P
         3 P
         3 P
         3 P
         3 P
         3 P
         3 P
         3 P
         3 P
         4 P
         4 P
         4 P
         4 P
         5 P
         1 M
         1 M
         1 M
         1 M
         1 M
         1 M
         1 M
         1 M
         1 M
         1 M
         2 M
         2 M
         3 M
         3 M
         3 M
         3 M
         3 M
         3 M
         3 M
         3 M
         4 M
         4 M
         5 M
         5 M
         1 B
         1 B
         1 B
         1 B
         1 B
         2 B
         2 B
         2 B
         2 B
         3 B
         4 B
         4 B
         4 B
         4 B
         4 B
         4 B
         4 B
         4 B
         5 B
         5 B
         5 B
         5 B
         1 T
         1 T
         2 T
         2 T
         2 T
         4 T
         5 T
         5 T
         5 T

76 rows selected.

SQL> col a heading "Sr|No" format 99
SQL> col b heading "Department" format a15
SQL> col c heading "Phd." format 999
SQL> col d heading "Master|Science" format 999
SQL> col e heading "Bachelor|Science" format 999
SQL> col f heading "Tech.|Cert." format 999
SQL> col g heading "Total" format 999
SQL> break on report
SQL> compute sum of c d e f g on report
SQL> select  d.dept_id a
  2   ,d.dept_name b
  3   ,sum(decode(degree_code,'P',1,0))c
  4   ,sum(decode(degree_code,'M',1,0))d
  5   ,sum(decode(degree_code,'B',1,0))e
  6   ,sum(decode(degree_code,'T',1,0))f
  7   ,count(degree_code) g
  8  from gazal p, gazal_dept d
  9  where p.dept_id = d.dept_id
 10  group by d.dept_id, d.dept_name;

 Sr                       Master Bachelor Tech.
 No Department      Phd. Science  Science Cert. Total
--- --------------- ---- ------- -------- ----- -----
  1 Research           5      10        5     2    22
  2 Production         1       2        4     3    10
  3 Quality Control   10       8        1     0    19
  4 Packaging          4       2        8     1    15
  5 Others             1       2        4     3    10
                    ---- ------- -------- ----- -----
sum                   21      24       22     9    76

5 rows selected.

Let me know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 11:50 (25Oct04) UTC (aka "GMT" and "Zulu"),
@ 04:50 (25Oct04) Mountain Time

 

[sem]

gazal, have you ever heard about RELATIONAL DATABASES, NORMALIZATION or MASTER-DETAIL forms?

Regards, Dima

http://seminihin.narod.ru

 

[gazal]

Hi Musafa

Thanks for the example and the time devoted for me.

Here is the point, my whole idea of using nested tables or varrays was to avoid storing 75 records for 1 Registration, though your Sql Report will be of great use to me.

And Dima i know very well about RDBMS and Master Details Forms and Relations but thanks anyway.

Regards
Gazal

 

[SantaMufasa]

Gazal,

The question that I have remaining is, "What are the source characteristics of your reference data? Are you gathering degree information from the records of individuals, or are you receiving summary information (i.e., totals of degree holders) from your data source?"

If you are receiving and storing summary information about degree holders, then you certainly do not need 75 rows of data. You just create your report by displaying virtually "input images" of your summary data.

If, however, your data are coming from the individual records of degreed personnel, then why are you going to extra trouble of "using nested tables or varrays"? Just query the individuals' detail data using the query I provided you.

Let me know...I'm curious about your environment in this matter.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 08:32 (28Oct04) UTC (aka "GMT" and "Zulu"),
@ 01:32 (28Oct04) Mountain Time