CREATE DATABASE CITYOA
ON
(NAME=數據庫邏輯名
FILENAME='數據庫文件路徑與物理名.mdf')
LOG ON
(NAME=日誌文件邏輯名
FILENAME='日誌文件路徑與物理名.ldf')
2.1
CREATE TABLE OA_FUNCTION
(FUNNO Int PRIMARY KEY,
FUNNAME Varchar(50) NOT NULL,
UPFUNNO Int ,
FOREIGN KEY (UPFUNNO) REFERENCES OA_FUNCTION (FUNNO)
)
2.2
CREATE TABLE OA_DEPARTMENT
(DEPTNO Int PRIMARYKEY,
DEPTCODE Varchar(10) NOT NULL UNIQUE,
DEPTNAME Varchar(50) NOT NULL UNIQUE,
LOCATION Varchar(200) ,
POSTCODE CHAR(6)
)
2.3員工表
CREATE TABLE OA_EMPLOYEE
( EMPNO Int PRIMARY KEY,
EMPID Varchar(20) NOT NULL UNIQUE,
PASSWORD Varchar(20) NOT NULL,
NAME Varchar(50) NOT NULL,
SEX Char(2) CHECK(SEX IN ('男','女')) default '男',
AGE Int CHECK(AGE BETWEEN 18 AND 60) DEFAULT 20,
WORKAGE int CHECK(Age>workage+18),
HIREDATE Datetime CHECK(HIREDATE>'2000-10-10'),
LeaveDate Datetime CHECK(LeaveDate>HIREDATE), Salary decimal(10,2) CHECK(BETWEEN 1000 AND 9000),
BONUS Decimal(10,2) CHECK(Salary>bonus),
Mail Varchar(100) ,
Photo Image ,
JobDesc Text ,
DEPTNO int ,
FOREIGN KEY (DEPTNO) REFERENCES OA_Department(DEPTNO)
)
2.4技能表
CREATE TABLE OA_SKILL
SKILLNO Int PRIMARY KEY,
SKILLNAME Varchar(50) NOT NULL UNIQUE
)
2.5員工技能關聯表
CREATE TABLE OA_EMPSKILL
( EMPNO Int ,
SKILLNO Int ,
PRIMARY KEY (EMPNO,SKILLNO),
FOREIGN KEY (EMPNO) REFERNCES EMP (EMPNO),
FOREIGN KEY (SKILLNO) REFERNCES SKILL (SKILLNO)
)
2.6 員工功能關聯表
CREATE TABLE OA_EMPFUNCTION
(
EMPNO Int ,
FUNNO Int ,
PRIMARY KEY (EMPNO,FUNNO),
FOREIGN KEY (EMPNO) REFERENCES EMP (EMPNO),
FOREIGN KEY (FUNNO) REFERENCES OA_FUNCTION (FUNNO)
)
2.1的FK->PK(本表PK)不大清楚是不是這樣有空問下妳們老師。
3. 使用T-SQL語句完成如下修改表的任務
(3.1) 為員工表OA_EMPLOYEE增加列：MobilePhone，類型為varchar(50), 且為唯壹
ALTER TABLE OA_EMPLOYEE
ADD COLUMN MobilePhone varchar(50) UNIQUE
(3.2) 將員工表OA_EMPLOYEE的Mail列修改為Varchar(200)
ALTER TABLE OA_EMPLOYEE ALTER COLUMN Mail Varchar(200)
(3.3) 將員工表OA_EMPLOYEE的JobDesc列刪除
ALTER TABLE OA_EMPLOYEE DROP COLUMN JobDesc
(3.4) 刪除技能表OA_SKILL字段SKILLNAME上的唯壹約束
ALTER TABLE OA_SKILL DROP constraint 約束名（約束名我也不知道是什麽。。。）
(3.5) 為員工表OA_EMPLOYEE的字段Mail增加唯壹約束
ALTER TABLE OA_EMPLOYEE ADD Mail UNIQUE
註：修改任務不用截屏，直接寫出T-SQL語句即可。
4. 使用T-SQL語句完成刪除表的任務
(4.1) 刪除員工表
(4.2) 刪除員工功能關聯表