1. Project background and demand analysis

1.1 project background

E-mail is widely used in enterprises. E-mail is generally used for communication and business between employees and customers. However, due to the lack of awareness of managing e-mail in many enterprises, a large number of e-mail management is chaotic, and there are some hidden dangers of business data security. In order to enable enterprises to apply e-mail efficiently, safely and stably, improve the management, business and customer service level of the company, and improve the efficiency of internal information communication, an e-mail management system database is designed to ensure the efficient operation of internal communication and external business.

1.2 demand analysis

1.2. 1. Information requirements

For enterprises, e-mail management is an important part of management, but the e-mail workload of enterprises is large and complex, and manual processing is very difficult. Therefore, with the help of powerful computer processing ability, people can be freed from the heavy mail management work, and a more accurate, safe and clear management environment.

1.2. 2 functional requirements

Capable of data definition, data manipulation, data control and other processing functions of the database. The specific functions shall include: contact information management and email information management, including the functions of adding, inserting, deleting, updating and querying, reply email identification, and employee and customer contact information query.

1.2. 3 safety and integrity requirements

For the database of e-mail management system, because its main data is e-mail and contact information, which can only be known by the sender, recipient and relevant CC, it is the top priority to do a good job in data security. In addition, the email information of all employees shall be entered, and individual situations shall be designed.

2. Conceptual structure design

Conceptual structure design is the key of the whole database design. It forms a conceptual model independent of specific DBMS by synthesizing, summarizing and abstracting user requirements.
According to the design requirements of e-mail information management database, four entities are abstracted: contact, e-mail, recipient set and CC set. Contact and e-mail are strong entities, and recipient set and CC set are weak entities. Both depend on contact and e-mail. The relationship between the four entities is analyzed as follows:
A contact will send and receive multiple e-mails, and an e-mail may be received by multiple people, so the relationship between contact and e-mail is many to many (m:n);
A contact may belong to multiple recipient sets, and a recipient set may contain multiple contacts, so the relationship between the contact and the recipient set is many to many (m:n);
As above, a contact may belong to multiple CC sets, and a CC set may contain multiple contacts, so the relationship between the contact and the CC set is many to many (m:n);
An e-mail will be received by a collection of recipients (here, the collection of recipients as a whole may contain multiple recipients), and a collection of recipients may receive multiple e-mails, so the e-mail and the collection of recipients are in a many to one (n:1) relationship;
As above, an e-mail will be received by a CC set (here, the CC set as a whole, a set may contain multiple CC people), and a CC set may receive multiple e-mails, so the relationship between e-mail and CC set is many to one (n:1);

2.1 abstracting system entities

Contact person (user ID, user name, Email, telephone, contact address);
E-mail (e-mail ID, e-mail title, sender ID, e-mail sending time, e-mail content, replied e-mail ID);
Recipient collection (recipient ID, mail ID, recipient ID);
CC collection (CC ID, mail ID, recipient ID);

2.2 global E-R diagram

3. Logical structure design

3.1 relationship model

The problem to be solved in the transformation from E-R diagram to relational model is how to transform the entity type and the relationship between entities into relational patterns, and how to determine the attributes and codes of these relational patterns.
Design the mail management database, including four entities: contacts, e-mails, mail_recipients and copy_recipients. The attributes defined for each entity in the relationship mode are as follows:
Contacts table: user id (uid), user name (uname), email (email), telephone (cell phone) and address (address). This is the relationship mode corresponding to the entity "contacts table", and the user id is the candidate code of the relationship, meeting the third paradigm;
Emails table: email id (EID), email title (title), sender id (uid), email send time (create_time), email content (textbody) and reply_eid (reply_eid). This is the relationship mode corresponding to the entity "emails table", and the email id is the candidate code of the relationship, meeting the third paradigm;
mail_ Recipients table: recipient id (mid), mail id (eid), recipient id (uid), which is the relationship mode corresponding to the entity "mail_recipients table", and recipient id is the candidate code of the relationship, meeting the third normal form;
copy_ Recipients table: CC id (cid), email id (eid) and recipient id (uid). This is the relationship mode corresponding to the entity "copy_recipients table". CC id is the candidate code of the relationship, meeting the third paradigm.

3.2 table structure

The database contains four tables, namely contacts, emails, mail_recipients and copy_recipients.

Table structure of contacts table

Table structure of emails table

mail_ Table structure of recipients table

copy_ Table structure of recipients table

4. Physical design and Implementation

4.1 database and table creation

4.1. 1 create database

-- If the database already exists, delete it
DROP DATABASE IF EXISTS EmailManagement;
-- Create database
CREATE DATABASE EmailManagement CHARSET=UTF8;
-- Use database
USE EmailManagement;

4.1. 2 create data table

-- Create data table
-- table 1: contacts

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
uid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
uname VARCHAR(25),
email VARCHAR(50),
cellphone BIGINT(11),
address VARCHAR(100)
);

-- set up CHECK constraint
ALTER TABLE contacts ADD CHECK (cellphone>0);

-- table 2: emails
DROP TABLE IF EXISTS emails;
CREATE TABLE emails(
eid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
title VARCHAR(120),
uid INT(10) UNSIGNED NOT NULL,
create_time DATETIME,
reply_id INT(10),
textbody TEXT,
FOREIGN KEY(uid) REFERENCES contacts(uid)
);

-- table 3: mail_recipients
DROP TABLE IF EXISTS mail_recipients;
CREATE TABLE mail_recipients(
mid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
eid INT(10) UNSIGNED NOT NULL,
uid INT(10) UNSIGNED NOT NULL,
FOREIGN KEY(eid) REFERENCES emails(eid),
FOREIGN KEY(uid) REFERENCES contacts(uid)
);

-- table 4: copy_recipients
DROP TABLE IF EXISTS copy_recipients;
CREATE TABLE copy_recipients(
cid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
eid INT(10) UNSIGNED NOT NULL,
uid INT(10) UNSIGNED NOT NULL,
FOREIGN KEY(eid) REFERENCES emails(eid),
FOREIGN KEY(uid) REFERENCES contacts(uid)
);

4.2 table data insertion

4.2. 1 insert data

-- insert data
INSERT INTO contacts VALUES
(0, "Feng ER", "abc123@126.com", 13288886666, "101, 1f, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Zhang San", "bbc789@126.com", 13288886669, "201, 2f, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Li Si", "tyuio3@126.com", 13288886696, "301, floor 3, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Wang Wu", "func89@126.com", 13288886966, "402, 4th floor, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Zhao Liu", "defc23@126.com", 13288889666, "503, 5 / F, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Zhao Qi", "cda789@126.com", 13288896666, "604, 6th floor, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Zhao Ba", "ertq23@126.com", 13288986666, "705, 7th floor, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Sun Shi", "qwert9@126.com", 13298886666, "806, 8th floor, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Monday", "zxcvb3@126.com", 13289886666, "907, floor 9, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Tuesday", "asdfg9@126.com", 13988886666, "908, floor 9, Boxin international building, Xuehua Avenue, Haidian District, Beijing"),
(0, "Wednesday", "sdfghj@126.com", 19288886666, "909, floor 9, Boxin international building, Xuehua Avenue, Haidian District, Beijing");

4.2. 2 test constraints

-- test CHECK constraint
INSERT INTO contacts VALUES
(0, "Wednesday", "sdfghj@126.com", -1528886666, "909, floor 9, Boxin international building, Xuehua Avenue, Haidian District, Beijing");

– CHECK constraint test results are as follows:
The CHECK constraint is normal. When a negative number is inserted into the cell phone field, the constraint takes effect and an error is reported.

4.2. 3 insert other table data

INSERT INTO emails (eid, title, uid, create_time, textbody) VALUES 
(0, "Epidemic prevention notice", 1, "2020-06-20 09:30:00", "According to the latest situation of epidemic prevention and control, the company established a leading group for epidemic prevention in order to win the prevention and blocking war of group prevention and control"),
(0, "Meeting notice", 2, "2020-06-21 10:00:00", "Notice: the company will hold an employee meeting tomorrow, XXX Personnel must participate!"),
(0, "Fire drill notice", 3, "2020-06-23 09:45:00", "Notice: the company will hold a fire drill tomorrow, XXX Personnel must participate!"),
(0, "Financial reimbursement notice", 4, "2020-06-24 09:45:00", "Notice: the company will hold a presentation on financial reimbursement tomorrow, XXX Personnel must participate!"),
(0, "Rules and regulations notice", 5, "2020-06-25 09:45:00", "Notice: the company will implement the new system tomorrow, XXX Personnel shall check and abide by it in time!"),
(0, "Personnel training notice", 6, "2020-06-26 09:45:00", "Notice: the company will hold corporate culture training tomorrow, XXX Personnel must participate!"),
(0, "Annual travel notice", 7, "2020-06-27 09:45:00", "Notice: the company will hold a one-day tour in Shanghai tomorrow, XXX Personnel must participate!"),
(0, "Project conclusion", 8, "2020-06-28 09:45:00", "Notice: the company will hold the 618 project closing meeting tomorrow, XXX Personnel must participate!");

-- Insert reply message
INSERT INTO emails (eid, title, uid, create_time, reply_id, textbody) VALUES 
(0, "Reply to project conclusion", 9, "2020-06-29 09:45:00", 8, "Yes, I promise to participate!"),
(0, "Personnel training reply", 10, "2020-06-27 11:45:00", 6, "Yes, I promise to participate!"),
(0, "Annual travel reply", 11, "2020-06-28 15:45:00", 7, "Yes, I promise to participate!");

INSERT INTO mail_recipients VALUES
(0, 1, 2),
(0, 1, 3),
(0, 1, 4),
(0, 2, 1),
(0, 4, 3),
(0, 5, 4),
(0, 6, 5),
(0, 7, 6),
(0, 8, 7),
(0, 2, 8),
(0, 8, 9),
(0, 6, 10),
(0, 7, 11);

INSERT INTO copy_recipients VALUES
(0, 1, 5),
(0, 1, 6),
(0, 1, 7),
(0, 1, 8),
(0, 2, 1),
(0, 2, 3),
(0, 2, 4),
(0, 2, 5),
(0, 3, 6),
(0, 3, 7),
(0, 3, 8),
(0, 5, 2);

4.3 query test

– 1. Query the people surnamed Zhao in the contact and display all their information

SELECT * FROM contacts WHERE uname LIKE "Zhao%";

Query results:

– 2. Query the people who replied to the email, and display: user id, name, and the number of emails replied

SELECT 	 e.uid,
		 c.uname,
		 count(*) AS reply_num
FROM 	 contacts AS c INNER JOIN emails AS e ON c.uid = e.uid
WHERE	 e.reply_id IS NOT NULL
GROUP BY e.uid, c.uname;

Query results:

– 3. Query the number of emails with 3 or more CC recipients. It is required to display: email id, email subject and email content

SELECT 	 e.eid,
		 e.title,
		 e.textbody
FROM 	 emails AS e
WHERE 	 e.eid IN (SELECT eid FROM copy_recipients GROUP BY eid HAVING count(*)>=3);

The results are as follows:

– 4. Query the reply time of the replied email. It is required to display: user name, email subject and reply time

SELECT 	 c.uname,
		 e1.title,
		 TIMESTAMPDIFF(HOUR, e2.create_time, e1.create_time) AS 'Response time'
FROM 	 contacts AS c INNER JOIN emails AS e1 ON c.uid = e1.uid
			INNER JOIN emails AS e2 ON e1.reply_id = e2.eid;

The results are as follows:

– 5. Query the amount of e-mail data sent and received by users. It is required to display: user ID, user name, sent quantity and received quantity

SELECT   e.uid,
		 c.uname,
		 count(e.uid) AS 'Shipment quantity',
		 (count(mr.uid) + count(cr.uid)) AS 'Receipt quantity'
FROM 	 contacts AS c LEFT JOIN emails AS e ON c.uid = e.uid
			LEFT JOIN mail_recipients AS mr ON e.eid = mr.eid
			LEFT JOIN copy_recipients AS cr ON e.eid = cr.eid
GROUP BY e.uid, c.uname;

The results are as follows: