Database Design Issues and Normalization

1
Database Design Issues and Normalization

• Constructing models of the Information used in an
  Enterprise

2
What is Normalization?

• A technique used in developing a logical data
  model
• To create an accurate representation of data, its
  relationship and constraints
• Produces a set of relations with desirable
  properties, given the data requirements of an
  enterprise
• A formal method for analyzing relations based on
  their primary keys (or candidate keys) Connolly,
  2000

3
Data Redundancy

• A major aim of relational database design is to
  produce relations that minimize data redundancy.
• Data redundancy leads to
• increased storage space and
• Update Anomalies

4
Example of Data Redundancy

• Faculty(Faculty_No, FName, FAddress, Position,
  Salary, Dept_No)
• Department(Dept_No, DName, DChair, TelNo)
• Faculty_Dept(Faculty_No, FName, FAddress,
  Position, Salary, Dept_No, DName, DChair,
  TelNo)

5
Example of Data Redundancy

• Faculty Relation

Department Relation
6
Faculty_Dept Relation
Example of Data Redundancy
7
Update Anomalies
Insertion Anomalies

• Inserting the information of a new faculty member
  requires the inclusion of the details of the
  department to which the faculty belongs.
• Inserting the details of a new department that
  has no faculty members at present requires the
  provision for null values to the attributes of
  the faculty.

8
Update Anomalies

• If a tuple (row) of the Faculty_Dept relation
  which represents the last member of a faculty in
  a department is deleted, then the details about
  the department is lost forever.
• For example, if the record of Ramez Elmasri is
  deleted, then the information about Information
  Technology department is lost.

Deletion Anomalies
9
Update Anomalies
Modification Anomalies

• In the Faculty_Dept relation, if the value of the
  department chair is updated, that is there is a
  change of management in the department, then all
  the records of all faculty belonging to the
  department should be updated.
• An inconsistent database will result if the
  required changes are not carried out (a potential
  problem).

10
Functional Dependencies

• Describes the relationship between attributes in
  a relation.
• Given Relation(A,B), B is functionally dependent
  on A, (denoted by A ? B), if each value of A in
  Relation is associated with exactly one value of
  B in Relation.
• If the value of A is known, there is only one
  value of B in all tuples (rows) that have a given
  value of A, at any moment in time. However, for a
  given value of B, there may be several different
  values of A.

11
Functional Dependencies

• In a functional dependency, the attribute or
  group of attributes on the left-hand side of the
  arrow is called the determinant.
• In a trivial functional dependency, B is
  dependent on a proper subset of A.

12
Example of Functional Dependency

• Faculty_Dept(Faculty_No, FName, FAddress,
  Position, Salary, Dept_No, DName, DChair, Tel_No)
• Position is functionally dependent on Faculty_No
• Faculty_No ? Position
• 3 ? Instructor
• 10 ? Asst. Professor
• However, Faculty_No is not functionally dependent
  on Position
• 10 (Lewis)
• Asst. Professor
• 20 (Elmasri)

13
Example of Functional Dependency
Faculty_Dept(Faculty_No, FName, FAddress,
Position, Salary, Dept_No, DName, DChair,
Tel_No)

• Faculty_No ? FName Dept_No ? DName
• Faculty_No ? FAddress Dept_No ? DChair
• Faculty_No ? Position Dept_No ? Tel_No
• Faculty_No ? Salary DName ? Dept_No
• Faculty_No ? Dept_No DChair ? Dept_No
• Faculty_No ? DName Tel_No ? Dept_No
• Faculty_No ? DChair
• Faculty_No ? Tel_No

14
Example of Functional Dependency
(Alternative notation)

• Faculty_No ? FName, FAddress, Position, Salary,
• Dept_No, DName, DChair, Tel_No
• Dept_No ? DName, DChair, Tel_No
• DName ? Dept_No
• DChair ? Dept_No
• Tel_No ? Dept_No

15
The Normalization Process

• Individual relations are tested against a set of
  rules in order to normalize the database to any
  degree.
• When a requirement is not satisfied, the relation
  that violates the requirement is decomposed into
  relations that specifically satisfies the
  requirements of the normalization.

16
The Normalization Process

• The normalization process is often executed in a
  step-by-step fashion, and each step corresponds
  to a specific normal form.
• The 1NF is the only critical aspect in creating
  appropriate relations. Subsequent normal forms
  are optional. ?
• But to avoid update anomalies, it is normally
  recommended that relational databases be
  normalized at least up to 3NF.

17
First Normal Form (1NF)

• An unnormalized form (UNF) contains one or more
  repeating groups.
• A relation is in 1NF if the intersection of each
  row and column contains one and only one value.
• the domains of the attributes must be atomic and
  that the value of the attribute must be a single
  value.
• The 1NF disallows relations within a relation or
  relations as attributes of tuples. (no tables
  within a table).

18
First Normal Form (1NF)

• To normalize to 1NF,
• remove repeating groups by entering appropriate
  data in the empty columns of rows containing
  repeating data (flattening the table) and
  identify the appropriate primary key or
• nominate an attribute or group of attributes as a
  key for the unnormalized table and remove
  repeating groups by placing the repeating data,
  along with a copy of the original key
  attribute(s) in a separate relation.

19
Example of UNF
Prop_Inspect Relation
Prop_Inspect ( Prop_No, PAddress, IDate, ITime,
Comments, Staff_No, SName, Car_Reg )
20
Example of 1NF
Prop_Inspect Relation
Prop_Inspect ( Prop_No, IDate, PAddress, ITime,
Comments, Staff_No, SName, Car_Reg )
21
Example 2 of UNF
Department Relation

• Department(Dept_No, DName, DChair, Tel_no)

22
Example 2 of 1NF (approach 1)
Department ( Dept_No, DName, DChair, Tel_No )
23
Example 2 of 1NF (approach 2)
Department(Dept_No, DName , DChair) Dept_Phone(Dep
t_No, Tel_No)
24
Full Functional Dependency

• A?B is a full functional dependency if B is fully
  functionally dependent on A but not on any proper
  subset of A.
• Example
• Employee ( ID, Name, Addr, Hourly_Rate,
  Hours_Worked, Income )
• Hourly_Rate, Hours_Worked ? Income
• The above is a full functional dependency because
  neither
• Hourly_Rate ? Income nor
  Hours_Worked ? Income

25
Partial Dependency

• A?B is a partial dependency if there is some
  attribute that can be removed from A, and the
  dependency still holds.
• Example
• Faculty (Faculty_No, FName, FAddress, Position,
  Dept_No)
• Faculty_No, FName ? Dept_No
• Dept_No is functionally dependent on
  (Faculty_No, FName) but not a
• FULL functional dependency because
• Faculty_No ? Dept_No

26
Second Normal Form (2NF)

• A relation in 2NF is a relation that is in 1NF
  and every non-primary key attribute is fully
  functionally dependent on the primary key.
• It applies to relations with composite keys.
• To normalize to 2NF, eliminate partial
  dependencies by removing functionally dependent
  attributes from the relation and placing them in
  a new relation along with a copy of their
  determinant.

27
Example of 2NF
Faculty_Work(FNo, FName, Work_Type,
Work_Desc, Hours) Functional
Dependencies FNo, Work_Type ? Hours, Fname,
Work_Desc FNo ? FName Work_Type ? Work_Desc
28
Example of 2NF
Faculty_Name (FNo, FName) Faculty_Hrs (FNo,
Work_Type, Hours) Work_Desc (Work_Type, Work_Desc)
29
Transitive Dependency

• A condition where A, B, and C are attributes of a
  relation such that if 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).
• Example
• Staff_Branch ( Staff_No, SName, SAddr, SPhone,
  Branch_No, BName )
• Functional dependencies
• Staff_No ? SName, SAddr, SPhone, Branch_No, BName
• Branch_No ? BName

30
Third Normal Form (3NF)

• A relation that is in 1NF and 2NF, and in which
  no non-primary key attribute is transitively
  dependent on the primary key.
• To normalize to 3NF, remove the transitively
  dependent attribute(s) from the relation by
  placing the attribute(s) in a new relation along
  with a copy of the determinant(s).

31
Example of 3NF

• Faculty_Dept ( FNo, FName, BDate, Address,
• DNo, DName, ChairFNo )
• Functional dependencies
• FNo ?? FName, Bdate, Address, DNo, DName,
  ChairFNo
• DNo ? DName, ChairFNo
• 3NF
• Faculty ( FNo, FName, BDate, Address, DNo )
• Department ( DNo, DName, ChairFNo )

32
Boyce-Codd Normal Form (BCNF)

• A relation is in BCNF if and only if every
  determinant is a candidate key.
• i.e., if whenever a functional dependency X?A
  holds in R, then X is a candidate key of R
• To transform to BCNF, identify all determinants
  and make sure that they are all candidate keys.

33
Example Property for Rent
34
Example of 2NF

• Prop_Inspect ( Prop_No, IDate, ITime, PAddress,
  Comments, Staff_No, SName, Car_Reg )
• Functional dependencies
• Prop_No, IDate ? ITime, PAddr, Comments,
  Staff_No, SName, Car_Reg
• Prop_No ? PAddress
• Staff_No ? SName
• Staff_No, IDate ? Car_Reg
• Car_Reg, IDate, ITime ck ? Prop_No, PAddress,
  Comments,
• Staff_No, SName
• Staff_No, IDate, ITime ck ? Prop_No, PAddress,
  Comments
• 2NF
• Property ( Prop_No, PAddress )
• Prop_Inspect (Prop_No, IDate, ITime, Comments,
• Staff_No, SName, Car_Reg )

35
Example of 3NF

• Prop_Inspect ( Prop_No, IDate, ITime, Comments,
  
• Staff_No, SName, Car_Reg )
• Functional dependencies
• Prop_No, IDate ? ITime, Comments, Staff_No,
  SName, Car_Reg
• Staff_No ? SName
• Staff_No, IDate ? Car_Reg
• Car_Reg, IDate, ITime ck ? Prop_No, Comments,
  Staff_No, SName
• Staff_No, IDate, ITime ck ? Prop_No, Comments
• 3NF
• Staff ( Staff_No, SName)
• Prop_Inspect (Prop_No, IDate, ITime, Comments,
• Staff_No, Car_Reg )

36
Example of BCNF

• Prop_Inspect (Prop_No, IDate, ITime, Comments,
• Staff_No, Car_Reg )
• Functional Dependencies
• Prop_No, IDate ? ITime, Comments, Staff_No,
  Car_Reg
• Staff_No, IDate ? Car_Reg
• Car_Reg, IDate, ITime ck ? Prop_No, Comments,
  Staff_No
• Staff_No, IDate, ITime ck ? Prop_No,
  Comments, Car_Reg
• BCNF
• Staff_Car ( Staff_No, IDate, Car_Reg )
• Inspection ( Prop_No, IDate, ITime, Comments,
  Staff_No )