Try to find Partial and transitive dependencies

Question

Cust_id| Name |Ord_no|Ord_Date     |PROD-ID|Descr |Qty_ord|
C001   | Pink | O81  |    15-Apr   |P005   |Chisel|6      |
C001   | Pink | O81  |    15-Apr   |P004   |Jane  |14     |
C0075  | Red  | O99  |    16-Apr   |P015   |Saw   |3      |
C009   | Black| O56  |    16-Apr   |P033   |Punch |24     |
C009   | Black| O56  |    16-Apr   |P004   |Jane  |9      |
C001   | Pink | O88  |    17-Apr   |P015   |Saw   |10     |

From this table example I am trying to understand both this dependencies. According to me partial dependencies have a primary composite key and transitive don't have.

I think Primary key are Ord_no and Prod_id.Not sure about Cust_id

The only non key column which depends on the whole key is Quantity. All the rest are partial dependencies.Not sure about Transitive dependency exist or not in this table

Partial Dependency in the table are : • Cust_id and Name • Prod_id and Decr

Also the Transitive Dependency in the table are as follow : • Ord no_ and Ord_date can be ?

Update 1-I try to figure out but not sure about my answer. I just want clarification like order no is unique, and determines the customer so how can two different order_no 81 & 88 can have same customer id C001. Therefore I think no transitive dependency.

Please google 'stackexchange homework'. Give and/or quote your textbook & show & justify your work following it. Explain where & why you are stuck. This is just a jumble of statements not related to any definitions or algorithms. — philipxy, Apr 05 '18 at 18:19
Partial & transitive FDs are defined without reference to CKs. NFs have to do with transitive FDs & CKs. — philipxy, Apr 05 '18 at 18:34
Possible duplicate of [Identifying Transitive Dependencies](https://stackoverflow.com/q/27393366/3404097) — philipxy, Apr 05 '18 at 18:39

Tim Biegeleisen · Answer 1 · 2018-04-05T04:10:39.880

1

I see many dependencies in your current table which could be refactored:

The Cust_id most likely determines the customer name
The Ord_no determines the set of products included in that order
The PROD-ID determines the description of that product

I would suggest the following schema, involving four tables:

Customers

Cust_id (PK) | Name
C001         | Pink
C009         | Black
C0075        | Red

Products

PROD-ID (PK) | Descr
P004         | Jane
P005         | Chisel
P015         | Saw
P033         | Punch

Orders

Ord_no (PK) | Ord_Date | Cust_id
056         | 16-APR   | C009
081         | 15-APR   | C001
088         | 18-APR   | C001
099         | 16-APR   | C0075

OrdersDetails

Ord_no | PROD-ID | Qty-ord   (primary key is Ord_no, PROD-ID)
O56    | P004    | 9
O56    | P033    | 24
O81    | P004    | 14
O81    | P005    | 6
O88    | P015    | 10
O99    | P015    | 3

Now if you want the current output you have, you can obtain it via a join query:

SELECT
    c.Cust_id,
    c.Name,
    o.Ord_no,
    o.Ord_Date,
    od.PROD-ID,
    p.Descr,
    od.Qty-ord
FROM Customers c
INNER JOIN Orders o
    ON c.Cust_id = o.Cust_id
INNER JOIN OrdersDetails od
    ON o.Ord_no = od.Ord_no
INNER JOIN Products p
    ON od.PROD-ID = p.PROD-ID;

edited Apr 05 '18 at 04:10

answered Apr 05 '18 at 03:50

Tim Biegeleisen

502,043
27
286
360

So which are primary key? – Rohan Kapoor Apr 05 '18 at 03:54
@RohanKapoor I have labelled the primary key columns. My approach may not be 100% correct, but it is on the right track and way better than with what you started. – Tim Biegeleisen Apr 05 '18 at 04:00
sorry men,there was a mistake in my table I forgot to add one of the column. – Rohan Kapoor Apr 05 '18 at 04:08
Cust-id,Ord_no and Prod_id? are the primary key which I thought – Rohan Kapoor Apr 05 '18 at 04:09
Take a step back here. You don't even have normalized tables yet, and you are asking for what the primary keys are. First, is it clear why I generated the tables above? If so, then we can talk about primary keys. – Tim Biegeleisen Apr 05 '18 at 04:11
You have after removing all partial dependencies and draw the new dependency diagrams. Is am correct? – Rohan Kapoor Apr 05 '18 at 04:12
The primary keys are the leftmost columns, except for `OrdersDetails`, where the primary key is the combination of order and product IDs. – Tim Biegeleisen Apr 05 '18 at 04:14
NAME depends on CUST_NO which depends on ORD_NO.This is a transitive dependency. and The only non key column which depends on the whole key is Quantity. All the rest are partial dependencies.Is am correct? don't scold me please. :) – Rohan Kapoor Apr 05 '18 at 04:17
`CUST_NO` does depend on `ORD_NO` insofar that each order has a single customer (at least under my assumption). This is a dependency, but since we have a separate table for customers it is not a problem. The orders table only records the customer's ID. – Tim Biegeleisen Apr 05 '18 at 04:21
I'm done. I don't know how to further improve what I already gave you. – Tim Biegeleisen Apr 05 '18 at 04:28
Sorry men but I am confuse you said cust_id is PK or not? – Rohan Kapoor Apr 05 '18 at 04:29
wait let me draw a diagram and let me know if I am correct or not? – Rohan Kapoor Apr 05 '18 at 04:31
Seems OK. I don't really think in terms of diagrams. To build the correct diagram you need to go through process I went through, and stare at your data for a while to see how it is related. – Tim Biegeleisen Apr 05 '18 at 04:47
I get better understanding by that.One more query I still think `Cust_ID`don't depend on the `Ord_ num` since in the last row C001 have ord_no 088.Correct? That why no transitive dependency – Rohan Kapoor Apr 05 '18 at 05:05
Hard for me to answer using your terms. An order ID is unique, and determines the customer, but just naming a customer does not mean you know which order we are referring to. – Tim Biegeleisen Apr 05 '18 at 05:07
I got it One Customer can have many orders. But One Order cannot have many customers. sorry if I act as a dumber – Rohan Kapoor Apr 05 '18 at 14:07
I just realized there could be a dependency of the quantity ordered on the product. Suppose certain products always have to be ordered in a ceetain quantity. But if not, then what I have given you should be fine. – Tim Biegeleisen Apr 05 '18 at 14:58
In the question there was assumption mentioned _You may assume that an order number references more than one product and that the table does not contain repeating groups._ – Rohan Kapoor Apr 05 '18 at 23:45

Try to find Partial and transitive dependencies

1 Answers1