Data Modeling

1. Data Modeling Data Masters

2. Special Thanks To… Planet Linux Caffe http://planetlinuxcaffe.com

3. Short bio about me… • Andrew Simkovsky • 15 years working with database technology • Worked across many industries • Why Data Masters? • Used to be a certification instructor – I miss it! • Sharing knowledge • Fostering a community

4. DBTekPro www.dbtekpro.com andrew@dbtekpro.com @asimkovsky

5. Data Modeling

6. Quick History of the Relational Data Model • Early Data Systems – were actually file processing systems • Challenges: • Data retrieval • Portability • Data redundancy • Modification anomalies

7. Data Anomalies Jane Doe, 1 Oak St, Riverside, MN, shirt, X3JDK1, small, 2 Jane Doe, 1 Oak St, Riverside, MN, pants, X3JDK7, size 4, 1 Jane Doe, 1 Oak St, Riverside, MN, socks, X3JDK4, small, 1

8. Advent of the RDBMS • Separate logical from physical • Preserves natural relationships between pieces of data • System is a running database engine • Standardized data format • Standardized data retrieval and manipulation language • Avoids modification anomalies • Database is self-describing

9. Components of a Data Model • Entities - real world objects you want to store data about • Attributes – details for those entities • Relationships – a real world connection between entities • Relationship Context (verb forms) – description of the relationship between entities • Cardinality - how many of each entity is on each side of the relationship

10. Example Data Model contains classroom desk is used in room number length width height number of doors number of windows desk type width height number of seats

11. Cardinality 1:1 1:M M:N Zero Cardinality

12. Example Data Model contains classroom desk is used in room number length width height number of doors number of windows desk type width height number of seats

13. Cardinality Notation 0 or 1 1 and only 1 1 or more 0, 1, or more

14. Zero Cardinality CLASSROOM DESK 1 M 1 0 1 1 0 1 1:M

15. Example Data Model contains classroom desk is used in room number length width height number of doors number of windows desk type width height number of seats

16. Constraints • Constraints are used to enforce rules • A relationship can be considered a rule • Cardinality can be considered a rule • Forcing a specific value or values can be a rule

17. Types of Constraints • Primary key – single unique identifier per row • Alternate key – an attribute that can be used as an alternative to the primary key • Foreign key – pointer from one specific instance of an entity to a specific instance of another entity • Check constraint – ensure that only specific values are allowed in an attribute

18. Example Data Model contains classroom desk is used in desk id classroom id room number length width height number of doors number of windows desk type width height number of seats classroom id

19. Mapping Logical Data Model to Physical Database Entities Tables Attributes Columns Relationships Constraints

20. Normalization DEFINITION Refining a data model to reduce data modification anomalies

21. Data Anomalies Jane Doe, 1 Oak St, Riverside, MN, shirt, X3JDK1, small, 2 Jane Doe, 1 Oak St, Riverside, MN, pants, X3JDK7, size 4, 1 Jane Doe, 1 Oak St, Riverside, MN, socks, X3JDK4, small, 1

22. The Normal Forms • Set of rules to follow, in sequence, to eliminate modification anomalies • Normal forms are nested • If you can prove your data model is in one normal form, then its automatically in all lower normal forms

23. The Normal Forms • 1st Normal Form (1NF) • 2nd Normal Form (2NF) • 3rd Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF) • Domain Key Normal Form (DKNF) • 6th Normal Form (6NF)

24. Data Normalization http://en.wikipedia.org/wiki/Database_normalization

25. The Normal Forms 1NF 2NF 3NF BCNF 4NF 5NF DKNF 6NF

26. Normalization Process • Iterative in nature • Normalize for data efficiency • Denormalize for database performance • Differences between types of databases • Exceptions to normalization rules

27. Questions