Home » SQL & PL/SQL » SQL & PL/SQL » creation of records with respect to the delimited data from a column of base table
creation of records with respect to the delimited data from a column of base table [message #633695] Tue, 24 February 2015 05:28 Go to next message
saralay
Messages: 1
Registered: February 2015
Location: Pune
Junior Member
Hi,

I want to create a Child table with respect to the data from the base table.
For eg, I have a base table called employee in which the data is as shown below,

BAse table Employee:

EmpId EmpGroup EmpDept
1 A;B;C Oracle
2 X;Y;Z DBA


Child Table Employee_1;

EmpName EmpDept
A Oracle
B Oracle
C Oracle
X DBA
Y DBA
Z DBA

I have to create a child table, in which the number of records are taken with respect to data(delimited) from a column(EmpGroup) of basetable(Employee).

Please help me with the query.

Thanks in advance!!!!


Re: creation of records with respect to the delimited data from a column of base table [message #633696 is a reply to message #633695] Tue, 24 February 2015 05:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
This looks like a college homework question. THe forum guidelines are that no-one will do your homework for you, but when you get stuck people will help.

It sounds as though what you want to do is normalize your one table into two tables, with rows that are connected by a primary key and foreign key relationship. If you provide the CREATE TABLE statements, including the constraint definitions, then this will become clear.
Be sure to enclose the code for your statements within [code] tags.
Re: creation of records with respect to the delimited data from a column of base table [message #633700 is a reply to message #633695] Tue, 24 February 2015 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also this post and the following ones will give some hints.

Re: creation of records with respect to the delimited data from a column of base table [message #633785 is a reply to message #633700] Tue, 24 February 2015 15:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am curious as to what the need is that requires this kind of design, and who told you that you HAVE to do it?

Can you describe the business problem, or the technical problem you are faced with that makes you think you need this type of process? I am just curious is all.
Re: creation of records with respect to the delimited data from a column of base table [message #633804 is a reply to message #633785] Wed, 25 February 2015 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tend to think like John, this is a homework, so no business problem just teacher's one. Smile

Re: creation of records with respect to the delimited data from a column of base table [message #633823 is a reply to message #633804] Wed, 25 February 2015 07:31 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are really only two kinds of Relational Database Designs:

1. Designs that follow Normalization Theory.

2. Designs that due to the nature of the data, do not from a practical perspective allow us to follow Normalization Theory.  One (though not the only) response to this problem is the "data as columns" solution that some people use.


Said another way:

1. do we store data as "modeled"
2. or "un-modeled".

In the end this depends upon how you want to use it.

In a database design where data has been previously modeled, everthing must be "addressable". That means, every piece of information must be locatable. Thus every piece of information has a unique name, and ties back to an unambiguous KEY. Your first step in design is to identify these names and keys.

In a traditional database design, every key maps back to one entity. This one entity can be implemented as multiple entities if necessary; one obvious example of this being SUPER-TYPE / SUB-TYPE designs, but it is still one entity because there is only one key and all sub-types map back to that one key but just as a specialized version of the master entity type. It is not THIS simple due to the possibility of overlapping sub-types (?) (did they call this multiple-inherentance?), but we try to avoid the more complicated design theories unless they really provide some significant value to the end product.

You said there was only ONE key for all the data you were getting.

If this is REALLY true, then there is only one entity in your design (which sounds totally bogus but who knows) and all data you have is a part of that entity. This brings us to the nullable and/or super-type/sub-type question we asked before to which you said "no nulls" (no nulls is not really the end of super-type/sub-type design of course but we ignore this for now).

However, the ONLY ONE KEY remark, suggests that you are simply not analyzing the data sufficiently. What is this ONE key? Is it artificial (a surrogate)? If so then let me point out another fact.

Keys can be SYSTEM IDENTIFIERS or BUSINESS IDENTIFIERS. When you design a data model, you design using BUSINESS IDENTIFIERS. SYSTEM KEYS are almost exclusively sequence numbers and are not real keys to data. SYSTEM KEYS are technically added later as part of an access or physical implementation strategy and each database may do them differently.

So let me ask you this:

What are the BUSINESS KEYS to your data?
Is there more than one?  If not what is the one?
Have you taken the time to actually model your incoming data?  Sounds like maybe not yet.

You remakred something like "we do not know all the data till after we have analyzed it". Well that is design. Your comment suggests that you have not modeled anything. So that leads to another question taking us back to the begining.

Do you want to store your data MODELED or UN-MODELED?

Un-modeled data cannot have SQL used against it. Since it is un-modeled, it is basically just a blob unknown stuff until it is used. This leaves applications using this data to have to analyze it every time they access it. There are some systems that do this but they are generally very specialized in nature and they may not work very fast.

Which leads us to one more question group.

How will you use this data if it is not modeled?
What processes are you expecting to support?
Who are the end users that will use what you create?
What are the problems ehse end users are trying to solve?
What kind of application are you expecting to create to help them solve these problems?


These questions help you understand what you are working withing with and thus when data can be modeled or un-modeled.

Kevin
Previous Topic: Oracle dbms scheduler
Next Topic: How to handle unknown number of variables
Goto Forum:
  


Current Time: Fri Mar 29 10:10:34 CDT 2024