Data Modeling Seminar

1. Data ModelingSeminar February 18, 2012

2. Jamie Schultz • jschultz@bthreesolutions.com • Bob Elliott • relliott@bthreesolutions.com • Aaron Cuffman • acuffman@bthreesolutions.com • Andy Nagle • anagle@bthreesolutions.com • Adam Schultz • aschultz@bthreesolutions.com • Web Site • http://www.bthreesolutions.com

3. Objectives • Learn the fundamentals of building databases • Learn ERD Diagraming • Utilize Visio 2010 • Learn Normalization • Elimination of data redundancy • Review proper dos and don’ts • Learn basic SQL • Triggers, Store Procedures • Learn about Data Access Objects • Basics – Power tools • Eliminate SQL - Injection • Apply new knowledge by putting it all together

4. Procedures • Seminar will consist of 6 Lessons & 5 Labs • Labs are designed for individuals • Group work will be accommodated • Each Lab is self contained • If you are unable to complete a lab or partial seminar attendance occurs; labs can be completed on your own • Next lab will not require prior lab completion • Open Conversation • Object is to help you learn the material

5. Agenda • 9:00 – 9:30 Lesson 1: Introduction • 9:30 – 10:00 Lesson 2: Entity Relation Diagraming using Visio 2010 • 10:00 – 10:30 Lab 1 • 10:30 – 10:45 Break • 10:45 – 11:15 Lesson 3: Standard SQL • 11:15 – 11:45 Lab 2 • 11:45 – 12:30 Break • 12:30 – 1:00 Lesson 4: Normalization Rules for Data • 1:00 – 1:30 Lab 3 • 1:30 – 2:00 Lesson 5: Data Access Object Layering – Part 1 • 2:00 – 2:30 Lab 4 • 2:30 – 3:00 Lesson 6: Data Access Object Layering – Part 2 • 3:00 – 3:30 Lab 5 • 3:30 – 4:00 Wrap Up

6. Data ModelingSeminar February 18, 2012 Lesson 1 Fundamentals of Building Databases

7. Lesson 1 • Introduction • MySQL • By: Joshua Mostafa • Publisher: Virtual Training Company, Inc. • Pub. Date: July 25, 2002 • Print ISBN-10: 1-930519-74-5 • Data Modeling Part 1 • Data Modeling Part 2

8. Data Modeling • Real-world needs • Consultations, Analysis, Definition of Objects • Brainstorming • Diagram real world objects / elements • Use lines for relations • Scrappy process .. designed to flush out questions/ideas • Simple diagram to map of actions

9. Data Modeling • Entity Relationship Diagrams • Entities • Single item – real world entitles – translate to tables • Relationship - Lines connecting Entities • “Use a standard style” • Crow's Foot Notation • Crow's Foot diagrams represent entities as boxes, and relationships as lines between the boxes. • The ends of these lines are shaped to represent the cardinality of the relationship. • Bachman diagram • For every relation a rectangle has to be drawn and every coupling is illustrated by a line that connects the relations. • On the edge of each line arrows indicate the cardinality. We have 1-to-n, 1-to-1 and n-to-m. • The latter has to be avoided and must be replaced by two 1-to-n couplings.

10. ERD Notation Styles

11. Relationships • many to many … use bridge tables

12. Relationships • Optional • Denoted by the not required FK id • (FK1 DepartmentId is regular font)

13. Relationships • Required (Child has Parent) • Denoted by the required FK id • (FK1 StudentId is bold font)

14. Relationships • many-to-many • Use a bridge table

15. SQL Schema • ERD to SQL Scripts • Tables • Primary keys • Foreign Keys • Tools • Visio • Entity Framework • MySql Workbench

16. Questions