Database Systems Exam Notes

Fundamentals of Data and Database Management

• Data Concept and Characteristics:

  • Definition: Data is a collection of raw facts and figures that can be processed to obtain meaningful information.

  • Example: Raw data input consisting of 101, Khushi, 20, BCA represents attributes of a student.

  • Information Transformation: After processing the raw facts, meaningful information is generated: "Khushi is a 20-year-old BCA student."

• Entities:

  • Definition: An entity is a real-world object about which data is stored in a database.

  • Real-World Examples: Student, Teacher, Employee, Product.

  • Practical Example: In a college database, Student is an entity. Its corresponding attributes can be RollNo, Name, Age, and Address.

• Features of a Database Management System (DBMS):

  • Data Security: Protects database information against unauthorized access.

  • Data Integrity: Maintains the accuracy and consistency of data across transactions.

  • Reduced Data Redundancy: Minimizes unnecessary duplication of data.

  • Data Sharing: Enables multiple users to access and work with data concurrently.

  • Backup and Recovery: Provides automated and reliable mechanisms to restore data after system failures.

  • Concurrency Control: Facilitates safe and simultaneous multi-user access without data corruption.

  • Data Independence: Shields applications from being impacted by structural modifications to the underlying database.

  • Easy Data Management: Enables efficient operations to insert, update, delete, and retrieve data.

• Functions of a Database Administrator (DBA):

  • Definition: A Database Administrator (DBA) is an administrative professional responsible for managing and maintaining the database system.

  • Core Responsibilities:

  1. Database installation and configuration.

  2. User management.

  3. Security and permissions administration.

  4. Backup and recovery implementation.

  5. Data integrity maintenance.

  6. Performance monitoring.

  7. Storage management.

  8. Database maintenance and ongoing updates.

Relational Database Management Systems (RDBMS)

• Overview of RDBMS:

  • Definition: RDBMS stands for Relational Database Management System. It is a database architecture in which data is organized and stored in tabular structures consisting of rows and columns.

  • Core Features:

  1. Data is stored structured inside tables.

  2. Tables can be logically related to each other using keys.

  3. Native support for Structured Query Language (SQL) commands.

  4. Reduces data redundancy.

  5. Provides data security and structural integrity.

  • Commercial and Open-Source Systems: MySQL, Oracle, PostgreSQL, SQL Server.

• Comparative Analysis: File Processing System vs. DBMS:

  • File Processing System:

  • Storage Mechanism: Data is stored across separate, isolated files.

  • Duplication: High level of data redundancy and duplication.

  • Security & Sharing: Security features and data sharing capabilities are strictly limited.

  • Management Operations: Executing backups, system recovery, and establishing inter-file relationships are complex and difficult.

  • Database Management System (DBMS):

  • Storage Mechanism: Data is centrally stored within a structured database.

  • Duplication: Significantly reduces data redundancy.

  • Security & Sharing: Delivers enhanced security protocols and simultaneous multi-user sharing.

  • Management Operations: Backup/recovery operations and structural relationships are straightforward to establish and maintain.

• Relations and Attributes:

  • Relation: A relation refers to a table defined within a relational database, consisting of horizontal rows and vertical columns.

  • Tuples / Records: The horizontal rows within a relation are formally called tuples or records.

  • Attributes: The vertical columns within a relation are called attributes.

  • Practical Example: Given the relation schema Student(RollNo, Name, Age), the column identifiers RollNo, Name, and Age represent the attributes.

Keys, Integrity Rules, and Normalization

• Primary Key:

  • Definition: A Primary Key is an attribute or a set of attributes that uniquely identifies each individual record in a table.

  • Essential Characteristics:

  • Values must be strictly unique across all rows.

  • Cannot contain NULL values under any circumstances.

  • Uniquely identifies each record.

  • SQL Creation Example: sql CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50), Age INT );          In this definition, RollNo acts as the primary key.

• Referential Integrity:

  • Definition: Referential integrity is a relational database rule that ensures structural consistency between linked tables.

  • Fundamental Requirement: A foreign key value in a child table must strictly point to an existing primary key value within its parent table.

  • Concrete Example: Consider Student.RollNo as the Primary Key in the parent table Student, and Marks.RollNo as the Foreign Key in the child table Marks. Under this rule, entering RollNo = 5 into the Marks table is forbidden if RollNo = 5 does not already exist in the Student table.

• Normalization and Second Normal Form (2NF):

  • Normalization Definition: Normalization is the systematic process of organizing data in a database to reduce data redundancy and eliminate update, insertion, and deletion anomalies.

  • Second Normal Form (2NF) Rules:

  • The relation must already satisfy the requirements of First Normal Form (1NF).

  • There must be no partial dependency present in the relation.

  • Every non-key attribute must functionally depend on the complete, whole primary key.

  • Partial Dependency Example and Resolution:

  • Problem Scenario: If a table has a composite primary key consisting of (StudentID, CourseID), and the non-key attribute StudentName depends solely on StudentID, a partial dependency occurs.

  • Resolution Strategy: Such non-key attributes must be extracted and moved into a separate relation to satisfy 2NF.

Relational Algebra Operations

• Relational Algebra Operators:

  • Selection Operator ($\sigma$): Filters and retrieves specific tuples (rows) that satisfy a designated condition.

  • Projection Operator ($\pi$): Filters and retrieves specific attributes (columns) from a relation.

  • Union Operator ($\cup$): Combines all tuples from two union-compatible relations while automatically removing duplicates.

  • Set Difference Operator ($-$): Yields tuples that are present in the first relation but absent in the second relation.

  • Cartesian Product Operator ($\times$): Combines every tuple from the first relation with every tuple of the second relation.

  • Join Operator ($\bowtie$): Combines related tuples originating from two different relations based on a common matching condition.

• Outer Join Operations:

  • Definition: An Outer Join returns matching records from both relations along with non-matching records from one or both tables.

  • Categorization of Outer Joins:

  • Left Outer Join: Returns all records from the left relation alongside the matched records from the right relation.

  • Right Outer Join: Returns all records from the right relation alongside the matched records from the left relation.

  • Full Outer Join: Returns all combined records from both left and right relations regardless of whether matching entries exist.

  • Conceptual Algebra Definition: Outer join operations can be expressed conceptually using basic relational algebra operations including join ($\bowtie$), union ($\cup$), and set difference ($-$).

• Cartesian Product Operations ($\times$):

  • Operational Details: A Cartesian Product pairs each row of the first relation with every row of the second relation.

  • Mathematical Calculation Example: If relation Student contains 2 rows and relation Course contains 2 rows, the output relation contains $2 \times 2 = 4$ rows.

  • SQL Query Example: sql SELECT * FROM Student, Course;     

• Natural Join:

  • Definition: A Natural Join combines two tables based on matching values across columns that share identical names and compatible data types.

  • SQL Query Example: sql SELECT * FROM Student NATURAL JOIN Marks;     

  • Execution Behavior: If both Student and Marks tables contain a RollNo column, the query automatically joins the rows where RollNo values match and outputs a single unified RollNo column.

• Self-Join:

  • Definition: A Self-Join is a join operation in which a table is joined with itself.

  • Application Scenario: Useful when logical hierarchical relationships exist between different records contained within the same table.

  • SQL Query Example: sql SELECT E.Name AS Employee, M.Name AS Manager FROM Employee E JOIN Employee M ON E.ManagerID = M.EmpID;     

  • Execution Breakdown: The Employee table is aliased twice as E (acting as the employee context) and M (acting as the manager context).

Structured Query Language (SQL) Commands

• Data Manipulation Language (DML):

  • Overview: DML encompasses SQL commands used to insert, modify, and delete records stored within database tables.

  • Primary DML Commands:

  • INSERT: Adds new records into a table.

  • UPDATE: Modifies existing records.

  • DELETE: Removes records from a table.

  • Practical DML Example: sql INSERT INTO Student VALUES (1, 'Khushi', 20);     

• Creating Database Tables (CREATE TABLE):

  • Standard SQL Command: sql CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50), Age INT, DoB DATE, Address VARCHAR(100) );     

  • Attribute Field Breakdown:

  • RollNo: Integer column configured as the primary key identifying each student.

  • Name: Variable character string column (up to 50 characters) storing the student's name.

  • Age: Integer column storing age.

  • DoB: Date column storing date of birth.

  • Address: Variable character string column (up to 100 characters) storing residential address.

• Querying Data (SELECT Command and Clause):

  • Functionality of the SELECT Clause: Specifies the explicit columns to retrieve from a target table.

  • Basic Retrieval Syntax:

  SELECT column_name FROM table_name;
      ```
  - Explicit Column Query Example:
  

  sql SELECT Name, Age FROM Student;     ```

  • Clause Breakdown: SELECT indicates target columns, Name, Age are displayed fields, and FROM Student identifies the target table. The wildcard symbol * denotes all columns.

  • Conditional Filtering Query Example: sql SELECT * FROM Student WHERE Age > 18;     

• Structural Alterations and Truncation (ALTER and TRUNCATE):

  • ALTER Command: Used to alter or modify the column structure of an existing database table.

  • Add Column Example:

    ALTER TABLE Student ADD Address VARCHAR(100);
        ```
  - `TRUNCATE` Command: Removes all data records from a target table while leaving the structural table definition intact.
  - Table Truncation Example:
  

  sql TRUNCATE TABLE Student;       ```

• Updating Records (UPDATE Command):

  • Purpose: Modifies existing attribute values in table rows.

  • Standard Syntax Structure: sql UPDATE table_name SET column_name = value WHERE condition;     

  • Execution Example: sql UPDATE Student SET Age = 21 WHERE RollNo = 5;     

  • Operational Result: Updates the Age attribute value to 21 specifically for the record where RollNo equals 5.

Built-in SQL Functions and Database Views

• Built-in Functions and Mathematical Functions:

  • Function Definition: A predefined named operation that accepts an input argument, performs internal computation, and returns a calculated result.

  • Mathematical Functions: Perform explicit mathematical operations on scalar inputs.

  • Function Code Examples:

  • Absolute Value: SELECT ABS(-10); returns 10.

  • Rounding: SELECT ROUND(15.678, 2); returns 15.68.

  • Ceiling Value: SELECT CEIL(10.2); returns 11.

  • Floor Value: SELECT FLOOR(10.8); returns 10.

• String Manipulation Functions:

  • LOWER() Function: Transforms character strings into lowercase.

  • Example Query: SELECT LOWER('KHUSHI'); yields 'khushi'.

  • UPPER() Function: Transforms character strings into uppercase.

  • Example Query: SELECT UPPER('khushi'); yields 'KHUSHI'.

• Date Functions:

  • NOW() Function: Retrieves and displays the current system date and time stamp.

  • Example Query: SELECT NOW();.

  • YEAR() Function: Extracts the numeric four-digit year from a formatted date input.

  • Example Query: SELECT YEAR('2026-09-25'); yields 2026.

• Virtual Tables (Views):

  • Definition: A View is a virtual table defined dynamically by the execution output of an underlying SQL query.

  • Storage Characteristics: A view generally does not store a separate copy of physical table data; instead, the database engine stores the SQL view definition.

  • SQL Creation and Retrieval Examples: sql CREATE VIEW Student_View AS SELECT RollNo, Name FROM Student;     sql SELECT * FROM Student_View;     

  • System Advantages:

  1. Enhances security by restricting exposure to underlying base tables.

  2. Hides unnecessary or sensitive table columns.

  3. Simplifies complex joined queries.

  4. Provides tailored access to specific data subsets.

Exam Strategy and Answering Guidelines

• Response Structuring Principles for Examinations:

  • Guidelines for 5-Mark Questions: Provide a clear formal definition, list 3 to 5 key structural points, and include a relevant real-world example.

  • Guidelines for SQL-Based Questions: Write the formal command definition, supply the standardized generic query syntax, provide an illustrative code example, and conclude with a short explanation of execution logic.