Grade 12

Chapter 1 Chapter 2 SQL Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Table

Chapter 1

Data vs Information

Data consists of raw facts and figures that by themselves do not carry meaning. These can be numbers, text, images, or sounds collected from various sources. Data alone cannot help in decision-making without interpretation. For example, a temperature reading of "30" is data until you know whether it's in Celsius or Fahrenheit, the location, or time.

Information is the processed form of data that is meaningful and useful. It results from organizing, structuring, or presenting data in context. For example, "The temperature in New York on June 15th was 30°C, which is higher than the average" is information because it adds context, making the data valuable for analysis or decisions.

Understanding the distinction is crucial for fields like data science, business intelligence, and database management where the goal is to convert raw data into actionable information.

Features of Information

  • Accuracy: Information must be precise and error-free. Inaccurate information can lead to poor decisions or business losses.
  • Timeliness: Information must be available when needed. Outdated information might be irrelevant or misleading.
  • Relevance: The information should be applicable to the purpose it serves, ensuring it meets the needs of users or processes.
  • Completeness: All necessary details should be present for the information to be fully understood and actionable.
  • Consistency: Information should be consistent across different sources and over time to build trust and reliability.
  • Understandability: Information should be presented clearly and understandably so that users can interpret it correctly.
  • Accessibility: Authorized users should be able to easily access the information when needed without unnecessary hurdles.

Database and Its Purpose

A database is a collection of related data organized systematically to facilitate easy access, management, and updating. Databases enable users to store vast amounts of information securely and retrieve it quickly using query languages like SQL.

The primary purpose of a database is to support efficient data storage, retrieval, and manipulation, enabling applications to serve user needs effectively. Databases eliminate redundant data storage, provide data integrity, and support concurrent access by multiple users, which is essential in modern multi-user environments such as banking, e-commerce, and social networking.

For example, in an online store, a database maintains records of products, customers, orders, and payments, allowing seamless operations like searching products, placing orders, and tracking shipments.

Terminologies in Database

  • Table: The basic unit of data storage in relational databases. Each table contains rows (records) and columns (fields), representing entities such as employees or products.
  • Field: A column in a table, defining the data type and meaning of the data stored. For example, "Name," "Date of Birth," or "Price."
  • Record: A row in a table representing a single entity instance, such as a specific employee or product entry.
  • Tuple: Synonymous with a record, emphasizing its ordered structure in a table.
  • Object: In object-oriented or object-relational databases, an object is a data entity encapsulating attributes and behaviors, representing real-world entities more naturally.
  • Keys: Fields that uniquely identify records or establish relationships:
    • Primary Key: A unique identifier for each record in a table (e.g., employee ID).
    • Foreign Key: A field in one table that links to the primary key in another, establishing relational connections.

Data Dictionary

The data dictionary is an essential metadata repository in a database system that stores definitions, constraints, and structural information about data elements and their relationships. It acts as a blueprint, documenting the design and structure of the database to aid developers, DBAs, and users in understanding and managing the data.

It typically includes:

  • Table names and descriptions
  • Field names, data types, and allowed values
  • Relationships and constraints between tables
  • Access permissions and user roles

An accurate data dictionary is crucial for maintaining data integrity, consistency, and facilitating changes or upgrades in the database structure over time.

Database Management System (DBMS)

Introduction

A DBMS is a sophisticated software system that provides an interface to define, create, query, update, and administer databases. It abstracts the complexity of data storage and management from users, enabling efficient handling of large data volumes and concurrent multi-user access.

Popular DBMS examples include MySQL, Oracle, Microsoft SQL Server, and PostgreSQL, widely used in enterprise and web applications.

Objectives

  • Provide a convenient and efficient environment for users to store and retrieve data.
  • Ensure data integrity, security, and privacy.
  • Enable concurrent multi-user access with transaction management.
  • Provide backup, recovery, and fault tolerance mechanisms.
  • Maintain data independence by separating physical and logical data structures.

Advantages

  • Data Integrity: DBMS enforces rules to maintain accuracy and consistency of data.
  • Security: Access controls restrict unauthorized data access and manipulation.
  • Reduced Redundancy: Data normalization eliminates duplication, saving storage and improving consistency.
  • Backup and Recovery: DBMS automates data backup and recovery, minimizing data loss risks.
  • Multi-User Access: Supports concurrent access while preventing conflicts through locking and transactions.

Disadvantages

  • Complexity: Setting up and managing a DBMS requires skilled personnel and planning.
  • Cost: Licensing, hardware, and maintenance can be expensive.
  • Performance Overhead: Additional layers of abstraction may impact speed compared to file-based systems.
  • Single Point of Failure: Centralized DBMS can cause system-wide downtime if not designed with fault tolerance.

Types of Database Models

  • Hierarchical Model:

    Organizes data in a tree-like structure with parent-child relationships, where each child record has only one parent. This model is efficient for representing structured data like organizational charts, XML data, or file systems. However, its rigid hierarchy limits flexibility in representing complex relationships.

  • Network Model:

    Extends the hierarchical model by allowing multiple parent records, forming a graph structure. This many-to-many relationship model supports more complex real-world scenarios such as supply chains or telecommunications. Though flexible, network models are complex to design and manage.

  • Relational Model:

    The most widely used model today, it stores data in tables (relations) with rows and columns. Data relationships are established using keys, and operations are performed using relational algebra and SQL. This model offers simplicity, data independence, and flexibility, making it suitable for most business applications.

  • Entity-Relationship Model:

    Primarily used for database design, this conceptual model uses diagrams (ER diagrams) to illustrate entities, attributes, and relationships. It helps in visualizing and planning the database structure before implementation.

Integrity Constraints and Types

Integrity constraints enforce rules on data to ensure accuracy, consistency, and reliability in a database.

  • Domain Constraint: Defines the permissible set of values for a column, such as data type restrictions, value ranges, or formats (e.g., a date field must be a valid date).
  • Entity Integrity: Ensures that each table’s primary key is unique and not null, so every record is uniquely identifiable.
  • Referential Integrity: Maintains valid relationships between tables by ensuring foreign keys correspond to existing primary keys. It prevents orphan records and maintains relational consistency.
  • Key Constraints: Enforce uniqueness and correct usage of primary and foreign keys to maintain data consistency.

Normalization

Introduction

Normalization is a systematic approach of decomposing tables to minimize data redundancy and eliminate undesirable characteristics like update, insert, and delete anomalies. It improves data integrity and efficiency in relational databases by organizing data into multiple related tables.

Normalization follows several stages called normal forms, each with specific rules:

Normal Forms

  • First Normal Form (1NF): Ensures that all table columns contain atomic, indivisible values, and there are no repeating groups or arrays within rows.
  • Second Normal Form (2NF): Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key, removing partial dependencies.
  • Third Normal Form (3NF): Reached when the table is in 2NF and all attributes are only dependent on the primary key, eliminating transitive dependencies.

Advantages of Normalization

  • Eliminates redundant data, reducing storage needs.
  • Ensures data consistency and accuracy.
  • Makes the database easier to maintain and update.
  • Improves query performance by structuring data efficiently.

Disadvantages of Normalization

  • Increased number of tables can make queries more complex and slower due to joins.
  • Over-normalization may result in performance bottlenecks.
  • Sometimes denormalization is preferred for read-heavy applications to improve speed.

Centralized and Distributed Database

Introduction

Centralized Database systems store data in a single central location managed by one DBMS instance. All data access, processing, and administration occur on this central server.

Distributed Database systems distribute data across multiple physical locations, connected through a network but appear as a single database to users. This approach can enhance performance, fault tolerance, and scalability.

Advantages

  • Centralized: Easier management, simplified backup, and strong data integrity due to centralized control.
  • Distributed: Improved reliability (no single point of failure), better performance by localizing data access, and scalability by adding nodes.

Disadvantages

  • Centralized: Potential bottlenecks and vulnerability to single points of failure.
  • Distributed: Complex data synchronization, possible consistency issues, and higher maintenance costs.

Comparison

Feature Centralized Database Distributed Database
Data Location Stored in one place Spread across multiple locations
Management Complexity Simple Complex
Fault Tolerance Low (single point of failure) High (redundant nodes)
Performance Limited by server capability Potentially higher through parallelism
Cost Lower initial cost Higher infrastructure and maintenance costs

Database Security

Introduction

Database security involves protecting the database against unauthorized access, misuse, or theft. It is critical because databases often store sensitive information such as personal data, financial records, and proprietary business information.

Challenges

  • Preventing unauthorized access from hackers or malicious insiders.
  • Protecting against SQL injection and other cyber attacks.
  • Maintaining privacy and compliance with regulations such as GDPR or HIPAA.
  • Ensuring data availability despite threats like denial of service attacks.

Security Measures

  • Authentication: Verifying user identities through passwords, biometrics, or tokens.
  • Authorization: Granting appropriate access rights based on user roles.
  • Encryption: Protecting data at rest and in transit using cryptographic techniques.
  • Auditing: Keeping logs of access and modifications for accountability.
  • Backup and Recovery: Regularly backing up data to recover from security breaches or data loss.

Roles of DBA (Database Administrator)

The DBA plays a crucial role in managing database security, performance, and availability. Key responsibilities include:

  • Installing and configuring the DBMS software.
  • Defining and enforcing security policies.
  • Performing backup and recovery operations.
  • Monitoring system performance and tuning databases.
  • Managing user accounts, roles, and privileges.
  • Ensuring data integrity and compliance with regulations.

Practical Topics: DDL and DML Languages

Data Definition Language (DDL) commands are used to define and modify database structures such as tables, indexes, and schemas. Common DDL commands include CREATE, ALTER, and DROP.

Data Manipulation Language (DML) commands are used to manipulate data stored within tables. These include INSERT, UPDATE, DELETE, and SELECT.

SQL Data Types

SQL supports various data types to define the nature and constraints of data stored in table fields:

  • CHAR(size): Fixed-length character string. Useful for storing data with a fixed size, such as country codes.
  • VARCHAR(size): Variable-length character string. Efficient for storing text with varying lengths, like names or emails.
  • BINARY(size): Fixed-length binary data, used to store raw bytes.
  • VARBINARY(size): Variable-length binary data.
  • TINYBLOB: Very small binary large object (up to 255 bytes).
  • TINYTEXT: Very small text data.
  • TEXT: Large text data (up to 65,535 characters).
  • LONGTEXT: Very large text data (up to 4GB).
  • ENUM: A string object that can have one value chosen from a list of predefined values.
  • BIT: Stores bit-field values.
  • TINYINT: Very small integer (usually 1 byte).
  • BOOLEAN: Stores true or false values (often implemented as TINYINT).
  • INTEGER: Standard integer value.
  • FLOAT: Floating-point number with single precision.
  • DOUBLE: Floating-point number with double precision.
  • DECIMAL(p,s): Fixed-point number with precision p and scale s, suitable for financial calculations.
  • DATE: Stores calendar dates (year, month, day).
  • DATETIME: Stores date and time values.

Syllabus

S.N. Topic Content wise marks Working Hours
1 Database management system 8 12
2 Data communication and networking 9 15
3 Web Tech- II 8 12
4 C programming -II 8 12
5 Object Oriented Programming 6 10
6 Software devolopment life cycle 6 10
7 Recent Trends in Technology 5 9
Total 50 80

Database and DBMS Questions

1. What is Database and DBMS? List advantages and disadvantages of DBMS.

Database: A structured collection of related data.

DBMS: Software that stores, manages, and controls access to the database.

Advantages: Reduces redundancy, improves data integrity, provides security, backup and recovery, multi-user access.

Disadvantages: Complex to set up, costly, needs skilled staff, single point of failure.

2. Differentiate between File Processing System and DBMS.

3. Explain different DBMS models with advantages and disadvantages.

4. What is a relational database? How is it different?

It organizes data in tables with rows and columns, using keys to link tables. Easier to understand, more flexible than hierarchical or network models.

5. What is data redundancy? How does DBMS reduce it?

Unnecessary duplication of data. DBMS uses normalization, keys, and constraints to minimize redundancy.

6. Differentiate between centralized and distributed database systems.

7. Who is DBA? Major responsibilities?

Database Administrator (DBA): Manages database system.

Responsibilities: Install DBMS, design schema, manage security, backup and recovery, monitor performance, enforce policies.

8. Define normalization. Explain 1NF, 2NF, 3NF with examples.

Normalization: Process to reduce redundancy.

9. Explain primary key, foreign key, candidate key with examples.

10. What is SQL? Explain its components and functions.

SQL: Structured Query Language for managing databases.

Functions: Retrieve, update, manage, and secure data.

Chapter 2

Working with Functions

1. Working with a Function

a. Define Function: A function is a self-contained block of code that performs a specific task. Functions help break a program into smaller, manageable parts.

b. Syntax of Functions:

return_type function_name(parameters)
{
    // body of function
    return value;
}

c. Types of Functions:

d. Components of a Function:

2. Categories of Functions with Example

3. Storage Classes

SQL Commands Examples

a. Create a table named students:

CREATE TABLE students (
  Id INT PRIMARY KEY,
  Name VARCHAR(50),
  Class VARCHAR(10),
  Marks INT
);

Output

Output

b. Insert records into the students table:

INSERT INTO students (Id, Name, Class, Marks)
VALUES
  (1, 'Anita', '12A', 78),
  (2, 'Max', '12B', 82),
  (3, 'Buddhi', '12A', 90),
  (4, 'Harold', '12C', 75),
  (5, 'Sunnayana', '12B', 88);

Output

Output

c. Display all records from the students table:

SELECT * FROM students;

Output

Output

d. Update the marks of student named 'Anita' to 85:

UPDATE students
SET Marks = 85
WHERE Name = 'Anita';
output

Output

e. Delete the record of the student with Id = 3:

DELETE FROM students
WHERE Id = 3;

Output

Recursive Function

Definition: A recursive function is a function that calls itself to solve a smaller subproblem until a base condition is met.

Syntax of a Recursive Function:

return_type function_name(parameters)
{
    if (base_condition)
        return result;
    else
        return function_name(smaller_problem);
}

Example: Factorial using recursion

#include <stdio.h>

int factorial(int n) {
    if (n == 0)
        return 1;
    else
        return n * factorial(n - 1);
}

int main() {
    int result = factorial(5);
    printf("Factorial is %d", result);
    return 0;
}

Passing Array to a Function

Explanation: In C, you can pass an entire array to a function by passing its name (which acts as a pointer to the first element).

Example: Passing an array to a function

#include <stdio.h>

void display(int arr[], int size) {
    for (int i = 0; i < size; i++) {
        printf("%d ", arr[i]);
    }
}

int main() {
    int numbers[5] = {1, 2, 3, 4, 5};
    display(numbers, 5);
    return 0;
}