國企系資料庫系統概論第三週(3/17)內容

Relation Data Model關聯式資料模型

所謂Relation是指由多個屬性所組成的表格. 如下面這個Student Relation具有三個屬性: (學號,姓名,所屬系所), 內有三筆資料.

學號姓名所屬系所

85212007 張振洋國企系

85212008 王淑芬國企系

85213009 俞旭昇資管系

下面這個Relation則是學生的選課資料:

課號學年期班別學號成績

040001 862 0 85212007 80

040001 862 0 85212009 69

040002 862 0 85212007 80

若某一組屬性可以唯一決定某一筆資料, 則稱這一組屬性為key. 我們可以設定某一個key為primary key. 若某一組屬性要對照到另一個Relation 的primary key, 則稱該組屬性為foreign key. 如學生資料中的primary key 為(學號), 選課資料的primary key為(學號,學年期,課號). 選課資料中的學號必需參考到學生資料中的學號, 因此我們說選課資料有一個foreign key (學號)參考到學生資料這個Relation.

除了定義Relation所有的屬性及primary key, foreign key之外, 我們也可對屬性的值域加以限制. 如學號是由八位數字所組成, 0<=成績<=100等.

以下是建立本校教務資料庫表格之MicroSoft SQL Server 6.5資料定義程式:

/* 暨南大學教務系統資料綱要設定程式
   作者：俞旭昇
   最後修改日期：12/9/1997
*/
USE ncnu
go
dump transaction ncnu with no_log
go
DROP TABLE whodouble
go
DROP TABLE whoassist
go
DROP TABLE transfer
go
DROP TABLE teaching
go
DROP TABLE selected
go
DROP TABLE rest
go
DROP TABLE outcredit
go
DROP TABLE opened
go
DROP TABLE coremin
go
DROP TABLE conduct
go
DROP TABLE classroom
go
DROP TABLE assist
go
DROP TABLE require
go
DROP TABLE precourse
go
DROP TABLE course
go
DROP TABLE teacher
go
DROP TABLE student
go
DROP TABLE department
go
DROP TABLE college
go

CREATE TABLE college /* 學院資料 */
(
  colid              char(1)            NOT NULL, /* 學院代碼 */
  name               varchar(10)        NOT NULL, /* 學院名稱 */
  PRIMARY KEY(colid)
)
go
CREATE TABLE department /* 系所資料 */
(
  deptid             char(2)            NOT NULL, /* 系所代碼 */
  colid              char(1)            NOT NULL, /* 所屬學院代碼 */
  degree             varchar(20)        NOT NULL, /* 學士學位名稱 */
  gdegree            varchar(20)        NOT NULL, /* 博碩士學位名稱 */
  cname              varchar(40)        NOT NULL, /* 系所中文名 */
  ename              varchar(40)        NOT NULL, /* 系所英文名 */
  sname              varchar(10)        NOT NULL, /* 中文簡稱 */
  mincredit          numeric(3,0)       NOT NULL, /* 大學部最低畢業學分 */
  gmincredit         numeric(2,0)       NOT NULL, /* 研究所最低畢業學分 */
  pmincredit         numeric(2,0)       NOT NULL, /* 博士班最低畢業學分 */
  dmincredit         numeric(2,0)       NOT NULL, /* 直攻生最低畢業學分 */
  note               text               NULL,     /* 選課注意事項 */
  PRIMARY KEY (deptid),
  foreign key(colid) references college
)
CREATE INDEX dept_id ON department(deptid)
go
CREATE TABLE student /* 學生學籍資料 */
( 
  studentid          char(9)            NOT NULL, /* 學號 */
  deptid             char(2)            NOT NULL, /* 所屬系所代碼 */
  name               varchar(20)        NOT NULL, /* 姓名 */
  birthday           char(7)            NULL, /* 生日 */
  sex                char(1)            NOT NULL, /* 性別 */
  grade              char(1)            NOT NULL, /* 年級 */
  type               varchar(40)        NOT NULL DEFAULT '一般生', /* 身分別 */
  status             char(1)            NOT NULL DEFAULT '0', /* 學生狀態 */
  leavedate          char(7)            NULL, /* 離校日期 */  
  email              varchar(40)        NULL, /* 電子郵件信箱 */
  id                 char(10)           NULL, /* 身分證字號 */
  tel                varchar(14)        NULL, /* 聯絡電話 */
  citizen            varchar(12)        NULL, /* 國籍 */
  addr1              varchar(80)        NULL, /* 戶籍地址 */
  addr2              varchar(80)        NULL, /* 通訊地址 */
  thesis             varchar(255)       NULL, /* 論文題目 */
  tscore             numeric(3,0)       NULL, /* 學位考試成績 */
  diplomaid          char(4)            NULL, /* 畢業證書號碼 */
  entrydoc           varchar(40)        NULL, /* 入學文號 */
  entrydiploma       varchar(40)        NULL, /* 入學學歷 */
  password           varchar(20)        NULL, /* 密碼 */
  entrydate          char(5)            NOT NULL, /* 入學年月 */
  ograduate          char(1)            NOT NULL DEFAULT '1', /* 原學校畢肄業 */
  oentrydoc          varchar(40)        NULL, /* 原學校入學資格核准年月文號 */
  ograde             char(1)            NULL, /* 原學校肄業年級 */
  ograduatedoc       varchar(40) 	NULL, /* 原學校畢業證書號碼 */
  equaldoc           varchar(40)        NULL, /* 比敘大專同等學力年月文號 */
  pracscore          numeric(3,0)       NULL, /* 實習成績 */
  leavereason        varchar(40)	NULL, /* 離校原因 */
  deptgroup          varchar(20)        NULL, /* 系所組別 */
  PRIMARY KEY (studentid),
  foreign key(deptid) references department
)
CREATE INDEX student_id ON student(studentid)
go
CREATE TABLE teacher /* 教師基本資料 */
(
  teacherid          varchar(10)        NOT NULL, /* 身分證字號 */
  deptid             char(2)            NOT NULL, /* 所屬系所代碼 */
  name               varchar(20)        NOT NULL, /* 姓名 */
  sex                char(1)            NOT NULL, /* 性別 */
  birthday           char(7)            NULL,     /* 生日 */
  isfulltime         char(1)            NOT NULL DEFAULT '1', /* 是否專任 */
  position           varchar(8)         NOT NULL, /* 職稱 */
  parttime           varchar(40)        NULL,     /* 兼職職務 */
  overtime           numeric(2,0)       DEFAULT 0, /* 兼職核減時數 */
  hiredate           char(7)            NOT NULL, /* 起聘日期 */
  status             char(1)            NOT NULL DEFAULT '0', /* 狀態 */
  password           varchar(20)        NULL, /* 密碼 */
  PRIMARY KEY (teacherid),
  foreign key(deptid) references department
)
CREATE INDEX teacher_id ON teacher(teacherid)
go
CREATE TABLE course /* 課程基本資料 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  cname              varchar(40)        NOT NULL, /* 中文名稱 */
  ename              varchar(80)        NULL,     /* 英文名稱 */
  grade              char(2)            NULL,     /* 修習年級 */
  credit             numeric(1,0)       NOT NULL, /* 學分數 */
  coretype           char(1)            NOT NULL DEFAULT 0, /* 核心類別 */
  practice           char(1)            NOT NULL DEFAULT 0, /* 是否為實習課 */
  PRIMARY KEY (courseid)
)
CREATE INDEX course_id ON course(courseid)
go
CREATE TABLE precourse /* 先修學分要求 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  precourseid        char(6)            NOT NULL, /* 先修課號 */
  start              numeric(3,0)       NOT NULL, /* 啟用年份 */
  stop               numeric(3,0)       NOT NULL DEFAULT 999, /* 失效年份 */
  PRIMARY KEY (courseid,precourseid),
  foreign key (courseid) references course,
  foreign key (precourseid) references course
)
CREATE INDEX precourse_id on precourse(courseid)
go
CREATE TABLE require /* 必修學分 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  deptid             char(2)            NOT NULL, /* 要求必修系所代碼 */
  start              numeric(3,0)       NOT NULL, /* 啟用年份 */
  stop               numeric(3,0)       NOT NULL DEFAULT 999, /* 失效年份 */
  PRIMARY KEY (courseid,deptid,start,stop),
  foreign key (courseid) references course,
  foreign key (deptid) references department
)
create index require_id on require(courseid)
create index require_deptid on require(deptid)
go
CREATE TABLE assist /* 輔系必修學分 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  deptid             char(2)            NOT NULL, /* 輔系系所代碼 */
  start              numeric(3,0)       NOT NULL, /* 啟用年份 */
  stop               numeric(3,0)       NOT NULL DEFAULT 999, /* 失效年份 */
  PRIMARY KEY (courseid,deptid,start,stop),
  foreign key (courseid) references course,
  foreign key (deptid) references department
)
create index assist_id on assist(courseid)
go
CREATE TABLE classroom /* 教室資料 */
(
  classroomid        varchar(8)         NOT NULL, /* 教室代碼 */
  capacity           numeric(3,0)       NOT NULL, /* 容量 */
  equipment          char(1)            DEFAULT '0', /* 設備 */
  deptid             char(2)            NULL, /* 優先分配系所 */
  PRIMARY KEY (classroomid)
)
create index classroom_id on classroom(classroomid)
go
CREATE TABLE conduct /* 操行成績 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  year               char(4)            NOT NULL, /* 學年期 */
  score              numeric(3,0)       NOT NULL DEFAULT -1, /* 操行成績 */
  PRIMARY KEY (studentid,year),
  foreign key(studentid) references student
)
create index conduct_sid on conduct(studentid)
go
CREATE TABLE coremin /* 核心課程最低學分數 */
(
  coretype           char(1)            NOT NULL, /* 核心類別 */
  mincredit          numeric(2,0)       NOT NULL, /* 最低學分數 */
  PRIMARY KEY (coretype)
)
go
CREATE TABLE opened /* 開設課程資料 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  year               char(4)            NOT NULL, /* 學年期 */
  class              char(1)            NOT NULL DEFAULT '0', /* 班別 */
  requirement        char(1)            DEFAULT '0', /* 設備需求 */
  time               varchar(8)         NULL, /* 上課時間 */
  place              varchar(16)        NULL, /* 上課地點 */
  evaluation         char(1)            NULL, /* 評鑑結果 */
  hours              numeric(1,0)       NULL, /* 授課時數 */
  syllabus           text               NULL, /* 課程綱要 */
  limit              numeric(3,0)       NOT NULL DEFAULT 0, /* 人數限制 */
  note               varchar(40)        NULL, /* 註記 */
  PRIMARY KEY (courseid,year,class),
  foreign key(courseid) references course
)
CREATE INDEX opened_all on opened(courseid,year,class)
CREATE INDEX opened_year on opened(year)
go
CREATE TABLE outcredit /* 外修學分資料 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  equals             char(6)            NULL,     /* 抵免課號 */
  credit             numeric(1,0)       NULL,     /* 抵免學分數 */
  school             varchar(30)        NOT NULL, /* 修習學校 */
  year               char(4)            NOT NULL, /* 修習學年期 */
  score              numeric(3,0)       NOT NULL DEFAULT -1, /* 成績 */
  cname              varchar(40)        NULL,     /* 中文課名 */
  ename              varchar(80)        NULL,     /* 英文課名 */
  primary key(studentid,year,cname),
  foreign key(studentid) references student
)
CREATE INDEX student_id ON outcredit(studentid)
go
CREATE TABLE rest /* 休學資料 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  year               char(4)            NOT NULL, /* 開始休學學年期 */
  period             numeric(1,0)       NULL, /* 修學期間(學期) */
  restdoc            varchar(20)        NULL, /* 休學文號 */
  PRIMARY KEY (studentid,year),
  foreign key (studentid) references student
)
go
CREATE TABLE selected /* 選課資料 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  year               char(4)            NOT NULL, /* 學年期 */
  class              char(1)            NOT NULL DEFAULT '0', /* 班別 */
  studentid          char(9)            NOT NULL, /* 學號 */
  score              numeric(3,0)       NULL DEFAULT -1, /* 成績 */
  PRIMARY KEY (studentid,courseid,year),
  foreign key (courseid,year,class) references opened,
  foreign key (studentid) references student
)
CREATE INDEX selected_who on selected(courseid,year,class)
CREATE INDEX selected_cid on selected(courseid,studentid)
CREATE INDEX selected_sid ON selected(studentid,year,class)
go
CREATE TABLE teaching /* 授課資料 */
(
  courseid           char(6)            NOT NULL, /* 課號 */
  year               char(4)            NOT NULL, /* 學年期 */
  class              char(1)            NOT NULL DEFAULT '0', /* 班別 */
  teacherid          varchar(10)        NOT NULL, /* 教師身分證字號 */
  PRIMARY KEY (teacherid,courseid,year,class),
  foreign key(courseid,year,class) references opened,
  foreign key(teacherid) references teacher
)
CREATE INDEX teaching_tid on teaching(teacherid)
CREATE INDEX teaching_cid on teaching(courseid,year,class)
go
CREATE TABLE transfer /* 轉系資料 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  year               char(4)            NOT NULL, /* 轉系學年期 */
  fromdep            char(2)            NOT NULL, /* 原系所 */
  todep              char(2)            NOT NULL, /* 轉至系所 */
  beforegrade        char(1)            NOT NULL, /* 轉系前年級 */
  aftergrade         char(1)            NOT NULL, /* 轉系後年級 */
  PRIMARY KEY (studentid,year),
  foreign key(studentid) references student
)
go
CREATE TABLE whoassist /* 輔系學生 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  deptid             char(2)            NOT NULL, /* 系所代碼 */
  PRIMARY KEY (studentid,deptid),
  foreign key (studentid) references student,
  foreign key (deptid) references department
)
go
CREATE TABLE whodouble /* 雙主修學生 */
(
  studentid          char(9)            NOT NULL, /* 學號 */
  deptid             char(2)            NOT NULL, /* 系所代碼 */
  PRIMARY KEY (studentid,deptid),
  foreign key(studentid) references student,
  foreign key(deptid) references department
)
go

學號	姓名	所屬系所
85212007	張振洋	國企系
85212008	王淑芬	國企系
85213009	俞旭昇	資管系

課號	學年期	班別	學號	成績
040001	862	0	85212007	80
040001	862	0	85212009	69
040002	862	0	85212007	80