22DataIntegrity

Data Integrity

Outline

Data integrity ? What is it ?
Consistency constraints
Verification of consistency constraints
ROLLBACK and COMMIT statements
Backup and recovery

The term: data integrity, refers to the overall completeness, accuracy and consistency of data.

It can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning, that data is intact and unchanged.

A term: data integrity, also refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle.

It is a critical aspect to the design, implementation and usage of any system which stores, processes or retrieves data.

Data integrity is the opposite to data corruption, which is a form of data loss.

The overall intent of any data integrity technique is the same: to ensure, that data is recorded exactly as intended and upon later retrieval, to ensure the data is the same as it was when it was originally recorded.

Data integrity ? What is it ?

A term data integrity refers to the overall completeness, accuracy and consistency of data
This can be indicated by the absence of alteration between two instances or between two updates of a data record, meaning data is intact and unchanged
A term data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle
Data integrity is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data
Data integrity is the opposite to data corruption, which is a form of data loss
The overall intent of any data integrity technique is the same:
to ensure, that data is recorded exactly as intended and upon later retrieval, to ensure, that data is the same as it was when it was originally recorded

Data integrity ? What is it ?

In short, data integrity aims to prevent the unintentional changes to information

Data Integrity

Consistency constraints

A consistency constraint is a property, that is always valid in a fragment of the real world modelled by a database
A consistency constraint is a condition, that must be satisfied by every persistent state of a database
For example:
- an attribute student-number uniquely identifies each student
- a budget of a small ARC grant cannot exceed 10K
- a value of attribute date-of-birth can be unknown
- an employee is a member of precisely one department
- a salary of full professor is in a range from x to y

Consistency constraints

Key constraint: primary and candidate key constraint

 
STUDENT(snum, first-name, last-name, date-of-birth, medicare-num, degree ) 
primary key= (snum)
candidate key 1 = (first-name, last-name, date-of-birth)
candidate key 2 = (medicare-num)

SUBJECT(code, title, credits)
primary key = (code)
candidate key = (title)

Consistency constraints

Referential integrity constraint: foreign key

ENROLMENT(snum, code, edate)
primary key = (snum, code, edate)
foreign key 1 = (snum) references STUDENT(snum)
foreign key 2 = (code) references SUBJECT(code)

COUNTRY(name)
primary key = (name)
CUSTOMER(cnum, first-name, last-name, country-name)
primary key: (cnum)
foreign key country-name references COUNTRY(name)

Another consistency constraint, that can be enforced with CREATE TABLE and ALTER TABLE statements, is a constraint, that determines whether a value in a column is mandatory or optional. It is commonly called as: NULL/NOT NULL constraint.

For example, all columns, that belong to a primary key constraint must have the values, it means, that both column must NOT be NULL.

It is possible to determine through NULL, NOT NULL clause attached to a specification of a column whether the values are mandatory or optional.

For example, see a specification of: degree column, in a relational table: STUDENT.

Yet another consistency constraint, that can be enforced by CREATE TABLE and ALTER TABLE statements, is a column type constraint. A column type constraint, determines a set of values, that can be used in a given column, in a given relational table.

For example, a column student number (s num) is a sequence of digits, maximum 7 digits long.

Yet another consistency constraint, that can be enforced by CREATE TABLE and ALTER TABLE statements is a domain constraint. A domain constraint, precisely determines a set of values that can be included in a given column.

For example, a column: credits, in a relational table: SUBJECT, can take only two values: 6 or 12.

Consistency constraints

NULL/NOT NULL constraint


STUDENT(snum, first-name, last-name, date-of-birth, medicare-num, degree ) 
degree    ...    NOT NULL

Attribute type constraint


STUDENT(snum, first-name, last-name, date-of-birth, medicare-num, degree ) 
snum   DECIMAL(7)  ...

Domain constraint


SUBJECT(code, title, credits)
credits IN (6, 12)

There exists many, many other types of consistency constraints, that cannot be enforced with CREATE TABLE or ALTER TABLE statements. For example, a numerical constraint saying, that the total number of rows in a relational table: EMPLOYEE, must be less than 100.

Another type of consistency constraint is an exclusion constraint. An exclusion constraint enforces a rule, saying, that a student cannot be in the same moment undergraduate and postgraduate student.

Another type of consistency constraint is a generalisation of referential integrity constraint over several relational tables. It is also called as a distributed multi-table referential integrity constraint. For example, a column student number (s num) in a relational table: SCHOLARSHIP, references either a column: s num in a relational table: POSTGRADUATE STUDENT or a column: s num, in a relational table: UNDERGRADUATE STUDENT.

A subset constraint is another type of consistency constraint, that cannot be enforced with CREATE TABLE or ALTER TABLE statements. For example, all values in a column: city, in a relational table: PROJECT, are included in a set of values in a column: city, in a relational table: EMPLOYEE. It means, that only employees living in the same city, as a project is located at, can be involved in the project.

It is possible to find many, many other constraints, that reflect the properties of the real world in data stored in a database.

Consistency constraints

Other constraints

Numerical constraint: total number of rows in a table EMPLOYEE is less than 1000
Exclusion constraint: a student cannot be in the same moment undergraduate and postgraduate student
Distributed (multitable) referential integrity constraint:

			  
UNDERGRADUATE-STUDENT(snum, first-name, last-name, date-of-birth) 
POSTGRADUATE-STUDENT(snum, first-name, last-name, date-of-birth) 
SCHOLARSHIP(snum, amount)
SCHOLARSHIP.snum references either UNDERGRADUATE-STUDENT.snum or 
                                   POSTGRADUATE-STUDENT.snum

Subset constraint:

	      
EMPLOYEE( enum, ... , city, ... ) 
PROJECT(pnum, ... , city, ... )
PROJECT.city is included in EMPLOYEE.city

… and many, many other constraints

Data Integrity

Outline

Data integrity ? What is it ?
Consistency constraints
Verification of consistency constraints
ROLLBACK and COMMIT statements
Backup and recovery

Consistency constraints

How do we enforce consistency constraints ?
- Define a consistency constraint in CREATE TABLE statement
- Then, the verification is performed by a database server

Another method to enforce a consistency constraint, is to periodically validate the constraint in the contents of a database. If a constraint is not satisfied, then it is possible to reverse all modifications, related to the constraint and performed from a moment when the constraint was enforced last time.

A verification of a consistency constraint, can be done through negation of a constraint condition and through processing of a query with WHERE clause, that implements a negated consistency constraint condition. Such query is equivalent to a query, that finds all data violating a given consistency constraint. If processing of the query provides an empty result, then a consistency constraint validates well in the contents of a database. If the query returns a nonempty result, then it means, that a database contains data, that violates a consistency constraint.

Assume, that we would like to enforce a consistency constraint, saying that a student cannot enrol in more than 4 subjects per session. Then, after negation of the consistency constraint, we try to find the students who enrolled in more than 4 subjects per session. We can implement SELECT statement, that finds the students who enrolled in more than 4 subjects per session and we can process such statement after insertion of a new row into a relational table: ENROLMENT. If the SELECT statement returns a nonempty results, then we can roll back the latest insertion.

A way how the modification to a database can be rolled back, will be discussed in one the presentations coming soon.

Consistency constraints

How do we enforce consistency constraints ?
- Implement verification of a consistency constraint as SQL script reporting violation of consistency constraints and process the script from time to time
- Then, the verification is performed by a database server

There is a pretty large number of other ways, how we can organise verification of consistency constraints in the contents of a database.

One of the ways is to implement verification of consistency constraints within a database application that performs the modifications of the database contents.

For example, we implement a Java application accessing a relational database, through Java DataBase Connectivity (JDBC). In such a case, verification of a consistency constraint, is performed by a database application.

It is also possible to implement a verification of consistency constraints, within a stored procedure or stored function. A stored procedure or a stored function, is a piece of code written in a host language of database system + SQL and stored in a data dictionary. In such a case, verification of a consistency constraint, is performed by a database server.

It is possible to implement a verification of consistency constraints, as a servlet or php code located at a Web server. In such a case, verification of a consistency constraint, is performed by a Web server.

It is also possible to implement a verification of consistency constraints, as a database trigger. A database trigger is a piece of code written in a host language of a database system + S Q L and stored in a data dictionary. A database trigger can be automatically activated, when a predefined event happens and a predefined condition is satisfied. In such a case, verification of consistency constraint, is performed by a database server.

In the next few slides, we shall look at the details how a consistency constraint can be verified as SQL script, as a stored procedure or function or as a database trigger.

Consistency constraints

How do we enforce consistency constraints ?
- Implement verification of a consistency constraint within a database application, for example Java application accessing a relational database through Java DataBase Connectivity (JDBC)
- Then, the verification is performed by a database application
- Implement verification of consistency constraints within a stored procedure or stored function
- Then, the verification is performed by a database server
- Implement verification of consistency constraints as a servlet, php code, etc
- Then, the verification is performed by a Web server
- Implement verification of consistency constraints as a database trigger
- Then, the verification is performed by a database server

First, we look at the application of SQL scripts to verify consistency constraints.

Assume, that a relational table: EMPLOYEE, contains information common to all employees and the relational tables: DRIVER and ADMIN, contain information specific to the drivers and administration people. We would like to enforce a multi-table constraint saying, that a table EMPLOYEE must contain only information about drivers and admin people. It means, that if information about an employee is recorded in a relational table: EMPLOYEE, then more information related to the employee must be recorded either in a relational table: ADMIN or in a relational table: DRIVER or in both of them. Well, we assume, that sometimes an employee can be a driver and an administration person.

To verify the consistency constraint, we negate its condition and we put it in SELECT statement. A SELECT statement given at the bottom of the present slide lists all rows in the EMPLOYEE table, that violate the constraint. The SELECT statement finds the employees recorded in a relational table: EMPLOYEE, and not recorded in both relational tables: DRIVER and ADMIN.

If processing of SQL script, that contains the SELECT statement returns an empty result, then it means, that the consistency constraint is enforced in the present contents of a database.

If the consistency constraint is not valid in the present contents of a database, then SELECT statement returns information about all data, that violates the constraint.

Consistency constraints

Verification of a consistency constraint through SQL script
Assume that a relational table EMPLOYEE contains information common to all employees and relational table DRIVER and ADMIN contains information specific to drivers and administration people
We would like to enforce a multitable constraint saying, that a table EMPLOYEE must contain only information about the drivers and admin people, it means, that if a person is recorded in EMPLOYEE table then the person must be recorded in ADMIN or DRIVER table or in both tables

The following SELECT statement included in SQL script verifies the constraint and lists all rows in EMPLOYEE table that violate the constraint

SELECT 'Multitable constraint failed, employee' AS "Constraint",
       enum, 'is not included in either DRIVER or ADMIN tables' AS "Condition"
FROM EMPLOYEE
WHERE enum NOT IN (SELECT enum
                   FROM DRIVER)
and
enum NOT IN (SELECT enum
                   FROM ADMIN);

Consistency constraints

The following SELECT statement

	      
SELECT 'Multitable constraint failed, employee' AS "Constraint",
       enum, 'is not included in either DRIVER or ADMIN tables' AS "Condition"
FROM EMPLOYEE
WHERE enum NOT IN (SELECT enum
                   FROM DRIVER)
and
enum NOT IN (SELECT enum
                   FROM ADMIN);

may return the following results


+----------------------------------------+------+--------------------------------------------------+
| Constraint                             | enum | Condition                                        |
+----------------------------------------+------+--------------------------------------------------+
| Multitable constraint failed, employee |   20 | is not included in either DRIVER or ADMIN tables |
+----------------------------------------+------+--------------------------------------------------+

The next implementation technique, that verifies a consistency constraints, is based on application of stored routines: procedures and/or functions.

A stored routine is a piece of code, whose logic is usually implemented in a host language of a database system, together with embedded SQL statements accessing the contents of a database. A host language is a general purpose algorithmic programming language. Different database systems may have different host languages.

A stored routine is stored in a data dictionary, also called: data repository of a database system, for example: mysq database or information system database, on MySQL database server.

A sample stored procedure: country_hos is listed at the bottom of the present slide. The procedure finds the names and heads of states of the countries located at a continent, whose name is passed to a procedure through an input parameter.

Consistency constraints

Verification of a consistency constraint through stored routines (functions and procedures)
A stored routine is a piece of code whose logic is usually implemented in a general purpose procedural language (host language) including embedded SQL statements to communicate with a database server
A stored routine is stored in a data dictionary (data repository) of a database management system, for example information_system database on MySQL

A stored procedure country_hos


CREATE PROCEDURE country_hos
(IN con CHAR(20))
BEGIN
  SELECT Name, HeadOfState FROM Country
  WHERE Continent = con;
END;

A stored procedure country_hos can be invoked using a CALL statement, in a way listed at the top of the present slide. Then, it is possible to include within a stored procedure, S Q L statements, that verifies a consistency constraint.

The rights to process a stored procedure can be granted to different users implementing their own applications. Then, there is no need to repeat the identical S Q L statements, that verifies a constraint in every application.

For example, a stored procedure: insert employee, can be used to verify the consistency constraints. Then a call to: insert employee, replaces the application of INSERT statement.

A stored procedure does not have to return a value and it can modify its parameters, for a later inspection by the caller, A stored procedure can also generate the results, to be returned to the client program, through one of its parameters.

Consistency constraints

A stored procedure country_hos is invoked using CALL statement in the following way
The following call to a stored procedure insert_employee can be used instead of INSERT statement to verify the consistency constraints within the procedure
A stored procedure does not have to return a value and it can modify its parameters for a later inspection by a caller
A stored procedure can also generate the result sets to be returned to the client program through one of its parameters

Consistency constraints

A stored function can be used much like a built-in row function

When a stored function is invoked in an expression, then it returns a value during the evaluation of the expression


CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
  DETERMINISTIC
BEGIN
  DECLARE lvlvarchar(10);
    IF p_creditLimit> 50000 THEN
        SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit<= 50000 AND p_creditLimit>= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit< 10000 THEN
        SET lvl = 'SILVER';
    END IF;
RETURN (lvl);
END;

Then it can be used in SELECT statement in the following way


SELECT customerName, CustomerLevel(creditLimit)
FROM customers
GROUP BY customerName;

Another way how verification of consistency constraints can be done, is to implement it, as a database trigger.

A trigger is a named database object, associated with a relational table. A trigger is activated, when a particular event occurs for the relational table.

A database trigger can be used to evaluate consistency constraints, after the modifications of a database or to perform the calculations of the values of derived columns in a database.

The triggers can also be used, to enforce sophisticated database security constraints and/or to audit suspicious database activities, like for example an update to a column: SALARY, performed on Sunday.

A trigger is created through processing of CREATE TRIGGER statement.

A trigger is automatically activated when an SQL statement inserts, updates or deletes the rows in a relational table associated with the trigger.

Consistency constraints

Verification of a consistency constraint through the database triggers
A database trigger is a named database object associated with a table and such that it is automatically activated when a particular event occurs for the table
Some uses for database triggers are to evaluate consistency constraints after the modifications or to perform the calculations of the values of derived attributes
The database rriggers can also be used to enforce sophisticated database security constraints and/or to audit suspicious database activities, like for example an update to a column SALARY performed on Sunday
A database trigger is created through CREATE TRIGGER statement
A database trigger is activated when a statement inserts, updates, or deletes the rows in the associated table

Consistency constraints

For example, the rows can be inserted by INSERT statements, and then insert trigger is automatically activated for each inserted row
A database trigger can be also activated either before or after the trigger event
We can activate a database trigger before each row is inserted into a table or after each row that is updated.

For example:


CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
  IF NEW.amount < 0 THEN
    ROLLBACK;
  END IF;
END;

Consistency constraints

In another application a database trigger is used to audit the updates on a relational table EMPLOYEE

 
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employees_audit( SETaction='update', 
                               employeeNumber=OLD.employeeNumber,
                               lastname=OLD.lastname, changedat=NOW() );
END;

Data Integrity

Outline

Data integrity ? What is it ?
Consistency constraints
Verification of consistency constraints
ROLLBACK and COMMIT statements
Backup and recovery

ROLLBACK and COMMIT statements

Database systems allow for the immediate reversals of the recent modifications with ROLLBACK statement
On the other side, COMMIT statement makes all modifications performed since the beginning of a session or since the latest processing of COMMIT statement, permanent in a database and it also makes the reversal of such modification with ROLLBACK statement impossible
All modifications performed since the latest COMMIT statement or the beginning of a sesson can be reversed with ROLLBACK statement

A system variable: AUTOCOMMIT, can be used to control the permanent recording of the modifications in a database.

If a system variable: AUTOCOMMIT, is set to: ON, then all data manipulation statements like: INSERT, UPDATE and DELETE, are immediately and automatically committed, at the end of processing of the data manipulation statement.

If a system variable: AUTOCOMMIT, is set to OFF, then all modifications to a database are either committed by COMMIT statement or any data definition statement like: CREATE, ALTER or DROP.

EXIT statement, that ends a session, does not commit the modifications.

By default, a variable AUTOCOMMIT is set to: 'ON', at the beginning of a session.

A value of AUTOCOMMIT variable, can be changed in the same way as the values of other system variables.

ROLLBACK and COMMIT statements

A system variable AUTOCOMMIT can be used to control making the database modifications permanent
If AUTOCOMMIT = 'OFF' then all modifications to a database are committed by either COMMIT statement or any data definition statement like CREATE, ALTER, DROP
If AUTOCOMMIT = 'ON' then all modifications to a database are immediately and automatically committed at the end of processing of the data manipulation statement
EXIT statement at the end of a session does not commit the modifications
By default, a system variable AUTOCOMMIT is set to 'ON' at the beginning of a session
A value of AUTOCOMMIT variable can be changed in the same way as the values of other system variables

Information about the departments is kept in a relational table: DEPARTMENT.

The relational table: DEPARTMENT, consists of six columns: department name (name), department code (code), total staff number, chairperson (chair ), and budget.

A column: name, is a primary key and the values in a column: name, are the variable size strings, no longer than 50 characters, A column: name, is a primary key and because of that all values in the column are mandatory (NOT NULL).

A column: code, is a candidate key and all it’s values are fixed size strings 5 characters long. If the codes are shorter than 5 characters, then such values are padded with a sequence of blanks, up to 5 characters long. The values in a column code are mandatory (NOT NULL).

The values in a column: total staff, are integer values, in a range from one to fifty. The values in a column: total staff, are mandatory (NOT NULL). Total staff number per department cannot be greater than 50.

A column: chair, contains the variable size strings, no more than 50 characters long, and all its values are optional (NULL)

A column: budget, contains the real numbers, no more than 9 digits long, with at most one position after a decimal dot. The values in a column budget are mandatory (NOT NULL).

A relational table COURSE contains the descriptions of course.

A description of course consists of a course number, title, credits and name of department that offers a course.

A column course number is a unique identifier of each course. A value of course number is a fixed length string of characters, 7 characters long.

A column title contains titles of the courses. A title is a variable length string of characters, maximum 200 characters long.

A column credits determine the credits granted fro passing a course.The granted credits can be equal to either 6 or 12.

A column offered_by contains the names of departments that offer courses.

A column offered_by is a foreign key referencing the names of departments in. column name in a table DEPARTMENT.

ROLLBACK and COMMIT statements

We use a sample database that consists of the following tables

CREATE TABLE DEPARTMENT(
name               VARCHAR(50)        NOT NULL,
code               CHAR(5)            NOT NULL,
total_staff_number DECIMAL(2)         NOT NULL,
chair              VARCHAR(50)            NULL,
budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

CREATE TABLE COURSE(
cnum               CHAR(7)           NOT NULL,
title              VARCHAR(200)      NOT NULL,
credits            DECIMAL(2)        NOT NULL,
offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name)  );

In some cases setting a system variable: AUTOCOMMIT, to: ON, may contribute to an unintentional corruption of a database.

Assume, that we would like to delete the Department of Arts from a relational table: DEPARTMENT and a system variable: AUTOCOMMIT, is set to: ON. First, we use DELETE statement, to delete all courses offered by the Department of Arts. DELETE statement is given in the second paragraph of the present slide.

If a value of system variable: AUTOCOMMIT, is ON, then DELETE statement is immediately committed, and the results of the deletion are visible to the other users of the same database.

Assume, that at this point another user processes SELECT statement, that finds the total number of departments and the total number of courses offered by each department. Both SELECT statements are given at the bottom of the present slide. The SELECT statement accesses two relational tables. One of the relational table: COURSE, is changed and its contents are visible to the other users. However, the other relational table: DEPARTMENT, is not changed yet.

It is why, a user querying a database gets incorrect information, that the Department of Arts still exists and it offers no courses !

It may happen, that a modification of a database needs two or more data manipulation statements.

And, it may also happen, that an intermediate state of a database, between two data manipulations is inconsistent.

Then, it is incorrect to set a system variable: AUTOCOMMIT, to: ON, because such inconsistent state of a database is revealed to the users.

ROLLBACK and COMMIT statements

Assume, that we would like to delete the Department of Arts and AUTOCOMMIT = 'ON'
First, we delete all courses offered by the Department of Arts
With AUTOCOMMIT = 'ON' such statement is immediately committed and the results of the deletion are available to the other user of the same database
Assume, that at this point, another user processes SELECT statements, that find the total number of departments and the total number of courses offered by each department
The user querying a database gets incorrect information, that the Department of Arts still exists and it offers no courses !

Now, in another case, we assume, that we would like to delete the Department of Arts and a value of system variable: AUTOCOMMIT is set to: OFF.

Like before, first, we delete all courses offered by the Department of Arts.

If a system variable AUTOCOMMIT is set to OFF, then the DELETE statement is not immediately committed and the results of the deletion are not visible to the other users of the same database.

Assume, that at this point in time, another user processes SELECT statements, that find the total number of departments and the total number of courses offered by each department. Then, another user obtains the correct information, because the deletions have not been committed yet and another user does not see the deletions at all.

If the deletions are committed with COMMIT statement, then the deletions become permanent and both deletions are visible to the other users.

ROLLBACK and COMMIT statements

Now, assume, that we would like to delete the Department of Arts and AUTOCOMMIT = 'OFF'
First we delete all courses offered by the Department of Arts
With AUTOCOMMIT = 'OFF' such statement is not immediately committed and the results of the deletion are not available to other users of the same database
Assume, that at this point, another user processes SELECT statements, that find the total number of departments and the total number of courses offered by each department
Another user gets correct information because the deletions have not been committed yet and another user does not see the deletions

Data Integrity

Outline

Data integrity ? What is it ?
Consistency constraints
Verification of consistency constraints
ROLLBACK and COMMIT statements
Backup and recovery

A database system, like MySQL, supports two types of backups: physical backup and logical backup.

A physical backup consists of the raw copies of the directories and files, that store the contents of a database. Physical backup, is more suitable for the large, important databases, that need to be recovered quickly, when a database integrity problem occurs.

Logical backup, saves information represented as the logical database structures and the contents. For example, as INSERT statements or delimited-text files. The logical data structures, are represented by the SQL statements like: CREATE DATABASE and CREATE TABLE statements. Logical backup is more suitable for the smaller amounts of data, where we can edit the data values or the table structures or recreate the data on a different system architecture.

A diagram at the bottom of the present slide, shows the outcomes from processing of a program: mysqldump, that creates a logical backup.

A logical backup contains SQL statements, generated from the contents of a database. SQL script generated by: myqsqldump program, consist of: CREATE DATABASE, DROP TABLE, CREATE TABLE and INSERT statements. SQL script can by processed by a command line interface: my SQL, to restore data from a logical backup.

Backup and recovery

Physical backup versus Logical backup
- A physical backup consists of the raw copies of the folders and files, that store database contents
- A physical backup is suitable for the large, important databases, that need to be recovered quickly when the problems occur
- A logical backup saves information represented as a logical database structure (CREATE DATABASE, CREATE TABLE statements) and the contents (INSERT statements or delimited-text files)

An interesting question is: which backup technique is better ?

It is impossible to give a decisive answer, that entirely favours one of the techniques. It depends on the situation, where a backup is taken and on the external constraints.

It is definitely true, that physical backup is more compact than logical backup. It is because only data is backed up in the persistent storage. Logical backup needs more space for representation of the database structures, when saved in a text format.

Also, application of physical backup, are faster than logical backup, because it involves only copying files without any conversion. Logical backup is slower than physical one, because the server must access database information and convert it to a logical format.

Physical backup can be performed only while the MySQL server is not running. If a database server is running, then it is necessary to perform appropriate locking, such that the server does not change the database contents at backup time. It means, that to take a physical backup we must shutdown the system.

Logical backup is done by querying MySQL server to obtain database structure and content information. Granularity of logical backup and restore is available at a server level: all databases, at a database level: all tables in a particular database or a table level. It is an evident advantage of logical backup over physical backup. Logical backup is performed with the MySQL server running and the server is not taken offline. It is possible to manipulate the contents of logical backup, for example, we can remove the fragments of backup or transform it into another format. Such operations are not possible with a physical backup.

Backup and recovery

Physical backup versus Logical backup
- Physical backup is more compact than logical backup
- Application of physical backup is faster than logical backup because it involves only copying files without any conversion
- Physical backup can be performed while the MySQL server is not running
- If a database server is running, then it is necessary to perform the appropriate locking, so that the server does not change the database contents during the backup
- Logical backup is done by querying MySQL server to obtain the database structures and the database contents
- Logical backup is slower than physical backup because the server must access database information and convert it into a logical format
- Output from logical backup is larger than from physical backup, particularly when saved in a text format
- Granularity of logical backup and restore is available at the server level (all databases), at a database level (all tables in a particular database), or at a table level

Backup and recovery

Physical backup versus Logical backup
- Logical backup is performed with the MySQL server running and the server does not need to be taken offline
- Logical backup is suitable for the smaller amounts of data where we might edit the data values or the table structures, or recreate the data on a different machine architecture

Backup and recovery

Logical backup with mysqldump program
mysqldump program produces two types of output, depending on whether --tab option is used
- Without --tab option mysqldump writes SQL statements to the standard output
- The output consists of CREATE statements, that create the dumped objects (databases, tables, stored routines, and so forth), and INSERT statements, that load data into the tables
- The output can be saved in a file and reloaded later using mysql command line interface to recreate the dumped objects
- Options are available to modify the format of the SQL statements, and to control which objects are dumped

Backup and recovery

Logical backup with mysqldump program
mysqldump program produces two types of output, depending on whether the --tab option is used
- With --tab option mysqldump produces two output files for each dumped table
- The server writes one file as tab-delimited text, one line per table row
- This file is named tbl_name.txt in the current folder
- The server also sends a CREATE TABLE statements to mysqldump, which writes it as a file named tbl_name.sql in the current folder.

Now, we present a few examples of creating a logical backup with the: mysqldump program, without the --tab option.

It means that all output is written into a single SQL script file.

By default, mysqldump program writes information as SQL statements to the standard output, that can be saved in a file through redirection of the standard output to a file.

Redirection of the standard output to a file, is implemented with a greater character followed by a name of a file, see a command in the middle of the present slide.

In the first example, a program: mysqldump, connects as a user: root, prompts about a password, uses a verbose mode, locks all dumped tables to prevent data inconsistencies, takes a backup of all databases and saves it in a file dump.sql

An option: --user, determines a user account is used by mysqldump.

An option: --password, means, that mysqldump program, interactively asks about a password to a user account before creating a logical backup.

An option: --verbose, means, that mysqldump program, displays on a terminal screen all messages issued when a backup is created.

An option: --lock_tables, means, that all relational tables to be backed up, must be locked before a backup is taken, to avoid data inconsistencies.

An option: --all_databases, means that all databases created on the server installation, must be backed up.

Finally, greater dump dot SQL, means, that the standard output (terminal screen) will be redirected to a file: dump dot SQL.

Backup and recovery

The examples of logical backup created with mysqldump program without --tab option, it means, that the outputs are directly written to a single SQL script file
By default, mysqldump writes information as SQL statements to the standard output that can be saved in a file with file-name
Example 1: mysqldump connects as a user root, prompts about a password, uses a verbose mode, locks all dumped tables, to prevent data inconsistencies, takes a backup of all databases and saves it in a file dump.sql

Backup and recovery

Example 2: mysqldump connects as a user root, prompts about a password, uses a verbose mode, locks all dumped tables to prevent data inconsistencies, takes a backup of csit115 database and saves it in a file csit115dump.sql

Backup and recovery

Sample contents of a file csit115dump.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'csit115' /*!40100 DEFAULT CHARACTER SET latin1 */;
USE 'csit115';
DROP TABLE IF EXISTS 'ADMIN';
CREATE TABLE 'ADMIN' (
  'ENUM' decimal(12,0) NOT NULL,
  'POSITION' varchar(50) NOT NULL,
  PRIMARY KEY ('ENUM'),
  CONSTRAINT 'ADMIN_FKEY' FOREIGN KEY ('ENUM') REFERENCES 'EMPLOYEE' ('ENUM')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES 'ADMIN' WRITE;
INSERT INTO 'ADMIN' VALUES (18,'SUPPORT'),(19,'DIRECTOR'),(20,'CEO');
UNLOCK TABLES;
DROP TABLE IF EXISTS 'DRIVER';
CREATE TABLE 'DRIVER' (
  'ENUM' decimal(12,0) NOT NULL,
  'LNUM' decimal(8,0) NOT NULL,
  'STATUS' varchar(10) NOT NULL,
  PRIMARY KEY ('ENUM'),
  UNIQUE KEY 'DRIVER_UNIQUE' ('LNUM'),
  CONSTRAINT 'DRIVER_FKEY' FOREIGN KEY ('ENUM') REFERENCES 'EMPLOYEE' ('ENUM')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES 'DRIVER' WRITE;
INSERT INTO 'DRIVER' VALUES (1,10001,'AVAILABLE'),(2,10008,'ON LEAVE'),(3,10002,'AVAILABLE'),
(4,10004,'AVAILABLE'),(5,10003,'ON LEAVE'),(6,10012,'AVAILABLE'),(7,20002,'BUSY'),
(8,20003,'BUSY'),(9,30005,'BUSY'),(10,40002,'BUSY'),(11,20045,'AVAILABLE');
UNLOCK TABLES;
... ... ...

Backup and recovery

To restore a database csit115: connect as a user csit115
Drop a database csit115 and exit mysql
Connect as a user csit115 and restore a database csit115

In the last example, a program mysqldump connects as a user: root, prompts about a password, uses verbose mode, locks all relational tables to be backed up to prevent data inconsistencies.

Then, it takes backup of: EMPLOYEE and DRIVER tables, located in the: csit115 database and saves the backup in a file emp_driv.sql.

In the present example, we use the options: user, password, verbose and lock tables, in the same way as in the previous examples.

This time: mysqldump program, has two arguments: a name of a database used: csit115 and the names of relational tables: EMPLOYEE and DRIVER to be included in a logical backup.

A logical backup is saved in a file emp_driv.sql, please see a redirection of an output from mysqldump program.

In order to restore the relational tables: EMPLOYEE and DRIVER from a logical backup, we connect as a user csit115 and we drop the relational tables: EMPLOYEE and DRIVER.

To do so we have to first drop two referential integrity constraints attached to the relational tables: TRIP and ADMIN.

At the end we quit a command line interface my SQL.

Backup and recovery

Example 3: mysqldump connects as a user root, prompts about a password, uses a verbose mode, locks all dumped tables to prevent data inconsistencies, takes a backup of EMPLOYEE and DRIVER tables located in a csit115 database and saves it in a file empdriv.sql
To restore the tables EMPLOYEE and DRIVER: connect as a user csit115

Drop the tables EMPLOYEE and DRIVER and exit mysql


ALTER TABLE TRIP DROP FOREIGN KEY trip_fkey1;
ALTER TABLE ADMIN DROP FOREIGN KEY admin_fkey;
DROP TABLE DRIVER;
DROP TABLE EMPLOYEE;
exit;

Backup and recovery

Recreate and restore tables DRIVER and EMPLOYEE from a backup file empdriv.sql

Recreate referential integrity constraints

	      
ALTER TABLE ADMIN ADD CONSTRAINT admin_fkey 
                      FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM);
ALTER TABLE TRIP ADD CONSTRAINT trip_fkey1 
                     FOREIGN KEY (LNUM) REFERENCES DRIVER(LNUM);

Data Integrity

Outline

Data integrity ? What is it ?

Data integrity ? What is it ?

Data Integrity

Outline

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Data Integrity

Outline

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Consistency constraints

Data Integrity

Outline

ROLLBACK and COMMIT statements

ROLLBACK and COMMIT statements

ROLLBACK and COMMIT statements

ROLLBACK and COMMIT statements

ROLLBACK and COMMIT statements

Data Integrity

Outline

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

Backup and recovery

References