ER-MODER繪圖程式＋範例練習－百花

繪圖程式：ＴＤＭ

１．１Ｖ１　ＰＦＫ

２．１Ｖ１　ＦＫ

３．ＭＶＮ

家長是弱實體

自動產生ＳＱＬ　script

點選

/*
Created       2019/3/21
Modified       2019/3/25
Project
Model
Company
Author
Version
Database       MS SQL 2005
*/

Create table [學生]
(
   [學號] Char(4) NOT NULL,
   [姓名] Nvarchar(10) NOT NULL,
   [性別] Bit NOT NULL,
   [電話] Varchar(16) NULL,
   [生日] Datetime NULL,
Primary Key ([學號])
)
go

Create table [課程]
(
   [課程編號] Char(5) NOT NULL,
   [名稱] Nvarchar(20) NULL,
   [學分] Integer NULL,
Primary Key ([課程編號])
)
go

Create table [班級]
(
   [學號] Char(4) NOT NULL,
   [課程編號] Char(5) NOT NULL,
   [上課時間] Datetime NOT NULL,
   [上課教室] Varchar(1) NULL,
   [教授編號] Char(4) NOT NULL,
Primary Key ([學號],[課程編號],[教授編號])
)
go

Create table [教授]
(
   [教授編號] Char(4) NOT NULL,
   [職稱] Varchar(10) NULL,
   [科系] Varchar(5) NULL,
   [身分證字號] Char(10) NULL,
Primary Key ([教授編號])
)
go

Create table [員工]
(
   [身分證字號] Char(10) NOT NULL,
   [姓名] Varchar(12) NULL,
   [電話] Char(12) NULL,
   [薪水] Money NULL,
   [保險] Money NULL,
   [扣稅] Money NULL,
   [城市] Nchar(4) NULL,
   [街道] Nchar(20) NULL,
Primary Key ([身分證字號])
)
go

Create table [測驗]
(
   [測驗編號] Char(4) NOT NULL,
   [測驗名稱] Nvarchar(20) NOT NULL,
   [種類] Char(1) NOT NULL,
   [課程編號] Char(5) NULL,
Primary Key ([測驗編號])
)
go

Create table [測驗結果]
(
   [學號] Char(4) NOT NULL,
   [測驗編號] Char(4) NOT NULL,
   [成績] Integer NULL,
   [日期] Datetime NULL,
Primary Key ([學號],[測驗編號])
)
go

Create table [地址]
(
   [地址流水號] Bigint Identity NOT NULL,
   [地址] Nvarchar(30) NOT NULL,
   [學號] Char(4) NOT NULL,
Primary Key ([地址流水號],[學號])
)
go

Create table [家長]
(
   [姓名] Nvarchar(20) NOT NULL,
   [關係] Nvarchar(10) NULL,
   [學號] Char(4) NOT NULL,
Primary Key ([姓名],[學號])
)
go

Alter table [班級] add foreign key([學號]) references [學生] ([學號]) on update no action on delete no action
go
Alter table [測驗結果] add foreign key([學號]) references [學生] ([學號]) on update no action on delete no action
go
Alter table [地址] add foreign key([學號]) references [學生] ([學號]) on update no action on delete no action
go
Alter table [家長] add foreign key([學號]) references [學生] ([學號]) on update no action on delete no action
go
Alter table [班級] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [測驗] add foreign key([課程編號]) references [課程] ([課程編號]) on update no action on delete no action
go
Alter table [班級] add foreign key([教授編號]) references [教授] ([教授編號]) on update no action on delete no action
go
Alter table [教授] add foreign key([身分證字號]) references [員工] ([身分證字號]) on update no action on delete no action
go
Alter table [測驗結果] add foreign key([測驗編號]) references [測驗] ([測驗編號]) on update no action on delete no action
go