Texas A&M University



DBM Functions and Features

Basic DBMS Features

• Update and Retrieve data

• Catalog services

• Support Concurrent update

• Recover data

• Provide Security services

• Provide data integrity features

Updating and Retrieving Data

• DBMS provides users the ability to edit and update

o acts as an intermediate for many things

• Some DBMS’ are GUI or TEXT based

|Overall DBMS setup |

|[pic] |

• Users don’t need to know how data is stored or manipulated

• DBMS relays any possible problems or errors that can occur when users are adding/editing data

|Retrieving and Updating scenarios |

|[pic] |

|Changing the price of a part in the Premiere Products database |

|[pic] |

|Retrieving a balance amount from the Premiere Products database |

Provide Catalog Services

• Metadata: data about data

• Stores metadata and makes it accessible to users

• Enterprise DBMSs often have a data dictionary (a super catalog)

• in PHPMyAdmin, it an icon below an selected database

|PHPMyAdmin Data Dictionary |

|[pic] |

|[pic] |

Support Concurrent Update

• Ensures accuracy when several users update database at the same time

• Manages complex scenarios for updates

• Concurrent update: multiple users make updates to the same database at the same time

What could we do to stop this from happening?

Avoiding concurrent updating techniques

• Batch processing

o All updates done through a special program

o Problem: data becomes out of date

o Does not work in situations that require data to be current

|The batch process visually |

|[pic] |

• Two Phase Locking

o Locking: deny other users access to data while one user’s updates are being processed

o Transaction: set of steps completed by a DBMS to accomplish a single user task

o Two-phase locking solves lost update problem

o Growing phase: DBMS locks more rows and releases none of the locks

o Shrinking phase: DBMS releases all the locks and acquires no new locks

• Timesharing

o DBMS assigns each database update a unique time (timestamp) when the update started

o Advantages

▪ Avoids need to lock rows

▪ Eliminates processing time needed to apply and release locks and to detect and resolve deadlocks

o Disadvantages

▪ Additional disk and memory space

▪ Extra processing time

• MySQL Locking and Unlock table commands

o simple, lock the table when someone is about to edit

o unlock when they want to edit

o you must have lock permissions!!!

o lock types

▪ read (only can read from it)

▪ write (can only write to it)

|Locking and Unlocking MySQL syntax |

|[pic] |

|[pic] |

Backing-up of a MySql database

• It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

1. Open phpMyAdmin.

2. Click Export in the Menu to get to where you can backup you MySql database.

3. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.

4. Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".

5. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.

|How to backup your Mysql database with phpMyAdmin |

|[pic] |

Restoring a backup of a MySql database

1. To restore a database, you click the SQL tab.

2. On the "SQL"-page , unclick the show query here again.

3. Browse to your backup of the database.

4. Click Go.

|How to restore your Mysql database with phpMyAdmin |

|[pic] |

Encryption

• Encryption: converts data to a format indecipherable to another program and stores it in an encrypted format

• Encryption process is transparent to a legitimate user

• Decrypting: reversing the encryption

• Algorithms

o AES: This function allows decryption of data using the official AES (Advanced Encryption Standard) algorithm.

o MD5: Calculates an MD5 128-bit checksum for the string. The value is returned as a binary string of 32 hex digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key.

|Testing MD5 |

|[pic] |

|MySQL Encryption Functions |

|[pic] |

User Authorizations (privileges)

• specifying specific privileges for each user

• privileges include database rights and

|PHPMyAdmin Privileges |

|[pic] |

|[pic] |

Provide Data Integrity Features

• Rules followed to ensure data is accurately and consistently updated

• Key integrity

o Foreign key and primary key constraints

• Data integrity

o Data type

o Legal values

o Format

Support Data Independence

• Data independence: can change database structure without needing to change programs that access the database

• Types of changes:

o Adding a field

o Changing a field property (such as length)

o Creating an index

o Adding or changing a relationship





................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download