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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10395457/viewspace-1003230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10395457/viewspace-1003230/