1 / 16

Database Analysis and Data Modeling

Learn about ERD methodology, modeling concepts, attributes, relationships, cardinality, and exercises to master data modeling concepts.

phillipsr
Download Presentation

Database Analysis and 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. Database Analysis and Data Modeling Well done, is better than well said. ~ Benjamin Franklin ~

  2. SDLC / DBDLC IPlanning IIAnalysis IIIDesign IVImplementation VMaintenance &Support Resources Data Analysis and Conceptual Modeling LogicalModel Conceptual Model PhysicalModel Internal / ExternalModel Time Where are we?

  3. Chen –vs- Crow’s Foot Models Figure 3.6

  4. Attribute Examples Vehicle Unique Identifier (PK Candidate) VIN [U]Make [R] Model ManufYearAge [D]EngineDetail [C]Color [M] Required Derived Attribute (Calc from ManufYear) Composite Attribute(HP, NumCyls, CC) Multi-valued Attribute(White with Blue Trim)

  5. Example: Cardinality • 1-1 • 1-M • M-N Read in bothdirections!A resident possesses a DL or they don’t. A drivers license is possessed by one and only one Resident. A Resident registers zero or more Vehicles, a Vehicle is owned by one and only one resident. A Driver is Licensed to drive one or more vehicles, A vehicle is licensed to be driven by one or more drivers.

  6. Example: Relationship Strength • Identifying Relationship • Existence-Dependent • Composite Identifier (PK) • BBPlayer is weak entity “Player must be on a team” • Non-Identifying Relationship • Existence-Independent • BBPLayer is not weak “Player need not be on a team” Any relationship can be made Strong/WeakHow and Why Depend on the Requirements!

  7. Weak Entity • Existence-dependent on another entity • A Strong Relationship produces a weak entity • Has primary key that is partially or totally derived from parent entity Beveled Corners Figure 3.19

  8. Example: Composite Entity Attributes Employee may or may not participate How do we represent the position the employeeholds on the project committee? Employee may or may not participate

  9. Example: Relationship Degree Unary Binary Ternary FilmEvent is actually anassociative (weak) entitybecause it cannot existwithout the other three.

  10. Recursive Relationships • The relationship exists between occurrences of same entity set (unary relationships)

  11. Employee Empno Lname Fname DOB Address Hourly Salaried Rate AnnualPay StockOptns Entity Sub-Type / Super-Type • Two Distinctions: • d = disjoint • o=overlap • Helps Depict “Is a” “part of” relationships. d

  12. 3x5 Approach to ERD • Write Entity on 3x5 card • Write Attributes on back • Helps with entity placement issues in your diagram (esp. large ones)

  13. ERD Design Methodology • Identify Entities • Identify Attributes for each Entity • For each Attribute identify the Attribute Type[C],[M],[D],[R],[U] • Identify Relationships / Bus. Rules • Establish Cardinality 1-1,1-M,M-M • Establish Participation for each end 0/1 • Work Out Relationships • Sub-Type/Super-Type (is-a/has-a) • Establish Weak Entities in 1-M • Resolve M-M’s with Data (Assoc. / Comp. Entities)

  14. Exercises • Can you draw this diagram? • Can you establish these requirements and then draw the diagram?

  15. Entity-Relationship Modeling Questions?

More Related