1 / 45

Data Modeling

Data Modeling. Lecture 2 I502. Outline. Understand nature of data Data Flow Data Models Why model?. Data for Decisions and Knowledge. The goal is not just storage of data but support for decision making and learning by key people and groups in the organization

niesha
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 Lecture 2 I502

  2. Outline • Understand nature of data • Data Flow • Data Models • Why model?

  3. Data for Decisions and Knowledge • The goal is not just storage of data but support for decision making and learning by key people and groups in the organization • DB designer must establish the object & info flow in the organization • Object & Info flow varies

  4. Object & Info Flow in Organization-OldBookReadersClub Business Office - Book Purchasing & Inventory Management Store Publishers Warehouse Customers

  5. Object & Info Flow in Organizations- Amazing.com (distributed approach) Book Purchasing & Collection Management Warehouse Maintenance & Inventory Online Presence & Sales Publishers & Distribution Centers Fulfillment (Seattle) Customers

  6. Object & Information Flow • Warehouse and inventory can use information about orders going out to publishers • Online presence department must have information on inventory • Online presence department can also use information about new book titles that have been ordered and will soon become part of the inventory

  7. Centralized Info Flow Periodic Management Report Amazing Sales & Acquisition Group record screens invoice & other data queries electronic payments inventory update Publishers Consumers shipping info Warehouse

  8. From Amazing Data to Amazing “Knowledge” • Simple dumping of data on the storage medium provides little value

  9. Amazing Sales Data CUSTOMER id name address country pay due 100 J. Rodney 12 High Rd., Leeds UK 33.75 523 E. Hoover 52 Ln. Muncie, IN USA 0 800 M. Old Box. 9, Miami, FL USA 12.50 PRODUCT Product_id title cost sale price 123-19-20 Joy of Living 12.50 19.25 169-15-34 Learning Judo 20.00 25.00 354-90-33 Your Dream Home 18.25 24.25

  10. More Data ... SALE id zone product_id quantity total price 100 UK 123-19-20 2 38.50 523 USMW 354-90-33 1 24.25 100 UK 169-15-34 1 25.00 800 USSE 123-19-20 1 19.25 MARKETINGCAMPAIGN DIR zone id contact UK M. Ashton USMW B. Miller USSE K. Baxter

  11. Multipurposing – toward better decisions and knowledge • The marketing department can use the sales data to look at broad trends

  12. Trends “Joy of Living had a sale of $3800.50 in the UK ZONE & Learning Judo had a sale of $2500.0” - Summarized fact or knowledge

  13. Path to Knowledge • To promote decision making and learning in an efficient manner requires: • Data Organization or Modeling

  14. Why Model? • To understand and identify essential data elements • To systematically (easily) produce a representation that can be transformed into a schema

  15. Modeling Model Level Typical Construct A conceptual model, e.g., E-R diagram A specific design methodology Schema containing tables, relationships and attributes Relational Model Names, addresses, position and salary of employees represented as fields in tables A segment of reality

  16. Conceptual Modeling • A methodology for ultimately producing database schemas for particular applications • The database itself is a model that provides a logical structure to capture relevant facts about a particular portion of reality • Example: First Internet Bank

  17. I2Bank Balance * Checking Account Key Has Chkg-Acct ID# Balance * * Customer * Has Sav-Acct Savings Account Human Customer Inst. Customer No. Employees Birthday Gender Org. Type

  18. Entity • Represents a homogeneous set of things that are important - can be concrete or general PERSON Object Set Name Instance person

  19. Generalization & Specialization • An object set that contains subsets or is a superset is called a generalization • An object set that is a subset of another object set is called a specialization

  20. Relationships • Association or link between two (or more) entities • Each instance has a corresponding instance e.g., assigned-to Managers assigned-to Stores

  21. Relationships - Aggregate • An aggregate - Viewing a relationship as an entity Married men is-married-to Marriedwomen Has anniversary Date

  22. Relationships - Cardinality • Number of instances of an object set that are related to a single instance in an associated entity (e.g., a single store is related to -> only one manager) 1 1 Managers assigned-to Stores

  23. Degree of Cardinality • Can be expressed in terms of maximum or minimum • Can also be expressed in terms of one-to-one, one-to-many and many-to-many (only accounting for maximum) 1,* 1,1 Supervisor manages Worker

  24. Cardinality – Many to Many * * Authors writer_of Books

  25. Attributes • Is an entity that has functional relationship with another entity • For every object instance there is only one instance present in the attribute entity (object set) LastName Last Name Has a Last Name 1 Person * Person For every person instance there is ONE Last name

  26. Attributes – the case of multiple values (multi-valued) ISBN Multi-valued Author Book A multi-valued attribute is not allowed – the entity must have a functional relationship with the attribute. The author attribute should be converted to a separate entity, and the book entity should be linked to the author entity as a many-many relationship.

  27. Dealing with Multiple Values (multi-valued) Author_Last Author_ID ISBN Author_First * * Book Author

  28. Attributes • A specialization inherits all attributes and relationships of the generalization e.g., human customer has chkg-account & ID# ID# Checking-acc Customer Gender HumanCustomer Organization

  29. Key • A key is part of a special attribute object set • A key attribute uniquely identifies an object instance • The key attribute object set always has one-one mapping with other object sets

  30. Key is a special attribute WID# BID# 1 1 1 1 Worker Building

  31. Composite Key WID# BID# 1 1 * * JOB Assignment A worker can be assigned multiple times … An assignment has multiple workers … A worker can only be assigned once to a building …

  32. Transformation • From entity to a relation • From entity without a key to a relation

  33. Entity & Attributes – Key present SS# Birthdate PERSON Relation: PERSON (SS#, BIRTHDATE)

  34. Entity & Attributes – No key Product# Amount SALE Relation without a key: SALE (AMOUNT, PRODUCT#) Relation with a key: SALE(SALE#, AMOUNT, PRODUCT#)

  35. Transformation • From specialization-generalization to relation

  36. Specialization & Generalization Name SS# Address PERSON Spouse U Relation1: PERSON(SS#, NAME, ADDRESS) Relation2: MP(SS#, NAME, ADDRESS, SPOUSE) Relation2 Re-defined: MP(SS#, SPOUSE) Foreign Key: SS# references PERSON MARRIED PERSON

  37. Transformation • From relationships to relations • one-one • one-many • many-many • From aggregate entity to relations

  38. Relationships Relation- One-One: CUSTOMER(CUSTOMER#) CHKG-ACC(CACC#, CUSTOMER#) An acc must have a customer Customer may or may not have chkg acc _________________________________ Relation- One-Many: Same as above 1,1 0,1 CHECKING ACCOUNT CUSTOMER HAS-zero or one-CHKG-ACC CHECKING ACCOUNT 1 * CUSTOMER NO JOINT ACC - BUT CUSTOMER CAN OWN MANY ACC

  39. Relationships • Relation- Many-Many • A customer can have many accounts and an account can be owned by multiple customers (joint accounts allowed) • CUSTOMER(CUSTOMER#) • CHKG-ACC(CACC#) • HAS-CHKG-ACC(CUSTOMER#, CACC#) • Need an intersecting relation. CHECKING ACCOUNT * * CUSTOMER

  40. Aggregates QUANTITY * * PROD# PRODUCT COUNTRY CCODE# Quantity of products sold in various countries Relations PRODUCT(PROD#) COUNTRY(CCODE#) IS-SOLD-IN(PROD#, CCODE#, QUANTITY) Foreign Keys: PRODUCT# references PRODUCT, COUNTRY-CODE# references COUNTRY

  41. A Generic Design Example: Next Construction Co. • Global view: Workers are assigned to buildings to work on various assignments. Workers and buildings are identified with unique identifiers. Workers can be assigned to more than one building and multiple workers can be assigned to a single building.

  42. A Generic Design Example: 10 Steps … • Step1) Establish entities • Step 2) Establish attributes and keys (if possible) • Step 3) Check entities • Step 4) Establish relationships • Step 5) Establish cardinalities • Step 6) Identify aggregates • Step 7) Establish attributes for aggregates • Step 8) Check relationships • Step 9) Check model for completeness • Step 10) Transform to schema

  43. AAA Construction - Conceptual Model worker_id bldg_id name hourly_rate address type Assignment * * 1 * worker building worker quality_level assigned_to supervises * status has_skill skill_type 1 skill bonus_rate start_date hours_per_week number_of_days

  44. Company - Schema • Worker(worker_id, name, hourly_rate, sup_id, skill_type) • Skill(skill_type, bonus_rate, hours_per_week) • Building(bldg_id, address, type, quality_level, status) • ??? Anything more?

  45. Company relations • Assignment(worker_id, building_id, start_date, number_of_days) • The junction table (based on the aggregate)

More Related