Database Concepts

Prior to Database Management Systems (DBMS), organizations used traditional operating system flat files to store data, leading to severe computational flaws:

• Data Redundancy: The same customer information is duplicated across multiple department files, wasting expensive storage.
• Data Inconsistency: When an address changes, updating it in the billing file but forgetting to update it in the shipping file creates conflicting, contradictory data.
• Data Isolation & Difficulty in Access: Data scattered across disparate file formats requires writing custom code from scratch for every ad-hoc report.
• Lack of Concurrency & Integrity: Concurrent writes cause race conditions and data loss; enforcing rules (e.g., balance ≥ 0) requires custom code in every application.

Advantages of a DBMS

A DBMS (such as MySQL, PostgreSQL, Oracle) is a comprehensive software system that controls data creation, maintenance, querying, and access:

• Controls Redundancy: Integrates data into a single centralized logical structure.
• Enforces Data Integrity: Validates constraints (Primary Key, Foreign Key, NOT NULL, CHECK) at the engine level.
• Data Independence: Separates physical storage representation from logical application programs (Three-Schema Architecture).

Formal mathematical relational terminology maps directly to database objects:

Degree & Cardinality Calculation Law:

If Table A has Degree 4 and Cardinality 10, and Table B has Degree 3 and Cardinality 5, their Cartesian Product ($A \times B$) has:

• $\text{Degree}(A \times B) = \text{Degree}(A) + \text{Degree}(B) = 4 + 3 = 7$ columns.
• $\text{Cardinality}(A \times B) = \text{Cardinality}(A) \times \text{Cardinality}(B) = 10 \times 5 = 50$ rows.

Keys are attribute subsets that uniquely identify tuples and establish linkages across relations:

1. Candidate Key: A minimal set of attributes that can uniquely identify every tuple in a relation without redundancy. A table can possess multiple candidate keys.
2. Primary Key: The specific candidate key chosen by the database designer to officially and uniquely identify tuples in the table.
   • Entity Integrity Rule: A Primary Key must be UNIQUE and can NEVER contain NULL values.
3. Alternate Key: Any candidate key that was *not* selected as the Primary Key. ($\text{Alternate Keys} = \text{Candidate Keys} - \text{Primary Key}$).
4. Foreign Key: An attribute (or set of attributes) in a relation whose values are derived from and reference the Primary Key of another (or the same) relation.
   • Referential Integrity Rule: A Foreign Key value in a child table must either match an existing Primary Key value in the parent table or be completely NULL. It prevents dangling references (e.g., enrolling a student in a department ID that does not exist).