data modeling
Download
Skip this Video
Download Presentation
Data Modeling

Loading in 2 Seconds...

play fullscreen
1 / 21

Data Modeling - PowerPoint PPT Presentation


  • 108 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Data Modeling' - paley


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda
Agenda
  • Conceptual Data Modeling Overview
  • Components of an ERD
  • Drawing an ERD
  • Exercises
data requirements discovery
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
conceptual data model
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
conceptual data modeling cont
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
entity relationship diagram erd
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

entities
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, …

attributes
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
primary keys
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
relationships
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]
relationships con t
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)

|

associative composite entities
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

generalization hierarchy
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
drawing an erd
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
how do you start
How do you start?
  • Common Business Entities
    • Customer
    • Product
    • Transaction
    • Employee
    • Supplier/Vendor
    • Physical Assets/Facilities
    • Money
erd example problem 1
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?
erd example problem 2
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.
conceptual data modeling review
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
ad