# 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 :::