1 / 42

Modeling Data In The Organization

Modeling Data In The Organization. Chapter 3. Objectives. Definition of terms Importance of data modeling Distinguish unary, binary, and ternary relationships Model different types of attributes, entities, relationships, and cardinalities Draw E-R diagrams for common business situations

adonai
Download Presentation

Modeling Data In The Organization

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. Modeling Data In The Organization Chapter 3

  2. Objectives • Definition of terms • Importance of data modeling • Distinguish unary, binary, and ternary relationships • Model different types of attributes, entities, relationships, and cardinalities • Draw E-R diagrams for common business situations • Convert many-to-many relationships to associative entities • Model time-dependent data using time stamps

  3. Entity-Relationship (ER) Model A logical data model that captures the structure and meaning of data • Entity • Strong, Weak, Associative • Attribute • Simple, Composite, Multivalued, Optional, Required, Derived, Identifier • Relationship • Unary, Binary, Ternary • Cardinality

  4. E-R Model: Entity • A person, place, object or concept in the user environment about which the organizations wishes to maintain data

  5. E-R Model: Entity • An entity has many possible instances each with distinguishing characteristics

  6. E-R Model: Entity • Warning: An Entity Type is NOT System Input, Output or User

  7. E-R Model: Entity (Example 1) • Identify the entities • A laboratory has several chemists who work on one or more projects. • Chemists also may use certain kinds of experiments on each project.

  8. E-R Model: Entity (Example 1) • Identify the nouns • A LABORATORY has several CHEMISTS who work on one or more projects. • Chemists also may use certain kinds of EXPERIMENTS on each PROJECT

  9. E-R Model: Entity (Example 1) • What kind of data do you wish to maintain? • A LABORATORY has several CHEMISTS who work on one or more projects. • Chemists also may use certain kinds of EXPERIMENTS on each PROJECT CHEMIST (Employee_ID, Name, Phone No) PROJECT (Project_ID, Start Date) EQUIPMENT (Serial No, Cost)

  10. E-R Model: Entity (Example 2) • Identify the entities • Create a database to represent a college sorority’s expense system. • The sorority treasurer manages accounts and records the expenses against each account in an expense report.

  11. E-R Model: Entity (Example 2) • Identify the nouns • Create a DATABASE to represent a college sorority’s expense SYSTEM. • The sorority TREASURER manages ACCOUNTS and records the EXPENSES against each account in an expense REPORT.

  12. E-R Model: Entity (Example 2) • What kind of data do you wish to maintain? • Create a DATABASE to represent a college sorority’s expense SYSTEM. • The sorority TREASURER manages ACCOUNTS and records the EXPENSES against each account in an expense REPORT ACCOUNT (Bank, Account #, Balance) EXPENSE (Id, date, person, amount, description)

  13. E-R Model: Entity (Example 2) • An Entity Type is NOT System Input, Output or User • Create a DATABASE to represent a college sorority’s expense SYSTEM. • The sorority TREASURER manages ACCOUNTS and records the EXPENSES against each account in an expense REPORT. TREASURER – System User EXPENSE REPORT - System Output

  14. E-R Model: Entity • Strong Entity -Exists independently of other entities • Weak Entity -Existences depends on another entity type Record the DEPENDENTS of each EMPLOYEE EMPLOYEE (Employee_id, name, address, salary) DEPENDENT (Employee_id, name, birthdate)

  15. weak E-R Model: Entity (Example 3) • Identify the strong and weak entities • A PATIENT is assigned a BED in a CARE-CENTER PATIENT( Number, Name) CARE_CENTER (Id, Name) BED (CareCenter_id, Room_No, Bed_No)

  16. E-R Model: Attribute • A property or characteristic of an entity type that is of interest to the organization

  17. E-R Model: Attribute (Example 1) • Identify the attributes of an employee • Janice is 5’3”with blond hair and blue-eyes. • Her employee id number is 123 and her home address is “PO Box 123, Main, GA”. • She started working for the company on Jan. 2, 1999. • She works in department 41, the finance department EMPLOYEE (Id, Name, Address, Start_date, Dept_Num)

  18. E-R Model: Attribute • Required or Optional (null) • Simple (Atomic) or Composite • Composite: Home Address = Address + City, State + Zip • Composite: Name = First Name + Last Name • Eventually all composite attributes must be simplified if you plan on using/sorting a part of a composite address, you • Stored or Derived • Stored: Date of Birth • Derived: Age • Do not record the derived attributes

  19. E-R Model: Attribute • Identifier (key) • An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type • Cannot be null or change value • Substitute new, simple keys for long, composite keys Employee (Employee ID, Employee Name, DOB) Flight (Flight Num, Date, Num_Passengers) Flight_ID

  20. E-R Model: Attribute • Single-Value or Multi-valued • Single: Date of Birth • Multi-value: Programming Languages (C++, Java, Visual Basic) • Eventually all multi-valued attributes need to be stored separately

  21. E-R Model: Attribute (Example 2) • Multi-valued attribute • Janice knows C++, C, Java and Visual Basic EMPLOYEE (Employee ID, Name, Skill) 123- Janice – (C++, Java, Visual Basic) EMPLOYEE (Employee ID, Name, Skill) 123- Janice - C++ 123- Janice - Java 123- Janice - Visual Basic

  22. E-R Model: Attribute (Example 2) • Normalize the multi-valued attribute • Janice knows C++, C, Java and Visual Basic EMPLOYEE (Employee ID, Name) 123- Janice EMPLOYEE_SKILL (Employee ID, Skill) 123 – C++ 123 – Java 123 – Visual Basic

  23. E-R Model: Associate Entity • Associates the instances of one or more entities and contains attributes that are peculiar to the relationship between those entities. • Needs identifiers of related entities EMPLOYEE_SKILL (Employee ID, Skill_ID) 123 – C++ 123 – Java 123 – Visual Basic

  24. E-R Model: Associative Entity (Example) EMPLOYEE_SKILL (Employee ID, Skill) 123 – C++ 123 – Java 123 – Visual Basic CERTIFICATE (Certificate#, Employee ID, Course ID, Date Completed) 1 – 123 - Java 101 - 06/30/1999 12 – 123 - C++ 101 - 06/30/2005

  25. E-R Model: Relationship • Meaningful association between entities • Relationships have a verb phrase

  26. E-R Model: Relationship (Example 1) • Identify the relationships • An Employee manages an Employee • A patient is assigned a bed • A student registers for a course • A vendor supplies a part to a warehouse

  27. E-R Model: Relationship (Example 1) • Identify the relationships • An Employee manages an Employee • A patient is assigned a bed • A student registers for a course • A vendor supplies a part to a warehouse

  28. E-R Model: Relationship • Degree of Relationship – number of entity types that participate in it • Unary (1) • EMPLOYEE manages and EMPLOYEE • Binary(2) • STUDENT registers for a COURSE • Ternary (3) • A VENDOR supplies a PART to a WAREHOUSE

  29. E-R Model: Cardinality of Relationships • Cardinality of Relationships • The number of entities associated with each instance in a relationship • One to One (1:1) • One to Many (1:M) • Many to Many (M:N)

  30. E-R Model: Cardinality of Relationships • One – to – One (1:1) • Each entity instance in the relationship will have exactly one related entity instance • An EMPLOYEE is assigned a PARKING SPACE • Each Employee is assigned ONE Parking space • Each Parking space is assigned to ONE Employee

  31. E-R Model: Cardinality of Relationships • One – to – Many (1:M) • An entity instance on one side of the relationship can have many related entity instances, but an entity instance on the other side will have a maximum of one related entity instance • A PRODUCT LINE contains a PRODUCT • Each PRODUCT is contained on ONE Product Line • Each Product Line contains MANY Products

  32. E-R Model: Cardinality of Relationships • Many – to – Many (M:N) • Entities instances on both sides of the relationship can have many related entity instances on the other side • A STUDENT registers for a COURSE • Each STUDENT registers for MANY Courses • Each COURSE has been registered by MANY Students

  33. E-R Model:Cardinality Constraints • Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity. • Minimum Cardinality • If zero, then optional • If one or more, then mandatory • Maximum Cardinality • The maximum number

  34. E-R Model: Cardinality Constraints • Mandatory One • Mandatory Many • Optional One • Optional Many

  35. Relationship Cardinality

  36. E-R Model: Cardinality Constraints (a) Mandatory cardinalities

  37. E-R Model: Cardinality Constraints (b) One optional, one mandatory cardinality

  38. E-R Diagram

  39. Relationship symbols Entity symbols Attribute symbols A special entity that is also a relationship Relationship degrees specify number of entity types involved Relationship cardinalities specify how many of each entity type is allowed

  40. Alternate E-R Model Notation

  41. A Comparison of ERD Symbols

  42. Microsoft Visio Notation for Pine Valley Furniture Different modeling software tools may have different notation for the same constructs

More Related