---
# System prepended metadata

title: DB Fin Project (05/29)
tags: [DB]

---

# DB Fin Project (05/29)
###### tags: `DB`

<style>
.markdown-body {max-width: 1024px;}
</style>

---

[TOC]

---

:::info
### Specifications
- [The Specification](https://drive.google.com/file/d/1xliNeW938VhZsT7G-VzdHB46vmlSVTlo/view?usp=sharing)
- [Sample Data](https://reurl.cc/yk9b8O)

### Notes
- [HAMI_DUCK 5](https://hackmd.io/@hami-duck/Skdnc7mRs/%2FXzDg50vQSF64DoNdMrbjbQ)

### Sources
- [GitHub Repository](https://github.com/Mibudin/ncu_db_system_project)

### Run Online
- [DB Fiddle](https://www.db-fiddle.com/f/sH5GYyzPSxkxuzhEpPwAA8/9)

### Report
- [Database Final Project Report](/648-xKM9SKK2OxuUGstT5A)
:::

---


## E-R Model  ==(WIP)==

<!-- ![](https://hackmd.io/_uploads/SyIZdC_E3.png) -->
<!-- ![](https://hackmd.io/_uploads/ryvd1C1rn.png) -->
<!-- ![](https://hackmd.io/_uploads/Syxw2hXWS2.png) -->
<!-- ![](https://hackmd.io/_uploads/SkzT037H3.png) -->
<!-- ![](https://hackmd.io/_uploads/B1wsSbHrh.png) -->
![](https://hackmd.io/_uploads/rkxOvAO8h.png)


## E-R Diagram  ==(WIP)==

<!-- ![](https://hackmd.io/_uploads/BJUNpzrr2.png) -->
<!-- ![](https://hackmd.io/_uploads/HJp0-4rH3.png) -->
<!-- ![](https://hackmd.io/_uploads/S1-SzwrBh.png) -->
<!-- ![](https://hackmd.io/_uploads/SJDPsDBSh.png) -->
<!-- ![](https://hackmd.io/_uploads/rkbwe63r2.png) -->
![](https://hackmd.io/_uploads/By4_PRO8n.png)


## THONK  ==(WIP)==

:::spoile r
:hammer_and_wrench: ***WIP*** :hammer_and_wrench:

- ~~**`student`**~~
- ~~**`course`**~~
- ~~**(`room`)**~~
- ~~**(`building`)**~~
- ~~**`curriculum_field`**~~
- ~~**`teacher`**~~
- ~~**(`college`)**~~
- ~~**`department`**~~
- ~~**(`grade`)**~~
- ~~**(`class`)**~~

---

- ~~`student` ---N---> **(*`takes`* / *`selects`*)** ---M---> `course`~~
- ~~`teacher` ---N---> ***`teaches`*** ---M---> `course`~~
- ~~`course` ---N---> ***`???`*** ---M---> `curriculum_field`~~~~

:::warning
***THONK***
- ***Important information***:
    - ~~NCU's English localization sucks~~
    - Ref: [*NCU Main Website*](https://www.ncu.edu.tw/tw/index.php)
    - Ref: [*Colleges & Centers of NCU*](https://www.ncu.edu.tw/en/pages/index.php?num=2)
    - Ref: [*The Organization of NCU (English & Older)*](https://in.ncu.edu.tw/ncu7060/en.php)
    - Ref: [*The Organization of NCU (Chinese & Newer)*](https://in.ncu.edu.tw/ncu7060/files_web/201301051401.pdf)
    - Ref: [*NCU Organization Regulations*](https://in.ncu.edu.tw/ncu7060/files_web/201301051400.pdf)
- [x] How to express **academic year & semester**
    - ~~? Composite attributes~~
        - Too redundant
    - ==?== Single string
        - including academic year & semester
- [x] How to express **department & grade & class**
    - ~~? One entity: *"department"*, and explicit raw strings / numbers for *"grade"* and *"class"*~~
        - ? Too raw
    - ==?== Two entities: *"department"*, *"class"*
        - *"class"* consists of tuples of grades and classes (and department)
        - That is, "class major" (a grade is just a attribute of a class)
        - ? That is, the grade of a student is not directly dependent on the identification to this student
    - ~~? Three entities: *"department"*, *"grade"*, *"class"*~~
        - That is, "department-grade-class hierarchy" with entities and weak entities
        - Actually, it is still "class major"
        - ? Too complicated
- [x] Need/How to express **group**
    - The original schema seems that it is not under consideration
- [x] How to express **locations (building & room)** attributes
    - ~~? Raw strings~~
    - ~~? Composite attributes~~
        - ? Raw strings
        - ? FKs to the building and the room
    - ==?== One FK to the room ("room" is a weak entity of buildings)
- [x] Is it possible that it is not necessary for all time slots to map to the same location
    - The original schema seems that it is not allowed
    - ~~? *"lesson"*: pair of time slot & place~~
- [x] How to express **time slot**
    - ~~? Raw strings~~
    - ==?== Multi-valued composite attributes
        - "Weekday code"
        - "Time code" ---> [`time`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-ver16) range
        - Easier to apply time-related information
        - ~~? Record time code ---> time range relationships~~
            - Less dependencies on external documents
    - ~~? Multi-valued binary strings attributes~~
        - Suppose that the max number of time slots in one day is 24 (NYCU) 
        - ?: 3 (weekdays) + 5 (times) = 8 bits ---> 1 bytes (8/8 bits used, 112/128 compositions used)
        - ? Worse than multi-valued composite attributes
            - Very ad hoc
            - Low readability
    - ~~? Binary strings attributes~~
        - ?: 24 (times) * 7 (weekdays) = 168 bits ---> 21 bytes (all used)
        - ?: 16 (times) * 7 (weekdays) = 112 bits ---> 14 bytes (all used)
        - Too redundant
        - Hard to fine it
    - 節：
        - NCU: "Course Unit"
        - NYCU: "Time"
        - Me: "Session"
    - Ref: [*NCU Course Timetable Codes (Chinese)*](https://pdc.adm.ncu.edu.tw/Course/course/COUR_U.pdf)
    - Ref: [*NCU Course Timetable Codes (English)*](https://pdc.adm.ncu.edu.tw/Course/course/COUR_U_EL.pdf)
    - Ref: [*NYCU Course Timetable Codes*](https://timetable.nycu.edu.tw/?r=main/timeClassroomCode&flang=en-us)
- [x] How to distinguish & name **undergraduate & graduate school / college**
    - From the naming conventions of the original Chinese names (AKA manually observation with fucking raw eyes)
    - ~~? To a attribute in "department"~~
    - ==?== To a attribute in "class"
    - Due to NCU's organization:
        Some graduate institute/class is NOT one department
        - Academic Unit: *"College" ---> "Department & Graduate Institute" ---> "Class"*
        - However, "department & institute" field on the NCU's student cards includes the identification of graduate CLASS
            - ![](https://hackmd.io/_uploads/BJaB0ryBh.png =200x)
            - ![](https://hackmd.io/_uploads/HJLd0SkH3.png)
        - Ref: [國立中央大學組織規程](https://in.ncu.edu.tw/ncu7060/files_web/201301051400.pdf)第二十四條－節錄
            > *本大學設下列學術單位：*
            > *一、學院：文學院、理學院、工學院、管理學院、資訊電機學院、地球科學學院、客家學院及生醫理工學院等八個學院。其下分設學系及研究所。各學院置院長一人，綜理院務。**各學系置主任一人，綜理系務。各研究所置所長一人，綜理所務。如系所合一者，應由一人兼任**。各學院、系、所置職員若干人。本大學各學院現有系、所詳如「國立中央大學組織架構表」。本架構表所列各院、系、所得依實際情況變更之。*
        - Ref: [大學法第十一條](https://law.moj.gov.tw/LawClass/LawSingle.aspx?pcode=H0030001&flno=11) ([Eng](https://law.moj.gov.tw/ENG/LawClass/LawAll.aspx?pcode=H0030001))－節錄
            > *大學下設**學院**或單獨設研究所，學院下得設**學系**或**研究所**。*
            > *Universities may establish subsidiary **colleges** and/or independent graduate institutes; a college may establish subsidiary **departments** and/or **graduate institutes**.*
        - Ref: [大學法施行細則第七條](https://law.moj.gov.tw/LawClass/LawSingle.aspx?pcode=H0030028&flno=7) ([Eng](https://law.moj.gov.tw/ENG/LawClass/LawAll.aspx?pcode=H0030028))－節錄
            > *學院依本法第十一條第一項規定下設之學系，包括與該學系相同或性質相近之**碩士班及博士班**。*
            > *Departments established by a university in accordance with Paragraph 1,Article 11 of the Act shall include **master’s and doctorate classes** the same or similar to those departments in nature.*
    - Bilingual glossary:
        - ==TODO==
- [x] How to express **college's direct subsidiary classes**
    - ==?== Suppose that there is no college's direct subsidiary classes
        - that is, colleges are not included in this case
- [x] How to distinguish **select & take**
    - ==?== *"selection"* and *"taking"*
    - Student can have many *"selection"* on the same course
        - that is, this student selected this course many times (and perhaps failed)
    - Each *"taking"* refer to exactly only one *"selection"* as the reason why a student takes a course
- [x] How to distinguish **semester & course**
    - ==?== Use a semester and a course number as a course's identifier
- ==TODO== Implementation of **state values**
- ==TODO== **State tables**
    - ? Passing score
        - ? Or implementing this logic by a programmatic way
- ==TODO== **Other Explicit Check** (more important when it comes to scalar values)
    - ? Time slot
    - ? Feedback rank
    - ? Score
- ==TODO== Check **normal forms**
- [x] Too complicated **coupling**
    - Fuck it
- [x] *"course"* has too many primary keys
    - Fuck it
- ==TODO== Use CYP's statements
- Instead of maximization, just minimize to just exactly fit the origin specification.
- ==TODO== Foreign constraints' actions for updating and deleting
- ==TODO== Talk about *"taking"*'s special weak entity relationship, PK, and FK
:::

