Advanced Relational Design

1
Advanced Relational Design

• Sue Patience
• SCAT

2
Contents

• The Relational Model
• Functional Dependency
• Normalisation

3
The Relational Model

• Broad, flexible model
• Basis for almost all DBMS products
• E.F. Codd defined well-structured normal forms
  of relations, normalisation

4
Components of the Relational Model

• Relation
• A two-dimensional table consisting of rows and
  columns
• Tuples
• The rows (records) in a relation(table)
• Attributes
• The columns (fields) in a relation(table)
• Key
• A group of one or more attributes that uniquely
  identifies a tuple

5
Functional Dependency

• Functional Dependencies(FD) are the relationships
  among the attributes within a relation.
• Attribute B is functionally dependent on
  attribute A, if the value of A determines one
  value of B.
• A ? B
• A functionally determines B, or
• B is functionally dependent on A

6
Functional Dependency Notation

• RHS known as the dependent
• LHS known as the determinant
• Eg Student No ? Programme, Student No ? Name
• 00123456s ? BA BC, 00123456s ? Tom
• Student no Name Prog

7
Functional Dependency Notation(contd)

• Student No ? Programme
• Student No is paired with only 1 value of
  Programme, conversely Programme can be paired
  with 1 or more values of Student No.
• Relationship is many to one, N1

8
Functional Dependency Notation(contd)

• Grade is functionally dependent on the
  combination of Student No and Module Code
• (Student No, Module Code) ? Grade
• (00123456s,COM348) ? 65
• Student no Module code CR Grade

9
Transitive Dependencies

• If attribute A ? B and B ? C then A ? C.
• C is said to depend on A transitively, via B.
• Eg Student no ? Residence and Residence ? Fee,
• then Student no ? Fee
• Student no
  Residence Fee

10
Question 1
11
Normalisation

• Normalisation is a process of evaluating and
  converting a relation to reduce modification
  anomalies
• Normalisation detects and eliminates data
  redundancy

12
An Anomaly

• An anomaly is an undesirable consequence of a
  data modification
• Eg deletion anomalies, insertion anomalies, etc
• Student No Sport Fee

13
Normal Forms

• Normalisation theory is built around the concept
  of Normal Forms
• Normal Forms are state-classes of relations which
  identify the level of anomaly-avoidance
• A relation is said to be in a particular normal
  form if it satisfies a certain set of
  constraints.

14
Normal Forms Levels

• 1NF First Normal Form
• 2NF Second Normal Form
• 3NF Third Normal Form
• BCNF Boyce-Codd Normal Form
• 4NF Fourth Normal Form
• 5NF Fifth Normal Form

15
Nonloss(Lossless) Decomposition

• Normalisation is the process of breaking down or
  decomposing a relation into other relations.
• The decomposition is required to be reversible,
  so that no information is lost in the process.
• Consider the relation SUPPLIER
• Supplier code Status City
  

16
Nonloss Decomposition contd
a)
Supplier code Status
Supplier code City
b)
Supplier code Status
Status City
17
First Normal Form (1NF)

• To be in First Normal Form (1NF) a relation must
  have only single-valued attributes - neither
  repeating groups nor arrays are permitted

18
Second Normal Form (2NF)

• To be in Second Normal Form (2NF) the relation
  must be in 1NF and each nonkey attribute must be
  dependent on the whole key (not a subset of the
  key)

19
Third Normal Form (3NF)

• To be in Third Normal Form (3NF) the relation
  must be in 2NF and no transitive dependencies may
  exist within the relation.
• A transitive dependency is when an attribute is
  indirectly functionally dependent on the key
  (that is, the dependency is through another
  nonkey attribute)

20
Third Normal Form (3NF) contd

• Codds original definition of 3NF cannot handle
  a relation that
• has multiple candidate keys, where those
  candidate keys are composite, and the keys
  overlap.
• So new stronger definition of 3NF known as
  Boyce-Codd Normal Form developed.

21
Boyce-Codd Normal Form (BCNF)

• To be in Boyce-Codd Normal Form (BCNF) the
  relation must be in 3NF and every determinant
  must be a candidate key.

22
Example

• 3NF
• FD Tutor? Subj
• Student No Subj Tutor

• BCNF

23
Fourth Normal Form (4NF)(Fagin 1977)

• To be in Fourth Normal Form (4NF) the relation
  must be in BCNF and the relation may not contain
  multi-valued(M-V) dependencies.
• Consider a student relation showing the
  relationship between student, subject studied and
  the sport they do.

24
Fourth Normal Form (4NF) contd

• Student relation
• Key(Student no,Subject,Sport)
• M-V DStudent no ? ? Subject
• Student no ? ? Sport
• Student no Subject Sport

• Student no Subject Sport

25
Elimination of M-V Dependencies

• Student-subject relation
• Key (Student no,Subject)
• Student no Subject

• Student-Sport relation
• Key (Student no,Sport)
• Student no Sport

26
Fifth Normal Form (5NF)

• 5NF has no join dependency.
• (aka Project-Join Normal Form(PJNF))
• Nonloss(Lossless)-Join Dependency
• A relation can be decomposed into n relations,
  the resulting relations have the nonloss-join
  property meaning we can rejoin the resulting
  relations to produce the original relation
  without the creation of spurious tuples.

27
Fifth Normal Form (5NF) contd

• Property_Item_Supplier relation
• Property P10 requires a washer,
• Supplier S2 supplies property P10,
• Supplier S2 provides a washer,
• So Supplier S2 can provide a washer for Property
  P10.
• This illustrates the cyclical nature of the
  constraint. If this holds then the tuple
  (P10,Washer,S2) must exist.
• The relation contains a join dependency

• Prop no Item Supplier no

28
Fifth Normal Form (5NF) contd
Remove the join dependency R1 R2
R3 Prop Item Item Supplier
Prop Supplier
29
Fifth Normal Form (5NF) contd

• Join R1 and R2

? spurious tuple
Now join this with R3 to eliminate the spurious
tuple and produce the original relation.
30
Summary

• Advanced Relational Design
• The Relational model
• Functional Dependency and Keys
• Transitive Dependency
• Normalisation 1NF-5NF
• Nonloss Decomposition
• Multi-Value Dependency
• Nonloss-Join Dependency

31
Further Reading

• Date, Database Systems 7th edition chpt
  10,11,12, p330-404
• Connolly Begg, Database Systems 3rd edition
  chpt 13
• Connolly Begg, Database Systems 4th edition
  chpts 13 14
• Kroenke, Database Processing 8th edition - chpt 5
• Reference Date, Connolly Begg, Kroenke as above