DBMS (3) - Data Integrity

Data Integrity

• Refers to the degree to which data is accurate and reliable
• Integrity constraints - rules all data must follow

- if integrity constraints are not followed, the data is unreliable

Integrity Constraints

• Define acceptable values for a field (for example, the value of a month cannot be greater than 12)
• Primary keys cannot be duplicated
• Foreign keys cannot be used unless they exist as a primary key (A SalesID that is used in the customer table must exist as a primary key in the salesperson table)

DATABASE MANAGEMENT

• Database Management System (DBMS) - allows you to create a database and enter, modify, and retrieve data as needed
• Available at all levels (Sophisticated systems for a mainframe environment) - (Inexpensive, easy-to-use personal computer systems)

DATABASE FEATURES

• Data dictionary
• Data maintenance
• Data retrieval
• Concurrency control
• Security
• Backup and recovery

Data Dictionary

• Stores data about the tables and fields within the database (for each table, it stores the table name and relationships to other tables) - (For each field, it records information about that field)
• Also called a catalog
  

Data Maintenance

• Three basic operation (Adding new data - Modifying existing data - Deleting data)
• Two methods for performing operations (Interacting directly with the DBMS - Using programs to access the data using special commands)

Data Retrieval

• Involves extracting the desired data
• Two primary forms of data retrieval (Queries - Reports)

QUERIES

• You present a set of criteria; the DBMS selects matching data from the database
• Use a query language (Structured Query Language [SQL] is supported by most relational database) - (Query-by-example [QBE] uses a graphical interface to generate the SQL) - (Display results in a table-like grid)

SQL - Uses English-like statements (-SELECT specifies the field(s) to be selected - FROM specifies the table(s) containing the fields - JOIN specifies the relationship between tables - WHERE specifies the criteria that must be met

QUERY BY EXAMPLE

• User adds tables to query (user drags fields down into grid at bottom of window - criteria can be added by typing them in the Criteria row of the grid)
• Use can execute the query by clicking an icon a toolbar

QUERY RESULTS

• Retrieved records are displayed as rows
• Field specified in the select clause are displayed as columns

REPORTS

• Provide a formatted presentation of data from the database (Allow you to group reports and total by group, if desires - Normally contaon more data than querues)
• Reports are designed with a report generator

Concurrency control

• Manages simulataneous database users (If several users tried to update the same record at the same time, apdates might not be processed correctly)
• Employs a record-locking scheme (Once a user accesses a record, it is locked until the first update is complete
  

Security

• Many security features are built into most DBMS software (Users can be required to enter a user ID and password - Each user ID may have different access to the data [Read-only, permits the user to look at the data but not change it - Update, permits the user to make changes to the data - No privilages, user can not read or update the data
  

Backup and recovery

• Backup - a copy of database (Backup should be made periodically)
• Recovery - replace a damaged database with the good backup