Personnel Management System Database Setup

Introduction

In this project, you will learn how to create a personnel management system database using MySQL. The database will store information about employees, their work units, and residential details.

👀 Preview

MariaDB [companySys]> DESC company;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| company_name | varchar(50) | NO   | PRI | NULL    |       |
| city         | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command
• How to create a database called companySys
• How to create three tables: company, work, and employ
• How to set appropriate primary key and foreign key constraints for each table
• How to insert data into the tables

🏆 Achievements

After completing this project, you will be able to:

• Understand the basics of database creation and management
• Implement CRUD (Create, Read, Update, Delete) operations on a relational database
• Establish relationships between tables using primary and foreign keys
• Manage and manipulate data in a personnel management system database

Access MySQL and Create the Database

In this step, you will learn how to access MySQL using the sudo command and create the companySys database.

1. Open a terminal and run the following command to start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Open the creatComDatabase.sql file.
4. In the creatComDatabase.sql file, create the companySys database with the UTF-8 character set:

CREATE DATABASE IF NOT EXISTS companySys DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. Use the companySys database:

USE companySys;

Create the Tables

In this step, you will create the three tables: company, work, and employ.

1. Open the creatComDatabase.sql file and create the following tables in the file in order.
2. Create the company table:

CREATE TABLE IF NOT EXISTS company (
  company_name varchar(50) NOT NULL,
  city varchar(10),
  PRIMARY KEY (company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Create the work table:

CREATE TABLE IF NOT EXISTS work (
  per_name varchar(10) NOT NULL,
  company_name varchar(50),
  salary decimal(10,2),
  PRIMARY KEY (per_name),
  FOREIGN KEY (company_name) REFERENCES company(company_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. Create the employ table:

CREATE TABLE IF NOT EXISTS employ (
  id int NOT NULL,
  person_name varchar(10),
  street varchar(100),
  city varchar(10),
  PRIMARY KEY (id),
  FOREIGN KEY (person_name) REFERENCES work(per_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Insert the Data

In this step, you will insert the data into the three tables.

1. Open the creatComDatabase.sql file and continue from the previous step to add the following sql statement.
2. Insert data into the company table:

INSERT INTO company (company_name, city) VALUES
  ('ABC Company', 'New York'),
  ('LabEx', 'London'),
  ('Company 3', 'Paris');

3. Insert data into the work table:

INSERT INTO work (per_name, company_name, salary) VALUES
  ('John', 'ABC Company', 5000.00),
  ('Jane', 'LabEx', 6000.00),
  ('Mary', 'Company 3', 5500.00);

4. Insert data into the employ table:

INSERT INTO employ (id, person_name, street, city) VALUES
  (1, 'John', '123 Main Street', 'New York'),
  (2, 'Jane', '456 Park Avenue', 'London'),
  (3, 'Mary', '789 Broadway', 'Paris');

Verify the Database

In this step, you will verify the database by describing the tables.

1. In the MySQL client, run the following command to execute the SQL script:

MariaDB [(none)]> SOURCE /home/labex/project/creatComDatabase.sql;

2. Describe the company table:

DESC company;

MariaDB [companySys]> DESC company;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| company_name | varchar(50) | NO   | PRI | NULL    |       |
| city         | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

3. Describe the employ table:

DESC employ;

MariaDB [companySys]> DESC employ;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| person_name | varchar(10)  | YES  | MUL | NULL    |       |
| street      | varchar(100) | YES  |     | NULL    |       |
| city        | varchar(10)  | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

4. Describe the work table:

DESC work;

MariaDB [companySys]> DESC work;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| per_name     | varchar(10)   | NO   | PRI | NULL    |       |
| company_name | varchar(50)   | YES  | MUL | NULL    |       |
| salary       | decimal(10,2) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

You have now successfully created the personnel management system database and inserted the data. Congratulations!

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.