How are one to one recursive relationships addressed using the relational model?

Entity to Relation Conversion

• For a majority of ER Models, entities and weak entities convert easily into relations.
• General steps:
  1. Entities – In general, each entity will be converted directly to a Relation.
  2. The attributes of the entity become the attributes of the Relation.
  3. The Identifier of the Entity becomes a Key of the Relation.
  4. Relationships will be mapped as “Foreign Keys”.
     There are a number of variations to this step that will be described in more details below.
• A simple example with one “One to Many” relationship:
  
  Inhaltsverzeichnis Show

  • Entity to Relation Conversion
  • Representing Relationships – One to Many
  • Representing Relationships – Many to Many
  • Recursive Relationships
  • How is a 1 m'self referencing relationship represented in a relational database?
  • Which of the following helps when representing a 1 1 relationship in a relational database design?
  • Which of the data model is suitable for one
  • What are the 3 types of relationships in a relational database?

  CUSTOMER (

  CustomerID

  , Name, Address, Phone)
  ORDER (

  OrderNumber

  , OrderDate, SalesPerson, CustomerID (fk))

• Weak Entities – If a weak entity is not ID Dependent, then treat it like any other entity – The Database Application must enforce referential integrity.

  If a weak entity is ID Dependent, then the parent relation’s key (Identifier) is copied into the dependent relation and made into a Composite Key.

  

  CUSTOMER (

  CustomerID

  , Name, Address, Phone)
  ORDER (

  OrderNumber

  , OrderDate, SalesPerson, CustomerID (fk))
  ORDERITEMS (

  OrderNumber

  ,

  ItemNumber

  , PartNumber, Quantity, Price)

  In the above example, in the ITEMS Relation: OrderNumber is the Foreign Key and OrderNumber plus ItemNumber is the Composite Key.
  In the ORDER Relation: OrderNumber is the Key.

• Important Note: After converting an ER model, the result should be one set of Relations. Each relation should only be included one time in the relational model.

Representing Relationships – One to Many

• 1:1 Relationships. The key or one relation is stored in the second relation.
  Look at example queries to determine which key is queried most often.
• 1:N Relationships.
  Parent – Relation on the “1” side.
  Child – Relation on the “Many” side.
• Represent each Entity as a relation.
  Copy the key of the parent into the child relation.
  

  CUSTOMER (

  CustomerID

  , Name, Address, Phone…)
  ORDER (

  OrderNumber

  , OrderDate, SalesPerson, CustomerID (fk))

Representing Relationships – Many to Many

• M:N Relationships. Many to Many relationships can not be directly implemented in relations.
• Solution: Introduce a new Intersection relation and copy keys from original two relations.
  Make up a name for this new relation by combining the names of the two original relations.

  SUPPLIER (SupplierID, FirmName, Address, …)
  PRODUCT (ProductID, Description, …)
  PRODUCT_SUPPLIER (SupplierID (fk), ProductID (fk))

• The Key for the PRODUCT_SUPPLIER relation is a Composite Key. Both SupplierID and ProductID are combined together to form the key.
• Note that this can also be shown in the ER diagram. Also, look for potential added attributes in the intersection relation.

Recursive Relationships

• Consider assembling an automobile – a collection of basic parts are combined together to make a complete car.
• In this case, a part or component might be made up of one or more other parts. This forms a recursive 1:N relationship.
  
• We can implement this directly by including another copy of the key in the relation.
  PART (Part_ID, Parent_Part_ID (fk), Description)
• Here are some example data:
  

  Part_ID	Parent_Part_ID	Description
  1000	null	Complete Car
  200	1000	Engine
  500	1000	Body
  510	500	Doors
  520	500	Hood
  530	500	Quarter Panels
  540	500	Trunk
  550	500	Roof
  512	510	Front Passenger Door
  514	510	Front Driver’s Door
  516	510	Back Passenger Door
  518	510	Back Driver’s Door etc.

• This forms a Tree structure:

        Complete Car                          1000
          Engine                               200
          Body                                 500
             Doors                             510
                 Front Passenger Door          512
                 Front Driver's Door           514
                 Back Passenger Door           516
                 Back Driver's Door            518
             Hood                              520
             Quarter Panels                    530
             Trunk                             540
             Roof                              550 
   
  

• null = No Value present

• Other kinds of recursive relationships:
  1:1 CUSTOMER is referred by one and only one other CUSTOMER
  N:M STUDENT tutors one or more other STUDENTS – Also, that STUDENT can be tutored by one more other STUDENTS.

  STUDENT (Student_ID, Name, Address, …)
  TUTOR_INTERSECTION ( Student_ID (fk), Tutored_by_ID (fk))

• Example Data:
  STUDENT

  Student ID	Name	Address
  101	B. Smith	1234 Smith St.
  202	A. Green	12 Grant St.
  303	D. Jones	98 Short St.
  404	P. Ewing	999 Tall St.

  TUTOR_INTERSECTION

  Student ID	Tutored by ID
  101	202
  404	202
  202	303
  404	303

Pages: 1 2 3 4