Class 12 – CS 083 Database Management – Notes

by

Databases hold significant importance for 12th-grade students due to their versatile and practical applications across various academic subjects and future career prospects. These structured data repositories serve as invaluable tools for managing, organizing, and analyzing data efficiently, skills that are indispensable in subjects like mathematics, science, and social studies. So we prepared proper Class 12 Database Management Notes for students to score in CBSE exams.

Unit 3 – Database Management – Notes

Database Concepts

Introduction to Database Concepts

A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval, management, and manipulation of information. It serves as a central repository for storing and managing data, making it a fundamental component of modern information systems. Here, we will delve into the basics of database concepts and understand why they are essential.

The Need for Databases

  1. Data Organization: Databases provide a structured way to organize data. Instead of storing information in isolated files or spreadsheets, databases allow for the systematic arrangement of data into tables, rows, and columns. This organization simplifies data management and retrieval.
  2. Data Integrity: Maintaining data accuracy and consistency is crucial for any organization. Databases employ mechanisms such as constraints, validation rules, and data relationships to ensure data integrity. This reduces the risk of errors and data inconsistencies.
  3. Data Security: Databases offer security features to control access to data. User authentication, authorization, and encryption help protect sensitive information from unauthorized access or tampering.
  4. Efficient Data Retrieval: With databases, you can quickly retrieve specific pieces of information using structured query languages (SQL). This efficient data retrieval is vital for applications like customer relationship management (CRM), e-commerce, and more.
  5. Concurrency Control: In multi-user environments, where multiple users access and modify data simultaneously, databases employ concurrency control mechanisms to prevent data conflicts and ensure data consistency.
  6. Scalability: As an organization grows, so does its data. Databases provide scalability options, allowing organizations to handle increasing amounts of data efficiently by adding more hardware resources or optimizing database design.
  7. Data Analysis: Databases are crucial for data analysis and business intelligence. They enable the storage of historical data, which can be analyzed to make informed decisions, track trends, and forecast future performance.
  8. Data Backup and Recovery: Databases offer backup and recovery features to safeguard against data loss due to hardware failures, accidents, or disasters. Regular backups ensure that data can be restored in case of emergencies.
  9. Data Redundancy Reduction: Databases minimize data redundancy by storing data in normalized tables. This reduces storage requirements and ensures consistency by updating data in one place.
  10. Support for Complex Data Types: Modern databases can handle complex data types such as images, videos, and JSON documents, making them versatile for various applications.

Relational Data Model

Introduction to the Relational Data Model

The relational data model is a fundamental concept in database management, introduced by Edgar F. Codd in the 1970s. It represents data as a collection of tables, known as relations, which are interconnected by common attributes / fields / columns. Here, we’ll explore the key components and concepts of the relational data model.

Components of the Relational Data Model

  1. Relation:
    • A relation is a table in the relational data model.
    • It consists of rows and columns.
    • Each row represents a record or tuple of an individual, and each column represents an attribute or field holding the same property.
  2. Attribute:
    • An attribute is a characteristic or property of an entity represented as a column in a relation.
    • For example, in a “Customers” relation, attributes might include “CustomerID,” “Name,” “Email,” and “Phone.”
  3. Tuple:
    • A tuple is a row in a relation.
    • It represents a single instance or record of data.
    • Each tuple contains values for each attribute defined in the relation.
  4. Domain:
    • A domain specifies the set of allowable values for a particular attribute.
    • For instance, the domain of the “Age” attribute might be defined as integers between 0 and 100.
    • It can be termed as range.
  5. Degree:
    • The degree of a relation is the number of attributes it contains.
    • It indicates the complexity and structure of the relation.
  6. Cardinality:
    • Cardinality refers to the number of tuples in a relation.
    • It signifies the size of the data set represented by the relation.

Keys in the Relational Data Model

  1. Candidate Key:
    • A candidate key is a minimal set of attributes that can uniquely identify each record / tuple in a relation / table.
    • There can be multiple candidate keys in a relation.
    • One of them will be chosen as the primary key.
  2. Primary Key:
    • The primary key is a specific candidate key chosen to uniquely identify each record / tuple.
    • It must be unique and cannot contain NULL values.
    • It is used for data retrieval and integrity enforcement.
  3. Alternate Key:
    • Alternate keys are candidate keys that are not selected as the primary key.
    • They can still be used for uniqueness constraints or as reference keys in other relations.
  4. Foreign Key:
    • A foreign key is an attribute or set of attributes in one relation that refers to the primary key of another relation.
    • It establishes relationships between tables and enforces referential integrity.
    • Foreign keys ensure that values in the referencing table exist in the referenced table.

The relational data model forms the basis for most modern database management systems (DBMS). It provides a structured and efficient way to organize, store, and retrieve data while maintaining data integrity through the use of keys and relationships between tables. Understanding these concepts is crucial for designing and managing relational databases effectively.

Structured Query Language

Introduction to SQL

SQL (Structured Query Language) is a powerful and standardized language used for managing and manipulating relational databases. It provides a structured and efficient way to interact with databases, enabling tasks such as data retrieval, insertion, deletion, and schema definition. In this set of notes, we will explore various aspects of SQL.

SQL Language Components

Data Definition Language (DDL) and Data Manipulation Language (DML)

SQL is divided into two main categories: DDL and DML.

  • DDL deals with defining the database structure, including creating, altering, and deleting tables and databases.
  • DML focuses on manipulating data within the database, such as inserting, updating, and deleting records.

Data Types

SQL supports various data types to specify the kind of data that can be stored in each column of a table:

  • CHAR(n): Fixed-length character strings with a specified maximum length ‘n’.
  • VARCHAR(n): Variable-length character strings with a maximum length of ‘n’.
  • INT: Integer data type for whole numbers.
  • FLOAT: Floating-point number data type for decimals.
  • DATE: Date data type for storing dates.

Constraints

SQL allows you to apply constraints to columns for data integrity:

  • NOT NULL: Ensures that a column cannot contain NULL values.
CREATE TABLE Students (
    StudentID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Age INT NOT NULL,
    GPA DECIMAL(3, 2) NOT NULL
);

In this example:

  • We’re creating a table named Students.
  • We specify the NOT NULL constraint after each column definition to ensure that the StudentID, FirstName, LastName, Age, and GPA columns must have a value, and NULL values are not allowed.

This ensures that when you insert data into the Students table, you must provide a value for each of these columns, and those values cannot be NULL.

  • UNIQUE: Enforces unique values within a column.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE,
    Department VARCHAR(50)
);

In this example:

  • We’re creating a table named Employees.
  • We specify the UNIQUE constraint after the Email column definition to ensure that email addresses in the Email column must be unique across all rows in the table.
  • The EmployeeID column is designated as the primary key, which is also unique by definition.

This ensures that when you insert data into the Employees table, each email address in the Email column must be unique, preventing duplicate email addresses in the table.

  • PRIMARY KEY: Specifies a column or set of columns as the primary key for unique identification.
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    GPA DECIMAL(3, 2)
);

In this example:

  • We’re creating a table named Students.
  • We specify the PRIMARY KEY constraint after the StudentID column definition to designate it as the primary key for the table.
  • The StudentID column will contain unique values for each student, and it cannot contain NULL values.

This ensures that each row in the Students table has a unique StudentID, and you can use the StudentID to uniquely identify each student in the table.

  • FOREIGN KEY: Establishes a relationship between tables by linking a column to the primary key of another table.
-- Create the parent table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Create the child table with a FOREIGN KEY constraint
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example:

  • We create two tables, Departments and Employees.
  • In the Departments table, the DepartmentID column is the primary key, which ensures unique department IDs.
  • In the Employees table, we have a DepartmentID column, which is intended to store the department each employee belongs to.
  • The CONSTRAINT clause is used to define the FOREIGN KEY constraint named FK_Department. It specifies that the DepartmentID column in the Employees table is a foreign key that references the DepartmentID column in the Departments table.

This FOREIGN KEY constraint ensures that every value in the DepartmentID column of the Employees table corresponds to a valid department ID in the Departments table. It maintains referential integrity and helps enforce relationships between tables.

Database Operations

  • CREATE DATABASE: Creates a new database.
  • USE DATABASE: Specifies the database to be used.
  • SHOW DATABASES: Lists all available databases.
  • DROP DATABASE: Deletes a database.
  • SHOW TABLES: Displays a list of tables in the current database.
  • CREATE TABLE: Creates a new table with specified columns and data types.
  • DESCRIBE TABLE: Provides information about the structure of a table.
  • ALTER TABLE: Modifies the structure of an existing table, including adding or removing columns and altering primary keys.
  • DROP TABLE: Deletes a table and its data.

Data Manipulation

  • INSERT: Adds new records into a table.
  • DELETE: Removes records from a table.
  • SELECT: Retrieves data from one or more tables.
  • Operators: Mathematical (+, -, *, /), relational (=, <>, >, <, >=, <=), and logical (AND, OR, NOT) operators.
  • Aliasing: Renaming columns or tables for readability.
  • DISTINCT: Retrieves unique values from a column.
  • WHERE: Filters rows based on a specified condition.
  • IN: Checks for a value within a list.
  • BETWEEN: Filters rows within a specified range.
  • ORDER BY: Sorts query results.
  • NULL: Represents missing or unknown data.
  • IS NULL: Tests for NULL values.
  • IS NOT NULL: Tests for non-NULL values.
  • LIKE: Searches for a specified pattern.
  • UPDATE: Modifies existing data.
  • DELETE: Removes data from a table.

Aggregate Functions and Grouping

  • MAX, MIN, AVG, SUM, COUNT: Perform calculations on data.
  • GROUP BY: Groups data by one or more columns.
  • HAVING: Filters grouped data.

Joins

  • Cartesian Product: Combines every row from one table with every row from another.
  • Equi-Join: Combines rows based on a specified equality condition.
  • Natural Join: Joins tables using columns with the same name.

SQL is a versatile language that forms the foundation of database management systems, enabling users to interact with and extract valuable information from relational databases effectively. Understanding SQL is essential for working with databases in various applications and domains.

Interface of Python with an SQL database

Connecting SQL with Python

Python provides several libraries and modules that enable interaction with SQL databases, the most popular being SQLite, MySQL, and PostgreSQL. This section focuses on the basics of connecting Python with an SQL database.

Key Components and Functions

1. connect(): Establishing a Database Connection

  • To connect to an SQL database, you typically use the connect() function provided by the respective database library.
  • Example (SQLite):
import sqlite3 conn = sqlite3.connect('mydatabase.db')

2. cursor(): Creating a Cursor Object

A cursor is a control structure used to traverse and manipulate database records.

  • You create a cursor object using the cursor() method of the connection.
  • Example:
cursor = conn.cursor()

3. execute(): Executing SQL Queries

  • The execute() method of the cursor is used to run SQL queries.
  • Example:
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ('Alice', 25)

4. commit(): Committing Changes

  • After executing queries that modify the database, you must commit the changes to make them permanent.
  • Example:
conn.commit()

5. fetchone(): Retrieving a Single Row

  • Use fetchone() to retrieve the next row from the result set.
  • Example:
row = cursor.fetchone()

6. fetchall(): Retrieving All Rows

  • fetchall() returns a list of all rows from the result set.
  • Example:
all_rows = cursor.fetchall()

7. rowcount: Counting Rows

  • rowcount attribute of the cursor provides the number of rows affected by the last operation.
  • Example:
num_rows_affected = cursor.rowcount

Performing Queries Using %s Format Specifier or format()

To perform SQL queries with dynamic data, you can use placeholders and format specifiers:

  • Using %s Format Specifier (for SQLite):
name = 'Bob' age = 30 cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", (name, age))
  • Using format() (for MySQL/PostgreSQL)
name = 'Bob' age = 30 cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))

Creating Database Connectivity Applications

With the knowledge of these components and functions, you can create Python applications that interact with SQL databases. These applications can perform a wide range of operations, including data insertion, retrieval, updates, and deletions.

Example:

import sqlite3

# Establish a connection to the database
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object
cursor = conn.cursor()

# Execute SQL queries
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ('Alice', 25))
conn.commit()

# Fetch and display data
cursor.execute("SELECT * FROM students")
all_students = cursor.fetchall()
for student in all_students:
    print(student)

# Close the cursor and the connection when done
cursor.close()
conn.close()

Python’s versatility and database libraries make it an excellent choice for creating applications that interact with SQL databases, whether for web development, data analysis, or other database-driven tasks.

Leave a Comment