---
# System prepended metadata

title: DB HW1 (03/27)
tags: [DB]

---

---
title: DB HW1 (03/27)
tags: DB
---

# 資料庫系統 Database <small>(CE6039*)</small> <br>HW 2
> 劉子雍．108502523
> 資訊工程學系四年級 A 班


<style>
.markdown-body
{
    max-width: 1200px !important;
}
</style>


```graphviz
digraph HW_2_0
{
    label="題目原圖\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];
        STUDENT [
            xlabel=<<b>STUDENT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="SSN"><u>SSN</u></td>
                <td port="Name">Name</td>
                <td port="Major">Major</td>
                <td port="Bdate">Bdate</td>
                </tr></table>>
        ];
        COURSE [
            xlabel=<<b>COURSE</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Cname">Cname</td>
                <td port="Dept">Dept</td>
                </tr></table>>
        ];
        ENROLL [
            xlabel=<<b>ENROLL</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="SSN"><u>SSN</u></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Quater"><u>Quater </u></td>
                <td port="Grade">Grade</td>
                </tr></table>>
        ];
        BOOK_ADOPTION [
            xlabel=<<b>       BOOK_ADOPTION</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Quater"><u>Quater </u></td>
                <td port="Book_ISBN">Book_ISBN</td>
                </tr></table>>
        ];
        TEXT [
            xlabel=<<b>TEXT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Book_ISBN"><u>Book_ISBN</u></td>
                <td port="Book_Title">Book_Title  </td>
                <td port="Publisher">Publisher   </td>
                <td port="Author">Author</td>
                </tr></table>>
        ];
    }

    {
        edge [style=invis];
        STUDENT:_head -> COURSE:_head;
        COURSE:_head -> ENROLL:_head;
        ENROLL:_head -> BOOK_ADOPTION:_head;
        BOOK_ADOPTION:_head -> TEXT:_head;
    }
}
```

---

```graphviz
digraph HW_2_1
{
    label="HW2\n1. 畫出 Foreign key\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];
        STUDENT [
            xlabel=<<b>STUDENT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="SSN"><u>SSN</u></td>
                <td port="Name">Name</td>
                <td port="Major">Major</td>
                <td port="Bdate">Bdate</td>
                </tr></table>>
        ];
        COURSE [
            xlabel=<<b>COURSE</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Cname">Cname</td>
                <td port="Dept">Dept</td>
                </tr></table>>
        ];
        ENROLL [
            xlabel=<<b>ENROLL</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="SSN"><u>SSN</u></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Quater"><u>Quater </u></td>
                <td port="Grade">Grade</td>
                </tr></table>>
        ];
        BOOK_ADOPTION [
            xlabel=<<b>       BOOK_ADOPTION</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Course#"><u>Course# </u></td>
                <td port="Quater"><u>Quater </u></td>
                <td port="Book_ISBN">Book_ISBN</td>
                </tr></table>>
        ];
        TEXT [
            xlabel=<<b>TEXT</b>>,
            label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
                <td port="_head" cellpadding="0" border="0"></td>
                <td port="Book_ISBN"><u>Book_ISBN</u></td>
                <td port="Book_Title">Book_Title  </td>
                <td port="Publisher">Publisher   </td>
                <td port="Author">Author</td>
                </tr></table>>
        ];
    }

    {
        edge [style=invis];
        STUDENT:_head -> COURSE:_head;
        COURSE:_head -> ENROLL:_head;
        ENROLL:_head -> BOOK_ADOPTION:_head;
        BOOK_ADOPTION:_head -> TEXT:_head;
    }

    graph [splines=polyline];
    {
        edge [constraint=false];
        ENROLL:SSN -> STUDENT:SSN;
        ENROLL:"Course#" -> COURSE:"Course#";
        BOOK_ADOPTION:"Course#" -> COURSE:"Course#";
        BOOK_ADOPTION:Book_ISBN -> TEXT:Book_ISBN;
    }
}
```

---

```graphviz
graph HW_2_2 {
    label = "HW2\n2. 畫出可能 ER diagram\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"];

        STUDENT;
        COURSE;
        TEXT;

        node [peripheries=2];
        QUATER;
    };

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

        ENROLLS; ADOPTS;

        node [peripheries=2];

        HAS;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        STUDENT -- {
            SSN [label=<<u>SSN</u>>];
            Name; Major; Bdate;
        };

        COURSE -- {
            "Course#" [label=<<u>Course#</u>>];
            Cname; Dept;
        };

        QUATER -- {
            Quater [label=<<u>Quater</u>>];
        };

        TEXT -- {
            Book_ISBN [label=<<u>Book_ISBN</u>>];
            Book_Title; Publisher; Author;
        }

        ENROLLS -- {Grade;};
    };

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

        STUDENT -- ENROLLS [label="(0, N)"];
        ENROLLS -- QUATER [label="  (0, N)"];

        COURSE -- HAS [label="(1, N)"];
        HAS -- QUATER [label="(1, 1)"];

        QUATER -- ADOPTS [label="(0, N)"];
        ADOPTS -- TEXT [label="  (0, N)"];
    }

    //
    // Layout
    //
    {
        {rank=same; STUDENT; ENROLLS; Grade;}
        {rank=same; COURSE; HAS; QUATER; ADOPTS;}
        {rank=same; TEXT;}

        edge [style=invis];
        SSN -- COURSE;
    }
}
```
