---
# System prepended metadata

title: DB Mid (04/17)
tags: [DB]

---

# DB Mid (04/17)
###### tags: `DB`

---

[TOC]

---

<style>
.markdown-body {
    min-width: 1200px;
}
u.dot {
    text-decoration-style: dotted;
}
</style>


## Useful Refs
- https://hackmd.io/@hami-duck/rk1YP49g9/%2FiU4PYRx4SPm-L_ZlJ-dh0w
    - MySQL 標準
- https://www.w3schools.com/sql/default.asp


## 考古 2021
### 1.a
```graphviz
graph PAST_2021_1A {
    label = "Past 2021 1.a\n\n";
    labelloc="t";
    fontsize=26;

    layout=dot;
    fontname="Sarasa Fixed TC, monospace";
    node [fontname="Sarasa Fixed TC, monospace"];
    edge [fontname="Sarasa Fixed TC, monospace"];
    
    //
    // Entities
    //
    {
        node [shape=box, fontsize=16, style=filled, fillcolor=lightgrey, ordering="out"];

        AIRPORT;
        FLIGHT;
        AIRPLANE;

        node [peripheries=2];

        FLIGHT_LEG;
        LEG_INSTANCE;
        FARES;
        SEAT_RESERVATION;
    };

    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];

        S_DEPARTS; S_ARRIVES;
        A_DEPARTS; A_ARRIVES;

        node [peripheries=2];

        HAS; INSTANCES; OFFERS; PRICES; USES;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        AIRPORT -- {
            AIRPORT_CODE [label=<<u>AIRPORT_CODE</u>>];
            NAME; CITY; STATE;
        };

        FLIGHT -- {
            FLIGHT_NUMBER [label=<<u>FLIGHT_NUMBER</u>>];
            AIRLINE; WEEKDAYS;
        };

        FLIGHT_LEG -- {
            LEG_NUMBER [label=<<u class="text-decoration-style: dotted;">LEG_NUMBER</u>>];
        };

        LEG_INSTANCE -- {
            DATE [label=<<u style="text-decoration-style: dotted;">DATE</u>>];
        };

        FARES -- {
            FARE_CODE [label=<<u style="text-decoration-style: dotted;">FARE_CODE</u>>];
            AMOUNT; RESTRICTIONS;
        };

        AIRPLANE -- {
            AIRPLANE_ID [label=<<u>AIRPLANE_ID</u>>];
            TOTAL_NUMBER_OF_SEATS;
        };

        SEAT_RESERVATION -- {
            SEAT_NUMBER [label=<<u style="text-decoration-style: dotted;">SEAT_NUMBER</u>>];
            CUSTOMER_NAME; CUSTOMER_PHONE;
        };

        S_DEPARTS -- SCHEDULE_DEPARTURE_TIME;
        S_ARRIVES -- SCHEDULE_ARRIVAL_TIME;
        A_DEPARTS -- DEPARTURE_TIME;
        A_ARRIVES -- ARRIVAL_TIME;
    };

    //
    // Participations
    //
    {
        edge [len=1.8];

        FLIGHT -- HAS [label="1"];
        HAS -- FLIGHT_LEG [label="N", color="black:invis:black"];

        FLIGHT_LEG -- INSTANCES [label="1"];
        INSTANCES -- LEG_INSTANCE [label="N", color="black:invis:black"];

        FLIGHT -- PRICES [label="1"];
        PRICES -- FARES [label="N", color="black:invis:black"];

        FLIGHT_LEG -- S_DEPARTS [label="N", color="black:invis:black"];
        S_DEPARTS -- AIRPORT [label="1"];
        FLIGHT_LEG -- S_ARRIVES [label="N", color="black:invis:black"];
        S_ARRIVES -- AIRPORT [label="1"];

        LEG_INSTANCE -- A_DEPARTS [label="N", color="black:invis:black"];
        A_DEPARTS -- AIRPORT [label="1"];
        LEG_INSTANCE -- A_ARRIVES [label="N", color="black:invis:black"];
        A_ARRIVES -- AIRPORT [label="1"];
        LEG_INSTANCE -- USES [label="N", color="black:invis:black"];
        USES -- AIRPLANE [label="1"];
        
        LEG_INSTANCE -- OFFERS [label="1"];
        OFFERS -- SEAT_RESERVATION [label="N", color="black:invis:black"];
    }

    //
    // Layout
    //
    {
        {rank=same; FLIGHT; PRICES; FARES;}
        {rank=same; LEG_INSTANCE; INSTANCES; FLIGHT_LEG; USES;}

        edge [style=invis];

        SCHEDULE_DEPARTURE_TIME -- AIRPORT;
        AIRPLANE_ID -- SEAT_RESERVATION;
    }
}
```
<!-- - Entity:
    - **`AIRPORT`**: <u>`AIRPORT_CODE`</u>, `NAME`, `CITY`, `STATE`
    - **`FLIGHT`**: <u>`FLIGHT_NUMBER`</u>, `AIRLINE`, `WEEKDAYS`
    - **[`FLIGHT_LEG`]**: (<u>`FLIGHT_NUMBER`</u>), <u class="dot">`LEG_NUMBER`</u>, (`DEPARTURE_AIRPORT_CODE`, `ARRIVAL_AIRPORT_CODE`), <`SCHEDULE_DEPARTURE_TIME`, `SCHEDULE_ARRIVAL_TIME`>
    - **[`LEG_INSTANCE`]**: (<u>`FLIGHT_NUMBER`</u>, <u>`LEG_NUMBER`</u>), <u class="dot">`DATE`</u>, (`AIRPLANE_ID`, `DEPARTURE_AIRPORT_CODE`, `ARRIVAL_AIRPORT_CODE`), <`DEPARTURE_TIME`, `ARRIVAL_TIME`>
    - **[`FARES`]**: (<u>`FLIGHT_NUMBER`</u>), <u class="dot">`FARE_CODE`</u>, `AMOUNT`, `RESTRCITIONS`
    - **`AIRPLANE`**: <u>`AIRPLANE_ID`</u>, `TOTAL_NUMBER_OF_SEATS`
    - **[`SEAT_RESERVATION`]**: (<u>`FLIGHT_NUMBER`</u>, <u>`LEG_NUMBER`</u>, <u>`DATE`</u>), <u class="dot">`SEAT_NUMBER`</u>, `CUSTOMER_NAME`, `CUSTOMER_PHONE` -->

### 1.b
https://www.w3schools.com/sql/sql_primarykey.asp
- composite foreign key
- escape from keywords
- null?

```sql=
-- TOO FUCKING LONG

CREATE TABLE AIRPORT
(
    AIRPORT_CODE INT NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    CITY VARCHAR(50) NOT NULL,
    STATE VARCHAR(50) NOT NULL,

    CONSTRAINT PK_AIRPORT
        PRIMARY KEY (AIRPORT_CODE)
);

CREATE TABLE FLIGHT
(
    FLIGHT_NUMBER INT NOT NULL,
    AIRLINE VARCHAR(50) NOT NULL,
    WEEKDAYS CHAR(7) NOT NULL,

    CONSTRAINT PK_FLIGHT
        PRIMARY KEY (FLIGHT_NUMBER)
);

CREATE TABLE FLIGHT_LEG
(
    FLIGHT_NUMBER INT NOT NULL,
    LEG_NUMBER INT NOT NULL,
    DEPARTURE_AIRPORT_CODE INT NOT NULL,
    SCHEDULE_DEPARTURE_TIME TIME NOT NULL,
    ARRIVAL_AIRPORT_CODE INT NOT NULL,
    SCHEDULE_ARRIVAL_TIME TIME NOT NULL,

    CONSTRAINT PK_FLIGHT_LEG
        PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER),
    CONSTRAINT FK_FLIGHT_LEG__FLIGHT
        FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT(FLIGHT_NUMBER),
    CONSTRAINT FK_FLIGHT_LEG__AIRPORT__DEPART
        FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE),
    CONSTRAINT FK_FLIGHT_LEG__AIRPORT__ARRIVE
        FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE)
);

CREATE TABLE LEG_INSTANCE
(
    FLIGHT_NUMBER INT NOT NULL,
    LEG_NUMBER INT NOT NULL,
    "DATE" DATE NOT NULL,
    AIRPLANE_ID INT NOT NULL,
    DEPARTURE_AIRPORT_CODE INT NOT NULL,
    DEPARTURE_TIME TIME NOT NULL,
    ARRIVAL_AIRPORT_CODE INT NOT NULL,
    ARRIVAL_TIME TIME NOT NULL,

    CONSTRAINT PK_LEG_INSTANCE
        PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE"),
    CONSTRAINT FK_LEG_INSTANCE__FLIGHT_LEG
        FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER) REFERENCES FLIGHT_LEG(FLIGHT_NUMBER, LEG_NUMBER),
    CONSTRAINT FK_LEG_INSTANCE__AIRPLANE
        FOREIGN KEY (AIRPLANE_ID) REFERENCES AIRPLANE(AIRPLANE_ID),
    CONSTRAINT FK_LEG_INSTANCE__AIRPORT__DEPART
        FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE),
    CONSTRAINT FK_LEG_INSTANCE__AIRPORT__ARRIVE
        FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE)
);

CREATE TABLE FARES
(
    FLIGHT_NUMBER INT NOT NULL,
    FARE_CODE INT NOT NULL,
    AMOUNT INT NOT NULL,
    RESTRICTIONS VARCHAR(8000) NOT NULL,

    CONSTRAINT PK_FARES
        PRIMARY KEY (FLIGHT_NUMBER, FARE_CODE),
    CONSTRAINT FK_FARES__FLIGHT
        FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT(FLIGHT_NUMBER),
);

CREATE TABLE AIRPLANE
(
    AIRPLANE_ID INT NOT NULL,
    TOTAL_NUMBER_OF_SEATS INT NOT NULL,

    CONSTRAINT PK_AIRPLANE
        PRIMARY KEY (AIRPLANE_ID)
);

CREATE TABLE SEAT_RESERVATION
(
    FLIGHT_NUMBER INT NOT NULL,
    LEG_NUMBER INT NOT NULL,
    "DATE" DATE NOT NULL,
    SEAT_NUMBER INT NOT NULL,
    CUSTOMER_NAME VARCHAR(50) NOT NULL,
    CUSTOMER_PHONE VARCHAR(50) NOT NULL,

    CONSTRAINT PK_SEAT_RESERVATION
        PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE", SEAT_NUMBER),
    CONSTRAINT FK_SEAT_RESERVATION__LEG_INSTANCE
        FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE") REFERENCES LEG_INSTANCE(FLIGHT_NUMBER, LEG_NUMBER, "DATE")
);
```

### 1.c
- =="For each ..." 要不要選出來？==
https://www.w3schools.com/sql/trysql.asp?filename=trysql_editor
==https://database.guide/5-ways-to-select-rows-with-the-maximum-value-for-their-group-in-sql/==
==https://stackoverflow.com/a/71548342==

```sql=
-- UGLY

SELECT FLIGHT_LEG.FLIGHT_NUMBER          AS "the flight number",
       FIRST_LEG.SCHEDULE_DEPARTURE_TIME AS "the departure time",
       LAST_LEG.SCHEDULE_ARRIVAL_TIME    AS "the arrival time"
  FROM FLIGHT_LEG AS FIRST_LEG
  JOIN FLIGHT_LEG AS LAST_LEG
    ON FIRST_LEG.FLIGHT_NUMBER = LAST_LEG.FLIGHT_NUMBER
 WHERE FIRST_LEG.LEG_NUMBER = 
       (SELECT MIN(LEG_NUMBER)
          FROM FLIGHT_LEG
         WHERE FLIGHT_LEG.FLIGHT_NUMBER = FIRST_LEG.FLIGHT_NUMBER)
   AND LAST_LEG.LEG_NUMBER = 
       (SELECT MAX(LEG_NUMBER)
          FROM FLIGHT_LEG
         WHERE FLIGHT_LEG.FLIGHT_NUMBER = LAST_LEG.FLIGHT_NUMBER);
```
<!--
SELECT FirstOrder.OrderID,
       FirstOrder.ProductID AS FirstProductID,
       FirstOrder.Quantity AS FirstQuantity,
       LastOrder.ProductID AS LastProductID,
       LastOrder.Quantity AS LastQuantity
  FROM OrderDetails AS FirstOrder
  JOIN OrderDetails AS LastOrder
    ON FirstOrder.OrderID = LastOrder.OrderID
 WHERE FirstOrder.ProductID = 
       (SELECT MIN(ProductID)
          FROM OrderDetails
         WHERE OrderDetails.OrderID = FirstOrder.OrderID)
   AND LastOrder.ProductID = 
       (SELECT MAX(ProductID)
          FROM OrderDetails
         WHERE OrderDetails.OrderID = LastOrder.OrderID);
-->

### 1.d
```sql=
SELECT LEG_NUMBER                                       AS "leg number",
       SUM(TOTAL_NUMBER_OF_SEATS) - SUM(RESERVED_SEATS) AS "the number of available seats"
  FROM LEG_INSTANCE
       NATURAL JOIN AIRPLANE
       NATURAL JOIN
       (SELECT FLIGHT_NUMBER, LEG_NUMBER, "DATE", COUNT(SEAT_NUMBER) AS RESERVED_SEATS
          FROM LEG_INSTANCE
               NATURAL LEFT JOIN SEAT_RESERVATION
         GROUP BY FLIGHT_NUMBER, LEG_NUMBER, "DATE")
 WHERE FLIGHT_NUMBER IN
       (SELECT FLIGHT_LEG.FLIGHT_NUMBER
          FROM FLIGHT_LEG
         GROUP BY FLIGHT_LEG.FLIGHT_NUMBER
        HAVING COUNT(LEG_NUMBER) >= 2)
 GROUP BY FLIGHT_NUMBER, LEG_NUMBER;
```

### 1.e
```sql=
-- i do not actually know how to calculate "average fares"
SELECT AVG(AMOUNT) / COUNT(DISTINCT LEG_NUMBER) AS "average Fares"
       COUNT(DISTINCT AIRPLANE_ID)              AS "number of different planes used in the flight"
  FROM LEG_INSTANCE
       -- NATURAL JOIN AIRPLANE
       NATURAL JOIN FARES
 GROUP BY FLIGHT_NUMBER;
```

### 2.a ==TODO==
> - R-A step: Managers and Clerks，這個步驟的主要任務是確認使用者的需求，避免開發出的成果不符合使用者的需求，因此會由負責監督專案的 managers 向使用資料庫的 clerks 詢問、確認 clerks 的需求，以便後續開發。
> - M-D step: Managers, Expert and Local IT，這個步驟如其名，也就是要開始規劃資料模型了，而資料模型的設計取決於用戶的需求與資料的特性，因此雖然 managers 沒有任何電腦科學的背景，但仍要參與此步驟以傳達其他 group 的成員最正確的用戶資訊；Local IT 身為開發與後續維護資料庫的主要 group，必須熟知資料庫的設計，故一定是必須參與設計環節的；最後則是來自諮詢公司的  experts，這邊的 experts 相較於 Local IT 應是資料庫領域的專家，他們參與本步驟的討論能夠即時地指出 local IT 可能構想出的模型設計不良或能再改進之處，良好的設計基礎對於開發與後續維護相當有幫助。
> - D-P step: Managers and Local IT，與上步驟類似，Managers 雖無電腦科學背景，但他們在 R-A step 時通過直面用戶，瞭解了用戶的需求與資料特性，故在此步驟需參與監督，以避免實際動手整理資料的 Local IT 破壞了資料；而 Local IT 如前面所述，是實際動手做事的人，故一定會參與此步驟。
> - T-C step: Managers, Clerks and Local IT，此步驟已是最後的測試與驗證步驟，clerks 身為實際使用資料庫的人，能夠參與本階段能較為有效的檢驗成品是否有問題，而在給予 clerks 測試前，Local IT 和 Managers 應會先進行內部測試。( 可能 Local IT 完成後自己測試一遍，自認沒問題後交由熟悉用戶需求的 Managers 再測試一遍，也沒問題才交付 Clerks 進行最後測試 )
> 
> [name=Cloudy]

### 2.b ==TODO==
> - R-A step, M-D step, D-P step。
> - R-A step 部分，若在此部分 managers 沒有正確理解 clerk 的需求，或是在此階段時 clerk 並沒有規劃好未來長遠的需求，就會使得後續的模型設計、資料前處理都受到嚴重的影響，雖然未來再針對需求更新資料庫理論上是可行的，但難度與成本都相當高，也可能造成後續維護困難，使得 DB 無法 work in the long run，故此步驟是關鍵步驟之一。
> - M-D step、D-P step 部分，類似於上步驟所述，不好或錯誤的模型設計或資料前處理對於未來的影響都相當大，而且此類的影響在未來若想修正，都必須付出相當高的代價，故對於資料庫開發來說，此二步驟也是相當關鍵的。
> 
> [name=Cloudy]


## 考古 2020
### 1.a
```graphviz
graph PAST_2020_1A {
    label = "Past 2020 1.a\n\n";
    labelloc="t";
    fontsize=26;

    layout=dot;
    fontname="Sarasa Fixed TC, monospace";
    node [fontname="Sarasa Fixed TC, monospace"];
    edge [fontname="Sarasa Fixed TC, monospace"];
    
    //
    // Entities
    //
    {
        node [shape=box, fontsize=16, style=filled, fillcolor=lightgrey, ordering="out"];

        Library_branch;
        Book;
        Borrower;

        node [peripheries=2];

        Book_copy;
        Loaning;
    };

    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];

        node [peripheries=2];

        HasVersion;Loans;
        IsLoanedTo;
        IsLoaned;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        Library_branch -- {
            branch_id [label=<<u>branch_id</u>>];
            branch_name; address;
        };

        Book -- {
            book_id [label=<<u>book_id</u>>];
            title; author_name; publisher_name;
        };

        Book_copy -- {
            No_of_copies [label=<<u style="text-decoration-style: dotted;">No_of_copies</u>>];
        };

        Borrower -- {
            card_no [label=<<u>card_no</u>>];
            name; b_address [label="address"]; phone_number;
        };
    };

    //
    // Participations
    //
    {
        edge [len=1.8];

        Book -- HasVersion [label="1"];
        HasVersion -- Book_copy [label="N", color="black:invis:black"];

        Library_branch -- Loans [label="1"];
        Loans -- Loaning [label="N", color="black:invis:black"];
        Book_copy -- IsLoanedTo [label="1"];
        IsLoanedTo -- Loaning [label="N", color="black:invis:black"];
        Loaning -- IsLoaned [label="N", color="black:invis:black"];
        IsLoaned -- Borrower [label="1"];
    }

    //
    // Layout
    //
    {
        {rank=same; Library_branch; Book_copy;}

        edge [style=invis];

    }
}
```

### 1.b
```graphviz
digraph PAST_2020_1b
{
    label="Past 2020 1.b\n\n";
    labelloc="t";
	fontsize=26;

    layout=dot;
    fontname="Sarasa Fixed TC, monospace";
	node [fontname="Sarasa Fixed TC, monospace"];
	edge [fontname="Sarasa Fixed TC, monospace"];

    {
        node [shape=none];
        Library_branch [
            xlabel=<<b>Library_branch</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="branch_id"><u>branch_id </u></td>
                <td port="branch_name">branch_name</td>
                <td port="address">address </td>
                </tr></table>>
        ];
        Book [
            xlabel=<<b>Book</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="book_id"><u>book_id</u></td>
                <td port="title">title   </td>
                <td port="author_name">author_name</td>
                <td port="publisher_name">publisher_name </td>
                </tr></table>>
        ];
        Book_copy [
            xlabel=<<b>Book_copy</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="book_id"><u>book_id</u></td>
                <td port="No_of_copies"><u>No_of_copies</u></td>
                </tr></table>>
        ];
        Borrower [
            xlabel=<<b>Borrower</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="card_no"><u>card_no</u></td>
                <td port="name">name</td>
                <td port="address">address </td>
                <td port="phone_number">phone_number</td>
                </tr></table>>
        ];
        Loaning [
            xlabel=<<b>Loaning</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="branch_id"><u>branch_id</u> </td>
                <td port="book_id"><u>book_id</u></td>
                <td port="No_of_copies"><u>No_of_copies</u> </td>
                <td port="card_no"><u>card_no</u></td>
                </tr></table>>
        ];
    }

    {
        edge [style=invis];
        Library_branch:_head -> Book:_head;
        Book:_head -> Book_copy:_head;
        Book_copy:_head -> Borrower:_head;
        Borrower:_head -> Loaning:_head;
    }

    graph [splines=polyline];
    {
        edge [constraint=false];
        Book_copy:book_id -> Book:book_id;
        Loaning:branch_id -> Library_branch:branch_id;
        Loaning:book_id -> Book:book_id;
        Loaning:No_of_copies -> Book_copy:No_of_copies;
        Loaning:card_no -> Borrower:card_no;
    }
}
```

### 2.a
```sql=
SELECT Fname, Lname
  FROM EMPLOYEE
 WHERE NOT EXISTS
       (SELECT *
          FROM WORKS_ON
          JOIN PROJECT
            ON WORKS_ON.Pno = PROJECT.Pnumber
         WHERE WORKS_ON.Essn = EMPLOYEE.Super_ssn
           AND PROJECT.Dnum = 5);
```

### 2.b
```sql=
SELECT Fname, Lname,
       SUM(WORKS_ON.HOURS) AS "total hours on all projects"
  FROM EMPLOYEE
  JOIN WORKS_ON
    ON EMPLOYEE.Ssn = WORKS_ON.Essn
 GROUP BY EMPLOYEE.Dno, EMPLOYEE.Ssn, EMPLOYEE.Fname, EMPLOYEE.Lname
HAVING COUNT(WORKS_ON.Pno) >= 3
   AND MIN(WORKS_ON.HOURS) >= 2;
```

### 2.c
```sql=
SELECT Lname, Salary, Hours
  FROM EMPLOYEE AS E1
  JOIN WORKS_ON
    ON E1.Ssn = WORKS_ON.Essn
 WHERE WORKS_ON.Pno IN
       (SELECT Pno
          FROM WORKS_ON
         GROUP BY Pno
        HAVING COUNT(Essn) > 5)
   AND Salary >
       (SELECT AVG(Salary)
          FROM EMPLOYEE AS E2
         GROUP BY Sex, Dno
        HAVING E1.Sex = E2.Sex
           AND E1.Dno = E2.Dno);
```

### 3.a ==TODO==

### 3.b ==TODO==


## 考古 2019
### 1.a
```graphviz
digraph PAST_2019_1a
{
    label="Past 2019 1.a\n\n";
    labelloc="t";
	fontsize=26;

    layout=dot;
    fontname="Sarasa Fixed TC, monospace";
	node [fontname="Sarasa Fixed TC, monospace"];
	edge [fontname="Sarasa Fixed TC, monospace"];

    {
        node [shape=none];
        SHIP [
            xlabel=<<b>SHIP</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Sname"><u>Sname</u></td>
                <td port="Owner">Owner</td>
                <td port="Type">Type</td>
                <td port="Home_port_cname">Home_port_cname</td>
                <td port="Home_port_pname">Home_port_pname</td>
                </tr></table>>
        ];
        SHIP_MOVEMENT [
            xlabel=<<b>SHIP_MOVEMENT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Sname"><u>Sname</u></td>
                <td port="Date"><u>Date</u></td>
                <td port="Time"><u>Time</u> </td>
                <td port="Longitude">Longitude </td>
                <td port="Latitude">Latitude  </td>
                </tr></table>>
        ];
        SHIP_TYPE [
            xlabel=<<b>SHIP_TYPE</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Type"><u>Type</u></td>
                <td port="Tonnage">Tonnage</td>
                <td port="Hull">Hull</td>
                </tr></table>>
        ];
        PORT [
            xlabel=<<b>PORT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Cname"><u>Cname</u></td>
                <td port="Pname"><u>Pname</u></td>
                <td port="Oname">Oname</td>
                </tr></table>>
        ];
        PORT_VISIT [
            xlabel=<<b>PORT_VISIT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Sname"><u>Sname</u></td>
                <td port="Cname"><u>Cname</u></td>
                <td port="Pname"><u>Pname</u></td>
                <td port="Start_date"><u>Start_date</u>   </td>
                <td port="End_date">End_date</td>
                </tr></table>>
        ];
        STATE_COUNTRY [
            xlabel=<<b>STATE_COUNTRY</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Cname"><u>Cname</u></td>
                <td port="Continent">Continent </td>
                </tr></table>>
        ];
        SEA_OCEAN_LAKE [
            xlabel=<<b>SEA_OCEAN_LAKE</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Oname"><u>Oname</u></td>
                </tr></table>>
        ];
    }

    {
        edge [style=invis];
        SHIP:_head -> SHIP_MOVEMENT:_head;
        SHIP_MOVEMENT:_head -> SHIP_TYPE:_head;
        SHIP_TYPE:_head -> PORT:_head;
        PORT:_head -> PORT_VISIT:_head;
        PORT_VISIT:_head -> STATE_COUNTRY:_head;
        STATE_COUNTRY:_head -> SEA_OCEAN_LAKE:_head;
    }

    graph [splines=polyline];
    {
        edge [constraint=false];
        SHIP:Type -> SHIP_TYPE:Type;
        SHIP:Home_port_cname -> PORT:Cname;
        SHIP:Home_port_pname -> PORT:Pname;
        SHIP_MOVEMENT:Sname -> SHIP:Sname;
        PORT:Cname -> STATE_COUNTRY:Cname;
        PORT:Oname -> SEA_OCEAN_LAKE:Oname;
        PORT_VISIT:Sname -> SHIP:Sname;
        PORT_VISIT:Cname -> PORT:Cname;
        PORT_VISIT:Pname -> PORT:Pname;
    }
}
```

### 1.c
```sql=
SELECT Fname, Lname
  FROM EMPLOYEE
  JOIN WORKS_ON
    ON EMPLOYEE.Ssn = Works_ON.Essn
  JOIN PROJECT
    ON WORKS_ON.Pno = PROJECT.Pnumber
 WHERE PROJECT.Dnum IN (3, 4)
 GROUP BY Ssn, Fname, Lname
HAVING COUNT(DISTINCT PROJECT.Dnum) = 2;
```

### 1.d
```sql=
SELECT Fname, Lname
  FROM EMPLOYEE
  JOIN WORKS_ON
    ON EMPLOYEE.Ssn = WOKS_ON.Essn
 GROUP BY Ssn, Fname, Lname
HAVING SUM(Hours) > 10;
```

### 1.e
```sql=
-- SUPER UGLY

  WITH S (Dno, Sum_salary) AS
       (SELECT Dno, SUM(Salary)
          FROM EMPLOYEE AS Super
         WHERE EXISTS
               (SELECT *
                  FROM EMPLOYEE
                 WHERE Super.Ssn = EMPLOYEE.Super_ssn)
         GROUP BY Dno),
       N (Dno, Sum_salary) AS
       (SELECT Dno, SUM(Salary)
          FROM EMPLOYEE
         WHERE NOT EXISTS
               (SELECT *
                  FROM S
                 WHERE EMPLOYEE.Ssn = S.Ssn)
         GROUP BY Dno)
SELECT S.Dno,
       S.Sum_salary AS "the total amount of salary for supervisor employees"
       N.Sum_salary AS "the total amount of salary for non-supervisor employees"
  FROM S
  JOIN N
    ON S.Dno = N.Dno;
```

### 2 ==TODO==

### 3 ==TODO==


## 考古 2018




## Notes
### `SELECT` 組成部分邏輯執行順序
1. `FROM`
2. `WHERE`
3. `GROUP BY`
4. `HAVING`
5. `SELECT`
6. `DISTINCT`
7. `ORDER BY`
8. `TOP/FETCH/OFFSET`

### *"List the various cases where the use of a NULL value would be appropriate."*


### *"Discuss the difficulties for possible DBMSs to enforce key, entity integrity and foreign key constraints, if related tables are located in different sites distributedly."*


### *"Discuss the advantages and disadvantages of different ways of handling View Materialization."*



