locked
Adventure Works STORE sample table question RRS feed

  • Question

  • Dear all,

    I am actually studing the database structure of the AW sample database, which is quite close to one of my current project architecture.
    I have seen in this sample database that the CUSTOMER table seems to be used to identify either a STORE of a physical Person through the PersonID column.

    My question is that if we define a STORE information from that sample databse where can we find the address, tel, ... of the STORE ?

    Does those information for a store is actually not in that sample or do we consider that there is at least a contact person for a defined STORE and the address and telephone of that physical primary contact person identify also the STORE contact info ?

    Thanks for clarification

    regards

    Tuesday, February 18, 2014 4:22 PM

Answers

  • AdventureWorks Cycles selling bikes and related accessories both online to individual buyers and through resellers (dealers, stores).

    BOL: "As a bicycle manufacturing company, Adventure Works Cycles has two types of customers:

    • Individuals. These are consumers who buy products from the Adventure Works Cycles online store.

    • Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives."

    Here are the related tables:

    SELECT * FROM [AdventureWorks2012].[Sales].[Customer];
      /*
      ....
      CustomerID	PersonID	StoreID	TerritoryID	AccountNumber	rowguid	ModifiedDate
    700	NULL	1030	5	AW00000700	540FB57F-F81A-4794-8E9A-C071C4F0BC18	2008-10-13 11:15:07.263
    701	NULL	844	6	AW00000701	61AE9625-8BD8-48B7-B171-8A90CBBA494C	2008-10-13 11:15:07.263
    11000	13531	NULL	9	AW00011000	477586B3-2977-4E54-B1A8-569AB2C7C4D4	2008-10-13 11:15:07.263
    11001	5454	NULL	9	AW00011001	C32A8084-9077-4F13-9738-1E2DA7C1DCD9	2008-10-13 11:15:07.263
    11002	11269	NULL	9	AW00011002	45715DD8-2F57-4A39-BEB4-6A8F99D59794	2008-10-13 11:15:07.263
    ....
    */
    
    SELECT * FROM [AdventureWorks2012].[Sales].[Store];
    
    /*
    .....
    BusinessEntityID	Name	SalesPersonID  .......
    292	Next-Door Bike Store	279
    294	Professional Sales and Service	276
    296	Riders Company	277
    298	The Bike Mechanics	275
    300	Nationwide Supply	286
    .....
    */

    Here is the data model description:

    http://technet.microsoft.com/en-us/library/ms124824(v=sql.100).aspx


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    • Edited by Kalman Toth Tuesday, February 18, 2014 5:18 PM
    • Proposed as answer by Naomi NEditor Wednesday, February 19, 2014 3:09 PM
    • Marked as answer by tracycai Thursday, February 20, 2014 2:47 AM
    Tuesday, February 18, 2014 5:17 PM
  • Please see the link Kalman provided. http://technet.microsoft.com/en-us/library/ms124824(v=sql.100).aspx

    Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives.

    A Store links to a StoreContact.


    See example "F. Viewing stores by locations"
    • Edited by Tom PhillipsEditor Tuesday, February 18, 2014 6:35 PM
    • Marked as answer by wakefun Wednesday, February 19, 2014 7:36 AM
    Tuesday, February 18, 2014 6:32 PM
    Answerer

All replies

  • I am not sure what you mean by STORE?  Do you mean where it was purchased or where it was shipped??

    In any case Sales.Store contains the store information.  Customers are linked to a Store.

    Tuesday, February 18, 2014 4:51 PM
    Answerer
  • AdventureWorks Cycles selling bikes and related accessories both online to individual buyers and through resellers (dealers, stores).

    BOL: "As a bicycle manufacturing company, Adventure Works Cycles has two types of customers:

    • Individuals. These are consumers who buy products from the Adventure Works Cycles online store.

    • Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives."

    Here are the related tables:

    SELECT * FROM [AdventureWorks2012].[Sales].[Customer];
      /*
      ....
      CustomerID	PersonID	StoreID	TerritoryID	AccountNumber	rowguid	ModifiedDate
    700	NULL	1030	5	AW00000700	540FB57F-F81A-4794-8E9A-C071C4F0BC18	2008-10-13 11:15:07.263
    701	NULL	844	6	AW00000701	61AE9625-8BD8-48B7-B171-8A90CBBA494C	2008-10-13 11:15:07.263
    11000	13531	NULL	9	AW00011000	477586B3-2977-4E54-B1A8-569AB2C7C4D4	2008-10-13 11:15:07.263
    11001	5454	NULL	9	AW00011001	C32A8084-9077-4F13-9738-1E2DA7C1DCD9	2008-10-13 11:15:07.263
    11002	11269	NULL	9	AW00011002	45715DD8-2F57-4A39-BEB4-6A8F99D59794	2008-10-13 11:15:07.263
    ....
    */
    
    SELECT * FROM [AdventureWorks2012].[Sales].[Store];
    
    /*
    .....
    BusinessEntityID	Name	SalesPersonID  .......
    292	Next-Door Bike Store	279
    294	Professional Sales and Service	276
    296	Riders Company	277
    298	The Bike Mechanics	275
    300	Nationwide Supply	286
    .....
    */

    Here is the data model description:

    http://technet.microsoft.com/en-us/library/ms124824(v=sql.100).aspx


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    • Edited by Kalman Toth Tuesday, February 18, 2014 5:18 PM
    • Proposed as answer by Naomi NEditor Wednesday, February 19, 2014 3:09 PM
    • Marked as answer by tracycai Thursday, February 20, 2014 2:47 AM
    Tuesday, February 18, 2014 5:17 PM
  • In fact I imagine myself has the owner of AW and would like to know for instance the STORES which resell my product. For that I am expecting somehow to find the STORES information like address, telephone, etc... no ?

    Actually in the STORE table I can see only the name of the store, does its other information are store somewhere else or it is just limited to the Name ?

    regards


    • Edited by wakefun Tuesday, February 18, 2014 5:22 PM
    Tuesday, February 18, 2014 5:21 PM
  • Please see the link Kalman provided. http://technet.microsoft.com/en-us/library/ms124824(v=sql.100).aspx

    Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives.

    A Store links to a StoreContact.


    See example "F. Viewing stores by locations"
    • Edited by Tom PhillipsEditor Tuesday, February 18, 2014 6:35 PM
    • Marked as answer by wakefun Wednesday, February 19, 2014 7:36 AM
    Tuesday, February 18, 2014 6:32 PM
    Answerer