Normalization Question Regarding Column Combinations

Jul 23, 2005

We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:

Victoria store:

TorrensUnit
TorrensVolume
TorrensFolio

Queensland store:

TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef


There are 11 different columns and they are used in 8 different
combinations depending on the state.

Since we need to store information about land in different states I see
two possible solutions:

1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.

2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.


I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.

View 4 Replies


ADVERTISEMENT

Combinations

Jan 14, 2008

Hi,

I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane

I need to generate all 2 pair combinations of the names(i,e)

(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)

Can some one give me a generalized query for this problem?

Thanks,

Prakash.P

View 4 Replies View Related

SetComponentProperty - Possible Key / Value Combinations

Oct 12, 2007

I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.

I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?




Code Block
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");



Thanks

View 5 Replies View Related

Selecting All Unique Combinations

Jul 31, 2004

I want to start with a table that has 4 records:

-Self
-Supervisor
-Peer
-Direct Rep

And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)

-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer

How would I do this in an SQL Query? Thanks for your help!

View 1 Replies View Related

How To Get Top 2 Rows For All Composite Key Combinations?

Jul 9, 2006

The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.

Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.

select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))


The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000


The desired output should look like:

cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000


All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000





Thanks in advance.

View 3 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

View 9 Replies View Related

Produce All Combinations For A Situation

Sep 6, 2013

How would I write a query to produce all combinations for a situation such as the following?

Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.

Adjective table looks like e.g.

ID Adj
1 good
2 so so
3 bad

Noun table looks like e.g.

ID Noun
1 apple
2 orange
3 banana

And the result set would look like

This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.

I would take a stab at this myself and post even something that doesn't work...

View 1 Replies View Related

Count Of Unique Combinations

Oct 26, 2007

Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do

select distict One, Two from MyTable

How do I return the count of such unique combinations? The following of course does not work but you get the idea.

select count(distinct One, Two) from MyTable

Thanks.

View 6 Replies View Related

Finding All Unique Combinations Of Values?

Mar 29, 2013

how to find all possible combinations of values, for example:

My table includes:

Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);

I need the output to be like this:

apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry

I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.

View 5 Replies View Related

Need A Routine For Making All Combinations With Given Characters

Jan 11, 2008

I need a function or a routine in sql for making all possible combinations of strings with given letters.

means if I give a string 'ab' the function should return

'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.

return data can be a single list or the list elements can be printed to screen one by one.

I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.

Please help.

View 4 Replies View Related

Selecting Alphanumeric Combinations That Do Not Exist Already Intable?????

Mar 24, 2008

Hello all, I have an odd requirement. I have a column with a systemgenerated username that is a 6 character, alphanumeric, field. Theseusernames are randomly generated by code. I need to create a storedprocedure that will return all combinations that are not alreadybeingused. Maybe the result of still trying to wake-up from a longweekend.But, I cannot think of an easy way to do this.Any help would be greatly appreciated.Best regards,rbr

View 4 Replies View Related

Transact SQL :: Query To Get List Of Permutation And Combinations

Aug 27, 2015

Below query:

Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)

Insert into @table
Select 1,123
union all

[Code] ...

--Expected output concatenation of consumer numbers all permutation and combinations having same

customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

View 4 Replies View Related

Algorithm To Populate A Table With Finite Value Combinations

Jul 2, 2007

I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.

For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.

What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.

If I need to submit this in another forum please let me know.

View 3 Replies View Related

Advanced Select Based On Multiple Field Combinations

May 31, 2006

I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.

I was curious what is the best way to perform this in a Stored Procedure?

Can I use a UNION -- Not sure if this is the best choice

or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?

View 3 Replies View Related

Normalization

Aug 17, 2004

Hi all,

I am still confuse in normalization. (1st, 2nd, 3rd, and more.)

Any body have a detail idea abt that ???
Pls explain with example.

Thxs.

Regards,
Shailesh Patel

View 2 Replies View Related

Normalization

Nov 6, 2000

Is it possible to normalize a database using SQL statement? I have a huge duplicated records on a certain fields and need to do some normalization on it. For example, the raw data below

Field_A Field_B Field_C Field_D Field_E
1 0001 00/05/11 14.00 Start
1 0001 00/05/11 15.00
1 0001 00/05/11 16.00 End
1 0002 00/05/11 16.10 Start
1 0002 00/05/11 17.50
1 0002 00/05/11 17.10 End

should be normalized to

Field_A Field_B Field_C Duration(End-Start)
1 0001 00/05/11 16.00-14.00=2.00
1 0002 00/05/11 17.10-16.10=2.00


How can I do it using a SQL Query?



Andy

View 3 Replies View Related

Help With Normalization

Jan 15, 2008

Hi all,
Does anyone know of a good resource or tutorial that teaches you the basics of the 3 normal forms?

Muchos Gracias

View 2 Replies View Related

Please Look @ My Normalization

Mar 6, 2008

I'm working on a normalization for one of my classes and I've been sick and I feel lost now, could one of you please look at my database statements and tell me if/what is wrong with it?

FIRE_REPORT(FireID,Alarms,Address,FiremanID,FiremanName,FiremanPhone,FiremanHomeStation,StationAddress,StationPhone,TruckID,License,TruckHomeStation)

I came up with..

TruckID->License,TruckHome
FireID->Alarms,Address
FiremanID->FiremanName,FiremanPhone,FiremanHomeStaion,StationAddress,StationPhone

Which lead to...

FIRE(FireID,Alarms,Address)
FIREMAN(FiremanID,FiremanName,FiremanPhone,FiremanHomeStation,StationAddress,StationPhone)
TRUCK(TruckID,License,TruckHome)

Referencial Integriety Statements

FireID in FIRE_Report must exist in FireID in FIRE

FiremanID in FIRE_Report must exist in Fireman

TruckID in FIRE_REPORT must exist in TruckID in TRUCK


Please, someone tell me if this is right or not. -cheers

View 3 Replies View Related

Tables Normalization..:(

Apr 14, 2005

Hi everyone..
Well i have my tables ready to build the database on to the sql server... My probs is normalization of the tables being used in the database....
Is there any best possible way / [short-cut.. very weird to ask this :) ] using the sql server...?
 
 

View 6 Replies View Related

Advice Normalization

Jun 13, 2005

I have a web app which is used to do normal insert/update of employee info. Connected to each employee that is entered is some data that is imported from an outside source for each employee. The question I have is currently my database is very normalized and importing data from this outside source will be quite a pain because of this. Is it bad practice to denormalize a specific table if no user will every insert/update it beside DTS?

View 11 Replies View Related

Tools For Normalization...

Sep 22, 2001

What's my question is ? Whether there are any tools available for normalization produced by Database vendors or any third party. If yes, Can u kindly give me clear documentation.

Thanks,
venkat.
venkat_26178@yahoo.com

View 2 Replies View Related

Whether There Are Any Tools For Normalization..

Sep 22, 2001

whether there are any tools for Normalization? If yes, Can u please send some documentation or some reference where I can get them.

-venkat
venkat_26178@yahoo.com

View 1 Replies View Related

Am I Takin This Normalization Too Far?

Feb 25, 2004

Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities.



tbEstimatedProjects
Reference (PK) | Name | City | Postal |...
-----------------------------------------------------------
1 | Some Project | Niagra Falls | N8E7J5 | ....

tbAwardedProjects
Project (PK) | Reference
-------------------------
1001 | 1

tbProjectTeam
Project (PK)| Login (PK) | Activity (PK)
-----------------------------------------
1001 | jsmith | Detailer

tbEmployees
Login (PK) | First | Last |......
----------------------------------
jsmith | Jim | Smith |.....

tbDepartmentListing
Login | DeptCode
---------------------
jsmith | ENG

tbDepartments
Code | Department
------------------------
ENG | Engineering

tblDepartmentActivities
Code (PK) | Activity (PK)
----------------------
ENG | Engineering
ENG | Detailer




Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?

Mike B

View 6 Replies View Related

Database Normalization

Jan 31, 2006

Hi,I am using MS-SQL server to store my database.My problem is that I have around 150+ database files in DBF format.Each database file consists of fields ranging from 2 to 33 in number.Also, there are some fields which have just one entry and rest areNULL.This database will be accessed by a printing software.Please advice as to how I should proceed to normalize this database.Regards,Shwetabh

View 2 Replies View Related

Normalization Connundrum

Jul 20, 2005

I've come up with this issue in several apps now. There are things that, fromone perspective, are all handled the same, so it would be desirable that theyall be handled in the same table with some field as a type specification.From other perspective of foreign key relationships, however, they aredifferent things and can't be stored in the same table.For example, I have a scheme for indicating mappings between dimension recordsat one time period to new dimension records at another time period. I coulduse one set of tables for all mappings since they all work exactly the sameway, but then I can't set up DRI between the mapping tables and the dimensiontables. If I just make separate mapping tables for each dimension table, thenI'm creating 4 new tables per dimension table, all identical with respect towhat fields they contain, what kinds of unique constraints they have, and whatrelationships they have to each other with the sole distinction that they eachmap to the integer-type key of a different dimension table. I would not lookforward to doing maintenance on this schema!Is there any strategy for having the cake and eating it, too?

View 7 Replies View Related

Normalization Question

Feb 13, 2008

I have 3 code tables :

create table cd_fiq_a
( fiq_id int not null primary key, fiq_name varchar(50) not null)

create table cd_sal_b
( sal_id int not null primary key, sal_name varchar(50) not null)


create table cd_rak_c
( rak_id int not null primary key, rak_name varchar(50) not null)



insert into cd_fiq_a values (1, 'Fiq1')

insert into cd_fiq_a values (2, 'Fiq2')



insert into cd_sal_b values (1, 'Sal1')

insert into cd_sal_b values (2, 'Sal2')

insert into cd_sal_b values (3, 'Sal3')

insert into cd_sal_b values (4, 'Sal4')

insert into cd_sal_b values (5, 'Sal5')



insert into cd_rak_c values (1, 'Rak1')

insert into cd_rak_c values (2, 'Rak2')

insert into cd_rak_c values (3, 'Rak3')

insert into cd_rak_c values (4, 'Rak4')


Now there is a relationship between cd_fiq_a, cd_sal_b and cd_rak_c. For a given Faq there can be one or more records of Sal. For a given Fiq and a given Sal there can be one or more records of Rak.
I am thinking that i can do it one table or two tables:

One Table Solution
----------------------------

create table relation_d
( relation_id int not null primary key,
fiq_id int not null foreign key REFERENCES cd_fiq_a (fiq_id),
sal_id int not null foreign key REFERENCES cd_sal_b (sal_id),
rak_id int not null foreign key REFERENCES cd_rak_c (rak_id),
sort_order int not null )

Two Table Solution
---------------------------

create table relation_header_d
( relation_header_id int not null primary key,
fiq_id int not null foreign key REFERENCES cd_fiq_a (fiq_id),
sal_id int not null foreign key REFERENCES cd_sal_b (sal_id) )


create table relation_detail_e
( relation_detail_id int not null primary key,
relation_header_id int not null foreign key REFERENCES relation_header_d (relation_header_id),
rak_id int not null foreign key REFERENCES cd_rak_c (rak_id),
sort_order int not null )


Which solution is more normalized and will result in better execution of Sql? Or is there any other solution which is more better?

Thanks

View 11 Replies View Related

Amount Of Normalization

Jun 8, 2007

Hello all,

My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.



- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.

- the largest data type is nvarchar 125.

- number of rows in the largest table will eventually grow to hundreds of thousands.

- users access the database online







This is large to me but I expect that to some of you this not.

My application would be easier to setup if the contacts table were to include address info.



So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.



Any help you could provide with this scetchy info would be greatly appreciated.



View 6 Replies View Related

Normalization Questions

Oct 18, 2006

Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.



2)
Employee (ssn, Name, Salary, Address, ListOfSkills)



Yes,
No. Ans: No. as list of skills would be repeated.




3)
Department (Did, Dname, ssn)

Yes,
No. Ans: No. ssn and did should be moved to a seperate table.




4)
Vehicle (LicensePlate, Brand,
Model, PurchasePrice, Year, OwnerSSN, OwnerName



Yes,
No Ans: No.




5)
Employee (ssn, Name, Salary, did) (obs.:
employee can only belong to one department)



Yes,
No. Ans: Yes.


6)
Customer (Cust_Id, Name, Salesperson, Region) where Salesperson
determines Region.



Yes,
No. Ans: No.Salesperson and region should be moved to a seperate table.



7)
Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo
-> ItemName




Yes,
No. Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.


View 3 Replies View Related

Database De-normalization, Is It Worth It?

Feb 14, 2008

Is it worth it for performance purposes to create additional fields in table so that when a database query is made two tables don't need to be joined?

View 1 Replies View Related

A Debate About Data Normalization

May 28, 2008

Hi All
Please guide me in the following situation. I am new in programming
I have a master table tblCompany with fields:  Company Name, Address, Phone number
Second table is tblUsers with     Company Name, User Name , Password
Third table is tblDealing with field Company Name , Dealer Name, Dealer Address
According to the normalization rules I shoud put a column named Company_Id in tblCompany(master table)
and use it in other two tables instead of CompanyName colum to reduce the data retundancy.
But my question is accessing data from master detail tables with join quries will take more processing time(taking the company name against the company ID). On the other hand memory wise its same to store the company ID(like 0012786) and company name (like somecompany Ltd). So should I go for normalization or simply store the Company name in each table.
Thanks

View 4 Replies View Related

Database Table Normalization

Dec 8, 2005

Hi all,
This is actually a pretty stupid question, but somehow I need an answer from you experts.
We are currently building a web application using ASP.NET, and it simply manages contact information, like outlook. Contact information include first name, last name, birthday, etc. It also tracks address, phone number, and email. Here come the problem.
We allow only one address, 4 phone numbers and an email for each contact. When we building the database table, should we create 6 fields to contain all the information or should we create address, phone, and email table and then create the relationship between them.
Will the first method speed up the performance? Or the second method is the proper way?
I need some pros and cons on each
Thanks so much for your opinion
Sam

View 5 Replies View Related

SQL Query Help - - Table Normalization

Jul 20, 2005

helloI've a denormalized table PRODUCTS with following fields:ProductNo ,OrderNo ,SerialNo ,OrderDate ,PromiseDate ,ManufacturerID ,......DistributorID ,DealerID ,......ReceiptDate ,......I have to denormalize this table, so I created 3 tables:Table Name : ProductOrdersFields:+ProducrOrderID,ProductNo ,OrderNo ,SerialNo ,OrderDate ,PromiseDate ,ManufacturerID ,-------------------------------Table Name: ProductsOrdersDetailsFields:+ProductsOrdersDetailsID,ProductOrdersID,DistributorID ,DealerID......-----------------------Table Name: ProductsOrdersReceiptsFields:+ProductsOrdersReceiptsID,ProductsOrdersDetailsID,ReceiptDate ,......----------------------------DistributorID and DealerID appear in Details table because aparticular order number for a specific product number can come fromdifferent distributor and dealer.What I need is a query to populate these normalized tables from theoriginal denormalized Products table.Can any one please help?Thanks

View 1 Replies View Related

Data Normalization - Best Approach?

Feb 23, 2006

Hi!

I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows:
I got plain table with several columns in it.Some of columns can be copied straight into destination tableSome columns (String) should be lookup in another table to get IDOn success just replace string with IDOn fail - create new record in lookup table and return newly created ID
Thanks for any ideas and maybe short samples

Anrijs Vitolins

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved