Data Access Data Retrieval and Query Fundamentals Relational

Data Access Data Retrieval and Query Fundamentals

Relational Data Model z. Relationships z. Operations z. Table z. Foreign Keys z. SQL or Query by Example

Relational Operations z. Select Choose rows for display z. Project Choose columns for display z. Join Connect rows from different tables z. Append Attach additional rows to a table

Select

Project

Join (Natural Join on X)

Join (Outer Join on X)

Append

Efficiency z. Indexes z. Joins and denormalization z. Partitions z. Extracts and samples

Structured Query Language) z A standard query language for relational databases proposed by ANSI (American National Standards Institute) in 1986 z Has become the standard for relational databases. z Supported by many network and hierarchical databases as well.

COMPONENTS OF SQL z DATA DEFINITION: Create tables and views and specify constraints. z DATA MANIPULATION: Extract data from tables and organize displays. z DATA MODIFICATION: Add and modify data in existing tables. z TRANSACTION INTEGRITY: Maintain the relationships among data in multiple table operations.

THE BASIC SELECT STATEMENT The basic syntax for data retrieval in the SQL Relational Calculus is: SELECT columns FROM tables WHERE row conditions ORDER BY columns GROUP BY columns for control breaks HAVING group conditions

Processing Issues z. Security z. Integrity z. Concurrency z. Transaction control Data warehouse principles try to avoid these issues by creating read-only databases.