Day 4 - Relationships and Joins, and the Transactions a Model Expresses

📌 Key Takeaways

• A relationship in the model becomes an FK in the physical model and a join condition in SQL. If you look at the relationships, you can see the join paths.
• A hierarchical data model is a structure where an entity has a relationship with itself. In SQL it is read with a self join or a hierarchical query.
• A mutually exclusive relationship is a structure where one entity relates to only one of two entities (an arc relationship).
• A transaction unit that must be saved together is expressed in the model as a mandatory relationship (mandatory on both sides).

A Relationship Becomes a Join Condition

A model with its relationships drawn already tells you "which tables should be joined with which columns."

• When you split entities through normalization, relationships appear to the same extent, and those relationships are the joins at query time.
• A join is an operation that connects rows where the values of the related PK and FK are equal.

Translating one relationship into SQL looks like this.

SELECT D.DEPTNO, D.DNAME, COUNT(E.EMPNO) AS EMP_CNT, SUM(E.SAL) AS SAL_SUM
FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO
GROUP BY D.DEPTNO, D.DNAME ORDER BY D.DEPTNO;

    DEPTNO DNAME             EMP_CNT    SAL_SUM
---------- -------------- ---------- ----------
        10 ACCOUNTING              3       8750
        20 RESEARCH                5      10875
        30 SALES                   6       9400
        40 OPERATIONS              0

• Department 40 has no employees. In the model, this means "a department has zero or more employees," an optional relationship.
• To see all departments while preserving this optionality, you need an OUTER JOIN. With a regular join, department 40 would drop out of the result.
• In other words, the optional/mandatory notation of a relationship is the basis for deciding whether to use an INNER or an OUTER join.

Some Joins Have No Relationship

Not every join follows an FK. SALGRADE has no relationship line to EMP, but they are connected by salary range.

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE E.DEPTNO = 10 ORDER BY E.EMPNO;

ENAME             SAL      GRADE
---------- ---------- ----------
CLARK            2450          4
KING             5000          5
MILLER           1300          2

• A join that connects with a condition other than equality is called a Non-EQUI join. We cover it in detail in Week 4.
• What to note for Subject 1 is that "relationship = join path" is the default, and that joins are also possible without a relationship.

Hierarchical Data Model

MGR in EMP points to EMPNO of the same EMP. This is a structure where an entity has a relationship with itself.

           EMP
        ┌────────────┐
        │ EMPNO  PK  │◄──┐
        │ ENAME      │   │  Manager-subordinate relationship
        │ MGR    FK  │───┘  (self-referencing, recursive)
        └────────────┘

• Such a model is called a hierarchical data model, and such a relationship is called a recursive relationship (also called a self-referencing relationship).
• It is used when data of the same kind is linked up and down, such as an org chart, a bill of materials (BOM), or parent and child menus.
• The top-level row has no parent, so its FK value is NULL. In EMP, that is KING.

Reading the same table twice to attach the manager's name is a self join.

SELECT E.EMPNO, E.ENAME, E.MGR, M.ENAME AS MGR_NAME
FROM EMP E LEFT OUTER JOIN EMP M ON E.MGR = M.EMPNO
WHERE E.DEPTNO = 10 ORDER BY E.EMPNO;

     EMPNO ENAME             MGR MGR_NAME
---------- ---------- ---------- ----------
      7782 CLARK            7839 KING
      7839 KING
      7934 MILLER           7782 CLARK

• E plays the employee role and M plays the manager role. You give the same EMP two aliases and use them like different tables.
• KING has NULL in MGR, so a regular join would drop him. Here too, we kept him with an OUTER JOIN.
• To walk down several levels at once, use a START WITH … CONNECT BY hierarchical query. That is in Week 6's scope.

⚠️ Trap: A hierarchical data model is a structure where a parent-child relationship arises within a single entity. Choices saying "two entities form a hierarchy" or "a self join cannot be used in a hierarchical model" are wrong.

Mutually Exclusive Relationship

The party placing an order can be either an individual customer or a corporate customer. One order relates to only one of the two.

  Individual Customer ─┐
                       ├── (one of the two) ──< Order
  Corporate Customer  ─┘

• This is called a mutually exclusive (Exclusive OR) relationship, and depending on the notation, an arc relationship.
• In Barker notation, it is shown with an arc crossing the two relationship lines.
• There are several ways to carry it into the physical model. You can have one customer number column together with a "customer type" column, or have separate individual and corporate FKs and fill in only one.
• If you use a customer type column, which table you join with depends on that value at join time. That is why the SQL gets complicated.

Transactions Expressed by the Model

A transaction is a group of operations that must succeed together or fail together. What Subject 1 asks is how this grouping shows up in the model.

Take Order and Order Item as an example.

• An order must have at least one product. An order without products is meaningless.
• An order item also cannot exist alone without an order.
• So the order header and the order item rows must be saved together as one transaction.

  Order                Order Item
┌──────────┐        ┌────────────┐
│ OrderNo   │|──────<│ OrderNo     │   Mandatory on both sides (|)
│ OrderDate │        │ ProductNo   │   → Bundle the order INSERT and the order item INSERT
└──────────┘        │ Quantity    │     into one transaction
                    └────────────┘

• If you drew the relationship as mandatory, it is a signal that entering the two entities is one unit of work. The developer handles both INSERTs inside the same transaction and COMMITs them together.
• If you wrongly draw the relationship as optional, data can remain where the order went in but the order items are missing.
• Conversely, if you bind together things that the business saves separately as mandatory, you get an unnecessarily large transaction.
• The ACID properties of transactions and the COMMIT and ROLLBACK syntax are covered in Week 7 on TCL.

Tomorrow we cover how NULL behaves in operations and aggregations, and the difference between natural and surrogate identifiers.

📖 Glossary

• Join: An operation that connects rows of two tables based on the values of related columns.
• Join path: The sequence of tables and join conditions you must pass through to reach the data you want.
• Hierarchical data model: A model where a parent-child relationship arises within one entity. An org chart is the classic example.
• Recursive relationship: A relationship where an entity references itself. Also called a self-referencing relationship.
• Self join: Giving the same table two aliases and joining it with itself.
• Mutually exclusive relationship: A structure where one entity relates to only one of several entities. Also called an arc relationship.
• Transaction: A group of operations that must succeed together or fail together.
• Mandatory relationship: A relationship where an instance of the other entity must exist. The opposite of an optional relationship.