I was getting a little confused with the last post so I found a nice example which should clear things up.
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!
EDIT: 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?
At a glance . . .
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
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.
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')
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');
Does model determine make?
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.
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
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
Yup. One hireDate for each combination of carReg and custName. So based on the sample data, {carReg, custName} -> hireDate.