conceptual data modeling n.
Skip this Video
Loading SlideShow in 5 Seconds..
Conceptual Data Modeling PowerPoint Presentation
Download Presentation
Conceptual Data Modeling

Loading in 2 Seconds...

play fullscreen
1 / 21

Conceptual Data Modeling - PowerPoint PPT Presentation

  • Uploaded on

Conceptual Data Modeling. Agenda. Conceptual Data Modeling Overview Components of an ERD Drawing an ERD Exercises. What is a Conceptual Data Model?. Documents “what” data is required to support a specific task, business process, decision, etc… Graphical High-level

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

PowerPoint Slideshow about 'Conceptual Data Modeling' - ariane

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
  • Conceptual Data Modeling Overview
  • Components of an ERD
  • Drawing an ERD
  • Exercises
what is a conceptual data model
What is a Conceptual Data Model?
  • Documents “what” data is required to support a specific task, business process, decision, etc…
    • Graphical
    • High-level
    • Independent of implementation
data models logical vs physical
Data Models: Logical vs. Physical
  • Logical view of data is how the user sees the data
    • Very user-oriented; high user involvement
    • Not tied to a specific DBMS
    • Documented in a standard format like an ERD
  • Physical view of data deals with the design of data as stored on disk
    • Geared to database administrators
    • Tied to a specific DBMS
    • No uniform format for documenting
entity relationship diagram erd
Entity-Relationship Diagram(ERD)
  • Entities
    • Attributes
      • Primary Key
      • Foreign Key
      • Multivalued attributes*
  • Relationships
    • Degree (unary/binary/ternary)*
    • Cardinality (min and max)
      • Associative Entities
    • Generalization Hierarchy*

* will not cover


Sample ERD

(we will add attributes)

erds entities and attributes
ERDs: Entities and Attributes






  • Entity
    • something that you wish to track data about
    • Person, place, thing, or event
    • E.g., project, game, customer, donation, course, …
  • Attributes
    • describe the characteristics of an entity
    • E.g., due date, start date, title
  • Each entity must have a unique identifier
    • Typically called the primary key
    • E.g., Project ID
  • Entity Representation
    • rectangular box
    • name of entity is noun
    • attributes listed inside entity
primary keys
Primary Keys
  • Guidelines (not requirements)
    • Helpful if easy to write, read, hear, remember, BUT…
    • Avoid letters
    • Avoid using data that changes
    • Break long codes into chunks
    • Record time on 24-hour clock (any time field, not just if PK)
    • "Surrogate" keys





  • Business association or event linking one or more entities
    • Usually binary relationships, though unary and ternary relationships can and do exist in the business world
  • Relationship Representation:
    • line connecting related entities
    • name of relationship on line
    • name must be verb [phrase]
relationships cont
Relationships, cont...
  • 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
  • Optionality
    • Is the related record required?
      • Mandatory
      • Optional


relationships cont1
Relationships, cont…
  • Foreign Keys
    • Why?
      • Links two related records
      • Ensures a value entered in one record already exists in its related record
    • How?
      • Store the PK of one record as an (FK) attribute in the related record
      • BUT which record gets the FK???
        • 1:1
        • 1:M
associative entities



Associative Entities
  • When?
    • M:M Relationships
    • Attributes about Relationship
  • Associative Entity representation:
    • rectangular box (may have diamond inside box*)
    • name is noun
    • contains PKs of related entities
      • can together become a concatenated key
      • each is also a FK

*diamond does not appear in Visio

Project Personnel

drawing an erd
Drawing an ERD
  • > Entities
    • people, places, things, events important to the process
  • > Attributes of entities
    • important characteristics/properties of entities
    • identify your primary key
  • > Relationships between entities
    • events, activities, or transactions that involve handling data related to several entities
    • identify needed foreign keys
  • > Convert M:M relationships
    • create associative entities
how do you start
How do you start?
  • Common Entities
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
  • A local car dealership considers anyone who comes on their lot to be a customer, whether or not they actually purchase a car. If they do purchase one or more cars the dealership certainly wants to keep track of those purchases. In addition, the dealership would like to keep track of any service that the customer receives on each car purchased.
data modeling review
Data Modeling Review
  • Components of an ERD
    • Entities
      • What they represent
      • attributes
        • What they represent
        • PKs and FKs
          • Guidelines for PKs
    • Relationships
      • Cardinality
      • Optionality
    • Associatives
      • What they represent
  • How to construct ERDs
      • Common entities
      • Symbols
      • Naming conventions
next time
Next Time…
  • 2/4 *** Quiz 1***
  • 2/4 Creating and Populating Databases
  • 2/11 *** Assignment 1 ERD DUE ***