1 / 47

Lecture 3 :Database Analysis and Design (II)

Lecture 3 :Database Analysis and Design (II). ITN 170 MySQL Database Programming. Topics covering:. Identify and Model Entities Relationships. Entity definition Attribute definition ERD and ERM. Entities Definition. Entity

basil-bird
Download Presentation

Lecture 3 :Database Analysis and Design (II)

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. Lecture 3 :Database Analysis and Design (II) ITN 170 MySQL Database Programming ITN 170 - MySQL Database Programming

  2. Topics covering: • Identify and Model Entities • Relationships • Entity definition • Attribute definition • ERD and ERM ITN 170 - MySQL Database Programming

  3. Entities Definition • Entity An entity is something that can be identified in the user’s work environment; something that the users want to track. Example: Mary Doe, Peter Chen, etc (They are instructors at WTCC.) • Entity Classes (also called entity) a collection of the same type of entities. • Attribute Attributes, within an entity, describe the entity’s characteristics. • E-RM / E-RD Entity-relational diagram is a set of concepts and graphical symbols that can be used to create database. The E-R model was first published by Peter Chen in 1976. ITN 170 - MySQL Database Programming

  4. Identify and model entities from a set of interview notes. • Examine the nouns. Are they things of significance? • Name each entity. • Is there information of interest about the entity that the business needs to hold? • Is each instance of the entity uniquely identifiable? Which attribute or attributes could serve as its UID? • Write a description of it. “An EMPLOYEE has significance as a paid worker at the company. For example, John Brown and Mary Smith are EMPLOYEEs”. • Diagram each entity and all of its attributes. ITN 170 - MySQL Database Programming

  5. Example: Identify the entities in the following set of information “I’m the manager of a training company that provides instructor-led courses in management techniques. We teach many courses, each of which has a code, a name, and a fee. Introduction to UNIX and C Programming are two of our more popular courses. Courses vary in length from one day to four days. An instructor can teach several courses. Paul Rogers and Maria Gonzales are two of our best teachers. We track each instructor’s name and phone number. Each course is taught by only one instructor. We create a course and then line up an instructor. The students can take several courses over time, and many of them do this. Jamie Brown from AT&T took every course we offer! We track each student’s name and phone number. Some of our students and instructors do not give us their phone numbers.” ITN 170 - MySQL Database Programming

  6. Example Result: “I’m the manager of a training company that provides instructor-led courses in management techniques. We teach many courses, each of which has a code, a name, and a fee. Introduction to UNIX and C Programming are two of our more popular courses. Courses vary in length from one day to four days. An instructorcan teach several courses. Paul Rogers and Maria Gonzales are two of our best teachers. We track each instructor’s name and phone number. Each course is taught by only one instructor. We create a course and then line up an instructor. The studentscan take several courses over time, and many of them do this. Jamie Brown from AT&T took every course we offer! We track each student’s name and phone number. Some of our students and instructors do not give us their phone numbers.” ITN 170 - MySQL Database Programming

  7. Solution The following entities model the Training Company's information requirements. COURSE code name fee duration INSTRUCTOR (TEACHER) name phone number STUDENT name phone number ITN 170 - MySQL Database Programming

  8. Entity Descriptions • A COURSE has significance as a training event offered by the Training Company. For Example, Introduction to UNIX and C Programming. • A STUDENT has significance as a participant in one or more COURSEs. For example, Jamie Brown. • An INSTRUCTOR has significance as a teacher of one or more COURSEs. For example, Paul Rogers and Maria Gonzales. ITN 170 - MySQL Database Programming

  9. Relationships A relationship is a two-directional, significant association between two entities, or between an entity and itself (e.g. employee & manager). Relationship Syntax: { } { } Must be or may be relationship name One or more or one and only one each entity2 Each entity1 ITN 170 - MySQL Database Programming

  10. Relationships Example: The relationship between INSTRUCTER and COURSE is: Each COURSE may be taught by one and only one INSTRUCTOR Each INSTRUCTOR may be assigned to one or more COURSEs. ITN 170 - MySQL Database Programming

  11. Relationships • Each direction of relationship has: • Each direction of relationship has: • a name – e.g. taught by or assigned to. • an optionality – either must be or may be. • a degree – either one and only one, or one or more. • Quick Notes • Cardinality is a synonym for the term degree. • A degree of 0 is addressed by may be. ITN 170 - MySQL Database Programming

  12. Relationships • Diagramming Conventions • A line between two entities • Lower case relationship names • Optionality - - - - - - - - - - Optional (may be) ___________ Mandatory (must be) • Degree ___________ One and only one ___________ One or more ITN 170 - MySQL Database Programming

  13. Relationships Example: one many mandatory optional ITN 170 - MySQL Database Programming

  14. Relationships First read a relationship in one direction, and then read the relationship in the other direction: Example: Read the relationship between EMPLOYEE and DEPARTMENT. assigned to DEPARTMENT EMPLOYEE responsible for Read this relationship first from left to right, and then from right to left. ITN 170 - MySQL Database Programming

  15. Relationships Relationship from Left to Right (partial diagram) assigned to DEPARTMENT EMPLOYEE Each EMPLOYEE must be assigned to one and only one DEPARTMENT ITN 170 - MySQL Database Programming

  16. Relationships Relationship from Right to Left (partial diagram) DEPARTMENT EMPLOYEE responsible for Each DEPARTMENT may be responsible for one or more EMPLOYEEs. ITN 170 - MySQL Database Programming

  17. Relationships In class exercises: Exercise 1. Read the relationship between STUDENT and COURSE. enrolled in COURSE STUDENT taken by Write down the relationships. ITN 170 - MySQL Database Programming

  18. Relationships In class exercises: Exercise 1. Read the relationship between STUDENT and COURSE. enrolled in COURSE STUDENT taken by Each STUDENT must be enrolled in one or more COURSEs. Each COURSE may be taken by one or more STUDENTs. ITN 170 - MySQL Database Programming

  19. Relationships In class exercises: Exercise 2. Read the relationship between PAYCHECK and EMPLOYEE. for EMPLOYEE PAYCHECK the receiver of Write down the relationships. ITN 170 - MySQL Database Programming

  20. Relationships In class exercises: Exercise 2. Read the relationship between PAYCHECK and EMPLOYEE. for EMPLOYEE PAYCHECK the receiver of Each PAYCHECK must be for one and only one EMPLOYEE. Each EMPLOYEE may be the receiver of one or more PAYCHECKs. ITN 170 - MySQL Database Programming

  21. Relationships • There are three types of relationships: • Relationship Types • Many to One Relationships • Many to Many Relationships • One to One Relationship All relationships should represent the information requirements and rules of the business. ITN 170 - MySQL Database Programming

  22. Relationships A Many to One Relationship (M to 1 or M:1) has a degree of one or more in one direction and a degree of one and only one in the other direction. Quick Notes - M:1 relationships are very common. - M:1 relationships that are mandatory in both directions are rare. ITN 170 - MySQL Database Programming

  23. Relationships Example: visited by SALES REPRESENTATIVE CUSTOMER assigned to visit Each CUSTOMER must be visited by one and only one SALES REPRESENTATIVE. Each SALES REPRESENTATIVE may be assigned to visit one or more CUSTOMERs. ITN 170 - MySQL Database Programming

  24. Relationships A Many to Many Relationship (M to M or M:M) has a degree of one or more in both directions. Example 1: There is a M:M relationship between STUDENT and COURSE. enrolled in COURSE STUDENT taken by Each STUDENT must be enrolled in one or more COURSEs. Each COURSE may be taken by one or more STUDENTs. ITN 170 - MySQL Database Programming

  25. Relationships Example 2: There is a M:M relationship between EMPLOYEE and JOB. assigned to JOB EMLOYEE Carried out by Each EMPLOYEE may be assigned to one or more JOBs. Each JOB may be carried out by one or more EMPLOYEEs. ITN 170 - MySQL Database Programming

  26. Relationships - M:M relationships are very common. - Many to Many relationships that are usuallyoptional in both directions (e.g. EMPLOYEE vs JOB), (this implies that an instance of either entity can exist without an association with the other). - Many to Many Relationship may be optional in just one direction (e.g. STDUDENT vs COURSE). - Many to Many Relationships that are mandatory at both ends are very rare because each is fully dependent on the other for existence. ITN 170 - MySQL Database Programming

  27. Relationships A One to One Relationship (1 to 1 or 1:1) has a degree of one and only in both directions. Example: There is a 1:1 relationship between MICROCOMUTER and MOTHERBOARD. the host for MOTHERBOAD MICROCOMPUTER incorporated into Each MICROCOMPUTER must be the host for one and only one MOTHERBOARD. Each MOTHERBOARD may be incorporated into one and only one MICROCOMPUTER. ITN 170 - MySQL Database Programming

  28. COUPLE husband wife Relationships Quick Notes • 1:1 relationships are rare (in the real world, you usually do not see an example, but think about one) • Furthermore, A 1:1 Relationship that is mandatory in both directions is very rare (Think about an example) • Entities which seem to have a 1:1 relationship may really be the same entity. HUSBAND vs WIFE HUSBAND vs WIFE ITN 170 - MySQL Database Programming

  29. ORDER INVOICE Relationships Another Example: There is a 1:1 relationship between INVOICE and ORDER. ORDER INVOICE In this example, although the names order and invoice are different, the data contained in them is probably almost identical. ITN 170 - MySQL Database Programming

  30. Relationships Answer: ORDER number type ITEM number description issued for in originated by stored in the originator of the repository for WAREHOUSE id address CUSTOMER first name last name ITN 170 - MySQL Database Programming

  31. Follow a series of five steps to analyze and model relationships. • Determine the existence of a relationship. • Name each direction of the relationship. • Determine the optionality of each direction of the relationship. • Determine the degree of each direction of the relationship. • Read the relationship aloud to validate it. ITN 170 - MySQL Database Programming

  32. DETERMINE A RELATIONSHIP’S EXISTENCE Determine the existence of a relationship. Examine each pair of entities to determine if a relationship exists. Existence Name Optionality Degree Validate • Ask About a Relationship’s Existence • Does a significant relationship exist between ENTITY A and ENTITY B? ITN 170 - MySQL Database Programming

  33. DETERMINE A RELATIONSHIP’S EXISTENCE Existence Name Optionality Degree Validate • Consider the entities DEPARTMENT and EMPLOYEE. • Is there a significant relationship between DEPARTMENT and EMPLOYEE? • Yes, there is a significant relationship between DEPARTMENT and EMPLOYEE Example 1 ITN 170 - MySQL Database Programming

  34. DETERMINE A RELATIONSHIP’S EXISTENCE Existence Name Optionality Degree Validate • Consider the entities DEPARTMENT and ACTIVITY. • Is there a significant relationship between DEPARTMENT and ACTIVITY? • No, there is NOT a significant relationship between DEPARTMENT and ACTIVITY. Example 2 ITN 170 - MySQL Database Programming

  35. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate It is a good working practice to name every relationship in your Entity Relationship Model. In some methodologies, it is optional; and in others, it is mandatory. However, since every relationship represents a business rule and the name reflects that rule, it is sensible to try to name all relationships. ITN 170 - MySQL Database Programming

  36. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate The name that you give to the relationship is important since it reflects your understanding of the business rule that links the entities together. Without a name, a relationship may be misleading, and incorrect assumptions can easily be made. ITN 170 - MySQL Database Programming

  37. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate Name each direction of a relationship. • Ask a Relationship’s Name • How is an ENTITY A related to ENTITY B? An ENTITY A is relationship name in ENTITY B. • How is an ENTITY B related to an ENTITY A? An ENTITY B is relationship name an ENTITY A. ITN 170 - MySQL Database Programming

  38. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate • Consider the relationship between DEPARTMENT and EMPLOYEE. • How is a DEPARTMENT related to an EMPLOYEE? Each DEPARTMENT is responsible for an EMPLOYEE. • How is an EMPLOYEE related to a DEPARTMENT? Each EMPLOYEE is assigned to a DEPARTMENT. Example ITN 170 - MySQL Database Programming

  39. NAME THE RELATIONSHIP Use a list of relationship name pairs to assist in naming relationships. Existence Name Optionality Degree Validate • Useful Relationship Name Pairs • based on the basis for • bought from supplier of • description of for • operated by the operator for • represented by the representation of • responsible for the responsibility of Oracle’s suggestion: Do not use related to or associated with as relationship name ITN 170 - MySQL Database Programming

  40. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate Quick Note: You must not use words such as related to or associated with. The presence of the relationship indicates an association. The name must convey the business rule causing the association. Choose a name that is meaningful within the business being modeled. ITN 170 - MySQL Database Programming

  41. NAME THE RELATIONSHIP Existence Name Optionality Degree Validate Use a list of relationship name pairs to assist in naming relationships. • Useful Relationship Name Pairs • based on the basis for • bought from supplier of • description of for • operated by the operator for • represented by the representation of • responsible for the responsibility of ITN 170 - MySQL Database Programming

  42. DETERMINE THE RELATIONSHIP’S OPTIONALITY Determine the optionality of each direction of the relationship Existence Name Optionality Degree Validate • Ask About a Relationship’s Optionality • Must ENTITY A be relationship name ENTITY B? • Must ENTITY B be relationship name ENTITY A? ITN 170 - MySQL Database Programming

  43. DETERMINE THE RELATIONSHIP’S OPTIONALITY Existence Name Optionality Degree Validate Example • Consider the relationship between DEPARTMENT and EMPLOYEE • Must an EMPLOYEE be assigned to a DEPARTMENT? Always? Is there any situation in which an EMPLOYEE will not be assigned to a DEPARTMENT? • No, an EMPLOYEE must always be assigned to a DEPARTMENT. • Must a DEPARTMENT be responsible for an EMPLOYEE? • No, a DEPARTMENT does not have to be responsible for an EMPLOYEE. ITN 170 - MySQL Database Programming

  44. DETERMINE THE RELATIONSHIP’S DEGREE Determine the degree of the relationship in both directions Existence Name Optionality Degree Validate • Ask about a Relationship’s Degree • May ENTITY A be relationship name more than one ENTITY B? • May ENTITY B be relationship name more than one ENTITY A? ITN 170 - MySQL Database Programming

  45. DETERMINE THE RELATIONSHIP’S DEGREE Example Existence Name Optionality Degree Validate • Consider the relationship between DEPARTMENT and EMPLOYEE • May an EMPLOYEE be assigned to more than one DEPARTMENT? • No, an EMPLOYEE must be assigned to only one DEPARTMENT. • May a DEPARTMENT be responsible for more than one EMPLOYEE? • Yes, a DEAPRTMENT may be responsible for one or more EMPLOYEEs. ITN 170 - MySQL Database Programming

  46. DETERMINE THE RELATIONSHIP’S DEGREE Existence Name Optionality Degree Validate • Add the relationship degrees to the E-R Diagram. assigned to DEPARTMENT EMPLOYEE responsible for ITN 170 - MySQL Database Programming

  47. VALIDATE THE RELATIONSHIP Re-examine the E-R model and validate the relationship. Existence Name Optionality Degree Validate • Read the Relationship Aloud • Relationship must be readable and make business sense. assigned to DEPARTMENT EMPLOYEE responsible for Each EMPLOYEE must be assigned to one and only one DEPARTMENT Each DEPARTMENT may be responsible for one or more EMPLOYEEs. ITN 170 - MySQL Database Programming

More Related