Resource download address ：https://download.csdn.net/download/sheziqiong/85821146
Resource download address ：https://download.csdn.net/download/sheziqiong/85821146

One 、 introduction

1.1 Purpose of writing

Workers' wage system , Record workers' wages and export data .

1.2 The background that

It is still a traditional manual method for workers to pay wages on construction sites , Prone to errors and data loss , The database system can effectively manage and store data , Reduce operation .

1.3 Expected readers and reading suggestions

Suitable for software development members and project managers .

1.4 Division of labor

Surilog , Bai Songfu ： Demand analysis

Donghongyi ： Conceptual structural design

Ni Anxiang ： Logical structure design

Leizhijie ： Database programming

Two 、 Mission Overview

2.1 The goal is

Record workers' wages and generate a batch handling template for paying wages .

2.2 User characteristics

The project manager can add, delete, check and modify employee information and salary .

Employees can view personal information and salary .

The database administrator runs and maintains the database .

2.3 Assumptions and constraints

General constraints 、 Assumptions and requirements for users .

3、 ... and 、 Database requirements analysis

For existing systems （ Including automatic or manual ） Make a brief analysis .

3.1 Data item / Data structure analysis

The financial system shares 5 Tables , It can be roughly divided into four categories according to functions ：

1： Employee information , Include employees surface .

2： Public information , Include bank Table and fdl_city surface .

3： Salary information , Include salary surface .

4： Payment information , Include payment surface .

analysis ：

employees staff from （eno,eaccount,ename,cnaps_code,id,email,phone）7 Data items consist of

eno Employee number number(7) Only identified employees ,

eaccount Employee bank account number VARCHAR2(19)19 A bank card number Each employee registers a bank card account number ,

ename Employee name VARCHAR2(12) Non empty ,

cnaps_code Joint line number VARCHAR2(12) 12 Bit interlink No Non empty reference bank surface ,

id City Numbers number(10) Non empty reference biaofdl_city surface ,

email E-mail VARCHAR2(20) ,

phone Mobile phone VARCHAR2(17)

salary Wages from （eno,year_month,amount） Three data items make up

eno Employee number number(7) reference employees surface ,

year_month Salary date date , Indicates the salary of the current month ,

amount Wages number(6) ,

eno and year_month Determine the salary of an employee for the current month together .

bank Bank （cnaps_code,bname,branch）

cnaps_code Joint line number VARCHAR2(12) 12 position The only branch that identifies the bank ,

bname Bank name VARCHAR2(20) , Non empty ,

branch Branch name VARCHAR2(50) , Non empty

biaofdl_city（ID,serial_no,l_name,parent_id,l_level）

id Total number number(10) The only identified region ,

serial_no Independent number varchar2(25) , Non empty ,

l_name Name of province or city varchar2(50) , Non empty ,

parent_id The province to which the city belongs varchar2(25) , Non empty ,

l_level Hierarchy number(4) , Non empty

payment Payment information （Account,purposes,Currency,branch,settlement_method,business_types）

Account Payment account number VARCHAR2(19) Information that uniquely determines payment ,

purposes purpose VARCHAR2(10) , Non empty ,

Currency currency VARCHAR2(10) , Non empty ,

branch Paying Branch VARCHAR2(50) , Non empty ,

settlement_method Settlement method VARCHAR2(4) , Non empty ,

business_types Business type varchar2(10) , Non empty

Through this 5 A watch , We can see that , The main contents involved in the whole financial system include : Management of employee information , Including banks and regions , Salary management .

3.2 Data definition analysis

The definition of the table ：

create table employees(
eno number(7) not null PRIMARY KEY,
eaccount VARCHAR2(19) not null,
ename VARCHAR2(12) not null,
cnaps_code VARCHAR2(12) not null references bank(cnaps_code),
id number(10) not null references fdl_city(id),
email VARCHAR2(20),
phone VARCHAR2(17));
create table bank(
cnaps_code VARCHAR2(12) primary key,
bname VARCHAR2(20) not null,
branch VARCHAR2(80)not null);
CREATE TABLE fdl_city(
id number(10) not null PRIMARY KEY,
serial_no varchar2(25) NOT NULL ,
l_name varchar2(50) NOT NULL,
parent_id varchar2(25) NOT NULL ,
l_level number(4) NOT NULL
);
create table salary(
eno number(7) not null references employees(eno),
year_month date not null,
amount number(6),
primary key(eno,year_month));
create table payment(
Account VARCHAR2(19) not null primary key,
purposes VARCHAR2(10) not null,
Currency VARCHAR2(10),
branch VARCHAR2(50),
settlement_method VARCHAR2(4),
business_types varchar2(10)
);
insert into bank values(402191030498,' Rural credit cooperatives ',' Inner Mongolia Hohhot Jingu Rural Commercial Bank Co., Ltd. Chuangye road banking office ');
insert into fdl_city values ('1', '11', ' The Beijing municipal ', '0', '1');
insert into payment values(471901379510902,' Labor income ',' RMB ',' Hohhot Branch ',' Ordinary ');

3.3 Data manipulation analysis

City information and Bank branch information should be entered in advance ,

Employee information 、 The addition, deletion and query of salary and payment information are implemented by the manager .

3.4 Data integrity analysis

Entity integrity ：

• employees surface eno Primary key , Only identified employees
• salary surface eno and year_month Primary key , Determine the monthly salary of employees together
• bank surface cnaps_code Primary key , Only certain sub branch
• biaofdl_city surface id Primary key , The only certain city
• payment surface Account Primary key , Uniquely identify payment information

Referential integrity ：

• employees in id reference biaofdl_city in id,cnaps_code reference bank Medium cnaps_code, The employee's city and bank must be present with the table biaofdl_city And table bank Medium .

• salary Medium eno reference employees Medium eno, Salaried employees must be in the employee table .

User defined integrity ：

• employees Of eno,eaccount,ename,id,cnaps_code Not empty , Guarantee that the salary can be sent out .
• salary Of eno,year_month Not empty , Guarantee if there is a salary , There must be a record , Whose monthly salary .
• bank Of cnaps_cod,ebank,branch Not empty , Ensure that there is an associated bank number and the corresponding bank name , Branch name .
• biaofdl_city Of * Not empty , Make sure you have all the information about the region , Including numbering , Independent number , The province to which the city belongs , Level .

3.5 Conceptual structural design

E-R chart ：

• explain ： An employee can only register one bank card and the corresponding bank , One bank can handle bank cards of multiple employees , Corresponding relation n：1.
• An employee can only have one location , A city can accommodate many employees , Corresponding relation n：1.
• An employee can have more than one salary , A salary belongs to only one person , Corresponding relation 1：n.

3.6 Logical structure design

logical model

relational model ：

3.7 Physical structure design

SID： Use the name orcl Of SID

Table space ：C:\tbspace\FINANCIAL_SYSTEM_TBSPACE

Four 、 Detailed description of business functions

4.1 Subsystem （ Module one ）

4.1.1 Business function description

Add, delete, check and modify employee information , Salary increase, deletion, check and modification , Add, delete, check and modify the payment method , Payroll export .

4.1.2 Business flow chart

4.1.3 Topic description and use case view

4.1.4 Use case description

• Delete employee / Wages / Payment information ： Delete information from the database .
• Increase staff / Wages / Payment information ： Increase staff / The salary information is transferred to the database .
• Modify employees / Wages / Payment information ： Modify the information in the database .
• Look at employees / Wages / Payment information ： To employees / View salary information
• Sign in ： Connect to database , Log in to the system .
• Sign out ： disconnect , Exit the system .
• Export batch handling template ： Export to... Available in the bank handling system Excel form

4.1.4 Use case name 1

【 Use case function description 】

To insert information into the database , Realize information entry .

【 Operation description 】

Enter employee information

Click the insert button

【 Activity diagrams 、 Sequence diagram or collaboration diagram 】 （ Options ）

【 Interface prototypes 】 （ Options ）

4.1.4 Use case name 2

【 Use case function description 】

Export batch handling template Excel file , It is used to import the batch handling function of the banking system .

【 Operation description 】

Enter start and end time

Enter the payment account number

Enter the name of the exported file

Click the OK button

【 Activity diagrams 、 Sequence diagram or collaboration diagram 】 （ Options ）

【 Interface prototypes 】（ Options ）

Resource download address ：https://download.csdn.net/download/sheziqiong/85821146
Resource download address ：https://download.csdn.net/download/sheziqiong/85821146