AdventureWorks DB question

Hi, I have been looking at the Adventure works DB diagram and I am confused about the relationship that employees, customers and individuals has with contact i.e. a 1-1, 1-many or many-many relationship. Also why is it broken down like that.

Thanks,

Nadim.

Hi Nadim,

AdventureWorksDB is designed to show a complex data schema in a nomalized structure. AdventureWorks is probably a bit overcomplex if you're just learning about normalization, but once you understand the concepts, it becomes more clear.

Note: If you are new to normalization, you should search for a few topics about it to get the basics. You might also find the Northwind or Pubs sample databases to be a bit more understandable. You can download either from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. They are SQL 2000 samples, but they will still work in 2005.

On to the description...

If you look at the fields in each of the tables, you'll see that the Contact table contains the base information about a person, e.g. first name, last name, phone, etc. The Contact table is linked to both the Employee and Individual tables. Both Employees and Individuals are types of people that have special properties in addition to the base person information stored in the Contact table. So an Employee has a ManagerID and LoginID, but an Individual doesn't. Because of the relationship between Contact and Employee, there is no need to store the first name, last name, etc. in the Employee table, it is retrieved through a join.

If I wanted to get a list of Employee names, with Titles and Hire Dates, I'd need to write a query that uses both the Contact and Employee table, as follows:

SELECT C.FirstName, C.LastName, E.Title, E.HireDate
FROM Person.Contact C INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID

The relationship with Individual is similar, only it extends through Individual onto Customer. Each Customer has an Individual associated with it, and that Individual, is a person who has Contact information. I'll be honest, I don't really know why they split Customer and Individual, it could have something to do with the way they wanted to use the data being stored.

Hopefully that brings a little light to the subject.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families.
"Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNum1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's names
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped in
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Advanced find and replace

I have two tables.
One table contains ID numbers for individuals (Ind_ID), and ID numbers for
their family unit (Fam_ID), there isn't any words in this table just numbers
.
The information in the Fam_ID field would be like this: ID001, ID002, ID003,
ID004. This represents four people in this field. We'll call this table IDNu
m1
The other table has the a field that has each ID number that matches the
other table (IDNum1.Ind_ID) and another field that has the individual's name
s
(Names). We'll call this table IDNam1
I need to perform an update query that will:
Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
Go to IDNam1.Names, look at the ID number of the individuals
Write to a new table the names from IDNam1.Names according to the number of
IDs from IDNum1.Fam_ID.
The end result is a find and replace using IDNum1.Fam_ID as the structure
for the a new table that now contains only names from IDNam1.Names grouped i
n
families."Steve1445" <Steve1445@.discussions.microsoft.com> wrote in message
news:399D9D4B-083A-4934-A6F4-A60DE3F1C111@.microsoft.com...
>I have two tables.
> One table contains ID numbers for individuals (Ind_ID), and ID numbers for
> their family unit (Fam_ID), there isn't any words in this table just
> numbers.
> The information in the Fam_ID field would be like this: ID001, ID002,
> ID003,
> ID004. This represents four people in this field. We'll call this table
> IDNum1
> The other table has the a field that has each ID number that matches the
> other table (IDNum1.Ind_ID) and another field that has the individual's
> names
> (Names). We'll call this table IDNam1
> I need to perform an update query that will:
> Look at the table IDNum1 to see how many ID numbers there are in Fam_ID,
> Go to IDNam1.Names, look at the ID number of the individuals
> Write to a new table the names from IDNam1.Names according to the number
> of
> IDs from IDNum1.Fam_ID.
> The end result is a find and replace using IDNum1.Fam_ID as the structure
> for the a new table that now contains only names from IDNam1.Names grouped
> in
> families.
I'm not quite following all of this... Can you please provide DDL, some
sample data and the results you are looking for. See this for more
information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA