University Database ER Diagram

Database Management, Spring 2026 — Assignment #3 (30 pts)

Entity Details

Entity	Attributes	PK	Type
COLLEGE	Name, COffice, CPhone	Name	Strong
DEPARTMENT	DName, Code, DNumber, DPhone	Code	Strong
COURSE	CsName, CsNumber, CsDesc, CLevel, Credits	CsNumber	Strong
SECTION	SNumber, Semester, Year, Classroom(BuildingNo, RoomNo)	{CsNumber, Semester, Year, SNumber}	Weak (owner: COURSE)
INSTRUCTOR	IId, IName, Office, Phone, IRank	IId	Strong
STUDENT	SId, SName, SAddr, SPhone, Dob	SId	Strong

Entity

Attributes

Type

COLLEGE

Name, COffice, CPhone

Name

Strong

DEPARTMENT

DName, Code, DNumber, DPhone

Code

Strong

COURSE

CsName, CsNumber, CsDesc, CLevel, Credits

CsNumber

Strong

SECTION

SNumber, Semester, Year, Classroom(BuildingNo, RoomNo)

{CsNumber, Semester, Year, SNumber}

Weak (owner: COURSE)

INSTRUCTOR

IId, IName, Office, Phone, IRank

IId

Strong

STUDENT

SId, SName, SAddr, SPhone, Dob

SId

Strong

Relationship Details

Relationship	Entities	(min,max)	Attributes	Description
Administers	COLLEGE — DEPARTMENT	College(1,N) — Dept(1,1)	—	Each college administers multiple departments; each department belongs to one college.（每個學院管理多個系所。）
Deans	INSTRUCTOR — COLLEGE	Instructor(0,1) — College(1,1)	—	Each college has exactly one dean (a faculty member).（每個學院有一位院長。）
Chairs	INSTRUCTOR — DEPARTMENT	Instructor(0,1) — Dept(1,1)	CStartDate	Each department has one chair; tracks start date.（每個系所有一位系主任，記錄開始日期。）
Works_For	INSTRUCTOR — DEPARTMENT	Instructor(1,1) — Dept(0,N)	—	Each instructor has one primary department.（每位教師有一個主要系所。）
Offers	DEPARTMENT — COURSE	Dept(1,N) — Course(1,1)	—	Each department offers courses; each course belongs to one department.（每個系所開設課程。）
Has_Section	COURSE — SECTION	Course(1,N) — Section(1,1)	—	Identifying relationship. Each course has sections.（識別關係，課程有多個 section。）
Teaches	INSTRUCTOR — SECTION	Instructor(0,N) — Section(1,1)	—	Each section has one instructor; an instructor can teach many sections.（每個 section 有一位教師。）
Enrolls	STUDENT — SECTION	Student(0,N) — Section(5,N)	Grade	Students enroll in sections; each section needs at least 5 students.（學生選課，每個 section 至少 5 人。）
Major	STUDENT — DEPARTMENT	Student(1,1) — Dept(0,N)	—	Each student has exactly one major department.（每個學生有一個主修。）
Minor	STUDENT — DEPARTMENT	Student(0,N) — Dept(0,N)	—	A student may have zero or more minor departments.（學生可以有零到多個副修。）

Relationship

Entities

(min,max)

Attributes

Description

Administers

COLLEGE — DEPARTMENT

College(1,N) — Dept(1,1)

—

Each college administers multiple departments; each department belongs to one college.（每個學院管理多個系所。）

Deans

INSTRUCTOR — COLLEGE

Instructor(0,1) — College(1,1)

—

Each college has exactly one dean (a faculty member).（每個學院有一位院長。）

Chairs

INSTRUCTOR — DEPARTMENT

Instructor(0,1) — Dept(1,1)

CStartDate

Each department has one chair; tracks start date.（每個系所有一位系主任，記錄開始日期。）

Works_For

INSTRUCTOR — DEPARTMENT

Instructor(1,1) — Dept(0,N)

—

Each instructor has one primary department.（每位教師有一個主要系所。）

Offers

DEPARTMENT — COURSE

Dept(1,N) — Course(1,1)

—

Each department offers courses; each course belongs to one department.（每個系所開設課程。）

Has_Section

COURSE — SECTION

Course(1,N) — Section(1,1)

—

Identifying relationship. Each course has sections.（識別關係，課程有多個 section。）

Teaches

INSTRUCTOR — SECTION

Instructor(0,N) — Section(1,1)

—

Each section has one instructor; an instructor can teach many sections.（每個 section 有一位教師。）

Enrolls

STUDENT — SECTION

Student(0,N) — Section(5,N)

Grade

Students enroll in sections; each section needs at least 5 students.（學生選課，每個 section 至少 5 人。）

Major

STUDENT — DEPARTMENT

Student(1,1) — Dept(0,N)

—

Each student has exactly one major department.（每個學生有一個主修。）

Minor

STUDENT — DEPARTMENT

Student(0,N) — Dept(0,N)

—

A student may have zero or more minor departments.（學生可以有零到多個副修。）

Assumptions:

College Name is unique and serves as PK for COLLEGE.（學院名稱唯一，作為主鍵。）
SECTION is a weak entity identified by its owner COURSE plus {Semester, Year, SNumber}. The full PK is {CsNumber, Semester, Year, SNumber}.（Section 是弱實體，完整主鍵包含課程編號+學期+年份+節次編號。）
Classroom is a composite attribute of SECTION consisting of BuildingNo and RoomNo.（教室是複合屬性。）
A faculty member can be dean of at most one college and chair of at most one department.（一位教師最多擔任一個學院的院長和一個系所的主任。）
Dean and Chair are faculty members (Instructors) as stated in the requirements.（院長和系主任都是教師。）
Each student has exactly one major and zero or more minors.（每個學生有一個主修，零到多個副修。）
A section must have at least 5 students enrolled (min cardinality = 5).（每個 section 至少 5 名學生。）
Grade is an attribute of the Enrolls relationship; it may be NULL if not yet available.（成績是選課關係的屬性，可能尚未給分。）
Each course is offered by exactly one department.（每門課由一個系所開設。）

Assignment #3 — Database Management, Spring 2026 — 1134542 俞綱皓