Interfacing with Databases | CompTIA IT Fundamentals FC0-U61 | 5.3

In this video you will learn how to summarize methods used to interface with databases such as:  relational methods, database access methods, and exporting/importing.

Relational Methods

Relational methods refer to how a relational database is created, used, and updated.  Two major categories are discussed in this section:

• Data manipulation
• Data definition

Data Manipulation

Data manipulation is the process of changing data to make it easier to read or be more organized. Data manipulation can be summarized as follows:

• Select: The process of specifying desired data. This can be done with a query.
• Insert: Refers to the process of adding new records to a table. New data can be added by importing data from a source file, by entering new records manually, or by using SQL statements.
• Delete: Refers to the process of removing one or more existing records from a table. If a result in a query that includes more than one table is deleted, the matching records are deleted.
• Update: Refers to the process of changing one or more existing records in a table. If a result in a query that includes more than one table is updated, the matching record is updated.

Data Definition

Data definition, also referred to as data definition language (DDL), refers to how the structure of a database is created.  The common commands used in data definition, particularly in SQL, include the following:

• Create: Used to make a new data structure (database, table, index) or a stored procedure (a subroutine).
• Alter: Used to modify an existing database object.
• Drop: Used to destroy a database, a table, an index, or a view.

To eliminate the contents of a table while preserving the table, use DELETE or TRUNCATE.

Permissions

Permissions for database access are specified after a database has been created.  Users can be provided any desired combination of select, insert, delete, and update permissions.  Using the principle of least privilege, users should be provided only the permissions needed for their assigned tasks.  For example, a user who needs to look up information should have select permission. A user who needs to add new records needs insert permission.  A user who is responsible for purging obsolete records needs select and delete permissions. A user who is responsible for updating records needs select and update permissions.

Database Access Methods

A database can be accessed in a variety of ways:

• Direct/manual access
• Programmatic access
• User interface/utility access
• Query/report builders

Direct/Manual Access

Direct access (manual access) takes place when a user enters SQL commands or uses a menu to access the tables in a database.  This is a suitable method for working with a database that is stored locally or on a local area network and needs to be accessed by just a few users.

Programmatic Access

Programmatic access refers to the use of programming language to access a database.  Using a programming language to access a database enables the data to be used in more ways and is also safer than using direct access/manual access.  For example, Oracle databases support access from many languages, including Oracle PL/SQL (combines a procedural language with SQL), Oracle JMS (a Java application programming interface API), ProC/C++, and others.  Depending on the database and programming language you want to use, it might be necessary to perform a query using a supported language and then connect to the result with the language you prefer.

User Interface/Utility Access

A database such as Microsoft Access is designed to permit database access through its user interface.  However, with SQL databases, it might be necessary to add an optional user interface to make database usage easier.  For example, Oracle offers MySQL Workbench as a GUI user interface for use with MySQL.

Query/Report Builders

A query is used to find specified data in a database by using the SELECT statement.  Some databases, such as Microsoft Access, have built-in query and reporting functions.

Export/Import

Exporting a database enables the database data to be used by another app or be moved to a server with more space.  Importing a database enables a database app to use an already existing database.

When an SQL database is exported, the export file is referred to as a database dump.

Database Dump

A database dump (also referred to as a logical backup) contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss. Before you can import the data into a new database, the database must be re-created.  After a database is created (a quick process), the database dump is used to re-create the schema and tables.  This process can take some time.

Backup

The term backup can be used to refer to a database dump or a physical backup.  In this section, we will use backup to refer to physical backups only. A physical backup copies the database files from the original source to backup media.  If the backup is made while the database is running, the backup is called a hot backup; if the backup is made when the database has been shut down, the backup is a cold backup.  Physical backups can be recovered quickly in the event of problems because restoration involves restoring the backup files. Restoring a database dump (logical backup) can take much longer.