1 / 21

Data Modeling

Data Modeling. Chapters 2 and 3. Agenda. Conceptual Data Modeling Overview Components of an ERD Drawing an ERD Exercises. Conceptual Data Model. Describes the business in terms of "things" it deals with "Logical" (i.e. business) model Foundation for:

blenda
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 Chapters 2 and 3

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

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

  4. Conceptual Data Modeling, cont… • Build conceptual data model using the ____________ ____________ ______________ technique. • Steps: • Determine user’s info requirements • represent as __________ with _____________ • Determine business rules • represent as ____________ with____________

  5. Entity-Relationship Diagram(ERD) • Entities (types and instances) • Attributes • Primary Key • Multivalued attributes* • Relationships (unary/binary/ternary) • Cardinality (min and max) • Associative Entities • Generalization Hierarchy * will not be left on our ERDs

  6. Sample E-R Diagram (Figure 2-1)

  7. 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

  8. 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 (for this class) • Minimize derived attributes • Attribute Representation • listed underneath entity name • mixed case • primary key typically underlined

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

  10. 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]

  11. 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) |

  12. 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

  13. 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 contain ______________________ • subtype must contain ______________________ • Supertype/Subtype Relationship Representation • hierarchy • no cardinalities • no relationship name

  14. 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

  15. How do you start? • Common Business Entities

  16. 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?

  17. 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.

  18. Common ERD Mistakes

  19. More ERD Examples...

  20. 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

  21. Next Time… • 1/23Assignment 1: DB Development Due • 1/30 *** Quiz 1 ***

More Related