识别功能依赖性II [英] Identifying Functional Dependencies II

问题描述

我对上一篇文章有​​点困惑，所以我找到了一个很好的例子，应该可以解决问题。

I was getting a little confused with the last post so I found a nice example which should clear things up.

hireDate& carReg是主键。所以我的问题是谁能找到除了我在下面确定的功能之外的任何其他功能依赖项。也欢迎进行修改：

hireDate & carReg are the primary keys. So my question can anyone find any extra functional dependencies other than the ones I have identified below....Modifications also welcome:

fd1 carReg -> make, model, outletNo, outletLoc
fd2 custNo -> custName
fd3 outletNo -> outletLoc
fd4 model -> make (only if we assume a model name is unique to a make)
fd5 carReg, hireDate -> make, model, custNo, custName, outletNo, outletLoc 

我不确定以上是否正确而且我相信还有更多。

i'm not sure if the above are correct and I am sure there are more. Please can someone help me finally understand these damned FD's!

编辑：根据catcall的回答...。我的问题是：custName-> custNo有效的FD是什么？ ？对于上述关系，可以肯定的是，一个客户名称恰好映射到一个客户编号，但是根据直觉，我们知道可以将多个J SMith添加到表中。在这种情况下，此FD无效，因为它形成1 .. *关系。我们真的可以说custName-> custNo不知道这个事实吗？我们是否仅将FD作为样本数据的依据？还是我们考虑到可以添加的可能值？

Based on catcall's answer.... My question is this: How is custName -> custNo a valid FD? For the above relation, sure, a customer name maps onto exactly one customer number, but by intuition, we know more than one J SMith could be added to the table. If this is the case, this FD is void as it forms a 1..* relationship. Can we really say that custName -> custNo knowing this fact? Do we merely base FD's on the sample data? Or do we take into account the possible values that can be added?

推荐答案

一目了然。 。

custName -> custNo
model -> make
outletLoc -> outletNo
carReg, custNo -> hireDate
carReg, custName -> hireDate

我敢肯定还有其他人。样本数据不具有代表性，当您尝试从数据确定功能依赖性时，这就是一个问题。假设您的样本数据只有一行。

And I'm sure there are others. The sample data isn't representative, and that's a problem when you try to determine functional dependencies from data. Let's say your sample data had only one row.

carReg    hireDate make  model  custNo  custName  outletNo  outletLoc
--
MS34 0GD  14/5/03  Ford  Focus  C100    Smith, J  01        Bearsden

FD回答问题，给'x'一个值，我是否知道'y'一个值而只有一个值？基于该单行样本数据集，每个属性确定其他每个属性。 custNo确定hireDate。 hireDate确定outletLoc。

FDs answer the question, "Given one value for 'x', do I know one and only one value for 'y'?" Based on that one-row set of sample data, every attribute determines every other attribute. custNo determines hireDate. hireDate determines outletLoc. custName determines model.

当样本数据不具有代表性时，很容易出现无效的FD。您需要更多具有代表性的示例数据，以清除一些无效的功能依赖性。

When sample data isn't representative, it's easy to turn up FDs that aren't valid. You need more representative sample data to weed out some invalid functional dependencies.

custName -> custNo isn't valid ('C101', 'Hen, P')
carReg, custNo -> hireDate isn't valid ('MS34 0GD', 'C100', '15/7/04')
carReg, custName -> hireDate isn't valid ('MS34 0GD', 'Hen, P', '15/8/03')

您可以使用SQL调查示例数据中的功能依赖性。

You can investigate functional dependencies in sample data by using SQL.

create table reg (
  CarReg char(8) not null,
  hireDate date not null,
  Make varchar(10) not null,
  model varchar(10) not null,
  custNo char(4) not null,
  custName varchar(10) not null,
  outletNo char(2) not null,
  outletLoc varchar(15) not null
);

insert into reg values
('MS34 OGD', '2003-05-14', 'Ford', 'Focus', 'C100', 'Smith, J', '01', 'Bearsden'),
('MS34 OGD', '2003-05-15', 'Ford', 'Focus', 'C201', 'Hen, P', '01', 'Bearsden'),
('NS34 TPR', '2003-05-16', 'Nissan', 'Sunny', 'C100', 'Smith, J', '01', 'Bearsden'),
('MH34 BRP', '2003-05-14', 'Ford', 'Ka', 'C313', 'Blatt, O', '02', 'Kelvinbridge'),
('MH34 BRP', '2003-05-20', 'Ford', 'Ka', 'C100', 'Smith, J', '02', 'Kelvinbridge'),
('MD51 OPQ', '2003-05-20', 'Nissan', 'Sunny', 'C295', 'Pen, T', '02', 'Kelvinbridge');

模型是否确定制造商？

select distinct model 
from reg
order by model;

model
--
Focus
Ka
Sunny

三个不同的模型。 。 。

Three distinct models . . .

select model, make
from reg
group by model, make
order by model;

model   make
--
Focus   Ford
Ka      Ford
Sunny   Nissan

是的。每个型号一个。根据样本数据，模型->制造。

Yup. One make for each model. Based on the sample data, model -> make.

carReg，custName-> hireDate吗？

Does carReg, custName -> hireDate?

select distinct carReg, custName
from reg
order by custName;

carReg
--
MH34 BRP  Blatt, O
MS34 OGD  Hen, P
MD51 OPQ  Pen, T
MS34 OGD  Smith, J
NS34 TPR  Smith, J
MH34 BRP  Smith, J

六个carreg和custName的组合。

Six distinct combinations of carReg and custName.

select carReg, custName, hireDate
from reg
group by carReg, custName, hireDate
order by custName;

carReg  custName  hireDate
--
MH34 BRP  Blatt, O  2003-05-14
MS34 OGD  Hen, P    2003-05-15
MD51 OPQ  Pen, T    2003-05-20
MH34 BRP  Smith, J  2003-05-20
NS34 TPR  Smith, J  2003-05-16
MS34 OGD  Smith, J  2003-05-14

是的。 carReg和custName的每种组合都需要一个hiredDate。因此，基于示例数据{carReg，custName}-> hireDate。

Yup. One hireDate for each combination of carReg and custName. So based on the sample data, {carReg, custName} -> hireDate.

这篇关于识别功能依赖性II的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！