What is consistency in DBMS

Database systems

As an introduction to the problem of processing large amounts of data, the necessary terms for the electronic processing of data with database systems should first be developed in this teaching unit. Following this, the MySQL database management system (DBMS) is presented using a small example.

  1. Comparison file system vs. database: Files and databases represent two different approaches to describing, storing and processing large amounts of data. Compare files and databases! Work out the advantages of a database over the file system!

  2. [Top of page]
  3. Application areas of database systems: Name and explain two examples of databases in the private sector, in the state or in the corporate sector!

  4. [Top of page]
  5. Terms: Download the document Database Systems.zip! Unzip the file and open the document! Work out the specified terms with the help of the "Duden Informatik Gymnasiale Oberstufe" p. 153 ff. Or with the help of the Internet (e.g. Wikipedia)!

  6. [Top of page]
  7. Areas of responsibility of the database management system:
    • system support tasks: Creation and management of files and data sets, regulation of user access to multi-user systems, providing interfaces to other application programs
    • property care tasks: Providing tools for the analysis, evaluation, processing and presentation of the data, ensuring data integrity
  8. [Top of page]
  9. Requirements for the database:
    • Homogeneity: The data must all be available in a similar structure. (homogeneous: similar, consistent, uniform; Knaur's dictionary of foreign words, Lexicographical Institute, Munich 1982)
    • Redundancy: Redundancy is understood as the (superfluous) multiple storage of the same information (data duplication). As a rule, this should be avoided, as problems, so-called anomalies, can occur when inserting or changing data records.
    • Consistency: Consistency is understood to mean the absence of contradictions in a database. If the database is without contradictions, then it is consistent, if it contains inconsistencies, then it is inconsistent.
      Example: In the case of multiple-user operation of a database, several users can work on one database at the same time. If you make changes to the database at the same time, these may not be visible to other users (dirty read). There is a consistency problem here. One solution, for example, is to assign exclusive write and read locks (concurrency control).
    • Integrity: The integrity of a database is guaranteed if the database describes the data completely and without errors. A database is always a model that describes the resulting data, e.g. in a very specific company. If the processes within the company are not correctly and completely reproduced by the database, the integrity of the data is violated. (integrity: without blemishes, clean; Knaur's dictionary of foreign words, Lexicographical Institute, Munich 1982)
    • Key: A key is used to uniquely identify a data record within the entire database. The key is usually a single data field.
  10. [Top of page]
  11. Introduction to the MySQL database management system:

    The DBMS MySQL is to be operated using the phpMyAdmin program. "phpMyAdmin is a free PHP application for the administration of MySQL databases. Administration takes place via HTTP with a browser. Therefore, databases on other computers can also be administered via a network connection or over the Internet. No knowledge of SQL necessary because the application works according to the WYSIWYG method. " (Source: Wikipedia)
    As a first example, let's create a library database. It should have the following structure:

    Field nameField contentField data type
    LID15123Integer
    SurnameBowlvarchar (255)
    First name Karlvarchar (255)
    addressBauernallee 49varchar (255)
    Postcode99561varchar (255)
    placePegnitzvarchar (255)
    Date of Birth1958-03-05date
    titleThe oven is turned offvarchar (255)
    ISBN3-451varchar (255)
    Authorsballvarchar (255)
    LoanDat2001-02-17date
    ReturnDat2001-03-20date
    1. Now create the database under the name Library1 using phpMyAdmin!
      Name the table library!
      Create the structure of the table! For the Standard attribute, select the value NULL. This means something like "not known". By default, the value NULL is initially entered in each data field.
      Also activate the property NULL. This means that a "NULL value" is allowed for this data field.
      The data field L_ID represents the primary key attribute of our table. This must be entered separately when creating the table structure. Danger! A "NULL value" is not permitted for a primary key attribute!
      Enter the sample data set from the table above and then check your entries by displaying the structure of the table and the data set!
    2. [Top of page]
    3. Import more records! To make the monotonous work of entering the data easier, you should not enter the data by hand. phpMyAdmin offers the possibility to import data from external files. The data are in the following CSV file (library.csv). First download the file before importing it! After the import, check by displaying the data records whether the import was successful!

    4. [Top of page]
    5. We want to continue working on the library database in the next lesson. But for this we need a broader database. You should therefore then add 4 more data sets with fantasy data!

    6. [Top of page]
    7. So that one database can also be used on another computer, phpMyAdmin offers the convenient option of creating a so-called dump (see e.g. Wikipedia keyword: database dump). Export the database by creating an SQL dump!
      HA: Install the XAMPP environment on your home computer and import the library1 database from the SQL dump you created!

Exercise 1: Importing a database

  • Create a new database with the name Menagerie!
  • Download the associated SQL dump menagerie_dump.sql!
  • Import the data from the dump!
  • Check whether the import was successful!

Exercise 2: Creating and editing a database with MySQL and phpMyAdmin

  • Create a new database with the name personal_db and a table personal according to the given structure view (see figure)! Define the data field personal number as the primary key!
  • An Excel table personal.xls is specified. Save this table as a CSV file under the name personal_import.csv! Convert dates and German special characters beforehand!
  • Import these data records into the personal_db database! Make sure (by viewing the records) that the records have been imported!
  • Mr. Adalbert Zweistein was awarded the title of professor for his outstanding research work. In this regard, change Mr. Zweistein's qualification data field!
  • Mr. Robert Bunsen has left the company. Delete his record from the database!
  • Ms. Susi Schnell was hired as the new secretary: complete your data record!
  • Export the database to a SQL dump!

Protecting data - aspects of data security

The protection of the data stored in a database is very important for a company. If this data is lost or certain data falls into the wrong hands, this can lead to major problems. It is therefore necessary that every professional DBMS provides ways to protect the stored data.

In addition, not all users of the DB should be able to read all the data and certainly not be able to change them. For data protection and business reasons, this is not desired.

  • Work out the difference between data protection and data security with the help of the small guide to informatics p. 83! Give a practical example of this!
  • Give examples for the search for information (so-called queries) that illustrate this problem using DB Personal!
  • With the help of the small guide for computer science, p. 188 f., Indicate which basic options a DBMS offers for data protection!
  • Find out about the options for protecting the data stored in a DB that the DBMS phpMyAdmin offers!

last changed at:
A page by Mirko Hans