Chapter 6 & 7 SQL Databases

For Quiz 6 and 7 SQL Database Chapter from Modern Database Management Textbook

What is a join in relational databases?

A relational operation where two or more tables with a common domain combine into a single table/view.

What is an equi-join?

A join where the condition is based on equality between values in the common columns, which appear redundantly in the result table.

example of an equi-join SQL query.

SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID ORDER BY OrderID.

What is an inner join?

Matches primary and foreign keys and only returns rows from each table that have matching rows in the other.

What is a natural inner-join?

An equi-join where one of the duplicate columns is eliminated in the result table.

What is an outer join?

A join where rows that don’t have common columns are included in the result table regardless.

What is a union join?

Includes all data from each table that was joined.

Left outer join

Causes rows from the first-mentioned table to appear even if there is no corresponding data in the second table.

What is a self join?

Involves tables that implement 1-many unary relationships.

What is a subquery?

Placing an inner query (SELECT statement) inside an outer query, usually placed in the WHERE or HAVING clause of the outer query.

What is a noncorrelated subquery?

A subquery that does not depend on data from the outer query and is executed once for the entire outer query.

What is a correlated subquery?

A subquery that makes use of data from the outer query and is executed once for each row returned by the outer query.

List one way to improve query efficiency related to subqueries.

Limit the number of subqueries; try to make everything done in a single query if possible.

Example of reducing network traffic in SQL ?

Instead of SELECT *, identify the specific attributes in the SELECT clause.

What can be done if data needs to be used multiple times in a database?

Make a separate query and store it as a view.

Client/Server Architectures

A networked computing model where processes are distributed between clients and servers.

Client

A workstation (PC, smartphone, tablet) that requests and uses a service provided by a server.

Server

A powerful computer (PC/mini/mainframe) that provides a service; for DBMS, this is a database server, and for the Internet, it is a Web server.

Application Logic Components

Consists of the GUI interface (presentation logic), procedures/functions/programs (processing logic), and DBMS activities (storage logic).

Application Partitioning

The process of placing portions of application code in different locations such as the client and server after it is written to improve performance and balance workloads.

Fat client

A client PC responsible for processing presentation logic, extensive application and business rules logic, and many DBMS functions.

Thin client

An application where the client accessing the application primarily provides user interfaces and limited application processing, usually with little or no local data storage.

Two-tier client-server environment

An environment where processing logic can reside at the client (fat client), server (thin client), or both (distributed environment).

Three-tier and n-tier architectures

Client-server environments where the processing logic resides at the application server or Web server.

Database server

The component that hosts the DBMS; examples include Oracle, SQL Server, Informix, MS Access, and MySql.

Web server

A component that receives and responds to browser requests using the HTTP protocol, such as Apache or Internet Information Services (IIS).

Application server

Software building blocks for creating dynamic Web sites, such as MS ASP.NET framework, Java EE, or PHP.

Web browser

A client program like Internet Explorer, Mozilla Firefox, Apple Safari, or Google Chrome that sends Web requests and receives Web pages.

Stored Procedures

Compiled SQL statements that improve performance, reduce network traffic, and enhance security, though they are proprietary and time-consuming to program.

Concurrency Control

The process of managing simultaneous operations against a database to maintain data integrity and prevent interference in a multi-user environment.

Lost update problem

A problem in multi-user environments where simultaneous access to data causes updates to cancel each other out.

Serializability

A concurrency control technique where one transaction must be finished before another one starts.

Locking Mechanisms

The most common method of achieving serialization where data retrieved for updating is locked so no other user can update it until it is unlocked.

Shared lock

A lock type that permits reading but no updates; used to prevent other users from placing an exclusive lock on the record.

Exclusive lock

A lock type that permits no access to the record; used when a transaction is preparing to update data.

Deadlock

An impasse that occurs when two or more transactions have locked common resources and each is waiting for the other to release their lock.

Two-phase locking protocol

A deadlock prevention method that involves a "growing phase" for acquiring locks and a "shrinking phase" for releasing them.

Versioning

An optimistic approach to concurrency control that creates a new version of a record during an update rather than using locks.

Database Security

The protection of data against accidental or intentional loss, destruction, or misuse.

Loss of confidentiality

A security threat specifically involving the loss of corporate data, as opposed to the loss of privacy which involves personal data.