Database Management Systems: Essential Concepts and Queries
Mapping Conceptual Models to Relational Models
Mapping a conceptual (ER) model to a relational model involves:
- Converting entities into tables
- Converting attributes into columns
- Defining primary keys for each table
- Representing relationships using foreign keys
DML in DBMS
DML (Data Manipulation Language) is used to retrieve and manipulate data in database tables. Examples include: SELECT, INSERT, UPDATE, and DELETE.
Reasons for Defining a View
- To provide security by restricting access to sensitive data.
- To simplify complex queries for users.
Primary and Foreign Key Relationships
A primary key combined with a foreign key creates a relationship between two tables and helps in maintaining referential integrity.
Key Terms in DBMS
- Primary Key: Uniquely identifies each record in a table and cannot be NULL.
- Secondary Key: Used for searching records but may have duplicate values.
- Foreign Key: An attribute that refers to the primary key of another table.
ACID Properties
ACID properties ensure reliable transactions in DBMS:
- Atomicity: All or nothing execution.
- Consistency: Database remains valid.
- Isolation: Transactions do not interfere.
- Durability: Changes are permanent.
Phantom Phenomenon
The phantom phenomenon occurs when a transaction re-executes a query and finds new rows inserted by another committed transaction.
Query Processing
Query processing is the process of translating, optimizing, and executing a query to retrieve data efficiently. It involves query parsing, validation, optimization to choose the best execution plan, and evaluation by the DBMS.
Functional Dependency
Functional Dependency (FD) describes the relationship between attributes in a relation. A functional dependency exists when the value of one attribute (the determinant) uniquely determines the value of another attribute (the dependent attribute). Denoted as X → Y.
Data Models: Conceptual vs. Internal
A data model describes the structure, relationships, constraints, and semantics of data.
Conceptual Data Model
- High-level model using ER diagrams.
- Independent of DBMS.
- Used by designers and users.
Internal Data Model
- Low-level model describing physical storage.
- DBMS dependent.
- Used by database administrators.
Integrity Constraints
- Entity Integrity: Ensures primary key values are unique and not NULL.
- Referential Integrity: Ensures a foreign key value must match a primary key in another table.
Database Anomalies
- Insertion Anomaly: Cannot insert data without adding unwanted data.
- Deletion Anomaly: Deleting a record causes loss of additional important data.
- Update Anomaly: Modifying data in multiple rows leads to inconsistency.
Query By Example (QBE)
QBE is a graphical query language where users specify queries by filling sample values in tables instead of writing SQL commands.
Query Optimization
The goal is to find the most efficient execution plan by minimizing execution time, disk I/O, and memory usage to improve performance.
Semi Join
A semi join returns only those tuples from one relation that have matching tuples in another, without including attributes of the second relation.
Tuple Relational Calculus (TRC)
TRC is a non-procedural query language specifying what data is required. It uses tuple variables and well-formed formulas (WFF) involving logical operators and quantifiers.
Data Redundancy
Redundancy is the duplication of data, leading to anomalies, increased storage costs, and data inconsistency. It should be minimized using normalization.
Normalization and Normal Forms
Normalization organizes data to minimize redundancy and anomalies.
- 1NF: Atomic values, no repeating groups.
- 2NF: 1NF and no partial dependency.
- 3NF: 2NF and no transitive dependency.
- BCNF: For every X → Y, X must be a super key.
- 4NF: BCNF and no multivalued dependencies.
- 5NF: Cannot be decomposed without loss of information.
Key Types Summary
- Primary Key: Uniquely identifies each record; no NULLs.
- Foreign Key: Refers to a primary key in another table; maintains referential integrity.
- Candidate Key: Any attribute that can uniquely identify tuples; one is chosen as the primary key.
Serializability
A schedule is serializable if its outcome is equivalent to a serial execution. Conflict serializability uses precedence graphs, while view serializability is more general and based on read-from/final-write operations.
Basic Relational Operations
Operations include Selection, Projection, Union, Set Difference, Cartesian Product, and Join, represented across Relational Algebra, TRC, DRC, and SQL.