CS157A. Lecture 14. Keys and Functional Dependency. Prof. Sin-Min Lee Department of Computer Science San Jose State University. Data Normalization. Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.
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.
Lecture 14
Prof. Sin-Min Lee
Department of Computer Science
San Jose State University
R
X Y Z
1 2 3
2 4 5
1 2 4
1 2 7
2 4 8
3 7 9
EMPLOYEE2 (Emp_ID, Course_Title, Name, Dept_Name, Salary, Date_Completed)
not fully functionally dependant on the primary key
By FD2. BC -> D BC result
result = {A, C}
By FD3. E ->AB E result
result = {A, C}
{A}+ = {A, C}
Now, we see
{AB}+ = {ABCD} {AC}+ = {AC} {AD}+ = {ACD}
{BC}+ = {BCD} {BD}+ = {BD} {CD}+ = {CD}
{ABC}+ = {ABCD} {ABD}+ = {ABCD} {BCD}+ = {BCD}
{ACD}+ = {ACD}
What is the largest normal form of this table?
R(A B C D E)
FD1. A ->C
FD2. BC ->D
FD3. E ->AB
Answer: {E} is the only candidate key of R
The non-prime attributes are: A, B, C, D
As FD!. A->C, we have transitive dependency.
Thus R(ABCD) is 2NF but not 3NF
Department
DNAME DNUMBER DMGRSSN DLOCATIONS
research 5 333445555 {Bellaire ,
Sugarland Houston}
Administration 4 987654321 {Stafford}
Headquarters 1 888665555 {Houston}
PROJ_NUM
PROJ_NAME
EMP_NUM
EMP_NAME
JOB_CLASS
CHG_HOUR
HOURS
EMPLOYEE2 (Emp_ID, Course_Title, Name, Dept_Name, Salary, Date_Completed)
not fully functionally dependant on the primary key
fd1
fd2
fd3
EMP_PROJ
fd1
fd2
fd3
2NF NORMALIZATION
EP2
EP3
EP1
fd2
fd1
fd3
Table Name: PROJECT
PROJ_NUM
PROJ_NAME
Table Name: EMPLOYEE
EMP_NUM
EMP_NAME
JOB_CLASS
CHG_HOUR
Table Name: ASSIGN
PROJ_NUM
EMP_NUM
HOURS
EMP_NUM
EMP_NAME
JOB_CLASS
CHG_HOUR
Our example contains the transitive dependency:
JOB_CLASS -----> CHG_HOUR
Second normal form:
Let R’ be a relation, and let F be the set of governing FDs. An attribute belongs to R’ is prime if a key of R’ contains A. In other words, A is prime in R’ if there exists K<R’ such that (1) K->R’,
(2) for all B belongs to K, (K-B)->R’ not belongs to F+, and
(3) A belongs to K
Third normal form:
Let R’ be a relation, a subset of the universal relation, in the context of a set of FDs F. R’ satisfies third normal form if for every nontrival X->A belong to F+, either
(1). X is superkey for R’ or
(2). A is a prime attribute in R’.
EMP_DEPT
ENAME SSN BDATE ADDRESS DNUMBER DNAME DMGRSSN