Join Dependencies and Fifth Normal Form Join Dependencies

Join Dependencies and Fifth Normal Form

Join Dependencies: • A join dependency (JD) denoted by JD (R 1, R 2, ……. Rn) specified on relation schema R, specifies a constraint on the state of R. The constraint states that query legal state r of R should have a lossless join decomposition into R 1, R 2, ……. Rn. ie. for every such r we have, π(πR 1(r), πR 2(r), ……. πRn(r)) = r.

Join Dependencies (cont…) • A join dependency JD (R 1, R 2, ……. Rn) specified on relation schema R is a trivial JD if one of the relation schema R in JD (R 1, R 2, ……. Rn) is equal to R. Such a dependency is called trivial because it has the lossless join property for any relation state r of R and hence does not specify any constraint on R. R R 1 R 2 (Decompose) R

Fifth Normal Form (5 NF) • Definition: A relation R is in fifth normal form (5 NF) if and only if the following conditions are satisfied simultaneously. (i) R is already in 4 NF. (ii) If cannot be further non-lossless decomposed [Project – Join normal form with respect to a set f of functional multivalued and join dependencies if, for every non trivial join dependency. • Actually, 5 NF is of little practical use for a database designer.

Example 1 Shipments Part number Project number S 1 P 1 J 2 S 1 P 2 J 1 S 2 P 1 J 1 S 1 P 1 J 1 Supplier number Shipments Part number Supplier number S 1 P 2 S 2 P 1 Join over part numbers

PJ Supplier number Part number P 1 J 2 P 2 J 1 P 1 Join over part numbers JS Supplier number Project number J 2 S 1 J 1 S 2 Join over project numbers, supplier numbers Original shipments Part number Project number Supplier number S 1 P 1 J 2 S 1 P 2 J 1 S 2 P 1 J 2 S 1 P 1 J 1 (Join first two) (Extra)

Example 2: Dealer – Ports – Customer (DPC) Ports Customer D 1 P 1 C 1 D 1 P 1 C 2 D 1 P 2 C 1 D 2 P 1 C 1 Dealer – Ports (DP) Ports Dealer D 1 P 1 D 1 P 2 D 2 P 1 Ports – Customer (PC) Customer Ports P 1 C 1 P 1 C 2 P 2 C 2

Customer – Dealer (CD) Dealer Customer C 1 D 1 C 2 D 1 C 1 D 2 Join over ports DP – DC Ports Customer D 1 P 1 C 1 D 1 P 1 C 2 D 1 P 2 C 1 D 2 P 1 C 1 Dealer (Extra) Join DP – DC with CD over customer and dealer together. Ports Customer D 1 P 1 C 1 D 1 P 2 C 1 D 1 P 1 C 2 D 2 P 1 C 1 Dealer

Domain – Key Normal Form (DKNF): The idea behind DKNF is to specify the ultimate normal form that takes into account all possible types of dependencies. • Definition: • A relation is in DKNF if every general constraint can be inferred from the knowledge of the attributes involved in the schema their underlying domains and the sets of attributes that from the keys. Example: Course is 3 digit long 000 to 900. Grade is from the set {A, B, C, D, E, F}. Anomalies in DKNF: Insert Anomaly: • It occurs when a tuple is inserted in a relation and the resulting relation violates one or more general constraints. Deletion Anomaly: • It occurs when a tuple from a relation is deleted and the remaining relation violates one or more general constraints.