Database Design Implementation

First, Experiment introduction 1.1 experimental content

This section of the course on the Personnel Management System database design and implementation of the table.

1.2 Experimental Knowledge points

• E-r Chart
• MySQL Build Table
• MySQL Inserting data

1.3 Experimental environment

• MySQL 5.5.50
• XFCE Terminal

Second, the Experiment Step 2.1 system E-r diagram

According to the system function module, the E-R diagram is as follows:

The analysis E-r diagram shows:

• The relationship exists between the administrator and the announcement two entities that 一对多 an administrator can post multiple announcements;
• The relationship exists between department and employee two entities, that 一对多 is, one department can have multiple employees, and one employee belongs to only one department;
• Employees and positions the relationship exists between two entities 多对一 , that is, an employee holds only one position.

Convert to Table:

1. Administrator table (Administrator ID, user name, password, user role), 管理员 id for主键
2. Announcement form (announcement ID, title, content, Administrator ID, release date), 公告 id primary key, 管理员 id for外键
3. Department Table (Department ID, name, description), 部门 id for主键
4. Job table (Job ID, name, description), 职位 id for主键
5. Employee table (Employee ID, name, gender, phone number, mailbox, address, education, birthday, Department ID, job ID), 员工 id primary key, 部门 id for 外键 , 职位 id外键

2.2 Database Preparation

This session uses the MySQL database. Start MySQL First:

$ sudo service mysql start

Then enter the following command under the terminal to go to the MySQL database (-u means the user name, for example, here the root,-p represents the password, there is no password is omitted):

$ mysql -u root

Create hrms_db a database as a personnel management system.

create database hrms_db;

2.3 Build Table 2.3.1 Administrator Table Admin_tb

To build a table statement:

 create table admin_tb (admin_id int (11) not null auto_increment, username varchar ( 20) not null, password varchar (20) not null, role_name varchar (20) not null  Default key (admin_id));     

Insert data:

insert into admin_tb(username,password,role_name) values(‘superadmin‘,‘123456‘,‘superadmin‘),(‘admin1‘,‘123456‘,‘normaladmin‘);

2.3.2 Notice Form POST_TB

To build a table statement:

CreateTable POST_TB (post_idInt (one) not null auto_increment, title varchar (.) not  null, content text  Not null, admin_id int (one) is not  null, create_date date is not null, primary key ( post_id), Foreign Key (admin_id) references Admin_tb (admin_id));        

Insert data:

insert into post_tb(title,content,admin_id,create_date) values(‘Leave notice‘,‘Please pay attention to holiday safety!‘,1,‘2017-4-30‘);

2.3.3 Department Table DEPT_TB

To build a table statement:

create table dept_tb(    dept_id int(11) not null auto_increment, dept_name varchar(50) not null, dept_description varchar(200) not null, primary key(dept_id));

Insert data:

insert into dept_tb(dept_name,dept_description) values(‘course‘,‘make courses‘),(‘development‘,‘Software development‘);

2.3.4 Job List Position_tb

To build a table statement:

create table position_tb(    pos_id int(11) not null auto_increment, pos_name varchar(50) not null, pos_description varchar(200) not null, primary key(pos_id));

Insert data:

insert into position_tb(pos_name,pos_description) values(‘Java course staff‘,‘make Java courses‘),(‘front-end engineers‘,‘responsible for front-end development‘);

2.3.5 Employee Table EMPLOYEE_TB

To build a table statement:

CreateTable EMPLOYEE_TB (emp_idvarchar20)NotNULL, Emp_namevarchar50)NotNULL, Sexvarchar10)NotNULL, phonevarchar20)NotNULL, emailvarchar50)NotNULL, Addressvarchar100)not null, education varchar ( Span class= "Hljs-number" >50) not null, birthday date not null, dept_id int (11) not null, pos_id int (11) not null, primary key (emp_id), foreign key (dept_id) references dept_tb (dept_id), foreign key (pos_id) references position_tb (pos_id));       

Insert data:

INSERT into EMPLOYEE_TB values (' 10001 ',' Tom ',' Male ',' 18211234567 ',' 524123456@qq. com ',' Chengdu ',' Bachelor ',' 1990-4-30 ',1,1), (' 10002 ',' Jack ',' Male ',' 18211234568 ',' 524123457@qq. com ',' Chongqing ',' Master ',' 1986-9-2 ',1,1), ( ' 10011 ',  ' female ',  ' 18211234580 ', Span class= "hljs-string" > ' 524198757 @qq. com ',  ' Chengdu ', Span class= "hljs-string" > ' Bachelor ',  ' 1991-11-21 ', 2,2), ( ' 10012 ',  ' Anny ',  ' 18211237580 ',  @qq. com ',  ' Chengdu ', 2,2);    

Iii. Summary of the experiment

Database Design Implementation