+ Reply to Thread
Results 1 to 2 of 2

Understanding Many-to-Many relationships and Sales database

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Understanding Many-to-Many relationships and Sales database

    Hello everyone. It has probably been over a year since I have posted something. I am somewhat new to Access and have mostly work in Excel. I have been given the following test question and I am somewhat stumped. Any help would be appreciated. Most important here is setting up the Many-Many relationship. I am sure the query is strightforward.

    Deliverable
    Write an SQL statement that lists the department(s) that have at least one sale of every type of item delivered to them. The department need not have sold out of the item or exhausted their inventory to qualify.

    Assumptions
    The tables below follow a relational schema using foreign keys in a many-to-many relationship. Assume that the data types and relationships are properly enforced by the DBMS. Please form your answer in ANSI-compliant (SQL92) structures. Please describe any other assumptions required.

    Metadata
    The table Item contains the inventory (SKU’s) for sale
    The table Department contains the list of departments (stores) selling items
    The table Delivery contains the list of deliveries of items to departments
    The table Sale contains the list of sales of items by departments

    Example of Tables

    Table: Item

    ItemID ItemName
    1 Item1
    2 Item2
    3 Item3
    4 Item4
    5 Item5

    Table: Department

    DeptID DeptName
    1 Dept1
    2 Dept2
    3 Dept3
    4 Dept4
    5 Dept5

    Table: Sale

    SaleID ItemID DeptID Qty
    1 1 1 75
    2 2 1 67
    3 3 1 61
    4 4 1 82
    5 5 1 73
    6 1 3 100
    7 2 3 70
    8 3 4 67
    9 4 5 78
    10 5 5 99

    Table: Delivery

    DeliveryID ItemID DeptID Qty
    1 1 1 120
    2 2 1 183
    3 3 1 152
    4 4 1 185
    5 5 1 173
    6 1 2 173
    7 2 2 124
    8 3 2 150
    9 4 2 135
    10 5 2 114
    11 1 3 120
    12 2 3 175
    13 3 3 118
    14 2 4 125
    15 3 4 102
    16 1 5 149
    17 4 5 146
    18 5 5 127
    Last edited by AnthonyWB; 05-09-2012 at 03:52 PM.

  2. #2
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    458

    Re: Understanding Many-to-Many relationships and Sales database

    I figured it out it was easy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1