SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
twgrops
twgrops
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 4
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.
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37965 Visits: 7164
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
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86766 Visits: 22262
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
twgrops
twgrops
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 4
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
twgrops
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 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
twgrops
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 4
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
Chris Harshman
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37965 Visits: 7164
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
Sergiy
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100655 Visits: 14345
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.
twgrops
twgrops
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 4
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
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86766 Visits: 22262
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. Smile


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search