Identifying Functional Dependencies II

Here is an example which should clear things up for the last post.

Enter image description here

hireDate & carReg are the primary keys. Are there extra functional dependencies (FDs) 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.

Based on Mike Sherrill Cat Recall'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 FDs 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;

    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;
    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.