1 / 21

Data Modeling

Data Modeling. Agenda. Conceptual Data Modeling Overview Components of an ERD Drawing an ERD Exercises. Data Requirements Discovery. Interviews Identify who to interview Executives? Managers? Staff Employees? Conduct Interviews What do you do? Business challenges

paley
Download Presentation

Data Modeling

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

  2. Agenda • Conceptual Data Modeling Overview • Components of an ERD • Drawing an ERD • Exercises

  3. Data Requirements Discovery • Interviews • Identify who to interview • Executives? • Managers? • Staff Employees? • Conduct Interviews • What do you do? • Business challenges • Definition of success • Information to be more effective in job… • Other discovery methods • Existing systems • Existing documents (e.g., applications, reservations, timesheets, contracts, catalogs, etc.) • Document & Prioritize Requirements

  4. Conceptual Data Model • Describes the business in terms of "things" it deals with • A "Logical" (i.e. business) model • Foundation for: • Promoting cross-functional "business understanding" • Organizing data that supports a business process • Defining scope of database

  5. Conceptual Data Modeling, cont… • Build conceptual data model using the Entity RelationshipDiagramming technique. • Steps: • Determine user’s information requirements • represent as Entities with Attributes • Determine business rules • represent as Relationships with Cardinalities

  6. Entity-Relationship Diagram(ERD) • Entities (types & instances) • Attributes • Primary Key • Multivalued attributes* • Relationships (unary, binary, ternary) • Cardinality (minimum & maximum) • Associative Entities • Generalization Hierarchy (supertypes & subtypes) * will not be left on our ERDs

  7. Sample E-R Diagram

  8. Entities • People, events, objects, assets, things • Entity Type • Collection or class of entities • Examples • Entity Instance • Single occurrence of entity • Examples • Entity Representation • Rectangular box • Name is singular noun, in CAPS Employee1, Gina Green,… Employee2, John Smith, … …

  9. Attributes • Characteristics that describe entities • Common to each instance of entity • Primary Key attribute (identifier): • Uniquely identifies an instance of an entity • CandidateKey • Atomic vs. Concatenated (or composite) Key • Avoid MVAs • Minimize derived attributes • Attribute Representation • Listed underneath entity name • Mixed case • Primary key typically underlined

  10. Primary Keys • Guidelines (not requirements) • Helpful if easy to write, read, hear, remember • Record time on 24-hour clock • Avoid using data that changes • Avoid letters • Surrogate vs. Natural Key

  11. Relationships • Business association or event linking one or more entities • Degrees • Unary (degree=1) • Binary (degree=2) • Ternary (degree=3) • Relationship Representation: • Line connecting related entities • Name of relationship on line • Name must be verb [phrase]

  12. Relationships, con’t... • Maximum Cardinality • Given oneinstance of an entity, what is the maximum number of instances of the other entity that it can be related to? • 1:1 • 1:M • M:M • Minimum Cardinality • … minimum number of instances… • mandatory (min = 1) • optional (min = 0) |

  13. Associative (Composite) Entities • When? • M:N Relationship • Attributes on Relationship • Associative Entity representation: • rectangular box (may have diamond inside box*) • name is singular noun, in CAPS • contains PKs of related entities (can be concatenated key) *diamond does not appear in Visio

  14. Generalization Hierarchy • Grouping common attributes into one entity; unique attributes/relationships into other entities • supertype • subtype • Supertype/Subtype Entity Representation • same as entities • supertype must have discriminator • subtypes must have pk of supertype • Supertype/Subtype Relationship Representation • hierarchy • no cardinalities • no relationship name

  15. Drawing an ERD • Strong entities • People, places, things, events important to the process • Weak entities • Depends on strong entity for existence • Often have composite PKs • Attributes • Important characteristics/properties of entities • Include primary key • Relationships between entities • Events, activities, or transactions that involve handling data related to several entities • Remove M:M relationships • i.e., associative entities • Generalization hierarchies, if any • Supertypes with discriminator • Subtypes with parent PK

  16. How do you start? • Common Business Entities • Customer • Product • Transaction • Employee • Supplier/Vendor • Physical Assets/Facilities • Money

  17. ERD Example Problem 1 • A company sells products to customers • Each customer sale is depicted as an order • Orders consist of one or more ordered products • Products may be contained on many orders or on no orders at all • Attributes?

  18. ERD Example Problem 2 • Dairy farmers and the cows they own are of interest. • Each farmer has a unique combination of first and last name. We also wish to record the county and state in which the farmer lives. • Cows have a uniquely numbered plastic ear tag. Data on cows that we wish to store include the cow’s breed and date of birth. • Most, but not all, farmers own at least one cow. • All cows belong to only a single farmer. • Cows can be special breed or non-special breed. We need to record pedigree info on special breed cows.

  19. Common ERD Mistakes

  20. More ERD Examples...

  21. Conceptual Data Modeling Review • Components of an ERD • Entities • Types vs. instances • attributes (including types of keys) • Relationships • degree • cardinality • Associatives/Composites • Supertypes/Subtypes • How to construct ERDs • symbols • naming conventions • Common Entities • Avoiding Common Mistakes

More Related