Module 6. Relational Database Design. Topics to be covered. Pitfalls in relational database design Functional dependencies Armstrong Axioms Decomposition Desirable properties of decomposition Boyce-code normal form 3 rd and 4 th normal form Mention of other normal forms.
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Module 6
Relational Database Design
Module 6
Module 6
Module 6
Module 6
Module 6
EMP_DEPT
ENAME
SSN
BDATE
ADDRESS
DNUMBER
DNAME
DMGRSSN
EMP_PROJ
SSN
PNUMBER
HOURS
ENAME
PNAME
PLOCATION
Module 6
Module 6
Module 6
EMP_DEPT
ENAME
SSN
BDATE
ADDRESS
DNUMBER
DNAME
DMGRSSN
EMP_PROJ
SSN
PNUMBER
HOURS
ENAME
PNAME
PLOCATION
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Problems pointed out:
Module 6
Module 6
Module 6
Module 6
Continent->NameName ->Length
Module 6
Module 6
Module 6
Module 6
Module 6
TEACHER -> COURSE
TEXT -> COURSE
TEACHER
COURSE
TEXT
Module 6
Module 6
Module 6
(MGR_SSN->MGR_PHONE) then these two dependencies together imply that (DEPT_NO->MGR_PHONE)
Module 6
F={SSN {ENAME, BDATE, ADDRESS, DNUMBER},
DNUMBER {DNAME, DMGRSSN}}
Module 6
Module 6
Module 6
Module 6
Module 6
For a given set F of FDs the set F+ may contain a large number of FDs.
It is desirable to find sets that contain smaller number of FDs than F and
still generate all the FDs of F+. Sets of FDs that satisfy this condition are
said to be equivalent sets.
Module 6
Module 6
no redundancies
no dependencies may be replaced by a dependency that involves a subset of the left hand side.
Module 6
Module 6
Module 6
Given a set F of FDs find a cononical cover for F
FC = {XZ, XYWP, XYZWQ, XZR}
Module 6
Module 6
Module 6
Normal form refers to the highest normal form condition that it meets and indicates the degree to which it can be normalized
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Pg 178, 5.1
Module 6
Definitions:
Examples:- {SSN, PNUMBER} -> HOURS is a full FD since neither SSN -> HOURS nor PNUMBER -> HOURS hold
- {SSN, PNUMBER} -> ENAME is not a full FD (it is called a partial dependency ) since SSN -> ENAME also holds
Module 6
Module 6
Module 6
Convert to
AA A
BB D
CC
D
Module 6
Prog_task(prog_ID, Prog_Pack_ID, prog_Pac_name, Tot-Hours-wor)
Prog_Pack_IDProg_Pac_name
What is the highest normal form?
Transform into next highest form?
Module 6
Definition:
Examples:
- SSN -> DMGRSSN is a transitive FD since
SSN -> DNUMBER and DNUMBER -> DMGRSSN hold
- SSN -> ENAME is non-transitive since there is no set of attributes X where SSN -> X and X -> ENAME
Module 6
NOTE:
InX -> Y and Y -> Z, with X as the primary key, we consider this a problem only if Y is not a candidate key. When Y is a candidate key, there is no problem with the transitive dependency .
E.g., Consider EMP (SSN, Emp#, Salary ).
Here, SSN -> Emp# -> Salary and Emp# is a candidate key.
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Pg 186,5.13
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
When we decompose a relation we need to make sure that we can recover the original relation from the new relations that have replaced it.
If we can recover the original relation then the decomposition is lossless else it is lossy.
Example 5.11 pg 162
Module 6
Lossless join algorithm
Example 5:12
Module 6
Module 6
Module 6
Module 6
Module 6
Module 6
A relation schema R is in fifth normal form (5NF) (or
Project-Join Normal Form (PJNF)) with respect to a
set F of functional, multivalued, and join dependencies
if, for every nontrivial join dependency JD(R1, R2, ...,
Rn) in F+ (that is, implied by F), every Ri is a superkey
of R.
Module 6
(c) The relation SUPPLY with no MVDs is in 4NF but not in 5NF if it has the JD(R1, R2, R3).
(d) Decomposing the relation SUPPLY into the 5NF relations R1, R2, and R3.
Module 6
Module 6