Last Minutes Notes for DBMS

 1. What is a Database?

• A database is like an organized file cabinet for computer data.
• It stores information in a structured way, making it easy to manage and retrieve.

2. Components of DBMS:

• Data: Information you want to store.
• Database: A collection of related data.
• DBMS (Database Management System): Software to manage, organize, and retrieve data efficiently.

3. Types of DBMS:

• Relational DBMS (RDBMS): Stores data in tables with rows and columns. Examples: MySQL, Oracle, SQL Server.
• NoSQL DBMS: Handles unstructured or semi-structured data. Examples: MongoDB, Cassandra.

4. Basic Terms:

• Table: A collection of data organized in rows and columns.
• Row/Record: A horizontal collection of data in a table.
• Column/Attribute: A vertical collection of data in a table.

5. Key Concepts:

• Primary Key: Unique identifier for each record in a table.
• Foreign Key: Links one table to the primary key in another table.

6. SQL (Structured Query Language):

• SQL is like the language of databases.
• SELECT: Retrieve data.
• INSERT: Add new data.
• UPDATE: Modify existing data.
• DELETE: Remove data.
• CREATE: Make new tables.
• JOIN: Combine data from two or more tables.

7. Normalization:

• Organizing data efficiently.
• First Normal Form (1NF): No repeating groups.
• Second Normal Form (2NF): 1NF + no partial dependencies.
• Third Normal Form (3NF): 2NF + no transitive dependencies.

8. Indexing:

• Like an index in a book.
• Speeds up data retrieval.
• Primary Index: Created on the primary key.
• Secondary Index: Created on non-primary key columns.

9. ACID Properties:

• Atomicity: All or nothing.
• Consistency: Database is always in a valid state.
• Isolation: Transactions do not interfere.
• Durability: Changes are permanent.

10. Backup and Recovery:

• Regularly back up your data.
• In case of a problem, restore from the backup.

11. Data Integrity:

• Maintain accuracy and consistency of data.
• Constraints (like NOT NULL): Rules to ensure data integrity.

12. Security:

• Control who can access what.
• User authentication and authorization.

13. Transactions:

• A sequence of one or more SQL statements.
• All or nothing principle.

14. Data Models:

• Entity-Relationship (ER) Model: Represents entities and their relationships.
• Object-Relational Model: Extends the relational model with object-oriented features.

15. Big Data and Cloud Databases:

• Handling massive amounts of data.
• Databases in the cloud (like AWS, Azure): Access databases over the internet.

1. Primary Key:

• Definition: A primary key is a unique identifier for each record in a table.
• Characteristics:
  • No two rows can have the same primary key value.
  • Primary key values cannot be NULL.
• Example:
• CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) );

2. Foreign Key:

• Definition: A foreign key is a field in a table that refers to the primary key of another table.
• Characteristics:
  • It establishes a link between two tables.
  • Helps maintain referential integrity.
• Example:
• CREATE TABLE Orders ( OrderID INT PRIMARY KEY, ProductID INT, CONSTRAINT fk_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID) );

3. Composite Key:

• Definition: A composite key is a key that consists of two or more attributes to uniquely identify a record.
• Characteristics:
  • It combines multiple columns to create a unique identifier.
• Example:
• CREATE TABLE EmployeeProject ( EmployeeID INT, ProjectID INT, PRIMARY KEY (EmployeeID, ProjectID) );

4. Super Key:

• Definition: A super key is a set of one or more keys that can uniquely identify a record in a table.
• Characteristics:
  • It may contain more attributes than necessary for uniqueness.
• Example:
• CREATE TABLE Student ( StudentID INT, FirstName VARCHAR(50), LastName VARCHAR(50), SSN VARCHAR(9), PRIMARY KEY (StudentID), UNIQUE (SSN) );

5. Candidate Key:

• Definition: A candidate key is a minimal super key, meaning it is a super key without unnecessary attributes.
• Characteristics:
  • Each candidate key can uniquely identify a record.
• Example:
• 
  
• CREATE TABLE Employee ( EmployeeID INT, SSN VARCHAR(9), PRIMARY KEY (EmployeeID), UNIQUE (SSN) );

6. Alternate Key:

• Definition: An alternate key is a candidate key that is not chosen as the primary key.
• Characteristics:
  • While not the primary key, it can still uniquely identify a record.
• Example
• CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, Email VARCHAR(50) UNIQUE, AlternateID INT UNIQUE );

7. Surrogate Key:

• Definition: A surrogate key is an artificially generated key, often an auto-incremented number, used as the primary key.
• Characteristics:
  • It has no business meaning and is solely for identification purposes.
• Example:
• CREATE TABLE Book ( BookID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(100), Author VARCHAR(50) );

8. Natural Key:

• Definition: A natural key is a key composed of attributes that have a business meaning and can uniquely identify a record.
• Characteristics:
  • It is based on real-world data.
• Example:
• CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, EmployeeSSN VARCHAR(9) UNIQUE, EmployeeEmail VARCHAR(50) UNIQUE );

9. Unique Key:

• Definition: A unique key is a key that enforces the uniqueness of values in a column or a set of columns.
• Characteristics:
  • Similar to a primary key but allows NULL values.
• Example:
• CREATE TABLE Product ( ProductID INT UNIQUE, ProductName VARCHAR(50) );

10. Indexed Key:

• Definition: An indexed key has an index associated with it to speed up the data retrieval process.
• Characteristics:
  • Improves the speed of search operations.
• Example:
• CREATE INDEX idx_EmployeeName ON Employee (EmployeeName);

Understanding and implementing these keys appropriately is crucial for designing efficient and well-structured databases. Each key type serves a specific purpose in ensuring data integrity and relationships between tables.

Dr.Poonam