Effective Database Normalization Techniques for Optimal Design
This lecture covers essential database normalization methods to enhance design efficiency. It highlights the importance of eliminating repeating groups and redundant data, guiding students through real-world examples like the Daisy Hill Puppy Farm. The session offers two main normalization methods: the traditional approach and the improved Object-Role Model. Students will learn how to generate entity-relationship diagrams and build effective database tables. Additionally, the lecture discusses best practices in tackling database design challenges and offers practical exercises for hands-on experience.
Effective Database Normalization Techniques for Optimal Design
E N D
Presentation Transcript
Database Design 1 CMS 476 Fall 1, 2007 Dr. Karl Horak, Instructor
Session 5 • Week in Review • Application du Jour • Lecture: Normalization • Demonstration: Modeling, cont. • Exercises
Week in Review • Cross-site scripting vulnerability • Visiting scholar returns home • Oh, yes, and grading
Tonight’s Lecture Topic:Normalization • Method 1 • Method 2
Method 1—The Old Way • Eliminate Repeating Groups • Eliminate Redundant Data • Remove Items Not Dependent On Key
Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. How?Split the table into two tables: Puppy Table = Puppy Number+ Puppy Name+ Kennel Code+ Kennel Name+ Kennel LocationTrick Table = Puppy Number + Trick ID+ Trick Name+ Trick Where Learned+ Skill Level
Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it into a separate table. How?Split the Trick Table into two: Tricks and Puppy Tricks. Tricks = Trick ID+ Trick NamePuppy Tricks = Puppy Number + Trick ID+ Trick Where Learned+ Skill LevelPuppy Table = Puppy Number+ Puppy Name+ Kennel Code+ Kennel Name+ Kennel Location
Tricks = Trick ID+ Trick NamePuppy Tricks = Puppy Number + Trick ID+ Trick Where Learned+ Skill Level Puppies = Puppy Number+ Puppy Name+ Kennel Code Kennels = Kennel Code+ Kennel Name+ Kennel Location Remove Items Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. How?Split Puppy Table into two tables: Puppies and Kennels
Result PuppyTricks Tricks Puppies Kennels
Method 2—New and Improved • Create Object-Role Model • Generate Entity-Relationship Diagram • Build database tables
Your Choice • Method 1—Largely by inspection and following arcane rules. • Very difficult in complex situations • Easy to screw up • Method 2—Model the objects and their roles, then let the machine do it. • Requires additional software proficiency • Easy
Some More VM Tips • File | Preferences to change “freeform” to “guided” for default Fact Editor screen • Under Application tab, set to save before build • Look at the documentation—excellent PDFs with real-world samples and explanations
Demonstration • Back to VisioModeler • The Daisy Hill Puppy Farm Example http://70.56.215.209/khorak/CSF/SQLexercises.mdb
Exercises and Q&A • Approaching a solution for HW #2 • Scoping the problem • Core objects and verbs
