final design of OA project part 1

if exists (select * from sysdatabases where name='myoa')
drop database myoa
go

create database myoa

use myoa
go

if exists (select * from sysobjects where name='T_subjects' )/*公告欄目*/
drop table T_subjects
go
create table T_subjects
(
subjectid int identity primary key,
subjectname varchar(50)not null
)

insert into T_subjects values('企業文化學習')
insert into T_subjects values('技術知識')
insert into T_subjects values('公司內部章程')

if exists (select * from sysobjects where name='T_announcement' )/*公告*/
drop table T_announcement
go

create table T_announcement
(
announcementid int identity primary key,
announcementname varchar(50) not null,
announcementSubject int not null,
adduser int not null,
adddepartment int not null,
addtime varchar(50)not null,
deadline varchar(50)not null
)
select * from T_announcement
insert into T_announcement values('企業新知識文化學習',1,2,1,'2008-04-01',4,'有關企業新知識文化的學習，已經向個部門主管下發了有關企業新知識文化的學習資料，請各部門積極學習有關資料，提升員工在企業內部對企業文化的學習')
alter table T_announcement add constraint fk_adduser foreign key (adduser) references T_user(userid)
alter table T_announcement add constraint fk_adddepartment foreign key (adddepartment) references T_department(departmentid)
alter table T_announcement add constraint fk_announcementSubject foreign key (announcementSubject) references T_subjects(subjectid)
alter table T_announcement add column
select departmentname from T_department,T_user where T_user.userdepartment=T_department.departmentid and T_user.username='admin'

if exists (select * from sysobjects where name='T_department')/*部門表*/
drop table T_department
go
create table T_department
(
departmentid int identity primary key,
departmentname varchar(50)not null,
)
alter table T_department add departmentgrade int
alter table T_department add constraint fk_departmentgrade foreign key(departmentgrade) references T_departmentgrade(departmentgradeid)

insert into T_department values('生產部')

if exists (select * from sysobjects where name='T_user')/*使用者表*/
drop table T_user
go
create table T_user
(
userid int identity primary key,
username varchar(20)not null,
userage varchar(4)not null,
useraddress varchar(100)not null,
userdepartment int not null,
userposition int not null
)
select * from T_user
update T_user set usersex='女',useridenti='610101198112150512' where userid=1
alter table T_user add usersex varchar(2)
alter table T_user add useridenti varchar(18)
alter table T_user add constraint ck_useridenti check(len(useridenti)=15 or len(useridenti)=18)
select * from T_user
alter table T_user add constraint ck_username check (len(username)<20)
alter table T_user add constraint ck_userage check(userage>1 and userage<100)
alter table T_user add constraint ck_userage1 check(userage like '[0-9]%[0-9]')
insert into T_user values('sss','23','xa',1,1,'aaa')
insert into T_user values('admin0','25','xian',1,1,'aaa')

alter table T_user add constraint fk_userdepartment foreign key (userdepartment) references T_department(departmentid)
alter table T_user add constraint fk_userposition foreign key (userposition) references T_position(positionid)

if exists (select * from sysobjects where name='T_position')/*職位表*/
drop table T_position
go
create table T_position
(
positionid int identity primary key,
positionname varchar(30)not null
)

insert into T_position values('普通員工')

create view View_announcement as
select a.announcementid,a.announcementname,a.addtime,a.deadline,b.subjectname,c.username,d.departmentname
from T_announcement as a,T_subjects as b,T_user as c,T_department as d where a.announcementSubject=b.subjectid and a.adduser=c.userid and a.adddepartment=d.departmentid

if exists (select * from sysobjects where name='T_airplane')/*飛機時刻表*/
begin drop table T_airplane
end
go
create table T_airplane
(
airplaneid int identity primary key,
airplanename varchar(30) not null,
startpoint varchar(30) not null,
endpoint varchar(30) not null,
startdate datetime not null,
enddate datetime not null,
aircompany varchar(30) not null,
airplanetype varchar(30) not null,
price money not null
)

if exists (select * from sysobjects where name='T_train')/*列車時刻表*/
begin drop table T_train
end
go
create table T_train
(
trainid int identity primary key,
trainname varchar(30)not null,
startpoint varchar(30)not null,
endpoint varchar(30)not null,
startdate datetime not null,
enddate datetime not null,
traintype varchar(30) not null,
price money
)

if exists (select * from sysobjects where name='T_province')/*省份*/
begin drop table T_province
end
go
create table T_province
(
provinceid int identity primary key,
provincename varchar(30)not null
)

if exists (select * from sysobjects where name='T_post')/*郵政編碼*/
begin drop table T_post
end
go
create table T_post
(
postid int identity primary key,
provinceid int not null,
city varchar(20) not null,
postcode varchar(7) not null,
areano varchar(4)not null
)
alter table T_post add constraint fk_province
foreign key (provinceid) references T_province(provinceid)

if exists (select * from sysobjects where name='T_phone')/*電話*/
begin drop table T_phone
end
go
create table T_phone
(
phoneid int identity primary key,
type varchar(30)not null,
departmentid int not null,
phoneno varchar(20)not null
)

alter table T_phone add constraint fk_phone foreign key (departmentid)
references T_department(departmentid)

if exists (select * from sysobjects where name='T_magzinesort')/*報刊分類*/
begin drop table T_magzinesort
end
go
create table T_magzinesort
(
magzinesortid int identity primary key,
magzinesortname varchar(50) not null
)

alter table T_magzinesort add managemagzineuserid int
alter table T_magzinesort add constraint fk_magzu foreign key (managemagzineuserid) references T_user(userid)
select * from T_magzinesort

if exists (select * from sysobjects where name='T_magzine')/*報刊*/
begin drop table T_magzine
end
go
create table T_magzine
(
magzineid int identity primary key,
magzinename varchar(30)not null,
magzinsortid int not null,
adduserid int not null,
adddepartmentid int not null,
adddate datetime not null
)

select * from T_magzine
alter table T_magzine add constraint fk_magzinesortid foreign key (magzinsortid) references T_magzinesort(magzinesortid)
alter table T_magzine add constraint fk_adduserid foreign key (adduserid) references T_user(userid)
alter table T_magzine add constraint fk_adddepartmentid foreign key(adddepartmentid) references T_department(departmentid)

[@more@]