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

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

<!--
## Notes

## Graphviz Test
- https://github.com/ballantony/dot-graphviz/blob/main/graphs.org
- https://graphviz.org/Gallery/neato/ER.html

graph ER {
	fontname="Helvetica,Arial,sans-serif"
	node [fontname="Helvetica,Arial,sans-serif"]
	edge [fontname="Helvetica,Arial,sans-serif"]
	layout=neato
	node [shape=box]; course; institute; student;
	node [shape=ellipse]; {node [label="name"] name0; name1; name2;}
		code; grade; number;
	node [shape=diamond,style=filled,color=lightgrey]; "C-I"; "S-C"; "S-I";

	name0 -- course;
	code -- course;
	course -- "C-I" [label="n",len=1.00];
	"C-I" -- institute [label="1",len=1.00];
	institute -- name1;
	institute -- "S-I" [label="1",len=1.00];
	"S-I" -- student [label="n",len=1.00];
	student -- grade;
	student -- name2;
	student -- number;
	student -- "S-C" [label="m",len=1.00];
	"S-C" -- course [label="n",len=1.00];

	label = "\n\nEntity Relation Diagram\ndrawn by NEATO";
	fontsize=20;
}

-->

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

graph HW_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"];
    
    //
    // Entities
    //
    {
        node [shape=box, fontsize=16, style=filled, fillcolor=lightgrey, ordering="out"];

        COLLEGE; INSTRUCTOR;
        DEPT;    STUDENT;
        COURSE;  SECTION;
    };
    
    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
        
        DEAN;   ADMINS; CHAIR;   EMPLOYS; HAS;
        OFFERS; TAKES;  TEACHES; SECS;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        COLLEGE -- {
            CName [label=<<u>CName</u>>];
            COffice; CPhone;
        };

        DEPT -- {
            DName [label=<<u>DName</u>>];
            DCode [label=<<u>DCode</u>>];
            DOffice; DPhone;
        };

        INSTRUCTOR -- {
            Id [label=<<u>Id</u>>];
            Rank; IName; IOffice; IPhone;
        };

        STUDENT -- {
            SId [label=<<u>SId</u>>];
            DOB; SName; Addr; Phone; Major;
        };
        SName -- {FName; MName; LName;};

        COURSE -- {
            CCode [label=<<u>CCode</u>>];
            CoName [label=<<u>CoName</u>>];
            Credits; Level; CDesc;
        };

        SECTION -- {
            SecId [label=<<u>SecId</u>>];
            SecNo; Sem; Year; CRoom; DaysTime;
        };
        CRoom -- {Bldg; RoomNo;};
        
        CHAIR -- {CStartDate};
        
        TAKES -- {Grades};
    };
    
    //
    // Participations
    //
    {
        edge [label=" ", len=1.8];

        COLLEGE -- DEAN [label="(1,1)"];
        DEAN -- INSTRUCTOR [label="(0,1)"];
        
        COLLEGE -- ADMINS [label="(0,N)"];
        ADMINS -- DEPT [label="(1,1)"];
        /* invis */ CName -- ADMINS [style=invis];
        
        INSTRUCTOR -- CHAIR [label="(0,1)"];
        CHAIR -- DEPT [label="(1,1)"];
        
        DEPT -- EMPLOYS [label="(0,N)"];
        EMPLOYS -- INSTRUCTOR [label="(1,1)"];
        
        DEPT -- HAS [label="(0,N)"];
        HAS -- STUDENT [label="(0,1)"];
        
        DEPT -- OFFERS [label="(0,N)"];
        OFFERS -- COURSE [label="(1,1)"];
        /* invis */ DName -- OFFERS [style=invis];
        
        COURSE -- SECS [label="(0,N)"];
        SECS -- SECTION [label="(1,1)"];
        
        STUDENT -- TAKES [label="(0,N)"];
        TAKES -- SECTION [label="(5,N)"];
        
        INSTRUCTOR -- TEACHES [label="(0,N)"];
        TEACHES -- SECTION [label="(1,1)"];
    }
    
    {rank=same; COLLEGE; DEAN; INSTRUCTOR;};
    {rank=same; ADMINS; CHAIR; CStartDate; EMPLOYS;};
    {rank=same; DEPT; HAS; STUDENT; TEACHES;};
    {rank=same; OFFERS; Grades; TAKES;};
    {rank=same; COURSE; SECS; SECTION;};
}

---

graph HW_1 {
    label = "HW 1\n1. 修改標記為單雙線\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"];

        COLLEGE; INSTRUCTOR;
        DEPT;    STUDENT;
        COURSE;  SECTION;
    };
    
    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
        
        DEAN;   ADMINS; CHAIR;   EMPLOYS; HAS;
        OFFERS; TAKES;  TEACHES; SECS;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        COLLEGE -- {
            CName [label=<<u>CName</u>>];
            COffice; CPhone;
        };

        DEPT -- {
            DName [label=<<u>DName</u>>];
            DCode [label=<<u>DCode</u>>];
            DOffice; DPhone;
        };

        INSTRUCTOR -- {
            Id [label=<<u>Id</u>>];
            Rank; IName; IOffice; IPhone;
        };

        STUDENT -- {
            SId [label=<<u>SId</u>>];
            DOB; SName; Addr; Phone; Major;
        };
        SName -- {FName; MName; LName;};

        COURSE -- {
            CCode [label=<<u>CCode</u>>];
            CoName [label=<<u>CoName</u>>];
            Credits; Level; CDesc;
        };

        SECTION -- {
            SecId [label=<<u>SecId</u>>];
            SecNo; Sem; Year; CRoom; DaysTime;
        };
        CRoom -- {Bldg; RoomNo;};
        
        CHAIR -- {CStartDate};
        
        TAKES -- {Grades};
    };
    
    //
    // Participations
    //
    {
        edge [label=" ", len=1.8, color=red, fontcolor=red];

        COLLEGE -- DEAN [label="1", color="red:invis:red"];  // [label="(1,1)"];
        DEAN -- INSTRUCTOR [label="1"];  // [label="(0,1)"];
        
        COLLEGE -- ADMINS [label="1"];  // [label="(0,N)"];
        ADMINS -- DEPT [label="N", color="red:invis:red"];  // [label="(1,1)"];
        /* invis */ CName -- ADMINS [style=invis];
        
        INSTRUCTOR -- CHAIR [label="1"];  // [label="(0,1)"];
        CHAIR -- DEPT [label="  1", color="red:invis:red"];  // [label="(1,1)"];
        
        DEPT -- EMPLOYS [label="1"];  // [label="(0,N)"];
        EMPLOYS -- INSTRUCTOR [label="N", color="red:invis:red"];  // [label="(1,1)"];
        
        DEPT -- HAS [label="1"];  // [label="(0,N)"];
        HAS -- STUDENT [label="N"];  // [label="(0,1)"];
        
        DEPT -- OFFERS [label="1"];  // [label="(0,N)"];
        OFFERS -- COURSE [label="  N", color="red:invis:red"];  // [label="(1,1)"];
        /* invis */ DName -- OFFERS [style=invis];
        
        COURSE -- SECS [label="1"];  // [label="(0,N)"];
        SECS -- SECTION [label="N", color="red:invis:red"];  // [label="(1,1)"];
        
        STUDENT -- TAKES [label="M"];  // [label="(0,N)"];
        TAKES -- SECTION [label="  N", color="red:invis:red"];  // [label="(5,N)"];  // TODO: ???
        
        INSTRUCTOR -- TEACHES [label="1"];  // [label="(0,N)"];
        TEACHES -- SECTION [label="  N", color="red:invis:red"];  // [label="(1,1)"];
    }
    
    {rank=same; COLLEGE; DEAN; INSTRUCTOR;};
    {rank=same; ADMINS; CHAIR; CStartDate; EMPLOYS;};
    {rank=same; DEPT; HAS; STUDENT; TEACHES;};
    {rank=same; OFFERS; Grades; TAKES;};
    {rank=same; COURSE; SECS; SECTION;};
}

---

graph HW_3 {
    label = "HW 1\n2. 考慮加入不分系學生\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"];

        COLLEGE; INSTRUCTOR;
        DEPT;    STUDENT;
        COURSE;  SECTION;
    };
    
    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
        
        DEAN;   ADMINS; CHAIR;   EMPLOYS; HAS;
        OFFERS; TAKES;  TEACHES; SECS;
        {node [color=red, fontcolor=red, fillcolor=mistyrose] CONTAINS;}  // Newly added
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        COLLEGE -- {
            CName [label=<<u>CName</u>>];
            COffice; CPhone;
        };

        DEPT -- {
            DName [label=<<u>DName</u>>];
            DCode [label=<<u>DCode</u>>];
            DOffice; DPhone;
        };

        INSTRUCTOR -- {
            Id [label=<<u>Id</u>>];
            Rank; IName; IOffice; IPhone;
        };

        STUDENT -- {
            SId [label=<<u>SId</u>>];
            DOB; SName; Addr; Phone; Major;
        };
        SName -- {FName; MName; LName;};

        COURSE -- {
            CCode [label=<<u>CCode</u>>];
            CoName [label=<<u>CoName</u>>];
            Credits; Level; CDesc;
        };

        SECTION -- {
            SecId [label=<<u>SecId</u>>];
            SecNo; Sem; Year; CRoom; DaysTime;
        };
        CRoom -- {Bldg; RoomNo;};
        
        CHAIR -- {CStartDate};
        
        TAKES -- {Grades};
    };
    
    //
    // Participations
    //
    {
        edge [label=" ", len=1.8];

        COLLEGE -- DEAN [label="(1,1)"];
        DEAN -- INSTRUCTOR [label="(0,1)"];
        
        COLLEGE -- ADMINS [label="(0,N)"];
        ADMINS -- DEPT [label="(1,1)"];
        /* invis */ CName -- ADMINS [style=invis];
        
        INSTRUCTOR -- CHAIR [label="(0,1)"];
        CHAIR -- DEPT [label="(1,1)"];
        
        DEPT -- EMPLOYS [label="(0,N)"];
        EMPLOYS -- INSTRUCTOR [label="(1,1)"];
        
        DEPT -- HAS [label="(0,N)"];
        HAS -- STUDENT [label="(0,1)"];

        // Newly added
        COLLEGE -- CONTAINS [label="(0,N)", color=red, fontcolor=red];
        CONTAINS -- STUDENT [label="(0,1)", color=red, fontcolor=red];
        
        DEPT -- OFFERS [label="(0,N)"];
        OFFERS -- COURSE [label="(1,1)"];
        /* invis */ DName -- OFFERS [style=invis];
        
        COURSE -- SECS [label="(0,N)"];
        SECS -- SECTION [label="(1,1)"];
        
        STUDENT -- TAKES [label="(0,N)"];
        TAKES -- SECTION [label="(5,N)"];
        
        INSTRUCTOR -- TEACHES [label="(0,N)"];
        TEACHES -- SECTION [label="(1,1)"];
    }
    
    {rank=same; COLLEGE; DEAN; INSTRUCTOR;};
    {rank=same; ADMINS; CHAIR; CStartDate; EMPLOYS;};
    {rank=same; DEPT; HAS; STUDENT; TEACHES;};
    {rank=same; OFFERS; Grades; TAKES;};
    {rank=same; COURSE; SECS; SECTION;};
}

---

graph HW_2 {
    label = "HW 1\n3. INSTRUCTOR 考慮合聘\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"];

        COLLEGE; INSTRUCTOR;
        DEPT;    STUDENT;
        COURSE;  SECTION;
    };
    
    //
    // Relationships
    //
    {
        node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
        
        DEAN;   ADMINS; CHAIR;   EMPLOYS; HAS;
        OFFERS; TAKES;  TEACHES; SECS;
    }
    
    //
    // Attributes
    //
    {
        node [shape=oval, fontsize=14];

        COLLEGE -- {
            CName [label=<<u>CName</u>>];
            COffice; CPhone;
        };

        DEPT -- {
            DName [label=<<u>DName</u>>];
            DCode [label=<<u>DCode</u>>];
            DOffice; DPhone;
        };

        INSTRUCTOR -- {
            Id [label=<<u>Id</u>>];
            Rank; IName; IOffice; IPhone;
        };

        STUDENT -- {
            SId [label=<<u>SId</u>>];
            DOB; SName; Addr; Phone; Major;
        };
        SName -- {FName; MName; LName;};

        COURSE -- {
            CCode [label=<<u>CCode</u>>];
            CoName [label=<<u>CoName</u>>];
            Credits; Level; CDesc;
        };

        SECTION -- {
            SecId [label=<<u>SecId</u>>];
            SecNo; Sem; Year; CRoom; DaysTime;
        };
        CRoom -- {Bldg; RoomNo;};
        
        CHAIR -- {CStartDate};
        
        TAKES -- {Grades};
    };
    
    //
    // Participations
    //
    {
        edge [label=" ", len=1.8];

        COLLEGE -- DEAN [label="(1,1)"];
        DEAN -- INSTRUCTOR [label="(0,1)"];
        
        COLLEGE -- ADMINS [label="(0,N)"];
        ADMINS -- DEPT [label="(1,1)"];
        /* invis */ CName -- ADMINS [style=invis];
        
        INSTRUCTOR -- CHAIR [label="(0,1)"];
        CHAIR -- DEPT [label="(1,1)"];
        
        DEPT -- EMPLOYS [label="(0,N)"];
        EMPLOYS -- INSTRUCTOR [label="(1,N)", fontcolor=red];  // [label="(1,1)"];
        
        DEPT -- HAS [label="(0,N)"];
        HAS -- STUDENT [label="(0,1)"];
        
        DEPT -- OFFERS [label="(0,N)"];
        OFFERS -- COURSE [label="(1,1)"];
        /* invis */ DName -- OFFERS [style=invis];
        
        COURSE -- SECS [label="(0,N)"];
        SECS -- SECTION [label="(1,1)"];
        
        STUDENT -- TAKES [label="(0,N)"];
        TAKES -- SECTION [label="(5,N)"];
        
        INSTRUCTOR -- TEACHES [label="(0,N)"];
        TEACHES -- SECTION [label="(1,1)"];
    }
    
    {rank=same; COLLEGE; DEAN; INSTRUCTOR;};
    {rank=same; ADMINS; CHAIR; CStartDate; EMPLOYS;};
    {rank=same; DEPT; HAS; STUDENT; TEACHES;};
    {rank=same; OFFERS; Grades; TAKES;};
    {rank=same; COURSE; SECS; SECTION;};
}