Chapter 4: Relational Databases

Definition: Relational databases efficiently and centrally coordinate information for a related group of files.
Basic Data Hierarchy: Data is organized hierarchically:
- Field: A specific attribute of interest for an entity (record). For example, a customer's name.
- Record: A related group of fields. For example, all information about a single customer.
- File: A related group of records. For example, a collection of all customer records.
SQL Examples: The following are fundamental SQL (Structured Query Language) operations:
- Selecting Data: Retrieving information from one or more tables.
  - Example: SELECT customers.id, customers.name, products FROM customers, orders, products WHERE customers.id = orders.customer_id AND orders.product_id = products.id ORDER BY customers.name; This query joins customer, order, and product information to list customers and the products they ordered, sorted by customer name.
- Creating Tables: Defining the structure of a new table.
  - Example: CREATE TABLE customer_profile (profile_id INTEGER, name VARCHAR(50), prof_factor INTEGER, date_start DATE, date_end DATE, create_by VARCHAR(10), modified_by VARCHAR(10)); This creates a table named customer_profile with specified columns and data types.
- Updating Data: Modifying existing records in a table.
  - Example: UPDATE pricelist SET price = 0.00 WHERE price = NULL; This updates all NULL prices in the pricelist table to $0.00$ .

File Approach (Traditional):
- Each application program (e.g., Sales, Shipping, Billing) has its own set of data files.
- Characteristics:
  - Data Redundancy: The same facts (e.g., Fact A, Fact C) are often stored in multiple files, leading to duplicated data.
  - Data Inconsistency: Duplicated data can become inconsistent if updates are not applied uniformly across all files.
  - Program-Data Dependence: Changes to data structures in one file often require changes to the programs that access that file, limiting flexibility.
Database Approach (Modern):
- A single, centralized database serves multiple application programs through a Database Management System (DBMS).
- Characteristics:
  - Data Independence: Data is separate from the programs that use it. Programmers do not need to know the physical storage details.
  - Data Sharing: Different applications and users can easily share the same integrated data.
  - Minimized Data Redundancy and Inconsistencies: Facts are stored once, reducing duplication and ensuring consistency.
  - Centralized Management: The DBMS handles data access, security, and integrity.

Data Warehouse: One or more very large databases containing detailed and summarized historical data from a number of years.
- Purpose: Primarily used for analysis and strategic decision-making, rather than day-to-day transaction processing.
Data Analytics: The process of analyzing large amounts of data to uncover insights for strategic decision-making.
- Tools: Involves sophisticated tools for multidimensional analysis, complex calculations, data projections, and simulations.

Data Integration: Data from various sources is combined into a unified view.
Data Sharing: Facilitates easy access and sharing of information across different users and applications.
Minimized Data Redundancy and Inconsistencies: Reduces the duplication of data and ensures accuracy.
Data Independence: Data and its logical structure are separated from the application programs, making systems more flexible.
Ease of Access: Data is easily accessible for reporting and cross-functional analysis.

Logical View (Conceptual View):
- Represents how users conceptually organize and understand the relationships among data items.
- This is the view most users interact with, focusing on what data exists and how it's related, not where it's stored.
- Examples: A user seeing a table of