1 / 17

Database Design 1

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.

kizzy
Download Presentation

Database Design 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design 1 CMS 476 Fall 1, 2007 Dr. Karl Horak, Instructor

  2. Session 5 • Week in Review • Application du Jour • Lecture: Normalization • Demonstration: Modeling, cont. • Exercises

  3. Week in Review • Cross-site scripting vulnerability • Visiting scholar returns home • Oh, yes, and grading

  4. Tonight’s Lecture Topic:Normalization • Method 1 • Method 2

  5. The Daisy Hill Puppy Farm

  6. Method 1—The Old Way • Eliminate Repeating Groups • Eliminate Redundant Data • Remove Items Not Dependent On Key

  7. 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

  8. 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

  9. 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

  10. Result PuppyTricks Tricks Puppies Kennels

  11. Method 2—New and Improved • Create Object-Role Model • Generate Entity-Relationship Diagram • Build database tables

  12. Object-Role Model

  13. Generate ER Diagram

  14. 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

  15. 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

  16. Demonstration • Back to VisioModeler • The Daisy Hill Puppy Farm Example http://70.56.215.209/khorak/CSF/SQLexercises.mdb

  17. Exercises and Q&A • Approaching a solution for HW #2 • Scoping the problem • Core objects and verbs

More Related