Unit 1: Database Management System (DBMS) (12 Hours)

Imagine a central 'Database' connected to 'DBMS Software'. Surrounding this core, you have 'Hardware' (servers, storage) providing the infrastructure, and 'Users' (administrators, end-users, application programmers) interacting with the DBMS software to access and manipulate the data. All these components form the Database System.

DBMS (Database Management System)

A Database Management System (DBMS) is a software system that enables users to define, create, maintain, and control access to the database. It acts as an interface between the user/applications and the database itself.

Role of DBMS:

• Data Definition: Allows users to specify the data types, structures, and constraints for the data to be stored.
• Data Manipulation: Provides functions for retrieving, inserting, deleting, and modifying data.
• Data Control: Manages access control, security, concurrency, and recovery mechanisms.

Components of DBMS:

• Hardware: Physical devices like computers, storage devices (HDDs, SSDs), network devices that host the database and DBMS software.
• Software: The DBMS itself (e.g., MySQL), operating system, network software, and application programs.
• Data: The actual information stored in the database, along with its metadata (data about data, schema).
• Users: Individuals who interact with the database system, including database administrators (DBAs), application programmers, and end-users.
• Procedures: Instructions and rules that govern the design and use of the database system, such as how to log in, backup data, or handle system failures.

Examples of DBMS:

• MySQL: A popular open-source relational DBMS, widely used for web applications.
• PostgreSQL: Another powerful open-source relational DBMS, known for its extensibility and standards compliance.
• Oracle Database: A commercial, enterprise-grade relational DBMS, known for its robustness and advanced features.
• Microsoft SQL Server: A commercial relational DBMS from Microsoft, commonly used in enterprise environments, especially with Windows-based applications.

1.2 Field, Record, Object, Primary Key, Alternate Key, Candidate Key

These terms are fundamental to understanding the structure and organization of data within a relational database.

Field (Attribute/Column)

A Field, also known as an attribute or column, is the smallest logical unit of data that represents a specific characteristic or property of an entity. It holds a single type of data.

Example: In a 'Students' table, 'StudentID', 'StudentName', 'DateOfBirth', and 'Major' would be fields.

Record (Tuple/Row)

A Record, also known as a tuple or row, is a collection of related fields that together describe a single entity or instance. Each record represents a complete set of information about one item in a table.

Example: In a 'Students' table, a record might be (101, 'Alice Smith', '2005-03-15', 'Computer Science').

Object

In the context of object-oriented databases or conceptual modeling, an Object is an entity that has both attributes (data) and methods (operations or functions that can be performed on the data). It encapsulates data and behavior.

Example: A 'Student' object might have attributes like studentID, name, major, and methods like enrollCourse() or calculateGPA().

Keys in a Database

Keys are crucial for uniquely identifying records and establishing relationships between tables.

Candidate Key

A Candidate Key is a minimal set of attributes (one or more) that can uniquely identify each record in a table. 'Minimal' means that if any attribute is removed from the set, it can no longer uniquely identify records.

Example: Consider a 'Students' table with fields: StudentID, RollNo, Email, Name, Address.

• StudentID alone can uniquely identify a student.
• RollNo alone can uniquely identify a student.
• Email alone can uniquely identify a student (assuming unique emails).

Primary Key

The Primary Key is a candidate key that is chosen by the database designer to uniquely identify each record in a table. It must satisfy two main conditions: it must be unique for every record, and it cannot contain NULL values (NOT NULL constraint).

Example: From the candidate keys {StudentID}, {RollNo}, {Email}, the designer might choose StudentID as the Primary Key for the 'Students' table.

Alternate Key

Alternate Keys are the candidate keys that were not chosen to be the primary key. They still have the ability to uniquely identify records but are not designated as the primary identifier.

Example: If StudentID is chosen as the Primary Key, then {RollNo} and {Email} would be alternate keys.

Super Key

A Super Key is any set of attributes that uniquely identifies a record in a table. It can contain extra attributes that are not necessary for unique identification, unlike a candidate key which must be minimal.

Example: In the 'Students' table, {StudentID} is a candidate key and thus a super key. {StudentID, Name} is also a super key, but not a candidate key because Name is redundant for unique identification (StudentID alone is sufficient).

Composite Key

A Composite Key is a primary key that consists of two or more attributes (fields) whose values, when combined, uniquely identify each record in a table. No single attribute within the composite key can uniquely identify a record on its own.

Example: In a 'Enrollment' table with fields StudentID, CourseID, EnrollmentDate, neither StudentID nor CourseID alone can uniquely identify an enrollment record (a student can enroll in multiple courses, a course can have multiple students). However, the combination {StudentID, CourseID} can uniquely identify a specific student's enrollment in a specific course. Thus, {StudentID, CourseID} is a composite primary key.

Foreign Key

A Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. It establishes a link or relationship between two tables. The foreign key in the child table references the primary key (or a unique key) in the parent table.

Example:

Table: Students

Table: Majors

In the 'Students' table, MajorID is a Foreign Key that references the MajorID (Primary Key) in the 'Majors' table. This links students to their respective majors.

1.3 Advantages of Using DBMS

Using a Database Management System offers numerous benefits over traditional file-based systems, enhancing data management efficiency, integrity, and security.

• Data Redundancy Control: DBMS helps minimize duplication of data by storing it in a centralized, controlled manner. For example, student details are stored once in a student table, rather than multiple times in different department files.
• Data Inconsistency Reduction: By controlling redundancy, DBMS also reduces the chances of data inconsistency, where the same data item has different values in different places. If a student's address changes, it only needs to be updated in one place, ensuring all applications see the consistent, correct address.
• Data Sharing: A DBMS facilitates controlled sharing of data among multiple users and applications. Different departments (e.g., Admissions, Academics, Finance) can access the same student data simultaneously, with appropriate permissions.
• Data Integrity: DBMS enforces data integrity rules and constraints to ensure the accuracy and reliability of data. This includes domain constraints (e.g., age must be positive), entity integrity (primary key cannot be NULL), and referential integrity (foreign key must reference an existing primary key).
• Data Security: DBMS provides mechanisms for data security, such as user authentication, authorization (GRANT/REVOKE permissions), and encryption. This ensures that only authorized users can access specific data and perform allowed operations.
• Backup and Recovery: DBMS includes utilities for backing up the database and recovering it to a consistent state in case of hardware failure, software error, or other disasters. This protects against data loss.
• Data Independence: DBMS provides data independence, meaning changes to the database schema at one level do not require changes at higher levels.
  • Logical Data Independence: The ability to change the conceptual schema (e.g., add a new attribute) without affecting the external schemas or application programs.
  • Physical Data Independence: The ability to change the internal schema (e.g., change storage device, file organization) without affecting the conceptual schema or application programs.
• Concurrent Access Support: DBMS allows multiple users to access and modify the same data concurrently without interfering with each other's operations or compromising data consistency. It uses locking mechanisms and transaction management to achieve this.
• Standardized Access: DBMS provides a standardized language, typically SQL (Structured Query Language), for defining, manipulating, and controlling data. This simplifies development and provides a common interface across different applications.

1.4 DDL and DML

SQL (Structured Query Language) is the standard language for interacting with relational databases. It is broadly categorized into several sub-languages, each serving a distinct purpose.

DDL (Data Definition Language)

DDL commands are used to define, modify, or delete the structure of database objects like tables, views, indexes, etc. They deal with the schema of the database.

• CREATE: Used to create new database objects.

  CREATE TABLE Students (
      StudentID INT PRIMARY KEY,
      StudentName VARCHAR(100) NOT NULL,
      Major VARCHAR(50)
  );

• ALTER: Used to modify the structure of an existing database object.

  ALTER TABLE Students
  ADD COLUMN Email VARCHAR(100) UNIQUE;

• DROP: Used to delete an existing database object entirely.

  DROP TABLE Students;

• TRUNCATE: Used to remove all records from a table, but keeps the table structure. It's faster than DELETE for all rows as it doesn't log individual row deletions.

  TRUNCATE TABLE Students;

• RENAME: Used to change the name of a database object.

  ALTER TABLE Students RENAME TO UniversityStudents;

  (Syntax may vary slightly between DBMS, e.g., RENAME TABLE Students TO UniversityStudents; in MySQL)

DML (Data Manipulation Language)

DML commands are used for managing data within schema objects. They deal with inserting, retrieving, modifying, and deleting data records.

• SELECT: Used to retrieve data from one or more tables.

  SELECT StudentName, Major
  FROM Students
  WHERE StudentID = 101;

• INSERT: Used to add new records (rows) into a table.

  INSERT INTO Students (StudentID, StudentName, Major)
  VALUES (101, 'Alice Smith', 'Computer Science');

• UPDATE: Used to modify existing data in a table.

  UPDATE Students
  SET Major = 'Electrical Engineering'
  WHERE StudentID = 101;

• DELETE: Used to remove existing records (rows) from a table.

  DELETE FROM Students
  WHERE StudentID = 101;

DCL (Data Control Language)

DCL commands are used to control access to data in the database. They manage user permissions and privileges.

• GRANT: Used to give specific privileges to a user or role.

  GRANT SELECT, INSERT ON Students TO user_manager;

• REVOKE: Used to remove specific privileges from a user or role.

  REVOKE INSERT ON Students FROM user_manager;

TCL (Transaction Control Language)

TCL commands are used to manage transactions within the database. A transaction is a sequence of operations performed as a single logical unit of work.

• COMMIT: Used to permanently save the changes made during the current transaction to the database.

  INSERT INTO Students (StudentID, StudentName, Major) VALUES (102, 'Bob Johnson', 'Physics');
  COMMIT;

• ROLLBACK: Used to undo changes made during the current transaction, reverting the database to its state before the transaction began.

  INSERT INTO Students (StudentID, StudentName, Major) VALUES (103, 'Charlie Brown', 'Chemistry');
  ROLLBACK; -- Changes for Charlie Brown are undone

• SAVEPOINT: Used to create a point within a transaction to which you can later roll back.

  SAVEPOINT S1;
  -- Perform some operations
  ROLLBACK TO S1; -- Rolls back to Savepoint S1

1.5 Database Models

Database models define the logical structure of a database and how data is stored, organized, and manipulated. They dictate the relationships between data elements.

Network Model (CODASYL)

The Network Model organizes data in a graph-like structure, allowing a record to have multiple parent and multiple child records. It uses "sets" to define one-to-many relationships, where an owner record can have many member records, and a member record can be owned by multiple owners.

• Structure: Records are linked via pointers, forming a complex graph. Relationships are defined explicitly.
• Flexibility: More flexible than the hierarchical model as it allows many-to-many relationships directly.
• Limitations: Complex to design and manage. Navigating the database requires a detailed understanding of its structure and specific programming for path traversal. CODASYL (Conference on Data Systems Languages) defined the standards for this model.

Example (Conceptual): Imagine a student who can enroll in multiple courses, and a course can have multiple students. In a network model, a 'Student' record could be linked to several 'Course' records, and a 'Course' record could be linked to several 'Student' records directly, forming a mesh. Also, a 'Student' could be linked to a 'Department' and a 'Project', and the 'Project' could also be linked to 'Faculty'.

Hierarchical Model

The Hierarchical Model organizes data in a tree-like structure, where each record type (segment) has a single parent and can have multiple child segments. Relationships are strictly one-to-many, flowing from parent to child.

• Structure: Data is organized like an inverted tree, with a root segment at the top. Each child segment can have only one parent.
• Flexibility: Simple to understand for one-to-many relationships.
• Limitations: Difficulty in representing many-to-many relationships, data redundancy can occur. Querying requires traversing the tree from the root, which can be inefficient for complex queries. IBM's Information Management System (IMS) is a prominent example.

Example (Conceptual): A 'Department' could be the parent of multiple 'Courses', and each 'Course' could be the parent of multiple 'Students'. Here, a 'Student' can only belong to one 'Course' in this specific hierarchy, and a 'Course' to one 'Department'. If a student takes courses from different departments, redundancy would be needed or a complex workaround.

Relational Database Model

The Relational Database Model organizes data into two-dimensional tables, also known as relations. Each table consists of rows (records/tuples) and columns (fields/attributes). Relationships between tables are established through common fields (foreign keys).

• Structure: Data is stored in tables. Each table has a unique name, and columns have unique names within the table. Rows are unordered.
• Flexibility: Highly flexible and easy to understand. Supports complex queries using relational algebra and SQL. Many-to-many relationships are handled by creating an intermediate linking table.
• Codd's 12 Rules: Dr. E.F. Codd proposed 12 rules (later 13, including rule zero) that define what a relational database should be. These rules emphasize data integrity, independence, and the use of a high-level language (like SQL) for data manipulation. Key aspects include data representation as tables, guaranteed access to data, systematic treatment of NULL values, and physical/logical data independence.
• Relational Algebra: A procedural query language that defines a set of operations (e.g., SELECT, PROJECT, JOIN, UNION, INTERSECT, DIFFERENCE) on relations (tables) to produce new relations. It forms the theoretical basis for SQL.

Example (Conceptual):

Students Table:

Majors Table:

The MajorID in the Students table links to the MajorID in the Majors table.

Comparison of Database Models

1.6 Concept of Normalization

Normalization is a systematic process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves decomposing tables into smaller, related tables based on a set of rules called normal forms.

Purpose of Normalization:

• Reduce Redundancy: Eliminates duplicate data, saving storage space and improving efficiency.
• Eliminate Anomalies: Prevents data inconsistencies that can arise from redundant data during insertion, deletion, and update operations.
  • Insertion Anomaly: Cannot insert a new record without also inserting data for related fields, even if that data is not yet available.
  • Deletion Anomaly: Deleting a record unintentionally removes other related, necessary data.
  • Update Anomaly: Updating a data item requires updating multiple records, leading to inconsistencies if not all copies are updated.
• Improve Data Integrity: Ensures data accuracy and consistency by enforcing rules and relationships.
• Better Database Design: Leads to a more logical, flexible, and robust database structure.

Normal Forms:

The most common normal forms are 1NF, 2NF, and 3NF.

1NF (First Normal Form)

A table is in 1NF if it satisfies two conditions:

1. All attributes (columns) contain atomic (indivisible) values. There are no multi-valued attributes.
2. There are no repeating groups of columns. Each column should contain a single value.

Example Table (Unnormalized):

This table is not in 1NF because Course1/Grade1 and Course2/Grade2 are repeating groups. Also, if a student takes more than two courses, we'd need more columns, or if they take less, we'd have NULLs.

To 1NF: Separate repeating groups into a new table or restructure.

Table: Students (1NF)

Table: Enrollments (1NF)

Primary Key for 'Students' is StudentID. Primary Key for 'Enrollments' is {StudentID, CourseID} (composite key).

2NF (Second Normal Form)

A table is in 2NF if it is in 1NF AND no non-key attribute is partially dependent on the primary key. This means that every non-key attribute must depend on the entire primary key, not just a part of it.

This rule applies primarily to tables with composite primary keys.

Consider the 'Enrollments' table from above:

Table: Enrollments (1NF)

Primary Key: {StudentID, CourseID}

Here, Grade depends on {StudentID, CourseID} (which student got what grade in which course). This is fine.

However, CourseName and InstructorName depend only on CourseID (a part of the primary key), not on the full composite key {StudentID, CourseID}. This is a partial dependency, violating 2NF.

To 2NF: Remove partial dependencies by creating new tables.

Table: Students (still 1NF/2NF)

Table: Enrollments (2NF)

Table: Courses (2NF)

3NF (Third Normal Form)

A table is in 3NF if it is in 2NF AND there are no transitive dependencies. A transitive dependency exists when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key.

In simpler terms, non-key attributes should depend only on the primary key, and not on other non-key attributes.

Consider the 'Courses' table from above:

Table: Courses (2NF)

Primary Key: CourseID

Here, CourseName depends on CourseID. This is fine.

However, InstructorDept depends on InstructorName (a non-key attribute), and InstructorName depends on CourseID. This is a transitive dependency: CourseID -> InstructorName -> InstructorDept. This violates 3NF.

To 3NF: Remove transitive dependencies by creating new tables.

Table: Students (still 1NF/2NF/3NF)

Table: Enrollments (still 1NF/2NF/3NF)

Table: Courses (3NF)

Table: Instructors (3NF)

Now, our tables are in 3NF, minimizing redundancy and improving data integrity.

1.7 Centralized vs Distributed Database

Databases can be deployed in various architectures, with centralized and distributed being two primary models.

Centralized Database

A Centralized Database is a database system where all data is stored, maintained, and accessed from a single physical location (e.g., a single server or data center).

• Characteristics:
  • Single point of control and management.
  • All data resides on one machine or a tightly coupled cluster.
  • Users connect to this central location to access data.
• Advantages:
  • Simpler Management: Easier to administer, backup, and restore as everything is in one place.
  • Data Consistency: Easier to maintain global data consistency and integrity.
  • Lower Initial Cost: Can be cheaper to set up initially for smaller scales.
• Disadvantages:
  • Single Point of Failure: If the central server fails, the entire system becomes unavailable.
  • Performance Bottleneck: Can suffer from performance issues if many users or applications try to access data simultaneously, especially from geographically distant locations.
  • Scalability Limitations: Scaling vertically (adding more resources to a single server) has limits.
  • Network Dependency: Heavily relies on the network connection to the central server.

Distributed Database

A Distributed Database is a database system where data is stored across multiple physical locations (nodes) that are interconnected by a communication network. The system appears as a single logical database to the user.

• Characteristics:
  • Data is fragmented (divided) and/or replicated across different nodes.
  • Nodes are geographically dispersed or located in different data centers.
  • Managed by a Distributed Database Management System (DDBMS).
• Fragmentation: Dividing a table into smaller parts and storing them at different sites.
  • Horizontal Fragmentation: Dividing a table by rows (e.g., storing student records for different regions in different locations).
  • Vertical Fragmentation: Dividing a table by columns (e.g., storing student personal details at one site and academic details at another).
• Replication: Storing identical copies of data at multiple sites.
  • Improves availability and read performance.
  • Increases complexity for ensuring consistency during updates.
• Advantages:
  • Improved Availability: If one node fails, others can still provide data.
  • Enhanced Reliability: Data is less susceptible to complete loss due to site failures.
  • Better Performance: Data can be stored closer to the users who need it, reducing network latency. Queries can be processed in parallel across multiple nodes.
  • Easier Scalability: Can scale horizontally by adding more nodes.
  • Local Autonomy: Local sites can manage their own data more independently.
• Disadvantages:
  • Increased Complexity: Design, implementation, and management are significantly more complex.
  • Data Consistency Challenges: Ensuring data consistency across multiple, distributed copies is difficult.
  • Higher Cost: More expensive to set up and maintain due to complex software and network infrastructure.
  • Security Challenges: More points of entry and network communication make security more challenging.

CAP Theorem Basics

The CAP Theorem (Consistency, Availability, Partition Tolerance) states that it is impossible for a distributed data store to simultaneously provide more than two out of the three guarantees:

• Consistency (C): All clients see the same data at the same time, regardless of which node they connect to.
• Availability (A): Every request receives a response, without guarantee that it contains the most recent version of the information.
• Partition Tolerance (P): The system continues to operate even if there are network failures (partitions) that prevent some nodes from communicating with others.

In a distributed system, network partitions are inevitable. Therefore, a distributed system must choose between Consistency and Availability during a partition:

• CP System: Prioritizes Consistency and Partition Tolerance. If a partition occurs, the system will choose to be unavailable (or return an error) rather than risk inconsistent data. (e.g., traditional RDBMS with distributed transactions).
• AP System: Prioritizes Availability and Partition Tolerance. If a partition occurs, the system will remain available but might return stale or inconsistent data. (e.g., many NoSQL databases like Cassandra).

1.8 Database Security

Database security involves protecting the database and its data from unauthorized access, misuse, disclosure, destruction, or modification. It's a critical aspect of any database system.

Threats to Database Security:

• Unauthorized Access: Individuals gaining access to data or the database system without proper permissions.
• SQL Injection: A common web security vulnerability where an attacker uses malicious SQL code to manipulate the database or gain access to sensitive information.

  Example: If an input field takes ' OR 1=1 --, it might bypass authentication: SELECT * FROM Users WHERE Username = '' OR 1=1 --' AND Password = '...'

• Privilege Escalation: A user gaining higher access privileges than they are authorized for.
• Data Tampering/Corruption: Intentional or unintentional modification or damage to data.
• Denial of Service (DoS): Attacks aimed at making the database system unavailable to legitimate users.
• Insider Threats: Malicious actions by current or former employees, contractors, or business partners.

Security Measures:

• Authentication: Verifying the identity of a user attempting to access the database. This typically involves username/password, but can also include multi-factor authentication, biometric verification, etc.
• Authorization: Granting specific permissions or privileges to authenticated users, defining what actions they can perform (e.g., read, write, update, delete) and on which database objects (tables, views).
• Encryption: Converting data into a coded form to prevent unauthorized access.
  • Encryption at Rest: Encrypting data stored on disks.
  • Encryption in Transit: Encrypting data as it moves over networks (e.g., using SSL/TLS).
• Auditing: Recording and reviewing database activities (who accessed what, when, and what operations were performed) to detect suspicious behavior and ensure compliance.
• Regular Security Patches: Applying updates and patches to the DBMS software and operating system to fix known vulnerabilities.

Access Control:

Access control mechanisms are central to database security.

• GRANT/REVOKE: As discussed in DCL, these SQL commands are used to manage user permissions.

  GRANT SELECT ON Employees TO SalesDept;
  REVOKE DELETE ON FinancialRecords FROM JuniorStaff;

• Views for Security: A view is a virtual table based on the result-set of a SQL query. Views can restrict access to sensitive data by only showing specific columns or rows, or by joining multiple tables to present only relevant, non-sensitive aggregated data.

  Example: Create a view for HR staff that only shows employee names and departments, but not salaries.

  CREATE VIEW HR_Employee_View AS
  SELECT EmployeeID, Name, Department
  FROM Employees;
  GRANT SELECT ON HR_Employee_View TO HR_User;

• User Roles and Permissions: Instead of granting permissions directly to individual users, roles (e.g., 'DB_Admin', 'Data_Analyst', 'App_User') are created with a predefined set of privileges. Users are then assigned to these roles. This simplifies management and ensures consistent permission sets.

Backup Strategies:

Regular and robust backup strategies are essential for disaster recovery and maintaining data availability.

• Full Backup: A complete copy of the entire database.
• Differential Backup: Backs up all changes made since the last full backup.
• Incremental Backup: Backs up only the data that has changed since the last full or incremental backup.
• Transaction Logs: Record all database changes, allowing for point-in-time recovery.
• Offsite Storage: Storing backups in a different physical location to protect against site-specific disasters.
• Regular Testing: Periodically testing backup and recovery procedures to ensure they work as expected.