Home Forums SQL Server 2008 Working with Oracle Need help/ push in right direction with assignment 3NF, Composite Key to SQL+ RE: Need help/ push in right direction with assignment 3NF, Composite Key to SQL+

  • November 13, 2017 at 3:16 pm

    #1967879

    Looks like you have a many to many relationship. It's true that an order will contain many items of equipment, and a piece of equipment can be within many orders, but for 3NF that presents a problem. Instead what you have to do is build a composite key table. I haven't created the Keys etc for you, but as VERY simple exampe you'd need 3 tables along the lines of:

    CREATE TABLE Equipment
      (EquipmentID int IDENTITY(1,1),
      EquipmentName varchar(50),
      Price decimal(8,2));
    CREATE TABLE ClientOrder
      (OrderID int IDENTITY(1,1),
      ClientID int,
      OrderDate date);
    --Now the composite key table
    CREATE TABLE EquipmentOrder
      (EquipmentOrderID int IDENTITY(1,1),
      EquipmentID int,
      OrderID int
      /*--You could put a quantity in here if you wanted
      --or, otherwise, you insert entries for each item,
      --which might be repeated items*/);
    GO

    This means you end up with a one to many relationship between ClientOrder and EquipmentOrder (A Client Order contains many Equipment Orders) and a one to many for Equipment and EquipmentOrders (A piece of Equipment is in many Equipment Orders).

    Hopefully that gets you on the right path.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk