A Practical Introduction to Transactional Database Modeling and Design

1. A Practical Introduction to Transactional Database Modeling and Design Mike Burr

2. Database Design • Create a structure to model and store data about a part of the real world. Example of data: 35, “tall and skinny”, 9/16/1975. • Data is not useful without context. • The model (and resulting database) is used to generate information.

3. The Waterfall • Project Planning • Requirements Analysis • Design and Development • Implementation • Operation and Maintenance

4. Big Ideas… • Databases are rarely used in isolation from an application. • Most modern databases are implemented on multiuser relational database management systems where access can be granted at a granular level • One of the main goals of transactional database design is normalization (ensuring that data is stored efficiently and transactions are free from insert, update, and delete anomolies)

5. Basic Ideas • Entity: An object in the real world that we might want to collect information about. Examples are people, cars, loans, transactions, etc. • Relationship: A real world association between two or more entities to be captured in the database • Attribute: A characteristic of an entity that needs to be captured in the database

7. Basic Ideas • Primary Key: One or more attributes that uniquely identify an instance of an entity (or a row in a table) - Two Types: natural and artificial • Foreign Key: An attribute in an entity that references an attribute in another table. Usually used to enforce referential integrity.

9. Mapping from ER Concepts to Table Concepts • Entity -> Table • Attribute -> Column • Relationship -> Foreign Key Constraint or Trigger

10. A Couple of Example Tables

11. Basic Ideas • Actors are the particular people (accountants, shipping clerks, vendor*) or systems that will create or use data from the model • View (from the perspective of a data model): A collection of entities and relationships that a particular actor in a system is interested in. • View (from a SQL perspective): An artificial table created from joining one or more tables that can be used in other queries. More on this later with the discussion of SQL…

12. Two Important Notations Crow’s Foot Microsoft Access

13. Cardinality Zero or One Zero or more Exactly One One or more

14. General Design Procedure • Identify all of the relevant actors for the system • Determine the entities, attributes, and relationships from the perspective of each actor • Combine the views to form the model

15. Guidelines • Attributes may not have attributes of their own, this implies that entities may need to be created (1NF) • All attributes must depend on the primary key (2NF). If attributes are not dependent on primary key, then more entities may need to be created. • The primary key must identify the entire row and many-to-many relationships must be resolved to maintain data integrity.

16. Example: Accounts Payable

17. Store Clerk Logon and PO Validation(First Guess)

18. Problems with First Guess • Impossible to know how many of each item is included on PO • If price is included in PO, then prices cannot be itemized on an invoice • If price is included in Item, then any change to the price will result in a change to all POs, past, present, and future. • Quantities cannot be tracked • Root issue is known as a “many to many relationship” • Prices and quantities are not attributes of the PO nor the item (3NF violation) Problem:

19. Second Attempt After resolving many-to-many

20. References • [DMD] Toby Teorey, Sam Lightstone, and Tom Nadeau. Database Modeling and Design, 4 ed. Elsevier. 2006. ISBN 978-0-12-685352-0