Help Normalizing a Big Table to 3 Sub Tables

Asked By 280 points N/A Posted on -
qa-featured

Hi,

I am assigned for normalizing an established database written in MySQL. The database consists of only 1 table, with about 18 columns. All of them having information about the members(personal detail), their address, and job detail. I exported the database to Oracle with some effort, but I do not know how to divide this big table to 3 smaller ones. Here is the main table:
 
ID VARCHAR2(500),
NAME VARCHAR2(500),
ADDR VARCHAR2(500),
AREA_CODE VARCHAR2(500),
TEL VARCHAR2(100),
AINS VARCHAR2(100),
YEARPASS VARCHAR2(100),
CENTRE VARCHAR2(500),
DOP VARCHAR2(100),
DUES VARCHAR2(100),
DIPNO VARCHAR2(100),
CODE VARCHAR2(100),
DEPT VARCHAR2(100),
DOB VARCHAR2(100),
GENDER VARCHAR2(100),
EMAIL VARCHAR2(100),
RELIGION VARCHAR2(100),
FAX VARCHAR2(100),
FOREIGN VARCHAR2(100),
MEM_ID NUMBER
 
I have designed the children tables as follows. They will also use lookup tables, for National ID, Department etc.
 
CREATE TABLE PERSONNEL (
ID NUMBER,
MEM_NO VARCHAR2(50),
NAME VARCHAR2(50),
TEL VARCHAR2(50),
GENDER NUMBER,
ADDRESS VARCHAR2(500),
AREA NUMBER,
DOB DATE,
EMAIL VARCHAR2(50),
RELIGION NUMBER,
FAX VARCHAR2(50)
)
 
CREATE TABLE professional_detail(
Id NUMBER,
Ains NUMBER,
Year pass NUMBER,
Centre NUMBER,
Dipno VARCHAR2(30),
Code NUMBER,
Dept NUMBER
)
 
CREATE TABLE ACCOUNT_INFO(
ID NUMBER,
DUE VARCHAR2(10),
DOP DATE
)
 
Problem is that some of the columns in the big table is NULL and almost all of them do not follow any typical rule. I tried to create a procedure just to handle them but it seems to be a very ugly routine, to add all these (53,000) records by just hand coding. Even when I do so, the NULL values seem to cause a lot of trouble. The insertion does not work at all. Please help.
SHARE
Best Answer by technical007
Go To Solution
Answered By 0 points N/A #91963

Help Normalizing a Big Table to 3 Sub Tables

qa-featured

 

It looks like you are using look up tables in some places. If the mother table has NULL values in such fields, you have to handle them first. Try doing something like this:
 
Add a field to each of the lookup tables "UNKNOWN" and key value 0 (i am guessing you haven't used 0 any were else). Run a update statement to mother table altering all the null fields to 0. This way you can avoid NULL values. You have not mentioned in you post how you populated the lookup tables.
 
If you populated it manually then you have to make sure all the values are there. But it is more convenient to do some procedure like this to populate the lookups. I am writing this assuming that you have a lookup table for area named l_area with columns(a_id,area).
 
CREATE VIEW VU_AREA AS SELECT DISTINCT(area_code) FROM main_TBL ORDER BY area_code;
 
CREATE SEQUENCE sq_buf
START WITH 1
INCREMENT BY 1
CACHE 20 ;
 
INSERT INTO l_area (a_id,area) SELECT sq_buf.NEXTVAL,area_code FROM VU_AREA;
 
DROP SEQUENCE SQ_BUF;
 
This shall auto populate the lookup table.
About technical007
Questions
0
Answers
14
Best Answers
4
Answered By 280 points N/A #91965

Help Normalizing a Big Table to 3 Sub Tables

qa-featured
Thanks very, very much for the informative and elaborate answer. I finally managed to organize the work. Now I have my lookup tables ready and main table fixed with NULL values replaced by 0.
 
The three tables are created with appropriate size and data type. Now I need to take one row at a time from the main table ( "main_tbl" i call it.) And add the appropriate rows to each of them. All three of them have the ID column as common. I am doing something like this:
 
Insert into personnel(
 
ID,
MEM_NO,
NAME,
TEL,
GENDER,
ADDRESS,
AREA,
DOB,
EMAIL,
RELIGION,
FAX
)
Values(
Select
MEM_ID,
NAME,
TEL,
Getgender(GENDER),
ADDR,
GetArea(AREA_CODE),
EMAIL,
GetReligion(RELIGION)
From main_tbl
)
 
Its not working. The functions getGender getArea are just used to fetch the key of the Specific Gender/Area in lookup table.
About Hansey
Questions
1
Answers
3
Best Answers
0
Answered By 0 points N/A #91966

Help Normalizing a Big Table to 3 Sub Tables

qa-featured
You do not need the word VALUES, when inserting from another table. It would be:
 
Insert into personnel(
ID,
MEM_NO,
NAME,
TEL,
GENDER,
ADDRESS,
AREA,
DOB,
EMAIL,
RELIGION,
FAX
)
Select(
MEM_ID,
NAME,
TEL,
Getgender(GENDER),
ADDR,
GetArea(AREA_CODE),
EMAIL,
GetReligion(RELIGION)
From main_tbl
)
 
And yes, there is a multi-table insert function named INSERT ALL, which I think would be handy. Check out:
 
About technical007
Questions
0
Answers
14
Best Answers
4
Answered By 280 points N/A #91967

Help Normalizing a Big Table to 3 Sub Tables

qa-featured

 

Thanks a lot. I am truly amazed to have the job done in such less time! I have the whole week in front for free. Anyway just out of  curiosity I also tried multi table insert.
 
INSERT ALL INTO PERSONNEL
(
……)
VALUES
(
………)
PROFESSIONAL_DETAIL
(
……)
VALUES
(
……)
ACCOUNT_INFO
(
…
 
)
VALUES
(
…)
SELECT
 ……
FROM main_tbl
 
It's not working, it's giving an error, "ORA-00936: missing expression".
About Hansey
Questions
1
Answers
3
Best Answers
0
Best Answer
Best Answer
Answered By 0 points N/A #91968

Help Normalizing a Big Table to 3 Sub Tables

qa-featured

 

You have to add the keyword, INTO to all of the insert commands:
 
INSERT ALL INTO PERSONNEL
(—–
—–)
VALUES
(—-
—)
INTO PROFESSIONAL_DETAIL
(—-
—-)
VALUES
 
—–
 
——
About technical007
Questions
0
Answers
14
Best Answers
4
Answered By 280 points N/A #91969

Help Normalizing a Big Table to 3 Sub Tables

qa-featured

Thanks a lot. That would be very handy in future.

About Hansey
Questions
1
Answers
3
Best Answers
0
Answered By 590495 points N/A #91970

Help Normalizing a Big Table to 3 Sub Tables

qa-featured

You do not need the word VALUES, when inserting from another table. It would be:

Insert into personnel(

ID,

MEM_NO,

NAME,

TEL,

GENDER,

ADDRESS,

AREA,

DOB,

EMAIL,

RELIGION,

FAX

)

Select(

MEM_ID,

NAME,

TEL,

Getgender(GENDER),

ADDR,

GetArea(AREA_CODE),

EMAIL,

GetReligion(RELIGION)

From main_tbl

)

And yes, there is a multi-table insert function called INSERT ALL, which I think would be handy. You can check its proper syntax together with other Oracle Insert statements by visiting PSOUG.org | Oracle Insert Statements.

About Sharath Reddy
Questions
1
Answers
14599
Best Answers
2290

How to increase default table space size of a custom Oracle?

Previous

Interfacing a robot arm with C++ in linux.

Next

Related Questions