Re: Convert 1NF to 3NF

nyluke_at_gmail.com wrote:
> I will be converting a first normal form Oracle schema to third normal
> form. I am looking for a way to do this without requiring the
> application to be aware of the change. I have tried to figure out a
> view for this but I don't know if it's possible. Any suggestions?
> Thanks..
>

When converting from 1NF to 3NF, you will be decomposing the table into more tables. Assume that you have a table that is 1NF called 1NF_TABLE. You end up decomposing this table into 2 tables called 3NF_TABLE1 and 3NF_TABLE2. Those tables can be joined together by some key. Either that, or you'll need a third table which is a "crosswalk" table that captures the relationship between the other two. In either case, you can drop the table 1NF_TABLE and create a view called 1NF_TABLE that does the join:

CREATE OR REPLACE VIEW 1nf_table AS
SELECT t1.col1, ... ,t2.col1, ... , t2.coln FROM 3nf_table1 t1, 3nf_table2 t2
WHERE t1.key_col1 = t2.key_col1

   AND t1.key_col2 = t2.key_col2;

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown