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+

  • twgrops - Monday, November 13, 2017 4:06 PM

    Thom A - Monday, November 13, 2017 3:16 PM

    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.

    Thankyou I will just look into this more I am in UK and is late so will try report back tommorow

    ah I had a little look and I understand i need a composite key however I am not allowed to mix it up like that I have to follow the guides they give us and it has got to be 4 tables which is what I have got however I think I went wrong somewhere in normalisation