Databases and SQL 2

How databases are ran and how SQL works

• database administrator

• responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

• database designer

• determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging.

• database programmer

• develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. Query languages and general-purpose languages have significant differences, so database programming is a specialized challenge.

• database user

• consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

• query processor

• interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

• storage manager

• translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

• transaction manager

• ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.

• log

• a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database.

• catalog

also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries.

Select

• selects a subset of (or all) rows of a table.

• Project

• selects one or more columns of a table.

• Product

• lists all combinations of rows of two tables.

Join

• combines two tables by comparing related columns.

Union

• selects all rows of two tables.

Intersect

• selects rows common to two tables.

• Difference

• selects rows that appear in one table but not another.

Rename

• changes a table name.

• Aggregate

• computes functions over multiple table rows, such as sum and count.

• Data Definition Language (DDL)

• defines the structure of the database.

• Data Query Language (DQL)

• retrieves data from the database.

• Data Manipulation Language (DML)

• manipulates data stored in a database.

• Data Control Language (DCL)

• controls database user access.

• Data Transaction Language (DTL)

• manages database transactions.