Answer: A DBMS is a software system that allows users to define, create, maintain, and control access to a database. It acts as an interface between the user and the database, managing data storage, retrieval, and security efficiently.
Answer: A data model is a conceptual framework that describes how data is organised, stored, and manipulated in a database. It defines the logical structure of the database, the relationships between data items, and the constraints that govern the data. Examples: Relational, Hierarchical, Network, Object-oriented.
Answer: An Entity-Relationship (ER) diagram is a visual representation of the entity-relationship model. It uses rectangles (entities), ellipses (attributes), diamonds (relationships), and lines to show how entities relate to each other in a database schema.
Answer: An entity is a real-world object or concept that is distinguishable from other objects and can be represented in a database. For example, Student, Employee, Book are entities. An entity set is a collection of similar entities.
Answer: An attribute is a property or characteristic that describes an entity. For example, a Student entity may have attributes: Roll_No, Name, Age, Department. Attributes can be simple, composite, single-valued, multi-valued, or derived.
Answer:
- Data Redundancy Control: Eliminates duplicate data by centralised storage.
- Data Consistency: Redundant-free data ensures consistency across the system.
- Data Sharing: Multiple users can access the same data concurrently.
- Data Integrity: Enforces constraints and validation rules on data.
- Data Security: Provides authentication, authorisation, and encryption.
- Data Independence: Separation of logical and physical data levels.
- Backup and Recovery: Automatic mechanisms for crash recovery.
- Concurrent Access: Manages simultaneous data access by multiple users.
Answer:
- Entity: A real-world object distinguishable from others (e.g., Student, Course). Represented by a rectangle in an ER diagram.
- Attribute: Properties describing an entity (e.g., Name, Age). Represented by an ellipse.
- Relationship: An association between two or more entities (e.g., "enrolls"). Represented by a diamond.
- Entity Type: A category of entities sharing the same attributes (e.g., type "Student").
- Entity Set: A collection of all entities of a particular type at a point in time.
- Relationship Set: A set of similar relationships.
- Mapping Cardinality: Describes how entities of one set relate to entities of another (1:1, 1:N, M:N).
Answer:
Entity Types:
- Strong Entity Type: Exists independently (has its own key). E.g., Student, Employee.
- Weak Entity Type: Cannot exist without a strong entity (depends on it for identification). E.g., Dependent of an Employee.
Relationship Types:
- Binary Relationship: Between two entity types (most common).
- Recursive (Unary) Relationship: Entity relates to itself.
- Ternary Relationship: Involves three entity types simultaneously.
- n-ary Relationship: Generalisation involving n entity types.
Answer:
- Super Key: A set of attributes that uniquely identifies a tuple. May contain extra attributes. (Minimal & non-minimal both).
- Candidate Key: A minimal super key (no proper subset can uniquely identify a tuple). A table can have multiple candidate keys.
- Primary Key: One candidate key chosen by the DBMS designer to uniquely identify tuples. Cannot be NULL.
- Alternate Key: Candidate keys not chosen as the primary key.
- Foreign Key: An attribute in one relation that refers to the primary key of another relation. Establishes referential integrity.
- Composite Key: A key consisting of two or more attributes.
Answer:
The ER (Entity-Relationship) model is a high-level data model used for describing the data requirements of an organisation. It provides a conceptual view of the database.
Example — University Database:
Entities: Student, Course, Instructor, Department
Attributes:
Student: Roll_No (key), Name, Age, Class
Course: Course_ID (key), Title, Credits
Instructor: Emp_ID (key), Name, Dept
Relationships:
Student enrolls in Course (M:N)
Instructor teaches Course (1:N)
Department offers Course (1:N)
Student belongs to Department (N:1)
ER Diagram for a University Database
Answer:
| Key Type | Description |
|---|---|
| Super Key | Minimal or non-minimal set of attributes that uniquely identifies a tuple. |
| Candidate Key | Minimal super key — no proper subset can uniquely identify a tuple. |
| Primary Key | The candidate key chosen by the designer. Cannot be NULL or duplicate. |
| Alternate Key | Candidate keys not selected as primary key. |
| Foreign Key | Attribute referencing primary key of another relation. Enforces referential integrity. |
- DBMS = software for managing databases (storage, retrieval, security).
- Entity = real-world distinguishable object. Attribute = property of an entity.
- ER Diagram uses rectangles (entities), ellipses (attributes), diamonds (relationships).
- Entity types: Strong (independent) and Weak (dependent on strong entity).
- Cardinality: 1:1 (one-to-one), 1:N (one-to-many), M:N (many-to-many).
- Keys: Super → Candidate → Primary. Foreign key links two relations.
- DBMS advantages: No redundancy, consistency, sharing, security, integrity, recovery.
Answer: A relation is a two-dimensional table that represents an entity set or relationship set. It consists of rows (tuples) and columns (attributes). Each relation has a unique name, and all values in a column are of the same data type.
Answer: A tuple is a single row in a relation (table). It represents a single record of an entity and contains values for each attribute. Each tuple is uniquely identified by its primary key.
Answer: Cardinality refers to the number of tuples (rows) in a relation. The cardinality of a relation R is the number of tuples in R. It is also used to describe the mapping between entities in a relationship (1:1, 1:N, M:N).
Answer: DDL (Data Definition Language) is a set of SQL commands used to define and modify the database schema. It deals with the structure of the database rather than the data itself. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
Answer: DML (Data Manipulation Language) is a set of SQL commands used to manipulate the data stored in the database. It deals with querying, inserting, updating, and deleting data. Commands: SELECT, INSERT, UPDATE, DELETE.
Answer: The relational model, proposed by E.F. Codd in 1970, represents data as tables (relations). Key features:
- Relation (Table): A named, two-dimensional table with rows and columns.
- Attribute (Column): Each column has a unique name and data type.
- Tuple (Row): Each row is a unique record.
- Domain: The set of permissible values for an attribute.
- Degree: Number of attributes (columns) in a relation.
- Cardinality: Number of tuples (rows) in a relation.
- Keys: Super key, Candidate key, Primary key, Foreign key.
- Integrity Constraints: Entity integrity (PK not NULL), Referential integrity (FK references PK), Domain integrity.
- Relational Algebra: Theoretical foundation — SELECT, PROJECT, UNION, SET DIFFERENCE, CARTESIAN PRODUCT, RENAME.
Answer:
Roll_No INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK(Age >= 18),
Dept VARCHAR(30)
);
ALTER TABLE Student ADD COLUMN Email VARCHAR(100);
DROP TABLE Student;
TRUNCATE TABLE Student;
- CREATE: Creates a new table/database.
- ALTER: Modifies existing table structure (add/drop/modify column).
- DROP: Deletes a table and its data permanently.
- TRUNCATE: Removes all rows but keeps the table structure.
- RENAME: Renames a table or column.
Answer:
SELECT Name, Age FROM Student WHERE Dept = 'CSE';
-- INSERT: Add new row
INSERT INTO Student VALUES (101, 'Amit', 20, 'CSE');
-- UPDATE: Modify existing data
UPDATE Student SET Age = 21 WHERE Roll_No = 101;
-- DELETE: Remove rows
DELETE FROM Student WHERE Roll_No = 101;
- SELECT: Retrieves data from one or more tables. Supports WHERE, GROUP BY, HAVING, ORDER BY, JOIN.
- INSERT: Adds new rows into a table.
- UPDATE: Modifies existing rows based on a condition.
- DELETE: Removes rows from a table based on a condition.
Answer: Joins combine rows from two or more tables based on a related column.
- INNER JOIN: Returns rows with matching values in both tables.
- LEFT JOIN: Returns all rows from the left table + matched rows from the right (NULL if no match).
- RIGHT JOIN: Returns all rows from the right table + matched rows from the left.
- FULL JOIN: Returns rows when there is a match in either table.
- CROSS JOIN: Cartesian product of both tables (every row paired with every row).
- SELF JOIN: A table joined with itself.
FROM Student S INNER JOIN Enrollment E ON S.Roll_No = E.Roll_No
INNER JOIN Course C ON E.Course_ID = C.Course_ID;
SELECT S.Name, C.Title
FROM Student S LEFT JOIN Enrollment E ON S.Roll_No = E.Roll_No
LEFT JOIN Course C ON E.Course_ID = C.Course_ID;
Scenario: Find the names of students who have enrolled in more than 3 courses and have an average grade above 7.0.
Tables:
- Student (Roll_No, Name, Dept)
- Enrollment (Roll_No, Course_ID, Grade)
- Course (Course_ID, Title, Credits)
AVG(E.Grade) AS AvgGrade
FROM Student S
JOIN Enrollment E ON S.Roll_No = E.Roll_No
GROUP BY S.Roll_No, S.Name
HAVING COUNT(E.Course_ID) > 3
AND AVG(E.Grade) > 7.0;
-- Find department-wise student count using GROUP BY
SELECT Dept, COUNT(*) AS TotalStudents
FROM Student
GROUP BY Dept
ORDER BY TotalStudents DESC;
-- Find students who scored the highest grade in each course
SELECT C.Title, S.Name, E.Grade
FROM Course C
JOIN Enrollment E ON C.Course_ID = E.Course_ID
JOIN Student S ON E.Roll_No = S.Roll_No
WHERE E.Grade = (
SELECT MAX(Grade) FROM Enrollment WHERE Course_ID = C.Course_ID
);
Key Points:
- GROUP BY groups rows with the same values in specified columns.
- HAVING filters groups after GROUP BY (WHERE filters before grouping).
- INNER JOIN returns only matching rows from both tables.
- Use Aliases (S, E, C) to make queries readable.
- ORDER BY sorts the final result set.
- Relation = table, Tuple = row, Attribute = column.
- DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE.
- Keys: Super → Candidate → Primary → Alternate → Foreign.
- INNER JOIN: matching rows only. LEFT JOIN: all left + matching right.
- GROUP BY groups rows; HAVING filters groups; WHERE filters individual rows.
- Integrity Constraints: Entity (PK not NULL), Referential (FK must reference existing PK), Domain.
Answer: Normalization is the process of organising data in a database to reduce redundancy and improve data integrity. It involves decomposing a large table into smaller, well-structured tables and defining relationships between them. The main normal forms are 1NF, 2NF, 3NF, and BCNF.
Answer: A table is in 1NF if:
- It has no repeating groups or multi-valued attributes.
- Each column contains atomic (indivisible) values.
- Each row is unique (no duplicate rows).
- Each column has a unique name.
Answer: A table is in 2NF if:
- It is already in 1NF.
- No partial dependency exists — i.e., no non-prime attribute is partially dependent on a composite primary key (a non-prime attribute must depend on the entire key, not just part of it).
Answer: A table is in 3NF if:
- It is already in 2NF.
- No transitive dependency exists for non-prime attributes — i.e., a non-prime attribute does not depend on another non-prime attribute.
- Every non-prime attribute must be directly dependent on the primary key (not through another non-prime attribute).
Answer: A table is in BCNF if:
- For every non-trivial functional dependency X → Y, X must be a super key.
- BCNF is stricter than 3NF. Every BCNF table is in 3NF, but not every 3NF table is in BCNF.
- It eliminates anomalies not handled by 3NF.
Answer:
| Normal Form | Requirements | Removes |
|---|---|---|
| 1NF | Atomic values only, no repeating groups | Repeating groups, multi-valued attributes |
| 2NF | 1NF + no partial dependency on composite key | Partial dependencies |
| 3NF | 2NF + no transitive dependency (non-prime → non-prime) | Transitive dependencies |
| BCNF | For every FD X → Y, X must be a super key | All remaining anomalies |
Answer: A functional dependency (FD) X → Y means that for any two tuples with the same X value, they must also have the same Y value. X is the determinant; Y is functionally dependent on X.
Example: In a Student table, Roll_No → Name, Roll_No → Dept. So Roll_No determines both Name and Dept.
Properties (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.
Types:
- Trivial FD: Y ⊆ X (e.g., Roll_No, Name → Roll_No).
- Non-trivial FD: Y is not a subset of X (e.g., Roll_No → Name).
- Fully functional dependency: X → Y where removing any attribute from X breaks the dependency.
Answer: Decomposition is the process of splitting a relation into smaller relations. It is used in normalization to eliminate redundancy and anomalies.
Properties:
- Lossless Join: The natural join of the decomposed relations must produce exactly the original relation (no spurious tuples).
- Dependency Preservation: All functional dependencies from the original relation should be enforceable by checking the individual decomposed relations (without computing the join).
A good decomposition satisfies both lossless join and dependency preservation properties.
Original Table (Unnormalised):
| Student_ID | Student_Name | Course_ID | Course_Name | Instructor | Instructor_Office |
|---|---|---|---|---|---|
| S101 | Amit | C1, C2 | DBMS, ML | Dr. Singh, Dr. Patel | R201, R305 |
Step 1 — Convert to 1NF (Remove multi-valued attributes):
| Student_ID | Student_Name | Course_ID | Course_Name | Instructor | Instructor_Office |
|---|---|---|---|---|---|
| S101 | Amit | C1 | DBMS | Dr. Singh | R201 |
| S101 | Amit | C2 | ML | Dr. Patel | R305 |
Step 2 — Convert to 2NF (Remove partial dependency): Primary key = (Student_ID, Course_ID). Course_Name depends only on Course_ID (partial), so separate Course table.
Student(Student_ID, Student_Name)
Course(Course_ID, Course_Name, Instructor, Instructor_Office)
Enrollment(Student_ID, Course_ID)
Step 3 — Convert to 3NF (Remove transitive dependency): In Course, Instructor_Office depends on Instructor (non-prime → non-prime).
Course(Course_ID, Course_Name, Instructor)
Instructor(Instructor, Instructor_Office)
- Normalization: Process of organising data to reduce redundancy.
- 1NF: Atomic values, no repeating groups.
- 2NF: 1NF + no partial dependency on composite key.
- 3NF: 2NF + no transitive dependency (non-prime → non-prime).
- BCNF: Every determinant must be a super key. Stricter than 3NF.
- Functional Dependency: X → Y means X uniquely determines Y.
- Decomposition: Lossless join + dependency preservation = good decomposition.
Answer: A transaction is a sequence of database operations (read/write) that forms a single logical unit of work. A transaction must be atomic — either all its operations are completed successfully, or none are applied.
Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four key properties that ensure reliable transaction processing in a DBMS.
Answer: A commit is a SQL command that permanently saves all changes made by a transaction to the database. After a COMMIT, the changes cannot be rolled back. It signals the successful end of a transaction.
Answer: A rollback (or ROLLBACK) undoes all changes made by the current transaction since the last COMMIT. It restores the database to its previous consistent state. Rollback occurs automatically on error or manually via ROLLBACK command.
Answer: Serializability is the concurrency control property that ensures the result of concurrently executing transactions is equivalent to some serial (sequential) execution of those transactions. It prevents anomalies like lost updates, dirty reads, and non-repeatable reads.
Answer:
- Atomicity: A transaction is an all-or-nothing unit. Either all operations complete, or none do. Example: Bank transfer — if the debit succeeds but credit fails, the debit is rolled back.
- Consistency: A transaction brings the database from one consistent state to another. Integrity constraints must be maintained. Example: Account balance cannot go negative if there is a constraint.
- Isolation: Concurrent transactions execute independently. The result is as if they ran sequentially. Example: Two users checking the same bank balance get the same value.
- Durability: Once committed, changes persist even if the system crashes. Example: After a COMMIT on a transfer, the updated balances survive a power failure.
Answer:
- Active: Transaction is executing. Reads/writes are being performed.
- Partially Committed: Last operation has been executed but changes are not yet saved to disk.
- Failed: Normal execution cannot proceed due to an error or integrity violation.
- Aborted: Transaction has been rolled back. The database is restored to its consistent state before the transaction.
- Committed: Transaction has completed successfully and changes are permanently saved.
Transaction State Diagram: Active → Partially Committed → Committed OR Active → Failed → Aborted (then may restart).
Answer: Concurrency control manages simultaneous execution of transactions to ensure serializability and prevent anomalies.
Need: Without concurrency control, concurrent transactions can cause:
- Lost Update: Two transactions update the same data; the second update overwrites the first.
- Dirty Read: A transaction reads uncommitted data that may later be rolled back.
- Non-repeatable Read: Same data read twice in a transaction yields different values.
- Phantom Read: New rows appear in a re-executed query within the same transaction.
Methods: Locking protocols, Timestamp ordering, Optimistic concurrency control, Multiversion concurrency control (MVCC).
Answer:
Atomicity: Guaranteed by the transaction log and undo/rollback mechanism. The DBMS logs all changes. If a transaction fails, the log is used to undo partial changes.
Consistency: Enforced by integrity constraints (primary key, foreign key, check constraints). The DBMS checks these before and after each transaction.
Isolation: Achieved through concurrency control mechanisms like locking and timestamp ordering. Each transaction appears to execute in isolation from others.
Durability: Ensured by the write-ahead log (WAL) protocol. Before actual data is written to disk, the log record is written. On recovery, the log is replayed.
Real-world Example — Bank Transfer:
-- Atomicity: Both operations must succeed or both must fail
UPDATE Account SET Balance = Balance - 500 WHERE Acc_No = 'A001'; -- Debit
UPDATE Account SET Balance = Balance + 500 WHERE Acc_No = 'A002'; -- Credit
-- If either fails, ROLLBACK is issued
COMMIT; -- Durability: changes now permanently saved
- Transaction: A logical unit of work (all-or-nothing).
- ACID: Atomicity, Consistency, Isolation, Durability.
- States: Active → Partially Committed → Committed / Failed → Aborted.
- Concurrency anomalies: Lost update, dirty read, non-repeatable read, phantom read.
- Concurrency control: Locking, Timestamp ordering, MVCC.
- Deadlock: Two transactions waiting for each other's resources. Resolved by victim selection.
- Serializability: Concurrent execution equivalent to some serial execution.
Answer: Indexing is a technique used to speed up data retrieval operations in a database. An index is a data structure (like B+ tree or hash table) that stores a mapping between search key values and the location of corresponding data records, reducing the need for full table scans.
Answer: A B+ tree is a balanced tree data structure used for indexing in databases. It keeps all data records at leaf nodes, and internal nodes store only keys for navigation. All leaf nodes are at the same level, ensuring O(log n) search time.
Answer: Hashing is a technique for direct access to records using a hash function. A hash function converts a search key into a bucket address, allowing O(1) average-case search. Hashing is used in hash file organisations and hash indexes.
Answer: A primary index is an index built on the primary key of a relation. It is always dense (one index entry per search key) and the data file is ordered by the primary key. There can be at most one primary index per table.
Answer:
- Primary Index: Built on primary key. Ordered file. Sparse (only some keys indexed) with dense index entry at block level.
- Secondary (Non-clustering) Index: Built on non-key attributes. Data file may not be ordered. Dense index (every key value has an entry).
- Clustering Index: Built on non-key attributes when records with the same value are stored together. Data file is ordered on the clustering key.
- Unique Index: Ensures that the index key appears only once in the column (no duplicates).
- Composite Index: Index on multiple columns (useful for multi-column WHERE clauses).
- Bitmap Index: Uses bit arrays for each distinct value. Effective for low-cardinality columns (gender, status).
Answer: A B+ tree of order n (max n pointers per node):
- Root: May have between 2 and n children.
- Internal Nodes: Store only keys and pointers to child nodes. Each key acts as a separator.
- Leaf Nodes: Store (key, record pointer) pairs. All leaf nodes are at the same depth. Linked together for range queries.
- Search: Start at root, follow pointers using binary search at each internal node. Complexity: O(log n).
- Insert: Navigate to appropriate leaf; insert key. If overflow, split node and propagate middle key up.
- Delete: Navigate to leaf; remove key. If underflow, borrow or merge nodes.
B+ Tree Structure — Internal nodes guide search, leaf nodes store actual data pointers and are linked for range queries
Answer:
B+ Tree Definition: A B+ tree is a balanced tree where all leaf nodes are at the same level and contain actual data pointers. Internal nodes store only keys for navigation.
Operations:
- Search: Start at root, traverse to leaf using binary search at each level. O(log n) time.
- Insert: Navigate to correct leaf, insert key. If overflow (n+1 keys), split leaf into two and propagate middle key to parent.
- Delete: Remove key from leaf. If underflow, merge or redistribute keys with sibling.
B+ Tree vs B Tree:
| Feature | B+ Tree | B Tree |
|---|---|---|
| Data pointers | Only at leaf nodes | At both internal & leaf nodes |
| Search efficiency | All keys in leaves (faster range) | Keys may not be at leaves |
| Leaf linking | Yes (singly linked) | No |
| Range queries | Efficient (linked leaves) | Inefficient (in-order traversal needed) |
| Height | Shorter (more keys per node) | Comparatively taller |
Why B+ Tree in DBMS? Most DBMS (Oracle, MySQL InnoDB, PostgreSQL) use B+ trees for primary and secondary indexes because of efficient range queries and balanced structure.
- Indexing: Data structure for fast data retrieval. Reduces table scan cost.
- Primary Index: On primary key. Sparse + ordered file.
- Secondary Index: On non-key attributes. Dense.
- Clustering Index: On non-key attribute where records with same value stored together.
- B+ Tree: All data at leaves, leaves linked, internal nodes are navigation guides.
- Hashing: Direct access via hash function. O(1) average. Used in static and dynamic hashing.
- Index vs Sequential: Index search is O(log n) vs sequential O(n).
Answer: A data warehouse is a centralised repository that integrates data from multiple heterogeneous sources for analytical reporting and decision support. It uses ETL (Extract, Transform, Load) processes, stores historical data, and supports OLAP operations.
Answer: OLAP (Online Analytical Processing) is a technology for performing multidimensional analysis of data from a data warehouse. It enables complex queries for business intelligence, supporting operations like roll-up, drill-down, slice, dice, and pivot.
Answer: Data mining is the process of discovering patterns, correlations, trends, and knowledge from large volumes of data using techniques from statistics, machine learning, and AI. It is the analysis step of the KDD (Knowledge Discovery in Databases) process.
Answer: A distributed database stores data across multiple geographically dispersed sites connected by a network. It provides local autonomy, reliability, and improved performance. Data can be fragmented (horizontal, vertical, mixed) and replicated across sites.
Answer:
Three-Tier Architecture:
- Bottom Tier (Data Source Layer): Operational databases, external data sources, legacy systems. Data is extracted using ETL tools.
- Middle Tier (Data Warehouse Server): Relational DBMS or OLAP server. Data is cleaned, transformed, and loaded here. Uses a dimensional model (star/snowflake schema) with fact and dimension tables.
- Top Tier (Front-End Tools): Query and reporting tools, analysis tools, data mining tools, dashboards. Users interact with this layer.
ETL Process:
- Extract: Read data from source systems.
- Transform: Clean, filter, aggregate, and standardise data.
- Load: Write transformed data into the warehouse.
Characteristics of Data Warehouse: Subject-oriented, Integrated, Time-variant, Non-volatile.
Answer: OLAP operates on multidimensional data cubes. A data cube has dimensions (e.g., Time, Product, Region) and measures (e.g., Sales).
- Roll-up (Drill-up): Aggregate data to a higher level (e.g., city sales → country sales). Reduces dimension detail.
- Drill-down: Navigate to finer detail (e.g., year sales → month sales). Increases dimension detail.
- Slice: Select one dimension value (e.g., Sales where Product = 'Laptop'). Creates a sub-cube.
- Dice: Select multiple dimension values (e.g., Sales for Q1 AND Region = 'North').
- Pivot (Rotate): Reorient the cube view (e.g., swap rows and columns in a report).
OLAP Types: MOLAP (Multidimensional), ROLAP (Relational), HOLAP (Hybrid).
Answer: NoSQL (Not only SQL) databases are non-relational databases designed for large-scale, distributed data storage. They do not use the traditional table-based relational model.
Types:
- Document Databases: Store data as JSON/BSON documents (e.g., MongoDB, CouchDB). Flexible schema.
- Key-Value Stores: Simple key-value pairs (e.g., Redis, DynamoDB). Very fast lookups.
- Columnar (Wide-column) Stores: Store data in column families (e.g., Cassandra, HBase). Efficient for large-scale analytics.
- Graph Databases: Store data as nodes and edges (e.g., Neo4j, Amazon Neptune). Ideal for relationship-heavy data.
When to use NoSQL? Large volumes of unstructured/semi-structured data, horizontal scaling, agile development with flexible schemas, high throughput requirements.
Answer:
Data Warehouse: A centralised, subject-oriented, integrated, time-variant, and non-volatile collection of data used for decision support.
Architecture Components:
- Data Sources: Operational systems, external feeds, flat files.
- Data Staging Area: Raw data landing zone for ETL processing.
- ETL Process: Extract, transform, clean, load into warehouse.
- Data Warehouse Database: Core storage (relational or columnar). Star/Snowflake schema.
- OLAP Server: Multidimensional processing for analysis.
- Front-end Tools: Reporting, dashboards, data mining.
Data Warehouse vs Data Mart:
| Aspect | Data Warehouse | Data Mart |
|---|---|---|
| Scope | Enterprise-wide (all departments) | Department-specific (subset) |
| Size | Large (petabytes) | Small to medium |
| Cost | High | Lower |
| Data Sources | Multiple heterogeneous sources | Few sources, often one DW |
| Implementation Time | Long (months/years) | Short (weeks/months) |
| Schema | Normalized (3NF) or dimensional | Dimensional (star schema) |
- Data Warehouse: Subject-oriented, integrated, time-variant, non-volatile. For decision support.
- OLAP Operations: Roll-up, Drill-down, Slice, Dice, Pivot.
- ETL: Extract, Transform, Load.
- Star Schema: Central fact table surrounded by dimension tables.
- Snowflake Schema: Normalised dimension tables in a star schema.
- Data Mart: Subset of a DW, department-specific, easier and faster to build.
- NoSQL: Document (MongoDB), Key-Value (Redis), Columnar (Cassandra), Graph (Neo4j).
- Data Mining: Classification, Clustering, Association rules, Regression.