Database Management Systems Overview

Database Management Systems

Superkey: A set of attributes in a relation that can uniquely identify a tuple.
Candidate Key: A minimal superkey (no subset of attributes makes up a superkey).
Primary Key: A specific candidate key chosen to uniquely identify tuples in a relation.
Advantages of DBMS over traditional file systems:
- Data Independence: Data and its structure are separate, allowing modifications without affecting applications.
- Data Integrity: Enforces constraints to ensure data accuracy and consistency.
- Data Security: Provides mechanisms for access control and data protection.
- Data Sharing and Concurrency: Enables multiple users to access and modify data concurrently.
- Data Backup and Recovery: Offers mechanisms for regular backups and restoration in case of data loss.
DBMS Architecture:
- External Level: Users interact with the system through predefined interfaces.
- Conceptual Level: Defines the overall database structure, independent of physical implementations.
- Internal Level: Represents the physical storage and access details of the database.
Relation: A set of tuples (rows) with a common set of attributes (columns).
Relation Schema: Represents the structure of a relation, listing the attribute names and their data types.
Integrity Constraints: Rules that maintain data consistency and validity.
- Domain Constraint: Specifies the allowable values for each attribute.
- Entity Integrity: Enforces that the primary key of a relation cannot be null.
- Referential Integrity: Ensures that values in foreign keys reference existing values in the related table.
E-R Diagram: Entity Relationship Diagram, a visual representation of entities and relationships in a database.
- Generalization: Representing a more general concept (superclass) from specific entities (subclasses).
- Specialization: Defining specific entities (subclasses) from a more general concept (superclass).
Weak Entity: An entity type that cannot be uniquely identified without the participation of another entity (strong entity).
- Strong Entity: An entity type capable of being uniquely identified without the participation of other entities.
Functional Dependency: A relationship between attributes where one attribute uniquely determines another attribute's value.
- Armstrong's Axioms:
  - Reflexivity: If Y ⊆ X, then X → Y.
  - Augmentation: If X → Y, then XZ → YZ.
  - Transitivity: If X → Y and Y → Z, then X → Z.
Normalization: A process of organizing data in a database to reduce redundancy, improve data integrity, and enhance database performance.
- 2NF: Second Normal Form - A relation is in 2NF if it's in 1NF and all non-key attributes are fully functionally dependent on the primary key.
- 3NF: Third Normal Form - A relation is in 3NF if it's in 2NF and all non-key attributes are non-transitively dependent on the primary key.
- BCNF: Boyce-Codd Normal Form - A relation is in BCNF if every determinant is a candidate key.
** ACID properties:**
- Atomicity: All operations of a transaction are completed successfully or none are completed.
- Consistency: A transaction brings the database from one consistent state to another.
- Isolation: Concurrent transactions are isolated from each other, ensuring serializability.
- Durability: Once a transaction is completed, its changes are permanently stored, even if the system crashes.
Intrusion Detection: Mechanisms to detect unauthorized access or malicious activity within a DBMS.
Query Processing: The process of evaluating and executing user queries.
- Steps:
  - Parsing: Breaking down the query into meaningful parts.
  - Optimization: Finding an efficient execution plan.
  - Execution: Performing the required operations on the data.
Locks: Mechanisms used in transaction management to control concurrent access to data.
- Types:
  - Shared Lock: Allows multiple transactions to read the same data.
  - Exclusive Lock: Only one transaction can have an exclusive lock on a data item, preventing others from accessing or modifying it.
SQL Injection: A code injection technique used to exploit vulnerabilities in applications that use SQL databases.
Serializable Schedule: A schedule that is equivalent to a serial execution of transactions, ensuring consistency and correctness.
Trigger: A stored procedure automatically executed in response to specific data modification events (INSERT, UPDATE, DELETE).
Sparse Index: An index that stores only certain values, typically used for large data sets with low cardinality.
Dense Index: An index that stores all values from the indexed attribute, typically used for smaller data sets with high cardinality.
Cursor: A mechanism used to retrieve data from a result set row by row, allowing for more control over data processing.
- Implicit Cursor: Automatically created and managed by the DBMS, used for simple queries.
- Explicit Cursor: Created and managed by the programmer, provides more flexibility and control over data retrieval.
Open Hashing: A type of hashing where the data is directly mapped to addresses in the hash table.
Closed Hashing: A type of hashing where collisions are handled by searching for an empty slot in the hash table.