Need help/ push in right direction with assignment 3NF, Composite Key to SQL+

Question

Need help/ push in right direction with assignment 3NF, Composite Key to SQL+

twgrops

SSC Enthusiast

Points: 185
More actions
November 13, 2017 at 2:27 pm

#341374

I am working on a uni assignment so can't really take exact answers but can someone explain why I am struggling to build the database in SQL+ (Please see attached ER diagram). My issue is my understanding of Composite keys... I have a table named 'equipment' which has a composite key consisting of (order_id and equipment) I have a table called 'orders' which I believe needs a connection to the table as a foreign key but I am getting errors about being not unique.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

OK, here's a couple of questions for you to think about:
1. Can you have an Equipment row without an Order?
2. How many Order rows can an Equipment row relate to?

Thom A SSC Guru Points: 99130 More actions · Answer 2

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

twgrops SSC Enthusiast Points: 185 More actions · Answer 3

Chris Harshman - Monday, November 13, 2017 3:12 PM
OK, here's a couple of questions for you to think about:
1. Can you have an Equipment row without an Order?
2. How many Order rows can an Equipment row relate to?

Thank you for the reply
1. I just realised equipment table has the quantity so it can't exist without an order, however equipment itself exists in price table without an order.
2. an Order can have 1 or more equipment and I am a bit confused to the link back will try figure it out and report back.

twgrops SSC Enthusiast Points: 185 More actions · Answer 4

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

twgrops SSC Enthusiast Points: 185 More actions · Answer 5

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

twgrops - Monday, November 13, 2017 4:12 PM
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

I think in Thom's example he didn't write out the Client table because it's fine as is. I also think he has better table names, and that helps to think about the relationships between items better. The table you call Price is what he calls Equipment, and the table you call Equipment is what he calls Equipment Order.

It's probably easiest to think of it in terms of 3 entities, Client, Order, Equipment. I assume the following relationships exist:
- A client can have 0 or more orders, and each order only belongs to 1 client
- An order can have 1 or more equipments on it, and each equipment can be on 0 or more orders
- Since order and equipment have a many-to-many relationship, there needs to be an intersection table between them

Sergiy SSC Guru Points: 110208 More actions · Answer 7

There is Equipment is stock, and Equipment in Order.

Equipment in Stock may have 1or more Prices, defined for different sale conditions.
Equipment in Order must have a single price only. It may be one of the prices defined for Equipment in Stock, or a different one, if some kind of executive desicion was applied to the sale.

Instead of "Equipment " you must have an "Order Item" table which will contain OrderID, EquipmentID, Qty, Price (not per unit of equipment, but per Order Item, to avoid rounding errors).
EquipmentID in OrderItem must reference table Equipment, which has 1 or more entries in EquimpmentPrice.

_____________
Code for TallyGenerator

twgrops SSC Enthusiast Points: 185 More actions · Answer 8

Chris Harshman - Monday, November 13, 2017 4:41 PM
twgrops - Monday, November 13, 2017 4:12 PM
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
I think in Thom's example he didn't write out the Client table because it's fine as is. I also think he has better table names, and that helps to think about the relationships between items better. The table you call Price is what he calls Equipment, and the table you call Equipment is what he calls Equipment Order.
It's probably easiest to think of it in terms of 3 entities, Client, Order, Equipment. I assume the following relationships exist:
- A client can have 0 or more orders, and each order only belongs to 1 client
- An order can have 1 or more equipments on it, and each equipment can be on 0 or more orders
- Since order and equipment have a many-to-many relationship, there needs to be an intersection table between them

Right I had a look and realised my relationships were wrong between the original order and equipment entities. I have taken on board the advice and made a new ERD for an example showing what I believe is the correct relationship.
Because the 'order table' can have multiple 'equipment orders' I have put 1 to many, as for the other way around the 'equipment order' can only be in 1 and only 1 order (I think).
The equipment and price attributes itself reside inside the 'equipment' entity... Unfortunately this is a very crude assignment as he has worded it really bad in the original question to catch us out. They have already had to make changes to names multiple times because of the influx of confusion with the other students.

Thom A SSC Guru Points: 99130 More actions · Answer 9

Chris Harshman - Monday, November 13, 2017 4:41 PM
twgrops - Monday, November 13, 2017 4:12 PM
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
I think in Thom's example he didn't write out the Client table because it's fine as is. I also think he has better table names, and that helps to think about the relationships between items better. The table you call Price is what he calls Equipment, and the table you call Equipment is what he calls Equipment Order.
It's probably easiest to think of it in terms of 3 entities, Client, Order, Equipment. I assume the following relationships exist:
- A client can have 0 or more orders, and each order only belongs to 1 client
- An order can have 1 or more equipments on it, and each equipment can be on 0 or more orders
- Since order and equipment have a many-to-many relationship, there needs to be an intersection table between them

That's exactly what I'm going for, yes. Thanks for the assist Chris. I didn't include Client as there wasn't a problem there, I was purely focusing on the area where 3NF had failed. 🙂

Thom~

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

twgrops SSC Enthusiast Points: 185 More actions · Answer 10

Thom A - Tuesday, November 14, 2017 1:45 AM
Chris Harshman - Monday, November 13, 2017 4:41 PM
twgrops - Monday, November 13, 2017 4:12 PM
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
I think in Thom's example he didn't write out the Client table because it's fine as is. I also think he has better table names, and that helps to think about the relationships between items better. The table you call Price is what he calls Equipment, and the table you call Equipment is what he calls Equipment Order.
It's probably easiest to think of it in terms of 3 entities, Client, Order, Equipment. I assume the following relationships exist:
- A client can have 0 or more orders, and each order only belongs to 1 client
- An order can have 1 or more equipments on it, and each equipment can be on 0 or more orders
- Since order and equipment have a many-to-many relationship, there needs to be an intersection table between them
That's exactly what I'm going for, yes. Thanks for the assist Chris. I didn't include Client as there wasn't a problem there, I was purely focusing on the area where 3NF had failed. 🙂

Yes thank you for both of your help, I am more than confident now that it is correct enough. Also I have learned a loads from this. Many Many Thanks