1.
Discuss various DDL and DML commands with suitable examples
Data Definition Language (DDL) Commands
DDL commands are used to define and modify database structures. The main DDL commands include:
CREATE Command
The CREATE command is used to create objects in a database, primarily tables.
Syntax:
CREATE TABLE tablename (
column1 datatype(size),
column2 datatype(size),
...
);
Example:
CREATE TABLE student (
enroll_no NUMBER(12),
name VARCHAR2(15),
dept VARCHAR2(12)
);
Key Points:
• Table names must be unique and not match existing tables
• Table and column names must start with an alphabet
• Names should be a combination of A-Z, a-z, 0-9, and underscore
• Maximum length is 30 characters
ALTER Command
The ALTER command modifies existing database objects. It has several variations:
a) Adding Columns:
ALTER TABLE tablename
ADD (NewColumnName Datatype(size));
Example:
ALTER TABLE student
ADD (age NUMBER(3));
b) Dropping Columns:
ALTER TABLE tablename
DROP COLUMN columnname;
c) Modifying Columns:
ALTER TABLE tablename
MODIFY (columnName newDatatype(newSize));
Example:
ALTER TABLE student
MODIFY (name VARCHAR2(20));
DROP Command
The DROP command removes objects from the database completely.
Syntax:
DROP TABLE tablename;
Example:
DROP TABLE student;
TRUNCATE Command
The TRUNCATE command removes all records from a table but keeps the table structure intact.
Syntax:
TRUNCATE TABLE tablename;
Data Manipulation Language (DML) Commands
DML commands are used to manipulate data within database tables:
INSERT Command
The INSERT command adds new records to a table.
Syntax:
INSERT INTO tablename VALUES (value1, value2, ...);
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO student VALUES (101, 'John', 'Computer');
INSERT INTO student (enroll_no, name) VALUES (102, 'Alice');
SELECT Command
The SELECT command retrieves data from tables.
Syntax:
SELECT column1, column2, ... FROM tablename WHERE condition;
SELECT * FROM tablename;
Example:
SELECT name FROM student WHERE dept='Computer';
SELECT * FROM student;
UPDATE Command
The UPDATE command modifies existing data in a table.
Syntax:
UPDATE tablename SET column1=value1, column2=value2, ... WHERE condition;
Example:
UPDATE student SET dept='IT' WHERE enroll_no=101;
DELETE Command
The DELETE command removes specific records from a table.
Syntax:
DELETE FROM tablename WHERE condition;
DELETE FROM tablename; -- Deletes all records
Example:
DELETE FROM student WHERE enroll_no=101;
DELETE FROM student; -- Removes all records
2. Explain ER model with suitable example
Entity-Relationship (ER) Model
The Entity-Relationship (ER) Model is a conceptual data model that describes the structure of a
database using entities, attributes, and relationships. It provides a graphical representation called an ER
Diagram that serves as a blueprint for database design.
Components of ER Model
1. Entity
An entity is a real-world object or concept that can be distinctly identified and has an independent
existence.
Types of Entities:
• Strong Entity: Independent entities with their own primary key
• Weak Entity: Dependent on other entities, no primary key of their own
• Associative Entity: Represents relationships with attributes
2. Attributes
Attributes are properties or characteristics of entities.
Types of Attributes:
• Simple Attribute: Cannot be divided further (e.g., age, name)
• Composite Attribute: Can be divided into sub-parts (e.g., address = street + city + state)
• Derived Attribute: Value calculated from other attributes (e.g., age from birth_date)
• Multivalued Attribute: Can have multiple values (e.g., phone numbers)
• Key Attribute: Uniquely identifies an entity instance
3. Relationships
Relationships define associations between entities.
Types of Relationships:
• One-to-One (1:1): Each entity instance is associated with one instance of another entity
• One-to-Many (1:M): One entity instance associated with many instances of another entity
• Many-to-Many (M:M): Multiple instances of both entities are associated
Example: Student-Course Database
Let's consider a university database with the following entities:
Entities:
1. STUDENT with attributes:
o Student_ID (Primary Key)
o Student_Name
o Age
o Address (Composite: Street, City, State)
o Phone (Multivalued)
2. COURSE with attributes:
o Course_ID (Primary Key)
o Course_Name
o Credits
o Department
3. ENROLLMENT (Associative Entity) with attributes:
o Grade
o Enrollment_Date
Relationships:
• ENROLLS_IN: Many-to-Many relationship between STUDENT and COURSE
• Each student can enroll in multiple courses
• Each course can have multiple students
ER Diagram Representation:
[STUDENT] ----< ENROLLS_IN >---- [COURSE]
| |
Attributes: Attributes:
- Student_ID (PK) - Course_ID (PK)
- Student_Name - Course_Name
- Age - Credits
- Address - Department
- Phone (Multi)
Mapping Cardinalities
Mapping cardinalities express the number of entities to which another entity can be associated via a
relationship:
1. One-to-One: A student has one student ID card
2. One-to-Many: One department has many students
3. Many-to-One: Many students belong to one department
4. Many-to-Many: Students enroll in many courses, courses have many students
3. Write about various key attributes, constraints in DBMS
Key Attributes in DBMS
1. Super Key
A Super Key is a set of one or more attributes that can uniquely identify each tuple in a relation. It may
contain extra attributes that are not necessary for unique identification.
Example: In a Student table with attributes {Student_ID, Name, Phone, Age}, the following are super keys:
• {Student_ID}
• {Student_ID, Name}
• {Student_ID, Name, Phone}
2. Candidate Key
A Candidate Key is a minimal super key - it's a super key with no redundant attributes. It's the smallest
set of attributes that can uniquely identify each tuple.
Properties:
• No two tuples have the same value for candidate key
• No proper subset of candidate key has the uniqueness property
Example: In the Student table, if both Student_ID and Email are unique:
• {Student_ID} - Candidate Key
• {Email} - Candidate Key
3. Primary Key
A Primary Key is a candidate key chosen by the database designer to uniquely identify tuples in a
relation.
Characteristics:
• Must be unique for each tuple
• Cannot contain NULL values
• Should be stable (not frequently changed)
• Should be minimal
Example:
CREATE TABLE Student (
Student_ID NUMBER PRIMARY KEY,
Name VARCHAR2(50),
Email VARCHAR2(100)
);
4. Composite Key
A Composite Key consists of two or more attributes that together uniquely identify each tuple.
Example: In an enrollment table:
CREATE TABLE Enrollment (
Student_ID NUMBER,
Course_ID NUMBER,
Grade CHAR(2),
PRIMARY KEY (Student_ID, Course_ID)
);
5. Foreign Key
A Foreign Key is an attribute or set of attributes that refers to the primary key of another relation.
Example:
CREATE TABLE Enrollment (
Student_ID NUMBER REFERENCES Student(Student_ID),
Course_ID NUMBER REFERENCES Course(Course_ID),
Grade CHAR(2)
);
6. Alternate Key
Alternate Keys are candidate keys that are not chosen as the primary key.
Constraints in DBMS
Constraints are rules applied to data columns to ensure data integrity and consistency.
1. Domain Constraints
Domain constraints specify the permissible values for an attribute.
Example:
CREATE TABLE Student (
Age NUMBER CHECK (Age >= 18 AND Age <= 65),
Grade CHAR(1) CHECK (Grade IN ('A', 'B', 'C', 'D', 'F'))
);
2. Entity Integrity Constraint
This constraint ensures that the primary key of a relation cannot be NULL.
Rule: No primary key value can be NULL or partially NULL.
3. Referential Integrity Constraint
This constraint ensures that foreign key values refer to existing primary key values in the referenced
table.
Rules:
• Foreign key value must match a primary key value in the referenced table, OR
• Foreign key value must be NULL
4. Key Constraints
Key constraints ensure uniqueness of key attributes.
Types:
• UNIQUE: Ensures no duplicate values
• PRIMARY KEY: Combines UNIQUE and NOT NULL
5. Check Constraints
Check constraints ensure that values in a column satisfy specific conditions.
Example:
CREATE TABLE Employee (
Salary NUMBER CHECK (Salary > 0),
Department VARCHAR2(20) CHECK (Department IN ('IT', 'HR', 'Finance'))
);
6. NOT NULL Constraints
NOT NULL constraints ensure that a column cannot have empty values.
Example:
CREATE TABLE Student (
Name VARCHAR2(50) NOT NULL,
Email VARCHAR2(100) NOT NULL UNIQUE
);
4. Explain ER model with all its notations and relationship
ER Model Notations and Symbols
Entity Symbols
Symbol Name Description
Rectangle Strong Entity Independent entities with their own primary key. Often called parent entities.
Have a primary key distinguishing each occurrence.
Double Rectangle Weak Entity Dependent on other entity types. Don't have primary keys and have no meaning
without their parent entity.
Diamond within Associative Relate instances of several entity types. Contain attributes specific to
Rectangle Entity relationships between entity instances.
Relationship Symbols
Symbol Name Description
Diamond Relationship Represents associations between entities. Usually named with verbs like "assigns,"
"associates," or "tracks."
Double Weak Connections between a weak entity and its owner/parent entity.
Diamond Relationship
Attribute Symbols
Symbol Name Description
Oval/Ellipse Simple Attribute Basic attributes that cannot be divided further.
Double Oval Multivalued Attribute Attributes that can take multiple values for a single entity instance.
Dashed Oval Derived Attribute Attributes whose values can be calculated from other related attributes.
Oval with Underline Key Attribute Attributes that uniquely identify entity instances (primary key attributes).
Composite Oval Composite Attribute Attributes that can be broken down into smaller sub-attributes.
Relationship Types and Cardinalities
1. One-to-One (1:1) Relationship
Each entity instance in one entity type is associated with exactly one entity instance in another entity type.
Notation: Entity1 ——— Entity2
Example: Employee ——— Parking_Spot
• Each employee has exactly one assigned parking spot
• Each parking spot is assigned to exactly one employee
2. One-to-Many (1:M) Relationship
Each entity instance in one entity type can be associated with multiple entity instances in another entity
type, but each instance in the second entity type is associated with only one instance in the first.
Notation: Entity1 ———< Entity2
Example: Department ———< Employee
• One department has many employees
• Each employee belongs to exactly one department
3. Many-to-One (M:1) Relationship
Multiple entity instances in one entity type are associated with one entity instance in another entity type.
Notation: Entity1 >——— Entity2
Example: Student >——— Advisor
• Many students can have the same advisor
• Each student has exactly one advisor
4. Many-to-Many (M:M) Relationship
Entity instances in both entity types can be associated with multiple instances of each other.
Notation: Entity1 ———< >——— Entity2
Example: Student ———< ENROLLS >——— Course
• Students can enroll in multiple courses
• Courses can have multiple students enrolled
Participation Constraints
Total Participation (Existence Dependency)
Every entity instance must participate in the relationship.
Notation: Double line connecting entity to relationship
Example: Employee ═══ WORKS_FOR ——— Department
• Every employee must work for a department
Partial Participation
Some entity instances may not participate in the relationship.
Notation: Single line connecting entity to relationship
Example: Employee ——— MANAGES ——— Department
• Some employees manage departments, others don't
Complete ER Diagram Example
Consider a Library Management System:
Legend:
• Rectangles: Strong entities
• Diamonds: Relationships
• Ovals: Attributes
• Underlined ovals: Primary keys
• Double lines: Total participation
• Single lines: Partial participation
Advanced ER Concepts
1. Weak Entity Relationships
2. Ternary Relationships
Relationships involving three entities:
3. Recursive Relationships
An entity type relates to itself:
5. Discuss Extended ER model
Extended Entity-Relationship (EER) Model
The Extended Entity-Relationship (EER) Model is an enhanced version of the basic ER model that
includes additional concepts to handle more complex database design scenarios. It incorporates advanced
modeling concepts like specialization, generalization, aggregation, and categorization.
Key Concepts in EER Model
1. Specialization
Specialization is a top-down design process where we define subclasses of an entity type. It allows us to
define more specific entity types from a general entity type.
Characteristics:
• Subclasses inherit attributes and relationships from superclass
• Subclasses can have their own specific attributes and relationships
• Creates an IS-A relationship between superclass and subclass
Example:
Types of Specialization:
1. Attribute-Defined Specialization:
o Specialization is determined by a specific attribute value
o Example: Person specialized into Employee and Customer based on 'Type' attribute
2. User-Defined Specialization:
o Specialization is determined by user or application logic
o No specific attribute determines the specialization
2. Generalization
Generalization is a bottom-up design process where we combine multiple entity types into a single
generalized entity type based on common attributes and relationships.
Process:
1. Identify common attributes and relationships
2. Create a generalized superclass
3. Make original entities subclasses of the superclass
Example:
3. Specialization/Generalization Constraints
Disjointness Constraints:
1. Disjoint Specialization:
o An entity can belong to at most one subclass
o Notation: 'd' in the circle
2. Overlapping Specialization:
o An entity can belong to multiple subclasses
o Notation: 'o' in the circle
Completeness Constraints:
1. Total Specialization:
o Every superclass entity must belong to at least one subclass
o Notation: Double line from superclass to circle
2. Partial Specialization:
o A superclass entity may not belong to any subclass
o Notation: Single line from superclass to circle
Example with Constraints:
4. Categorization (Union Types)
Categorization represents a union of different entity types. An entity in a category can be an instance of
any of the participating entity types.
Notation:
• Circle with 'U' (Union)
• Category entity connected to circle
• Superclasses connected to circle
Example:
EER Diagram Symbols and Notations
Symbol Name Description
Triangle with 'ISA' Specialization/Generalization Shows IS-A relationships between super and subclasses
Circle with 'd' Disjoint Constraint Subclasses are mutually exclusive
Circle with 'o' Overlap Constraint Entity can belong to multiple subclasses
Double line to circle Total Participation Every superclass entity must be in a subclass
Single line to circle Partial Participation Some superclass entities may not be in any subclass
Circle with 'U' Union/Category Represents union of different entity types
Advanced EER Concepts
1. Multiple Inheritance
A subclass can inherit from multiple superclasses.
3. Multilevel Specialization Hierarchy
3. Lattice Structure
Complex inheritance where subclasses have multiple inheritance paths.
Practical EER Model Example: University Database
Key Features:
1. Total Disjoint Specialization: Every person is exactly one of Student, Faculty, or Staff
2. Overlapping Specialization: Students can be both Graduate and Undergraduate (dual degree)
3. Multiple Levels: Professor is a specialization of Faculty
4. Inherited Attributes: All subclasses inherit Name, SSN, Address from Person
6. Explain in detail about DCL and TCL commands
Data Control Language (DCL) Commands
DCL commands are used to control access to data in the database. They deal with rights, permissions, and
other controls of the database system.
Main DCL Commands:
1. GRANT Command
The GRANT command gives specific privileges to users or roles on database objects.
Syntax:
GRANT privilege_name ON object_name TO user_name;
GRANT privilege_name ON object_name TO user_name WITH GRANT OPTION;
Types of Privileges:
• SELECT: Permission to read data
• INSERT: Permission to add new data
• UPDATE: Permission to modify existing data
• DELETE: Permission to remove data
• ALL PRIVILEGES: All available privileges
• EXECUTE: Permission to execute procedures/functions
Examples:
-- Grant SELECT permission on Employee table to user 'john'
GRANT SELECT ON Employee TO john;
-- Grant multiple privileges to a user
GRANT SELECT, INSERT, UPDATE ON Student TO mary;
-- Grant all privileges on a table
GRANT ALL PRIVILEGES ON Department TO admin;
-- Grant with ability to grant to others
GRANT SELECT ON Employee TO john WITH GRANT OPTION;
-- Grant permission to create tables
GRANT CREATE TABLE TO developer;
-- Grant permission to a role
GRANT SELECT, INSERT ON Student TO student_role;
2. REVOKE Command
The REVOKE command removes previously granted privileges from users or roles.
Syntax:
REVOKE privilege_name ON object_name FROM user_name;
REVOKE privilege_name ON object_name FROM user_name CASCADE;
Examples:
-- Revoke SELECT permission from user 'john'
REVOKE SELECT ON Employee FROM john;
-- Revoke multiple privileges
REVOKE SELECT, INSERT ON Student FROM mary;
-- Revoke all privileges
REVOKE ALL PRIVILEGES ON Department FROM admin;
-- Revoke with CASCADE (removes dependent grants)
REVOKE SELECT ON Employee FROM john CASCADE;
3. DENY Command (SQL Server specific)
The DENY command explicitly denies permissions to users or roles.
Syntax:
DENY privilege_name ON object_name TO user_name;
Example:
DENY DELETE ON Employee TO intern_user;
Transaction Control Language (TCL) Commands
TCL commands are used to manage transactions in the database. A transaction is a sequence of database
operations that are treated as a single unit of work.
ACID Properties of Transactions:
• Atomicity: All operations in a transaction succeed or fail together
• Consistency: Database remains in a consistent state
• Isolation: Transactions don't interfere with each other
• Durability: Committed changes are permanent
Main TCL Commands:
1. BEGIN TRANSACTION / START TRANSACTION
Marks the beginning of a transaction block.
Syntax:
BEGIN TRANSACTION; -- SQL Server
START TRANSACTION; -- MySQL
BEGIN; -- PostgreSQL
Example:
BEGIN TRANSACTION;
UPDATE Account SET balance = balance - 1000 WHERE account_id = 101;
UPDATE Account SET balance = balance + 1000 WHERE account_id = 102;
2. COMMIT Command
The COMMIT command saves all changes made during the current transaction permanently to the
database.
Syntax:
COMMIT;
COMMIT TRANSACTION; -- SQL Server
Characteristics:
• Makes all changes permanent
• Releases all locks held by the transaction
• Ends the current transaction
Example:
BEGIN TRANSACTION;
INSERT INTO Student VALUES (101, 'Alice', 'Computer Science');
UPDATE Student SET department = 'IT' WHERE student_id = 102;
COMMIT; -- All changes are now permanent
3. ROLLBACK Command
The ROLLBACK command undoes all changes made during the current transaction.
Syntax:
ROLLBACK;
ROLLBACK TRANSACTION; -- SQL Server
Characteristics:
• Undoes all changes made in the current transaction
• Releases all locks held by the transaction
• Returns database to the state before transaction began
Example:
BEGIN TRANSACTION;
DELETE FROM Student WHERE student_id = 101;
-- Realize this was a mistake
ROLLBACK; -- Student with ID 101 is NOT deleted
4. SAVEPOINT Command
SAVEPOINT creates a point within a transaction to which you can later roll back.
Syntax:
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;
Example:
BEGIN TRANSACTION;
INSERT INTO Student VALUES (101, 'Alice', 'CS');
SAVEPOINT sp1;
UPDATE Student SET name = 'Alice Johnson' WHERE student_id = 101;
SAVEPOINT sp2;
DELETE FROM Student WHERE student_id = 102;
-- Realize deletion was wrong, but updates were correct
ROLLBACK TO sp2; -- Only the DELETE is undone
COMMIT; -- INSERT and UPDATE are committed
Practical Transaction Examples
Example 1: Bank Transfer Transaction
BEGIN TRANSACTION;
DECLARE @source_balance DECIMAL(10,2);
DECLARE @transfer_amount DECIMAL(10,2) = 500.00;
-- Check source account balance
SELECT @source_balance = balance
FROM Account
WHERE account_id = 'A001';
IF @source_balance >= @transfer_amount
BEGIN
-- Deduct from source account
UPDATE Account
SET balance = balance - @transfer_amount
WHERE account_id = 'A001';
-- Add to destination account
UPDATE Account
SET balance = balance + @transfer_amount
WHERE account_id = 'A002';
COMMIT; -- Transfer successful
PRINT 'Transfer completed successfully';
END
ELSE
BEGIN
ROLLBACK; -- Insufficient funds
PRINT 'Insufficient funds for transfer';
END
Example 2: Order Processing with Savepoints
BEGIN TRANSACTION;
-- Insert order
INSERT INTO Orders (order_id, customer_id, order_date)
VALUES (1001, 'C001', GETDATE());
SAVEPOINT order_created;
-- Insert order items
INSERT INTO OrderItems (order_id, product_id, quantity, price)
VALUES (1001, 'P001', 2, 25.00);
SAVEPOINT items_added;
-- Update inventory
UPDATE Products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 'P001';
-- Check if inventory went negative
IF EXISTS (SELECT 1 FROM Products WHERE product_id = 'P001' AND stock_quantity < 0)
BEGIN
ROLLBACK TO items_added; -- Undo inventory update
PRINT 'Insufficient stock';
ROLLBACK; -- Cancel entire order
END
ELSE
BEGIN
COMMIT; -- Complete order
PRINT 'Order processed successfully';
END
Transaction Isolation Levels
Different isolation levels control how transactions interact:
1. READ UNCOMMITTED
• Lowest isolation level
• Allows dirty reads
2. READ COMMITTED
• Prevents dirty reads
• Default in most databases
3. REPEATABLE READ
• Prevents dirty reads and non-repeatable reads
4. SERIALIZABLE
• Highest isolation level
• Prevents all concurrency issues
Setting Isolation Level:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- Transaction operations
COMMIT;
7. Overall structure (DBMS Architecture)
DBMS Architecture Overview
DBMS Architecture defines how a database management system is structured and how its components
interact. The architecture describes the design of the DBMS and how various levels of the system work
together to store, manage, and retrieve data efficiently.
Three-Schema Architecture (ANSI-SPARC Architecture)
The ANSI-SPARC Architecture is the standard way of designing DBMS architecture, proposing three
levels of data abstraction:
1. External Level (View Level)
Purpose: Provides different views of the database to different users
Characteristics:
• Highest level of abstraction
• Contains multiple external schemas (user views)
• Each view contains only the data relevant to specific users
• Hides complexity from end users
• Provides data security by restricting access
Components:
• External Schema: Defines user views
• Application Programs: Interface between users and database
• User Interface: Forms, reports, queries
Example:
Student View: {Student_ID, Name, Course, Grade}
Faculty View: {Faculty_ID, Name, Department, Courses_Taught}
Admin View: {All tables with full access}
2. Conceptual Level (Logical Level)
Purpose: Describes what data is stored and the relationships among data
Characteristics:
• Represents the entire database structure
• Independent of physical storage details
• Contains the logical schema for the entire database
• Describes entities, attributes, and relationships
• Handles data integrity constraints
Components:
• Conceptual Schema: Complete logical structure
• Data Model: ER model, Relational model, etc.
• Integrity Constraints: Primary keys, foreign keys, etc.
• Security Constraints: Access controls
3. Internal Level (Physical Level)
Purpose: Describes how data is physically stored
Characteristics:
• Lowest level of abstraction
• Deals with physical storage of data
• Describes data structures and access paths
• Handles performance optimization
• Machine-dependent
Components:
• Internal Schema: Physical storage structure
• Storage Structures: Files, indexes, clusters
• Access Methods: B-trees, Hash tables
• Physical Data Organization
Data Independence
1. Logical Data Independence
Definition: Ability to change the conceptual schema without affecting external schemas
Benefits:
• Add/remove tables without affecting user views
• Modify relationships without changing user programs
• Change constraints without impacting applications
Example:
-- Adding a new table 'Department' doesn't affect existing student views
CREATE TABLE Department (
Dept_ID NUMBER,
Dept_Name VARCHAR2(50)
);
2. Physical Data Independence
Definition: Ability to change internal schema without affecting conceptual schema
Benefits:
• Change storage devices without affecting logical structure
• Modify indexing strategies without changing applications
• Reorganize files without impacting users
Example:
• Changing from B-tree to Hash indexing doesn't affect SQL queries
DBMS Component Architecture
1. Query Processor
Components:
• DDL Compiler: Processes DDL statements
• DML Compiler: Processes DML statements
• Query Optimization: Finds efficient execution plans
• Query Execution Engine: Executes optimized queries
Process Flow:
SQL Query → Parser → Optimizer → Execution Plan → Execution Engine → Results
2. Storage Manager
Responsibilities:
• Interface between low-level data and application programs
• Manages data storage, retrieval, and updates
• Handles buffer management and file management
Components:
• Authorization Manager: Checks access permissions
• Transaction Manager: Ensures ACID properties
• File Manager: Manages file allocation and organization
• Buffer Manager: Manages memory buffers
3. Transaction Processing System
Components:
• Transaction Manager: Controls transaction execution
• Lock Manager: Handles concurrent access control
• Recovery Manager: Handles system failures
• Log Manager: Maintains transaction logs
Detailed DBMS Architecture Diagram
┌─────────────────────────────────────────────────┐
│ USERS │
│ Database Admin │ Application │ End Users │
│ │ Programmers │ │
└─────────┬────────────────┬───────────────┬──────┘
│ │ │
┌─────────▼────────────────▼───────────────▼──────┐
│ EXTERNAL LEVEL │
│ View 1 │ View 2 │ View n │
│ │ │ │
└─────────┬──────────┬──────────┬─────────────────┘
│ │ │
└──────────┼──────────┘
│
┌────────────────────▼─────────────────────┐
│ CONCEPTUAL LEVEL │
│ Conceptual Schema (Logical Structure) │
│ • Entities and Relationships │
│ • Constraints and Security │
│ • Data Integrity Rules │
└─────────────────────┬────────────────────┘
│
┌─────────────────────▼─────────────────────┐
│ INTERNAL LEVEL │
│ Internal Schema (Physical Storage) │
│ • File Organization │
│ • Indexing Structures │
│ • Access Methods │
│ • Storage Allocation │
└─────────────────────┬─────────────────────┘
│
┌─────────────────────▼─────────────────────┐
│ PHYSICAL STORAGE │
│ • Hard Disks • Memory • Tapes │
│ • Files • Blocks • Records │
└───────────────────────────────────────────┘
Multi-User Database Architecture
1. Centralized Architecture
Characteristics:
• Single server handles all database operations
• All processing occurs on one machine
• Users connect via terminals or PCs
Advantages:
• Simple administration
• Centralized control
• Easier maintenance
Disadvantages:
• Single point of failure
• Performance bottlenecks
• Limited scalability
2. Client-Server Architecture
Two-Tier Architecture:
Three-Tier Architecture:
3. Distributed Database Architecture
Characteristics:
• Data distributed across multiple locations
• Each site can operate independently
• Provides data sharing across network
Types:
• Homogeneous: Same DBMS at all sites
• Heterogeneous: Different DBMS at different sites
Modern DBMS Architecture Trends
1. Cloud Database Architecture
Components:
• Database as a Service (DBaaS)
• Multi-tenant architecture
• Auto-scaling capabilities
• Global distribution
2. Big Data Architecture
Components:
• Data Lake Storage
• NoSQL Databases
• Distributed Processing (Hadoop, Spark)
• Real-time Analytics
3. In-Memory Database Architecture
Features:
• RAM-based storage
• High-speed processing
• Real-time analytics
• Reduced I/O operations
This comprehensive coverage of DBMS architecture provides the foundation for understanding how
modern database systems are designed, organized, and optimized for various applications and user
requirements.
