What You'll Learn This Week

• Converting E-R Model to Relational Model
  • Entity to Relation Conversion
  • Representing Relationships
  • Recursive Relationships
  • Ternary Relationships
  • IS-A Relationships
• Common E-R Structures
  • Tree Structures
  • Network Structures - Simple and Complex
• For Next Week

Connolly, Begg, Holowczak	Pratt/Adamski	Elmasri/Navathe (3rd) ed.	Kroenke (7th ed.)	McFadden (5th ed.)
Chapter 10	Chapter 6	Chapter 9	Chapter 6	Chapter 6 and Appendix B

Converting (Mapping) E-R Model to Relational Model

 E-R Model  ->  Relational Model  ->  Database 

• Previously, we covered modeling the user's view as an E-R diagram. Entities, Relationships, Attributes and Identifiers.
• We now need to convert this conceptual representation to a model that can be implemented directly in a database.

• Relational Model includes: Relations, Tuples, Attributes, keys and foreign keys.
  • Relation: A two dimensional table make up of tuples (This is a simple definition that we will define more rigorously in a later chapter).
  • Tuple: A row of data in a relation made up of one or more attributes.
  • Attribute: A characteristic of the relation contained in a tuple.

  The following are some vocabulary that are commonly used. Note the different terms used depending on the model being discussed.

  ER Model	Relational Model	Database	Traditional Programmer
  Entity	Relation	Table	File
  Entity Instance	Tuple	Row	Record
  Attribute	Attribute	Column	Field
  Identifier	Key	Key	Key (or link)

Note: The terms in the left most column under ER Model are not the four ER modeling constructs.

Entity to Relation Conversion

• For a majority of ER Models, entities and weak entities convert easily into relations.

• Entities - In general, each entity will be converted directly to a relation. The attributes of the entity become the attributes of the Relation.

  The Identifier of the Entity becomes a Key of the Relation.
  

  
  CUSTOMER (CustID, Name, Address, Phone)

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

  If weak entity is ID Dependent, then the parent relation's key (Identifier) is copied into the dependent relation. This is often referred to as a Foreign Key.
  

  
  ORDER (OrderNum, OrderDate, SalesP)
  ITEMS (OrderNum, ItemNum, PartNum, Qty, Cost)
  

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

Representing Relationships

• 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, ...)
  ORDER (OrderNum, OrderDate, SalesP, CustomerID)
  

• M:N Relationships. Many to Many relationships can not be directly implemented in relations.
• Solution: Introduce a third Intersection relation and copy keys from original two relations.
  

  
  SUPPLIER (SupplierID, FirmName, Address, ...)
  PRODUCT (ProductID, Description, ...)
  PRODUCT_SUPPLIER (SupplierID, ProductID)

• 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, 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, Tutored_by_ID)
  

• 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

Ternary Relationships

• For most situations, we can treat a ternary relationship as several binary relationships between the entities.

• If we can re-write the ternary relationship as several binary relationships, then the above steps can be used to convert the entities into relations.

• Example: ORDER, CUSTOMER, SALESPERSON becomes:
  ORDER (Order_Number, Order_Date, Customer_ID, Salesperson_Id)
  CUSTOMER (Customer_ID, Contact, Address...Salesperson_id)
  SALESPERSON (Salesperson_Id, Name, Department, Phone)

• Applications must control the assignments of salesperson to customer and ensure this pair of IDs is represented properly in any Orders.

IS-A Relationships

• Recall that some entities may have subtypes associated with them.

  
  

• Strategy is:
  1. Convert the supertype entity directly into a relation using only those attributes in the supertype - note the key.
  2. Convert each of the subtype entities into relations - also only with the attributes they contain.
  3. Copy the key for the supertype entity into each of the subtypes.

• So our example becomes:
  CALL (Call_Identifier, Customer_Id, Source_Number, Destination_Number, TimeOfDay)
  REGULAR_CALL (Call_Identifier, Duration)
  LONG_DISTANCE (Call_Identifier, Duration, LongDistanceCarrier)
  CELL_CALL (Call_Identifier, Air_Time, Land_Time)
  CUSTOMER (Customer_ID, Name, Address, ...)

Common E-R Structures

• There are many common structures you are likely to encounter. Some simple ones include:
  Classic 1 to Many, also called Master/Detail or Parent/Child:
  Purchase Order/Items
  Customer Order/Items
  Class Roster/Students
  Employee/Dependents
  Portfolio/Stocks
  Delivery Manifest/Items
  ...
  

• More complex structures include Trees, Bill of Materials and Networks.

Tree Structures

• Also called a Hierarchy
• A series of 1:N relationships.
  

  
  

• Each Node in the hierarchy has at most only one parent.
• Each Parent can have zero, one or more children.
• with a common parent are called siblings.
• In a Tree structure, each node has exactly one parent (except for the top or root node.
• Example: Books (fiction / Non Fiction), etc.
• Another example:
  

  
  

• To convert a tree structure into a set of relations, follow the guidelines for converting 1:N relationships, and repeat for each one.

Network Structure

• Two types: Simple Network and Complex Network
• Simple Network
  All relationships are 1:N, however, each node can have more than one parent.
• Consider:
  

  
  

• To convert a simple network into relations, follow the same rules for converting 1:N relationships. For an entity with two parents, this means that there will be two foreign keys (one from each parent) copied in.

  
  

• Complex Network
  Like simple network only one of the relationships can be M:N.

• Consider the following example:
  

  
  

• LINE_ITEM has two parents, INVOICE and PART. These have 1:N relationships.
  There is also a M:N relationship between PART and SUPPLIER. This makes it a Complex Network.

• To convert a complex network into relations, follow the same guidelines given for simple networks. For the M:N relationship, introduce an intersection relation as we did before.
  INVOICE (Invoice_Id, Invoice_Date, Due_Date...)
  LINE_ITEM (Invoice_Id, Part_Id, Line_Number, Qty, Price...)
  PART (Part_Id, Description...)
  SUPPLIER (Supplier_Id, Name, Address, ...) SUPPLIER_PART (Supplier_Id, Part_Id)