Developing Data Models

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?