Functional Dependency

A functional dependency(FD) over schema R is a statement of the form R: X → Y, where X, Y Í schema(R).

     A FD R: X → Y is satisfied in a relation r over R, denoted by r |= R: X → Y, if for all t1, t2 Í r, if t1[X] = t2[X], then t1[Y] = t2[Y]

Examples:

The set F of functional dependencies in our banking example

1. Branch-schema: branch-name → assets, branch-city

The name of the branch is the primary key of the relation schema Branch-schema. Each branch has an amount of money (assets) and is located in a city.

branch-name	assets	branch-city
Clear Water Bay	6,000,000	Hong Kong
Downtown	12,000,000	Edmonton
Howe	9,000,000	Vancouver
McEwan	6,000,000	Calgary
Signal Hill	8,000,000	Calgary
Tsuen Wan	8,000,000	Hong Kong

 

2. Loan-schema: loan-number → amount, branch-name

The identifier loan-number is the primary key of the relation schema Loan-schema. Each loan is of a certain amount of money and is granted by a branch of the bank.

branch-name	loan-number	amount
Clear Water Bay	1	150,000
Howe	2	20,000
Howe	3	600,000
McEwan	4	80,000
Signal Hill	5	90,000
Signal Hill	6	20,000
Downtown	7	30,000
Downtown	8	10,000
Clear Water Bay	9	250,000
Tsuen Wan	10	10,000

 

3. Borrower-schema: loan-number → customer-id

The identifier loan-number is the primary key of the relation schema Borrower-schema. Each loan is borrowed by a customer of the bank.

customer-id	loan-number
C8392380567	1
C8392380567	9
C8954385123	10
C2389490434	4
C9384899234	5
C0930238083	6
C3487327487	7
C3249893849	8
C3948938442	3
C2394893848	2

 

4. Customer-schema: customer-id → customer-name, customer-street, customer-city

The name of the customer is the primary key of the relation schema Customer-schema. Each customer has a street and a city in his or her address.

customer-id	customer-name	customer-street	customer-city
C8392380567	Amy	Ping Ting Road	Hong Kong
C8954385123	Bob	Texaco Road	Hong Kong
C2389490434	Carson	Deerfoot Trail	Calgary
C9384899234	David	University Drive	Calgary
C0930238083	John	Ogden Road	Calgary
C3487327487	Linda	Kingsway Drive	Edmonton
C3249893849	Mandy	Victoria Road	Edmonton
C3948938442	Paul	Hornby Road	Vancouver
C2394893848	Ricky	Howe Road	Vancouver

 

5. Credit-card-schema: credit-card-number → limit, expiry-date

The credit-card-number is the primary key of the relation schema Credit-card-schema. Each credit card has a limit on the amount of money that its owner can consume and also an expiry date.

credit-card-number	limit	expiry-date
0000-1111-1111-1111	$5,000.00	1-Mar-02
0000-1111-1111-2222	$10,000.00	1-Dec-05
0000-2222-2222-2222	$5,000.00	1-Dec-03
0000-3333-3333-3333	$3,000.00	1-Nov-04
0000-4444-4444-4444	$20,000.00	1-Jan-03
0000-5555-5555-5555	$10,000.00	1-Jan-05
0000-6666-6666-6666	$10,000.00	1-Apr-04
0000-7777-7777-7777	$10,000.00	1-Jan-05
0000-8888-8888-8888	$20,000.00	1-Jan-02
0000-9999-9999-0000	$3,000.00	1-Jan-03
0000-9999-9999-1111	$10,000.00	1-Jan-02

6. Account-schema: account-number → category, balance

The account-number is the primary key of the relation schema Account-schema. Each saving account has a unique account-number, a category type and a current balance value.

account-number	category	balance
0-1-2222-3333-4444	Hong Kong dollars	10,000.00
0-1-2222-3333-5555	Hong Kong dollars	500,000.00
0-1-2222-2222-2222	Hong Kong dollars	2,000.00
0-1-3333-3333-3333	Canadian dollars	1,000.00
0-1-4444-4444-4444	Canadian dollars	5,000.00
0-1-5555-5555-5555	Canadian dollars	5,000.00
0-1-6666-6666-6666	Canadian dollars	3,000.00
0-1-7777-7777-7777	Canadian dollars	2,000.00
0-1-8888-8888-8888	Canadian dollars	1,000.00
0-1-9999-9999-0000	Canadian dollars	1,000.00
0-1-9999-9999-1111	Canadian dollars	2,000.00

 

7. Banker-schema: branch-name, customer-id → banker-id

The branch name and the customer-id together give the primary key of the relation schema Banker-schema. Each customer is served by a banker in a branch of the bank.

8. Banker-schema: banker-id → branch-name

Each banker works in one branch of the bank.

branch-name	customer-id	banker-id
Clear Water Bay	Amy	Rosana
Downtown	Linda	Carina
Howe	Paul	Gloria
McEwan	David	Elaine
Signal Hill	John	Wynne
Signal Hill	Bob	Shirley
Signal Hill	Carson	Wynne
Tsuen Wan	Bob	Kadora

9. Banker-info-schema: banker-ID → banker-name, banker-email

The banker-ID is the primary key of the relation schema Banker-info-schema. Each banker who works in the bank has a unique banker-ID, a name and an email address for communication.

banker-id	banker-name	branch-email
sta_rosana	Rosana	rosana@abcbank.com
sta_carina	Carina	carina@abcbank.com
sta_gloria	Gloria	gloria@abcbank.com
sta_elaine	Elaine	elaine@abcbank.com
sta_wynne	Wynne	wynne@abcbank.com
sta_shirley	Shirley	shirley@abcbank.com
sta_wynne	Wynne	wynne@abcbank.com
sta_kadora	Kadora	kadora@abcbank.com

 

Example proofs demonstrating Armstrong ’s axiom system for FD: