Introduction database system

Purpose of this paper:
Carry out the experiment according to the experimental contents listed in the exercise analysis and experimental guidance of introduction to database system (5th Edition), record the relevant contents, and be familiar with the common operations of the database.

reference material:
Introduction to database system (5th Edition), Wang Shan, SA Shixuan, higher education press;
Introduction to database system (5th Edition), exercise analysis and experimental guidance, Wang Shan, Zhang Jun, higher education press.

Experimental environment:
OS: ubuntu 16.04 server;
DBMS: MariaDB

Example source:
https://dev.mysql.com/doc/index-other.html；
https://github.com/datacharmer/test_db (2020.2.12 copy)；

0 preparation

0.1 query

0.1.1 limit the maximum number of queries

MYSQL:

• Query 10 records from the employee table

SELECT * FROM employees LIMIT 10;

0.2 SQL syntax

0.2.1 back quotation mark, single quotation mark

Backquotes are used to distinguish reserved words, such as for attribute names.
Single quotation marks are used to delimit a string or to escape characters from single quotation marks.

1 database definition and operation language

1.1 database definition language

1.1.1 experimental purpose

Understand and master the database DDL language, and be able to skillfully use SQL DDL statements to create, modify and delete databases, schemas and basic tables.

1.1.2 test contents and requirements

Understand and master the syntax of SQL DDL statements, especially the specific meaning and usage of various parameters; Use SQL statements to create, modify, and delete databases, schemas, and basic tables. Master the debugging methods of common syntax errors in SQL statements.

1.1.3 key and difficult points of experiment

Experimental focus: create database and basic table.
Experimental difficulties: when creating a basic table, select appropriate data types for different columns, and correctly create table level and column level integrity constraints, such as whether column values are allowed to be empty, main code and integrity restrictions. Note: data integrity constraints can be defined when creating a basic table, or you can create a table first and then define integrity constraints. Due to the limitation of integrity constraints, the referenced table must be created first.

1.1.4 test report

1.1.4.1 brief description of experiment contents

This experiment establishes the understanding of employees database, analyzes the relationship and constraints of each table, and analyzes the semantics of each column in the table.

1.1.4.2 database structure

Database name: employees

surface	meaning
departments	Department table
employees	Employee table
dept_emp	Employee department table
dept_manager	Department Manager table
titles	Position table
salaries	wages

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL,
   dept_no      CHAR(4)         NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
); 

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
); 

CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
); 

1.2 basic data query experiment

1.2.1 experimental purpose

Master the basic specification of SQL programming, and skillfully use SQL language to realize basic data query, including single table query, grouping statistical query and connection query.

1.2.2 test contents and requirements

Design various forms query SQL statements and grouping statistics query statements for TPC-H database; Design the connection query of a single watch pin to itself, and design the connection query of multiple tables. Understand and master the characteristics and functions of each word of SQL query statement, write specific SQL query statement according to SQL programming specification, and debug it.
Note: in short, SQL programming specification includes SQL keyword capitalization, table name, attribute name, stored procedure name and other identifiers, mixed case, SQL program writing and compact arrangement.

1.2.3 key and difficult points of experiment

Experimental key points: grouping statistical query, single table self connection query and multi table connection query.
Experimental difficulties: distinguish tuple filter conditions and grouping filter conditions; Determine the connection properties and correctly design the connection conditions.

1.2.4 test content record

1.2.4.1 single table query (query)

• Query department information.

SELECT dept_no, dept_name 
FROM departments;

1.2.4.2 single table query (selection)

• Query the information of all male employees whose employment date is January 1, 1990.

SELECT * 
FROM employees
WHERE hire_date='1990-01-01' 
  AND gender='M';

1.2.4.3 grouping statistics query without grouping filter conditions

• Query the payroll of all employees.

SELECT emp_no, SUM(salary)
FROM salaries
GROUP BY emp_no;

1.2.4.4 grouping statistics query with grouping filter conditions

• Query the payroll of all employees whose payroll is no less than 1 million.

SELECT emp_no, SUM(salary)
FROM salaries
GROUP BY emp_no
HAVING SUM(salary) >= 1000000;

1.2.4.5 two table connection query (common connection)

• Check the positions of all employees who joined in 1990.

SELECT DISTINCT e.emp_no, first_name, last_name, hire_date, title
FROM employees e, titles t
WHERE e.emp_no=t.emp_no 
  AND hire_date
    BETWEEN '1990-01-01'
    AND '1990-12-31';

1.3 advanced data query experiment

1.3.1 experimental purpose

Master the design methods of various advanced queries such as SQL nested query and set query.

1.3.2 test contents and requirements

For TPC-H database, correctly analyze user query requirements, and design various nested queries and set queries.

1.3.3 experimental key points and difficulties

Experimental focus: nested query.
Experimental difficulties: related sub query and multi-layer EXIST nested query.

1.3.4 test content record

1.3.4.1 IN nested query

• Query the salary records of the manager of the finance department.

SELECT s.emp_no, first_name, last_name, salary, 
  s.from_date, s.to_date
FROM salaries s, employees e
WHERE s.emp_no=e.emp_no
AND s.emp_no IN (
    SELECT dm.emp_no
    FROM dept_manager dm, departments d
    WHERE d.dept_name='Finance'
    AND d.dept_no=dm.dept_no
);

1.3.4.2 single layer EXISTS nested query

Note: subqueries with EXISTS predicates may not be equivalent to other forms of subqueries.

• Count the number of employees (once) in the finance department.

Using EXISTS predicate can be understood as finding an employee whose department is financial tuple.

SELECT COUNT(*)
FROM dept_emp de
WHERE EXISTS (
  SELECT *
  FROM departments d
  WHERE d.dept_name='Finance'
  AND d.dept_no=de.dept_no
);

If two tables are used to connect queries, similar semantics can be realized (there are subtle differences in semantics. The former is existential semantics and the latter is counting semantics).

SELECT DISTINCT COUNT(de.emp_no)
FROM dept_emp de, departments d
WHERE de.dept_no=d.dept_no
AND d.dept_name='Finance';

2 security language

3 integrity language

4 trigger

5 database design

6 stored procedure

7 database application development

8 database design and application development

9 database monitoring and performance optimization

10 database recovery technology

11 concurrency control