1 / 42

Relationships in Detail

Relationships in Detail. Overview. Relationships Ten different relationship types Nontransferability Relationships that seem to have attributes Rules of Normalization. Establishing a Relationship. Determine the existence of a relationship

brinly
Download Presentation

Relationships in Detail

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. Relationships in Detail

  2. Overview • Relationships • Ten different relationship types • Nontransferability • Relationships that seem to have attributes • Rules of Normalization

  3. Establishing a Relationship • Determine the existence of a relationship • Choose a name for the relationship from both perspectives • Determine optionality • Determine degree • Determine nontransferability

  4. Establishing the Relationship USER MESSAGE sending receiving replying

  5. Relationship Names sender of USER MESSAGE sent by sent to receiver of reply of replied to by

  6. Naming the Relationship receiving received by USER MESSAGE receiver of A MESSAGE is received by a USER A USER is receiver of a MESSAGE

  7. Optionality author of USER MESSAGE written by received by receiver of reply of replied to by

  8. Optionality No: Yes: received by USER MESSAGE receiver of Yes • Must every MESSAGE be received by a USER? No • Must every USER be receiver of a MESSAGE?

  9. B A Mandatory 1: Mandatory m split into part of • Every A must be split into at least one B • Every B must be partof exactly one A

  10. Degree author of USER MESSAGE written by received by receiver of reply of replied to by containing <5 with ATTACHMENT

  11. Degree One: One or more: received by USER MESSAGE receiver of • Can a MESSAGE be received by more than one USER? Yes • Can a USER be the receiver of more than one MESSAGE ? Yes

  12. Nontransferability FOLDER containing filed in author of USER MESSAGE written by received by receiver of reply of replied to by

  13. Relationship Types1:m (a) (b) (c) (d)

  14. Relationship Typesm:1 PRODUCT part of BUNDLE consists of

  15. Relationship Typesm:m (e) (f) (g)

  16. Relationship Typesm:m USER part of LIST consists of

  17. Relationship Types1:1 (h) ( i) ( j )

  18. 1:1 Relationships Roles acting as PATIENT* Blood Type PERSON* Name role of acting as EMPLOYEE* Job role of

  19. 1:1 Relationships Process MESSAGE DRAFT basis for result of

  20. Redundant Relationships COUNTRY COUNTRY location of of location of of birth of located in located in TOWN TOWN hometown of hometown of living in living in born in living in PERSON PERSON

  21. Relationships and Attributes ATTACHMENT TYPE* Name of with ATTACHMENT* Type* Content ATTACHMENT* Content • An attribute can hide a relationship • Relationship can be “downgraded” to attribute

  22. Value control List of values Other relationships Attribute Compared to Relationship ATTACHMENT TYPE* Name of with ATTACHMENT* Type* Content ATTACHMENT* Content • Easy model • Fewer tables • No join

  23. Attribute or Entity NAME JOB EMPLOYEE* Id SALARY BADGE GENDER NATIONALITY TEAM ADDRESS

  24. Attribute Compared to Relationship FOLDER* Name containing placed in MESSAGE* Message Id* Text* Folder Name • There is no such thing as a foreign key attribute • Usually, the attribute name should not contain an entity name

  25. Relationship Compared to Attribute MESSAGE addressed to USER addressee of MESSAGE* Addressee USER MESSAGEo Addressee USER addressed to addressee of

  26. m:m Relationships May Hide Something PRODUCT * Code * Name CUSTOMER* Id * Name buyer of bought by

  27. Quantity Is Attribute of ... ? PRODUCT CUSTOMER buyer of * Id * Name * Code * Name bought by Quantity ? PRODUCT CUSTOMER buyer of * Id * Name * Code * Name bought by Quantity

  28. Attribute of Relationship ? PRODUCT CUSTOMER buyer of * Id * Name * Code * Name bought by Quantity

  29. New Entity ORDER CUSTOMER* Id* Name with ORDER of PRODUCT* Code* Name *Quantity Sold with for PRODUCTS ORDERS CUSTOMERS Quantity_sold 221 Ctr_id 1123 Pdt_code 2 3 2 Code 12 3 4 Name JeansShirtTie Id 1 2 3 4 Name SanchezLowitchYomita

  30. ? Quantity Multiple PRODUCTS for an ORDER CUSTOMER* Id* Name with ORDER* Id* Date of with PRODUCT* Code* Name for

  31. Another New Entity: ORDER ITEM CUSTOMER* Id* Name with ORDER HEADER* Id* Date of PRODUCT* Code* Name with with for for ORDER ITEM *Quantity Sold

  32. Tables CUSTOMERS ORDER_HEADERS Id Name Date_ordered Id Ctr_id 1 2 SanchezLowitchYomita 1 2 1 2 25-MAY-1999 25-MAY-1999 25-MAY-1999 PRODUCTS ORDER_ITEMS Quantity_sold Code 12 3 4 Name JeansShirtTie Ohd_id Pdt_code 1 2 2 2 2 2 1

  33. Resolving m:m Relationship in A A of A/B COMBINATION xxx yyy B of B in • Create new intersection entity • Create two m:1 relationships, derive optionality • Remove m:m relationship

  34. A Resolving m:m Relationship with of A/B COMBINATION xxx yyy of B with • Create new intersection entity • Create two m:1 relationships, derive optionality • Remove m:m relationship

  35. Resolving m:1 Relationship external classifiedas PERSON CUSTOMER TYPE classification of internal

  36. Resolving m:1 Relationship external PERSON CUSTOMER TYPE with in for with CLASSIFICATION internal

  37. Normalization Rules Normal Form Rule Description First Normal Form All attributes are single valued. Second Normal Form (2NF) An attribute must be dependent upon entity’s entire unique identifier. Third Normal Form (3NF) No non-UID attribute can be dependent on another non-UID attribute. “A normalized entity-relationship data model automatically translates into a normalized relational database design” “Third normal form is the generally accepted goal for a database design that eliminated redundancy”

  38. First Normal Form in Data Modeling USER # Name * Person Name* Message Receive Dateo Message Subjecto MessageText All attributes must be single-valued. RECEIVED MESSAGE# Receive Date o Subject o Text USER # Name * Person Name received by receiver of

  39. Second Normal Form in Data Modeling An attribute must be dependent upon its entity’s entire unique identifier. RECEIVED MESSAGE # User Name * Receive Date * Subject MESSAGE# Id o Text including included in RECEIVED MESSAGE # User Name * Receive Date MESSAGE# Id o Text including included in * Subject

  40. No non-UID attribute can be dependent upon another non-UID attribute. Third Normal Form in Data Modeling USER# Name * Person Name * Password * Server Id * Server Name MAIL SERVER# Id * Name USER# Name * Person Name * Password assigned to distribute mail to

  41. Summary • Relationships express how entities are connected. • Initially relationships often seem to be of type m:m. • Finally relationships are most often of type m:1. • Relationships can be resolved into: • Two new relationships • One intersection entity • Third Normal form is generally accepted standard.

  42. Practices • Read the Relationship • Find a Context • Name the Intersection Entity • Receipt • Moonlight P&O • Price List • EMail • Holiday

More Related