Transcript project P07

Project Group P07
Relational Database Schema Designer using
Bernstein’s Algorithm
CASE Tool Implementation
Huang Wei Ling
A0101200R
Kang Hong Jin
A0096846L
Liu Chengkai
A0101923U
Tong Chun Kit
A0096670W
Features
● Input and edit Functional Dependencies (FDs)
● Find closure of attributes
● Normal form tester
● Bernstein’s algorithm: output results of the intermediate steps with brief
explanation
● Remove the shortcomings of Bernstein’s algorithm using LTK normal form
Normal Form Tester
● Checks based on hierarchy
o BCNF → 3NF → 2NF → 1NF
● Requires preliminary sets
o
set of candidate keys, superkeys, prime attributes, non-prime attributes
● Checks based on definition
o
●
eg. if relation has no non-prime attributes, we conclude that it is in 2NF and check if it is in
the next higher normal form (3NF) so and so forth
Requires iteration through all functional dependencies
o
o
o
o
first, check if FD is trivial
2NF: check if RHS is non-prime, if yes, check if LHS is the whole key
3NF: check if RHS is non-prime, if yes, check if LHS is a superkey
BCNF: check if LHS is a superkey
Bernstein’s Algorithm
● Eliminate extraneous attributes
● Find minimal cover
● Partition
● Merge equivalent keys
● Eliminate Transitive Dependencies
● Construct relations
Shortcomings of Bernstein’s
Algorithm
● Bernstein’s algorithm does not guarantee reconstructibility or losslessness
○ Construct additional relation for missing attributes
● Bernstein’s algorithm does not find all the candidate keys
● Bernstein’s algorithm does not remove all the superfluous attributes
○ Use LTK normal form
LTK Normal Form
● Construct preparatory schema using Bernstein’s algorithm
● Create an all-key relation if the schema does not contain all the starting
attributes. This is to ensure reconstructability.
● For every attribute in each relation, check if it is superfluous with respect to
the entire schema.
o If it is superfluous, remove it from the relation.
● The final schema is in improved 3NF (LTK normal form)
DEMO
Q&A
Thank you!