Database Concepts

A database is an organized collection of information stored electronically so that it can be easily accessed and managed.

For example, a school database may contain information about:

• Students
• Teachers
• Courses
• Attendance
• Examinations
• Fees

Instead of maintaining this information in separate paper files, a database can store it in a structured form.

Applications can contain thousands or millions of records. Managing such information manually becomes difficult.

Databases help us to:

• Store large amounts of data
• Search data quickly
• Update existing information
• Delete unwanted information
• Organize related data
• Control access to information
• Generate reports

Suppose a coaching institute wants to store student information. A database may contain a table called students.

This table contains structured student information that can be searched and managed using SQL.

A relational database stores information in tables. A table consists of rows and columns.

For example:

students
--------------------------------
id | name  | course | age
--------------------------------
1  | Rahul | Python | 21
2  | Priya | SQL    | 22
3  | Amit  | Java   | 20

Here, students is the table name.

A row represents one complete record in a table.

For example:

1 | Rahul | Python | 21

The above line represents one student's record.

Rows are also commonly called records.

A column represents a particular type of information stored for each record.

For example, the following are columns:

• id
• name
• course
• age

Columns are also called fields in many database contexts.

A record is a collection of related values representing one entity or item.

Example:

ID: 101
Name: Rahul
Course: Python
Age: 21

All these values together represent one student record.

A database schema describes the structure of a database. It can include tables, columns, relationships, constraints, and other database objects.

For example, a college database may contain:

• students
• teachers
• courses
• fees
• attendance

The design and relationships among these objects form part of the database schema.

A Database Management System (DBMS) is software used to create, store, organize, retrieve, and manage data in databases.

Examples include:

• MySQL
• PostgreSQL
• Oracle Database
• Microsoft SQL Server
• SQLite

A relational database organizes data into tables and allows relationships to be established between related tables.

For example, an application may have:

• students table
• courses table
• payments table

These tables can be related using common columns and keys.

A primary key uniquely identifies each record in a table.

For example, the id column can be used as a primary key in a students table.

id | name
---|------
1  | Rahul
2  | Priya
3  | Amit

Each student can have a unique ID.

A foreign key is used to create a relationship between tables by referring to a key in another table.

For example, a payments table can contain a student_id that refers to the ID of a student.

students
id | name

payments
id | student_id | amount

This allows payment records to be associated with students.

Relationships define how data in different tables is connected.

Common relationship types include:

• One-to-One
• One-to-Many
• Many-to-Many

Relationships help organize related information and reduce unnecessary duplication.

In a one-to-one relationship, one record in one table is associated with one record in another table.

For example, one employee may have one employee profile record in another table.

In a one-to-many relationship, one record can be associated with many records in another table.

For example, one customer can place many orders.

Customer
   |
   |---- Order 1
   |---- Order 2
   |---- Order 3

In a many-to-many relationship, many records in one table can be related to many records in another table.

For example, students can enroll in multiple courses, and each course can have multiple students.

A separate junction or linking table is commonly used to represent this relationship.

A query is a request made to a database to retrieve or manipulate information.

Example:

SELECT * FROM students;

This query requests all columns and rows from the students table.

CRUD represents four basic operations commonly performed on stored data.

Database security protects stored information from unauthorized access, modification, or deletion.

Common security practices include:

• Strong authentication
• User permissions
• Access control
• Regular backups
• Secure connections
• Parameterized queries

A database backup is a copy of database information that can be used to restore data if the original data is lost or damaged.

Regular backups are important for applications containing valuable information such as customer records, payments, and business data.

Normalization is a database design technique used to organize data and reduce unnecessary duplication.

Instead of storing the same information repeatedly, related information can be separated into appropriate tables and connected using keys.

Common normal forms include:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

An index is a database object that can help the database find rows more efficiently for suitable queries.

Indexes can improve query performance, but they also require additional storage and can add overhead to data modification operations.

A transaction is a sequence of database operations that are treated as a logical unit of work.

For example, a banking transaction may involve multiple related database operations that should be completed consistently.

Common transaction commands include:

• COMMIT
• ROLLBACK

A typical application works with a database through a backend program.

User
  ↓
Frontend
  ↓
Backend
  ↓
SQL Query
  ↓
Database
  ↓
Result
  ↓
Backend
  ↓
Frontend

This basic flow is used in many data-driven applications.