1 / 20

A Practical Introduction to Transactional Database Modeling and Design

A Practical Introduction to Transactional Database Modeling and Design. Mike Burr. 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.

inez
Download Presentation

A Practical Introduction to Transactional Database Modeling and Design

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

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

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

  8. A Couple of Example Tables

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

  10. Two Important Notations Crow’s Foot Microsoft Access

  11. Cardinality Zero or One Zero or more Exactly One One or more

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

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

  14. Example: Accounts Payable

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

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

  17. Second Attempt After resolving many-to-many

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

More Related