Keys and normal forms

1
Keys and normal forms
Lecture 14
CS157A

• Prof. Sin-Min Lee
• Department of Computer Science
• San Jose State University

2
Introduction

• Data integrity maintained by various constraints
  on data
• Functional dependencies are application
  constraints that help DB model real-world entity
• Join dependencies are a further constraint that
  help resolve some FD constraint limitations

3
(No Transcript)
4
Functional Dependencies
R
X Y Z

• FDs defined over two sets of attributes X, Y
  Ì R
• Notation X à Y reads as X determines Y
• If X à Y, then all tuples that agree on X must
  also agree on Y

1 2 3 2 4 5 1 2 4 1 2 7 2 4 8 3 7 9
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
Normal Forms provide database designers with

• A formal framework for analyzing relation schemas
  based on their keys and on the functional
  dependencies among their attributes.
• A series of tests that can be carried out on
  individual relation schemas so that the
  relational database can be normalized to any
  degree.

11
Keys

• superkeya superkey is a set of attributes S ?
  RA1,A2,.An with the property that no two
  tuples t1 and t2 in any relation state r of R
  will have t1S t2S.
• A key K is a superkey with the additional
  property that removal of any attribute from K
  will cause K not to be a superkey anymore.

12
Keys

• The difference between a key and a superkey is
  that a key has to be minimal.
• Example
• SSN is a key for EMPLOYEE, whereas SSN,
  SSN,ENAME, SSN, ENAME, BDATE are all
  superkeys.

13
Keys

• If a relation schema has more than one minimal
  key, each is called a candidate key.

14
Keys

• one of the candidate keys is designated to be the
  primary key.
• Each relation schema must have a primary key.
• For example, SSN is the only candidate key for
  EMPLOYEE, so it is also the primary key.

15
What is Normalization?

• The purpose of normalization is to produce a
  stable set of relations that is a faithful model
  of the operations of the enterprise. By following
  the principles of normalization, we can achieve a
  design that is highly flexible, allowing the
  model to be extended when needed to account for
  new attributes, entity sets, and relationships.

16
Normal Forms

• A relation is in specific normal form if it
  satisfies the set of requirements or constraints
  for that form. All of the normal forms are nested
  in that each satisfies the constraints of the
  previous one but is a "better" form because each
  eliminates flaws found in the previous

17
(No Transcript)
18
First Normal Form ( 1NF )

• the domains of attributes must include only
  atomic(simple, indivisible) values and the value
  of any attribute in a tuple must be a single
  value from the domain of the attribute.

19
First Normal Form ( 1NF )

• example
• Department
• DNAME DNUMBER DMGRSSN DLOCATIONS
• research 5 333445555
  Bellaire ,
• 
  Sugarland Houston
• Administration 4 987654321
  Stafford
• Headquarters 1 888665555
  Houston
• the domain of DLOCATIONS contains atomic values,
  but some tuples can have a set of these values.
  In this case,
• DNUMBER x-gtDLOCATIONS.
• The domain of DLOCATIONS contains sets of values
  and hence in non-atomic.

20
Our Example in 1NF
PROJ_NUM
PROJ_NAME
EMP_NUM
EMP_NAME
JOB_CLASS
CHG_HOUR
HOURS

• Key (PROJ_NUM, EMP_NUM)
• Given PROJ_NUM
• PROJ_NAME is determined
• Given EMP_NUM
• EMP_NAME, JOB_CLASS, and CHG_HOUR are determined

21
Second Normal Form

• A table is in second normal form (2NF) if
• It is in 1NF
• It includes no partial dependencies. No
  attribute is dependent on only a portion of the
  primary key.

22
Second Normal Form ( 2NF )

• it is based on the concept of full functional
  dependency.
• A functional dependency X?Y is a full functional
  dependency , for any attribute A ? X, X - A
  ? Y.

23
Second Normal Form ( 2NF )
fd1
fd2
fd3

• SSN, PNUMBER?HOURS is a fully dependency
  (neither SSN?HOURS nor PNUMBER?HOURS holds).

24
Second Normal Form ( 2NF )
EMP_PROJ
fd1
fd2
fd3
2NF NORMALIZATION
EP2
EP3
EP1
fd2
fd1
fd3

• The functional dependencies fd1,fd2,fd3 lead to
  the decomposition of EMP_PROJ into the three
  relation schemas EP1,EP2,EP3, each of which is in
  2NF.

25
Second Normal Form

• A relation is in second normal form (2NF) if and
  only if it is in first normal form and all the
  nonkey attributes are fully functionally
  dependent on the key.

26
(No Transcript)
27
Converting to 2NF

• To convert from 1NF to 2NF, list each key
  component and then the key itself.
• Each component will become the key in a new table.

28
Our Example in 2NF
Table Name PROJECT
PROJ_NUM
PROJ_NAME
Table Name EMPLOYEE
CHG_HOUR
EMP_NUM
EMP_NAME
JOB_CLASS
Table Name ASSIGN
HOURS
PROJ_NUM
EMP_NUM
29
Problems with 2NF

• Transitive Dependency
• An attribute that is dependent on a non-prime
  attribute exhibits transitive dependency.
• Still leads to data anomalies.

CHG_HOUR
EMP_NUM
EMP_NAME
JOB_CLASS
Our example contains the transitive
dependency JOB_CLASS -----gt CHG_HOUR
30
Second Normal Form

• 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 KltR such that
  (1) K-gtR,
• (2) for all B belongs to K, (K-B)-gtR not
  belongs to F, and
• (3) A belongs to K

31
(No Transcript)
32
Third Normal Form

• 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-gtA belong to F, either
• (1). X is superkey for R or
• (2). A is a prime attribute in R.

33
Third Normal Form ( 3 NF )

• Third normal form is based on the concept of
  transitive dependency.
• A functional dependency X?Y in a relation schema
  R is a transitive dependency if there is a set of
  attributes Z that is not a subset of any key of
  R, and both X?Z and Z?Y hold.

34
Third Normal Form ( 3 NF )
EMP_DEPT
ENAME SSN BDATE ADDRESS DNUMBER DNAME
DMGRSSN

• example
• the dependency SSN?DMGRSSN is transitive through
  DNUMBER in EMP_DEPT, because both the
  dependencies SSN?DNUMBER and DNUMBER?DMGRSSN hold
  and DNUMBER is not a subset of the key of
  EMP_DEPT.

35
General Definitions of Second and Third Normal
Forms

• A relation schema R is in second normal form
  (2NF) if every nonprime attribute A in R is fully
  functionally dependent on every key of R.
• A relation schema R is in 3NF if, whenever a
  functional dependency X? A holds in R, either
• (a) X is a superkey of R.
• (b) A is a prime attribute of R.

36
(No Transcript)
37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40
(No Transcript)