1 / 21

Conceptual Data Modeling

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

ariane
Download Presentation

Conceptual 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. Conceptual Data Modeling

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

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

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

  5. Data Modeling Documentation Formats

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

  7. Sample ERD (we will add attributes)

  8. ERDs: Entities and Attributes PROJECT Project_ID(PK) Due_Date Start_Date Title … • 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

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

  10. Project Task Relationships contains • 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]

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

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

  13. Project Personnel 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

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

  15. How do you start? • Common 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 • 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.

  18. ERD Example Problem 3

  19. More Examples…

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

  21. Next Time… • 2/4 *** Quiz 1*** • 2/4 Creating and Populating Databases • 2/11 *** Assignment 1 ERD DUE ***

More Related