Database Concepts (Copy)

Overview of Databases

• Databases are structured collections of data organized for easy access, management, and updating.
• They store information in tables made up of records (rows) and fields (columns), providing a foundation for efficient data handling.
• Widely used across industries for tasks like inventory management, customer relationship management, and financial systems.

Problems with File-Based Systems

1. Data Duplication and Redundancy:
   • Separate files often store the same data, leading to inefficiencies.
   • Example: A business may store customer details in both order and billing systems.
2. Data Inconsistency:
   • Changes in one file may not reflect in others, resulting in conflicts.
   • Example: An employee’s address update might appear in payroll records but not in HR systems.
3. Lack of Data Integration:
   • File-based systems make it difficult to cross-reference or share data between applications.
4. Data Dependency:
   • Changes to data structures require corresponding program modifications, increasing maintenance costs.

Benefits of Databases

1. Elimination of Redundancy:
   • A centralized database reduces duplication by storing data in a single location.
2. Improved Consistency:
   • Updates made in the database are instantly reflected across all connected applications.
3. Enhanced Security:
   • Databases provide user authentication, encryption, and role-based access to safeguard information.
4. Ease of Data Retrieval:
   • Query languages allow complex data searches and reporting, increasing productivity.
5. Data Independence:
   • Applications interact with data through a consistent interface, even if underlying structures change.

Database Terminology

1. Entity:
   • Represents a real-world object or concept, such as a student, product, or invoice.
2. Attribute:
   • A characteristic of an entity, such as a student’s name or age.
3. Table:
   • A collection of related records; for example, a “Students” table might include all enrolled individuals.
4. Record:
   • A single instance of an entity, stored as a row in a table.
5. Field:
   • A column in a table, corresponding to a specific attribute of the entity.

Keys in Relational Databases

1. Primary Key:
   • A unique identifier for each record in a table.
   • Example: Student ID in a “Students” table.
2. Candidate Key:
   • A set of attributes that could serve as a primary key.
   • Example: National Insurance Number and Employee ID in an “Employees” table.
3. Secondary Key:
   • Used for searching records but not required to be unique.
   • Example: Employee name in a company database.
4. Foreign Key:
   • Links two tables by referencing the primary key of another table, enabling relationships.
   • Example: “Course ID” in a “Student Enrollments” table referencing the “Courses” table.

Relationships and Referential Integrity

• One-to-One:
  • Each record in one table corresponds to one record in another.
  • Example: Passport and citizen.
• One-to-Many:
  • A single record in one table links to multiple records in another.
  • Example: A teacher assigned to multiple students.
• Many-to-Many:
  • Records in one table relate to multiple records in another, often resolved through an intermediary table.
  • Example: Students enrolling in multiple courses.
• Referential Integrity:
  • Ensures all foreign keys correspond to valid primary keys, maintaining consistency and preventing orphaned records.

Normalization

1. Purpose:
   • Organizes data into structured tables to minimize redundancy and dependency.
2. Forms of Normalization:
   • First Normal Form (1NF):
     • Removes repeating groups and ensures each field contains only atomic values.
     • Example: Splitting “Phone Numbers” into separate rows.
   • Second Normal Form (2NF):
     • Ensures all non-key attributes depend fully on the primary key.
     • Example: Separating customer details from orders in a retail database.
   • Third Normal Form (3NF):
     • Eliminates transitive dependencies, where non-key attributes depend on other non-key attributes.
     • Example: Storing department names in a separate table instead of duplicating them in an employee table.
3. Advantages:
   • Reduces storage requirements.
   • Improves update and retrieval efficiency.
   • Maintains data consistency.

Entity-Relationship (ER) Diagrams

• Visual tools to represent entities, their attributes, and relationships.
• Example: A database for a library could depict entities like “Books,” “Members,” and “Loans,” showing how they interconnect.

Database Management Systems (DBMS)

1. Definition:
   • Software enabling database creation, manipulation, and administration.
2. Core Functions:
   • Data Storage: Manages and stores data files efficiently.
   • Query Processing: Allows retrieval and manipulation of data through commands.
   • Security Management: Implements user authentication and access controls.
   • Backup and Recovery: Ensures data is recoverable in case of loss or corruption.
3. Advantages:
   • Simplifies complex data handling tasks.
   • Improves collaboration through shared access.
   • Provides tools for structured reporting.

Structured Query Language (SQL)

1. Purpose:
   • A language designed to interact with relational databases.
2. Basic Commands:
   • SELECT: Retrieves specific data from one or more tables.
     • Example: SELECT Name FROM Students WHERE Grade = 'A';
   • INSERT: Adds new records.
     • Example: INSERT INTO Students (Name, Age) VALUES ('John', 20);
   • UPDATE: Modifies existing records.
     • Example: UPDATE Students SET Age = 21 WHERE Name = 'John';
   • DELETE: Removes records.
     • Example: DELETE FROM Students WHERE Name = 'John';

Security in Databases

1. Authentication:
   • Ensures only authorized users access the system using credentials.
2. Access Controls:
   • Defines user permissions for reading, writing, or deleting data.
3. Encryption:
   • Protects data from unauthorized access during storage or transmission.
4. Audit Trails:
   • Records user activities for accountability and troubleshooting.

Applications of Databases

1. Educational Institutions:
   • Stores student records, grades, and attendance.
   • Ensures efficient course management and scheduling.
2. Healthcare Systems:
   • Manages patient records, appointments, and treatment histories.
   • Enhances accuracy and accessibility of critical medical information.
3. Retail Businesses:
   • Tracks inventory, sales, and customer data.
   • Facilitates personalized marketing and efficient supply chain management.

Conclusion

Databases offer a structured and efficient way to store and retrieve data. They address limitations of file-based systems by reducing redundancy, ensuring consistency, and providing enhanced security. Through normalization, relationships, and robust query capabilities, databases empower users to handle complex information efficiently, making them indispensable across industries.