IT 604 Anirban Goswami
DBMS
Project-Join Normal Form (PJNF)
A relation schema R is in PJNF with respect to a set D of functional, multivalued, and join
dependencies if for all join dependencies in D+ of the form *(R1 , R2 ,..., Rn ) where each Ri
R and R =R1 R2 ... Rn
at least one of the following holds:
*(R1 , R2 ,..., Rn ) is a trivial join dependency.
Every Ri is a superkey for R.
Since every multivalued dependency is also a join dependency, every PJNF schema is also
in 4NF.
Example
1. Consider Loan-info-schema = (branch-name, customer-name, loan-number, amount).
2. Each loan has one or more customers, is in one or more branches and has a loan amount; these
relationships are independent, hence we have the join dependency.
3. *(=(loan-number, branch-name), (loan-number, customer-name), (loan-number, amount))
4. Loan-info-schema is not in PJNF with respect to the set of dependencies containing the above
join dependency. To put Loan-info-schema into PJNF, we must decompose it into the three
schemas specified by the join dependency:
(loan-number, branch-name)
(loan-number, customer-name)
(loan-number, amount)
Domain-Key Normal Form (DKNF)
1. Domain declaration. Let A be an attribute, and let dom be a set of values. The domain
declaration A dom requires that the A value of all tuples be values in dom.
2. Key declaration. Let R be a relation schema with K R. The key declaration key (K) requires that
K be a superkey for schema R (K R). All key declarations are functional dependencies but not
all functional dependencies are key declarations.
3. General constraint. A general constraint is a predicate on the set of all relations on a given
schema.
4. Let D be a set of domain constraints and let K be a set of key constraints for a relation schema R.
Let G denote the general constraints for R. Schema R is in DKNF if D K logically imply G.
Example
1. Accounts whose account-number begins with the digit 9 are special high-interest accounts with a
minimum balance of 2500.
2. General constraint: ``If the first digit of t [account-number] is 9, then t [balance] 2500.''
3. DKNF design:
Regular-acct-schema = (branch-name, account-number, balance)
Special-acct-schema = (branch-name, account-number, balance)
1. Domain constraints for {Special-acct-schema} require that for each account:
The account number begins with 9.
The balance is greater than 2500.
DKNF rephrasing of PJNF Definition
Let R = (A1 , A2 ,..., An) be a relation schema. Let dom(Ai ) denote the domain of attribute Ai, and
let all these domains be infinite. Then all domain constraints D are of the form Ai dom (Ai ).
Let the general constraints be a set G of functional, multivalued, or join dependencies. If F is the
set of functional dependencies in G, let the set K of key constraints be those nontrivial functional
dependencies in F+ of the form R.
Schema R is in PJNF if and only if it is in DKNF with respect to D, K, and G.
Example
A violation of DKNF occurs in the following table:
1
IT 604 Anirban Goswami
DBMS
Wealthy Person
Wealthy Person Wealthy Person Type Net Worth in Dollars
Steve Eccentric Millionaire 124,543,621
Roderick Evil Billionaire 6,553,228,893
Katrina Eccentric Billionaire 8,829,462,998
Gary Evil Millionaire 495,565,211
(Assume that the domain for Wealthy Person consists of the names of all wealthy people in a pre-defined
sample of wealthy people; the domain for Wealthy Person Type consists of the values 'Eccentric
Millionaire', 'Eccentric Billionaire', 'Evil Millionaire', and 'Evil Billionaire'; and the domain for Net Worth
in Dollars consists of all integers greater than or equal to 1,000,000.)
There is a constraint linking Wealthy Person Type to Net Worth in Dollars, even though we cannot deduce
one from the other. The constraint dictates that an Eccentric Millionaire or Evil Millionaire will have a net
worth of 1,000,000 to 999,999,999 inclusive, while an Eccentric Billionaire or Evil Billionaire will have a
net worth of 1,000,000,000 or higher. This constraint is neither a domain constraint nor a key constraint;
therefore we cannot rely on domain constraints and key constraints to guarantee that an inconsistent
Wealthy Person Type / Net Worth in Dollars combination does not make its way into the database.
The DKNF violation could be eliminated by altering the Wealthy Person Type domain to make it consist of
just two values, 'Evil' and 'Eccentric' (the wealthy person's status as a millionaire or billionaire is implicit in
their Net Worth in Dollars, so no useful information is lost).
Wealthy Person
Wealthy Person Wealthy Person Type Net Worth in Dollars
Steve Eccentric 124,543,621
Roderick Evil 6,553,228,893
Katrina Eccentric 8,829,462,998
2
IT 604 Anirban Goswami
DBMS
Gary Evil 495,565,211
Wealthiness Status
Status Minimum Maximum
Millionaire 1,000,000 999,999,999
Billionaire 1,000,000,000 999,999,999,999
DKNF is frequently difficult to achieve in practice.
