数据库课程设计源代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
use kjq111007307
/*创建一个部门信息表
包含“部门号，部门名，部门经理，人数”属性列*/
create table department
(depart_no char(2)primary key,
depart_name char(30)not null,
depart_manage char(6)not null,
depart_people int not null
)
/*创建一个职位信息表
包含“职位，基本薪资,福利,失业保险,住房公积金”属性列*/ create table position
(pos char(30)primary key,
basesalary float not null,
benefits float not null,
insurances float not null,
housing_funds float not null
)
/*创建一个职工信息表
包含"职工号，职工名，性别，年龄，学历，部门号，职位"属性列*/ create table staff_message
(staff_no char(4)primary key,
staff_name char(10)not null,
staff_sex char(2)check(staff_sex in('男','女')),
staff_age int not null,
staff_edu char(10)not null,
staff_dep char(2)not null,
staff_job char(30)not null,
foreign key(staff_dep)references department(depart_no), foreign key(staff_job)references position(pos)
)
/*创建一个员工考勤表
包含“职工号，年月，迟到，缺勤，加班”属性列*/
create table staff_days
(staff_no char(4),
month_date char(6),
staff_late int not null,
staff_absent int not null,
workoverdays int not null,
primary key(staff_no,month_date)
)
/*创建一个薪资表
包含“职工号，年月，奖金，罚金，真实薪资”属性列*/
create table salary
(staff_no char(4),
month_date char(6),
addsalary float not null,
subsalary float not null,
relsalary float not null,
primary key(staff_no,month_date)
)
create index salary_index on salary(month_date asc,staff_no asc); /*部门表信息的录入*/
/*经理室*/
insert
into department
values('01','manage_department','王栋','1');
/*财务科*/
insert
into department
values('02','financial_department','张鹏','3');
/*技术科*/
insert
into department
values('03','plan_department','代淑英','5');
/*销售科*/
insert
into department
values('04','market_department','金加容','6');
/*职位表信息的录入*/
/*经理*/
insert
into position
values('manager','4500','1125','-45','-36');
/*副经理*/
insert
into position
values('assistant_manager','4000','1000','-40','-32');
/*办事*/
insert
into position
values('clerk','3500','875','-35','-28');
/*助理*/
insert
into position
values('assistant','2000','500','-20','-16');
/*职工信息表信息的录入*/
insert
into staff_message
values('0101','王栋','男','38','硕士','01','manager');
insert
into staff_message
values('0201','张鹏','男','35','硕士','02','manager');
insert
into staff_message
values('0202','程雷','男','30','本科','02','clerk');
insert
into staff_message
values('0203','王晶晶','女','29','本科','02','assistant');
insert
into staff_message
values('0301','代淑英','女','35','硕士','03','manager'); insert
into staff_message
values('0302','刘燕','女','30','本科','03','assistant_manager'); insert
into staff_message
values('0303','杨浩','男','27','本科','03','clerk');
insert
into staff_message
values('0304','程伟','男','31','本科','03','clerk');
insert
into staff_message
values('0305','唐琦','女','25','本科','03','assistant');
insert
into staff_message
values('0401','金加容','女','34','本科','04','manager'); insert
into staff_message
values('0402','吴辉','男','35','本科','04','assistant_manager'); insert
into staff_message
values('0403','陈睿','男','32','本科','04','clerk');
insert
into staff_message
values('0404','万莉','女','36','本科','04','clerk');