数据库课程设计源代码.docVIP

. . 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,硕士,