1 / 14

Data Modeling: It’s All About the Relationships

Data Modeling: It’s All About the Relationships. Presenter: Bert Scalzo, Oracle Domain Expert Bert.Scalzo@Quest.com AUDIO INFORMATION Toll free: Toll: Confirmation Code:. About the Author …. Domain Expert & Product Architect for Quest Software Oracle Background:

verna
Download Presentation

Data Modeling: It’s All About the Relationships

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. Data Modeling: It’s All About the Relationships Presenter: Bert Scalzo, Oracle Domain Expert Bert.Scalzo@Quest.com AUDIO INFORMATION Toll free: Toll: Confirmation Code:

  2. About the Author … Domain Expert & Product Architect for Quest Software Oracle Background: • Worked with Oracle databases for over two decades (starting with version 4) • Work history includes time at both “Oracle Education” and “Oracle Consulting” Academic Background: • Several Oracle Masters certifications • BS, MS and PhD in Computer Science • MBA (general business) • Several insurance industry designations Key Interests: • Data Modeling • Database Benchmarking • Database Tuning & Optimization • "Star Schema" Data Warehouses • Oracle on Linux – and specifically: RAC on Linux • Articles for: • Oracle’s Technology Network (OTN) • Oracle Magazine, • Oracle Informant • PC Week (eWeek) • Articles for: • Dell Power Solutions Magazine • The Linux Journal • www.linux.com • www.orafaq.com

  3. Books by Author … Coming in 2008 …

  4. Agenda • Purpose • Identify the primary and secondary goals of a data modeling • Speculate as to why data models so often miss primary goal • Propose data modeling tools can’t make up for human error • Examine three well known yet often repeated relationship errors • Provide Best Practices to resolve these issues (i.e. avoid them) • Overview • No such things “Partial Foreign Keys” • Neither do “Incomplete Foreign Keys” • Unification – what is it and when to do • Conclusion & Questions/Answers

  5. Partial Foreign Keys Don’t Exist Q: Why can’t I make certain attributes of a unique identifier relate to the foreign key (FK) in a child any way I like? A: Because Relational Algebra, Relational Calculus, Codd, Date, and all that’s relational say “Heck No!!!” • Three fundamental FK requirements: • Relation R1 references R2 • R1’s FK attributes have exactly the same domain R2’s PK attributes • For each tuple (i.e. row) t1 in r(R1) and t2 in r(R2), either t1[FK] = t2[PK] (entirely),or t1[FK] is NULL

  6. Example #1 Identifying relationship lost Toad Data Modeler won’t permit this error 5

  7. Toad Data Modeler makes this less likely Example #2 Identifying relationship overuse

  8. Example #3 Identifying relationship misuse Toad Data Modeler can workaround error 7

  9. Incomplete Foreign Keys Do Not Exist • All or None • Complete PK as the FK (i.e., both columns) or supply the FK with just column A • Not referentially correct, but permitted since column B is optional • Looks Safe • Just as bad or actually worse • Can be legal: A+B, or NULL+NULL • Can be illegal: A+NULL or B+NULL

  10. Incomplete Foreign Keys Do Not Exist Toad Data Modeler will generate check constraint to be safe: constraint FK_COMPLETE check ((A is null and B is null) Or (A is not null and B is not null)) • ANSI SQL 92 permits and databases such as Oracle support alternative matching rules for composite foreign keys, including: • Match Full –Partially null foreign keys are not permitted. Either all components of the foreign key must be null, or the combination of values contained in the foreign key must appear as the primary or unique key value of a single row of the referenced table. • Match Partial – Partially null composite foreign keys are permitted. Either all components of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table. • Match None – Partially null composite foreign keys are permitted. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.

  11. Unification – not as scary as it sounds! Q: Why doesn’t Table_3 have columns A and B twice? A: We performed column level unification

  12. Unification – not as scary as it sounds! …Otherwise we would have had this – with A & B duplicates in TABLE_3.

  13. Let’s Test What We’ve Learned – Is This Model Okay? • FK1 and FK2 both share the column SSN • If employee has doctor’s report, but without associated accident, unifying FK1 and FK2 leads to incomplete FK (i.e., SSN without an accident date) • Left side of Relationship2 is optional – therefore, model is correct

  14. Thank you! Questions or Comments? • Three Part Data Modeling Series: • Data Modeling: Common Mistakes and Their Impact • Data Modeling: It’s All About the Relationships • Data Modeling: Reality Requires Supertypes and Subtypes • Toad Data Modeler v3.2 • Reasonable cost, easy to use data modeling tool for the rest of us … • Support for Oracle 11g and Access 2000-2003, import from SQL script, SQL code generation by SCHEMA, and much more! • http://www.quest.com/Toad-Data-Modeler

More Related