1 / 45

Data Modeling 2

Data Modeling 2. Yong Choi School of Business CSUB. Entities???. Made up for the class…….ambiguous… Look for Noun but avoid noun w/o attributes And also avoid proper noun

alijah
Download Presentation

Data Modeling 2

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

  2. Entities??? • Made up for the class…….ambiguous… • Look for Noun but avoid noun w/o attributes • And also avoid proper noun ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost.

  3. Entities Project Chemist Equipment

  4. Entities’ Attributes??? ANG Laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost.

  5. Start-Date Proj# Project Phone# Emp# Chemist Serial# Equipment cost entities, attributes and identifiers

  6. Use Case Diagram Example: Placing Order • Start by listing a sequence of steps a user might take in order to complete an action.  For example a customer placing an order with a sales company might follow these steps.  • Browse catalog and select items. • Call sales representative. • Supply shipping information. • Supply payment information. • Receive conformation number from salesperson.

  7. Source: http://atlas.kennesaw.edu/~dbraun/csis4650/A&D/index.htm

  8. Example of Completed USD

  9. How to find relationships? • Relationship: • Association between entities • Two entities can have more than one type of relationship • look for a verb

  10. More about Relationship • Description of each relationship should be bidirectional. • Operate in both directions • Relationship between Student and Curriculum • A student is enrolled in many curriculums. • Each curriculum is being studied by many students.

  11. Relationships ??? ANG Laboratory has several chemists who work onone or more projects. Chemists also may use certain kinds of equipment on each project. The organization would like to store the chemist’s employee identification number, his/her name, up to three phone numbers, his/her project identification number and the date on which the project started. Every piece of equipment, the chemist uses, has a serial number and a cost.

  12. Entities/Relationships& their Attributes Start-Date Proj# Works-On Project Phone# Emp# Date-Assigned Chemist Uses Serial# Equipment cost Assign-Date

  13. Steps for creating an ERD • Identify the entities • look for nouns • Identify the attributes • look for entity characteristics • Identify the relationships • look for a verb between entities

  14. Degree of Relationship • Degree of a Relationship describes the number of entity participation • Unary (Recursive) Relationship: One instance related to another of the same entity type • Binary Relationship: Instances of two different entities related to each other • Ternary Relationship: Instances of three different types related to each other

  15. Relationships • Entities can be associated with one another in relationships. • Relationship degree defines the number of entity classes participating in the relationship: • Unary relationship. • binary relationship. • ternary relationship.

  16. Degree of Relationship …

  17. Unary (recursive) Relationship • It is possible for an entity to have a relationship to itself—this is called a recursive relationship.

  18. Binary Relationship

  19. Ternary Relationship

  20. Type of Relationships (Cardinality) • One – to – One (1:1) • Each instance in the relationship will have exactly one related member on the other side • One – to – Many (1:M) • A instance on one side of the relationship can have many related members on the other side, but a member on the other side will have a maximum of one related instance • Many – to – Many (M:N) • Instances on both sides of the relationship can have many related instances on the other side

  21. Optional Relationship • The Optionality is a property of an attribute which specify if a value is mandatory or optional.  • To identify optional relationship, look for auxiliary verb such as can or may

  22. Type of Relationship (Cardinality) The organization would like to store the date the chemist was assigned to the project and the date an equipment item was assigned to a particular chemist working on a particular project. A chemist must be assigned at least to one (or more) project and one (or more) equipment.Projects and equipments must be managed by only one chemist. A given project need not be assigned an equipment.

  23. Complete ER Diagram Start-Date Proj# Works-On 1 N Project Phone# Emp# Date-Assigned Chemist Uses Serial# 1 N Equipment cost Assign-Date

  24. Steps for developing an ERD 1 • User Interviews (group members) • Forms (competitor’s form) • Reports (competitors annual report) • Use Cases • Business Rules (create yours by bench marching competitors’ rules) based on business operation manual, procedure, and standard

  25. Steps for developing an ERD 2 • Identify the entities • Identify the attributes • Identify the relationships • Beginner: look for relationship-type related words and phrases such as zero, none, a, one, several, many….. • Optional relationship: look for auxiliary verbs such as may, might, can and based upon own judgment..) • Finalize business rules

  26. 1-to-1 relationship 1-to-M relationship M-to-N relationship Data Model Notation

  27. Data Model Notation

  28. weak entity relationship (see the next slide) optional relationship recursive relationship Data Model Notation Employee

  29. EMP DEP Weak Entity relationship • A weak entity is an entity that cannot be uniquely identified and existed by itself alone. • Thus, a weak entity is an entity that exists only if it is related to a set of uniquely determined entities (owners of the weak entity). • More examples on the textbook • Each employee might have none or multiple dependents. However, dependents must belong to at least one employee. weak entity notation

  30. 1:1 relationship A person must have one and only one DNA pattern and eac pattern must be applied to one and only one person.

  31. 1:1 with optional relationship (OR)on one side A person might not or might be a programmer, but a programmer must be a person.

  32. 1:M relationship Each department hires many employees, and each employee is hired by one department.

  33. 1:M with OR on many side A person might be a member or might not, but could be found multiple times (if the member entity represents membership in multiple clubs, for instance). A member must have only a single person.

  34. 1:M with OR on both side A person might have no phone, one phone or lots of phones, and that a phone might be un-owned or can only be owned by a person.

  35. M:N relationship Each student takes many classes, and a class must be taken by many students. STUDENT CLASS IS_TAKEN_BY TAKE **Many-to-many relationships cannot allowed in the data model because they cannot be represented by the relational model (see the next slide for the reason) **

  36. Example M:N Relationship Table to represent Entity 3 to 3 30 to 30 300 to 300 3000 to 3000 30,000 to 30,000 300, 000 to 300, 000

  37. Example of M:N Many-to-many relationships is a second sign of complex data. When x relates to many y's and y relates to many x's, it is a many-to-many relationship. In our example schema,a color swatch can relate to many types of sweaters and a type of sweater can have many color swatches. 

  38. CLASS ENROLL STUDENT Transformation of M:N • When transform to relational model, many redundancies can be generated. • The relational operations become very complex and are likely to cause system efficiency errors and output errors. • Break the M:N down into 1:N and N:1 relationships using bridge entity (weak entity).

  39. Converting M:N Relationship to Two 1:M Relationships Bridge Entity

  40. Bridge (Associative - textbook) Entity • ENROLL entitybecomes a weak entity of both STUDENT entity and CLASS entity • MUST have a composite (unique) identifier • STU_NUM (from STUDENTentity) and CLASS_CODE (from CLASSentity) • Another MUST know M:N example on the textbook page 63 and 64

  41. M:N with optionality on both side • A person might or might not work for an employer, but could certainly moonlight for multiple companies. An employer might have no employees, but could have any number of them. After broken down, optional relationship notation on both side of associative entity

  42. Student Recursive relationship • Each student is taught by aSTA (student teaching assistant). Each STA can teach several students. • A recursive relationship is an entity is associated with itself. teaches is taught by

  43. Data Modeling Errors • In general, there are two classes of E-R modeling errors that lead to normalization problems: • Incomplete data model error • Miss-modeled problem domain error • Read next two slides…

  44. In Complete Data Model • Occur in situations where the systems analysts is tasked to build a computer-based information system that is limited in scope. A key objective for successful information system project management is the definition of a limited, yet adequate project scope--a scope that enables the production of system deliverables within a reasonable time period. Limiting a project's scope often results in information systems that are based on limited data models. Limited information systems are fairly common throughout the IS world where dissimilar technologies prevent data sharing and work against the concept of a shared, enterprise-wide database.

  45. Miss-modeled problem domain error • The miss-modeled problem domain error is actually a class of errors including those that arise whenever systems analysts lack a complete understanding of the problem domain. These include errors such as depicting an attribute as single-valued when, in fact, the attribute is multi-valued, or depicting a single entity which includes attributes that should be assigned to two separate entities, or miss-modeling the connectivity or degree of a relationship.

More Related