DBMS Module-1
- Koach Sahab
- Oct 21, 2025
- 26 min read
Updated: Oct 22, 2025
What is a Database Management System (DBMS)?
A Database Management System (DBMS) is software that serves as an interface between the user (or applications) and the database. The database itself is a structured collection of data, while the DBMS is the powerful toolset used to define, create, query, update, and administer that data.
Think of the database as a highly organized physical library, and the DBMS as the librarian and the entire automated catalog system. The DBMS ensures that:
Data is Managed: It handles storage, retrieval, and updates of data efficiently.
Data is Consistent: It ensures that data follows all specified rules and constraints (e.g., a student ID must be unique).
Data is Secure: It manages access control, ensuring only authorized users can view or modify specific data.
Data is Accessible: It provides a structured query language (most commonly SQL) that allows users to access complex data with simple commands.
Core Functions of a DBMS
Data Definition: Allows users to define the structure of the data (tables, relationships, constraints).
Data Manipulation: Provides tools (like SQL) for inserting, deleting, modifying, and retrieving data.
Data Control: Manages concurrency (multiple users accessing the same data simultaneously) and provides security and data integrity features.
Advantages of Using a DBMS in Real-World Applications
The widespread adoption of DBMS technology, particularly the Relational Database Management System (RDBMS), is due to significant operational benefits across almost every industry.
1. Improved Data Consistency and Integrity
Description: The DBMS enforces Integrity Constraints (rules defined by the database designer, such as data type, primary keys, foreign keys) to ensure the data entered is accurate and consistent across the entire system.
Real-World Example: In a banking application, the DBMS ensures that a transaction cannot debit an account unless the balance is sufficient, and that every customer has a unique account number, preventing errors in financial records.
2. Enhanced Data Security
Description: A DBMS provides layers of security by managing user permissions and access rights. Users can be restricted to viewing only the data necessary for their job roles.
Real-World Example: An HR manager can view employee salaries, but a regular employee can only view their own profile information. The DBMS strictly controls these access privileges.
3. Efficient Data Access and Retrieval
Description: By using optimized data structures and indexing techniques, the DBMS can retrieve complex data rapidly, even from tables containing millions of records. The use of SQL makes querying simple and standardized.
Real-World Example: An e-commerce site can instantly query inventory, customer purchase history, and recommended products simultaneously to display a personalized product page.
4. Better Data Sharing and Concurrency Control
Description: The DBMS manages simultaneous access by multiple users, ensuring that one user's changes don't interfere with another's. It uses locking mechanisms to handle transactions safely.
Real-World Example: During a flight booking process, hundreds of agents can check seat availability at the same moment. The DBMS ensures that only one agent successfully books the last available seat, preventing overbooking.
5. Data Independence
Description: The DBMS separates the physical storage structure (how data is physically saved on the disk) from the logical view (how users perceive the data). Changes to the physical storage rarely require changes to application code.
Real-World Example: A company can upgrade its hard drives or change its internal storage strategy without needing to rewrite its customer relationship management (CRM) software.
Disadvantages and Challenges of Using a DBMS
While essential, the DBMS approach introduces certain drawbacks, particularly regarding complexity and cost.
1. High Initial Cost and Maintenance
Description: Licensing for enterprise-level DBMS (like Oracle or SQL Server) can be very expensive. Furthermore, the specialized hardware and trained database administrators (DBAs) needed for maintenance add significant overhead.
Real-World Challenge: Small startups often choose to use open-source alternatives (like PostgreSQL or MySQL) or simple file systems initially to avoid the steep financial barrier of commercial DBMS products.
2. Increased Complexity
Description: Setting up and tuning a DBMS requires extensive technical expertise. Managing complex schemas, optimizing queries, and ensuring proper data modeling is not trivial.
Real-World Challenge: Developing an application requires specialized knowledge of database design (e.g., normalization) and advanced SQL, increasing the complexity and learning curve for development teams.
3. Performance Overhead
Description: The DBMS performs many tasks automatically (like security checks, integrity validation, and logging for recovery) that consume system resources (CPU, RAM). This adds overhead compared to simple file-based systems.
Real-World Challenge: In high-speed, high-frequency data logging applications (like IoT device monitoring), the overhead of a full transactional DBMS might be too slow. Specialized, high-performance data stores might be preferred.
4. Centralization Vulnerability
Description: Since all data is stored centrally, if the DBMS server fails or is compromised, the entire organization's data and operations can be halted or destroyed.
Real-World Challenge: Organizations must invest heavily in backup and recovery systems (like redundant servers and failover clusters) to mitigate the risk of a single point of failure.
5. Vendor Lock-in
Description: Once a company chooses a commercial DBMS vendor (e.g., Oracle), migrating to a competitor can be extremely difficult, time-consuming, and expensive due to proprietary features and data structures.
Real-World Challenge: Companies must carefully consider their long-term growth and budget constraints before committing to a commercial database platform.
Detailed Explanation of Basic DBMS Architecture (Three-Schema Approach)
The three-schema architecture, also known as the ANSI/SPARC architecture, defines three distinct levels of abstraction, or views, of the data. The goal is to achieve Data Independence, allowing changes at one level without affecting the other levels.
1. The Physical (Internal) Level
This is the lowest level of data abstraction. It describes how the data is actually stored on the storage media.
What it Describes:
The physical storage structure (e.g., using block storage, contiguous allocation).
The file organization (e.g., sequential, indexed, or hashed files).
Details about data compression, encryption, and indexing techniques (B-trees, hash tables).
The access paths used to retrieve data.
Key Characteristic: This level is complex and deals with hardware-level details. End-users and application programmers are not concerned with this level.
Schema: The Internal Schema defines the physical storage structure of the entire database.
2. The Conceptual (Logical) Level
This is the middle level of data abstraction. It describes what data is stored in the database and the relationships among the data.
What it Describes:
All the data entities, their attributes, and their data types (e.g., "Student," "Name," "Course ID").
The relationships between entities (e.g., a "Student" can enroll in many "Courses").
The security and integrity constraints (e.g., primary keys, foreign keys).
The entire logical design of the database, often modeled using tools like the Entity-Relationship (ER) Model.
Key Characteristic: This level is managed by the Database Administrator (DBA). It hides the physical storage details and focuses only on the structure of the information.
Schema: The Conceptual Schema defines the complete structure of the database for the community of users.
3. The External (View) Level
This is the highest level of data abstraction. It describes the specific part of the database that a particular user or application program is interested in.
What it Describes:
Only a subset of the conceptual schema relevant to a specific user group.
The presentation of the data, which may be different from how it is conceptually structured. For example, a "View" can combine data from multiple conceptual tables into a single virtual table.
Any derived data (calculated values) that are presented to the user.
Key Characteristic: Each user or application may have a different external view. This level simplifies the interaction, making the database appear tailored to the user's needs and enhancing security by hiding sensitive data.
Schema: External Schemas or Views define the different external views of the data.
Data Independence: The Core Benefit of This Architecture
The primary goal of the three-schema architecture is Data Independence, which is the ability to modify the schema at one level without affecting the schema at the next higher level.
1. Physical Data Independence (PDI)
Definition: The ability to change the Internal Schema (physical storage structure) without needing to change the Conceptual Schema or application programs.
Example: A DBA decides to upgrade the storage from hard disk drives (HDDs) to Solid State Drives (SSDs) or reorganize the physical file structure by adding a new index.
Impact: Since the conceptual schema remains unchanged, application code that relies on the logical structure does not need to be modified.
2. Logical Data Independence (LDI)
Definition: The ability to change the Conceptual Schema (logical structure) without having to change the External Schemas (user views) or the application programs accessing those views.
Example: A DBA decides to split a large "Address" field in the conceptual schema into three separate fields: "Street," "City," and "Zip Code."
Impact: Applications accessing a pre-existing external view that still presents a single "Address" field will continue to work seamlessly, as the DBMS handles the translation and reconstruction of the single field from the three new fields.
Summary Diagram
Level | Abstraction | Who Uses It | Schema | Data Independence |
External | User-Specific View | End Users, Application Programmers | External Schema/Views | Logical Data Independence |
Conceptual | Logical Structure | Database Administrator (DBA) | Conceptual Schema | Physical Data Independence |
Internal | Physical Storage | DBMS System Engineers | Internal Schema |
Student Registration Table Example
Let's use a standard relational table, which is the core structure in a relational DBMS (RDBMS), to define the key terms.
StudentID | Name | Major | EnrollmentYear | IsFullTime |
S101 | Alice Johnson | CSE | 2023 | TRUE |
S102 | Bob Smith | ECE | 2024 | TRUE |
S103 | Charlie Brown | ME | 2023 | FALSE |
S104 | Diana Prince | CSE | 2024 | TRUE |
Core Relational Database Terminology
The table above illustrates the relationship between the key terms:
Term | Relational DBMS Concept | Description | Example (from Table) |
Entity | A real-world object or concept that data is collected about. | The primary subject of the table. | Student |
Relation/Table | The structured representation of an entity, composed of named columns and rows. | The entire set of data organized in a grid format. | The entire grid above. |
Attribute | A characteristic or property of the entity. | The named columns in the table. | StudentID, Name, Major, EnrollmentYear, IsFullTime |
Column | The vertical component of the table, defining a single type of attribute. | Defines the field's name and data type for all records. | The entire vertical list of names: Alice, Bob, Charlie, Diana. |
Tuple | A single instance of the entity. | A single row in the table. | The entire record for Bob Smith: (S102, Bob Smith, ECE, 2024, TRUE) |
Record | A term often used interchangeably with Tuple or Row. | A complete set of attribute values for one entity instance. | Same as Tuple. |
Row | The horizontal component of the table. | Contains all the values for one single record. | Any single horizontal entry in the table. |
Cell | The intersection of a row and a column. | A single atomic data value. | The value 'CSE' at the intersection of the 'Alice Johnson' row and the 'Major' column. |
Domain | The set of possible values for an attribute. | Defines the legal values an attribute can hold. | The domain for IsFullTime is restricted to {TRUE, FALSE}. |
Schema | The definition or structure of the table. | Defines the table name, attribute names, and their data types. | Student(StudentID: CHAR(5), Name: VARCHAR(50), Major: CHAR(3), ...) |
DBMS Role in Real-World Usage
The DBMS is the software layer that enforces the integrity of this structure:
Integrity: The DBMS ensures the StudentID column is unique (a Primary Key). If someone tries to register a new student with 'S101', the DBMS will reject the entry.
Querying: When a user wants to find all students majoring in CSE who enrolled in 2024, they don't manually search the table; they execute an SQL query like:
SQL
SELECT Name FROM Student WHERE Major = 'CSE' AND EnrollmentYear = 2024;
Security: The DBMS can hide columns. For example, a "Financial Aid" department might only see the StudentID and IsFullTime columns, while the "Academic Advisor" sees all columns, but neither can delete the main table structure.
In essence, the DBMS manages the complexity of the data so that applications and users can interact with the data simply by using the logical structure defined by the Table (Relation), Columns (Attributes), and Rows (Tuples).
A database in a DBMS could be viewed by lots of different people with different responsibilities.
The applications of a Database Management System (DBMS)
The applications of a Database Management System (DBMS) are extensive and touch nearly every sector of the modern economy. Its primary purpose is to provide a reliable, efficient, and secure method for storing, retrieving, and managing large amounts of structured data.
Here are the various applications of DBMS, categorized by industry, along with the specific purpose it serves in that context:
1. Banking and Finance
pplication | Purpose of DBMS Use |
Transaction Management | To manage customer accounts, transactions (deposits, withdrawals), and loan information. The DBMS ensures Atomicity, Consistency, Isolation, and Durability (ACID) properties for reliable financial data. |
Credit Card Transactions | To track purchases, generate statements, and manage security and fraud detection logs in real-time. |
Stock Trading | To manage historical and real-time data for billions of trades, portfolios, and market fluctuations. |
2. Education
Application | Purpose of DBMS Use |
Student Information Systems (SIS) | To store and manage student demographics, course enrollments, grades, attendance, and fee payment history. |
Library Management | To catalog books, track borrowing/return dates, and manage membership records efficiently. |
Online Learning Platforms | To track user progress, quiz scores, content access, and manage user authentication. |
3. E-commerce and Retail
Application | Purpose of DBMS Use |
Inventory Management | To track product stock levels, pricing, supplier information, and warehouse locations instantly. |
Customer Relationship Management (CRM) | To store detailed customer profiles, purchase history, payment information, and loyalty program data for personalized marketing. |
Order Processing | To manage the entire lifecycle of an order, from placement and payment confirmation to shipping and delivery tracking. |
4. Airlines and Railways
Application | Purpose of DBMS Use |
Reservation Systems | To manage flight/train schedules, ticket bookings, seat availability, and prevent overbooking through concurrent transaction control. |
Logistics and Scheduling | To track crew, aircraft/train maintenance schedules, and resource allocation. |
5. Manufacturing and Supply Chain
Application | Purpose of DBMS Use |
Production Planning | To manage Bill of Materials (BOM), production schedules, raw material inventory, and quality control data. |
Supply Chain Tracking | To track goods and materials across the supply chain, ensuring traceability and optimizing delivery routes. |
6. Telecommunications
Application | Purpose of DBMS Use |
Call Records Management | To store billions of call records (metadata like duration, time, destination) for billing and regulatory compliance. |
Customer Billing | To manage subscription plans, usage meters, and generate accurate, timely bills for millions of subscribers. |
7. Healthcare
Application | Purpose of DBMS Use |
Electronic Health Records (EHR) | To store sensitive patient data, medical history, diagnoses, lab results, and treatment plans securely, enforcing strict HIPAA/GDPR security rules. |
Hospital Management | To manage appointments, bed allocations, doctor and staff schedules, and pharmaceutical inventory. |
8. Human Resources (HR)
Application | Purpose of DBMS Use |
Employee Management | To store detailed records of employees, including personal data, salary, performance reviews, benefits, and payroll processing information. |
Recruitment | To manage applications, track candidate status, and store interview feedback. |
managing university data using a collection of separate operating system files and custom application programs—highlights the problems with early, file-based data management, which is precisely why Database Management Systems (DBMS) were invented.
The primary purpose of moving from file-based systems to Database Management Systems is to overcome the following limitations and achieve better data integrity, efficiency, and security.
1. Redundancy and Inconsistency
Problem in File System | DBMS Solution |
Data Redundancy: The same piece of information (ee.g., a student's name and address) might be duplicated across different files (e.g., in a "Registration File," a "Billing File," and a "Transcript File"). | Centralized Control: Data is stored once in a central location (the database). The DBMS ensures that data is shared, not duplicated. |
Data Inconsistency: If Alice changes her address in the "Registration File" but not the "Billing File," the data becomes inconsistent and incorrect. | Integrity Constraints: The DBMS enforces rules, ensuring that updates made to data are applied consistently across all parts of the database structure. |
2. Difficulty in Accessing Data
Problem in File System | DBMS Solution |
Complex Retrieval: Generating a new report (e.g., "List all students in the Computer Science department who have a GPA over 3.5") requires writing a new, complex application program to read, filter, and combine data from multiple files. | Standardized Query Language (SQL): Users can access complex data quickly using simple, non-procedural queries. The DBMS handles the complexity of how to retrieve the data efficiently. |
3. Data Isolation and Security
Problem in File System | DBMS Solution |
Data Isolation: Data is scattered in various files, often in different formats, making it hard to combine for comprehensive reports or analysis. | Unified View (Conceptual Schema): The DBMS provides a single, coherent logical view of all the data, making complex correlations easy. |
Program Dependence: Application programs are tightly coupled with the file structure. If the file format changes (e.g., adding a new field), all application programs accessing that file must be rewritten (Physical Data Dependence). | Data Independence: The DBMS separates the application layer from the storage layer. Changes to the physical storage rarely require changes to the application programs. |
Security Issues: Protecting specific parts of the data (e.g., grades but not addresses) is difficult since operating system file security often applies to the entire file. | Granular Security: The DBMS allows access control to be defined at the user, table, or even column level, enforcing much stricter security protocols. |
4. Integrity and Concurrency Problems
Problem in File System | DBMS Solution |
Lack of Atomic Transactions: If a program fails halfway through updating multiple related files (e.g., registering a student but failing to update the class roster file), the system is left in an inconsistent state. | ACID Properties: The DBMS ensures transactions are Atomic (either all changes are made, or none are), ensuring the database state is always valid. |
Concurrency Issues: When two different programs try to update the same file simultaneously, it can lead to corrupted or lost data. | Concurrency Control: The DBMS manages simultaneous access using mechanisms like locking, ensuring that transactions run correctly even when accessing shared data. |
Data abstraction in a Database Management System (DBMS) is the mechanism used to hide the complex, low-level details of how data is stored and maintained from the users and applications that interact with the database. Its goal is to provide users with a simpler, more relevant view of the data.
The concept is organized into three distinct levels, forming the Three-Schema Architecture (or ANSI/SPARC architecture):
1. Levels of Data Abstraction
The three levels separate the user's perception of the data from the physical storage details, achieving Data Independence.
A. Physical Level (Internal Level)
Lowest Level: This is where the physical reality of data storage is defined.
Focus: How the data is physically stored and organized on the storage devices (like hard drives or SSDs).
Details Hides: File organization (sequential, indexed, hashed), data structures used for indexing (e.g., B-trees), allocation of storage space, and encryption methods.
Example: Defining that a specific customer name will be stored in a sequence of bytes starting at a particular address on the disk, or that an index will be used to speed up lookups.
Users: Only the DBMS system engineers and specialized Database Administrators (DBAs) are concerned with this level.
B. Conceptual Level (Logical Level)
Middle Level: This level defines the entire database structure for the community of users.
Focus: What data is stored and the relationships among the data. It represents the logical structure of the database.
Details: It defines all the entities (tables), attributes (columns), data types, and integrity constraints (like primary and foreign keys). It hides the physical storage details defined in the internal level.
Example: Defining the Student entity with attributes like StudentID (primary key), Name, and Major, and establishing a relationship with a Course entity.
Users: Database designers and DBAs use this level to model the organization's information.
C. External Level (View Level)
Highest Level: This level describes only the specific part of the database that an individual user or application needs.
Focus: How different users view the data. It hides the rest of the database that is irrelevant or sensitive to that user.
Details: It is often implemented using Views (virtual tables). Multiple external views can be derived from the single conceptual schema.
Example:
Student View: Only sees their own grades and courses, not other students' data.
Admin View: Sees student demographics and enrollment data, but not financial records.
Users: End-users and application programmers interact with the database at this simplified level.
2. Schemas and Instances
To understand the three levels, you must distinguish between the definition of the data and the actual content of the data at any given moment.
A. Schema (The Blueprint)
The term Schema refers to the description, structure, or definition of the database. It defines the organization of data, the relationships, and the constraints. It is defined during the design phase and changes infrequently.
Conceptual Schema: The overall logical structure of the database (e.g., a table named Student with five columns).
Internal Schema: The physical storage structure definition (e.g., how the Student table records are indexed on the disk).
External Schema: The definition of a specific user's view (e.g., a view that shows only StudentID and Name).
B. Instance (The Snapshot)
The term Instance refers to the actual content of the database at a particular point in time. It represents the data stored in the database right now.
Example: If the conceptual schema defines the Student table, the Instance is the set of data currently inside that table (Alice, Bob, Charlie, etc.).
Characteristic: The instance changes constantly as users add, delete, or update data (e.g., when a new student registers or a course is completed).
Analogy:
Think of a Class Schedule Board.
Term | Analogy | Database Concept |
Schema | The empty, printed template for the board (columns for Time, Course, Room). | The unchanging structure of the tables. |
Instance | The handwritten entries on the board for today's classes (10:00 AM, Calculus, Room 301). | The actual data content at the moment. |
Data Abstraction | The student only sees their personal timetable (External View) and not the complex scheduling algorithm used by the registrar (Internal Level). | Hiding complexity across the three levels. |
VARIOUS DATA MODELS
A data model defines the logical structure of a database—how data is organized, how relationships between entities are established, and how constraints are applied.
Here are the four primary types of data models, with examples based on a Student Database.
1. Relational Model (RDBMS)
The Relational Model is the most widely used model today (e.g., MySQL, Oracle, PostgreSQL). It organizes data into two-dimensional tables (relations), where rows represent records (tuples) and columns represent attributes. Relationships are established using keys (Primary and Foreign).
Feature | Description |
Structure | Data is represented as a collection of normalized tables. |
Relationships | Established explicitly using Foreign Keys that reference Primary Keys in other tables. |
Advantage | Strong data consistency, high integrity, and flexible querying via SQL. |
Student Database Example:
Table | Structure | Relationship |
STUDENT | StudentID (PK), Name, MajorID (FK) | MajorID links to the MAJOR table. |
COURSE | CourseID (PK), CourseName, Credits | |
ENROLLMENT | StudentID (FK, PK), CourseID (FK, PK), Grade | StudentID links to STUDENT; CourseID links to COURSE. |
2. Entity-Relationship (ER) Model
The ER Model is a high-level conceptual model used primarily for designing the database before implementation. It represents the real-world view of data using entities, attributes, and relationships.
Feature | Description |
Structure | Uses graphical notation (ER Diagrams) with rectangles (entities), ovals (attributes), and diamonds (relationships). |
Relationships | Defined by cardinality (one-to-one, one-to-many, many-to-many). |
Advantage | Excellent communication tool between designers and users; easy to map to the Relational Model. |
Student Database Example (Conceptual):
Entities (Rectangles): Student, Course, Professor.
Attributes (Ovals): Student has Name, StudentID. Course has CourseName, Credits.
Relationship (Diamond): Student enrolls in Course.
Cardinality: Many-to-Many (M:N).
3. Hierarchical Model
The Hierarchical Model organizes data in a tree-like structure. It was one of the first DBMS models (used in IMS by IBM). Data is linked via parent-child relationships, where one parent can have many children, but each child can have only one parent.
Feature | Description |
Structure | Root and branches structure; top-down organization. |
Relationships | Rigid one-to-many (1:M) structure enforced by physical pointers. |
Disadvantage | Difficult to represent complex many-to-many relationships; inflexible querying. |
Student Database Example:
Root (Parent): Department
Child: Student (A student belongs to only one department).
Grandchild: Course (The courses a student is enrolled in).
Limitation: It is hard to model a course being offered by two different departments, or a student enrolling in a course outside their home department, without data duplication.
4. Network Model
The Network Model is an extension of the Hierarchical Model that allows a record (child) to have multiple parents. It represents data using a directed graph structure.
Feature | Description |
Structure | A graph structure allowing complex links between records. |
Relationships | Allows many-to-many (M:N) relationships using explicit pointers (set concept). |
Advantage | Better performance and flexibility than the Hierarchical Model for complex relationships. |
Student Database Example:
Structure:
Student record is linked to multiple Course records (enrollment).
Course record is linked to multiple Student records (roster).
Course record can also be linked to multiple Professor records (team teaching), and to multiple Department records (cross-listing).
Result: This model directly supports the many-to-many relationships naturally, overcoming the hierarchical model's limitation.
Modern Extensions (NoSQL)
Beyond these traditional models, modern applications often use NoSQL models, which are schema-less or flexible-schema:
Document Model (e.g., MongoDB): Data is stored in flexible, JSON-like documents. A student record might contain a nested array of their courses and grades within a single document.
Key-Value Model (e.g., Redis): Data is stored as a simple dictionary: Key maps to a Value. (e.g., Key: S101_Name, Value: Dinesh Kumar).
Graph Model (e.g., Neo4j): Data is stored as nodes (entities) and edges (relationships). Excellent for social networks and relationship analysis. (e.g., Node Student ’Alice’ connects to Node Course ’DBMS’ via Edge ’EnrolledIn’).
VARIOUS CONSTRAINTS
Integrity constraints are a set of rules used to maintain the quality, consistency, and accuracy of data in a database. They ensure that data changes—insertions, deletions, or updates—do not result in incorrect, invalid, or unreliable information. 🔒
Here are the main types of integrity constraints, explained with examples based on a relational database containing Student and Course tables.
1. Entity Integrity Constraint (Primary Key Rule)
This constraint ensures that every entity (row) in a table is uniquely identifiable.
Rule: The attribute(s) designated as the Primary Key (PK) for a table cannot contain NULL values and must contain unique values for every tuple (row).
Purpose: To guarantee that every single record can be accessed, referenced, and identified unambiguously.
Example (Student Table):
Example Tables with Integrity Constraints
We'll design two tables: STUDENT and COURSE, and a third table, ENROLLMENT, to link them.
1. STUDENT Table
This table stores information about students.
Primary Key (PK): StudentID (ensures each student record is unique and not NULL).
Domain Constraints:
Name: VARCHAR(50), not NULL.
Major: VARCHAR(10), not NULL.
Age: INT, must be between 17 and 100 (CHECK (Age >= 17 AND Age <= 100)).
Key Constraint: Email (ensures each email is unique, often used as an alternate login).
StudentID (PK) | Name | Major | Age | Email (CK) |
S101 | Dinesh Kumar | CSE | 20 | |
S102 | Bob Smith | ECE | 21 | |
S103 | Charlie Brown | ME | 19 | |
S104 | Diana Prince | CSE | 22 | |
S105 | Emily White | BBA | 18 |
What a DBMS would prevent:
Inserting a new student with StudentID = S101 (violates PK uniqueness).
Inserting a student with Name = NULL (violates NOT NULL domain constraint).
Inserting a student with Age = 15 or Age = 105 (violates Age domain/CHECK constraint).
Inserting a student with Email = 'dinesh@uni.edu' (violates Key Constraint for Email).
2. COURSE Table
This table stores information about the courses offered.
Primary Key (PK): CourseID (ensures each course is unique and not NULL).
Domain Constraints:
Title: VARCHAR(100), not NULL.
Credits: INT, must be between 1 and 4 (CHECK (Credits >= 1 AND Credits <= 4)).
Department: VARCHAR(10), not NULL.
CourseID (PK) | Title | Credits | Department |
CS301 | Database Management Systems | 4 | CSE |
MA101 | Calculus I | 3 | MATH |
ECE205 | Digital Logic Design | 4 | ECE |
ME450 | Thermodynamics | 3 | ME |
BBA101 | Introduction to Business | 3 | BBA |
What a DBMS would prevent:
Inserting a new course with CourseID = CS301 (violates PK uniqueness).
Inserting a course with Title = NULL (violates NOT NULL domain constraint).
Inserting a course with Credits = 0 or Credits = 5 (violates Credits domain/CHECK constraint).
3. ENROLLMENT Table
This table links students to courses, representing which student is taking which course and their grade. This table demonstrates Referential Integrity.
Primary Key (PK): A composite primary key consisting of (StudentID, CourseID) (ensures a student can enroll in a specific course only once).
Foreign Key (FK):
StudentID references STUDENT.StudentID (ensures only valid students can be enrolled).
CourseID references COURSE.CourseID (ensures students can only enroll in valid, existing courses).
Domain Constraint: Grade: CHAR(2), must be a valid grade (e.g., 'A+', 'A', 'B+', ..., 'F').
StudentID (PK, FK) | CourseID (PK, FK) | Grade | EnrollmentDate |
S101 | CS301 | A | 2024-01-15 |
S101 | MA101 | B+ | 2024-01-15 |
S102 | ECE205 | A- | 2024-01-20 |
S103 | CS301 | B | 2024-01-18 |
S104 | ME450 | C+ | 2024-01-22 |
S105 | BBA101 | A | 2024-01-17 |
What a DBMS would prevent:
Inserting (S101, CS301, B) after (S101, CS301, A) (violates composite PK uniqueness, as Dinesh is already enrolled in CS301).
Inserting (S999, CS301, A) if S999 does not exist in the STUDENT table (violates StudentID Foreign Key constraint).
Inserting (S101, CS999, A) if CS999 does not exist in the COURSE table (violates CourseID Foreign Key constraint).
Inserting (S101, CS301, 'Z') (violates Grade domain/CHECK constraint, assuming 'Z' is not a valid grade).
These examples illustrate how integrity constraints are applied at the table level to ensure the reliability and validity of the data within a database system.
2. Referential Integrity Constraint (Foreign Key Rule)
This constraint ensures that relationships between tables are valid and maintained. It prevents the creation of "orphan" records that reference non-existent records in another table.
Rule: An attribute(s) designated as a Foreign Key (FK) in a referencing table must match an existing value in the Primary Key (PK) of the referenced table, OR the Foreign Key value must be NULL.
Purpose: To maintain the structural consistency of linked data.
Example (Enrollment & Course Tables):
Referenced Table (Parent): Course (CourseID is PK).
Referencing Table (Child): Enrollment (CourseID is FK).
EnrollmentID | StudentID | CourseID (FK) | Grade |
E1 | S101 | CS301 | Valid, if CS301 exists in the Course table. |
E2 | S102 | NULL | Valid, if the FK allows NULLs (Student has not yet chosen a course). |
E3 | S103 | CS999 | Violation: If CS999 does not exist as a PK in the Course table. |
3. Domain Constraint (Attribute Rule)
This constraint specifies the set of valid values that an attribute can take.
Rule: An attribute value must be an atomic (indivisible) value and must belong to a predefined domain, which includes data type, format, and range.
Purpose: To limit the type, size, and range of values an attribute can store, ensuring data validity.
Example (Student Table):
Attribute: StudentID
Constraint: VARCHAR(5), starts with 'S' + 4 digits. Not NULL, UNIQUE.
Purpose: Ensures consistent and valid student identifiers.
Attempted Value | Constraint Check | Result | Explanation |
'S1023' | Matches format, not NULL, if unique. | PASS | A valid and well-formatted Student ID. |
'A1234' | Starts with 'A'. | FAIL | Does not start with 'S'. |
'S102' | Too short (4 chars, not 5). | FAIL | Violates size/format. |
NULL | Is NULL. | FAIL | Violates NOT NULL constraint. |
'S1023' (if 'S1023' already exists) | Value 'S1023' is not unique. | FAIL | Violates UNIQUE constraint. |
4. Key Constraint (Uniqueness Rule)
This is an extension of the Entity Integrity constraint, applying to all candidate keys.
Rule: All attributes designated as a Candidate Key must have unique values. A Primary Key is a special type of Candidate Key, but a table can have other Candidate Keys.
Purpose: To provide alternative, unique identifiers for records in a table.
Example (Student Table): . This table stores basic login information for students, where StudentID is the primary identifier, and UniversityEmail is another unique key.
Primary Key (PK): StudentID (ensures each student record is uniquely identified and not NULL).
Candidate Key (CK): UniversityEmail (ensures each student has a unique email for login, and it cannot be NULL).
StudentID (PK) | Name | UniversityEmail (CK) | PasswordHash |
S101 | Dinesh Kumar | $2a$10$xyz... | |
S102 | Bob Smith | $2a$10$abc... | |
S103 | Charlie Brown | $2a$10$def... | |
S104 | Diana Prince | $2a$10$ghi... |
Understanding the Key Constraints with Examples:
Let's illustrate how the DBMS would handle attempted insertions or updates based on these constraints:
Attempt to Insert a Duplicate StudentID (PK Violation):
Attempt: INSERT INTO Student_Login_Info VALUES ('S101', 'Alice Johnson', 'alice@uni.edu', '$2a$10$jkl...')
Result: FAIL
Explanation: The StudentID 'S101' already exists. The Primary Key constraint (on StudentID) ensures that each row is unique.
Attempt to Insert a Duplicate UniversityEmail (CK Violation):
Attempt: INSERT INTO Student_Login_Info VALUES ('S105', 'Emily White', 'dinesh@uni.edu', '$2a$10$mno...')
Result: FAIL
Explanation: The UniversityEmail 'dinesh@uni.edu' already exists for 'S101'. The Candidate Key constraint (on UniversityEmail) ensures all values in this column are unique.
Attempt to Insert NULL into StudentID (PK NOT NULL Violation):
Attempt: INSERT INTO Student_Login_Info VALUES (NULL, 'New Student', 'new@uni.edu', '$2a$10$pqr...')
Result: FAIL
Explanation: The Primary Key (StudentID) cannot accept NULL values. This is a fundamental part of entity integrity.
Attempt to Insert NULL into UniversityEmail (CK NOT NULL Violation, assuming it's also set as NOT NULL):
Attempt: INSERT INTO Student_Login_Info VALUES ('S105', 'New Student', NULL, '$2a$10$stu...')
Result: FAIL
Explanation: Similar to the Primary Key, Candidate Keys are typically also defined as NOT NULL to ensure they can reliably identify a record.
Successful Insertion (Satisfies all constraints):
Attempt: INSERT INTO Student_Login_Info VALUES ('S105', 'Emily White', 'emily@uni.edu', '$2a$10$vwx...')
Result: PASS
Explanation: S105 is unique, emily@uni.edu is unique, and neither are NULL.
This table effectively demonstrates how Key Constraints, particularly Primary Keys and other Candidate Keys, are crucial for maintaining the uniqueness and identifiability of records within a database.
5. User-Defined Integrity Constraint
These are custom rules defined by the user or DBA that go beyond the basic structural constraints. They often reflect specific business logic.
Rule: Any specific business requirement that must hold true for the data. These are typically enforced using CHECK constraints or database triggers.
Purpose: To enforce complex business rules that relational models don't inherently handle.
Example (Course Table):
Rule: No course can be offered for more than 4 Credits.
SQL CHECK Constraint: CHECK (Credits <= 4)
CourseID | Title | Credits | Status |
CS301 | DBMS | 4 | Valid |
CS501 | AI | 5 | Violation: Breaks the User-Defined CHECK constraint. |
Data Manipulation Language (DML):
Imagine a database as a gigantic, organized filing cabinet. While Data Definition Language (DDL) is about designing and building that cabinet (creating tables, defining rules), Data Manipulation Language (DML) is about what you do inside the cabinet: putting files in, taking them out, changing information on them, and finding specific files.
DML commands are the "action verbs" of SQL. They allow you to read, insert, modify, and delete data from your database tables. These commands are essential for any application that interacts with stored data.
The Four Core DML Operations (CRUD)
There are four primary DML commands, often remembered by the acronym CRUD:
Create (Insert)
Read (Select)
Update
Delete
Let's use our familiar STUDENT table as an example:
StudentID | Name | Major | Age | |
S101 | Dinesh Kumar | CSE | 20 | |
S102 | Sumit Saxena | ECE | 21 | |
S103 | Sumiti Khosla | ME | 19 | |
S104 | Shikha | CSE | 22 |
1. INSERT Statement (Create Data)
We will now use the INSERT command to add a new student, Priya, to the table.
SQL
INSERT INTO Student (StudentID, Name, Major, Age, Email)
VALUES (105, 'Priya', 'Art History', 22, 'priya.a@uni.edu');
StudentID: 105
Name: 'Priya'
Major: 'Art History'
Age: 22
Email: 'priya.a@uni.edu'
Output Table After INSERT
The table now contains the four original records plus the new record for Priya.
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 20 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Business | 20 | |
105 | Priya | Art History | 22 |
2. SELECT Statement (Read Data)
The SELECT query is the most crucial Data Manipulation Language (DML) command. Its primary purpose is to retrieve data from one or more tables in a database. It doesn't modify the data; it simply reads and presents it.
The basic structure of a SELECT query is:
SQL
SELECT Column(s)
FROM TableName
WHERE Condition(s); -- (Optional)
We'll use the final state of the Student table from the previous example for all demonstrations:
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 20 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Business | 20 | |
105 | Priya | Art History | 22 |
1. Selecting All Columns (∗)
This query retrieves all columns and all rows from the table. The asterisk (*) is a wildcard for "all columns."
SQL Query | Purpose |
SELECT * FROM Student; | Selects all data from the Student table. |
Output Table 1 (SELECT All)
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 20 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Business | 20 | |
105 | Priya | Art History | 22 |
2. Selecting Specific Columns
This query retrieves only the specified columns (Name and Major).
SQL Query | Purpose |
SELECT Name, Major FROM Student; | Selects only the student's name and their major. |
Output Table 2 (Specific Columns)
Name | Major |
Dinesh Kumar | Computer Science |
Sumati Khosla | Biology |
Shikha | History |
Ronit Saxena | Business |
Priya | Art History |
3. Selecting with a WHERE Clause (Filtering Rows)
The WHERE clause is used to filter records and retrieve only those that meet a specified condition.
SQL Query | Purpose |
SELECT Name, Age FROM Student WHERE Major = 'Business'; | Selects the Name and Age of students whose Major is exactly 'Business'. |
Output Table 3 (Filtering Rows)
Name | Age |
Ronit | 20 |
4. Selecting with Multiple Conditions (AND)
You can combine multiple conditions using logical operators like AND to narrow down the results.
SQL Query | Purpose |
SELECT Name, Major, Age FROM Student WHERE Age > 20 AND StudentID < 105; | Selects students who are older than 20 AND have an ID less than 105. |
Output Table 4 (Multiple Conditions)
Name | Major | Age |
Shikha | History | 21 |
5. Selecting with Ordering (ORDER BY)
The ORDER BY clause sorts the result set in ascending (ASC, default) or descending (DESC) order based on one or more columns.
SQL Query | Purpose |
SELECT Name, Age FROM Student ORDER BY Age DESC; | Selects all names and ages, but orders them with the oldest student first (Descending Age). |
Output Table 5 (Ordering)
Name | Age |
Priya | 22 |
Shikha | 21 |
Dinesh Kumar | 20 |
Ronit Saxena | 20 |
Sumati Khosla | 19 |
3. UPDATE Statement (Modify Data)
The UPDATE statement is a crucial Data Manipulation Language (DML) command used to modify existing data within a database table. It changes the values in specified columns for one or more rows that meet a defined condition.
UPDATE Statement Syntax
The core elements of the UPDATE statement are:
UPDATE TableName: Specifies the table you want to modify.
SET Column = NewValue: Specifies the column(s) to change and their new value(s).
WHERE Condition: Crucially, this clause specifies which rows will be updated. If the WHERE clause is omitted, the UPDATE operation will apply the changes to all rows in the table.
SQL
UPDATE TableName
SET Column1 = NewValue1, Column2 = NewValue2, ...
WHERE Condition;
Example: Updating Records
We will use the existing Student table and demonstrate two different UPDATE scenarios.
Initial Student Table State
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 20 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Business | 20 | |
105 | Priya | Art History | 22 |
Example 1: Updating a Single Record
Scenario: Ronit Saxena decides to change his Major from 'Business' to 'Finance'.
SQL Query | Purpose |
UPDATE Student SET Major = 'Finance' WHERE Name = 'Ronit Saxena'; | Updates the Major column to 'Finance' only for the row where Name is 'Ronit Saxena'. |
Output Table After Example 1
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 20 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Finance | 20 | |
105 | Priya | Art History | 22 |
Example 2: Updating Multiple Records
Scenario: All students aged 20 are getting a revised email domain (@college.edu) and a one-year age adjustment.
SQL Query | Purpose |
UPDATE Student SET Age = Age + 1, Email = REPLACE(Email, '@uni.edu', '@college.edu') WHERE Age = 20; | Updates two columns (Age and Email) for multiple rows where the starting Age is 20 (Dinesh Kumar and Ronit Saxena). |
Output Table After Example 2
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 21 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Finance | 21 | |
105 | Priya | Art History | 22 |
4 DELETE Statement (Remove Data)
The DELETE statement is a crucial Data Manipulation Language (DML) command used to remove existing rows (records) from a table.
It is important to note that the DELETE statement only affects the data (rows); it does not remove the table itself or its structure.
DELETE Statement Syntax
The key elements of the DELETE statement are:
DELETE FROM TableName: Specifies the table from which rows will be removed.
WHERE Condition: Crucially, this clause specifies which rows will be deleted. If the WHERE clause is omitted, the DELETE operation will remove all rows from the table, leaving an empty table structure.
SQL
DELETE FROM TableName
WHERE Condition;
Example: Deleting Records
We will use the final state of the Student table from the previous examples to demonstrate two different DELETE scenarios.
Initial Student Table State (After all previous updates)
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 21 | |
102 | Sumati Khosla | Biology | 19 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Finance | 21 | |
105 | Priya | Art History | 22 |
Example 1: Deleting a Single Record
Scenario: Sumati Khosla graduates and needs to be removed from the active student list.
SQL Query | Purpose |
DELETE FROM Student WHERE Name = 'Sumati Khosla'; | Deletes the single row where the Name is 'Sumati Khosla'. |
Export to Sheets
Output Table After Example 1
The row for Sumati Khosla (StudentID 102) is permanently removed.
StudentID | Name | Major | Age | |
101 | Dinesh Kumar | Computer Science | 21 | |
103 | Shikha | History | 21 | |
104 | Ronit Saxena | Finance | 21 | |
105 | Priya | Art History | 22 |
Example 2: Deleting Multiple Records
Scenario: All students who are 21 years old need to be archived (removed from the main table).
SQL Query | Purpose |
DELETE FROM Student WHERE Age = 21; | Deletes all rows where the Age is 21 (Dinesh Kumar, Shikha, and Ronit Saxena). |
Output Table After Example 2
The rows for Dinesh Kumar, Shikha, and Ronit Saxena are all permanently removed.
StudentID | Name | Major | Age | |
105 | Priya | Art History | 22 |
Comments