1 / 9

Join Dependencies and Fifth Normal Form

Join Dependencies and Fifth Normal Form. Join Dependencies:.

lucky
Download Presentation

Join Dependencies and Fifth Normal Form

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Join Dependencies and Fifth Normal Form

  2. Join Dependencies: • A join dependency (JD) denoted by JD (R1, R2, …….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 R1, R2, …….Rn. ie. for every such r we have, π(πR1(r), πR2(r), ……. πRn(r)) = r.

  3. Join Dependencies (cont…) • A join dependency JD (R1, R2, …….Rn) specified on relation schema R is a trivial JD if one of the relation schema R in JD (R1, R2, …….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 R1 R2 (Decompose) R

  4. Fifth Normal Form (5NF) • Definition: A relation R is in fifth normal form (5NF) if and only if the following conditions are satisfied simultaneously. (i) R is already in 4NF. (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, 5NF is of little practical use for a database designer.

  5. Example 1 Shipments Shipments Join over part numbers

  6. PJ Join over part numbers JS Join over project numbers, supplier numbers Original shipments (Join first two)

  7. Example 2: Dealer – Ports – Customer (DPC) Dealer – Ports (DP) Ports – Customer (PC)

  8. Customer – Dealer (CD) Join over ports DP – DC Join DP – DC with CD over customer and dealer together.

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

More Related