A database is an organized collection of structured data stored electronically in a computer system. A Database Management System (DBMS) is software that handles the storage, retrieval, and management of data in databases.

Advantages of DBMS:

• Data Independence: Applications are independent of data storage details
• Data Integrity: Ensures data accuracy and consistency through constraints
• Data Security: Provides access control and authentication mechanisms
• Concurrent Access: Multiple users can access data simultaneously
• Backup and Recovery: Built-in mechanisms for data protection
• Reduced Data Redundancy: Minimizes duplicate data storage
• Centralized Data Management: Single point of control for data operations

These are categories of SQL commands:

DDL (Data Definition Language): Defines database structure

• Commands: CREATE, ALTER, DROP, TRUNCATE
• Example: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

DML (Data Manipulation Language): Manipulates data within tables

• Commands: SELECT, INSERT, UPDATE, DELETE
• Example: INSERT INTO users (id, name) VALUES (1, 'John');

DCL (Data Control Language): Controls access to data

• Commands: GRANT, REVOKE
• Example: GRANT SELECT ON users TO user_role;

TCL (Transaction Control Language): Manages transactions

• Commands: COMMIT, ROLLBACK, SAVEPOINT
• Example: COMMIT; or ROLLBACK;

An ER model is a conceptual data model that describes the structure of a database using entities, attributes, and relationships.

Components:

Entities: Real-world objects or concepts (represented by rectangles)

• Example: Customer, Product, Order

Attributes: Properties of entities (represented by ovals)

• Example: Customer has attributes like CustomerID, Name, Email

Relationships: Associations between entities (represented by diamonds)

• Example: Customer "places" Order

Cardinality: Describes the number of instances of one entity that can be associated with another

• One-to-One (1:1): Employee has one SSN
• One-to-Many (1:M): Customer can have many Orders
• Many-to-Many (M:N): Students enrolled in Courses

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables and defining relationships between them.

Benefits:

• Eliminates Data Redundancy: Reduces storage space and inconsistencies
• Prevents Update Anomalies: Changes need to be made in only one place
• Improves Data Integrity: Reduces chances of inconsistent data
• Easier Maintenance: Simpler to modify database structure

Drawbacks:

• Increased complexity in queries (more JOINs)
• Potential performance impact
• More tables to manage

Primary Key: Uniquely identifies each row in a table

• Cannot be NULL
• Must be unique
• Example: CustomerID in Customer table

Foreign Key: References primary key of another table

• Establishes relationships between tables
• Can be NULL (unless constrained)
• Example: CustomerID in Order table referencing Customer table

Candidate Key: Any column or combination that could serve as primary key

• Unique and not NULL
• A table can have multiple candidate keys

Composite Key: Primary key made up of multiple columns

• Example: (OrderID, ProductID) in OrderDetails table

Unique Key: Ensures uniqueness but allows one NULL value

• Similar to primary key but allows NULL

Super Key: Any combination of columns that uniquely identifies a row

• Includes primary key and additional columns