Kszlt a HEFOP3'3'1P'20040600181'0 projekt keretben

1
Készült a HEFOP-3.3.1-P.-2004-06-0018/1.0 projekt
keretében
A szakirány neve

• Database Management Systems

Modul 3 Relational Database Design 3.2 Normal
forms 1NF, 2NF, 3NF, BCNF (2 lectures)
Az Európai Szociális Alap támogatásával
2
OUTLINE

• Motivation - an illustrative example
• Anomalies
• - update
• - delettion
• - insertion
• Normalization
• Normal forms
• - 1st Normal Form, example
• - 2nd Normal Form, example
• - 3rd Normal Form, example
• - Boyce-Codd Normal Form, example
• (- 4th Normal Form, 5th Normal Form)
• Relationship among normal forms

3
WHY? Motivation

• Example
• DRIVERS
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE-TYPE,
  VEHICLE_ID ROUTE_NUMBER, ROUTE_PATH,
  ROUTE_SCHEDULE)
• What is your guess, in how many rows does an
  instance of this relation have the data for the
  address of a driver, if this driver is assigned
  to drive a bus, a tram and an underground
  vehicle?
• Suppose, that this driver is also assigned to 4
  different routes for each vehicle (s)he can
  drive. You (the administrator in the PTS office)
  are told to modify the address because (s)he has
  moved into another place. How many rows have to
  be retyped by you?

4
WHY ? Motivation

• Example (cont)
• DRIVERS
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE-TYPE,
  VEHICLE_ID ROUTE_NUMBER, ROUTE_PATH,
  ROUTE_SCHEDULE)
• Problem
• Uncontrolled redundancy leads to anomalies
• Update anomaly occurs if some information is
  stored repeatedly. If one istance of such data is
  updated, then inconsistency is created, unless
  all occurance are updated to the same new value
  as the first instance has.

5
WHY? Motivation

• Example (cont)
• DRIVERS
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE-TYPE,
  VEHICLE_ID ROUTE_NUMBER, ROUTE_PATH,
  ROUTE_SCHEDULE)
• ? Suppose that from next month on 10 new
  routes will be available. At the moment all the
  drivers all overloaded, so no one is assigned to
  the new routes. How can you insert the rows with
  regarding to these new facilities?

6
WHY? Motivation

• Problem
• Uncontrolled redundancy leads to anomalies
• Insertion anomaly occurs if it is not possible
  to store information unless other information is
  stored also.

7
WHY? Motivation

• Example (cont)
• DRIVERS
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE-TYPE,
  VEHICLE_ID ROUTE_NUMBER, ROUTE_PATH,
  ROUTE_SCHEDULE)
• Suppose that there are routes, such that only one
  driver is assigned to each of them, since they
  have an hourly timing. Unfortunately all these
  drivers decided to work elsewhere. How can you
  reflect this change in the present database?

8
WHY? Motivation

• Problem
• Uncontrolled redundancy leads to anomalies
• Deletion anomaly occurs if it is not possible to
  delete some information without losing some
  information also.

9
WHY? Motivation

• Anomalies
• ? Update
• ? Insertion
• ? Deletion

10
Solution for avoiding anomalies Normalization

• Relations must be decomposed into good forms.
• These standard forms are called normal forms.
• The process to get normal forms is called
  normalization.
• Normalization
• Theory based on dependencies
• - functional
• - multivalued
• - other
• Tool decomposition slicing the relation
  vertically like projection

11
A decomposition example

• Tool decomposition slicing the relation
  vertically like projection
• DRIVERS
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE-TYPE,
  VEHICLE_ID ROUTE_NUMBER, ROUTE_PATH,
  ROUTE_SCHEDULE)
• Decomposition
• DRIVERS2
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE_TYPE)
• ROUTES
• (ROUTE_NUMBER, ROUTE_PATH, ROUTE_SCHEDULE)
• ASSIGNED_TO(ID, ROUTE_NUMBER)
• CAN_DRIVE(ID, VEHICLE_ID)

12
EXAMPLE

• What anomalies can you discover int he following
  schema?
• Teachers
• (SSN, Name, Address, Phone, CourseName,
  No-of-lectures, Description)
• Solution
• Redundancy How many times are data stored about
  teachers? About courses?
• Update What if the address of a teacher is
  changed?
• Delete What information is lost if a teacher is
  fired?
• Insertion
• A new curricula is under introducing, but no
  teachers are assigned yet for the courses. What
  would be resulted to store the informatin about
  the courses?

13
EXAMPLE (cont.)

• Dependencies in relation Teachers
• Possible cause of pitfalls mixed entity sets
• Other reason to use E/R model

14
EXAMPLE (cont.)

• Defense against PITFALLS Decomposition
• Teachers (SSN, Name, Address, Phone)
• Courses (Course-Name, No-of-lectures,
  Description)
• Teaches (SSN, Course-Name)
• The technique of decomposition will be covered
  in the next lectures

15
WHERE ARE WE IN THE DESIGN PROCESS?

• Normalization

16
NORMAL FORMS

• Normal forms
• ? 1st Normal Form 1NF
• 2nd Normal Form 2NF (FD)
• 3rd Normal Forms 3NF (FD)
• 4th Normal Form 4NF (MVD)
• Boyce-Codd Normal form BCNF
• 5th Normal Form (Join)5NF (lossless join decomp)
• Further Normal Forms Generalized dependencies
  (pattern)

17
1st Normal Form 1NF

• Definition
• 1NF each component of a tuple in the relation
  has an atomic value.
• ?
• ?
• a relation in 1NF has a candidate key

18
1NF

• Example
• Student
• (SSN, Name, Address(Street, Number, City)
  Course-name, Mark)
• NOT in 1NF
• Decomposition
• Student (SSN, City, Street, Number)
• Studies(SSN, Course-name, Mark)

19
2NF

• In the functional dependency ??? ? is totally
  functionally determined by ? if there is no ? ?
  ?, such that ???.
• In the functional dependency ??? ? is partially
  functionally determined by ? if there is a ? ? ?,
  such that ???. ??? is a partial dependency.
• Prime attribute involved in a CK
• Nonprime attribute none of the CKs contain that
  attribute
• 2nd Normal Form if all non-prime attribute is
  functionally totally depends on ANY CK
• 2nd Normal Form non-prime attributes do not
  depend partially on ANY CK. is functionally
  totally depends on any candidate key.
• ??? violates the given normal form, if it does
  not satisfy the conditions prewritten in the
  definitionof the normal form.
• In case of 2NF ??? violates 2NF if ? is a subset
  of a candidate key, and ? is not prime(not
  involved in any candidate key)

20
2NF

• Example
• DRIVERS(ID, NAME, ADDRESS, PHONE-NUMBER,
  LICENCE_TYPE, VEHICLE_ID, ROUTE_NUMBER,
  ROUTE_PATH, ROUTE_SCHEDULE)
• Is this relation in 2NF?
• What are the CKs?

21
2NF

• Are these relations below in 2NF?
• DRIVERS2
• (ID, NAME, ADDRESS, PHONE-NUMBER, LICENCE_TYPE)
• ROUTES
• (ROUTE_NUMBER, ROUTE_PATH, ROUTE_SCHEDULE)
• ASSIGNED_TO(ID, ROUTE_NUMBER)
• CAN_DRIVE(ID, VEHICLE_ID)

22
3NF

• The attribute set ? depends transitively on a
  set of attribute ? if there is a set of
  attributes ? such that ? is functionally depends
  on ? , and ? functionally determines ? ??? and
  ???.
• Third Normal Form
• Def1 A relation is in 3NF , if it is in 2NF and
  a non-prime attribute does not depend
  transitively on any candidate key.
• Def2 A relation is in 3NF, if for each
  nontrivial functional dependemcy ??? in the
  closure of the given dependency set either
• ? is a superkey, or
• ? is a prime attribute.

23
3NF

• Def1 A relation is in 3NF , if it is in 2NF and
  a non-prime attribute does not depend
  transitively on any candidate key.
• Def2 A relation is in 3NF, if for each
  nontrivial functional dependemcy ??? in the
  closure of the given dependency set either ? is
  a superkey, or ? is a prime attribute.
• Theorem Def1 is eqvivalent to Def2
• Proof
• I.Def2 implies Def1
• If ??? violates 3NF by Def2, it can happen in
  the following way
• ? is not a superkey AND ? non-prime
• If ? is not a superkey, then either
• ? ? Key, then ??? means a partial dependency on
  that key, so R is not in 2NF
• OR
• ? ? Key, then Key?? must hold, so because of ???
  Key determines ? transitively, so the relation
  is not in 3NF.
• II. Def1 implies Def2 proof is omitted-easy, try
  on your own!

24
3NF

• Example
• Consider the relation schema below (suppose that
  one employee works for exactly one department)
• Employees (SSN, E-name, E-address, Dept-name,
  Salary, Dept-Address, Dept-Phone)
• Is this relation in 1NF or 2NF?
• What are the functional dependencies in this
  schema?
• What anomalies can you find?
• Is there a transitive dependency?
• What do you think is the source of anomalies?

25
3NF

• Employees (SSN, E-name, E-address, Dept-name,
  Salary, Dept-Address, Dept-Phone).
• Dept-name? Dept-Address, Dept-Phone violates to
  be in 3NF calls for decomposition
• R1
• Departments
• (Dept-name, Dept-Address, Dept-Phone)
• R2
• Employees(SSN, Dept-name, E-name, E-address,
  Salary)

26
BCNF

• Boyce-Codd Normal Form (BCNF)
• Relation R is in BCNF if for each nontrivial
  functional dependency ???, ? is a superkey.
• Theorem Every two attributes relation is in
  BCNF (Homework)

27
Summary

• Relationships among normal forms