1 / 30

Data Modeling 1

Data Modeling 1. Yong Choi School of Business CSUB. Study Objectives. Understand concepts of data modeling and its purpose Learn how relationships between entities are defined and refined, and how such relationships are incorporated into the database design process

melvin-logan
Download Presentation

Data Modeling 1

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 1 Yong Choi School of Business CSUB

  2. Study Objectives • Understand concepts of data modeling and its purpose • Learn how relationships between entities are defined and refined, and how such relationships are incorporated into the database design process • Learn how ERD components affect database design and implementation • Learn how to interpret the modeling symbols

  3. Data Model and Data Modeling • Model: an abstraction of a real-world object or event • Useful in understanding complexities of the real-world environment • Data model • Relatively simple representations of complex real-world data structures • Data modeling is iterative and progressive process

  4. Data Model by Access

  5. Data Model by Peter Chen’ Notation (first - original)

  6. Data modeling • The data modeling revolves around discovering and analyzing organizational and users data requirements based on business rules. • Identify what data is important • Identify what data should be maintained • The major activity of this phase is identifying entities, attributes, and their relationships to construct model using the Entity Relationship Diagram.

  7. Data Model Basic Building Blocks • Entity : anything about which data are to be collected and stored • Attribute: a characteristic of an entity • Relationship: describes an association among entities • One-to-many (1:M) relationship • Many-to-many (M:N or M:M) relationship • One-to-one (1:1) relationship • Constraint: a restriction placed on the data

  8. The Importance of Data Model • Blue print: documentation • Facilitate interaction among the managers, the designer, and the end user • Effective Communication Tool • User involvement • Independence from a particular DBMS

  9. Business Rules • Descriptions of policies, procedures, or principles within a specific organization • Use for discovering and analyzing organizational and users data requirements for the data model • Use for describing characteristics of data • Allow designer to understand business processes • Allow designer to develop appropriate relationship participation rules and constraints

  10. Discovering Business Rules • Sources of business rules: • Top management (policy makers) and managers • Written documentation • Procedures • Standards • Operations manuals • Direct interviews with end users

  11. Translating Business Rules into Data Model Components • Nouns translate into entities • Verbs translate into relationships among entities • Relationships are bidirectional • Two questions to identify the relationship type: • How many instances of B are related to one instance of A? • How many instances of A are related to one instance of B? • Example relationship between student and class: • In how many classes can one student enroll? – many classes • How many students can enroll in one class? – many students

  12. Business Rules Example 1 • A professor can teach many classes and each class is taught by one professor. • A professor can advise many students and each student is advised by one professor.

  13. Business Rules Example 2 • Each sales representative writes many invoices and each invoice is written by one sales representative. • Each sales representative is assigned to many department and each department has one at most one sales representative. • Each customer can generate many invoices and each invoice is generated by one customer.

  14. Entity Relationship diagram (ERD) • Data modeling methodology • Developed by Peter Chen (1976). • Entity: anything about which data are to be collected and stored • Attribute - property or characteristic of interest of an entity (a field in a table) • Relationship – association between entities (corresponds to primary key-foreign key equivalencies in related tables)

  15. Entity “A fundamental THING of relevance to the enterprise about which data may be kept” • What should be an Entity: both tangible & intangible • An object that will have many instances in the database • An object that will be composed of multiple attributes • An object that we are trying to model

  16. Most popular ERD Notation (very minor differences with our textbook)

  17. Entity Instance Entity instance: a single occurrence of an entity. • 6 instances Entity: student instance

  18. Entity Instance (con’t)

  19. Entity Type and Entity Instances

  20. Attributes “Describe characteristics of an entity” • Entity: Employee • Attributes: • Employee-Name • Address (composite) • Phone Extension • Date-Of-Hire • Job-Skill-Code • Salary

  21. Classes of attributes • Simple attribute • Composite attribute • Derived attributes • Single-valued attribute • Multi-valued attribute

  22. Simple/Composite attribute • A simple attribute cannot be subdivided. • Examples: Age, Gender, and Marital status • A composite attribute can be further subdivided to yield additional attributes. • Examples: • ADDRESS -- Street, City, State, Zip • PHONE NUMBER -- Area code, Exchange number

  23. Derived attribute • is not physically stored within the database • instead, it is derived by using an algorithm. • Example: AGE can be derived from the date of birth and the current date. • MS Access: int(Date() – Emp_Dob)/365)

  24. Single-valued attribute • can have only a single (atomic) value. • Examples: • A person can have only one social security number. • A manufactured part can have only one serial number. • A single-valued attribute is not necessarily a simple attribute. • Part No: CA-08-02-189935 • Location: CA, Factory#:08, shift#: 02, part#: 189935

  25. Multi-valued attributes • can have many values. • Examples: • A person may have several college degrees. • A household may have several phones with different numbers • A car color

  26. Example - “Movie Database” • Entity: • Movie Star • Attributes: • SS#: “123-45-6789” (single-valued) • Cell Phone: “(661)123-4567, (661)234-5678” (multi-valued) • Name: “Harrison Ford” (composite) • Address: “123 Main Str., LA, CA” (composite) • Birthdate: “1-1-50” (simple) • Age: 50 (derived)

  27. How to find entities? • Entity: • A fundamental thingof relevance to the organization about which data may be kept • people, places, objects, events…. • Tangible: customer, product • Intangible: order, invoice • look for nouns (beginner) BUT a proper noun is not a good candidate….

  28. How to find attributes? • Attribute: • property or characteristic of an entity • A descriptor whose values are associated with individual entities of a specific entity type • look for characteristics of entity

  29. (unique) Identifier “attributes that uniquely identify entity instances” • Uniquely identify every instance of the entity • One or more of the entity’s attributes • Composite identifiers are identifiers that consist of two or more attributes • Identifiers are represented by underlying the name of the attribute(s) • Employee (Employee_ID), student (Student_ID)

  30. Practice • Try Practice 1 – 4 from Data Modeling Practice slide from week 6 – class website.

More Related