normalization of database tables
Download
Skip this Video
Download Presentation
Normalization of Database Tables

Loading in 2 Seconds...

play fullscreen
1 / 30

Normalization of Database Tables - PowerPoint PPT Presentation


  • 161 Views
  • Uploaded on

Normalization of Database Tables. CHAPTER 4. Chapter Objectives. Understand concepts of normalization Learn how to normalize tables Understand normalization and database design issues. Database Tables and Normalization. Normalization is a process for assigning attributes to entities.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Normalization of Database Tables' - janice


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
chapter objectives
Chapter Objectives
  • Understand concepts of normalization
  • Learn how to normalize tables
  • Understand normalization and database design issues
database tables and normalization
Database Tables and Normalization
  • Normalizationis a process for assigning attributes to entities.
  • It reduces data redundancies.
  • An un-normalized relation (table) stores redundant data, which can cause insertion, deletion, and modification anomalies.
  • In simple words: Normalization means keeping a single copy of data in your database.
  • Normalization theory provides a step by step method to remove redundant data and undesirable table structures.
normal forms
Normal Forms
  • Tables are normalized by applying rules to create a series of normal forms:
    • First normal form (1NF)
    • Second normal form (2NF)
    • Third normal form (3NF)
    • Boyce/Codd normal form (BCNF)
    • Fourth normal form (4NF)
    • Projection Join normal form (PJNF, aka 5NF)
  • A table or relation in a higher level normal form always confirms to lower level normal forms.
normal forms1

1NF Relations

2NF Relations

3NF Relations

BCNF Relations

4NF Relations

PJ/NF (5NF) Relations

Normal Forms
  • While higher level normal forms are available, normalization up to BCNF is often found to be adequate for business data.
first normal form
First Normal Form
  • A relation is in 1NF if all underlying domains contain atomic values only, i.e., the intersection of each row and column contains one and only one value.
  • The relation must not contain repeating groups.

PNo PName ENo EName Jcode ChgHr Hrs

1 Alpha 101 John Doe NE $65 20

105 Jane Vo SA $80 15

110 Bob Lund CP $60 40

2 Beta 101 John Doe NE $65 20

108 Jeb Lee NE $65 15

106 Sara Lee SA $80 20

3 Omega 102 Beth Reed PM $125 20

105 Jane Vo SA $80 10

Is the above relation in 1NF?

first normal form1
First Normal Form
  • The previous relation can be converted into first normal form by adding Pno and Pname to each row.

PNo PName ENo EName Jcode ChgHr Hrs

1 Alpha 101 John Doe NE $65 20

1 Alpha 105 Jane Vo SA $80 15

1 Alpha 110 Bob Lund CP $60 40

2 Beta 101 John Doe NE $65 20

2 Beta 108 Jeb Lee NE $65 15

2 Beta 106 Sara Lee SA $80 20

3 Omega 102 Beth Reed PM $125 20

3 Omega 105 Jane Vo SA $80 10

What is the primary key in this relation?

Do you see redundant data in this table?

What anomalies could be caused?

functional dependency revisited
Functional Dependency Revisited
  • If A and B are attributes (or group of attributes) of a relation R, B is functionally dependent on A (denoted A B), if each value of A in R is associated with exactly one value of B in R.
  • A is called a determinant.
  • Consider the relation
    • Student (ID, Name, Soc Sec Nbr, Major, Deptmt)
    • Assume a department offers several majors, e.g. INSY department offers, INSY, MASI, and POMA majors.
    • How many determinants can you identify in Student?
functional dependency revisited1

ID

Functional Dependency Revisited
  • A Dependency diagram

Name

Soc_Sec_Nbr

Major

Dept

functional dependency revisited2
Functional Dependency Revisited
  • Full functional dependency
    • Attribute B is fully functionally dependent on attribute A if it is functionally dependent on A and not functionally dependent on any proper subset of A.
    • This becomes an issue only with composite keys.
  • Transitive dependency
    • A, B and C are attributes of a relation such that A B and B C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C)
second normal form
Second Normal Form
  • Dependency diagram for Project

PNo

ENo

PName

ChgHr

Hrs

EName

JCode

second normal form1
Second Normal Form
  • A relation is in 2NF if:
    • It is in 1NF and
    • every nonkey attribute is fully dependent on the primary key, i.e., no partial dependency.
      • A nonkey attribute is one that is not a primary key or part of a primary key.
    • We create new relations that are in 2NF through projection of the original relation.
      • Project(PNo, PName)
      • Employee(ENo, EName, Jcode, ChgHr)
      • Charge(PNo, ENo, Hrs)
slide13
2NF

2NF

PNo

PName

ENo

ChgHr

EName

JCode

PNo

ENo

Hrs

second normal form2

Charge

PNo ENo Hrs

1 101 20

1 105 15

1 110 40

2 101 20

2 108 15

2 106 20

3 102 20

3 105 10

Employee

ENo EName JCode ChgHr

101 John Doe NE $65

102 Beth Reed PM $125

105 Jane Vo SA $80

106 Sara Lee SA $80

108 Jeb Lee NE $65

110 Bob Lund CP $60

Project

PNo PName

1 Alpha

2 Beta

3 Omega

Second Normal Form
  • Tables in 2NF
second normal form3
Second Normal Form
  • Note that the original relation can be recreated through natural join of the new relation.
  • Thus, no information is lost in the process of creating 2NF relations from a 1NF relation. This is called nonloss decomposition.
  • If a relation that is in 1NF has a non composite primary key (i.e., the primary key consists of a single attribute) what can you say about its status with regard to 2NF?
  • Do you see any redundant data in the tables that are in 2NF?
  • What anomalies could be caused by such redundancy?
third normal form
Third Normal Form
  • A relation is in 3NF if:
    • It is in 2NF and
    • every nonkey attribute is nontransitively dependent on the primary key (i.e., no transitive dependency).
  • Relation Employee has a transitive dependency:
    • ENo JCode ChgHr
  • Employee can be replaced by two relations, that are in 3NF:
    • Employee(ENo, EName, Jcode)
    • Job(JCode, ChgHr)
slide17
3NF

3NF

PNo

PName

ENo

ChgHr

EName

JCode

JCode

PNo

ENo

Hrs

third normal form1

Charge

PNo ENo Hrs

1 101 20

1 105 15

1 110 40

2 101 20

2 108 15

2 106 20

3 102 20

3 105 10

Project

PNo PName

1 Alpha

2 Beta

3 Omega

Employee

ENo EName Jcode

101 John Doe NE

102 Beth Reed PM

105 Jane Vo SA

106 Sara Lee SA

108 Jeb Lee NE

110 Bob Lund CP

Job

Jcode ChgHr

CP $60

NE $65

PM $125

SA $80

Third Normal Form
  • Tables in 3NF
boyce codd normal form
Boyce-Codd Normal Form
  • A relation is in BCNF if
    • every determinant is a candidate key.
      • A determinant is an attribute (combination of attributes) on which some other attribute is fully functionally dependent.
  • BCNF is a special case of 3NF.
  • The potential to violate BCNF may occur in a relation that:
    • contains two (or more) composite candidate keys,
    • these keys overlap and share at least one attribute.
  • Thus, if a table contains only one candidate key or only non-composite keys, then 3NF and BCNF are equivalent.
boyce codd normal form1

CID IDate ITime StaffID RmNo

C01 8-22-99 10:00 S01 B107

C02 8-22-99 11:00 S01 B107

C03 8-22-99 10:00 S05 B108

C01 8-29-99 3:00 S06 B108

Boyce-Codd Normal Form
  • Consider the following example:
    • The members of a recruiting team interview candidates on a one-to-one basis. Each member is assigned a particular room on a given date. Each candidate is interviewed only once on a specific date. He/she may return for follow up interviews on later dates.
    • Interview (CID, IDate, ITime, StaffID, RmNo)
boyce codd normal form2
Boyce-Codd Normal Form
  • This relation has following functional dependencies:
    • CID, IDate ITime, StaffID, RmNo
    • StaffID, IDate, ITime CID, RmNo
    • RmNo, Idate, Itime StaffID, CID
    • StaffID, IDate RmNo
  • This relation does not have any partial or transitive dependencies on the primary key (CID, IDate)
  • It is not in BCNF because (StaffID, Idate) is a determinant but not a candidate key.
  • The new relations in BCNF are:
    • Interview (CID, IDate, ITime, StaffID)
    • Room(StaffID, IDate, RmNo)
dependency diagram
Dependency Diagram

Dependency diagram

Fig 1

Fig 2

Fig 3

fourth normal form

CTXN (Normalized)

Course Teacher Text

Physics Green Basic Mechanics

Physics Green Intro to Optics

Physics Brown Basic Mechanics

Physics Brown Intro to Optics

Math White Modern Algebra

Math White Intro to Calculus

CTXU (Unnormalized)

Course Teacher Text

Physics Green Basic Mechanics

Brown Intro to Optics

Math White Modern Algebra

Intro to Calculus

Fourth Normal Form
  • A table is in 4NF if
    • it is in 3NF and
    • has no multiple sets of multivalued dependencies.
  • Consider the following example:
    • Each course is taught by many teachers and requires many texts.
fourth normal form1
Fourth Normal Form
  • CTXN is in BCNF, because it is all key and there are no other functional dependencies.
  • It, however, has redundant data that could cause update anomalies.
  • This table shows two multivalued dependencies:
    • Each course has a defined set of teachers and
      • Course Teacher
    • Each course has a defined set of textbooks.
      • Course Text
  • MVDs can exist only when the relation has at least three attributes.
  • An FD is a special case of MVD when the set of dependent values has a single value.
fourth normal form2

CT

Course Teacher

Physics Green

Physics Brown

Math White

Fourth Normal Form
  • Tables in 4NF

CX

Course Text

Physics Basic Mechanics

Physics Intro to Optics

Math Modern Algebra

Math Intro to Calculus

slide28

Conversion to 4NF

Figure 4.15

Set of Tables in 4NF

Figure 4.14

Multivalued Dependencies

slide29

Normalization and Database Design

  • Normalization should be part of the design process
  • E-R Diagram provides macro view
  • Normalization provides micro view of entities
    • Focuses on characteristics of specific entities
    • May yield additional entities
  • Difficult to separate normalization from E-R diagramming
  • Business rules must be determined
  • Normalization purity is difficult to sustain due to conflict in:
    • Design efficiency
    • Information requirements
    • Processing
denormalization
Denormalization
  • Normalized (decomposed) tables require additional processing, thus reducing system speed.
  • Sometimes normalization is not done keeping in mind processing speed requirements and practical aspects of the situation.
  • A good example is: storing Zip code and City as attributes in a Customer relation violates 3NF because City is transitively dependent on Cust ID via Zip Code.
    • Why should we not create a separate relation ZIP (ZipCode, City)?
ad