DATABASES

Page 1

1. Introduction to Databases

A database is an organized collection of data that allows efficient retrieval, insertion, updating, and deletion.

A DBMS (Database Management System) is software that manages databases (e.g., MySQL, SQLite, PostgreSQL, Access).

Advantages of databases over file-based systems:

Reduced data redundancy (duplicate data).

Improved data integrity and consistency.

Easier data sharing and security control.

Better backup and recovery.

2. Key Concepts

Term Definition

Entity A real-world object (e.g., Student, Course).

Attribute A property of an entity (e.g., Student Name, Age).

Record (Tuple)A single row in a table.

Field A column in a table.

Primary Key (PK)A unique identifier for each record.

Foreign Key (FK)A field that links to the primary key of another table.

Candidate Key A field that could be used as a primary key.

Composite Key A primary key made up of more than one field.

Entity Relationship (ER) Model A diagram showing how entities relate.

3. Relationships

Type Description Example

One-to-One (1:1)Each record in Table A links to one in Table B. Person ↔ Passport

One-to-Many (1:M)One record in Table A links to many in Table B. Teacher → Students

Many-to-Many (M:N)Records in A link to many in B, and vice versa .Students ↔ Courses

👉 Many-to-Many relationships are broken into two One-to-Many relationships using a junction (link) table.

4. Normalization

Purpose:

To reduce redundancy and improve data integrity.

Normal FormRules

1NFNo repeating groups; each field holds only one value.

2NFIn 1NF + no partial dependency on part of a composite key.

3NFIn 2NF + no transitive dependency (non-key → non-key).

Example:

If a student table stores CourseName (which depends on CourseID), move that into a separate Course table.

5. SQL (Structured Query Language)

🟢 Creating Tables

CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50), Age INT, CourseID INT, FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) );

🟢 Basic Queries

SELECT Name, Age FROM Students WHERE Age > 18; INSERT INTO Students VALUES (1, 'Aali Fahad', 19, 2); UPDATE Students SET Age = 20 WHERE StudentID = 1; DELETE FROM Students WHERE StudentID = 1;

🟢 Joins

SELECT Students.Name, Courses.CourseName FROM Students JOIN Courses ON Students.CourseID = Courses.CourseID;

🟢 Aggregate Functions

SELECT AVG(Age), COUNT(*) FROM Students;

6. Data Integrity and Validation

ConceptDescription

Entity IntegrityEach record must have a unique primary key.

Referential IntegrityForeign key values must exist in the related table.

ValidationEnsures data is correct before entry (e.g., range, type, format checks).

7. Security and Access Control

Authentication: Verify users (username/password).

Authorization: Control user permissions (read, write, update).

Encryption: Protect data at rest or in transit.

Backup and Recovery: Regular backups to prevent data loss.

8. Transaction Management

Transaction: A logical unit of work (e.g., transferring money).

ACID Properties:

Atomicity: All or nothing.

Consistency: Database remains valid.

Isolation: Transactions don’t interfere.

Durability: Changes persist after commit.

9. Views and Indexes

View: A virtual table created from a query.

CREATE VIEW AdultStudents AS SELECT Name, Age FROM Students WHERE Age >= 18;

Index: Improves search speed but increases storage use.

10. Big Data & Modern Databases (optional A-Level topics)

NoSQL Databases: Non-relational systems (e.g., MongoDB, Cassandra).

Advantages: Scalability, flexibility with unstructured data.

Types: Document, Key-Value, Column, Graph databases.