Basic Database Concepts
A database is an organized collection of data that is stored and managed efficiently.
It helps in storing, retrieving, and managing data easily.
Example:
A school database can store student records, including names, roll numbers, marks, and
attendance.
---
Database Approach vs. File-Based System
Feature | File-Based System | Database Approach
------------------ | ----------------- | ------------------
Data Storage | Separate files | Structured tables
Redundancy | High | Reduced
Data Retrieval | Difficult | Easy
Example:
- File-Based System: A school stores student marks in different Excel files.
- Database Approach: A school stores student marks in a database table, making it easy to search.
---
Database Architecture
1. Single-Tier: The database and application are on the same system.
2. Two-Tier: The application communicates with the database directly (e.g., client-server model).
3. Three-Tier: There is an additional middle layer (server), which processes requests.
Example:
A website login system:
- Browser (frontend) sends username/password to server.
- Server checks database and sends response.
---
Three-Level Schema Architecture
External Level (View Level) - What the user sees (e.g., only seeing your own bank account details).
Conceptual Level (Logical Level) - Defines the structure of the database (e.g., tables, relationships).
Internal Level (Physical Level) - How data is stored on disk.
Example:
ATM Transactions:
- You see your balance (External Level).
- The bank database manages account details (Conceptual Level).
- The data is stored in hard drives (Internal Level).
---
Relational Data Model
A relational database organizes data into tables.
Example:
Student Table
| ID | Name | Age |
|----|------|-----|
| 1 | John | 20 |
| 2 | Sarah | 22 |
---
Keys of Relations
Primary Key - Uniquely identifies each row.
Foreign Key - Links one table to another.
Candidate Key - A column that can be a primary key.
Composite Key - A combination of columns that uniquely identify a row.
Example:
| Student_ID | Name | Course_ID |
|------------|------|----------|
|1 | John | C101 |
|2 | Sarah | C102 |
---
Types of Joins
Inner Join - Returns matching rows from both tables.
Left Join - Returns all rows from the left table and matching rows from the right table.
Right Join - Returns all rows from the right table and matching rows from the left table.
Full Join - Returns all rows from both tables, with NULL if there is no match.
Example:
| Student_ID | Name | Course_ID |
|------------|------|----------|
|1 | John | C101 |
|2 | Sarah | C102 |
| Course_ID | Course |
|-----------|--------|
| C101 | Math |
| C103 | Physics |
Inner Join (Matching Course_ID):
| Student_ID | Name | Course_ID | Course |
|------------|------|----------|--------|
|1 | John | C101 | Math |
---
Conclusion
These are the basics of databases, relational models, and relational algebra with examples.
