1 / 10

Developing Data Models

Developing Data Models. Joe Meehean. Business Requirements. Business Narrative How the company works, what do they do Consistency and Simplicity make ERD consistent with narrative make ERD simple Lots of experience required to do right a rt not science. Identifying Entity Types. Nouns

rhonda
Download Presentation

Developing Data Models

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. Developing Data Models Joe Meehean

  2. Business Requirements • Business Narrative • How the company works, what do they do • Consistency and Simplicity • make ERD consistent with narrative • make ERD simple • Lots of experience required to do right • art not science

  3. Identifying Entity Types • Nouns • Entity types contain additional describing sentences (usually) • properties of a noun are likely attributes • Attributes are also nouns • without additional descriptive sentences • e.g., “Students are assigned IDs and a graduation date; they must declare a major.”

  4. Primary Keys • Good primary keys are: • Stable • never change after assignment • addresses are not stable • a book’s ISBN are • Single purpose • attributed used only for identification • automatically generated by DBMS • SSN is a bad primary key • SSN also encodes place where SSN was issued • Privacy concern of primary key • private key may be printed on documents

  5. Relationships • Verbs that connect nouns • Cardinality • plural nouns indicate a “many” relationship’ • words like “collection” or “set” also indicate “many” • “optional” or “required” indicate min cardinality • “optional” = 0 • “required” = 1 • min cardinality should default to mandatory • e.g., “Students are required to enroll in one course per semester”

  6. Relationships • Direct or indirect relationships • direct: 2 entities connected by a relationship • indirect: 2 entities connected by other entities with relationships in-between • Can be difficult to tell how entities are related • Entities involved in many relationships can become a hub • connects many entities indirectly • often important documents • e.g., “order”, “registration”

  7. Refining ERDs • Process is iterative involves common behaviors • Attribute expansion • expand attribute into an entity • more detail provided for an attribute • replace attribute with an entity and 1-M relationship • e.g., JobTitle • Split Compound Attributes • compound attributes contain multiple kinds of data • split into smaller attributes • e.g., address => street, city, state, zip

  8. Refining ERDs • Expanding Entity Types • divide an entity into two entities and a relationship • record finer detail about an entity • e.g., JobTitle => JobTitle & HasJobTitle • can now record years at job title • Transform a weak entity into a strong entity • add a primary key • Add history • history stored by adding another entity • primary key constructed by combining primary key from original entity with version # • often includes beginning and ending dates • e.g., SupplierHistory

  9. Final Steps • Documentation • not really a final step, should be doing all along • problems • inconsistency or incompleteness • potential future problems • design decisions • e.g., “a supplier is still a supplier even if they don’t supply anything anymore” • Check for common design errors • fan trap • chasm trap

  10. Questions?

More Related