1 / 45

D111-Data Fundamentals

D111-Data Fundamentals. Week 3. Objectives. Identifying relevant entities Identifying the relationships Entity Matix.

ivy
Download Presentation

D111-Data Fundamentals

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. D111-Data Fundamentals Week 3

  2. Objectives • Identifying relevant entities • Identifying the relationships • Entity Matix

  3. "As a salesman, I'm responsible for 10 clients . Each of my clients makes an appointment to come into the showroom to view the merchandise we have to offer for the current season. Part of my job is to answer any questions they have about our merchandise and make recommendations regarding the most popular items . Once they make a decision on the merchandise they'd like to purchase, I write up a sales order for the client. Then I give the sales order to my assistant, who promptly fills the order and sends it to the client."

  4. The List • Salesman • Client • Appointment • Showroom • View • Merchandise • Season • Question • Recommendations • Item • Decision • Purchase • Sales Order • Assistant • Sends Order

  5. Review the List • Decide what are entities as opposed to attributes • Rule of thumb: • An entity should contain more than one attribute to describe it (other than its ID) otherwise its probably an attribute. E.g. Season? • Identify entities that have a different name but are a similar thing and eliminate one e.g. (Merchandise and Item or Purchase and Sales Order or Salesman and ). • Decide and keep only what are the important entities (points of interest to the business). • Look for items in the list that may be derived from the other entities e.g. Recommendations (Are they based on the result of the number of previously sold items?) • While Salesman and Assistant are types of employees and share similar attributes we will leave them as separate entities for now, just to keep it simple.

  6. Attribute • Definition - In general, an attribute is a property or characteristic of an entity: • Colour = an attribute of your Hair. • First name = an attribute of a Person • Sale Price = an attribute of a Sale Item

  7. Review the List Salesman Client Appointment Showroom View Merchandise Season Question Recommendations Item Decision Purchase Sales Order Assistant Sends Order • Salesman • Client • Appointment • Item • Sales Order • Assistant

  8. Create an Entity Matrix

  9. Remember that not all entities are directly related to each other

  10. Start from the top entity on the left side and intersect it with the left most entity on the topYou must examine the relationship between each one from the left sides point of view (Salesman/Salesman)

  11. You must examine the relationship between each one from the left sides point of view (Salesman/Client)

  12. You must examine the relationship between each one from the left sides point of view (Salesman/Appointment)

  13. Keep moving from the left side list: top to bottom – left to rightYou must examine the relationship between each one from the left sides point of view (Client/Salesman)

  14. How do I determine the type relationship? • Ask the following question: • could a single record in (name of first entity) be associated with one or more records in (name of second entity)? • If the answer is yes, we need to determine how many AND the context of the relationship (In what way are they related?). Is it: • Ownership Oriented or • Action Oriented

  15. Relationship Context Categories Two types of categories • Ownership-Oriented questions include words or phrases such as: • own, belongs to,has, partof, orcontain... • Action-Oriented questions include action verbs such as: • make, visit, teach, sell, attend...

  16. Ownership-Oriented Context own, belong to,has, have, partof, contain ... • Examples : • Can a single Salesman own one or more Salesmen? • Can a single Salesman belong to one or more Salesmen? Make sure it makes sense: • Can a single Salesman contain one or more Salesmen? • Can a single Salesman be managed by one or more Salesmen?

  17. Action-Oriented Context • make, visit, teach, sell, attend... • Examples : • Make sure it makes sense: • Can a single Salesman make one or more Salesmen? • Can a single Salesman sell one or more Salesmen? • What is the nature of the relationship according to the system required. For example: • Can a single Salesman visit one or more Salesmen? • Yes they can, but does it fit with the requirements? • Can a single Salesman manage one or more Salesmen?

  18. What type of relationship is it? • One to One (1:1) • One to Many (1:N) • Many to Many (N:N)

  19. Conceptual Stage • We are still at a CONCEPTUAL stage of the development process.

  20. Given the two SALESMAN entities, ask the question: • Can a singlesalesman be managed by one or moresalesmen? • If a relationship exists, enter the relationship name and relationship type (either a 1:1, 1:N or N:N) in that intersecting cell.

  21. A relationship between two entities must be read from both sides. • Can a singlesalesmanmanageone or more salesmen. • If yes, enter the relationship name and relationship type into the intersecting cell

  22. Remember that the examples given are subjective. The exact number will depend on each individual business.

  23. The Official Conceptual Relationship • Apply the appropriate formula to the two entries and identify the official relationship between the tables.

  24. 1:1 + 1:1 = 1:1 • A pair of tables bears a one-to-one relationship when: • a single record in the first entity can be related to only one record in the second entity, and.. • a single record in the second entity can be related to only one record in the first entity.

  25. 1:N + 1:1 = 1:N • A one-to-many relationship exists between a pair of entities when: • a single record in the first entity can be related to one or more records in the second entity, and.. • a single record in the second entity can be related to one record in the first entity.

  26. 1:N + 1:N = M:N • A pair of entities bears a many-to-many relationship when: • a single record in the first entity can be related to one or more records in the second entity and.. • a single record in the second entity can be related to one or more records in the first entity.

  27. Applying the formula • For the example between SALESMAN and SALESMAN the formula is: 1:1 + 1:N = 1:N One to Many

  28. Add to Conceptual ERD • Now enter the two entity/s into your ERD and apply the relationship. Notice the lack of exact numbers. manages SALESMAN managed by

  29. Cross out of Matrix

  30. You must examine the relationship between each one from the left sides point of view (Salesman/Client)

  31. Given the SALESMAN and CLIENT entities, ask the question: • Can a singlesalesmanhaveone or moreclients? • If a relationship exists, enter the relationship name and relationship type (either a 1:1, 1:N or N:N) in that intersecting cell.

  32. You must examine the relationship between each one from the left sides point of view (Salesman/Client)

  33. A relationship between two entities must be read from both sides. • Can a singleclientbelong to one or more salesmen. • If yes, enter the relationship name and relationship type into the intersecting cell

  34. Now from the other entities point of view (CLIENT/SALESMAN)

  35. Apply the official Conceptual Relationship formula

  36. Applying the formula • For the example between SALESMAN and CLEINT the formula is: 1:1 + 1:N = 1:N One to Many

  37. Add to Conceptual ERD manages SALESMAN managed by has belongs to CLIENT

  38. Cross out in Matrix

  39. Exercise • Complete the rest of the Matrix. Look for direct relationships among the following: • SALESMAN /APPOINTMENT • APPOINTMENT/ SALESMAN • SALESMAN /ITEM • ITEM/ SALESMAN • SALESMAN /SALES_ORDER • SALES_ORDER/ SALESMAN • SALESMAN /ASSISTANT • ASSISTANT/SALESMAN • CLIENT/APPOINTMENT • APPOINTMENT/CLIENT • ...

  40. Cross out in Matrix

  41. To be Cont..

More Related