110 likes | 206 Vues
Learn how to develop data models aligned with business narratives, focusing on consistency and simplicity. Understand entity types, primary keys, relationships, and the iterative process of refining Entity-Relationship Diagrams (ERDs). Enhance your modeling skills efficiently by dividing entities, expanding types, and adding historical data. Ensure documentation to prevent common design errors for robust data modeling solutions.
E N D
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 • art not science
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.”
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
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”
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”
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
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
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